MySQL 系统结构

MySQL 的存储引擎是插件式的。存储引擎的基于表的，不是基于库的。

MySQL 存储引擎

InnoDB

• 支持事务，主要面向在线事务处理（OLTP）。
• 行锁设计，支持外键，非锁定读，5.5.8 开始默认的存储引擎。
• 数据存放在逻辑表空间，每个表文件存放到各自的 ibd 文件中。
• 通过多版本并发控制（MVCC）获得高并发性。通过 next-key locking 避免幻读。
• 提供了插入缓存（insert buffer），二次写（double write），自适应哈希索引（adaptive hash index），预读（readhead）等高性能和高可用的功能。
• 表中数据的存储，采用聚集索引，表的存储按照主键顺序进行存放。无主键，会每行自动生成一个 6 字节的 ROWID 作为主键。

MyISAM

• 不支持事务，表锁设计，支持全文索引，主要面向 OLAP
• 缓冲池只缓冲索引文件，不缓冲数据文件
• 存储引擎表由 MYD 和 MYI 组成，MYD 用来存放数据文件，MYI 用来存放索引文件。
• 在 MySQL 5.0 版本之前，MyISAM 默认支持的表大小为 4GB，如果需要支持大于 4GB 的 MyISAM 表时，则需要制定 MAX_ROWS 和 AVG_ROW_LENGTH 属性。从 MySQL 5.0 版本开始，MyISAM 默认支持256TB的单表数据，这足够满足一般应用需求。
• 在 MySQL 5.1.23 版本之前，缓存索引的缓冲区最大只能设置为 4GB。在之后的版本中，64 位系统可以支持大于4GB的索引缓冲区。

Memory

• 表中数据存放在内存中，数据库重启数据就丢失。
• 默认使用哈希索引，而不是我们熟悉的 B+ 树索引（也支持 B+ 树）。
• 只支持表锁，并发性能较差，并且不支持 TEXT 和 BLOB 列类型。存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存。
• MySQL 数据库使用 Memory 存储引擎作为临时表来存放查询的中间结果集（intermediate result）。如果中间结果集大于 Memory 存储引擎表的容量设置，又或者中间结果含有 TEXT 或 BLOB 列类型字段，则MySQL 数据库会把其转换到 MyISAM 存储引擎表而存放到磁盘中。之前提到 MyISAM 不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

InnoDB 存储引擎

版本对比

版本	功能
老版本 InnoDB	支持 ACID，行锁设计，MVCC
InnoDB 1.0.x	继承上述版本所有功能，增加了 compress 和 dynamic 页格式
InnoDB 1.1.x	继承上述版本所有功能，增加了 LinuxAIO，多回滚段
InnoDB 1.2.x	继承上述版本所有功能，增加了全文索引支持，在线索引添加

体系架构

InnoDB 存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

• 维护所有进程/线程需要访问的多个内部数据结构。
• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
• 重做日志（redo log）缓冲。
• ……

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态。

后台线程

• Master Thread Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO 页的回收等。
• IO Thread 在 InnoDB 存储引擎中大量使用了 AIO（Async IO）来处理写 IO 请求，这样可以极大提高数据库的性能。而 IO Thread 的工作主要是负责这些 IO 请求的回调（call back）处理。
  • InnoDB1.0 版本之前共有 4 个 IO Thread，分别是 write、read、insert buffer 和 log IO thread。在Linux 平台下， IO Thread 的数量不能进行调 整， 在 Windows 平台下可以通过参数innodb_file_io_threads 来增大 IO Thread。
  • InnoDB 1.0.x 版本开始，read thread 和 write thread 分别增大到了 4 个，并且不再使用innodb_file_io_threads 参数， 而是分别使用 innodb_read_io_threads 和 innodb_write_io_threads 参数进行设置。

mysql> show variables like 'innodb_%io_threads';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

可以通过命令SHOW ENGINE INNODB STATUS来观察 InnoDB 中的IO Thread：

--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
489 OS file reads, 53 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

• Purge Thread 事务被提交后，其所使用的 undolog 可能不再需要，因此需要 PurgeThread 来回收已经使用并分配的 undo页。在 InnoDB 1.1 版本之前，purge 操作仅在 InnoDB 存储引擎的 Master Thread 中完成。而从InnoDB 1.2 版本开始，purge 操作可以独立到单独的线程中进行，以此来减轻 Master Thread 的工作，从而提高 CPU 的使用率以及提升存储引擎的性能。

[mysqld]
innodb_purge_threads=1

mysql> select @@innodb_purge_threads;
+------------------------+
| @@innodb_purge_threads |
+------------------------+
|                      4 |
+------------------------+
1 row in set (0.00 sec)

• Page Cleaner Thread Page Cleaner Thread 是在 InnoDB 1.2.x 版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原 Master Thread 的工作及对于用户查询线程的阻塞，进一步提高 InnoDB 存储引擎的性能。

内存

InnoDB存储引擎中内存的结构

缓冲池

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。

可以配置多个缓冲池实例

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

LRU List、Free List 和 Flush List

Innodb 存储引擎使用 LRU 算法来管理内存页，LRU 列表中加入了 midpoint 位置。默认在 LRU 列表长度的 5/8 位置，由 innodb_old_blocks_pct 参数控制。

mysql> select @@innodb_old_blocks_pct;
+-------------------------+
| @@innodb_old_blocks_pct |
+-------------------------+
|                      37 |
+-------------------------+
1 row in set (0.00 sec)

新读取的页插入到 LRU 列表尾端的 37% 的位置（差不多 3/8 的位置）。在 InnoDB 存储引擎中，把 midpoint 之后的列表称为 old 列表，之前的列表称为 new 列表（活跃的热点数据）。 为了防止只使用一次的数据放到热点数据区，又一个参数 innodb_old_blocks_time 表示需要等待多久 后并且被再次访问才会被加入到热端。

mysql> select @@innodb_old_blocks_time;
+--------------------------+
| @@innodb_old_blocks_time |
+--------------------------+
|                     1000 |
+--------------------------+
1 row in set (0.00 sec)

LRU 列表用来管理已经读取的页，但当数据库刚启动时，LRU 列表是空的，即没有任何的页。这时页都存放在Free 列表中。当需要从缓冲池中分页时，首先从 Free 列表中查找是否有可用的空闲页，若有则将该页从 Free 列表中删除，放入到 LRU 列表中。否则，根据 LRU 算法，淘汰 LRU 列表末尾的页，将该内存空间分配给新的页。 当页从 LRU 列表的 old 部分加入到 new 部分时，称此时发生的操作为 page made young，而因为 innodb_old_blocks_time 的设置而导致页没有从 old 部分移动到 new 部分的操作称为 page not made young。可以通过命令 SHOW ENGINE INNODB STATUS 来观察 LRU 列表及 Free 列表的使用情况和运行状态。

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 111601
Buffer pool size   8192
Free buffers       7694
Database pages     496
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 461, created 35, written 39
0.44 reads/s, 0.02 creates/s, 0.06 writes/s
Buffer pool hit rate 983 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 496, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

• 当前 Buffer pool size 共有 8192 个页，即 8192*16K，总共 128 MB 的缓冲池
• Free buffers 表示当前 Free 列表中页的数量
• Database pages 表示 LRU 列表中页的数量
• Modified db pages 表示脏页数量，需要刷新到磁盘的页数量
• Buffer pool hit rate，表示缓冲池的命中率，这个例子中为98%，说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题

