MySQL InnoDB 存储引擎

https://zhuanlan.zhihu.com/p/103487968

对于 MySQL，要记住、或者要放在你随时可以找到的地方的两张图，一张是 MySQL 架构图：

MySQL 架构

另一张则是 InnoDB 架构图：

innodb-architecture

遇到问题，或者学习到新知识点时，就往里套，想一想，这是对应这两张图的哪个模块、是属于具体哪个成员的能力。

这其中，第一张图的最底下的存储引擎层（Storage Engines），它决定了 MySQL 会怎样存储数据，怎样读取和写入数据，也在很大程度上决定了 MySQL 的读写性能和数据可靠性。

对于这么重要的一层能力，MySQL 提供了极强的扩展性，你可以定义自己要使用什么样的存储引擎：InnoDB、MyISAM、MEMORY、CSV，甚至可以自己开发一个存储引擎然后使用它。

我一直觉得 MySQL 的设计，是教科书式的，高内聚松耦合，边界明确，职责清晰。学习 MySQL，学的不只是如何更好的使用 MySQL，更是学习如何更好的进行系统设计。

通常我们说 Mysql 高性能高可靠，都是指基于 InnoDB 存储引擎的 Mysql，所以，这一讲，先让我们来看看，除了 redo log，InnoDB 里还有哪些成员，他们都有什么能力，承担了什么样的角色，他们之间又是怎么配合的？

InnoDB 内存架构

从上面第二张图可以看到，InnoDB 主要分为两大块：

  • InnoDB In-Memory Structures
  • InnoDB On-Disk Structures

内存和磁盘，让我们先从内存开始。

1、Buffer Pool

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed.

正如之前提到的，MySQL 不会直接取修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。

而这些数据存放的地方，就是 Buffer Pool。

我们平时开发时，会用 redis 来做缓存，缓解数据库压力，其实 MySQL 自己也做了一层类似缓存的东西。

MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool 是a linked list of pages，一个以页为元素的链表。

为什么是链表？因为和缓存一样，它也需要一套淘汰算法来管理数据。

Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存：

LRU

关于 Buffer Pool 的更多知识，诸如如何配置大小、如何监控等等：Buffer Pool

2、Change Buffer

上面提到过，如果内存里没有对应「页」的数据，MySQL 就会去把数据从磁盘里 load 出来，如果每次需要的「页」都不同，或者不是相邻的「页」，那么每次 MySQL 都要去 load，这样就很慢了。

于是如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge：

  • merge：Change Buffer -> Buffer Pool
  • purge：Buffer Pool -> Disk

Change Buffer

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

上面是 MySQL 官网对 Change Buffer 的定义，仔细看的话，你会发现里面提到： Change Buffer 只在操作「二级索引」（secondary index）时才使用，原因是「聚簇索引」（clustered indexes）必须是「唯一」的，也就意味着每次插入、更新，都需要检查是否已经有相同的字段存在，也就没有必要使用 Change Buffer 了；另外，「聚簇索引」操作的随机性比较小，通常在相邻的「页」进行操作，比如使用了自增主键的「聚簇索引」，那么 insert 时就是递增、有序的，不像「二级索引」，访问非常随机。

如果想深入理解 Change Buffer 的原理，除了 MySQL 官网的介绍：Change Buffer，还可以阅读下《MySQL技术内幕》的「2.6.1 - 插入缓冲」章节，里面会从 Change Buffer 的前身 —— Insert Buffer 开始讲起，很透彻。

3、Adaptive Hash Index

MySQL 索引，不管是在磁盘里，还是被 load 到内存后，都是 B+ 树，B+ 树的查找次数取决于树的深度。你看，数据都已经放到内存了，还不能“一下子”就找到它，还要“几下子”，这空间牺牲的是不是不太值得？

尤其是那些频繁被访问的数据，每次过来都要走 B+ 树来查询，这时就会想到，我用一个指针把数据的位置记录下来不就好了？

这就是「自适应哈希索引」（Adaptive Hash Index）。自适应，顾名思义，MySQL 会自动评估使用自适应索引是否值得，如果观察到建立哈希索引可以提升速度，则建立。

4、Log Buffer

The log buffer is the memory area that holds data to be written to the log files on disk.

从上面架构图可以看到，Log Buffer 里的 redo log，会被刷到磁盘里：

 

Log Buffer

Operating System Cache

在内存和磁盘之间，你看到 MySQL 画了一层叫做 Operating System Cache 的东西，其实这个不属于 InnoDB 的能力，而是操作系统为了提升性能，在磁盘前面加的一层高速缓存，这里不展开细讲，感兴趣的同学可以参考下维基百科：Page Cache

 

