攻克 MySQL—索引优化

0. 引子

在上一篇文章中，我们学习了索引的概念、使用场景、常用的数据结构、InnoDB 引擎的数据结构——B+树，以及 MySQL 中的各种索引的分类，例如主键索引、二级索引、普通索引、唯一索引等等。

仅仅了解这些概念，还不足以让我们在工作或面试中游刃有余，原因在于有几个点我们没有讨论：

1. 对于如何创建良好的索引？

2. 如何合理地使用索引？

3. 索引是不是一本万利，可以使用的免费午餐？

本文会针对上面这些问题，做进一步的总结。

​

1. 索引的代价

1.1 维护代价

每张 MySQL 的表都会有一个主键索引（没有指定主键的话，引擎本身也会默认用 rowId 作为主键），因此在一张 MySQL 的表中至少有一棵 B+树。如果开发者再创建 N 个二级索引，就需要再创建 N 棵 B+树，新增数据时不仅要修改主键索引，还需要修改这 N 个二级索引。

1.2 空间代价

虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间。

2. 合理使用索引

2.1 主键索引

在非 KV 场景下，最好创建一个代理键作为主键，这种主键的数据应该和应用业务无关（业务数据一般很难保障顺序插入），最简单的方式就是使用 AUTO_INCREMENT 自增列。这样可以保障数据行是按顺序插入的，可以极大减少页的分裂和碎片。

从性能方面考虑，使用 UUID 作为主键，有下面这些缺点

• 随机 IO：写入的目标页可能已经刷到磁盘上，并从缓存中删除，或者还没有被加载到缓存中，在写入之前需要先将目标页读取到缓存中；

• 分裂次数增加：因为写入是乱序的，InnoDB 不得不为新插入的数据挪动空间，移动的时候需要不断调整 B+树的结构

• 由于频繁的的页分裂，页的空间使用率会比较差，充满碎片

从存储空间方面考虑，UUID 的字段长度长于自增 ID，导致本身的主键索引空间变大，另外二级索引的叶子节点中存放的也是主键索引的 ID，因此二级索引的占用空间也会变大。

相关技巧

1. 创建表的时候使用与业务无关的自增 id 作为主键

2.2 覆盖索引

创建二级索引之后，可以通过索引快速查找到需要的数据，但是我们不可能为每个列都创建索引，因此会存在“回表”的情况。在 InnoDB 中的存储方式是聚簇索引，二级索引在叶子节点中存放了行的主键，如果二级索引的查询可以查到 SQL 中所需要的全部字段，就避免了回表。当发起一个被索引覆盖的查询时，在 EXPLAIN 的 Extra 列可以看到“Using index”的信息。

相关技巧

1. 避免使用 select *，指定明确的字段

2.3 最左前缀

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。最左前缀原则在字段之间和字段内部都生效：如果有多个字段联合组成的组合索引，则先比较左边字段的值，如果是单个字符串列的普通索引，则比较的时候也符合最左前缀匹配原则。

相关技巧

1. 范围查询放在最后

2. 按照组合索引的顺序写查询字段的顺序

3. 避免使用 like '%xxx'，尽量使用'xxx%'

2.5 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推的执行流程如下：

有索引下推的执行流程如下：

相关技巧

1. 写查询语句的时候，尽量写全面一点，方便索引下推

3. 面试问题

1. 一些建表规范要求建表语句里一定要有自增主键，为什么这么要求？

2. 什么情况下索引可能失效？如何避免索引失效？

3. 如何为字符串类型的字段创建索引？

4. 在建立联合索引的时候，如何安排索引内的字段顺序。

4. 参考资料

1. 《MySQL 实战》

2. 《Java 业务开发常见错误 100 例》

3. 《高性能 MySQL》