mysql> select POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG from information_schema.innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
             POOL_ID: 0
            HIT_RATE: 1000
    PAGES_MADE_YOUNG: 0
PAGES_NOT_MADE_YOUNG: 0
1 row in set (0.00 sec)

在页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过 CHECKPOINT 机制将脏页刷新回磁盘，而 Flush 列表中的页即为脏页列表。

重做日志缓冲

InnoDB 存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）。InnoDB 存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制，默认为8MB

mysql> select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.00 sec)

什么时候刷盘

• Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件；
• 当重做日志缓冲池剩余空间小于 1/2 时，重做日志缓冲刷新到重做日志文件。

额外的内存池

• MySQL 的 InnoDB 存储引擎使用内存堆（heap）来管理内存。额外的内存池对于 InnoDB 存储引擎非常重要，因为它们用于分配一些数据结构所需的内存，这些数据结构无法通过缓冲池进行分配。例如，缓冲池中的帧缓冲和缓冲控制块都需要从额外的内存池中分配内存。
• 当分配了一个大的 InnoDB 缓冲池时，也应该相应地增加额外的内存池大小。这是因为随着缓冲池的增大，缓冲池中帧缓冲和缓冲控制块所需的内存也会增加。如果额外的内存池大小不足，则可能导致内存不足，并可能影响数据库性能。
• DBA 应该仔细考虑 InnoDB 的内存配置，包括缓冲池大小、额外的内存池大小以及其他相关参数，以获得最佳的数据库性能。

Checkpoint技术

Checkpoint（检查点）技术的目的是解决以下几个问题：

• 缩短数据库的恢复时间；
• 缓冲池不够用时，将脏页刷新到磁盘；
• 重做日志不可用时，刷新脏页。

InnoDB 存储引擎而言，其是通过 LSN（Log Sequence Number）来标记版本的。而 LSN 是 8 字节的数字，其单位是字节。每个页有 LSN，重做日志中也有 LSN，Checkpoint 也有 LSN。

---
LOG
---
Log sequence number 1438259531
Log flushed up to   1438259531
Pages flushed up to 1438259531
Last checkpoint at  1438259522
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second

何时刷新脏页

• 数据库发生关闭时
• Master Thread Checkpoint 主线程每秒或每十秒从缓冲池中刷新一定比例的脏页到磁盘，异步
• FLUSH_LRU_LIST Checkpoint

LRU 需要保证有足有的空闲页，页不够了就要刷盘，Page Cleaner Thread 处理

• Async/Sync Flush Checkpoint

重做日志文件空间不够

• Dirty Page too much Checkpoint

脏页太多，默认 75%，innodb_max_dirty_pages_pct控制

InnoDB 关键特性

InnoDB 存储引擎的关键特性包括：

• 插入缓冲（Insert Buffer）
• 两次写（Double Write）
• 自适应哈希索引（Adaptive Hash Index）
• 异步IO（Async IO）
• 刷新邻接页（Flush Neighbor Page）

上述这些特性为InnoDB存储引擎带来更好的性能以及更高的可靠性。

Insert Buffer

InnoDB 中的 Insert Buffer 不仅存在于 Buffer Pool，而且物理存在于系统表空间（默认也就是ibdata1中）。使用 B+ 树的数据结构。默认最大可以占用到 1/2 的缓冲池内存。

为什么需要 Insert Buffer

• 因为非聚集索引可能是无序的，查找写入是话是离散操作（非顺序写），需要大量磁盘 IO

使用 Insert Buffer 有必要条件

• 索引是辅助索引（secondary index）
• 索引不是唯一（unique）的
• 索引不含降序索引列或主键包含降序索引列

如果是主键索引，大概率是有序的，直接顺序写。 如果是唯一索引，插入时要查询之前的数据，判断插入的数据是不是唯一的，查找浪费性能（离散度）。Insert Buffer 不检查数据的唯一性。 降序索引会维护两个索引，一个正序索引，一个倒序索引。正序索引使用B+树等数据结构来存储索引数据，而倒序索引则使用B+树的变种或反转列表等数据结构来存储索引数据。Insert Buffer 只支持正序索引 https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html (opens new window) https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html#faq-innodb-change-buffer-operations (opens new window)

Change Buffer

InnoDB 从1.0.x 版本开始引入了 Change Buffer，可将其视为 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 DML 操作 —— INSERT、DELETE、UPDATE 都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge buffer。 可以通过 innodb_change_buffering 变量来控制缓冲哪些操作

• all：默认值：缓冲区插入、删除标记操作和清除。
• none：不要缓冲任何操作。
• inserts：缓冲区插入操作。
• deletes：缓冲区删除标记操作。
• changes：缓冲插入和删除标记操作。
• purges：缓冲在后台发生的物理删除操作。

innodb_change_buffer_max_size 值默认为25，表示最多使用 1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为50

mysql> select @@innodb_change_buffer_max_size;
+---------------------------------+
| @@innodb_change_buffer_max_size |
+---------------------------------+
|                              25 |
+---------------------------------+
1 row in set (0.00 sec)

Merge Insert Buffer

将 Insert Buffer 中的数据合并（merge）到真正的辅助索引中

何时进行 merge

• 辅助索引页被读取到缓冲池时；
• Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时；
• Master Thread。

两次写

由于 MySQL 默认页大小是 16k，文件系统的页缓存可能是 4k，需要多次写入，就可能只写了一半数据，然后就发生了宕机，这种情况被称为写失效。 如果发生写失效，Redo log 并不能恢复数据页，因为 Redo log 记录的是对页的物理操作（偏移量 678 的位置，写 'aaa' 记录）。也就是说需要一个页的副本，当发生写失效时，通过副本来还原页。 doublewrite 由两部分组成，一部分是内存中的 doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的 128 个页，即 2 个区（extent），大小同样为2MB。

1. 在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为 doublewrite 页是连续的，因此这个过程是顺序写的，开销并不是很大。
2. 在完成 doublewrite 页的写入后，再将 doublewrite buffer 中的页写入各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的 doublewrite 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

自适应哈希索引

InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI 是通过缓冲池的 B+ 树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。 AHI 有一个要求，即对这个页的连续访问模式必须是一样的。例如对于（a，b）这样的联合索引页，其访问模式可以是以下情况：

• WHERE a=xxx
• WHERE a=xxx and b=xxx

访问模式一样指的是查询的条件一样，若交替进行上述两种查询，那么InonDB存储引擎不会对该页构造 AHI。此外 AHI 还有如下的要求：

1. 以该模式访问了 100 次
2. 页通过该模式访问了 N 次，其中 N= 页中记录 * 1/16；即 访问次数 > 页记录数 1/16

异步 IO

为了提高磁盘操作性能，InnoDB 存储引擎采用异步IO（Asynchronous IO，AIO）的方式来处理磁盘操作。 AIO 的另一个优势是可以进行 IO Merge 操作，也就是将多个IO合并为1个IO，这样可以提高IOPS的性能。 例如：用户需要访问页的（space，page_no）为：（8，6）、（8，7），（8，8）每个页的大小为 16KB，那么同步IO 需要进行 3 次 IO 操作。而 AIO 会判断到这三个页是连续的（显然可以通过（space，page_no）得知）。因此 AIO 底层会发送一个 IO 请求，从（8，6）开始，读取 48KB 的页。

