5. MySQL InnoDB的索引与优化

专栏地址：

MySQL系列文章专栏

---

---

1. 索引与表的维护

为什么要重建索引

索引树会因为删除、页分裂等原因，导致数据页有了空洞。重建索引会创建一个新的索引，将数据按顺序插入。使得，索引更加紧凑，数据页利用率更高。

场景

假设表T有索引k和主键索引id，依次使用drop 和add重新创建索引是否合适？

# 重建索引k
alter table T drop index k; 
alter table T add index(k);

# 重建主键
alter table T drop primary key; 
alter table T add primary key(id);

这种方式重建索引k是合适的，但是不适用于主键。无论删除主键还是创建主键，都会导致整个表重建，drop和add导致表重建了两次。正确的做法是使用alter table T engine=innoDB直接进行重建表。

表空间的回收

InnoDB对行记录的删除并不是真正的物理删除，而是对删除的行记录进行标记（delete falg），并将其占用空间放入PAGE_FREE列表中，以便后续复用。若整个页的记录都被删除掉了，则整个页都可以复用。

因此删除数据不会并使得表空间缩小，同时还会产生数据页空洞。若要对表空间进行收缩，可以使用alter table命令进行重建表。

2. 使用自增主键

追加插入，无页分裂

自增主键追加的插入模式，不会造成页分裂，也就不需要移动其它记录。

长度短

自增主键一般使用8字节的bigint，较业务主键长度更短:

• 二级索引的叶子节点数据量更短，整体占用空间更少；
• 聚集索引内部更加紧凑。

反例

KV场景，只有一个索引，该索引必须唯一。

3. 使用覆盖索引

3.1 回表

二级索引（辅助索引）的叶子节点保存的是聚簇索引，即主键。利用二级索引进行查询时，若不能获取到所有需要查询的字段，则需要一次额外的回表操作——得到主键之后，再到聚簇索引树中进行查找。

回表操作位于引擎层，Server层和引擎层的交互以行记录为单位。

SELECT * FROM t WHERE k BETWEEN 5 AND 7;需要执行几次树的搜索操作？

对K索引树执行了1次搜索操作，并依次读取了3、5、7三个值，对3、5进行了2次回表，搜索了2次聚集索引树。

3.2 利用覆盖索引减少回表

二级索引已经包含SELECT以及WHERE中所有的列时，称之为覆盖索引，此时不需要在进行回表操作。

覆盖索引能够显著提升性能，是一个常见的优化手段。对于高频的请求上建立覆盖索引，不需要再进行回表操作查询整行记录，减少语句的执行时间。

但是索引的维护也是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑。

可以使用覆盖索引的典型场景有：

1. 查询条件是主键，SELECT列是主键；
2. 查询条件是联合索引，SELECT列是主键或者联合索引的部分列。

3.3 InnoDB对回表的优化

3.3.1 MRR（Mutil-Range Read）

为了减少回表时的随机IO，将其转换成较为顺序的磁盘读取，InnoDB查询二级索引得到主键后，在回表前会对主键先进行排序在进行回表。InnoDB并不是得到所有索引后在回表（减少不必要的额外存储），MRR只能减少回表的随机IO，此外，即使对主键进行排序，也不能保证这些记录在连续的数据页上。

回表仍然是一行行的搜索主键，但是调整顺序可以加速磁盘读取。

此外，MRR还可以降低缓存池中数据页被替换的次数、批量处理对键值的查询操作。

使用MRR时，EXPLAIN的Extra列会看到Using MRR。

3.3.2 索引下推（Index Condition Pushdown，ICP）

在InnoDB不支持索引下推之前，使用索引查询时，首先根据索引查找到记录，然后Server层在根据WHERE条件进行过滤。

MySQL5.6之后对引擎层的接口能力进行了提升，可以将部分的WHERE过滤操作下推到存储引擎层，大幅降低了回表的次数和SERVER层fetch的记录数。

具体而言，在使用联合索引时，对于WHERE条件中符合最左前缀的部分可以用于进行索引查找，对于不满足最左前缀的部分（或者主键的部分？），也传递给存储引擎，用于进行过滤。

比如，有联合索引 (name, age)，使用SELECT * FROM t WHERE name like '张%' AND age = 10 AND ismale = 1在查找姓张且年龄为10岁的男孩，在没有索引下推的情况下，需要对每一个张字打头的记录进行回表，总共需要回表4次。

而在有索引下推的情况下，InnoDB对于从二级索引树上读取到的记录，首先对索引中包含的age字段进行判断，过滤掉不符合条件的记录后，再进行回表，总共回表了2次。

PS

对于WHERE中含有多个索引的情况下，MySQL一般只会选择使用其中的一个索引。但是，MySQL还有索引合并优化，对同一个表的多个索引树同时进行条件扫描，并将结果进行合并（并集、交集），EXPLAIN中type列为index_merge。

4. 使用联合索引

联合索引是对表上的多个列建立一个索引，联合索引是有序的，索引的键值是按照列的顺序依次进行排序的。使用联合索引可以减少索引的个数、减少排序操作。

最左前缀

