MySQL之联合索引，覆盖索引，索引下推

本文演示所用的表结构

CREATE TABLE `test_group_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `d` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

不需要数据，全程使用Explain进行演示
Explain教程，点击跳转（转载的）

1、联合索引

联合索引，就是将多个列合并在一起作为一个索引，联合索引的底层数据结构，是以最左边那个列进行排序的。

可以看到，name列作为索引最左边的列，在索引树中是有序的，而phone在右边，则是无序的，这也就说明了，联合索引是以最左边的那个列的值为主，所以，联合索引才会有一个叫做最左匹配原则的东西

例如

ALTER TABLE test_group_index ADD INDEX idx_a_b_c (a,b,c);

这样创建索引，实际可以理解为有三个索引
1、a
2、a,b
3、a,b,c

也就是说，单独用a列查询，可以命中索引，用a,b两列查询也可以，a,b,c三列也可以，因为都是从左往右匹配，但是如果是a,c，就会出现索引下推的场景，这个在下面会有解释，单独c列，就会全索引扫描，下面也会解释

这里就创建了一个联合索引，联合索引的匹配规则叫最左匹配原则，也就是说，会以左边条件的列为准，看看是否命中索引**（并不是绝对的，由优化器决定，当优化器觉得不需要使用索引时，也不会走索引）**

场景1：

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a';

type列的值为red，key列的值显示使用了idx_a_b_c索引

场景2：

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a' AND b = 'b'; 

可以看到，两个列都是命中了索引，在ref列，有两个const，因为SQL的条件，都是等值查询，优化器会将值转换为常量，所以两个命中索引，就有两个const

场景3

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a' AND b = 'b' AND c = 'c'; 

同样的，也是都能命中索引，有三个const，这是三种正常的等值查询的情况，基本是没有什么问题的

下面来看几种，等值查询，列位置打乱的情况

场景1

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a' AND c = 'c';

使用了a和c，根据最左匹配原则，那么还是能命中索引的，但是只有a索引命中了，为什么？
可以看到ref列只有一个const，那如何确定是a，可以看 场景2的对比

场景2

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a';

EXPLAIN SELECT * FROM test_group_index WHERE c =  'c';

可以看到，第一条SQL执行的结果，ref列const只有一个，而第二条SQL的执行结果，ref列为null
由此可以看出，在where条件中，a在左,c在右，只会匹配a的为索引，c则会作为条件，进行索引下推的优化，而如果是a,b两个条件，则会两个都命中，而单独的使用b或者c会进行全表扫描

场景3

EXPLAIN SELECT * FROM test_group_index WHERE b = 'b' AND a = 'a'; 

从SQL表面看，好像是不会命中索引的，因为按照最左匹配原则来看，b在联合索引的中间，应该是不会匹配到的，来看结果

结果显示使用了索引，还是两个都命中了，为什么？
这里涉及到了MySQL的查询优化器（CBO）
查询的格式为： AND

优化的步骤：

1)如果两个列都没有索引，那么使用全表扫描。

2)否则，如果其中一个列拥有更好的存取类型(比如，一个具有索引，另外一个没有索引；再或者，一个是唯一索引，另外一个是非唯一索引)，那么使用该列作为驱动表达式。

3)否则，如果两个列都分别拥有索引，并且两个条件对应的存取类型是一致的，那么选择定义索引时的先定义的索引。

所以，where a = ‘a’ and b = 'b’和where b = ‘b’ and a = ‘a’
结果是一样的，优化器会进行优化

场景4

EXPLAIN SELECT * FROM test_group_index WHERE b = 'b' AND a = 'a'; 

同样的会命中索引，还是优化器的功劳，会把顺序调整，重新组合，使其命中索引
优化器会决定你的SQL该不该走索引，走那个索引，具体可以去看看MySQL的优化器

索引失效的条件
1、对索引列使用like时，%只能写在索引列的后面，因为上面有说到，索引树会通过最左边列的值进行排序，如果%写在前面，存储引擎也不知道你要查询的是哪个值，就会全表扫描
2、使用了函数，在条件中使用了函数也会导致索引实现
3、使用条件值的类型与列类型不匹配，比如列的类型是字符串，条件使用数字，mysql会隐式的加上toString函数，进行类型转换
4、对索引列在条件上使用表达式，例如where id + 1 = 4，这样也会失效
5、负向查询，<>,is not null,!=

但是，上面列举的几种情况，只是经常出现的，实际上，用不用索引，用那个索引，都是优化器说了算，当优化器觉得不使用索引的方案成本更小，就会使用不走索引的方案，这个不是我们能够干涉的。
基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），
也不是基于语义。怎么样开销小就怎么来。

2、覆盖索引

MySQL innodb存储引擎中，每个表，有且仅有一个聚簇索引

也就是说，所有的数据都在聚集索引上，那么其他的普通索引（二级索引），他们组成的B+树，存放的就是主键的值，想要通过普通索引查询数据，就需要现在普通索引的索引树中查询到主键，然后拿主键到聚簇索引上去查询具体的数据，这个过程，就叫做回表

那么也不是每次查询都会进行回表，当查询的列，和条件中，都是一棵索引树上的列时，就不需要再进行回表查询，例如

EXPLAIN SELECT a,b,c FROM test_group_index WHERE  a = 'a'; 

a,b,c为组合索引，那么通过a进行查询，他们仨都在一棵索引树上。结果

可以看到Extra列的值为Using index，这就表示没有进行回表，这也就说明了，在select后面为什么不要写*号，最好写上具体的列，这样有可能就可以避免回表

3、索引下推

索引下推是MySQL5.6版本推出的为了优化联合索引的一种方案，在5.6版本之前，联合索引，只会匹配最左边的索引列，而其他的索引列的条件，会在数据查询出来后，交给MySQL Server端进行where过滤。
例如：
索引列(name,age)
条件：where name like ‘Zhang%’ and age = 20;

这样的语句，5.6版本之前，MySQL会先从普通索引树中，将符合name like 'Jack%'条件的主键全部查出来，然后通过这些主键，再到聚簇索引去查询到具体的数据，再把这些数据返回给MySQL Server端，由Server端再通过age = 20进行二次过滤，也就是说，innodb第一次查询时，只会通过name like 'Jack%'条件进行查询，不会管age字段，如下图所示

可以看到，在innoDB进行查询时，将年龄22和24的都查出来了，最后将他们的主键值，返回给MySQL Server，由MySQL Server拿到主键值，到聚簇索引回表进行查询，然后再进行年龄过滤，最后得到一条数据

那么整个过程回表了三次，而其实，完全可以只回表一次，因为age也是索引中的一列，完全可以再索引树的匹配时，就将age列过滤一次。从而返回给MySQL Server的结果就是一条
数据。

在MySQL5.6时，就进行了这样的一个优化，把age列也一同下推到了索引树的匹配

先通过name列匹配到一部分索引，然后通过age再次进行过滤，得到最后的结果，返回主键值
这样就只需要回表一次，极大的提高了性能，减少了很多的无用回表次数。

如何验证是否进行了索引下推

EXPLAIN SELECT * FROM test_group_index WHERE a = 'a' AND c LIKE '%c';

可以看到Extra列，值为Using index condition这就表示使用了索引下推