浅谈MySQL InnoDB索引

基于MySQL 5.6.16

前言

一次内存访问、SSD 硬盘访问和SATA 硬盘随机访问的时间分别约是_______。

A 几微秒，几毫秒，几十毫秒

B 几微秒，几毫秒，几十毫秒

C 几十纳秒，几十微秒，几十毫秒

D 几十纳秒，几十微秒，十几毫秒
以上腾讯2017实习生题目，答案为C。

当前互联网时代，性能尤为重要，性能差即意味着不可用。既然内存性能最好，是否可以将数据全部加载在内存中？

2018年12月，“美光旗下品牌英睿达（Crucial）宣布已经开始出货自家容量最高、速度最快的服务器级内存，128GBDDR4－4266LRDIMM，一条就要3999美元，约合人民币2.65万元。”

大数据时代，数据随随便便就上T，基本成本、容量等方面考虑，无法将数据全部加载入内存。由于无法全部装入内存，则必然依赖磁盘存储。而内存的读写速度是磁盘的成千上万倍（与具体实现有关），因此，存储的核心问题是“如何减少磁盘读写次数”。

数据结构

哈希表

Hash table，也叫散列表， 是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。哈希表最大的优点，就是把数据的存储和查找消耗的时间大大降低，几乎可以看成是常数时间（不考虑hash冲突的情况下，时间复杂度为O(1)）。

• 无序性，导致无法范围查找和索引排序。 >、<、between等范围查询无法使用索引。
• 对完整的key计算hash，所以不支持部分匹配。无法使用like 'hash%'进行前缀匹配。
• 当产生hash碰撞的时候，数据库要遍历拉链中所有的行指针，逐个取出数据行进行比较，数据量越大，冲突越多，查找代价越高。

由于hash索引的上述缺点，所以实际使用hash索引的情况很少，MySQL的Memory存储引擎和NDB分布式存储引擎使用了hash结构索引。
当一个数据结构，在支持第一点（顺序存储）的情况下，对第二点（部分匹配）有一个天然的增强：所有前缀一样的值都是按顺序存储在一起的，当我们使用左前缀查询时，从第一个符合前缀条件的值开始扫描，扫到第一个不符合规则的值即可停止，不用扫描全部内容。

树

• 二叉搜索树（BST）：读/写平均O(log2(n))次；如果树不平衡，则最差读/写O(n)次
• 自平衡二叉搜索树（AVL）：在BST的基础上加入了自平衡算法，读/写最大O(log2(n))次
• 红黑树（RBT）：另一种自平衡的查找树，读/写最大O(log2(n))次

BST、AVL、RBT很好的将读写次数从O(n)优化到O(log2(n))；其中，AVL和RBT都比BST多了自平衡的功能，将读写次数降到最大O(log2(n))。

假设使用自增主键，则主键本身是有序的，树结构的读写次数能够优化到树高(无序的数据会导致插入位置前后的节点移动)，树的高度越低读写次数越少；自平衡保证了树结构的稳定。如果想进一步优化，可以引入B/B+树。

B+树

示例图：

如果抛开维护操作，那么B树就像一棵“m叉搜索树”（m是子树的最大个数），时间复杂度为O(logm(n))。然而，B树设计了一种高效简单的维护操作，使B树的深度维持在约log(ceil(m/2))(n)~logm(n)之间，大大降低树高。与单纯的算法不同，磁盘IO次数才是更大的影响因素。B树与AVL的时间复杂度是相同的，但由于B树的层数少，磁盘IO次数少，实践中B树的性能要优于AVL等二叉树，例如上图中查找“10”数据，只需要经过三次磁盘I/O即可。
另外，B树对局部性原理非常友好：

• 由于存储介质的特性，磁盘本身存取就比主存慢很多，对磁盘来说，能够最大化的发挥磁盘技术特性的使用方式是：一次性的读取或写入固定大小的一块数据，并尽可能的减少随机寻道这个操作的次数。为了提高效率，减少磁 盘I/O，磁盘往往不是严格按需读取，而是每次都会预读，无论是读取一行还是多行，都会将该行或者多行所在的页全部加载进来，然后再读取对应的数据记录。这样做的理论依据是计算机科学中著名的局部性原理：“空间局部性(sequential locality)”，其理论基础是认为数据往往是连续访问的，当一个数据被用到时，其附近的数据也通常会马上被使用。

