第1章 MySQL存储引擎介绍
1.1 MySQL存储引擎介绍
文件系统是一种软件,它是操作系统组织和存取数据的一种机制。文件系统类型分为:ext2、ext3、ext4,xfs和数据等,不管使用什么文件系统,数据内容都不会变化,不同的是文件系统的存储空间、大小、速度。
MySQL引擎可以理解为,MySQL的“文件系统”,只不过功能更加强大,除了可以提供基本的存取功能,还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
1.2 MySQL存储引擎种类
MySQL 提供以下存储引擎:InnoDB、MyISAM(以上两个最常用),MEMORY、ARCHIVE、FEDERATED、EXAMPLE、BLACKHOLE、MERGE、NDBCLUSTER、CSV等,还可以使用第三方存储引擎。
第2章 MySQL Innodb存储引擎简介
Innodb存储引擎是MySQL5.5版本之后默认的存储引擎,它提供了高可靠性和高性能。
2.1 Innodb存储引擎优点
- 事务安全(遵从 ACID)
- MVCC(Multi-Versioning Concurrency Control,多版本并发控制)(InnoDB 行级别锁定、Oracle 样式一致非锁定读取)
- 表数据进行整理来优化基于主键的查询
- 支持外键引用完整性约束
- 大型数据卷上的最大性能
- 将对表的查询与不同存储引擎混合
- 出现故障后快速自动恢复
- 用于在内存中缓存数据和索引的缓冲区池
2.2 Innodb功能总览
2.3 查看数据库的存储引擎设置
2.3.1 使用 SELECT 确认会话存储引擎
mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.01 sec)
2.3.2 使用 SHOW 确认每个表的存储引擎
mysql> use world; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table city; +-------+-------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------+ | city | CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show table status like 'countrylanguage'\G; *************************** 1. row *************************** Name: countrylanguage Engine: InnoDB Version: 10 Row_format: Compact Rows: 984 Avg_row_length: 99 Data_length: 98304 Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: NULL Create_time: 2017-11-15 11:05:34 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
2.3.3 使用 INFORMATION_SCHEMA 确认每个表的存储引擎
mysql> SELECT TABLE_NAME, ENGINE FROM -> INFORMATION_SCHEMA.TABLES -> WHERE TABLE_NAME = 'city' -> AND TABLE_SCHEMA = 'world'; +------------+--------+ | TABLE_NAME | ENGINE | +------------+--------+ | city | InnoDB | +------------+--------+ 1 row in set (0.00 sec)
2.4 设置存储引擎
2.4.1 在启动配置文件中设置服务器存储引擎
[mysqld] default-storage-engine=<Storage Engine>
2.4.2 使用 SET 命令为当前客户机会话设置
SET @@storage_engine=<Storage Engine>;
2.4.3 在 CREATE TABLE 语句指定
CREATE TABLE 表名(字段 类型) ENGINE = <Storage Engine>;
第3章 Innodb体系结构
共享表空间(ibdata1):默认情况下,InnoDB 数据字典(元数据)、撤销日志(UNDO,是事务日志的一种)和缓冲区存储在系统“表空间”中。
独立表空间:是5.6以后默认的存储引擎,影响的是在新创建的表时自动会使用独立表空间存储,每个表一个frm(表结构定义文件)和数据文件(.ibd)
3.1 物理存储结构(表空间)
3.1.1 段、区、页
- 页是数据文件中,最小分配单元,默认是16KB
- 区是连续的页
- 段,多个区构成,一个表是一个段
3.2 系统共享表空间
这是单个逻辑存储区域,可以包含一个或多个文件,每个文件可以是常规文件或原始分区,最后的文件可以自动扩展。
3.2.1 通过添加数据文件增加表空间大小
在 my.cnf 文件中使用 innodb_data_file_path 选项:
[mysqld] innodb_data_file_path=datafile_spec1[;datafile_spec2]...
提示:ibdata1 这个文件必须要和当前文件大小一致,不能多不能少
3.2.2 配置示例
创建一个表空间,其中包含一个名为 ibdata1 且大小为 50 MB (固定)的数据文件和一个名为 ibdata2 且大小为 50 MB(自动扩展)的数据文件:
[mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
提示:默认情况下将文件放置在 data 目录中,如果需要,可以显式指定文件位置。
3.3 数据独立表空间
除了系统表空间之外,InnoDB还在数据库目录中创建另外的表空间,用于每个InnoDB表的.ibd文件,InnoDB 创建的每个新表在数据库目录中设置一个.ibd文件来搭配表的.frm文件,可以使用innodb_file_per_table选项控制此设置,更改该设置仅会更改已创建的新表的默认值。
提示:在mysql5.6开始,默认的配置为:|innodb_file_per_table | ON|
第4章 Innodb引擎事务
4.1 Innodb引擎事务简介
一组数据操作执行步骤,这些步骤被视为一个工作单元,可以用于对多个语句进行分组,或在多个客户机并发访问同一个表中的数据时使用。所有步骤都成功或都失败,如果所有步骤正常,则执行;如果步骤出现错误或不完整,则取消。
事务是伴随着“交易”出现的数据库概念,数据库中的“交易”是指ACID。
4.1.1 事务ACID介绍
- Atomic(原子性):所有语句作为一个单元全部成功执行或全部取消
- Consistent(一致性):如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态
- Isolated(隔离性):事务之间不相互影响
- Durable(持久性):事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
4.1.2 事务处理流程举例
4.1.3 事务SQL控制语句
START TRANSACTION(或 BEGIN):显式开始一个新事务,事务开始的标记(二进制日志中有很明显的标记) SAVEPOINT:分配事务过程中的一个位置,以供将来引用 COMMIT:事务的完成标记,永久记录当前事务所做的更改 ROLLBACK:事务撤销标记,取消当前事务所做的更改 ROLLBACK TO SAVEPOINT:取消在 savepoint 之后执行的更改 RELEASE SAVEPOINT:删除 savepoint 标识符 SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit模式
4.2 Autocommit模式设置
从MySQL5.5开始,开启事务时不再需要begin或者start transaction语句,并且默认是开启了Autocommit模式,作为一个事务隐式提交每个语句,在有些业务繁忙企业场景下,这种配置可能会对性能产生很大影响,但对于安全性上有很大提高,将来我们需要去权衡我们的业务需求再去调整是否自动提交。
可以通过以下命令进行修改关闭(0是关闭,1是开启):
SET GLOBAL AUTOCOMMIT=0; # 所有新建会话 SET SESSION AUTOCOMMIT=0; # 当前会话 SELECT @@AUTOCOMMIT; # 看设置结果
也可以修改配置文件让其永久生效:
vim /etc/my.cnf [mysqld] AUTOCOMMIT=0
4.3 其他触发隐式commit的情况
4.3.1 用于隐式提交的 SQL 语句
START TRANSACTION SET AUTOCOMMIT = 1
4.3.2 导致提交的非事务语句
DDL语句: (ALTER、CREATE 和 DROP) DCL语句: (GRANT、REVOKE 和 SET PASSWORD) 锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
4.3.3 导致隐式提交的语句
TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE
4.4 事务日志redo
4.4.1 Redo简介
redo,顾名思义“重做日志”,记录的是内存页的变化,是事务日志的一种,也为了提高数据持久化速度的一种特性,可以理解为只要redo落地,就认为数据持久化了,因此可以不需要将数据页落地到磁盘才算数据安全。
4.4.2 Redo的作用
在事务ACID过程中,实现的是“D”持久化的作用。
4.5 事务日志undo
4.5.1 undo简介
undo,顾名思义“回滚日志”,修改之前的内存数据页,是事务日志的一种。当需要回滚的时候,将undo页覆盖到数据内存页。
4.5.2 undo的作用
在事务ACID过程中,实现的是“A”一致性的作用。
4.6 LSN日志版本号
LSN是日志版本号,或者叫日志序列号,存在于数据页,内存页,redo,undo中。
4.6.1 数据库崩溃恢复的过程原理
- 假设执行update命令将A=10改成A=20时突然断电,此时数据库恢复的过程为:
- 将A=10数据页加载到内存页中,假如LSN号原始是LSN=1,此时当前数据页LSN=1,当前的内存页LSN=1
- 发起update操作时,数据页LSN=1,A=10;undo的LSN=1,A=10;内存页LSN=2;redo内存页LSN=2,如果已经存储在磁盘上时,ib_logfile0的LSN=2
- 如果已经commit成功,在redo文件会有commit标记,如果没有提交,就会有rollback标记
- 如果有commit标记时,会读取redo文件,查看LSN=2,发现比数据页中的LSN高,此时数据库就会重演redo的变化操作,并且会直接将变化后的数据刷写到磁盘
- 如果没有commit标记,会读取redo文件,查看LSN=2,发现比数据页中的LSN高,数据库就会重演redo的变化操作,并且会利用undo回滚所有操作
总结:数据库崩溃时,永远是按照先前滚(redo),后回滚顺序(undo)。
4.7 事务中的锁
4.7.1 “锁”的作用
“锁”就是锁定的意思,在事务ACID过程中,“锁”和“隔离级别”一起来实现“I”隔离性的作用。
4.7.2 锁的粒度
- MyIasm:低并发锁—表级锁
- Innodb:高并发锁—行级锁
4.8 事务的隔离级别简介
- READ UNCOMMITTED:允许事务查看其他事务所进行的未提交更改
- READ COMMITTED:允许事务查看其他事务所进行的已提交更改
- REPEATABLE READ:(很重要)是InnoDB 的默认级别,确保每个事务的 SELECT 输出一致
- SERIALIZABLE:将一个事务的结果与其他事务完全隔离
第5章 扩展内容
5.1 线程+内存+存储结构整体图