InnoDB 磁盘架构

磁盘里有什么呢？除了表结构定义和索引，还有一些为了高性能和高可靠而设计的角色，比如 redo log、undo log、Change Buffer，以及 Doublewrite Buffer 等等.

有同学会问，那表的数据呢？其实只要理解了 InnoDB 里的所有表数据，都以索引（聚簇索引+二级索引）的形式存储起来，就知道索引已经包含了表数据。

1、表空间（Tablespaces）

从架构图可以看到，Tablespaces 分为五种：

  • The System Tablespace
  • File-Per-Table Tablespaces
  • General Tablespace
  • Undo Tablespaces
  • Temporary Tablespaces

其中，我们平时创建的表的数据，可以存放到 The System Tablespace 、File-Per-Table Tablespaces、General Tablespace 三者中的任意一个地方，具体取决于你的配置和创建表时的 sql 语句。

这里同样不展开，如何选择不同的表空间存储数据？不同表空间各自的优势劣势等等，传送门：Tablespaces

2、Doublewrite Buffer

如果说 Change Buffer 是提升性能，那么 Doublewrite Buffer 就是保证数据页的可靠性。

怎么理解呢？

前面提到过，MySQL 以「页」为读取和写入单位，一个「页」里面有多行数据，写入数据时，MySQL 会先写内存中的页，然后再刷新到磁盘中的页。

这时问题来了，假设在某一次从内存刷新到磁盘的过程中，一个「页」刷了一半，突然操作系统或者 MySQL 进程奔溃了，这时候，内存里的页数据被清除了，而磁盘里的页数据，刷了一半，处于一个中间状态，不尴不尬，可以说是一个「不完整」，甚至是「坏掉的」的页。

有同学说，不是有 Redo Log 么？其实这个时候 Redo Log 也已经无力回天，Redo Log 是要在磁盘中的页数据是正常的、没有损坏的情况下，才能把磁盘里页数据 load 到内存，然后应用 Redo Log。而如果磁盘中的页数据已经损坏，是无法应用 Redo Log 的。

所以，MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据。

留个问题，有了 Doublewrite Buffer 后，不就意味着 MySQL 要写两次磁盘？性能岂不是很差？

 

参考

 

作者：范鹏程，网易考拉海购

InnoDB是 MySQL最常用的存储引擎，了解InnoDB存储引擎的索引对于日常工作有很大的益处，索引的存在便是为了加速数据库行记录的检索。以下是我对最近学习的知识的一些总结，以及对碰到的以及别人提到过的问题的一些分析，如有错误，请指正，我会及时更正。

目录

InnoDB表结构

B树与B+树

聚簇索引和二级索引

SQL执行顺序

SQL优化建议

一些问题分析

参考资料

1. InnoDB表结构

此小结与索引其实没有太多的关联，但是为了便于理解索引的内容，添加此小结作为铺垫知识。

1.1 InnoDB逻辑存储结构

MySQL表中的所有数据被存储在一个空间内，称之为表空间，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),逻辑结构如下图：

  • 段(segment)

表空间是由不同的段组成的，常见的段有：数据段，索引段，回滚段等等，在 MySQL中，数据是按照B+树来存储，因此数据即索引，因此数据段即为B+树的叶子节点，索引段为B+树的非叶子节点,回滚段用于存储undo日志，用于事务失败后数据回滚以及在事务未提交之前通过undo日志获取之前版本的数据，在InnoDB1.1版本之前一个InnoDB,只支持一个回滚段，支持1023个并发修改事务同时进行，在InnoDB1.2版本，将回滚段数量提高到了128个，也就是说可以同时进行128*1023个并发修改事务。

  • 区(extent)

区是由连续页组成的空间，每个区的固定大小为1MB,为保证区中页的连续性，InnoDB会一次从磁盘中申请4~5个区，在默认不压缩的情况下，一个区可以容纳64个连续的页。但是在开始新建表的时候，空表的默认大小为96KB,是由于为了高效的利用磁盘空间，在开始插入数据时表会先利用32个页大小的碎片页来存储数据，当这些碎片使用完后，表大小才会按照MB倍数来增加。

  • 页(page)

页是InnoDB存储引擎的最小管理单位，每页大小默认是16KB，从InnoDB 1.2.x版本开始，可以利用innodb_page_size来改变页size，但是改变只能在初始化InnoDB实例前进行修改，之后便无法进行修改，除非mysqldump导出创建新库，常见的页类型有：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

  • 行(row)

行对应的是表中的行记录，每页存储最多的行记录也是有硬性规定的最多16KB/2-200,即7992行（16KB是页大小，我也不明白为什么要这么算,据说是内核定义）