• 预读：对于每个文件的第一个读请求，系统读入所请求的页面并读入紧随其后的少数几个页面（X86的Linux中一个标准页面大小是4KB），这时的预读称为同步预读。对于第二次读请求，如果所读页面不在 Cache 中，即不在前次预读的页中，则表明文件访问不是顺序访问，系统继续采用同步预读；如果所读页面在 Cache 中，则表明前次预读命中，操作系统把预读页的大小扩大一倍，此时预读过程是异步的，应用程序可以不等预读完成即可返回，只要后台慢慢读页面即可，这时的预读称为异步预读。任何接下来的读请求都会处于两种情况之一：

  • 若不在cache，操作系统从磁盘中读取对应的数据页，并且系统还会将该数据页之后的连续几页也一并读入到cache中，再将应用需要的数据返回给应用。此情况操作系统认为是跳跃读取，属于同步预读。
  • 若命中cache，相当于上次缓存的内容有效，操作系统认为顺序读盘，则继续扩大缓存的数据范围，将之前缓存的数据页往后的N页数据再读取到cache中，属于异步预读。

数据库系统的设计者巧妙利用了操作系统以上特性，将一个节点的大小设为等于一个页（MySQL InnoDB的页为16KB），新建节点时，InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块（磁盘块为512B）来达到页的大小16KB，保证一个节点物理上也存储在一个页里。在把磁盘数据读入到磁盘时会以页为基本单位，这样每个节点只需要一次I/O就可以完全载入。
MySQL 在执行读操作时，会先从数据库的缓冲区中读取，如果不存在与缓冲区中就会尝试从内存中加载页面，如果前面的两个步骤都失败了，最后就只能执行 I/O 从磁盘中获取对应的数据页。

B+树特点：

• 非叶子节点不存储数据，且是叶子节点的索引（稀疏索引）。

  • 保证查找性能的稳定：如果非叶子节点也存储数据，则最好情况下查找到根节点，最坏情况下查找到叶子结点。
  • 单个节点可以存储更多的数据，一次性读入内存中的数据也就越多，相对来说I/O次数也就减少了。
• 叶子节点存储数据（稠密索引），各个数据页之间组成一个有序链表（MySQL InnoDB是双向链表），便于范围/全表扫描，也便于节点分裂。

下图展示了“叶子节点是否存储数据”情况下，两颗树的差别，为了显示的更直观点，假设每个节点只够存储两条数据。
可以看到上面那颗树最多需要三次磁盘I/O，而下面的那棵树需要两次。

例子：
新闻文章的内容通常都很长，所以内容字段类型一般都是大文本类型。平时更多的是显示文章列表，如果显示的列表中仅需要展示标题/副标题，而不需要展示内容片段的话，可以将内容字段单独抽出一张表。剥离内容字段后的文章表记录相对小了很多，这样在获取文章列表时也会有比较少的I/O。
为什么like只能左匹配？
因为MySQL InnoDB使用原数据格式进行存储（函数索引存的是表中的数据应用函数后得到的数据），加上B+树的有序特性，如下简略图：

如果sql执行“ like '林%' ”，可以在查找到第一个“林”之后，向后一直取得数据，直到遇到第一个非“林”开头到数据，“ like '%1%' ”则不行。
注：
以上列出了了种种B/B+树做索引的优点，不代表B/B+树就是最适合做索引，还有其他适合做索引的数据结构/存储引擎，比如LSM树，两者各有优缺点，适用不同的场景。

索引

主键索引

索引可以分为主键索引（聚簇索引），非主键索引（非聚簇索引）。

• 主键索引：每个表都有一个索引是存储了所有数据的，这个索引既主键索引，一般建立在主键上，如果表没有主键，则为自增键，或隐藏的Rowid列。

  • 主键索引在插入新行和更新主键时，可能引起“页分裂”问题，导致性能下降。所以如果每次插入的主键值都是最大的（递增主键），由于都是在末尾插入，可以减少数据移动和页分裂。但是由于B+树“50%分裂策略”会造成空间利用率的问题（索引页面空间利用率在50%左右），目前所有的数据库都针对B+树索引的递增/递减插入进行了优化，详情见《从MySQL Bug#67718浅谈B+树索引的分裂优化》或《数据库索引算法——B树与B+树》。
    
    

• 非主键索引：不存储数据，仅存储索引数据和主键值。非唯一索引以“索引信息+主键”来保证键的唯一性。

  • 因为不存储数据，所以占用空间会比主键索引小很多，I/O也会小。统计一个表行数是(count)，一些优化器会选择表中最小的索引来作为统计的目标索引，性能也相应更快。

  • 查询时，需要通过对应的主键，做“回表”查询，即多一次I/O。
    当sql执行“ where name = 'n' ”（name为非主键索引）时：
    

    覆盖索引

    索引包含查询中所需要的全部数据列，为覆盖索引。
    例如：对于“ SELECT username, age FROM users WHERE username='林' ”， (username, age) 就是该查询的一个覆盖索引。
    覆盖索引能够避免“回表”查询。
    建立覆盖索引，查询速度可以提升数十倍，甚至上千倍，为什么回表查询这么耗时？

