【划重点】MySQL技术内幕：InnoDB存储引擎

说明

本文绝大部分内容来源《MySQL技术内幕：InnoDB存储引擎》一书，部分图片来源网络。#我是搬运工#

InnoDB 体系结构

后台线程

InnoDB存储引擎是多线程模型，其后台有多个不同的后台线程，负责处理不同的任务。

Master Thread

Master Thread 主要负责将缓存池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO页的回收。

IO Thread

IO Thread 主要负责 Async IO 请求的回调处理，包含 write、read、insert buffer 和 log IO thread。

Purge Thread

Purge Thread 负责回收已经使用并分配的 undo 页，减轻 Master Thread 的工作。

Page Cleaner Thread

Page Cleaner Thread 作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成，减轻 Master Thread 的工作及对于用户查询线程的阻塞。

内存

缓冲区

一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响；读取数据时，首先将从磁盘读到的数据存放在缓冲池中，下一次读取直接从缓冲池中取。更新数据时，先更显缓冲池的数据，然后通过后台线程定期将有过更新的缓冲数据刷新到磁盘。从而减少磁盘IO的读写。

LRU List、Free List 和 Flush List

数据库缓冲池通过 LRU （Last Recent Used）算法管理，LRU List 用来管理已经读取的页，数据库启动时，LRU List 为空列表，没有任何的页。此时页都存放在 Free List 中，当需要从缓冲池中分页时，首先从 Free List 中查找是否有可用的空闲页，若有空闲页则将该页从 Free 列表中删除并能够放入到 LRU List 中，否则淘汰 LRU List 中末尾的页。在 LRU List 中的页被修改后，称该页为脏页（dirty page）。脏页存储于 Flush List，表示缓冲池中的页与磁盘页不一致，等待被调度刷新。脏页同时存在于 Flush List 与 LRU List 中。

重做日志缓冲 redo buffer cache

InnoDB 将重做日志首先写入 redo buffer cache，之后通过一定频率写入到重做日志（redo logo）中。redo buffer cache 不需要设置太大，重做日志缓冲在一下情况下被刷入到重做日志文件中：
(1) Master Thread 每一秒将重做日志缓冲刷到重做日志文件
(2) 每个事务提交时会将重做日志缓冲刷新到重做日志文件
(3) 当重做日志缓冲池剩余空间小于50%时，重做日志缓冲刷新到重做日志

额外的内存池

InnoDB 对内存的管理是通过一种称为内存堆的方式进行的，对一些数据结构进行内存分配时，需要从额外的内存池中申请，当该区域不够时，会从缓冲池中进行申请。

InnoDB 关键特性

插入缓冲（insert buffer）

Insert Buffer

对于【非聚集索引】的更新或插入操作，不是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入，否则先放入到一个 Insert Buffer 中。再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的merge操作，合并插入操作，提高非聚集索引的插入性能。

Change Buffer

Insert Buffer 的升级，InnoDb 1.0.x 版本开始引入，同样适用对象为非唯一的辅助索引。可以对 DML 操作进行缓冲：insert、delete、update。

两次写（double write）

double write 带给 InnoDB 存储引擎的是数据页的可靠性。

当数据库发生宕机时，可能InnoDB存储引擎正在写入某个页到列表中，而这个页只写了一部分，，比如16KB的页，只写了4KB，之后发生宕机，此时次出现【部分写失效】（页断裂）的情况，InnoDB 通过 double write 解决出现这种情况时造成的数据丢失并且无法恢复的问题。
double write 工作流程：脏页刷新时，先拷贝至内存的 double write buffer，从缓冲区分两次接入磁盘共享表空间红，顺序写，缓冲区中的脏页数据写入实际的各个表空间，离散写。

页断裂数据恢复流程：通过页的 checksum，校验 double write 在磁盘中的数据块，通过 double write 缓冲区数据来修复。

