前言

只要说到联合索引，大家肯定都会想到“最左匹配”，相信不用解释大家也知道是啥意思，也很简单，但是联合索引中又有不少特殊情况，

比如：

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

这个如何建立索引？
如果回答是直接建立索引(a,b,c)的话，那么可以直接回家等通知了，其实对于这个例子，建立索引(a,b,c),(a,c,b),(b,a,c),(b,c,a),(c,b,a),(c,a,b)这几种都是可以的，之所以能这样是因为mysql在执行的过程中有会经历优化器这一层，在这层会自动优化顺序。

再比如，在一张表中(表名test),有联合索引(a,b,c),
那么

select a from test order by a,b,c;

是走索引的，但是

select * from test order by a,b,c;

是不走索引的，这又是为什么呢？

所以由此看来联合索引并不是简简单单的"最左匹配"几个字能完全涵盖的，所以咱们今天从原理来理解mysql的联合索引，从而达到真正的懂和理解联合索引的目的。

最左匹配及其原理

最左匹配

最左匹配原则就是指在联合索引中，如果你的 sql 语句中用到了联合索引中的最左边的索引，那么这条 sql 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c),现在你有如下语句：

select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引（a,b,c）

select * from t where a=1 and b=1;     #这样可以利用到定义的索引（a,b,c）

select * from t where a=1;     #这样也可以利用到定义的索引（a,b,c）

select * from t where b=1 and c=1;     #这样不可以利用到定义的索引（a,b,c）

select * from t where a=1 and c=1;     #这样不可以利用到定义的索引（a,b,c）

也就是说通过最左匹配原则你可以定义一个联合索引，但是使得多种查询条件都可以用到该索引。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：

select * from t where a=1 and b>1 and c =1;     #这样a,b可以用到（a,b,c），c不可以

这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。

原理

我们先来看看联合索引在b+树中是什么样的。
比如我们在一张表中创建了索引(a,b),那么在b+树中，它的表现形式是这样的

image

字段名	a	b
值	1	1
值	1	2
值	2	1
值	2	4
值	3	1
值	3	2

咱们来先观察一下，只看a字段，a字段的值分别是 1,1,2,2,3,3.我们会发现 a字段其实是排序好的，而b字段的顺序是 1,2,1,4,1,2，却是乱序的，但是在字段a相等的情况下，字段b是经过排序的。也就是说b是一种全局无序，局部相对有序状态!

所以为什么直接用b=2,是无法走索引的，但是在a=1 and b=2的情况下是可以走到索引的，而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。
综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

实战

咱们多看一些常见的实战题，多思考，这些题都会了，联合索引应该能够做到举一反三了。

question:1

如何给下列sql语句加上联合索引？

select * from test where a = 1 and b = 1 and c = 1;

建议思考一下再看答案。。。。。。。

answer

咱们一看，直接加索引(a,b,c)就可以了，其实不然，也不能说这个答案不对，只能说这个答案不够完整。因为mysql在执行的时候会经历优化器过程，所以会把sql语句自动优化成符合索引的顺序，所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的，那我们究竟如何确定索引呢？这个就得根据实际情况来了，比如a字段是表示性别的，只有0，1和2三个值来表示 未知，男，女三种性别，那么把a字段放在联合索引的最后会比较合适，反正哪个字段的内容重复率越低，就把哪个字段往联合索引的后面放。

question：2

如何给下列sql语句加上索引

SELECT * FROM table WHERE a > 1 and b = 2; 

建议思考一下再看答案。。。。。。。

answer

如果咱们建立索引(a,b),那么a>1是可以走到索引的，但是b=2就没法走到索引了。
但是如果咱们建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1，这样a和b都能走到索引，所以建立索引(b,a)比较合适

question:3

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3; 

answer

其实这个sql语句加上索引的方式和上面question2差不多，加上索引（b,a）或者(b,c)都是可以的。

question:4

这个情况比较多，咱们直接看sql语句分析吧

SELECT * FROM `table` WHERE a = 1 ORDER BY b;

对于上述情况，其实加上一个联合索引（a,b）就行，因为在a=1的情况下，b的顺序都是排好的，避免了再次排序

SELECT * FROM `table` WHERE a > 1 ORDER BY b; 

这种情况下如果建立了联合索引(a,b)，那么在a>1的情况下b是无序的，需要对b再次排序，所以直接在字段a上建立索引就可以了，完全没必要用联合索引。

SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

上述sql语句直接建立索引(a,b,c)就可以了，具体原因不再赘述。

下面咱们看几种比较麻烦的情况

question:5

咱们有表 test ，id为主键，字段(a,b,c)为联合索引，另外还有一个字段d和e，除了e为varchar，其余全是int，表的sql语句如下

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned NOT NULL DEFAULT '0',
  `b` int(11) unsigned NOT NULL DEFAULT '0',
  `c` int(11) unsigned NOT NULL DEFAULT '0',
  `d` int(11) unsigned NOT NULL DEFAULT '0',
  `e` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

情况1

咱们执行sql语句

select * from test where a>1;

结果却是不走索引的(用explain查看type为ALL),这是为什么呢？咱们查询条件是a>1,的确是符合最左匹配原则的，那为什么没有走索引呢？其实这个也和咱们mysql的自动优化有关，咱们先来看一看 走索引的过程。
首先会把a>1的情况都根据索引筛选出来，因为a是unsigned的，也就是是大于0的，所以a>0的情况基本上是把所有数据筛选出来了，其实这个还不是问题的关键所在，因为咱们查询的字段是select * ,所以每一次查询出来结果之后还需要回表，回表次数是非常多的，所以mysql在内部直接自动优化了，让它不走索引，直接一次性全部查出来算了。

情况2

咱们执行sql语句

select id from test where a>1;

这次是走索引的了，原理其实和上面情况一样，但是因为咱们查询的字段是id，不需要回表，同样，查询字段 id,a,b,c这四个字段都是不需要回表的

情况3

test表中一共1000000条记录
咱们执行sql语句

select * from test where a>999999;

咱们用explain查看这次却是走索引的了，为什么呢？因为虽然需要回表，但是咱们的限制条件a比较大，所以相对的回表次数也少了很多，所以这次走索引比较划算，性能会比较好。

question:6

sql1:  select * from test order by a,b,c;
sql2:  select a from test order by a,b,c;

同样还是question5的那张表，请问这俩sql哪个会走索引？
答案是sql2会走索引，sql1不会，因为联合索引a,b,c的确是按照a,b,c的顺序排好的，但是如果只是select a ，那直接找到叶子节点就可以返回，如果是select * 就还是需要不断的回表，所以mysql会直接选择不走索引直接查询出来，然后用内存进行排序。