回表查询

磁盘I/O

HDD

• 硬盘内部主要部件为磁盘盘片、传动手臂、读写磁头和主轴马达。实际数据都是写在盘片上，读写主要是通过传动手臂上的读写磁头来完成。实际运行时，主轴让磁盘盘片转动，然后传动手臂可伸展让读取头在盘片上进行读写操作。磁盘物理结构如下图所示：
  
  
  

• 磁盘读取时间

  • 寻道时间，表示磁头在不同磁道之间移动的时间。
  • 旋转延迟，表示在磁道找到时，中轴带动盘面旋转到合适的扇区开头处。
  • 传输时间，表示盘面继续转动，实际读取数据的时间。

• 顺序读写和随机读写对于机械硬盘来说为什么性能差异巨大？

  1. 随机读写：操作的磁盘地址不是连续的；需要多次寻道和旋转延迟，而这个时间可能是传输时间的许多倍。
  2. 顺序读写：操作的磁盘地址是连续的；主要时间花费在了传输时间，不需要寻道；磁盘会预读，预读即在读取的起始地址连续读取多个页面。

SSD
固态驱动器(solid state drives SSDs)没有旋转磁盘设备，全部都是采用闪存。SSD内部维护了一张映射表（Mapping Table），HOST每写入一个Host Page，就会产生一个新的映射关系，这个映射关系会加入（第一次写）或者更改Mapping Table；当读取某个Host Page时， SSD首先查找MappingTable中该Host Page对应的Physical Page，然后再访问Flash读取相应的Host数据。与传统的机械磁盘相比，省去了寻道时间和旋转时间。
SSD内部一般使用NAND Flash来作为存储介质，其逻辑结构如下：

SSD中一般有多个NAND Flash，每个NAND Flash包含多个Block，每个Block包含多个Page。由于NAND的特性，其存取都必须以page为单位，即每次读写至少是一个page，通常地，每个page的大小为4k或者8k。另外，NAND还有一个特性是，其只能是读或写单个page，但不能覆盖写入某个page，必须先要清空里面的内容，再写入。由于清空内容的电压较高，必须是以block为单位。因此，没有空闲的page时，必须要找到没有有效内容的block，先擦写，然后再选择空闲的page写入。
Block中的数据变老或者无效，是指没有任何映射关系指向它们，用户不会访问到这些FLASH空间，它们被新的映射关系所取代。比如有一个Host Page A，开始它存储在FLASH空间的X,映射关系为A->X。后来，HOST重写了该Host Page，由于FLASH不能覆盖写，SSD内部必须寻找一个没有写过的位置写入新的数据，假设为Y，这个时候新的映射关系建立：A->Y，之前的映射关系解除，位置X上的数据变老失效，我们把这些数据叫垃圾数据。
随着HOST的持续写入，FLASH存储空间慢慢变小，直到耗尽。如果不及时清除这些垃圾数据，HOST就无法写入。SSD内部都有垃圾回收机制，它的基本原理是把几个Block中的有效数据（非垃圾数据，上图中的绿色小方块表示的）集中搬到一个新的Block上面去，然后再把这几个Block擦除掉，这样就产生新的可用Block了。

上图中，Block x上面有效数据为A,B,C，Block y上面有效数据为D,E,F,G，红色方块为无效数据。垃圾回收机制就是先找一个未写过的可用Block z，然后把Block x和Block y的有效数据搬移到Block z上面去，这样Block x和Block y上面就没有任何有效数据，可以擦除变成两个可用的Block。

• 读：由于预读的原因，SSD的顺序读仍然比随机读要快的多。

• 写：写相同数据量的情况下：

  • 随机写可能会导致大量无效页面的数据分散在仍然包含有效数据的页面中。在对这些块进行垃圾回收期间，必须将所有有效数据移动到其他的块。

  • 而当文件被顺序写时，如果数据无效，通常都是整个块无效，因此不需要移动数据。有时，文件的一部分可能与另一个文件共享一个块，但平均而言，只需要移动大约一半这样的块，这使得它比随机写入的块的垃圾收集快得多。

    select * from user where name like '林%'

    假设按照name索引过滤剩下600条数据。则该索引总共会产生1次随机访问（查找第一个匹配的节点），和599次顺序访问（按着第一个匹配的节点顺序往下查找匹配）。因为该索引中的列并不能满足需要，所以会做“回表”查询，每一个索引行都会产生一次随机访问。以上查询总共有601次随机访问和599次顺序访问。
    注：MySQL会对特定的查询做优化，如MySQL5.6之后引入MMR，以上假设为未被优化情况。
    

