Mysql 的联合索引、覆盖索引、索引下推、MRR、索引提示

创建一个表：my_user

CREATE TABLE `my_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

1. 联合索引

创建联合索引：

create index `union_index` on `my_user`(`name`,`sex`,`date`);

联合索引的列为 name、sex、date。创建联合索引后，就是创建了一颗B+Tree。

和单列索引的区别是：单列索引是根据这一个列的值的排序分布在B+Tree上，而联合索引是根据这些列的综合排序部署在B+Tree上的。每个索引节点上这三列的值是同时存在的。 

单列索引结构图如下：

 联合索引结构图如下：

以上图创建的索引为例：create index `union_index` on `my_user`(`name`,`sex`,`date`);

索引树上先根据name字段排序，然后name相同的情况下根据sex字段排序，name、sex字段都相同的情况下，再根据date字段排序。整个联合索引树的数据就排序好了。

现在使用联合索引查询，只要遵守最左前缀原则，联合索引一般都生效：

select * from `my_user` where `name` = '123' and `sex` = 1 and `date` = '2012-01-02';


select * from `my_user` where `name` = '123' and `sex` = 1;


select * from `my_user` where `name` = '123';

 这种情况就不生效：

select * from `my_user` where  `sex` = 1 and `date` = '123';

select * from `my_user` where  `sex` = 1;

select * from `my_user` where  `date` = '123';

 联合所索引查询时，索引不生效的原因：

联合索引是根据 name、sex、date 这样的一个字段位置顺序创建的一颗B+Tree，先按照name排序、name相同再按照 sex 排序，name、sex相同再按照date排序，所以按照最左前缀查询的时候，都是遵循了排好序的规则查询。如果没有遵守最左前缀原则查询，没有name字段条件时，sex其实是乱序的、date字段也是乱序的，sex、date更是乱序的。这些字段都是建立在这些字段在联合索引之前位置字段的排好序的情况下排好序的。

可以举个例子来理解：sex、date字段只是在自己对应的name小组中排好序的，如 name=name1时，有10条数据，那么在这个范围中sex是有序的，依次论推。如果name=name2，name=name3分别再有10条、20条数据，那么name2小组的10条数据中sex也是有序的、name3小组同样是有序的。但是抛开name字段，这40条数据中，sex在联合索引上就不是有序的了，是无顺序的，所以，按照 sex字段查询时，肯定是用不到联合索引的。

看一个有趣的例子来验证上面所描述的小组排序的情况，首先在上述表无任何索引的情况下创建一个联合索引：

首先创建name、date的联合索引：

create index `union_index` on `my_user`(`name`,`date`); 

分析查询：

explain
select * from my_user where name = 'gg' order by date;
分析结果：

 分析结果：确定直接使用了联合索引。因为联合索引上，在name值相同的情况下，date字段在各自的name小组中已经排好顺序了，所以不用再额外的进行一次date排序。看Extra列就可以看出mysql优化器直接使用的是联合索引。

为了和上述情况做比较，我们再分析一下：

explain 
select * from my_user order by date;

 可以看出，虽然 name、date字段创建了联合索引，但是执行select * from my_user order by date;时，没有用到索引，需要额外的一次filesort排序。

在没有任何索引的上述表中，我们现在只对name字段创建索引：

创建索引：

create index `name_index` on `my_user`(`name`);

分析查询：

explain
select * from my_user where name = 'gg' order by date;

查询结果：

 分析结果：在是由name索引时，执行同样的查询，通过Extra列可以看出mysql优化器除了使用name索引查询时，也使用了Using filesort额外的进行了一次排序。

综上所属，我们容易理解到联合索引的小组排序以及mysql优化器对联合索引小组排序的充分利用。同时我们在使用索引时，应该：

1. 遵循最左前缀原则。

2. 涉及到排序时，在有了联合索引的情况下，充分利用联合索引，没有联合索引的情况下，可以适当考虑对排序字段进行创建索引。

2. 覆盖索引

InnoDB存储引擎支持覆盖索引，覆盖索引就是：从辅助索引树上就能得到要查询的所有信息时，直接返回查询结果，而不用在去聚集索引树上用辅助索引的查询结果去再次查询整行数据。（主键索引树上做书签查询）

创建表：

CREATE TABLE `my_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