能够利用联合索引进行查询的前提是，条件要符合最左前缀原则——最左前缀可以是前N个列也可以是前M个字符。

ps

对于二级索引上不符合最左前缀的查询，比如 ‘%j‘，虽然不能利用该索引进行快速查找，但是可以通过扫描二级索引树而不是扫描聚集索引树加快检索速度。

建立原则

建立联合索引时，如何安排内部列的顺序呢？如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是应该优先考虑的。

优点

建立联合索引可以减少索引的个数，同时由于索引的键值是按照列的顺序进行列排序，在很多情况下可以减少排序操作。

若业务中存在高频的需要排序的查询模式时，可以考虑通过建立冗余的联合索引来减少排序操作。

比如一个表有 (a, b)联合索引，同时也对c建立列索引：

PRIMARY KEY (`a`,`b`)
KEY `c` (`c`)

此时，对于如下的查询模式，是可以走索引的：

WHERE c ORDER BY a
WHERE c ORDER BY b

是否需要建立 ca 和 cb 两个冗余索引，以减少排序操作？

KEY `ca` (`c`, `a`)
KEY `cb` (`c`, `b`)

首先，ca是肯定不需要的，因为主键是ab，对于二级索引 c，其叶子节点记录的主键ab是有序的。其排序规则是c a b因此可以支持c、a排序操作。

对于cb，考虑c的选择性，如果重复率不高，那么排序的成本可以忽略。如果排序较为耗时，那么可以建立冗余索引cb。cb索引树的排序是c b a，非叶子节点记录c b并以此排序，叶子节点记录c b a。

5. 尽量使用普通索引而不是唯一索引

5.1 性能对比

在不影响业务逻辑的前提下，从性能上考虑，普通索引较唯一索引更优。

从查询上来看，两者的性能差距微乎其微；在DML操作上，普通索引能够利用Change Buffer缓存写操作，从而减少随机IO读、实现操作合并。具体来说：

查询操作

对于唯一索引，利用该索引进行检索时，在二级索引树上查询到第一条满足条件的记录后，由于唯一索引定义了唯一性，就会停止继续检索。

对于普通索引，在查询到第一条满足条件的记录后，需要继续往下读取，直到碰到不满足条件的记录。

由于InnoDB是按页读取的，默认的页大小是16k，在二级索引树上，一个数据页可以存储上千条记录（存储二级索引及主键）。那么，下一条记录大概率在同一个数据页中，而内存的一次读取和比较的开销几乎忽略不计。

DML操作

由于InnoDB利用缓存池buffer pool来加速数据页的访问速度，对数据页的修改也会先在缓存池中进行更新，并在随后适当的时候对脏页进行刷新。当待修改的页不在缓存，若将页先从磁盘中读取到缓存池中再进行修改，会产生较高的随机IO开销。为此，InnoDB引入了change buffer，将对非唯一辅助索引（二级索引）的操作缓存下来，以此减少随机读IO，并达到操作合并的效果。

所以，对于目标页在缓存池的情况，两者均是内存操作，只是唯一索引会多一次唯一性判断操作。

而对于目标页不在缓存池的情况，唯一索引由于需要判断唯一性，所以必须将数据页从硬盘读取到内存，产生了随机IO读。而普通索引则直接将更改记录于change buffer中即可。

5.2 如何保证数据不重复插入

唯一索引

利用唯一键保证数据唯一性，在存在逻辑删除的场景下，使用 业务字段 + 时间戳共同组成唯一索引。数据未删除时时间戳为-1，删除后为删除时间。

使用on duplicate key update可以在发生冲突时执行更新操作。

先检查再插入

在可重复读的隔离级别下，在同一个事务中，先SELECT..LOCK IN SHARE MODE或者SELECT..FOR UPDATE对将要插入的数据或范围加锁，若不存在再执行插入语句。主要利用了InnoDB的Nextkey锁。

分布式锁

利用Redis等实现分布式锁来保证插入操作的唯一性。

6. MySQL为什么会选错索引

在不断删除历史数据 / 新增数据、长事务中进行删除等场景下，会导致统计信息不准确、存在冗余的删除数据页，影响扫描行数的预估，进而导致优化器选择错误的索引。

6.1 影响优化器选择索引的因素

优化器位于SERVER层，用于生成执行计划：决定使用哪个索引、各个表连接顺序、IN查询优化（将IN进行排序然后使用二分法判断记录是否满足条件）、等值传播（WHERE条件同样适用于与其关联的列）、使用等价变换规则合并/减少WHERE条件等等，以最小的成本执行查询。

影响优化器成本计算的因素主要有：扫描行数、是否使用临时表、是否需要排序以及普通索引的回表代价等等。不准确的统计信息可能会导致成本计算错误，但即使统计信息准确，MySQL选择的执行计划也可能并不是最优的：计算IO成本时，因为Server层并不知道哪些数据页位于缓存池，无法准确知道实际的物理IO次数，也不知道是否为顺序读。此外，优化器对一些特定的查询优化具有一定的局限性。

6.1.1 扫描行数

什么是扫描行数