刷新邻接页

InnoDB 存储引擎还提供了 Flush Neighbor Page（刷新邻接页）的特性。 其工作原理为：当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作，故该工作机制在传统机械磁盘下有着显著的优势。 InnoDB 存储引擎从1.2.x 版本开始提供了参数 innodb_flush_neighbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着超高 IOPS 性能的磁盘，则建议将该参数设置为0，即关闭此特性。

文件

参数文件

当 MySQL 实例启动时，数据库会先去读一个配置参数文件（my.conf），用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。简单地说，可以把数据库参数看成一个键/值（key/value）对。 之前已经介绍了一个对于 InnoDB 存储引擎很重要的参数 innodb_buffer_pool_size。如我们将这个参数设置为 1G，即 innodb_buffer_pool_size=1G。这里的“键”是 innodb_buffer_pool_size，“值”是 1G，这就是键值对。可以通过命令 SHOW VARIABLES 查看数据库中的所有参数，也可以通过 LIKE 来过滤参数名。 若想在数据库实例下一次启动时该参数还是保留为当前修改的值，那么用户必须去修改参数文件。

日志文件

错误日志

错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录。MySQL DBA 在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。用户可以通过命令 SHOW VARIABLES LIKE'log_error' 来定位该文件。DBA 用的，业务开发用不上。

查询日志

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

慢查询日志

• slow_query_log：是否开启慢日志记录，默认 0（不开启记录）。
• slow_query_log_file：慢查询日志文件位置。
• long_query_time ：慢查询时间阈值，默认 10s，大于 10s 的查询才算（可精确到微秒）。
• log_queries_not_using_indexes：记录没有使用索引的查询 SQL，默认 0。
• log_throttle_queries_not_using_indexes：每分钟记录多少条未使用索引的查询 SQL，默认 0 无限制。如果开启，建议进行数量限制，防止日志不断增大。

二进制日志

二进制日志（binary log 简称 bin log）记录了对 MySQL 数据库执行更改的所有操作，还包括了执行数据库更改操作的时间等其他额外信息。 总的来说，二进制日志主要有以下几种作用。

• 恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL数据库（一般称为 slave 或 standby ）与一台 MySQL 数据库（一般称为 master 或 primary ）进行实时同步。
• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动（为什么默认关闭？1. 恢复可以用 redo；2. 没复制啊，什么时候复制什么时候开启 3. 我自己用，公司没审计。）。开启二进制日志会使性能下降1%。

通过配置参数 bin-log 来指定二进制日志文件名并开启日志记录。并且要给服务器标识设置为一个唯一值，以便在主从复制时进行区分。

[mysqld]
log-bin=myBinLog
server-id=1

-a----         2023/5/26     17:26       79691776 ibdata1
-a----         2023/5/26     17:26       12582912 ibtmp1
-a----         2023/5/26     17:26            865 ib_buffer_pool
-a----         2023/5/26     17:26       50331648 ib_logfile0
-a----         2022/3/18     20:03       50331648 ib_logfile1
-a----         2023/5/26     17:26            154 myBinLog.000001
-a----         2023/5/26     17:26             18 myBinLog.index
-a----         2023/5/26     17:18           2729 thinkbook16p-slow.log
-a----         2023/5/26     17:26           7662 thinkbook16p.err
-a----         2023/5/26     17:26              5 thinkbook16p.pid

• max_binlog_size：单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到 .index 文件。默认大小 1G。
• binlog_cache_size：当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交的二进制日志会被记录到一个缓存中去，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由 binlog_cache_size 决定，默认大小为 32K。
  • 此外，binlog_cache_size 是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。
  • 当一个事务的记录大于设定的 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过 SHOW GLOBAL STATUS 命令查看 binlog_cache_use（使用缓存次数）、binlog_cache_disk_use（使用临时文件次数） 的状态，可以判断当前binlog_cache_size 的设置是否合适。

mysql> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 1     |
+-----------------------+-------+
2 rows in set (0.00 sec)

• sync_binlog：参数 sync_binlog=[N] 表示每写缓冲多少次就同步到磁盘。
  • 默认值 1，采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。
  • 设置其他数值，提供性能。可能事务提交前写二进制数据，事务提交时宕机，数据不一致，innodb_support_xa 设为1来解决这个问题。
• binlog-do-db：表示需要写入哪些库的日志。默认为空，表示需要同步所有库的日志到二进制日志。
• binlog-ignore-db：表示需要忽略哪些库的日志。默认为空，表示需要同步所有库的日志到二进制日志。
• log-slave-update：从自己的主服务器取得二进制日志，然后将获取的二进制日志文件写入自己的二进制文件中去。
• binlog_format：该参数十分重要，它影响了记录二进制日志的格式。
  • STATEMENT：二进制日志文件记录的是日志的逻辑SQL语句。
  • ROW：二进制日志记录的不再是简单的 SQL 语句了，而是记录表的行更改情况，物理Standby。如果设置了 binlog_format 为 ROW，可以将 InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性（主从复制）。
  • MIXED：在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式：
    • 表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。
    • 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数。
    • 使用了 INSERT DELAY 语句。
    • 使用了用户定义函数（UDF）。
    • 使用了临时表（temporary table）。

套接字文件

在 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可由参数 socket 控制。一般在 /tmp 目录下，名为 mysql.sock

pid 文件

当 MySQL 实例启动时，会将自己的进程 ID 写入一个文件中——该文件即为 pid 文件。该文件可由参数 pid_file 控制，默认位于数据库目录下，文件名为主机名.pid

表结构定义文件

因为 MySQL 插件式存储引擎的体系结构的关系，MySQL 数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。

-rw-r--r-- 1 starry 197121    61 May 26 18:05 db.opt
-rw-r--r-- 1 starry 197121  8556 May 26 18:06 t_test.frm
-rw-r--r-- 1 starry 197121 98304 May 26 18:06 t_test.ibd

InnoDB 存储引擎文件

InnoDB 采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为 12MB（自动增长），名为 ibdata1 的文件，该文件就是默认的表空间文件（tablespace file）。用户可以通过参数 innodb_data_file_path 对其进行设置成两个文件。若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。 设置 innodb_data_file_path 参数后，所有基于 InnoDB 存储引擎的表的数据都会记录到该共享表空间中。 若设置了参数 innodb_file_per_table = ON，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中。

重做日志文件

在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件，即重做日志文件（redo log file），当实例或介质失败（media failure）时，重做日志文件就能派上用场，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。 每个 InnoDB 存储引擎至少有1个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎先写重做日志文件 1，当达到文件的最后时，会切换至重做日志文件 2，再当重做日志文件 2也被写满时，会再切换到重做日志文件 1中。

下列参数影响着重做日志文件的属性：

• innodb_log_file_size：指定每个重做日志文件的大小。在 InnoDB1.2.x 版本之前，重做日志文件总不得大于等于 4GB，而 1.2.x 版本将该限制扩大为了 512GB。
• innodb_log_files_in_group：指定了日志文件组中重做日志文件的数量，默认为2。
• innodb_mirrored_log_groups：指定了日志镜像文件组的数量，默认为1，表示只有一个日志文件组，没有镜像。若磁盘本身已经做了高可用的方案，如磁盘阵列，那么可以不开启重做日志镜像的功能。
• innodb_log_group_home_dir：指定了日志文件组所在路径。

