详解最左前缀法则+索引失效

1. 什么是最左前缀法则

最左前缀是在使用innodb存储引擎索引时，需要遵守的法则。在一个联合索引如idx(a, b, c)，执行查询SQL时，如果查询条件包含索引的最左前缀，那么可以使用联合索引加速查询。对于上述例子，最左前缀包括idx(a), idx(a, b), idx(a, b, c)

2. 为什么存在最左前缀

最左前缀涉及到联合索引如何构建这一问题。

我们构建如下图所示的数据库表。

我们构建a, b, c字段的联合索引，idx(a, b, c)。

索引构建的规则很简单，先按照a排序，如果a一致，则按照b排序。如果b一致，按照c排序。如果所有索引字段都一致，那么按照主键排序。

有上述规则，我们可以得到如下索引图

通过索引图，我们观察发现如下结论

• 在全局范围内，a字段（红框标记）全局有序
• 在全局范围内，b字段（橘色框标记）全局无序，但局部有序（从左往右数的第一个数据页）
• 在全局范围内，c字段（绿框标记）全局无序

由索引的构建规则，我们可以提炼非常关键的一条信息：右侧字段对应数据有序的前提是，左侧字段数据确定。

我们任然以索引图为例。b字段在全局的视角来看是没有顺序的。只有a字段确定下来，b字段才能有顺序。当a = Bill时，b字段对应的数据则呈现升序状态。同理，c字段要想有序，b字段必须确定下来

这就是为什么要遵守索引前缀法则。其核心原因就是联合索引创建时，需要优先满足左侧字段的有序性，然后才会考虑右侧字段

3. 索引失效情况

知道了为什么存在最左前缀法则，我们来分析一下什么时候联合索引会失效。

3.1 查询条件未添加最左侧列，索引失效

我们以dish_flavor数据表为例进行分析

tip：数据库的表最好不要太简单，数据不要太少。否则优化器可能并不会走索引，因为在数据量小的情况下，可能全表扫描效率更高

我们以dish_id，name，value为字段，创建联合索引

CREATE INDEX idx_dishid_name_value ON dish_flavor(dish_id, name, value);

我们分别执行以下SQL，看看索引情况

• EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

  

  走了索引

• EXPLAIN SELECT * FROM dish_flavor WHERE name = '中餐' AND value = '[]';

  

  没走索引

• EXPLAIN SELECT * FROM dish_flavor WHERE value = '["不辣"]';

  

  没走索引

• EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';

  

  走了索引

以上四种情况只有第一条、第四条SQL走了索引。而这两条SQL的共同点就是：都包含了dish_id这个最左侧的字段。因此，想要索引生效，必须包含最左侧的字段

当然了，第一句SQL和第四句SQL也是存在区别的。我们建立的索引是idx(dish_id, name, value)，dish_id紧邻的是name。因此第一句SQL索引全部生效EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

但第二句SQL，EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';缺少了name这个字段，而value字段必须要name字段确定才能够有序，因此当前SQL会走idx_dishid_name_value索引，但仅仅到dish_id这个字段为止。

我们可以观察explain输出表的ref列

第一句SQL两个筛选条件都用于和索引进行比较

第二句SQL只有第一个筛选条件用于和索引进行比较

tip: explain输出的表格，ref列表示的意思是，筛选条件是否和索引进行比较。下方是笔者从官方文档中摘录的信息

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table

3.2 使用OR，索引失效

执行这条SQL：EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';，索引失效

想要解释OR关键字为何会导致失效，其实很简单。

OR在结果上可以等价于当个SQL得到集合的并集，具体来说

SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐'

=>

SELECT * FROM dish_flavor WHERE dish_id = 1 并上 SELECT * FROM dish_flavor WHERE name = '中餐'

对于拆解成的两句SQL，前者可以走联合索引，后者不能走（因为最左前缀法则）。事实上，MySQL可不会真这么拆解SQL，那对于MySQL来说，OR的两个筛选条件一个能走idx，一个不能。这到底是能还是不能，干脆就不走联合索引。

即使走了联合索引，也只能对dish_id做筛选，而存在的name字段必须要全表扫描，因为dish_id没法被确定，因此他全局无序。既然如此，那为什么不直接全表扫描呢？

因此，OR关键字会导致索引失效。总结一下：OR关联的两个查询条件，必然存在一个条件无法满足最左前缀法则，走不了索引。对于那个走不了索引的查询条件，在不存在其它索引的前提下，必然需要全表扫描。因此OR关键字会破坏联合索引，导致索引失效

tip: 当前数据表中，只存在idx(dish_id, name, value)

---

现在，我们做些额外操作，在OR的查询条件下，让MySQL依然走索引。

我们为name字段单独创建索引CREATE INDEX idx_name ON dish_flavor(name);

现在我们在执行含有OR的SQL，EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';

我们发现，MySQL不仅走了联合索引，还走了idx_name索引。联合索引解决dish_id = 1这个条件，idx_name索引解决name = '中餐'这个条件，非常完美

tip: explain输出的type字段，内容为index_merge。这表明SQL走了多个索引

为了下文实验不被多余索引干扰，我们删除idx_name索引

3.3 函数运算，索引失效

我们执行SQLEXPLAIN SELECT * FROM dish_flavor WHERE dish_id + 1 = 1;，会发现索引失效
索引失效

• 模糊匹配中
  索引未失效
• 模糊匹配尾
  索引未失效

笔者尝试解释上述现象

首先，模糊匹配本质就是范围查找。既然是范围，那必然存在两端。我们查找范围的思路可以大致框定，对于有序的数据，我们可以通过索引确定端点，端点间的数据就是我们模糊匹配的内容。

• 首模糊

对于首模糊情况，他的起始端点无法走索引确定，因为起始端点可以是任何数据。

同样的，他的结束端点也无法走索引确定，因为字符串字段构建索引，依然遵循另一个层面的最左前缀法则，字符串比较就是从左到右一次比较。如果左侧字符串无法确定，右侧字符串就无法保证有序性，这种情况也可以归类为全局无序，局部有序。

• 中间模糊

对于中间模糊情况，他的起始端点可以走索引确定。但他的尾端点无法确定，但这足够了。因为有一部分内容可以走索引，剩下内容扫描整个索引即可

• 尾模糊

起始端点可以走索引确定，尾端点不需要确定，因为它可以无限匹配后续内容

一句话总结，模糊查询依然遵循另一个维度的最左前缀法则，它依赖于字符串索引创建的规则。优先匹配左侧字符串，右侧字符串确定顺序的前提是左侧字符串已确定。