自适应哈希索引（Adaptive Hash Index）

InnoDB 会监控对表上各索引页的查询。如果观察到建议哈希索引可以带来速度的提升，则建立哈希索引，称之为自适应哈希索引（AHI）。AHI 是通过缓冲池的 B+ 树构造来的，因此建立的速度非常快，而且不需要对整张表构建哈希索引，InnoDB 会根据访问频率和模式来自动创建自适应哈希索引，无需人为设置干预。

自适应哈希索引只适用于等值查询，比如 where smsId = 'XXXXXX'，不支持范围查找。

异步 IO（Asynchronous IO）

InnoDB 采用异步IO（AIO）的方式来处理磁盘操作，进而提高对磁盘的操作性能。InnoDB 存储引擎中，read ahead 方式的读取是通过 AIO 完成，脏页的刷新，即磁盘的写入操作也是由 AIO 完成。

刷新临接页（Flush Neighbor Page）

当刷新一个脏页到磁盘时，InnoDB 会检测该页所在区的所有页，如果是脏页，则一起进行刷新。通过 AIO 合并多个 IO 写入，减少磁盘的 IO，但是可能造成将不怎么脏的页的磁盘写入，对于 SSD 磁盘，本身有着较高的 IOPS，则建议关闭该特性，InnoDB 1.2.x 版本提供参数 innodb_flush_neighbors，设置为 0 可关闭该特性。而对于普通磁盘，建议开启。

Checkpoint 技术

Checkpoint 技术的目的是解决以下问题：

缩短数据库的恢复时间
缓冲池不够用时，刷新脏页
重做日志不可用时，刷新脏页

Checkpoint 类型

Sharp Checkpoint：发生在数据库关闭时，将所有脏页刷新到磁盘。
Fuzzy Chckpoint：数据库运行时使用该方式进行页的刷新，刷新部分脏页进磁盘。

InnoDB 中可能发生的 Fuzzy Checkpoint

Master Thread Checkpoint

Master Thread 以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页到磁盘，异步进行，用户查询线程不会阻塞。

FLUSH_LRU_LIST Checkpoint

InnoDB 存储引擎需保证差不多 100 个空闲页可用，空闲也不足时，InnoDB 会将 LRU 列表尾端的页移除，如果尾端页存在脏页，则需要进行 Checkpoint。

Async/Sync Flush Checkpoint

重做日志不可用时进行，强制将一些页刷新回磁盘，从脏页列表中选取。根据不同的状态使用不同的刷新方式（同步或异步）。

Dirty Page too much Checkpoint

脏页数量太多，比如占据缓冲池比例大于 75% 时，强制进行刷新，比例可调。

MySQL 文件

参数文件

告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置。在默认情况下，MySQL 实例会按照一定的顺序在指定的位置进行读取，通过以下命令可以寻找：

mysql --help | grep my.cnf

MySQL 数据库中的参数分类

动态参数：MySQL 运行期间中可以进行实时修改
静态参数：MySQL 运行期间不可修改，只读

日志文件

记录了影响 MySQL 数据库的各种类型活动，常见的日志文件有：

错误日志

对 MySQL 的启动、运行、关闭过程进行记录，可根据错误日志定位问题。不仅记录错误信息，同时也记录一些告警信息或正确的信息。

# 查看日志文件存储路径
mysql> show variables like 'log_error';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| log_error     | /data/mysql_data/data/r002.err |
+---------------+--------------------------------+

慢查询日志

帮助查找存在问题的 SQL 语句，记录执行时间超过某个时间长度的 SQL 语句。

# 查询记录执行时间长度（秒）
mysql> show variables like 'long_query_time' \g;
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
# 慢查询记录开关
mysql> show variables like 'log_slow_queries' \g;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
+------------------+-------+

慢查询日志文件可通过 mysqldumpslow 解析结果并查看。

查询日志

查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。

二进制日志