重做日志文件的大小设置对于 InnoDB 存储引擎的性能有着非常大的影响。

• 一方面重做日志文件不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间；
• 另一方面又不能设置得太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。此外，重做日志文件太小会导致频繁地发生 async checkpoint，导致性能的抖动。

既然同样是记录事务日志，和之前介绍的二进制日志有什么区别？

• 首先，二进制日志会记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM、Heap 等其他存储引擎的日志；而 InnoDB 存储引擎的重做日志只记录有关该存储引擎本身的事务日志。
• 其次，记录的内容不同，无论二进制日志文件记录的格式为 STATEMENT 还是 ROW，又或者是 MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志；而 InnoDB 存储引擎的重做日志文件记录的是关于每个页（Page）的更改的物理情况。
• 此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大；而在事务进行的过程中，却不断有重做日志条目（redo entry）被写入到重做日志文件中。

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地写入日志文件。 重做日志缓冲往磁盘写入时，是按 512 个字节，也就是一个磁盘扇区的大小进行写入。因为扇区是写入的最小单位(原子性)，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。 何时写入？

• 主线程定每秒将重做日志缓冲写入重做日志中，不论事务是否提交。
• innodb_flush_log_at_trx_commit 参数控制
  • 0，事务提交时，不写入磁盘
  • 1，事务提交时，写入磁盘（文件系统缓冲），并且有 fsync 的调用
  • 2，事务提交时，写入磁盘（文件系统缓冲），不调用 fsync（让系统自动刷盘）

表

InnoDB 逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block）。

表空间

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。 如果启用了 innodb_file_per_table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。这同时也说明了另一个问题：即使在启用了参数 innodb_file_per_table 之后，共享表空间还是会不断地增加其大小。 InnoDB 存储引擎不会在执行 rollback 时去收缩这个表空间。虽然 InnoDB 不会回收这些空间，但是会自动判断这些 undo 信息是否还需要，如果不需要，则会将这些空间标记为可用空间，供下次 undo 使用。

段

常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了 InnoDB 存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为 B+ 树的叶子节点（Leaf node segment），索引段即为 B+ 树的非索引节点（Non-leaf node segment）。回滚段较为特殊，将会在后面的章节进行单独的介绍。在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。

区

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4～5 个区。在默认情况下，InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64个连续的页。

页

同大多数数据库一样，InnoDB 有页（Page）的概念（也可以称为块），页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中，默认每个页的大小为 16KB。 常见页类型：

• 数据页（B-tree Node）
• undo页（undo Log Page）
• 系统页（System Page）
• 事务数据页（Transaction system Page）
• 插入缓冲位图页（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed BLOB Page）
• 压缩的二进制大对象页（compressed BLOB Page）

行

InnoDB 存储引擎是面向列的（row-oriented），也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200 行的记录，即 7992 行记录。

InnoDB 行记录格式

InnoDB 1.0.x 版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据，Redundant 格式是为兼容之前版本而保留的。

mysql> show table status like 't_test%'\G;
*************************** 1. row ***************************
           Name: t_test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-05-26 18:06:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

Compact 行记录格式

Redundant 行记录格式

行溢出数据

InnoDB 存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为 BLOB、LOB 这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB 可以不将数据放在溢出页面，而且即便是 VARCHAR 列数据类型，依然有可能被存放为行溢出数据。 InnoDB 存储引擎并不支持 65535 长度的 VARCHAR。这是因为还有别的开销，通过实际测试发现能存放 VARCHAR类型的最大长度为 65532 字节（不同数据编码最大长度不一样）。

MySQL 官方手册中定义的 65535 长度是指所有 VARCHAR 列的长度总和，如果列的长度总和超出这个长度，依然无法创建。

InnoDB 存储引擎的数据都是存放在页类型为B-tree node 中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB 页中。

Compressed 和 Dynamic 行记录格式

以前支持的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。Barracuda 文件格式下拥有两种新的行记录格式：Compressed 和 Dynamic。 新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，在数据页中只存放 20 个字节的指针，实际的数据都存放在 Off Page 中，而之前的 Compact 和 Redundant 两种格式会存放768个前缀字节。 Compressed 行记录格式的另一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

InnoDB 数据页结构

• File Header（文件头）
• Page Header（页头）
• Infimun和Supremum Records（虚拟行记录，页中主键最小/最大值）
• User Records（用户记录，即行记录）
• Free Space（空闲空间）
• Page Directory（页目录）
• File Trailer（文件结尾信息）

索引

B+ 树

可以看出，所有记录都在叶子节点上，并且是顺序存放的，如果用户从最左边的叶子节点开始顺序遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。 B+ 索引在数据库中有一个特点是高扇出性，因此在数据库中，B+ 树的高度一般都在 2～4 层，这也就是说查找某一键值的行记录时最多只需要 2 到 4 次 IO。当前一般的机械磁盘每秒至少可以做 100 次 IO，2～4 次的 IO 意味着查询时间只需 0.02～0.04 秒。 数据库中的B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary index，但是不管是聚集还是辅助的索引，其内部都是 B+ 树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

B+ 树索引

聚集索引

聚集索引（clustered index）就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同 B+ 树数据结构一样，每个数据页都通过一个双向链表来进行链接。 由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。 聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

辅助索引

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（主键的值）（bookmark）。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

索引管理

CREATE TABLE t(
a INT NOT NULL,
b VARCHAR(8000),
c INT NOT NULL,
PRIMARY KEY(a),
KEY idx_c(c),
KEY idx_a_c
)ENGINE=INNODB;

INSERT INTO t SELECT 1,REPEAT('a',7000),-1;
INSERT INTO t SELECT 2,REPEAT('a',7000),-2;
INSERT INTO t SELECT 3,REPEAT('a',7000),-3;
INSERT INTO t SELECT 4,REPEAT('a',7000),-4;

SHOW INDEX FROM t;

Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment  
--------------|------------|------------|--------------|-------------|-----------|-------------|----------|--------|------|------------|---------|----------------
t             | 0          | PRIMARY    | 1            | a           | A         | 4           |          |        | ""   | BTREE      | ""      | ""             
t             | 1          | idx_c      | 1            | c           | A         | 4           |          |        | ""   | BTREE      | ""      | ""             
t             | 1          | idx_a_c    | 1            | a           | A         | 4           |          |        | ""   | BTREE      | ""      | ""             
t             | 1          | idx_a_c    | 2            | c           | A         | 4           |          |        | ""   | BTREE      | ""      | ""