EXPLAIN中的rows字段表示优化器预估的扫描行数。

利用索引基数预估扫描行数

MySQL在真正执行SQL之前并不能准确的知道需要扫描多少条记录，而只能根据索引的选用情况和统计数据来进行估算。这个统计信息就是索引基数Cardinality，其表示着索引的区分度，即不同值的个数有多少个。知道了索引的基数，加上当前索引条件，就可以估算出大概有多少条满足条件的记录。

使用SHOW INDEX在Cardinality列可以看到每个索引的基数，Cardinality值约接近于行记录数，说明索引的选择性越高。

索引基数Cardinality如何统计

Cardinality的统计在引擎层完成，采用条件触发、采样统计的方式进行统计，尽量减少统计操作对数据库造成的压力。

默认情况下，InnoDB会随机选择辅助索引树8个叶子节点数据页，统计每个页不同记录的个数。然后计算平均值再乘以页面数就得到了整个索引的Cardinality值。

随着数据库表的持续更新，统计信息也不是固定不变的，但InnoDB并不是在每次发生变更的时候就及时更新，而是在表中的1 / 16的行发生变更的时候才会触发一次自动更新。

会什么会得到错误的扫描行数

一是统计信息的不准确，造成这种情况的原因是表数据频繁的修改，可以使用ALALYZE TABLE t重新计算一下统计信息。

二是在长事务中进行删除，若事务未提交，那么其旧的数据（页）就不能删掉，多余的数据（页）会使得扫描行数变多。解决办法就是减少长事务。

6.1.2 二级索引的回表代价

如果访问的数据量较小，优化器还是会优先选择二级索引，但当访问的数据占据整个表的较大部分，比如20%以上时，优化器会选择通过聚集索引来查找数据。主要考虑到二级索引的回表操作带来的随机IO成本要高于全表扫描的顺序IO成本。

虽然InnoDB有MRR，但只能减少回表的随机IO，而不能避免。

使用覆盖索引以减少回表。

6.2 索引选择异常的处理

统计信息不准确导致的问题，使用ALALYZE TABLE t重新计算一下统计信息。

使用FORCE INDEX来强行选择一个索引。

考虑修改SQL语句，引导MySQL使用我们期望的索引，比如加个ORDER BY。

新建一个合适的索引，或是删除误用的索引，如果其用处不大的话。

7. 如何给字符串字段加索引

前缀索引

使用前缀索引，占用空间更小，合理设置索引长度以平衡区分度和占用空间。但前缀索引查询无法使用覆盖索引特性。

若前缀索引区分度不够高，可以使用hash对字段进行散列再建立索引；或者可以利用字段的特性，比如字段的后半部分区分度高，那么可以使用倒序存储，再建立前缀索引。

8. 索引的失效场景及优化

8.1 索引失效场景

此处索引失效指的是无法利用索引进行查找，索引的另一种使用方式是对辅助索引树进行扫描，其较对聚集索引扫描的成本更低。

联合索引

不符合最左前缀原则：LIKE模糊查询以%开头会导致索引失效；abc索引，查询条件为ac，索引生效且c下推。

OR

OR两侧不全是索引。

索引操作

在索引上进行计算、类型转换、使用函数等等。

譬如在字符串索引未增加引号、关联查询中两个表字符集不一样导致的被驱动表索引增加函数操作。

8.2 高性能的索引策略

索引和锁

索引可以让查询锁定更少的行，降低锁开销、提高并发度。

独立的列

索引不能是函数或者表达式的一部分。

前缀索引和区分度

合理设置索引长度以平衡区分度和占用空间。

查询频率和区分度

在区分度高和区分度低但查询频繁的列上建立索引。

联合索引和字段顺序

合理设置联合索引及其顺序以减少索引数量、排序操作。

使用自增主键

长度短，追加的插入模式不会造成数据页分裂。

使用覆盖索引

减少不必要的回表。

使用普通索引

利用Change Buffer减少随机IO读。

利用索引进行排序

最好的索引既可以用于查找同时也可以用于排序。

避免使用多个范围查询

MySQL只能使用其中一个索引

ORDER BY列增加联合索引

避免排序

MySQL的排序有两种算法，请求的列较少时，则将所有字段放入缓存中进行快排，内存不够时，使用文件进行归并排序；请求的列较多时，则只将排序需要的列放入缓存，排序完成后，在进行回表得到所有列。

9. 如何优化慢查询

不要请求不必要的数据

不必要的记录、不必要的列。

扫描行数

扫描行数和返回行数比率应尽量接近于1，一般在1：1到1：10之间。

访问类型

EXLPAIN中的type列表示访问类型，从全表扫描、索引扫描、索引范围扫描、索引查找到常量引用，性能从差到好。

拆分查询

减少锁定的数据（譬如数据结转）、事务大小。

拆解关联查询

在程序中关联，缓存更高效、减少锁竞争

优化特定类型的查询

• 优化limit查询：覆盖索引+延迟关联
• 关联查询：在第二个表的ON列上建立索引

参考

《MySQL实战45讲》

《MySQL技术内幕（InnoDB存储引擎）》

《高性能MySQL》