mysql索引最左匹配原则的理解6_MySQL 索引最左匹配原则-Go语言中文社区

索引能做什么?

索引主要做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被利用：

当表students的联合索引为时：

多个不等于查询(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和一个联合索引：

然后分析下查询结果：

注释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字段)。

总结

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

参考文章：