• Table：索引所在的表名。
• Non_unique：非唯一的索引，可以看到 primary key 是 0，因为必须是唯一的。
• Key_name：索引的名字，用户可以通过这个名字来执行 DROP INDEX。
• Seq_in_index：索引中该列的位置，如果看联合索引 idx_a_c 就比较直观了。
• Column_name：索引列的名称。
• Collation：列以什么方式存储在索引中。可以是 A 或 NULL。B+树索引总是A，即排序的。如果使用了 Heap 存储引擎，并且建立了 Hash 索引，这里就会显示 NULL 了。因为 Hash 根据 Hash 桶存放索引数据，而不是对数据进行排序。
• Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 表的行数应尽可能接近 1，如果非常小，那么用户需要考虑是否可以删除此索引。
• Sub_part：是否是列的部分被索引。如果索引整个列，则该字段为 NULL。
• Packed：关键字如何被压缩。如果没有被压缩，则为 NULL。
• Null：是否索引的列含有 NULL 值。
• Index_type：索引的类型。InnoDB 存储引擎只支持 B+ 树索引，所以这里显示的都是 BTREE。
• Comment：注释。

Fast Index Creation

MySQL 5.5版本之前（不包括5.5）存在的一个普遍被人诟病的问题是 MySQL 数据库对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为：

• 首先创建一张新的临时表，表结构为通过命令 ALTER TABLE 新定义的结构。
• 然后把原表中数据导入到临时表。
• 接着删除原表。
• 最后把临时表重名为原来的表名。

可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。 对于辅助索引的创建，InnoDB 存储引擎会对创建索引的表加上一个 S 锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB 存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除 MySQL 数据库内部视图上对该表的索引定义即可。 由于 FIC 在索引的创建的过程中对表加上了 S 锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。

Online DDL

虽然 FIC 可以让 InnoDB 存储引擎避免创建临时表，从而提高索引创建的效率。但正如前面小节所说的，索引创建时会阻塞表上的 DML 操作。OSC 虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL 5.6 版本开始支持 Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作，这极大地提高了 MySQL 数据库在生产环境中的可用性。 此外，不仅是辅助索引，以下这几类 DDL 操作都可以通过“在线”的方式进行操作：

• 辅助索引的创建与删除
• 改变自增长值
• 添加或删除外键约束
• 列的重命名

ALTER　TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name,...)[index_option]...
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM 指定了创建或删除索引的算法

• COPY 表示按照MySQL 5.1版本之前的工作模式，即创建临时表的方式。
• INPLACE 表示索引创建或删除操作不需要创建临时表。
• DEFAULT 表示根据参数 old_alter_table 来判断是通过 INPLACE 还是 COPY 的算法，该参数的默认值为OFF，表示采用 INPLACE 的方式。

Variable_name   | Value
-------------- | -----
old_alter_table | OFF

LOCK 部分为索引创建或删除时对表添加锁的情况，可有的选择为：

• NONE 执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。
• SHARE 这和之前的 FIC 类似，执行索引创建或删除操作时，对目标表加上一个 S 锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持 SHARE 模式，会返回一个错误信息。
• EXCLUSIVE 在 EXCLUSIVE 模式下，执行索引创建或删除操作时，对目标表加上一个 X 锁。读写事务都不能进行，因此会阻塞所有的线程，这和 COPY 方式运行得到的状态类似，但是不需要像 COPY 方式那样创建一张临时表。
• DEFAULT DEFAULT 模式首先会判断当前操作是否可以使用 NONE 模式，若不能，则判断是否可以使用 SHARE 模式，最后判断是否可以使用 EXCLUSIVE 模式。也就是说 DEFAULT 会通过判断事务的最大并发性来判断执行 DDL 的模式。

InnoDB 存储引擎实现 Online DDL 的原理是在执行创建或者删除操作的同时，将 INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数 innodb_online_alter_log_max_size 控制，默认的大小为 128MB。若用户更新的表比较大，并且在创建过程中伴有大量的写事务，如遇到 innodb_online_alter_log_max_size 的空间不能存放日志时，会抛出错误。

Cardinality 值

怎样查看索引是否是高选择性的呢？可以通过 SHOW INDEX 结果中的列 Cardinality 来观察。Cardinality 值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality 是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加 B+ 树索引是非常有必要的。 因为 MySQL 数据库中有各种不同的存储引擎，而每种存储引擎对于 B+ 树索引的实现又各不相同，所以对 Cardinality 的统计是放在存储引擎层进行的。

InnoDB存储引擎内部对更新 Cardinality 信息的策略为：

• 表中1/16的数据已发生过变化。
• stat_modified_counter＞2 000 000 000。

Cardinality 如何计算？ 默认InnoDB存储引擎对8个叶子节点（Leaf　Page）进行采用。采样的过程如下：

• 取得 B+ 树索引中叶子节点的数量，记为 A。
• 随机取得 B+ 树索引中的 8 个叶子节点。统计每个页不同记录的个数，即为 P1，P2，…，P8。
• 根据采样信息给出 Cardinality 的预估值：Cardinality =（P1+P2+…+P8）* A/8。 random8_row_count * leaf_page_number / 8

预估的值，不精确，如果偏差太大，可以使用 ANALYZE TABLE t 重新收集和统计数据。

B+ 树索引的使用

联合索引

联合索引也是一棵 B+ 树，不同的是联合索引的键值的数量不是 1，而是大于等于 2。

• 联合索引可以只使用最左边的列进行匹配，还可以根据所有（顺序）索引列进行匹配
• 已经对第二个键值进行了排序处理

覆盖索引

InnoDB 存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。

对于统计操作 count(*) 而言，InnoDB 存储引擎并不会选择通过查询聚集索引来进行统计。辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。 在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列 b 中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择。select count(*) from t where b > 10 and b < 1000; 这里只根据列 b 进行条件查询，一般情况下是不能进行该联合索引的，但是这句 SQL 查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引。

Multi-Range Read

MySQL5.6版本开始支持 Multi-Range Read（MRR）优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升。Multi-Range Read 优化可适用于range，ref，eq_ref类型的查询。 MRR优化有以下几个好处：

• MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
• 减少缓冲池中页被替换的次数。
• 批量处理对键值的查询操作。

对于 InnoDB 和 MyISAM 存储引擎的范围查询和 JOIN 查询操作，MRR 的工作方式如下：

• 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
• 将缓存中的键值根据 RowID 进行排序。
• 根据 RowID 的排序顺序来访问实际的数据文件。

此外，若 InnoDB 存储引擎或者 MyISAM 存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降为最低。

Index Condition Pushdown

和 Multi-Range Read一样，Index Condition Pushdown 同样是 MySQL 5.6 开始支持的一种根据索引进行查询的优化方式。之前的 MySQL 数据库版本不支持 Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据 WHERE 条件来过滤记录。在支持 Index Condition Pushdown 后，MySQL 数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 SQL 层对记录的索取（fetch），从而提高数据库的整体性能。

假设某张表有联合索引 (zip_code，last_name，firset_name)，并且查询语句如下

SELECT*FROM people
WHERE zipcode='95054'
AND lastname LIKE'%etrunia%'
AND address LIKE'%Main Street%';

对于上述语句，MySQL 数据库可以通过索引来定位 zipcode 等于 95054 的记录，但是索引对 WHERE 条件的 lastname LIKE'%etrunia%' AND address LIKE'%Main Street%' 没有任何帮助。

• 若不支持 Index Condition Pushdown 优化，则数据库需要先通过索引取出所有 zipcode 等于 95054 的记录，然后再过滤 WHERE 之后的两个条件。
• 若支持 Index Condition Pushdown 优化，则在索引取出时，就会进行 WHERE 条件的过滤，然后再去获取记录。这将极大地提高查询的效率。当然，WHERE 可以过滤的条件是要该索引可以覆盖到的范围。

