简谈mysql索引之联合索引

前言

我们程序员日常工作中打交道最多的就是联合索引，基本上索引优化就是优化这一块。讲联合索引，一定要扯上最左匹配原则。不废话，直接进入正文。(注:本文是在Innodb的基础上讨论和实验的。)

正文

最左匹配原则

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
假设，我们对(col1,col2)字段建立一个索引，也就是说，你where后条件为

col1= 1
col1= 1 and col2= 2

是可以匹配索引的。但是要注意的是~你执行

col2= 2 and col1=1

也是能匹配到索引的，因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。
但是你执行

col2= 2

就匹配不到索引了。
而你对(col1,col2,col3,col4)建立索引,where后条件为

col1= 1 and col2= 2 and col3> 3 and col4= 4 

那么，col1,col2,col3三个字段能用到索引，而col4就匹配不到。因为遇到了范围查询！

最左匹配原则原理?

理解最左匹配原则之前最好了解一下B+树，因为Innodb存储引擎默认是用B+树作为索引的结构。
假设，我们对(col1,col2)字段建立索引，那么如下图所示:

如图所示他们是按照col1来进行排序，在col1相等的情况下，才按col2来排序。
因此，我们可以看到col1是有序的1，1，2，2，3，3。而col2是一种全局无序，局部相对有序状态!
什么意思呢？
从全局来看，col2的值为1，2，1，4，1，2，是无序的，因此直接执行col2 = 2这种查询条件没有办法利用索引。

从局部来看，当col1的值确定的时候，col2是有序的。例如col1 = 1时，col2值为1，2是有序的状态。当col1 = 2时候，col2的值为1，4也是有序状态。
因此，你执行col1 = 1 and col2 = 2时col1，col2字段能用到索引的。而你执行col1 > 1 and col2 = 2时，col1字段能用到索引，col2字段用不到索引。因为col1 的值此时是一个范围，不是固定的，在这个范围内col2值不是有序的，因此col2字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

实战

有了上面的基础，再看下面的五大题型，看完基本就懂！
题型一
如果sql为

SELECT * FROM t_tbl where  col1 =1 AND col2 = 2 AND col3 = 3;

如何建立索引?
如果此题回答为对(col1 ,col2,col3)建立索引，那都可以回去等通知了。
此题正确答法是:(col1 ,col2,col3)或者(col2 ,col1,col3)或者(col3 ,col2,col1)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。

例如假设区分度由大到小为col2,col1,col3。那么我们就对(col2,col1,col3)建立索引。在执行sql的时候，优化器会帮我们调整where后col1,col2,col3的顺序，让我们用上索引。

题型二
如果sql为

SELECT * FROM table 
WHERE col1> 1 and col2 = 2; 

如何建立索引?
如果此题回答为对(col1,col2)建立索引，那都可以回去等通知了。
此题正确答法是，对(col2 ,col1)建立索引。如果你建立的是(col1,col2)索引，那么只有col1字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配(注:如果表的数据很大，可能col1字段也用不上索引，因为mysql认为数据检索量已经超过某个阈值，就会自动进行全表扫描，所以索引失效。)。
如果对(col2,col1)建立索引那么两个字段都能用上，优化器会帮我们调整where后col1,col2的顺序，让我们用上索引。

题型三
如果sql为

SELECT * FROM `table` 
WHERE col1> 1 and col2= 2 and col3> 3; 

如何建立索引?
此题回答也是不一定，(col2,col1)或者(col2,col3)都可以，要结合具体情况具体分析。

拓展一下

SELECT * FROM table
WHERE col1= 1 and col2= 2 and col3> 3; 

怎么建索引？嗯，大家一定都懂了！

题型四
如果sql为

SELECT * FROM table WHERE col1 = 1 ORDER BY col2;

如何建立索引？ 这还需要想？一看就是对(col1,col2)建索引，当col1 = 1的时候，col2相对有序，可以避免再次排序！ 那么

SELECT * FROM `table` 
WHERE col1 > 1 ORDER BY col2; 

如何建立索引？
对(col1)建立索引，因为a的值是一个范围，这个范围内col2值是无序的，没有必要对(col1,col2)建立索引。

拓展一下

SELECT * FROM `table` 
WHERE col1= 1 AND col2= 2 AND col3>3 ORDER BY c;

怎么建索引?

题型五

SELECT * FROM `table` WHERE col1 IN (1,2,3) and col2 > 1;

如何建立索引？

还是对(col1，col2)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(col1,col2)!

拓展一下

SELECT * FROM `table` WHERE col1 = 1 AND col2 IN (1,2,3) AND col3 > 3 ORDER BY col3 ;

如何建立索引？此时col3 排序是用不到索引的。