二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作，不包含只读操作。二进制文件主要有以下几种作用：

恢复：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
复制：通过复制和执行二进制日志使一台远程的 MySQL 数据库与另一台 MySQL 数据库进行实时同步。
审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

套接字文件

在 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式。

# 查看套接字文件地址
mysql> show variables like 'socket';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| socket        | /var/mysql/mysql.sock |
+---------------+-----------------------+

pid 文件

在 MySQL 实例启动时，生成的进程ID会被写入到一个文件中，即 pid 文件。

# 查看 pid 文件
mysql> show variables like 'pid_file';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| pid_file      | /data/mysql_data/data/r002.pid |
+---------------+--------------------------------+

表结构定义文件

MySQL 数据的存储是根据表进行的，每个表都有与之对应的文件，是以 frm 为后缀名的文件，记录了表的表结构定义。

InnoDB 存储引擎文件

InnoDB 存储引擎独有的文件，与InnoDB 存储引擎密切相关，包括表空间文件、重做日志文件。

表空间文件

InnoDB 采用将存储的数据按表空间进行存放的设计。默认配置下有一个初始化大小为 10MB 的 ibdata1 文件，可自动增长。可以通过参数 innodb_data_file_path 对其进行设置。若设置了参数 innodb_file_per_table，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间。独立表空间命名规则：表名.ibd

# 查看是否开启独立表空间存储
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON   |
+-----------------------+-------+

需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲 BITMAP 等信息，其余信息还是存放在默认表空间中。下图显示了 InnoDB 存储引擎对于文件的存储方式：

重做日志文件

默认情况下，InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件，即 InnoDB 存储引擎的重做日志文件（redo log file），记录了对于 InnoDB 存储引擎的事务日志。

当实例或介质存储失败时，例如由于主机断电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到断电前的时刻，一次来保证数据的完整性。InnoDB 存储引擎会逐个循环写日志文件，当前写的日志文件被写满后，切到下一个日志文件，当下一个日志文件也被写满后，循环写前一个日志文件。日志文件数量及大小可配置（innodb_log_files_in_group、innodb_log_file_size）。

关于重做日志文件的大小设置：
(1) 不能设置太大，如果设置得很大，在恢复时可能需要很长的时间
(2) 不能设置过小，可能会导致一个事务的日志需要多次切换重做日志文件，也会导致频繁的发生 async checkpoint，导致性能抖动。

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的顺序写入日志文件：

MySQL 二进制文件

MySQL 二进制文件记录 MySQL 数据库执行的更新操作。包含二进制日志文件和二进制索引文件。

mysql-bin.index
mysql-bin.000001
mysql-bin.000002
mysql-bin.XXXXXX

mysql-bin.000001 即为二进制日志文件，日志文件超过一定大小（根据 max_binlog_size 确定）时生成新的文件，后缀名 +1。binlog 相关的参数有如下：

max_binlog_size    # 单个 binlog 日志文件的最大值
binlog_cache_size  # 事务提交时的二进制日志写入的缓冲区大小
sync_binlog           # 表示每写入多少次缓冲区就同步至磁盘
binlog-do-db       # 表示需要写入哪些库的日志
binlog-ignore-db   # 表示忽略写入哪些库的日志
bin_log_format     # 表示二进制日志的记录格式，包含 STATEMENT、ROW、MIXED

二进制日志记录格式

STATEMENT：MySQL 5.1 之前的存储格式，5.1 版本以后可选格式，记录日志的逻辑 SQL 语句。
ROW：二进制日志不再是简单的 SQL 语句，而是记录表行的更改情况，包含一行数据更改前与更改后列的内容。
MIXED：默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下回使用 ROW 格式，如以下情况：
1）表的存储引擎为 NDB
2）使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数
3）使用了 INSERT DELAY 语句
4）使用了用户定义函数（UDF）
5）使用了临时表

STATEMENT 与 ROW 模式对比