哈希算法

InnoDB 存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的 Page 页都有一个 chain 指针，它指向相同哈希函数值的页。 而对于除法散列，m 的取值为略大于 2 倍的缓冲池页数量的质数。例如：当前参数 innodb_buffer_pool_size 的大小为10M，则共有 640 个 16KB 的页。对于缓冲池页内存的哈希表来说，需要分配 640×2=1280 个槽，但是由于 1280 不是质数，需要取比 1280 略大的一个质数，应该是 1399，所以在启动时会分配 1399 个槽的哈希表，用来哈希查询所在缓冲池中的页。 那么 InnoDB 存储引擎的缓冲池对于其中的页是怎么进行查找的呢？上面只是给出了一般的算法，怎么将要查找的页转换成自然数呢？ 其实也很简单，InnoDB 存储引擎的表空间都有一个 space_id，用户所要查询的应该是某个表空间的某个连续 16KB 的页，即偏移量 offset。InnoDB 存储引擎将 space_id 左移20位，然后加上这个 space_id 和 offset，即关键字 K = space_id<<20 + space_id + offset，然后通过除法散列 k % m(1399) 到各个槽中去。

锁

InnoDB 存储引擎会在行级别上对表数据上锁，这固然不错。不过 InnoD B存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的 LRU 列表，删除、添加、移动 LRU 列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

lock 与 latch

在数据库中，lock 与 latch 都可以被称为“锁”，但是两者有着截然不同的含义。

• latch 一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在 InnoDB 存储引擎中，latch 又可以分为 mutex（互斥量）和 rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
• lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放（不同事务隔离级别释放的时间可能不同）。此外，lock，正如在大多数数据库中一样，是有死锁机制的。

InnoDB 存储引擎中的锁

锁的类型

InnoDB 存储引擎实现了如下两种标准的行级锁：

• 共享锁（S Lock），允许事务读一行数据。
• 排他锁（X Lock），允许事务删除或更新一行数据。

X 锁与任何的锁都不兼容，而 S 锁仅和 S 锁兼容。需要特别注意的是，S 和 X 锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。 此外，InnoDB 存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。 InnoDB 存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

• 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
• 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

从 InnoDB1.0 开始，在 INFORMATION_SCHEMA 架构下添加了表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。

一致性非锁定读

一致性的非锁定读（consistent nonlocking read）是指 InnoDB 存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。之所以称其为非锁定读，因为不需要等待访问的行上 X 锁的释放。快照数据是指该行的之前版本的数据，该实现是通过 undo 段来完成。而 undo 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。 一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。 在事务隔离级别 READ COMMITTED 和 REPEATABLE READ（InnoDB存储引擎的默认事务隔离级别）下，InnoD B存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。

• 在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
• 而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

在默认配置下，即事务的隔离级别为 REPEATABLE READ 模式下，InnoDB 存储引擎的 SELECT 操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于 SELECT 的只读操作。 InnoDB 存储引擎对于 SELECT 语句支持两种一致性的锁定读（locking read）操作：

• SELECT…FOR UPDATE：对读取的行记录加 X 锁。
• SELECT…LOCK IN SHARE MODE：对读取的行记录加 S 锁。

自增长与锁

innodb_autoinc_lock_mode 自增锁模式：

• 0：表锁，自增 id 数据插入 SQL 执行完释放，不等事务结束。
• 1：如果插入数量可知，使用互斥量，插入数量不可知（insert ... select ...）使用 0 的方式。
• 2：使用互斥量，主从复制需要使用 row 格式。性能最高。

innodb_autoinc_lock_mode=1是 MySQL 8.0.3 之前的默认设置。 从 MySQL 8.0.3 开始，innodb_autoinc_lock_mode=2 是默认的，这反映了从基于语句的复制到基于行的复制作为默认复制类型的变化。

锁的算法

InnoDB 存储引擎有 3 种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock∶Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。 Next-Key Lock 是结合了 Gap Lock 和Record Lock 的一种锁定算法，在 Next-Key Lock 算法下，InnoDB 对于行的查询都是采用这种锁定算法。例如一个索引有 10，11，13 和 20 这四个值，那么该索引可能被 Next-Key Locking 的区间为：

• (-∞,10]
• (10,11]
• (11,13]
• (13，20]
• (20,+∞)

采用 Next-Key Lock 的锁定技术称为 Next-Key Locking。其设计的目的是为了支持一些不需要使用幻读和不可重复读的场景。当查询的索引含有唯一属性时，InnoD B存储引擎会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

CREATE TABLE z(a INT,b INT,PRIMARY KEY(a),KEY(b));
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;

如果此时执行 select * from z where a = 5 for update 只锁定第三行记录，添加 Record Lock。因为 a是唯一索引。 SELECT*FROM z WHERE b=3 FOR UPDATE 并且由于有两个索引，其需要分别进行锁定。对于聚集索引，其仅对列 a 等于5的索引加上 Record Lock。而对于辅助索引，其加上的是 Next-Key Lock，锁定的范围是(1，3]，特别需要注意的是，InnoDB 存储引擎还会对辅助索引下一个键值加上 gap lock，即还有一个辅助索引范围为(3，6)的锁。即锁定了 (1, 3] 和 (3, 6)。

对于唯一性约束和外键约束，MySQL 必须使用 GAP 锁来防止幻读问题。除此之外，在 RC 隔离级别下，MySQL 仅使用 Record Lock 进行锁定。

锁的问题

脏读

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。脏读现象在生产环境中并不常发生，从上面的例子中就可以发现，脏读发生的条件是需要事务的隔离级别为 READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成 READ COMMITTED。InnoDB 存储引擎默认的事务隔离级别为 READ REPEATABLE。

幻读

同一个事物执行两次相同的查询，第一次查询的结果数量与第二次查询的结果数量不一致。幻读现象只存在于 Read Commited 事物隔离级别下。一般来说，幻读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商（如Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为 READ COMMITTED，在这种隔离级别下允许幻读的现象。在 InnoDB 存储引擎中，通过使用 Next-Key Lock 算法来避免幻读的问题。

不可重复读

同一个事物执行两次相同的查询，第一次查询的结果的值与第二次查询的结果的值不一致。不可重复读问题只存在于 Read Commited 事物隔离级别下。一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商（如Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为 READ COMMITTED，在这种隔离级别下允许不可重复读的现象。在InnoDB 存储引擎中，通过使用 Next-Key Lock 算法来避免不可重复读的问题。

死锁

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行。 在 InnoDB 存储引擎中， 参数 innodb_lock_wait_timeout 用来控制等待的时间（默认是 50 秒）， innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是 OFF，代表不回滚）。 死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。除了超时机制，当前数据库还都普遍采用 wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。wait-for graph 要求数据库保存以下两种信息：

• 锁的信息链表
• 事务等待链表

事务

ACID 是以下 4 个词的缩写：

• 原子性（atomicity）事务可由一条非常简单的 SQL 语句组成，也可以由一组复杂的 SQL 语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别与文件系统的重要特征之一。
• 一致性（consistency）一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束（主键，外键，唯一约束......）没有被破坏。
• 隔离性（isolation）事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。
• 持久性（durability） 事务一旦提交，其结果就是永久性的。MySQL 的持久存储是基于磁盘的。