1.2 InnoDB行记录格式

InnoDB提供了两种格式来存储行记录：Redundant格式、Compact格式、Dynamic格式、Compressed格式，Redudant格式是为了兼容保留的。

Redundant行格式（5.0版本之前的格式）

  • 字段长度偏移列表：存储字段偏移量，与列字段顺序相反存放，若列长度小于255字节，用一个字节表示，若大于255字节，用两个字节表示
  • 记录头信息：固定用6字节表示，具体含义如下：

隐藏列：事务id和回滚列id,分别占用6、7字节，若此表没有主键，还会增加6字节的rowid列。

Compact行格式(5.6版本的默认行格式)

  • 变长字段长度列表：此字段标识列字段的长度，与列字段顺序相反存放，若列长度小于255字节，用一个字节表示，若大于255字节，用两个字节表示，这也是 MySQL的VARCHAR类型最大长度限制为65535
  • NULL标志位：标识改列是否有空字段，有用1表示，否则为0，该标志位长度为ceil(N/8)（此处是 MySQL技术内幕-InnoDB存储引擎与官方文档有出入的地方）；
  • 记录头信息：固定用5字节表示，具体含义如下：

  • 列数据：此行存储着列字段数据，Null是不占存储空间的；
  • 隐藏列：事务id和回滚列id,分别占用6、7字节，若此表没有主键，还会增加6字节的rowid列。
Note: 关于行溢出，即Redundant格式、Compact格式存储很长的字符串，在该字段会存储该字符串的前768个字节的前缀（字段超过768字节则为变长字段），并将整个字符串存储在uncompress blob页中。

Dynamic格式(5.7版本默认行格式)和Compressed格式

Dynamic格式和Compressed格式与Compact的不同之处在于对于行溢出只会在该列处存放20字节的指针，指向该字符串的实际存储位置，不会存储768字节前缀，而且Compressed格式在存储BLOB、TEXT、VARCHAR等类型会利用zlib算法进行压缩，能够以很高的存储效率来存储字符串。

1.3 InnoDB数据页结构

《 MySQL技术内幕-InnoDB存储引擎》书中对此有描述，但是应该不是太准确，书中有如下描述，此处不做详细介绍，若有兴趣请看此神书。

2. B树与B+树

B树与B+树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。

2.1 B树

定义：

B树（B-TREE）满足如下条件，即可称之为m阶B树：

  • 每个节点之多拥有m棵子树；
  • 根结点至少拥有两颗子树（存在子树的情况下);
  • 除了根结点以外，其余每个分支结点至少拥有 m/2 棵子树；
  • 所有的叶结点都在同一层上；
  • 有 k 棵子树的分支结点则存在 k-1 个关键码，关键码按照递增次序进行排列；
  • 关键字数量需要满足ceil(m/2)-1 <= n <= m-1；

B树插入

B树删除

2.2 B+树

定义：

B+树满足如下条件，即可称之为m阶B+树：

  • 根结点只有一个，分支数量范围为[2，m]
  • 分支结点，每个结点包含分支数范围为[ceil(m/2), m]；
  • 分支结点的关键字数量等于其子分支的数量减一，关键字的数量范围为[ceil(m/2)-1, m-1]，关键字顺序递增；
  • 所有叶子结点都在同一层；

插入：

B+树的插入必须保证插入后叶节点中的记录依然排序，同时需要考虑插入B+树的三种情况，每种情况都可能会导致不同的插入算法，插入算法入下图：

插入举例(未加入双向链表)：

1、 插入28这个键值，发现当前Leaf Page和Index Page都没有满，直接插入。

2、 插入70这个键值，Leaf Page已经满了，但是Index Page还没有满，根据中间的值60拆分叶节点。

3、 插入记录95，Leaf Page和Index Page都满了，这时需要做两次拆分

4、 B+树总是会保持平衡。但是为了保持平衡，对于新插入的键值可能需要做大量的拆分页（split）操作，而B+树主要用于磁盘，因此页的拆分意味着磁盘数据移动，应该在可能的情况下尽量减少页的拆分。因此，B+树提供了旋转（rotation）的功能。旋转发生在Leaf Page已经满了、但是其左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。通常情况下，左兄弟被首先检查用来做旋转操作，在第一张图情况下，插入键值70，其实B+树并不会急于去拆分叶节点，而是做旋转，50，55，55旋转。

删除：

B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶节点中的记录依然排序，同插入一样，B+树的删除操作同样需要考虑下图所示的三种情况，与插入不同的是，删除根据填充因子的变化来衡量。

删除示例(未加入双向链表)：

1、删除键值为70的这条记录，直接删除（在插入第三点基础上的图）。