name字段上创建一个普通索引：

create index `name_index` on `my_user`(`name`);

分析：

explain
select `name` from my_user where `name` = '123';

结果：

 可以看出这个查询是使用到了覆盖索引。Extra中有 “Using index” 提示就是表明使用了覆盖索引。

分析：

explain
select count(*) from my_user where name = '12';

结果：

根据索引条件统计查询时 ，同样可以使用到覆盖索引。

3. 索引下推

Index Condition Pushdown  又称索引下推：一种根据索引进行查询的优化方式。在 Mysql5.6 之前，进行索引查询时，先在索引树上查询出结果集，然后再根据where条件过滤需要的结果。在 Index Condition Pushdown 出现后，mysql 在取出索引的同时，判断是否可以进行 where 条件的过滤，也就是将where的部分过滤放在了存储引擎层。在某些情况下，可以大大减少上层sql层对记录的索取，从而提高数据库的整体性能。

Index Condition Pushdown的优化支持 range、ref、eq_ref、ref_or_null类型的查询，支持MyISAM 和 InnoDB 存储引擎。当优化器选择 Index Condition Pushdown 优化时，可在执行计划的列 Extra 看到 Using index condition 提示。

创建表，且在 name、sex、address 字段上创建联合索引：

CREATE TABLE `my_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `union_name_index` (`name`,`sex`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

分析： 

explain
select * from my_user where name = '12' and sex like '%erre%' and address like '%papdas%';

结果：

  从Extra列中的 “Using index condition” 可以看出，sql执行中使用到了索引下推。name、sex、address字段创建了联合索引，但是因为sex和address使用了 like 范围查询，联合索引会失效，只有name最左条件这个索引会生效，并且存储引擎在查询出索引结果的同时会判断 sex 和 address 是否匹配，无需再额外的在name查询的结果集中再去使用where条件。

如果只在表的 name 字段上创建一个辅助索引：create index `name_index` on `my_user`(`name`);

再对上面的那个sql进行分析，分析结果：

 可以看出只是使用到了name_index这个辅助索引，然后再进行 where 条件的过滤，并没有使用到索引下推的优化。

4. Multi-Range Read 优化

Mysql5.6以后开始支持 MRR，优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的sql查询语句性能带来了极大的提升。MRR 优化可使用于 range、ref、eq_ref 类型的查询。

MRR优化的作用有以下几点：

1. MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的结果集，按照主键进行排序，并按照主键排序的顺序进行书签查找。

2. 减少缓冲池中页被替换的次数。

3. 批量处理对键值的查询操作。

对于 InnoDB 和 MyISAM 存储引擎的范围查询和JOIN查询操作，MRR 的工作方式如下：

1. 将查询得到的辅助索引键值存放于一个缓存中，这是缓存中的数据是根据辅助索引键值排序的。

2. 将缓存中的键值根据 RowID 进行排序。

3. 根据RowID排序顺序来访问实际的数据文件。

如何开启MRR?

SHOW VARIABLES LIKE '%optimizer_switch%';

set @@optimizer_switch='mrr=on,mrr_cost_based=off'

首先查询 optimizer_switch 中 mrr 和 mrr_cost_based 参数值，确定一下是否开启。mrr设置为on，mrr_cost_based=off 设置为 off 时，表示总是启用 Multi-Range Read。

看一个例子：

create index `order_id_index` on `my_user`(`order_id`);

explain
select * from my_user where order_id > 30000 and order_id < 40000;

在没有启用 Multi-Range Read 时，重启mysql服务，保证缓冲池中查询的数据没有被预热。然后执行上 select * from my_user where order_id > 30000 and order_id < 40000; 

 执行消耗了 192 毫秒。

看执行计划分析：

 从以上结果可以看出只使用到了辅助索引。

重启Mysql服务，启用 Multi-Range Read 。

重新执行同样的sql： select * from my_user where order_id > 30000 and order_id < 40000;

查看执行时间：

 执行了  106 毫秒。

看执行计划分析：

 使用到了辅助索引的同时，使用到了 MRR 优化。

上述 mrr 启用前后sql执行时间的测试对比，作者反复执行了多次，验证的结果执行消耗的时间：MRR 优化前和优化后的消耗比基本上都是 2:1 。因为表的数据量和服务器性能的原因，MRR 优化后对sql执行的性能提升也可能远高于 2:1 。读者如果想要验证，确保每次执行前都需要重启一下Mysql服务。确保缓冲池中没有预加载过查询的数据。

MRR 优化还可以将某些查询拆分为键值对，以此来进行批量数据查询。

创建表和对name、date字段创建联合索引：

CREATE TABLE `my_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `union_name_index` (`name`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

 执行查询操作：select * from my_user where order_id > 30000 and order_id < 40000 and date = '2020-12-01';

如果启用了 MMR。优化器会将条件拆分为（30001，‘2020-12-01’）、（30002，‘2020-12-01’）、（30003，‘2020-12-01’）......（39999，‘2020-12-01’）。最后再根据这些拆分的条件进行批量查询。

如果上述查询没有启用 MRR 优化，那么首先会把 大于30000 和 小于 40000 的所有数据都查询出来，然后再根据 date 条件逐一匹配，这样会导致无用的数据被取出。

4. 索引失效的情况

创建表：

CREATE TABLE `my_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

目前表中有10008条数据。在name字段上创建一个普通索引：name_index。

分别分析以下两条sql：

sql01:

explain
select * from my_user where `name` > '123' and `name` < 'jll钟爱的佛啊士大夫250';

sql02:

explain
select * from my_user where `name` > '123' and `name` < 'jll钟爱的佛啊士大夫250';

sql01的分析结果：

 sql02的分析结果：

 从以上结果分析可以看出：首先两条sql几乎一样，只有条件的查询范围不一样，sql01能查询出1647条数据，sql02能查询出10008条数据。sql01用到了辅助索引，而sql02没有用到索引，用到的是全表扫描。所以如果使用加了索引的字段进行范围查找时，不一定就用不到索引或者也不是一定能使用到索引。

为什么同样的sql，只是查询范围大小不一样，却造成了优化器最终执行的过程不一样呢？

原因：上表name字段加了索引，一般我们认为，使用name字段进行范围查询时，且要查询整行数据，那么sql肯定会使用到索引。但是真实情况却不是这样的，真实情况会因为范围查询出来的数据量大小导致mysql执行过程的差异化。因为name字段加了索引后，根据name字段范围查询出的结果集，需要再去聚集索引树上查询整行信息，name索引上查询出来的结果集再去聚集索引树上查询就属于离散读取了，磁盘上的离散度效率是很低的，如果name查询出来的结果集数据量比较少，那么还是会使用到name索引的，如果结果集数据量很大，mysql优化器认为可以直接去聚集索引树上全表查询，没必要额外的先在name索引树上查询，然后再去聚集索引上做离散查询。一般结果集占到整个数据量的20%时，索引就会失效。

5. 索引提示

索引提示一般不使用。一般只有两种情况下可能用到索引提示：

1. Mysql优化器错误的选择了某个索引，导致SQL执行很慢。

这种情况很少出现，尤其是在最新的mysql版本中。所以一般开发人员基本不使用索引提示。

2. 某sql语句可以选择的索引很多，这是优化器选择执行计划时间的开销可能会大于sql语句本身的执行时间。

如何使用索引提示？

首先说明作者的 my_user 表中 order_id 数据是1到60000。

查看一下这个表的索引：

 sql-01：

explain
select * from my_user use index(`order_id_index`) where order_id > 20000 and order_id < 40000;

执行计划：

 sql-02：

explain
select * from my_user force index(`order_id_index`) where order_id > 20000 and order_id < 40000;

执行计划：

 sql-01中只是显示提示优化器 `order_id_index` 这个索引可以使用，但是优化器并不一定使用这个索引。

sql-02中通过force index(`order_id_index`) 强制优化器使用 `order_id_index` 这个索引。优化器就会使用这个索引。