通常情况下 STATEMENT 因为只记录逻辑 SQL 语句，相关 ROW 模式下日志存储大小较小，特别是批量更新情况下，ROW 模式的日志文件远远大于 STATEMENT 模式下的日志文件，在做日志复制时，由于要传输 binlog 文件的内容，STATEMENT 模式的传输要优于 ROW 模式
如果更新的 SQL 语句中存在不确定的函数调用等情况，用 STATEMENT 模式记录的 SQL 语句做同步会导致数据不一致，因此使用场景有所局限。

二进制文件（binlog）与重做日志文件（redo log）

二进制日志记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM以及其他存储引擎的日志。而 InnoDB 存储引擎的重做日志只记录 InnoDB 存储引擎本身的事务日志。
记录的内容不同，二进制日志文件记录的是关于一个事务的具体操作内容，即该日志的逻辑日志。而重做日志文件记录的是关于每个页（Page ）的更改的物理情况。
写入时间不同，二进制日志文件仅在事务提交后进行写入，即只写磁盘一次，不论事务有多大。而在事务进行的过程中，却不断有重做日志条目被写入到重做日志文件中。

MySQL 分区表

InnoDB 逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑的存放在表空间（tablespace），表空间又分为段（segment）、区（extend）、页（page）组成。而表的行数据则存储在页中，一个页存储多个行。

分区

MySQL 数据库在 5.1 版本时添加了对分区的支持。分区功能并不是在存储引擎层完成的，因此不仅 InnoDB 存储引擎支持分区，MyISAM、NDB 等都支持。也并不是所有存储引擎都支持分区，如 CSV、MERGE、FEDORATED 等就不支持。

分区的作用

对一部分 SQL 语句性能带来明显的提高，但是分区主要用于数据库高可用性的管理。

分区类型

RANGE 分区

行数据基于属于一个给定连续区间的列值被放入分区。

mysql> create table test_range (id int) ENGINE = INNODB PARTITION BY RANGE (id)(
    -> PARTITION P0 VALUES LESS THAN (10),
    -> PARTITION P1 VALUES LESS THAN (20));
Query OK, 0 rows affected (0.03 sec)

创建分区后，存储文件的独立表空间将根据分区存储，如下图所示：

-rw-rw----  1 _mysql  _mysql    96K Jan 28 17:05 test_range#P#P0.ibd
-rw-rw----  1 _mysql  _mysql    96K Jan 28 17:05 test_range#P#P1.ibd
-rw-rw----  1 _mysql  _mysql   8.4K Jan 28 17:05 test_range.frm
-rw-rw----  1 _mysql  _mysql    28B Jan 28 17:05 test_range.par

对表添加数据时，会根据指定的列将数据存储到对应的分区存储文件中，如列对应的值不在对应范围内，将写入失败：

mysql> insert into test_range values (30);
ERROR 1526 (HY000): Table has no partition for value 30

LIST 分区

LIST 分区与 RANGE 分区非常相似，只是分区列的值是离散的，而非连续的。如下：

mysql> create table test_list (a INT, b INT) ENGINE = INNODB PARTITION BY LIST (b)(
    -> PARTITION P0 VALUES IN (1, 3 ,5, 7, 9),
    -> PARTITION P1 VALUES IN (0, 2, 4, 6, 8));
Query OK, 0 rows affected (0.03 sec)

同样的，添加数据时，对应的列必须在指定的范围内，否则将写入失败：

mysql> insert into test_list (a, b) values (1, 11);
ERROR 1526 (HY000): Table has no partition for value 11

HASH 分区

HASH 分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一样。用于需要对将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量：

mysql> create table test_hash (a INT, b DATETIME) ENGINE = INNODB
    -> PARTITION BY HASH (YEAR(b))
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.03 sec)

KEY 分区

KEY 分区与 HASH 分区相似，HASH 分区使用用户定义的函数进行分区，KEY 分区使用 MySQL 数据库提供的函数进行分区。