2、接着我们删除键值为25的记录，该值还是Index Page中的值，因此在删除Leaf Page中25的值后，还应将25的右兄弟节点的28更新到Page Index中。

3、删除键值为60的情况，删除Leaf Page中键值为60的记录后，填充因子小于50%，这时需要做合并操作，同样，在删除Index Page中相关记录后需要做Index Page的合并操作。

B树与B+树区别：

以m阶树为例：

  • 关键字不同：B+树中分支结点有m个关键字，其叶子结点也有m个，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
  • 存储位置不同：B+树非叶子节点的关键字只起到索引作用，实际的关键字存储在叶子节点，B树的非叶子节点也存储关键字。
  • 分支构造不同：B+树的分支结点仅仅存储着关键字信息和儿子的指针，也就是说内部结点仅仅包含着索引信息。
  • 查询不同（稳定）：B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。

3. 聚簇索引和二级索引

3.1 聚簇索引

每个InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是很有必要的。

聚簇索引按照如下规则创建：

  • 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
  • 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
  • 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。
Note: 对于选择唯一索引的顺序是按照定义唯一索引的顺序，而非表中列的顺序, 同时选中的唯一索引字段会充当为主键，或者InnoDB隐式创建的自增列也可以看做主键。

聚簇索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中的数据也是聚簇索引中的一部分，数据页之间是通过一个双向链表来链接的，上文说到B+树是一棵平衡查找树，也就是聚簇索引的数据存储是有序的，但是这个是逻辑上的有序，但是在实际在数据的物理存储上是，因为数据页之间是通过双向链表来连接，假如物理存储是顺序的话，那维护聚簇索引的成本非常的高。

3.2 辅助索引

除了聚簇索引之外的索引都可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引，但是只能有一个聚簇索引，通过辅助索引来查找对应的航记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚簇索引中查询到对应的行记录，也就是进行两次B+树搜索。相反通过辅助索引来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚簇索引）。

创建辅助索引，可以创建单列的索引，也就是用一个字段来创建索引，也可以用多个字段来创建副主索引称为联合索引，创建联合索引后，B+树的节点存储的键值数量不是1个，而是多个，如下图：

  • 联合索引的B+树和单键辅助索引的B+树是一样的，键值都是排序的，通过叶子节点可以逻辑顺序的读出所有的数据，比如上图所存储的数据时，按照(a,b)这种形式(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)进行存放，这样有个好处存放的数据时排了序的，当进行order by对某个字段进行排序时，可以减少复杂度，加速进行查询；
  • 当用select * from table where a=? and ?可以使用索引(a,b)来加速查询，但是在查询时有一个原则，sql的where条件的顺序必须和二级索引一致，而且还遵循索引最左原则，select * from table where b=?则无法利用(a,b)索引来加速查询。
  • 辅助索引还有一个概念便是索引覆盖，索引覆盖的一个好处便是辅助索引不高含行记录，因此其大小远远小于聚簇索引，利用辅助索引进行查询可以减少大量的IO操作。

4. SQL执行顺序

以下的每一步操作都会生成一个虚拟表，作为下一个处理的输入，在这个过程中，这些虚拟表对于用户都是透明的，只用最后一步执行完的虚拟表返回给用户，在处理过程中，没有的步骤会直接跳过。

以下为逻辑上的执行顺序：

(1) from：对左表left-table和右表right-table执行笛卡尔积(a*b)，形成虚拟表VT1;

(2) on: 对虚拟表VT1进行on条件进行筛选，只有符合条件的记录才会插入到虚拟表VT2中;

(3) join: 指定out join会将未匹配行添加到VT2产生VT3,若有多张表，则会重复(1)~(3);

(4) where: 对VT3进行条件过滤，形成VT4, where条件是从左向右执行的;

(5) group by: 对VT4进行分组操作得到VT5;

(6) cube | rollup: 对VT5进行cube | rollup操作得到VT6;

(7) having: 对VT6进行过滤得到VT7;

(8) select: 执行选择操作得到VT8，本人看来VT7和VT8应该是一样的;

(9) distinct: 对VT8进行去重，得到VT9;

(10) order by: 对VT9进行排序，得到VT10;

(11) limit: 对记录进行截取，得到VT11返回给用户。

Note: on条件应用于连表过滤，where应用于on过滤后的结果（有on的话），having应用于分组过滤

5. SQL优化建议

索引有如下有点：减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。