为什么分辨度不高的列（如性别）不适合建索引？
每一次回表都是耗时都随机访问，索引查找加上回表的性能并不会优于全表扫描。MySQL查询优化器也会自动优化成全表扫描。

案例

SELECT * FROM t_terminal WHERE token LIKE 'hw%' LIMIT 1006000, 5

设备表t_terminal数量4800W，token字段为索引字段，符合筛选条件的数据为370W。MySQL版本5.6.29-log。
以上SQL查询耗时3.5秒：

• MySQL执行limit时，会扫描所有limit的数据项(100605)，再跳过前面数据(100600)。

• 会执行100605次回表随机访问。
  如果优化成：

  SELECT * FROM t_terminal t1 
  INNER JOIN (
  SELECT id FROM t_terminal  WHERE token LIKE 'hw%' LIMIT 1006000, 5
  ) t2 USING (id);

  查询耗时0.5秒：子查询中会先筛选出5条limit结果，也就是只有5条数据做回表查询。

ORDER BY

有时候排序也会成为性能杀手，例如查出来的结果集很大，对结果集做排序也会耗去不少时间。
按照索引顺序扫描得出的结果自然是有序的，将排序字段加入到索引组中，以避免对结果重排序，减少磁盘I/O和内存的使用。

SELECT id, name FROM user WHERE name = '林' ORDER BY age ASC

以上SQL建立组合索引（name, age），利用索引树已经排好序的特性，查询结果无需再次排序。

思考：

SELECT id, name FROM user WHERE city = '深圳' AND name LIKE '林%' ORDER BY age ASC

A（city, name, age）和 B（city, age, name）以上哪种组合索引更合适？

• A：在name过滤之后的结果集，无法顺序获取age数据（即所有‘林’开头的name数据没有凑在一起），只能在内存中对结果集做重排序。
• B：在age排序后，无法顺序获取name数据，只能在内存中对结果集做过滤。

一个 SQL 查询中同时拥有范围谓词和 ORDER BY 时，我们能够做的就是在这两者之间做出选择。近几年排序速度已经提升很多，大多数情况下A和B一样快，甚至A比B更快。
但是如果：

• 索引筛选结果集很大。
• 程序需要的是其中的部分数据，即SQL加上limit 100。

那么B会比A快很多（很大的可能，除非符合的数据都排在后面），因为A需要扫描出所有符合name的条件，再按age排序，之后才limit，而B直接找到前100个符合条件的即可。

GROUP BY

group by操作在没有合适的索引可用的时候，通常先扫描整个表提取数据并创建一个临时表，然后按照group by指定的列进行排序(也就是group by之后的数据是有序的，但是Mysql 8.0之后不再隐式排序，详见Group by隐式排序，一个优美的BUG)。在这个临时表里面，对于每一个group的数据行来说是连续在一起的。完成排序之后，就可以发现所有的groups，并可以执行聚集函数（aggregate function）。在没有使用索引的时候，需要创建临时表和排序，所以制约group by性能的问题，就是临时表+排序，尽量减少磁盘排序，减少磁盘临时表的创建，是比较有用的处理办法。在执行计划中通常可以看到“Using temporary; Using filesort”。

