为什么有时候MySQL会放弃索引而选择全表扫描？MySQL之联合索引和覆盖索引

1、一个简单的例子

首先我们来看一个简单的例子，有表T，有字段如下：

• uid int(32) 主键
• a’int(32) 索引列
• ‘b’ int(32) 非索引列
  执行以下语句：
  1、insert into T values (1,1,1), (3,3,3), (5,5,5), (6,6,6);
  2、select * from T where a between 3 and 5;

我们来分析下语句2的执行过程：

• 首先查询条件使用了索引字段a，会根据索引树快速定位到存储a = 3记录的叶子节点（数据页），里面存储有a = 3这条记录的主键值
• 然后取到a = 3的记录的主键值，然后根据该主键值查询主键索引树，这个过程称为回表查询，主键索引树的叶子节点存储的是一包含所有字段数据的记录，如此就可以取出想要查询的内容了
• 接着读取下一条a = 5，继续进行回表查询，再读取a = 6，不满足条件，查询结束

可以看到在这个过程中，虽然在二级索引的叶子节点是连在一起的，只需遍历一次二级索引树，但是由于二级索引叶子节点不包含所有要查询的字段数据，所以每条记录都需要进行一次回表查询，效率比较低下。因此，回表查询的代价也经常作为是否选择索引的依据，看下面的例子：

1. 创建表：

create table ‘foo’ (
‘uid’ int Not Null,
‘name’ varchar(50),
‘idx’ int,
PRIMARY KEY(‘uid’),
Key ‘idx’ (‘idx’)
) engine = InnoDB;

2. 插入以下数据：

• ±----±-----±---------+
  | uid | idx | name |
  ±----±-----±---------+
  | 1 | 1 | zhangsan |
  | 4 | 4 | zhangsan |
  | 10 | 10 | zhangsan |
  | 16 | 16 | zhangsan |
  | 20 | 20 | zhangsan |
  ±----±-----±---------+

3. 查看以下两条语句的执行计划：

• explain select name,idx from foo where idx >= 10;
  explain select name,idx from foo where idx > 10;

• 结果如下:
  
  
  可以看到第一条语句使用了全表扫描，原因是name不在二级索引的叶子节点上，需要进行回表查询，而全表只有5条记录且占用很小，mysql判定回表代价（二级索引扫描3条索引记录，回表需扫描3条完整记录）大于全表扫描（扫描5条完整记录）。

  而第二条语句缩减了范围后，回表代价（二级索引扫描2条索引记录，回表2条完整记录）就小于全表扫描（扫描5条完整记录）

  注：需要注意的是，比较扫描代价实际上是比较需要扫描的数据量，比如为该表增加一个长度100的char字段，全表扫描的代价增大，第一条语句就会使用二级索引，感兴趣的同学可自行实验。

回表会增加额外的代价，那么如何避免回表呢？答案是覆盖索引

2、覆盖索引

我们将上面例子的查询语句改为：
select uid from T where between 3 and 5;

由于字段a的索引树叶子节点包含了主键列uid，所以在这条语句中，只需要查询一次二级索引树就可以得到全部数据了，也就是索引树a覆盖了查询内容，这种情况就称之为覆盖索引

但是只查询主键的业务毕竟少，有什么办法可以让覆盖索引大规模应用到项目中吗？答案自然是可以的，联合索引可以帮助我们做到这一点

3、联合索引

联合索引也即使用多个字段建立一个索引，联合索引的一个显著的好处就是可以形成覆盖索引，提高查询效率。我们通过一个简单例子来了解

假设有表tab，有字段a、b、c均为int类型，a经常作为查询条件，可以作为索引列，而b和c则是经常作为查询内容出现，也即经常出现以下语句：

• select b,c from tab where a = …；

那么我们就可以建立联合索引 index(a,b,c)，这样索引树的叶子节点就会同时具有a、b、c三个字段的数据了，查询时就不需要频繁回表了，大大提高查询效率。

学到联合索引，就必须了解它的另外一项特性，否则容易踩坑，这项特性名为最左前缀原则

4、最左前缀原则

最左前缀原则不仅仅是联合索引独有的特性，在字符串字段中也同样有该特性，我们进行分别讨论

• 联合索引：查询条件当中必须有声明联合索引时的第一个字段，索引才会生效；
  以上面的例子来说就是a必须出现在查询条件当中，但是出现的顺序却没有要求，如：
  1、where a = 1 and b = 2
  2、where b = 2 and a = 1
  3、where a = 1 and c = 3
  4、where b = 2 and c = 3
  其中，1和2是等价的，只要包含a，顺序无所谓，都是生效的；3的索引也是生效的，尽管b没有作为查询条件，但是实际进行定位的是使用a，故而生效；而4这种情况则不生效

  之所以需要遵循最左前缀原则，是因为联合索引的索引树是先对索引建立时最先声明的字段(a)进行排序，然后再按后面声明的字段依次排序(b、c)

• 字符串索引：使用字符串索引时，需要从该字符串的第一个字符开始匹配，如有字段name为索引，那么 name like ‘张%’ 这样的查询条件是生效的；而如果没有从第一个字符开始匹配，则索引不会生效，如 name like ‘%三’

由于维护索引需要耗费一定的空间资源和时间资源。那么基于最左匹配原则，可以适当调整联合索引的字段顺序，使得经常作为查询条件的字段位于最左侧，可以减少索引的数量。如上面的例子中，a作为查询条件的频率较高，那么就让a位于最左侧，这样就不需要再建立单独的索引 index(a)了

5、索引下推

联合索引的另外一个重要特性就是索引下推，这是MySQL5.6及以后才有的特性，前文说到联合索引
index (a,b,c) 在进行查询时实际上是使用a进行定位到具体记录，那么b和c除了作为覆盖索引的产生条件以外就没有其他作用了吗？

答案是有的，同样用一个简单的例子来说明，对表T执行以下语句：
select * from T where a > 5 and b < 10；
我们来分析下执行情况

• 首先根据a快速定位到符合条件的第一条记录，由于此时不满足覆盖索引的产生条件，因此需要进行回表查询；
• 在MySQL5.6以下的版本，拿到记录后就会立即进行回表查询，对于b < 10这个条件并不会在二级索引查询时进行判断，也就是说会对所有满足a > 5的记录进行回表查询；
• 而在MySQL5.6版本，则在二级索引查询时，会下推到联合索引的下一个字段b进行条件判断，倘若不满足b < 10这个条件，那么就不会进行回表查询。这是因为MySQL5.6的引擎接口进行了升级，使其可以支持索引下推的功能