redo log 称为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。锁用来保证事物的隔离性。

事务的实现

Redo Log

redo log 称为重做日志，用来保证事务的原子性和持久性。 其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。 InnoDB 是事务的存储引擎，其通过 Force Log at Commit 机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 COMMIT 操作完成才算完成。这里的日志是指重做日志，在 InnoDB 存储引擎中，由两部分组成，即 redo log 和 undo log 。redo log 用来保证事务的持久性，undo log 用来帮助事务回滚及 MVCC 的功能。redo log 基本上都是顺序写的，在数据库运行时不需要对 redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。 为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作。fsync 的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。 参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略。

• 1：默认值，表示事务提交时必须调用一次 fsync 操作。
• 0：事务提交时不进行写入重做日志操作，而在 master thread 中每 1 秒会进行一次重做日志文件的 fsync 操作。（丢 1s 的数据）
• 2：事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。（MySQL 重启不丢失，只有服务器宕机丢失数据）

bin log 和 redo log 区别

• 重做日志是在 InnoDB 存储引擎层产生，而二进制日志是在 MySQL 数据库的上层产生的，并且二进制日志不仅仅针对于 InnoDB 存储引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
• 两种日志记录的内容形式不同。MySQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句。而 InnoDB存 储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。
• 两种日志记录写入磁盘的时间点不同。二进制日志只在事务提交完成后进行一次写入。而 InnoDB 存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

数据恢复 InnoDB 存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志，要快很多。与此同时，InnoDB 存储引擎自身也对恢复进行了一定程度的优化，如顺序读取及并行应用重做日志，这样可以进一步地提高数据库恢复的速度。由于 checkpoint 表示已经刷新到磁盘页上的 LSN，因此在恢复过程中仅需恢复 checkpoint 开始的日志部分。

Undo Log

InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条 ROLLBACK 语句请求回滚，就可以利用这些 undo 信息将数据回滚到修改之前的样子。 redo 存放在重做日志文件中，与 redo 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段（undo segment）。undo 段位于共享表空间内。 undo 是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了（反向操作，insert 对于 delete，delete 对于 insert，反向 update），但是数据结构和页本身在回滚之后可能大不相同。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。 除了回滚操作，undo 的另一个作用是 MVCC，即在 InnoDB 存储引擎中 MVCC 的实现是通过 undo 来完成。 undo log 所包含的相关（增删改回滚）操作会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。 undo log分为：

• insert undo log 指在 insert 操作中产生的 undo log。
• update undo log 记录的是对 delete 和 update 操作产生的 undo log。该 undo log 可能需要提供 MVCC机制，因此不能在事务提交时就进行删除。

Purge

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故 InnoDB 存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过 purge 来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete 操作。可见，purge 操作是清理之前的 delete 和 update 操作，将上述操作“最终”完成。而实际执行的操作为 delete 操作，清理之前行记录的版本。InnoDB 存储引擎有一个 history 列表，它根据事务提交的顺序，将 undo log 进行链接。 InnoDB 存储引擎首先从 history list 中找到第一个需要被清理的记录，这里为 trx1，清理之后 InnoDB 存储引擎会在 trx1 的 undo log 所在的页中继续寻找是否存在可以被清理的记录，这里会找到事务 trx3，接着找到 trx5，但是发现 trx5 被其他事务所引用而不能清理，故去再次去 history list 中查找，发现这时最尾端的记录为 trx2，接着找到 trx2 所在的页，然后依次再把事务 trx6、trx4 的记录进行清理。 InnoDB存储引擎这种先从history list中找undo log，然后再从undo page中找undo log的设计模式是为了避免大量的随机读取操作，从而提高purge的效率。

Group Commit

为了提高磁盘 fsync 的效率，当前数据库都提供了 group commit 的功能，即一次 fsync 可以刷新确保多个事务日志被写入文件。（多次写入，一次 fsync） 对于 InnoDB 存储引擎来说，事务提交时会进行两个阶段的操作：

1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
2. 调用 fsync 将确保日志都从重做日志缓冲写入磁盘。

然而在 InnoDB1.2 版本之前，在开启二进制日志后，InnoDB 存储引擎的 group commit 功能会失效，从而导致性能的下降。并且在线环境多使用 replication 环境，因此二进制日志的选项基本都为开启状态，因此这个问题尤为显著。导致这个问题的原因是在开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，二者之间使用了两阶段事务，其步骤如下：

1. 当事务提交时InnoDB存储引擎进行prepare操作。
2. MySQL数据库上层写入二进制日志。
3. InnoDB存储引擎层将日志写入重做日志文件。
   1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
   2. 调用fsync将确保日志都从重做日志缓冲写入磁盘。

一旦步骤 2 中的操作完成，就确保了事务的提交，即使在执行步骤 3 时数据库发生了宕机（使用 XA 事务来保证）。此外需要注意的是，每个步骤都需要进行一次 fsync 操作才能保证上下两层数据的一致性。步骤2 的 fsync 由参数 sync_binlog 控制，步骤 3 的 fsync 由参数 innodb_flush_log_at_trx_commit 控制。 为了保证 MySQL 数据库上层二进制日志的写入顺序和 InnoDB 层的事务提交顺序一致，MySQL 数据库内部使用了 prepare_commit_mutex 这个锁。但是在启用这个锁之后，步骤 3 中的步骤 a 不可以在其他事务执行步骤 b 时进行，从而导致了 group commit 失效。

MySQL 5.6 BLGC 的实现方式是将事务提交的过程分为几个步骤来完成： 在 MySQL 数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为 leader，其他事务称为 follower，leader 控制着 follower 的行为。

• Flush 阶段，将每个事务的二进制日志写入内存中。
• Sync 阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次 fsync 操作就完成了二进制日志的写入，这就是 BLGC。
• Commit 阶段，leader 根据顺序调用存储引擎层事务的提交，InnoDB 存储引擎本就支持 group commit，因此修复了原先由于锁 prepare_commit_mutex 导致 group commit 失效的问题。

事务的隔离级别

SQL 标准定义的四个隔离级别为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。

• 在 SERIALIABLE 的事务隔离级别，InnoDB 存储引擎会对每个 SELECT 语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。
• InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ，但是与标准 SQL 不同的是，InnoDB 存储引擎在 REPEATABLE READ 事务隔离级别下，使用 Next-Key Lock 锁的算法。
• 在 READ COMMITTED 的事务隔离级别下，除了唯一性的约束检查及外键约束的检查需要 gap lock，InnoDB 存储引擎不会使用 gap lock 的锁算法（使用 record lock）。MVCC 控制读取最新版本快照。

分布式事务

XA 事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

• 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
• 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
• 应用程序：定义事务的边界，指定全局事务中的操作。

在 MySQL 数据库的分布式事务中，资源管理器就是 MySQL 数据库，事务管理器为连接 MySQL 服务器的客户端。

2PC

使用两段式提交（two-phase commit）的方式。

• 在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。
• 在第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT。如果任何一个节点显示不能提交（或者等待 RM 回复超时），则所有的节点都被告知需要回滚。

缺点：

• 一阶段还是二阶段，都是加锁的
• 单点故障，TM 宕机，RM 都一直阻塞，并且 RM 占有资源（锁）

3PC