可使用B+树索引的查询方式

  • 全值匹配：与索引中的所有列进行匹配，也就是条件字段与联合索引的字段个数与顺序相同；
  • 匹配最左前缀：只使用联合索引的前几个字段；
  • 匹配列前缀：比如like 'xx%'可以走索引；
  • 匹配范围值：范围查询，比如>,like等；
  • 匹配某一列并范围匹配另外一列：精确查找+范围查找；
  • 只访问索引查询：索引覆盖，select的字段为主键；

范围查询后的条件不会走索引，具体原因会在下一节进行介绍。

列的选择性（区分度）

选择性（区分度）是指不重复的列值个数/列值的总个数，一般意义上建索引的字段要区分度高，而且在建联合索引的时候区分度高的列字段要放在前边，这样可以在第一个条件就过滤掉大量的数据，有利用性能的提升，对于如何计算列的区分度，有如下两种方法：

  • 根据定义，手动计算列的区分度，不重复的列值个数/列值的总个数；
  • 通过 MySQL的carlinality，通过命令show index from <table_name>来查看，解释一下，此处的carlinality并不是准确值，而且 MySQL在B+树种选择了8个数据页来抽样统计的值，也就是说carlinality=每个数据页记录总和/8*所有的数据页，因此也说明这个值是不准确的，因为在插入/更新记录时，实时的去更新carlinality对于 MySQL的负载是很高的，如果数据量很大的话，触发 MySQL重新统计该值得条件是当表中的1/16数据发生变化时。

但是选择区分度高的列作为索引也不是百试百灵的，某些情况还是不合适的，下节会进行介绍。

MySQL查询过程

当希望 MySQL能够高性能运行的时候，最好的办法就是明白 MySQL是如何优化和执行的，一旦理解了这一点，很多查询优化工作实际上就是遵循了一些原则让优化器能够按照预想的合理的方式运行————《引用自高性能 MySQL 》

当想 MySQL实例发送一个请求时， MySQL按照如下图的方式进行查询：

  • 客户端先发送一条查询给服务器；
  • 服务器先检查查询缓存，如果命中了缓存，则立刻返回给存储在缓存中的结果，否则进入下一个阶段；
  • 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
  • MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询；
  • 将结果返回客户端。

注意&建议

  • 主键推荐使用整型，避免索引分裂；
  • 查询使用索引覆盖能够提升很大的性能，因为避免了回表查询；
  • 选择合适的顺序建立索引，有的场景并非区分度越高的列字段放在前边越好，联合索引使用居多；
  • 合理使用in操作将范围查询转换成多个等值查询；
  • in操作相当于多个等值操作，但是要注意的是对于order by来说，这相当于范围查询，因此例如select * from t1 where c1 in (x,x) order by c2的sql是不走索引的；
  • 将大批量数据查询任务分解为分批查询；
  • 将复杂查询转换为简单查询；
  • 合理使用inner join，比如说分页时候。

6. 一些问题分析

这个部分是我在学习过程中产生的一些疑问，以及在工作中碰到的或者同事提起的一些问题，对此我做了些调研，总结了一下并添加了些自己的理解，如有错误还请指正。

索引分裂

此处提一下索引分裂，就我个人理解，在 MySQL插入记录的同时会更新配置的相应索引文件，根据以上的了解，在插入索引时，可能会存在索引的页的分裂，因此会导致磁盘数据的移动。当插入的主键是随机字符串时，每次插入不会是在B+树的最后插入，每次插入位置都是随机的，每次都可能导致数据页的移动，而且字符串的存储空间占用也很大，这样重建索引不仅仅效率低而且 MySQL的负载也会很高，同时还会导致大量的磁盘碎片，磁盘碎片多了也会对查询造成一定的性能开销，因为存储位置不连续导致更多的磁盘I/O,这就是为什么推荐定义主键为递增整型的一个原因， MySQL索引页默认大小是16KB，当有新纪录插入的时候， MySQL会留下每页空间的1/16用于未来索引记录增长，避免过多的磁盘数据移动。

自增主键的弊端

对于高并发的场景，在InnoDB中按照主键的顺序插入可能会造成明显的争用，主键的上界会成为“热点”，因为所有的插入都发生在此处，索引并发的插入可能会造成间隙锁竞争，何为间隙锁竞争，下个会详细介绍；另外一个原因可能是Auto_increment的锁机制，在 MySQL处理自增主键时，当innodb_autoinc_lock_mode为0或1时，在不知道插入有多少行时，比如insert t1 xx select xx from t2，对于这个statement的执行会进行锁表，只有这个statement执行完以后才会释放锁，然后别的插入才能够继续执行，但是在innodb_autoinc_lock_mode=2时，这种情况不会存在表锁，但是只能保证所有并发执行的statement插入的记录是唯一并且自增的，但是每个statement做的多行插入之间是不连接的。

优化器不使用索引选择全表扫描