mysql> create table test_key (a INT, b DATETIME) ENGINE = INNODB
    -> PARTITION BY KEY (b)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

COLUMNS 分区

以上四种区分方式均存在一样的分区条件：数据必须是整型（INT）的，如果不是整型，需要将对应的值转化为整型，如 YEAR()，TO_DAYS() 等函数。MySQL 5.5 版本开始支持 COLUMNS 分区，可视为 RANGE 分区和 LIST 分区的一种进化。

可以直接使用非整形的数据进行分区，如所有的整型类型 SMALLINT、BIGINT，日期类型 DATE、DATETIME，字符串类型 CHAR、VARCHAR，相应的 FLOAT、DECIMAL、BLOB、TEXT、TIMESTAMP 不予支持。

分区和分区性能

数据库的应用分为两类：OLTP（在线事务处理）和 OLAP（在线分析处理）。

对于 OLAP 的应用，分区的确可以很好地提高查询性能。体现在扫描表时不需要扫描全表，扫描单个分区时效率最高；同时也依赖于应用的处理以及分区方式，如不合理的分区，将带来巨大的性能下降。比如对主键进行 HASH 分区，查询的时候通过非主键字段匹配查询，则同样是全量数据扫描，但是由于分区的数量较多，会大量增加系统的 IO 调用。

对于 OLTP 的应用，分区并不会明显的降低 B+ 树索引高度，一般的 B+ 树需要 2~3 次的磁盘 IO，分区并不能明显提升写入速度。但是设计不好的分区会带来严重的性能问题。

MySQL 索引

InnoDB 存储引擎支持以下几种常见索引：

B+ 树索引
哈希索引
全文索引

B+ 树

B+ 树的概念在此不做介绍，B+ 树的操作演示地址：https://www.cs.usfca.edu/~gal...

B+ 树索引

MySQL 并不是通过 B+ 树索引直接找到数据行，而是找到数据行所在的页，将页加载到内存，最后查找到行数据。一个数据页包含多行数据。

B+ 树索引包含数据页与索引页。数据页存放完整的行数据，索引页存放键值以及指向数据页的偏移量，而非完整的行记录。

B+ 树索引分类

聚集索引（Clustered Index）

InnoDB 存储引擎是索引组织表，即表中数据按照主键顺序存放，聚集索引就是按照主键构造一颗 B+ 树，同时叶子节点存放表的行记录数据，也将聚集索引的叶子节点称为数据页。简而言之，数据是索引的一部分。

MySQL 通过索引构造数据，所以一张数据表中只能有一个聚集索引。

辅助索引（Secondary Index）

也成为非聚集索引，叶子节点并不包含数据行的全部数据。叶子节点中的索引行中包含一个书签，该书签就是相应行数据的聚集索引键，因此通过非聚集索引查找行数据需要经过两级索引才能查找到具体的数据内容。比如，非主键索引查找行数据，先通过非主键索引查找到主键，再通过主键查找行数据。

哈希索引

MySQL 中的 HASH 索引为自适应的，无需人工干扰，MySQL 内部会针对查询业务自动创建 HASH 索引，以提高业务的查询效率。

HASH 索引仅适用的等值匹配查询，对于范围查找无能为力。

全文索引

InnoDB 1.2.x 版本开始，InnoDB 存储引擎开始支持全文索引，通过倒排索引来实现。因为业务极少使用 MySQL 的全文索引，通常如果需要做全文搜索，可选择 Elasticsearch。

Cardinality 值

Cardinality 值对列创建索引的选择性提供了较好的参考，Cardinality 为一个预估值，非准确值，某一个列的 Cardinality 值代表该列在整张表中不重复值的数量。

