面试官:谈谈你对mysql联合索引的认识?
面试官:谈谈你对mysql联合索引的认识?
前言
只要说到联合索引，大家肯定都会想到“最左匹配”，相信不用解释大家也知道是啥意思，也很简单，但是联合索引中又有不少特殊情况，
比如：
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+树中，它的表现形式是这样的
字段名 | 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会直接选择不走索引直接查询出来，然后用内存进行排序。
完