比如一张order表中有联合索引(order_id, goods_id)，在此例子上来说明这个问题是从两个方面来说：

  • 查询字段在索引中

select order_id from order where order_id > 1000,如果查看其执行计划的话，发现是用use index condition,走的是索引覆盖。

  • 查询字段不在索引中

select * from order where order_id > 1000, 此条语句查询的是该表所有字段，有一部分字段并未在此联合索引中，因此走联合索引查询会走两步，首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询，然后得到所有记录，利用主键id在聚簇索引中查询记录的过程是无序的，在磁盘上就变成了离散读取的操作，假如当读取的记录很多时（一般是整个表的20%左右），这个时候优化器会选择直接使用聚簇索引，也就是扫全表，因为顺序读取要快于离散读取，这也就是为何一般不用区分度不大的字段单独做索引，注意是单独因为利用此字段查出来的数据会很多，有很大概率走全表扫描。

范围查询之后的条件不走索引

根据 MySQL的查询原理的话，当处理到where的范围查询条件后，会将查询到的行全部返回到服务器端（查询执行引擎），接下来的条件操作在服务器端进行处理，这也就是为什么范围条件不走索引的原因了，因为之后的条件过滤已经不在存储引擎完成了。但是在 MySQL 5.6以后假如了一个新的功能index condition pushdown(ICP),这个功能允许范围查询条件之后的条件继续走索引，但是需要有几个前提条件：

  • 查询条件的第一个条件需要时有边界的，比如select * from xx where c1=x and c2>x and c3<x,这样c3是可以走到索引的；
  • 支持InnoDB和MyISAM存储引擎；
  • where条件的字段需要在索引中；
  • 分表ICP功能5.7开始支持；
  • 使用索引覆盖时，ICP不起作用。
set @@optimizer_switch = "index_condition_pushdown=on" 开启ICP set @@optimizer_switch = "index_condition_pushdown=off" 关闭ICP

范围查询统计函数不遵循 MySQL索引最左原则

比如创建一个表：

create table `person`(
`id` int not null auto_increment primary key,
`uid` int not null,
`name` varchar(60) not null,
`time` date not null,
key `idx_uid_date` (uid, time)
)engine=innodb default charset=utf8mb4;

