MySQL 组合索引：用好，性能提升 10 倍

---

前言

在实际业务中，我们会遇到很多复杂的场景，比如对多个列进行查询。这时，可能会要求用户创建多个列组成的索引，如列 a 和 b 创建的组合索引，但究竟是创建（a，b）的索引，还是（b，a）的索引，结果却是完全不同的

---

一、组合索引是什么？

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，是对多个列进行排序；组合索引既可以是主键索引，也可以是二级索引。

以组合索引(a, b, c)为例：
组合索引树的每个节点是由(a, b, c)构成的key，其中a有序，b和c无序；当a相同后，b有序；当b都相同, c有序

二、正确使用组合索引

最左匹配原则

假设：现有组合索引(a, b , c)

可以走组合索引的情况有：

• 包含查询条件 a 字段
• 查询条件包含 a b
• 查询条件包含 a b c字段

反例：b, c或者 b c等不走组合索引

相当于定义了(a)、(a, b)、(a, b, c)三个索引

where a = xxx; // 使用组合索引中的a
where a = xxx and b = xxx; // 使用组合索引中的(a, b)
where a = xxx and c = xxx; // 使用组合索引中的(a)
where a = xxx and b = xxx and c = xxx; // 使用组合索引中的(a, b, c)
where a = xxx and b like "%xxx%" and c = xxx; // 使用组合索引中的(a)
where a = xxx and b like "xxx%" and c = xxx; // 使用组合索引中的(a, b)
where a = xxx and b > xxx and c = xxx; // 使用组合索引中的(a, b)
where b = xxx and c = xxx; // 不使用索引
where c = xxx; // 不使用索引

注意：当遇到范围查询(>、<、between、like)会停止匹配

冗余定义索引

比如一张表定义了索引(a)、(b)、(a, b, c)三个索引

以上就存在多余的索引(a)，根据组合索引最前匹配原则，通过查询条件a是可以使用组合索引(a, b, c)

三、组合索引应用场景

避免额外排序

在实际的业务场景中，通常会遇到使用查询条件后又需要排序的需求。如果过滤后的数据量大，通常都会使用文件排序，这样一来处理成本就高了不少。如果通过组合索引解决排序问题，将是一个不错的方法

例如：要查询用户列表，条件是 按城市 查询，并且按用户创建时间倒序排。

通常情况下我们的处理方式是，将城市类别创建索引；这样以上的查询就会命中索引，但通常情况下一个城市的用户非常多，按时间倒序排列就需要额外的文件排序来完成这个操作；如果按城市搜索的条件下，数据的时间本身就是有序的，那就可以省去排序这个操作

因此，可以创建组合索引 idx_city_create_time(city, create_time)。需要注意的是，如果没有city作为查询条件，仅按时间排序是不会走该索引，组合索引遵循 “最左匹配原则”

避免回表，性能提升10倍

回表： SQL 需要通过二级索引查询得到主键值，然后再根据主键值搜索主键索引，最后定位到完整的数据。

如果二级组合索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖

一张表的索引不能超过 5 个？

引用姜承尧大佬的话：“我看见网上一些所谓的 MySQL“军规”中写道“一张表的索引不能超过 5 个”。根本没有这样的说法，完全是无稽之谈。

在我看来，如果业务的确需要很多不同维度进行查询，那么就该创建对应多索引，这是没有任何值得商讨的地方。”

真正在业务上遇到的问题是： 由于业务开发同学对数据库不熟悉，创建 N 多索引，但实际这些索引从创建之初到现在根本就没有使用过！因为优化器并不会选择这些低效的索引，这些无效索引占用了空间，又影响了插入的性能。

总结

组合索引也是一颗 B+ 树，只是索引的列由多个组成，组合索引既可以是主键索引，也可以是二级索引。组合索引的三大优势：

• 覆盖多个查询条件，如（a，b）索引可以覆盖查询 a = ? 或者 a = ? and b = ?；
• 避免 SQL 的额外排序，提升 SQL 性能，如 WHERE a = ? ORDER BY b 这样的查询条件；
• 利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升 SQL 的查询性能，用好索引覆盖技术，性能提升 10 倍不是难事。