MySQL 违反最左前缀法则索引失效的原因

最左前缀原则原理

最左前缀在 MySQL 的官方文档中称之为 leftmost prefix，该原则适用于多列索引。想仅仅用三言两语来说清楚什么是最左前缀匹配原则不太现实，但是如果使用官方文档的一个例子来说明该原则，或许会好得多。

假如现在有一张表的创建语句如下：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name, first_name)
);

可以命中索引的 SQL

如下的 SQL 可以命中 name 索引：

SELECT * FROM test WHERE last_name='Jones';
SELECT * FROM test WHERE last_name='Jones' AND first_name='John';
SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');
SELECT * FROM test WHERE last_name='Jones' AND first_name >= 'M' AND first_name < 'N';

无法命中索引的 SQL

如下的 SQL 无法命中 name 索引：

SELECT * FROM test WHERE first_name='John';
SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

原因分析

为什么前四个 SQL 可以命中 name 索引，后两个 SQL 却不行呢？因为 name 索引的定义是 last_name 在前，first_name 在后，所以它的最左前缀有两个：last_name、last_name first_name。如果有一个索引的定义为 idx(col1, col2, col3)，那么它的最多前缀有哪些呢？答案是有三个：col1、col1 col2、col1 col2 col3。前 4 个 SQL 的 WHERE 条件都是以最左前缀的顺序依次作为查询条件，后 2 个 SQL 却不是。

深入探讨

此时，此刻，或许你还有很多疑问，比如为什么上面第 3 条 OR 查询可以命中索引但是第 6 条就不行呢？范围查询呢？是不是我严格按照任意一个最左前缀书写 SQL，就一定会命中相应的索引？想要搞清楚这些问题，死记硬背一些原则或许是不合适的，还是应该尝试从本质上来探究这些问题。

我们知道，在 InnoDB 存储引擎中，任何一个索引的数据结构都是 B+ 树。该 B+ 树如何构建取决于索引是如何定义的。以例子中的 INDEX name (last_name, first_name) 为例，肯定是以 last_name 为主要排序对象，如果 last_name 相同，则按照 first_name 排序。假如我们有如下数据行：

id=1, last_name=aaa, first_name=dwf;
id=2, last_name=zzz, first_name=ytr;
id=3, last_name=mmm, first_name=edf;
id=4, last_name=bbb, first_name=abc;
id=5, last_name=zzz, first_name=abc;
id=6, last_name=qqq, first_name=gfd;
id=7, last_name=aaa, first_name=rfg;
id=8, last_name=zzz, first_name=sdd;
id=9, last_name=hhh, first_name=bgf;
id=10, last_name=aaa, first_name=abc;
id=11, last_name=zzz, first_name=bfd;
id=12, last_name=lll, first_name=cba;
id=13, last_name=aaa, first_name=bvf;
id=14, last_name=zzz, first_name=abd;
id=15, last_name=lll, first_name=sdd;
id=16, last_name=mmm, first_name=nbv;
id=17, last_name=zzz, first_name=trd;
id=18, last_name=aaa, first_name=oiu;

假设一个数据块可以存放 3 行数据，那么它的 B+ 树结构大概如下（索引页上面的三个字母代表 last_name，下面的代表 first_name）：

SQL 逐个分析

下面来逐个分析上面 6 个 SQL。

SELECT * FROM test WHERE last_name='Jones';
- 通过根索引页定位到第二层中间的索引页
- 通过第二层中间的索引页定位到第三层的第三个数据页(因为jones排序在lll的前面)
- 发现没有last_name='jones'，返回null
SELECT * FROM test WHERE last_name='Jones' AND first_name='John';
- 通过根索引页定位到第二层中间的索引页
- 通过第二层中间的索引页定位到第三层的第三个数据页(因为jones排序在lll的前面)
- 发现没有last_name='jones'和first_name='John';，返回null
SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');
- 该 SQL 语句可以改写成 SELECT * FROM test WHERE last_name='Jones' AND first_name='John' OR (last_name='Jones' and first_name='Jon')，本质上和第 2 条 SQL 一样，所以也可以命中索引。
SELECT * FROM test WHERE last_name='Jones' AND first_name >= 'M' AND first_name < 'N';
- 无非就是根据last_name一步步通过根索引页定位到相关的数据页，再通过数据页的页目录找到first_name是这个范围的，因为last_name相同的情况下，first_name是按顺序存储在数据页中的，所以也可以命中索引。
SELECT * FROM test WHERE first_name='John';
- 因为B+树是先按照last_name再按照first_name的顺序构建的，如果上去就按照first_name去查找相应的数据页，结果显然是不准确的。根索引页的first_name：dwf、gfd、ytr看起来确实是有顺序的，但实际上这完全是一个巧合，其实从第二层的第一个索引页的first_name也可以看出来：dwf、abc显然是无序的。只有当last_name一样时，first_name才是有顺序的，可以看看第三层第五个和第六个数据页，last_name都是zzz，first_name是按照顺序排列的，这也是为什么第4个sql可以命中索引的原因。
SELECT * FROM test WHERE last_name='Jones' OR first_name='John';
- 其实看这个sql的前半部分SELECT * FROM test WHERE last_name='Jones'其实可以通过该索引轻易地找到last_name='Jones'对应的数据页，但是下一步如何定位first_name='John'的数据页呢？显然等值查找first_name是无法使用该索引的，那就只能遍历聚簇索引的数据页，既然都遍历聚簇索引的数据页了，那为什么还要使用该索引定位last_name='Jones'的数据页呢，直接在遍历的时候顺带判断一下last_name的值不就好了吗，反正整个数据页都会加载到buffer_pool，无非就是在内存中做一次比较，所以不会命中该索引。