当执行select count(*) from person where time > '2018-03-11' and time < '2018-03-16'时，time是可以用到idx_uid_date`的索引的,看如下的执行计划：

其中extra标识use index说明是走索引覆盖的，一般意义来说是 MySQL是无法支持松散索引的，但是对于统计函数，是可以使用索引覆盖的，因此 MySQL的优化器选择利用该索引。

分页offset值很大性能问题

在 MySQL中，分页当offset值很大的时候，性能会非常的差，比如limit 100000, 20，需要查询100020条数据，然后取20条，抛弃前100000条，在这个过程中产生了大量的随机I/O,这是性能很差的原因，为了解决这个问题，切入点便是减少无用数据的查询，减少随机I/O。 解决的方法是利用索引覆盖，也就是扫描索引得到id然后再从聚簇索引中查询行记录，我知道有两种方式：

比如从表t1中分页查询limit 1000000,5

  • 利用inner join

select * from t1 inner join (select id from t1 where xxx order by xx limit 1000000,5) as t2 using(id),子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。

  • 利用范围查询条件来限制取出的数据

select * from t1 where id > 1000000 order by id limit 0, 5，即利用条件id > 1000000在扫描索引是跳过1000000条记录，然后取5条即可,这种处理方式的offset值便成为0了，但此种方式通常分页不能用，但是可以用来分批取数据。

索引合并

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

对于如上的sql在 MySQL 5.0版本之前，假如没有建立相应的联合索引，是要走全表扫描的，但是在 MySQL 5.1后引入了一种优化策略为索引合并，可以在一定程度上利用表上的多个单列索引来定位指定行，其原理是将对每个索引的扫描结果做运算，总共有：交集、并集以及他们的组合，但是索引合并并非是一种合适的选择，因为在做索引合并时可能会消耗大量的CPU和内存资源，一般用到索引合并的情况也从侧面反映了该表的索引需要优化。

7. 参考资料

  • 《 MySQL技术内幕-InnoDB存储引擎》：此书对于InnoDB的讲解是比较全面而且细致的，但是稍微有一点点老并且还有一点点错误地方，此书是基于 MySQL 5.6版本的，里边会混杂一些5.7的知识。
  • 《 MySQL技术内幕：SQL编程》：值得一看。
  • 《高性能 MySQL 第三版》：此书是一本 MySQL神书，里边有很多的 MySQL优化建议以及一些案例。
  • 官方文档：这个是比较权威而且是最新的文档，缺点是篇幅很长，内容很多，而且还是纯英文，在理解和阅读速度上相对而言没有中文来得快。

 

 

MySQL 是如何实现 ACID 中的 D 的？

假设你执行了一条 sql 语句：

update user set age = 18 where user_id = 345981

MySQL 会直接去磁盘修改数据吗？

明显不会，磁盘IO太慢了，如果每个请求过来 MySQL 都要写磁盘，磁盘肯定扛不住。

那就写内存？把数据从磁盘load到内存，然后修改内存里的数据。

也不行，万一掉电了，内存就没了，数据就再也找不回来了。

这其实是很多中间件都会遇到的问题，一个中间件做的再怎么分布式，怎么高可靠，都会遇到这个问题：

数据来了，写磁盘，还是写内存？

写磁盘，嫌太慢？写内存，又不安全？

MySQL 的解决方案是：既写磁盘又写内存。

数据写内存，另外再往磁盘写 redo log.

redo log 是什么

在执行上面这条 sql 语句时，MySQL 会判断内存中有没有 user_id = 345981 的数据，没有，则去磁盘找到这条数据所在的「页」，把整页数据都加载到内存，然后找到 user_id = 345981 的 row 数据，把内存中这行数据的 age 设置为 18。

这时，内存的数据是新的、正确的，而磁盘数据是旧的、过时的，所以我们称这时的磁盘对应的页数据，为「脏页」。

这里补充一个知识点：MySQL 是按页为单位来读取数据的，一个页里面有很多行记录，从内存刷数据到磁盘，也是以页为单位来刷。

这时候如果掉电了，数据就没了，于是 MySQL 把你对页修改了什么内容，记录了下来，保存到磁盘，也就是redo log。

写完 redo log，MySQL 就认为事务提交成功了，数据持久化了（ACID的D），然后在空闲的时候，再把内存的数据刷到磁盘。

如果在内存数据刷到磁盘之前，MySQL 掉电了，怎么办？

这时只需要在重启后，把「脏页」load 到内存，然后应用 redo log，脏页就变成「干净页」了。

你会说，万一我写内存成功，但是把 redo log 写到磁盘失败了呢？这点后面我们在讨论「两阶段提交」时再讨论。

你还会说，redo log 还是要写磁盘，那不还是很慢？

并不是，把 redo log 写到磁盘，比一般的写磁盘要快，原因有：

  • 一般我们写磁盘，都是「随机写」，而 redo log，是「顺序写」
  • MySQL 在写 redo log 上做了优化，比如「组提交」

这些后面我们会陆续展开。

redo log 怎么存储

MySQL 官方文档有几句话：

the redo log encodes requests to change table data that result from SQL statements or low-level API calls.
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.
By default, the redo log is physically represented on disk by two files named ib_logfile0 and ib_logfile1.
MySQL writes to the redo log files in a circular fashion.

从这几句话，我们大致可以get到：

  • redo log 记录了 sql 语句以及其他 api，对表数据产生的变化，也就是说，redo log 记录的是数据的物理变化，这也是它和后面要讲的 binlog 一个最大的区别，binlog 记录的是数据的逻辑变化，这也是为什么 redo log 可以用来 crash recovery，而 binlog 不能的原因之一
  • redo log 是存储在磁盘的，用于 crash recovery 后修正数据的，也就是我们常说的故障恢复，比如掉电，宕机等等
  • redo log 默认有两个文件
  • redo log 是循环写的（circular）

根据最后两个小点，我们大致可以画出这样一个图：

redo log

另外还有两个参数：

  • innodb_log_file_size：设置每个 redo log 文件的大小，默认是 50331648 byte，也就是 48 MB
  • innodb_log_files_in_group：设置 redo log 文件的数量，默认是 2，最大值是 100

我们常说事务具有 ACID 四个特性，其中 D（durability），数据持久性，意味着，一旦事务提交，它的状态就必须保持提交，不能回滚，哪怕你系统宕机了、奔溃了，你也要想办法把事务做到提交，把数据给我保存进去：

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash).

这确实很严格，但看起来也很好实现，就是每次都把数据写到磁盘，然后再告诉客户端，事务提交成功了。

但如果我要追求高性能呢？我要把数据写到内存呢？

所以我们说，innodb 在实现高性能写数据的同时，利用 redo log，实现了事务的持久性。

binlog

讲完了 redo log，我们再来聊聊 binlog。

还是这一条 update 语句：

update user set age = 18 where user_id = 345981

在这条 update 语句执行的时候，除了生成 redo log，还会生成 binlog。

binlog 和 redo log 有很多不同，有一点是一定要知道的，就是 redo log 只是 innodb 存储引擎的功能，而 binlog 是 MySQL server 层的功能，也就是说，redo log 只在使用了 innodb 作为存储引擎的 MySQL 上才有，而 binlog，只要你是 MySQL，就会有。

mysql 架构

binlog 里头记录了什么呢？上面有提到，和 redo log 记录数据的物理变化不同，binlog 记录的是数据的逻辑变化，比如上面这条 update 语句，你可以简单的认为，binlog 里头就是记录了这样一条 sql 语句，当然，它还会记录当前是在哪个数据库下的等等。

binlog 有三种格式，statement/row/mixed，有兴趣同学可以深入了解下：Binary Logging Formats

binlog 有什么作用呢？

MySQL 之所以把 binlog 放在了 server 层，说明 binlog 提供了一些通用的能力，比如：数据还原。

DBA 总说，他能把 MySQL 的数据还原到任意时刻，怎么还原？

假设你在周三晚上八点的时候，不小心把一张表的数据都清空了，怎么办？

这时候 DBA 就会找到最近的一次「全量备份」，然后重放从最近一次全量备份，到周三晚上八点，这段时间的 binlog，于是你的数据就还原回来了。

binlog 还有另一个作用：主从复制，主库把 binlog 发给从库，从库把 binlog 保存了下来，然后去执行它，这样就实现了主从同步。

当然，我们还能让自己的业务应用，去监听主库的 binlog，当数据库的数据发生变动时，去做特定的事情，比如进行数据实时统计。

两阶段提交

最后，那么当我执行一条 update 语句时，redo log 和 binlog 是在什么时候被写入的呢？这就有了我们常说的「两阶段提交」：

  • 写入：redo log（prepare）
  • 写入：binlog
  • 写入：redo log（commit）

为什么 redo log 要分两个阶段： prepare 和 commit ？redo log 就不能一次写入吗？

我们分两种情况讨论：

  • 先写 redo log，再写 binlog
  • 先写 binlog，再写 redo log

1、先写 redo log，再写 binlog

这样会出现 redo log 写入到磁盘了，但是 binlog 还没写入磁盘，于是当发生 crash recovery 时，恢复后，主库会应用 redo log，恢复数据，但是由于没有 binlog，从库就不会同步这些数据，主库比从库“新”，造成主从不一致

2、先写 binlog，再写 redo log

跟上一种情况类似，很容易知道，这样会反过来，造成从库比主库“新”，也会造成主从不一致

而两阶段提交，就解决这个问题，crash recovery 时：

  • 如果 redo log 已经 commit，那毫不犹豫的，把事务提交
  • 如果 redo log 处于 prepare，则去判断事务对应的 binlog 是不是完整的
    • 是，则把事务提交
    • 否，则事务回滚

两阶段提交，其实是为了保证 redo log 和 binlog 的逻辑一致性。

未完待续

总结一下：

  • redo log： innodb 在实现高性能写数据的同时，利用 redo log，实现了事务 ACID 中的D，持久性
  • binlog：MySQL 的数据还原、主从复制，都依赖 binlog 来实现
  • 两阶段提交：为了保证 redo log 和 binlog 的一致性

看似一条简单的 update 语句，MySQL 在这背后其实做了很多事情。

MySQL 是一个把单机性能发挥到极致的数据库，这也是为什么出现了那么多分布式数据库，MySQL 依然是很多公司的首选的原因吧。

当然这篇文章也只是个引子，很多细节，还没有展开。

比如看完两阶段提交，你们可能还有疑问，为什么不能先写 redo log，再写 binlog，如果发生 crash，重启后判断 binlog 是不是完整的，不完整，则回滚事务不就好了？

其实两阶段提交是经典的分布式系统问题，很多分布式系统也在用，包括上面讲的两阶段提交也只是一个粗略的提交过程，拆分的再细一点，应该是这样：

mysql two-phase commit

我们后面可以再深入研究下。

还有，每次事务提交，redo log 都是要写磁盘的，MySQL 怎么优化 redo log 的写入？

组提交、LSN 是什么？

最后再贴一张 innodb 架构图：

innodb 架构

今天其实只讲了这张图的右边的 redo log，其他的像 change buffer、double write buffer、undo log、log buffer 等等，都是些什么？

传送门：柳树：MySQL 的 InnoDB 存储引擎是怎么设计的？

你会发现，OMG，MySQL 怎么这么复杂？

大概是，越是看起来运转顺畅的系统，背后越是有复杂的机制来支撑吧。

别看人家看起来很轻松，其实人家背后很努力。

 

posted on 2020-10-07 23:51  ym65536  阅读(145)  评论(0)  编辑  收藏  举报