联合索引与最左前缀匹配
本文内容
前言
在谈及到索引失效时，往往都会想到查询的条件是否满足最左前缀匹配。同时，面试也经常会问有关最左前缀匹配的 SQL，问你到底有没有走索引。所以把最左前缀匹配弄清楚是十分有必要的。
1. 联合索引是如何存放的
因为最左前缀匹配原则主要是与联合索引有关。所以我们先来看看联合索引是如何存放在 B+ 树中的。
我们知道，InnoDB 中非聚簇索引的叶子节点是不存放完整记录的，它只存放 索引列 + 主键。那么如果我有多个索引列（即联合索引），它会怎么样帮我存放这多个列呢？
假设我们有个表，id 为主键，有一个联合索引（name, age)，那么这颗 B+ 树的叶子节点长这样（非叶子节点就只有索引列）：
可以发现，联合索引（name, age）的排序规则是 先按照最左侧的 name 排序，name 相同才按照 age 排序。
2. 最左前缀匹配原则是什么
正是因为联合索引的排序规则，所以才会存在 最左前缀匹配原则，顾名思义，即 优先按照最左列进行索引的匹配。
还是用上面的例子，如果我的查询条件是 where age = 16，就无法使用到索引（不考虑索引覆盖），因为不满足最左前缀匹配原则（只有 name 相等时，age 才有序，而只用 age 查询，age 在全局上是无序的）。
不过需要注意，联合索引出现的位置无关紧要，因为优化器会做优化，例如 where age = 14 and name = 小C 也是可以使用到索引的。
还有一种情况 where name like '小%'，也称得上是满足最左前缀匹配，也能用上索引，查找到第一个符合 小 开头的记录，然后向后遍历，直到不满足为止。
可以发现，最左前缀匹配分为两种情况：
- 联合索引的最左 N 个字段；
- 字符串索引的最左 M 个字符。
3. 如何安排联合索引中列的顺序
由于最左前缀匹配原则的存在，联合索引中列的顺序就显得尤其重要了，那我们在建立联合索引的时候，该如何决策联合索引中列的顺序呢？
一、索引的复用能力
由于可以满足最左前缀匹配，所以当有了 (a, b) 这个联合索引后，一般就无需再单独给 a 建立索引了（单独使用 a 字段查询是可以使用到联合索引的）。
所以第一原则是：如果通过调整联合索引的顺序，可以少维护一个索引，那么这个联合索引往往就是需要优先考虑采用的。
二、空间的考虑
如果有时候，我们既需要 (a, b) 这个联合索引，通过又有基于 a、b 各自的查询呢？查询条件里只有 b 是无法使用联合索引的。
这个时候我们可以有两个方案：
- 方案一：联合索引 (a, b)，单列索引 (b)；
- 方案二：联合索引 (b, a)，单列索引 (a)。
这两个方案到底选哪个呢？这时我们就要 考虑空间 了。如果 a 字段占用的字节数比 b 大，那么肯定选择方案一嘛，这样单列索引 (b) 占用的空间更小，一页中能存储的记录自然也就更多。
三、索引区分度的考虑
索引区分度，即某个字段不同值的数量「÷」表的总行数，公式如下：
所以，查看某个字段的区分度可以使用如下 SQL：
SELECT COUNT(DISTINCE LEFT(column_name, length)) / COUNT(*) FROM table_name;其中：
LEFT函数是取column_name这个字段的前 length 个字符；
所以，在建立联合索引的时候，应该尽量把区分度大的字段放在前面，因为区分度大的字段不重复的数据多，那么每次过滤掉的数据就多，使得查询效率更高。
4. 什么情况下联合索引会失效
通过对最左前缀匹配的理解，我们可以得出几个简单的索引失效的情况。例如联合索引 (a, b, c)，那么索引失效的情况可能有：
where b = 1；where c = 2；where b = 3 and c = 4。
这是比较容易判断的，那只要使用了联合索引的最左 N 个字段，就一定会全都走索引吗？
显然不是，例如下面的一种情况：
- SQL 语句：
select * from table where a > 1 and b = 3; - 联合索引 (a, b)。
这句 SQL 语句中，字段 a，b 都使用到了联合索引吗？
首先可以确定一点，字段 a 是肯定使用到了索引的，因为它满足最左前缀匹配，能形成有序的扫描区间 (1, +∞)，只需要找到第一条 a > 1 的记录，然后向后查找即可。
那么 b 有没有使用到索引，就要看看它是否有序。我们知道只有当 a 相等时，b 才有序，而在 a > 1 的记录中，b 是无序的，所以 b 使用不到索引。
那如果 SQL 语句是 select * from table where a >= 1 and b = 3;，此时 b 字段也就能使用到索引了，因为 当 a = 1 时，b 字段是有序的。
所以同理，where a between 1 and 5 and b = 3; 也是能使用到索引的，因为 between 是包含两边的边界值的。
所以，判断某条查询语句是否走了索引，关键就看它是否能形成有序区间，能就可以走索引。
5. 参考文章
- 《MySQL 实战 45 讲》
- 小林 coding