第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时突然断电，此时数据库恢复的过程为：

1. 将A=10数据页加载到内存页中，假如LSN号原始是LSN=1，此时当前数据页LSN=1，当前的内存页LSN=1
2. 发起update操作时，数据页LSN=1，A=10；undo的LSN=1，A=10；内存页LSN=2；redo内存页LSN=2，如果已经存储在磁盘上时，ib_logfile0的LSN=2
3. 如果已经commit成功，在redo文件会有commit标记，如果没有提交，就会有rollback标记
4. 如果有commit标记时，会读取redo文件，查看LSN=2，发现比数据页中的LSN高，此时数据库就会重演redo的变化操作，并且会直接将变化后的数据刷写到磁盘
5. 如果没有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 线程+内存+存储结构整体图