Can-commit 阶段：协调者询问所有参与者是否可以执行事务； Pre-commit 阶段：如果所有参与者都可以执行事务，则协调者发送预提交请求到所有参与者； Do-commit 阶段：参与者执行事务，并反馈执行结果到协调者，如果所有参与者都成功执行了事务，则协调者发送提交请求到所有参与者，否则发送回滚请求到所有参与者。

优点：

• 对比 2PC，加入 Can-commit 阶段，这里是无锁，这里先判断能不能获取到资源。不能的话，直接结束后续流程，没有开始事务。减少死锁。2PC 不论受否能执行都直接执行事务。
• 加入 RM 的超时机制，2PC 只有 TM 有超时机制（长时间未收到 RM 的回复），进行回滚；3PC 在 RM 也加入超时机制（长时间未收到 TM 的命令，TM 宕机），自动进行回滚/提交。

缺点：

• 设计复杂，参与者如何知道自身能否执行事务。
• 多轮消息交换，增加带宽和网络延迟。
• 还是无法解决网络分区的问题，最后阶段如果部分 RM 因为网络问题，未收到 TM 的命令。部分成功执行，部分未成功。 #### TCC Try，Confim，Cancel 需要业务上的失败补偿逻辑，代码入侵较大。

XA 事务

MySQL 数据库中还存在另外一种分布式事务，其在存储引擎与插件之间，又或者在存储引擎与存储引擎 之间，称之为内部 XA 事务。 最为常见的内部 XA 事务存在于 binlog 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 binlog 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入 InnoDB 存储引擎时发生了宕机，那么 slave 可能会接收到 master 传过去的二进制日志并执行，最终导致了主从不一致的情况。 为了解决这个问题，MySQL 数据库在 binlog 与 InnoDB 存储引擎之间采用 XA 事务。当事务提交时，InnoDB 存储引擎会先做一个 PREPARE 操作，将事务的 xid 写入，接着进行二进制日志的写入。如果在 InnoDB 存储引擎提交前，MySQL 数据库宕机了，那么 MySQL 数据库在重启后会先检查准备的 UXID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

备份

根据备份的方法不同可以将备份分为：

• Hot Backup（热备） 指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响
• Cold Backup（冷备）备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可
• Warm Backup（温备） 在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性

按照备份后文件的内容，备份又可以分为：

• 逻辑备份：逻辑备份是指备份出的文件内容是可读的，一般是文本文件。内容一般是由一条条SQL语句，或者是表内实际数据组成。如 mysqldump 和 SELECT*INTO OUTFILE 的方法。
• 裸文件备份：裸文件备份是指复制数据库的物理文件，既可以是在数据库运行中的复制（如 ibbackup、xtrabackup 这类工具），也可以是在数据库停止运行时直接的数据文件复制。这类备份的恢复时间往往较逻辑备份短很多。

若按照备份数据库的内容来分，备份又可以分为：

• 完全备份
• 增量备份
• 日志备份 （bin log）

热备

ibbackup 是 InnoDB 存储引擎官方提供的热备工具，可以同时备份 MyISAM 存储引擎和 InnoDB 存储引擎表。 对于 InnoDB 存储引擎表其备份工作原理如下：

1. 记录备份开始时，InnoDB 存储引擎重做日志文件检查点的 LSN。
2. 复制共享表空间文件以及独立表空间文件。
3. 记录复制完表空间文件后，InnoDB 存储引擎重做日志文件检查点的 LSN。
4. 复制在备份时产生的重做日志。

可以发现，在备份期间不会对数据库本身有任何影响，所做的操作只是复制数据库文件，因此任何对数据库的操作都是允许的，不会阻塞任何操作。 故 ibbackup 的优点如下：

• 在线备份，不阻塞任何的 SQL 语句。
• 备份性能好，备份的实质是复制数据库文件和重做日志文件。
• 支持压缩备份，通过选项，可以支持不同级别的压缩。
• 跨平台支持，ibbackup 可以运行在 Linux、Windows 以及主流的 UNIX 系统平台上。

ibbackup 对 InnoDB 存储引擎表的恢复步骤为：

• 恢复表空间文件。
• 应用重做日志文件。

ibbackup 提供了一种高性能的热备方式，是 InnoDB 存储引擎备份的首选方式。不过它是收费软件，并非免费的软件。Percona 公司给用户带来了开源、免费的 XtraBackup 热备工具，它实现所有 ibbackup 的功能，并且扩展支持了真正的增量备份功能。因此，更好的选择是使用 XtraBackup 来完成热备的工作。支持 MySQL5.0 以上的版本。

MySQL 数据库本身提供的工具并不支持真正的增量备份，更准确地说，二进制日志的恢复应该是 point-in-time 的恢复而不是增量备份。而 XtraBackup 工具支持对于 InnoDB 存储引擎的增量备份，其工作原理如下：

1. 首选完成一个全备，并记录下此时检查点的 LSN。
2. 在进行增量备份时，比较表空间中每个页的 LSN 是否大于上次备份时的 LSN，如果是，则备份该页，同时记录当前检查点的 LSN。

冷备

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

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

冷备的缺点是：

• InnoDB 存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如 undo 段，插入缓冲等信息。
• 冷备也不总是可以轻易地跨平台。操作系统、MySQL 的版本、文件大小写敏感和浮点数格式都会成为问题。

逻辑备份

1. mysqldump mysqldump 的语法如下：shell>mysqldump[arguments]>fle_name 如果想要备份所有的数据库，可以使用 --all-databases 选项：shell>mysqldump --all-databases>dump.sql 如果想要备份指定的数据库，可以使用 --databases 选项: shell>mysqldump --databases db1 db2 db3>dump.sql
2. SELECT...INTO OUTFILE SELECT...INTO语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据。SELECT...INTO的语法如下：

SELECT[column 1],[column 2]...
INTO
OUTFILE'file_name'
[{FIELDS|COLUMNS}
[TERMINATED BY'string']
[[OPTIONALLY]ENCLOSED BY'char']
[ESCAPED BY'char']
]
[LINES
[STARTING BY'string']
[TERMINATED BY'string']
]
FROM TABLE WHERE...... 

复制的工作原理

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。 总体来说，replication的工作原理分为以下3个步骤：

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

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。从服务器有 2 个线程，一个是 I/O 线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是 SQL 线程，复制执行中继日志。

查询运行状态 show slave status

调优

1. 单表调优。
2. 多插入的调优。（批量插入；id自增； group commit）
3. 多查询调优。索引，explain，B+树（覆盖索引特性多理解），严格约束字段的长度，能用数字就用数字，尤其是 tinyint。（男女，0,1）（like，join）
4. 分区 ，更快的查询速度。
5. 拆分表（水平拆分 、 垂直拆分=减少单表的字段量。）
6. MRR
7. ICP
8. 测试（非压测环境，非预生产环境）环境开启慢查询日志。进行日常 、 上线前的测试。分析慢查询sql。
9. CPU / 磁盘 / 内存 / RAID
10. 缓存 、 queue、分布式、读写分离

cpu

• thread 线程
  • io thread
    • log thread
    • insert buffer
    • read thread
    • write thread
  • master thread
  • purge thrad
  • page cleaner thread

内存

• InnoDB Buffer Pool：默认 128M，不够用。

压测

• sysbench、mysql-tpcc