MySQL 索引最左匹配原则

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

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

 

多个不等于查询（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>：

然后分析下查询结果：

注释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索引最左匹配原则