忽略业务因素以及数据类型，表中某个列是否适合创建索引，体现在该列所有的值是否相对分散，重复数据越少，相对来说越适合添加索引。因此，当某个列 Cardinality值/表行数约接近 1，代表重复数据越少，为该列建索引的选择性便越高。

InnoDB 存储引擎对于 Cardinality 的更新是非实时的，并且获取到的值为预估值，通过采样统计来获取该值。通常具体业务只需关心该值是否接近于表的行数，以判断某个列是否适合创建索引。

MySQL 锁

Innodb 存储引擎锁类型

行级锁
共享锁（S Lock）：允许事务读一行数据
排他锁（X Lock）：允许事务删除或更新一行数据
表级锁
意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁
意向排他锁（IS Lock）：事务想要获得一张表中某几行的排他锁

InnoDB 存储引擎中锁的兼容性：

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

如下图所示，当 InnoDB 需要做细粒度加锁时，比如对某一行加 X 锁，需要先对该行所在的表、页加 IX 锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。

一致性非锁定读：Consistent Nonlocking Read

InnoDB 存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 UPDATE 或 DELETE 操作，这时读取操作不会因此等待行上锁的释放。相应的，InnoDB 存储引擎会去读取行的一个快照数据。

非锁定读机制极大的提高了数据库的并发性，在 InnoDB 存储引擎的默认设置下，读取不会占用和等待表上的锁。快照数据即当前行记录的历史版本，每行记录可能有多个版本，由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

一致性锁定读：Consistent Locking Read

在某些情况下，用户需要显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。这要求数据库支持加锁语句，即使对于 SELECT 的支付操作。InnoDB 存储引擎对于 SELECT 语句支持两种一致性的锁定读操作:

# 对读取的行记录添加一个 X 锁
SELECT ... FOR UPDATE;

# 对读取的行记录添加一个 S 锁
SELECT ... LOCK IN SHARE MODE;

锁的算法

1）Record Lock：单个行记录上的锁

# 锁定 id = 5 的行记录
SELECT ... FROM ... WHERE id = 5 FOR UPDATE;

2）Gap Lock：间隙锁，锁定一个范围，单不包含记录本身

# 锁定 id < 5 的所有记录
SELECT ... FROM ... WHERE id < 5 FOR UPDATE;

3）Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

# 锁定 id <= 5 的所有记录
SELECT ... FROM ... WHERE id <= 5 FOR UPDATE;

幻像问题：Phantom Problem

Phantom Problem 是指同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能会返回之前不存在的行。比如：

# 表 t 中存在 id 为 1,2,3,4 四条数据，事务隔离级别为  READ-COMMITTED
BEGIN;
SELECT * FROM t WHERE id > 2;    # 此时查询结果有id为 3，4 的记录
# 此时其他线程增加一条数据 id = 5
SELECT * FROM t WHERE id > 2;    # 此时查询结果有id为 3，4，5 的记录

上述查询结果，在一个事务中出现同一个查询返回不同的结果，违反了事务的隔离性，即当前事务能够看到其他事务的结果。

InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，在该事务隔离级别下采用 Next Key Locking 的方式来加锁解决。同时应用也可以通过 InnoDB 存储引擎的 Next Key Locking 机制在应用层面实现唯一性的检查。例如：

SELECT * FROM t WHERE col = xxx LOCK IN SHARE MODE;

锁问题

脏读

脏读指一个事务可以读到另一个事务中未提交的修改数据，违反了数据库的隔离性。

脏读发生的条件是事务隔离级别是 READ UNCOMMITTED，目前大部分数据库都至少设置成 READ COMMITTED。

不可重复读

不可重复读指在一个事务内多次读取同一数据集合，出现了不同的数据结果。

不可重复读发生在事务隔离级别为 READ COMMITTED，事务 A 读取一个结果集，事务 B 同样读取到该结果集并对其进行修改，提交事务，事务 A 再次读取结果集时，两次结果不一致。一般情况下，不可重复的问题是可接受的，因为读取的是已经提交的数据，本身不会带来很大问题。