CREATE TABLE `t1` (
​
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
KEY `idx_g` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
mysql> explain extended select c1,c2  from t1 group by c2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: idx_g
          key: idx_g
      key_len: 15
          ref: NULL
         rows: 15441
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort

松散索引扫描(Loose Index Scan)

松散索引扫描不需要连续的扫描索引中得每一个元组，扫描时仅考虑索引中得一部分。当查询中没有where条件的时候，松散索引扫描读取的索引元组的个数和groups的数量相同。如果where条件包含范围预测，松散索引扫描查找每个group中第一个满足范围条件，然后再读取最少可能数的keys。
如果查询能够使用松散索引扫描，那么执行计划中Etra中提示“ using index for group-by”。

mysql> explain select c1, min(c2)  from t1 group by c1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: idx_g
          key: idx_g
      key_len: 10
          ref: NULL
         rows: 15442
        Extra: Using index for group-by

松散索引扫描只需要读取很少量的数据就可以完成group by操作，因而执行效率非常高。

松散索引条件：

• 查询在单一表上。
• group by指定的所有列是索引的一个最左前缀，并且没有其它的列。比如表t1（ c1,c2,c3,c4）上建立了索引（c1,c2,c3）。如果查询包含“group by c1,c2”，那么可以使用松散索引扫描。但是“group by c2,c3”(不是索引最左前缀)和“group by c1,c2,c4”(c4字段不在索引中)。
• 如果在选择列表select list中存在聚集函数，只能使用 min()和max()两个聚集函数，并且指定的是同一列（如果min()和max()同时存在）。这一列必须在索引中，且紧跟着group by指定的列。比如，select t1,t2,min(t3),max(t3) from t1 group by c1,c2。
• 如果查询中存在除了group by指定的列之外的索引其他部分，那么必须以常量的形式出现（除了min()和max()两个聚集函数）。比如，select c1,c3 from t1 group by c1,c2不能使用松散索引扫描。而select c1,c3 from t1 where c3 = 3 group by c1,c2可以使用松散索引扫描。
• 索引中的列必须索引整个数据列的值(full column values must be indexed)，而不是一个前缀索引。比如，c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。(前缀索引，与上面提到的索引的最左前缀是不同的)

自从5.5开始，松散索引扫描可以作用于在select list中其它形式的聚集函数，除了min()和max()之外，还支持：

• AVG(DISTINCT), SUM(DISTINCT)和COUNT(DISTINCT)可以使用松散索引扫描。AVG(DISTINCT), SUM(DISTINCT)只能使用单一列作为参数。而COUNT(DISTINCT)可以使用多列参数。

  紧凑索引扫描(Tight Index Scan)

  紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于：紧凑索引扫描需要在扫描索引的时候，读取所有满足条件的索引键（注意，是索引健，不包含索引树内没有的数据），然后再根据读取出的数据来完成 GROUP BY 操作得到相应结果。
  如果紧凑索引扫描起作用，那么必须满足：在查询中存在常量相等where条件字段（索引中的字段），且该字段在group by指定的字段的前面或者中间。
  紧凑索引扫描同样可以避免额外的排序操作，但是效率低于松散索引。使用紧凑索引扫描，执行计划Extra一般显示“using index”，相当于使用了覆盖索引。

  mysql> explain extended select c1,c2  from t1 where c1=2 group by c2 \G
  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
  possible_keys: idx_g
          key: idx_g
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using where; Using index

  在MySQL中，MySQL Query Optimizer首先会选择尝试通过松散索引扫描来实现GROUP BY操作，当发现某些情况无法满足松散索引扫描实现GROUP BY的要求之后，才会尝试通过紧凑索引扫描来实现。当MySQL Query Optimizer发现仅仅通过索引扫描并不能直接得到GROUP BY的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。当无法使用索引完成GROUP BY的时候，由于要使用到临时表且需要filesort，所以我们必须要有足够的sort_buffer_size来供MySQL排序的时候使用，而且尽量不要进行大结果集的GROUP BY操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降。此外，GROUP BY如果在无法利用到索引的情况下想避免filesort操作，可以在整个语句最后添加一个以null排序（ORDER BY null）。

DISTINCT

DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。当然，如果我们在进行DISTINCT的时候还使用了GROUP BY并进行了分组，并使用了类似于MAX之类的聚合函数操作，就无法避免filesort了。

扩展

explain原理

explain并没有真的去执行sql语句从而得出行数，而是进行了某种预估。

• mysql-5.5之前：首先找到查询第一个记录所在的page（记为PLeft），统计PLeft里的记录数（记为Records_PLeft），之后找到最后一个记录所在的page（记为PRight），统计PRight的记录数（Records_PRight），之后将Records_PLeft与Records_PRight取平均，最后乘以总共的page数目（记为Page_Num）。公式如下：Rows = ((Records_PLeft + Records_PRight) / 2) * Page_Num。
• mysql-5.5之后：由于采样的page数太少了，只采样了边界2个，存在比较大的偏差，新版本增加采样数目，比如采样10个page，具体来说，mysql除了边界2个外，还沿着左侧page往右连续查找8个page，如果总的page数目小于等于10个，那么预估的Rows和真实的Rows一致。公式如下：Rows = ((Records_PLeft + Records_P1 + Records_P2 + ... + Records_P8 + Records_PRight) / 10) * Page_Num。

增加增加采样数目，一定程度上缓解了有偏的问题，但是不准确还是存在的。

参考：
《浅谈Mysql的B树索引与索引优化》
《数据库中的索引》
《程序员需要知道的SSD基本原理》
《InnoDB一棵B+树可以存放多少行数据？》
《MySQL松散索引扫描与紧凑索引扫描》
《mysql 原理：explain》
《MySQL order by，group by和distinct原理》