MySQL 索引最左匹配原则

秒速五厘米 2022-04-23 08:18 302阅读 0赞

**索引能做什么?**  
索引主要做3件事：过滤（filter）、排序或分组（sort/group）、覆盖（cover）。  
一个基本查询的工作流如下：  
1. 使用索引以查找匹配的记录，并得到数据的指针。  
2. 使用相关数据的指针；  
3. 返回查询到的记录。  
当可以使用覆盖索引时，索引将会覆盖查询中的所有字段，因此第二步将会被跳过，于是查询流程就变成了下面这样：  
1. 使用索引以查找匹配的记录；  
2. 返回查询到的记录。  
大部分情况下，索引都比较小，可以加载在内存中，而数据很大，无法全部存放在内存里：当使用覆盖索引时，可以避免很多的磁盘操作，因此对性能也会有极大的改善。

下面让我们来看一些常见的查询案例。

**单个等于查询（Single equality）**  
这是最基本的情景：

SELECT * FROM t WHERE c = 100

毫无疑问这种情况下，要给c字段创建索引。要注意的是，如果查询条件不够精确（if the criteria is not selective enough，这句话我不理解），优化器很可能会选择全表查询，因为这样有可能性能更好。  
这种单个等于查询也包括只查询部分字段，而不是所有字段，如：

SELECT c1, c2 FROM t WHERE c = 100

这里应该创建一个（c，c1，c2）的索引，因为这样是覆盖索引。注意不是创建（c1，c2，c）！这同样也是覆盖索引，但是对过滤没什么帮助（记住MySQL索引的最左原则）。

**多个等于查询（Multiple equalities）**

SELECT * FROM t WHERE c = 100 and d = 'xyz'

这种情况也很容易优化：创建索引（c，d）或（d，c）。  
最常见的错误是建立两个索引：一个是c，一个是d。尽管MySQL根据index\_merge算法能同时使用这两个索引，但这样依然是糟糕的选择。详情参见以下几篇文章：https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/，https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/，https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/）

**等于与不等于并存的查询（Equality and inequality）**

SELECT * FROM t WHERE c > 100 and d = 'xyz'

这种情况我们必须要小心，因为只要有一列使用了不等于计算，那么它将阻止其他列使用索引。  
因此我们需要创建一个（d，c）的索引，这时候c和d两个条件都会走索引，这也是我们想要的结果。  
而如果我们创建的是（c，d）索引，则只有c列的索引会被利用，这样效率会比较低。  
因此，索引中字段的顺序对于这种等于/不等于并存的查询有极大的影响。

实例：当表students的联合索引为<phone,email>时，则只有第一个索引列phone被利用：

![20190130112401465.png][]

当表students的联合索引为<email,phone>时：

![20190130141511903.png][]

**多个不等于查询（Multiple inequalities）**

SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'

这里有两个不等于，前面已经说了不等于会终止索引查询，因此我们不可能做到b、c、d都被索引覆盖（注释1）。因此我们必须要做出决定，到底是创建索引（d，b）还是索引（d，c）？  
在不知道表里具体数据的情况下，创建上面任何一种都无所谓，最关键的是，一定要把等于条件（在这里是d）所在列，放在索引的最左侧。

实例：表students索引状况，包含一个主键索引stud\_id和一个联合索引<email\_phone\_ ischecked>：

![2019013014372173.png][]

然后分析下查询结果：

![20190130144427255.png][]

注释1：事实上还是有一种“曲线救国”的方法，能同时满足所有条件，即按照字段b分区(partition on b)，然后创建索引（d，c），或按照字段c分区(partition onc)，然后创建索引（d，b）。这个的细节已经超出了本文的讨论范围，不过这也是这种情况下的一种解决方法。

**多个等于与排序（Equalities and sort）**

SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

就像第一节中写的那样，索引可以过滤、排序，因此这个查询很容易优化。不过和不等于类似，我们对于索引中字段的顺序必须足够小心：要求是先过滤后排序。  
根据上面“先过滤后排序”的要求可知，（c，d，b）或（d，c，b）是不错的选择；而（b，c，d）或（b，d，c）则比较糟糕，因为他们只排序，不过滤。

如果是下面这种情况：

SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

我们可以创建一个集过滤、排序、覆盖于一体的索引：（c，d，b，c1，c2）。

**不等于与排序（Inequality and sort）**  
常见的情况有2种。下面是情况一（不等于、等于、排序都有）：

SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b

这种情况有两种思路：（d，b）或（d，c）。至于哪种效率更高，这取决于你的数据，需要具体情况具体分析。  
情况二如下（只有不等于和排序）：

SELECT * FROM t WHERE c > 100 ORDER BY b

这种情况没有等于条件，因此b和c只能选一种，具体选哪一种同样和你的数据有关。通常情况下，选过滤的会多一些（即c字段）。

**总结**  
本文并没有包含所有的情况，但同样指出了一些你必须要小心的地方。

参考文章：

[MySQL索引最左匹配原则][MySQL]

[20190130112401465.png]: /images/20220322/4b15a3760b1348c79f18549cfaa59ccf.png
[20190130141511903.png]: /images/20220322/adbcd88291614ed2a668e11abcbea739.png
[2019013014372173.png]: /images/20220322/1116abc893964ae0be5f9feba67b8aa8.png
[20190130144427255.png]: /images/20220322/6b58aa65943e45d08fff5e22bfe1ef3b.png
[MySQL]: https://blog.csdn.net/qq_26222859/article/details/52916257