InnoDB 存储引擎的隔离级别为 READ REPEATABLE 时，采用 Next Key Lock 算法，避免了不可重复读的现象。

丢失更新

一个事务的更新操作结果被另一个事务的更新操作结果所覆盖，从而导致数据的不一致。

数据库层面可以阻止丢失更新问题的发生，但是应用中存在一个逻辑意义的丢失更新问题。例如，多个线程同时读取到某条数据，之后均对数据进行修改再更新库，此时会出现最后一个线程的更新结果覆盖了先执行的更新结果。应用层面可以通过对查询的数据进行加锁，如前文提到的一致性锁定读方式，对需要更新的数据进行加锁，其他线程即会出现阻塞串行等待。

死锁

死锁是指两个或两个以上的事务在执行过程中，因抢夺锁资源而造成的互相等待的现象。

解决死锁的方式：
1）超时
当一个等待时间超过设置的某一阈值时，对该事务进行回滚，InnoDB 中通过参数 innodb_lock_wait_timeout 设置超时时间。

超时处理机制简单，但不判断事务所占权重，比如一个事务更新的行非常多，回滚也需要占用更多的时间，同时与该事务抢占资源的事务可能仅更新少量数据，回滚该事务应当更合理。

2） wait-for graph（等待图）死锁检测
主动检测死锁，判断事务之间的等待状态是否存在闭环。若检测到存在死锁的情况，InnoDB 存储引擎选择回滚 undo 量最小的事务。

锁升级

锁升级指将当前锁的粒度降低。比如数据库把一个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。从而避免锁的开销。

InnoDB 存储引擎根据页进行加锁，并采用位图方式，开销由页的量决定，因此 InnoDB 引擎不会产生锁升级的问题。

MySQL 事务

事务的实现

InndoDB 是事务的存储引擎，其通过 Forece Log at Commit 机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 COMMIT 操作完成才算完成。这里的日志是指重做日志，由两部分组成，即 redo log 和 undo log。

事务的 ACID 特性实现：隔离性，通过锁实现；原子性、一致性、持久性，通过数据库的 redo log 和 undo log 实现。

redo log 与 undo log

redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定的版本，用来帮助事务回滚及 MVCC 的功能。因此两者记录的内容不同，redo 通常是物理日志，记录的是页的物理修改操作。redo log 基本上都是顺序写的，undo 是逻辑日志，根据每行记录进行记录。undo log 是需要进行随机读写的。

redo

重做日志用来实现事务的持久性，即事务 ACID 的 D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是容易丢失的；二是重做日志文件（redo log file），其是持久的。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作。因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略，其参数含义如下：

1 -> 表示事务提交时必须调用一次 fsync
0 -> 表示事务提交是不进行写入重做日志操作，这个操作尽在 master thread 中完成，而 master thread 中每 1 秒进行一次重做日志文件的 fsync 操作
2 -> 表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。

MySQL 默认参数为 1，保证最高的数据可靠性，为 0 或 2 时可以提供更好的事务性能，但是存在数据库宕机时数据丢失风险。

undo

重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要 undo。在对数据库进行修改时，InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo，这样如果执行的事务或语句由于某种原因失败了，又或者是用户主动 ROLLBACK 请求回滚，就可以利用 undo 进行数据回滚到修改之前的样子。

purge

delete 和 update 操作并不直接删除原有的数据，执行 delete 语句时，受影响的数据被标记为逻辑删除，真正删除这行记录的操作在 purge 操作中完成。

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能再事务提交时立即进行处理。而是否可以删除该条记录通过 purge 来判断，若该行记录已不再被任务其他事务引用，那么就可以进行真正的 delete 操作。

group commit

若事务为非只读事务，则每次事务提交时需要进行一次 fsync 操作，以保证重做日志都已经写入磁盘。为了提高磁盘 fsync 的效率，当前数据库提供了 group commit 的操作，即一次 fsync 可以刷新确保多个事务日志被写入文件。对于 InnoDB 存储引擎来说，事务提交时会进行两个阶段的操作：
1）修改内存中事务对应的信息，并且将日志写入重做日志缓冲
2）调用 fsync 确保日志都从重做日志缓冲写入磁盘

步骤 2）相对步骤 1）是一个较慢的过程，当有事务进行步骤 2）时，其他事务可以进行步骤 1）的操作，正在提交的事务完成提交操作后，再次进行步骤 2）时，可以将多个事务的重做日志通过一次 fsync 刷新到磁盘，这样就大大的减少了磁盘的压力，从而提高了数据库的整体性能。

MySQL 备份与恢复

备份分类

根据不同的类型来划分：

Hot Backup（热备）
Cold Backup（冷备）
Warm Backup（温备）

按照备份后文件的内容划分：

逻辑备份
裸文件备份

按照备份数据库的内容划分：

完全备份
增量备份
日志备份

冷备

对于 InnoDB 存储引擎的冷备，只需要备份 MySQL 数据库的 frm 文件，共享表空间文件，独立表空间文件（*.ibd），重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，有利于恢复的操作。

冷备的优点：

备份简单，只需要复制相关文件即可
备份文件易于在不同操作系统，不同 MySQL 版本上进行恢复
恢复相当简单，只需要把文件恢复到指定位置即可
回复速度快，不需要执行任何 SQL 语句，不需要重建索引

冷备的缺点：

InnoDB 存储引擎冷备的文件通常比逻辑文件大很多
冷备也不总是可以轻易的跨平台

逻辑备份

mysqldump

用来完成转存数据库的备份及不同数据库之间的移植，如从 MySQL 低版本数据库升级到 MySQL 高版本数据库，又或者从 MySQL 移植到 Oracle，SQL Server 等。

mysqldump [arguments] > file_name
mysqldump --all-databases > dump.sql
mysqldump --databases db1 db2 db3 > dump.sql

SELECT ... INTO OUTFILE

逻辑备份方法，更准确的说是导出一张表中的数据。

SELECT * INTO OUTFILE '/home/yw/a.txt' FROM test;

逻辑备份的恢复

SOURCE

mysqldump 的恢复操作简单，仅需执行导出的 SQL 语句即可。

source /home/yw/dump.sql

LOAD DATA INFILE

恢复通过 SELECT INTO OUTFILE 导出的数据

LOAD DATA INTO TABLE test IGNORE 1 LINES INFILE '/home/yw/a.txt'

二进制日志备份与恢复

二进制日志非常关键，用户可以通过它完成 point-in-time 的恢复工作，MySQL 的 replication 同样需要二进制日志，在默认情况下并不开启二进制日志，要使用二进制日志必须启用它。InnoDB 存储引擎推荐的二进制日志的服务器配置如下：

[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

在备份二进制日志文件前，可以通过 FLUSH LOGS 命令生成一个新的二进制日志文件，然后备份之前的二进制日志。

恢复二进制日志也非常简单，通过 mysqlbinlog 即可：

mysqlbinlog [options] log_file
mysqlbinlog binlog.0000001 | mysql -uroot -p test

也可以先将二进制文件导出到一个文件，然后通过 source 进行导入：

shell > mysqlbinlog binlog.0000001 > /home/yw/binlog.sql
...
mysql > source /home/yw/binlog.sql

复制的工作原理

复制（replication）是 MySQL 数据库提供的一种高可用高性能的解决方案，replication 的工作原理分为以下 3 个步骤：

主服务器把数据更改记录到二进制日志（binlog）中
从服务器把主服务器的二进制日志复制到自己的中继日志（relay log）中
从服务器重做中继日志中的日志，把更改用到自己的数据库上，以达到最终一致性

复制的工作原理如下图所示：