【索引潜规则】-覆盖索引、ICP、MRR详解

点击上方蓝色字体，关注我们

    上篇文章我们说了创建索引的方法，有聚簇索引、辅助索引、前缀索引、联合索引等，也说了如何利用索引的排序功能，接着本篇文章主要来说一说索引的几种优化策略，首先我们先说下回表的概念。

    假设有这么一条SQL，select * from t where age=23，Innodb会通过二级索引找到主键的值20，然后拿着20再回到聚簇索引树搜索找到要找的行数据(data)，这一过程我们就称为回表。下图就展示了该条SQL回表的过程。

    通过上图我们可以看到回表的过程需要扫描两棵树，这样增加了磁盘扫描，如何避免回表呢？这就引出了我们下面要将的内容：覆盖索引。

    MySQL只需要通过索引就能取到想要的数据，不需要在回表查询数据了，也就说在这个查询中，索引age已经覆盖了我们的查询需求，这种情况称之为覆盖索引，其实我们在上一篇讲联合索引时已经用到了覆盖索引的技术了。

    举个例子：select id from t where age=23，我们只需要拿到id就行了，不需要知道其他的字段值。通过explian查看执行计划时，可以在Extra列看到using index，表示用的覆盖索引。

由于覆盖索引不需要回表，减少了树的搜索次数，能显著的提升查询性能。

mysql> explain select id from t where age=23;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ref  | age           | age  | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

    Index Condition Pushdown是MySQL5.6引入的根据索引从表中检索行的一种查询优化方式。

在没有使用ICP技术时，存储引擎会遍历索引然后回表找到对应的行，并将它们返回给MySQL服务器，服务器根据where条件进行过滤。启用ICP后，可以在索引遍历过程中，由存储引擎对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少了回表的次数。ICP可以减少回表的次数，同时也能减少server层与引擎层交互的次数。

接着我们还是用一个列子来说明下索引下推吧，还是用上篇文章的表，建表语句如下

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `addr` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB

SQL语句是：

select * from t where age = '30' and name  like '%ck';

    首先select *无法使用到覆盖索引的策略，上篇文章我们也说过范围查找时%放在前面时无法利用索引查找，这条SQL只能用到联合索引的age列。

    当不使用ICP策略时，引擎会通过二级索引根据age=30的条件找到对应的rowid，再根据rowid回表找到对应的数据行，然后拿到server层，最后在server层根据where条件进行过滤。

    开启ICP策略后，引擎在遍历索引的过程中就会对name字段进行判断，直接过滤掉不满足 name like '%ck' 条件的记录，然后再去获取行记录。减少了回表的次数，也减少了server层的二次判断，大大提高了查询的效率。

    当使用ICP时，通过explain查看执行计划时Extra列会显示Using index condition，如下图：

ICP优化策略虽然好，但是受限于以下条件（根据官方文档整理）：

• ICP用于range、ref、eq_ref和ref_or_null等访问方式且需要访问数据行时。

• ICP可以用于Innodb和MyISAM引擎表，包括两种引擎的分区表。

• 对于Innodb引擎表，ICP只适用于二级索引。ICP的主要目的就是减少回表的次数，从而减少I/O操作。但是对于Innodb引擎的聚簇索引，完整的数据行记录已经被读入到innodb bufferpool中，在这种情况下使用ICP并不能减少I/O操作。

• 在条件引用子查询时无法使用ICP。

• 当使用存储函数时无法使用ICP，存储引擎无法调用存储函数。

对于以下两种情况可以考虑使用ICP优化策略。

1. 对于where constant + like 查询时可以尝试创建联合索引。

select * from t where age = '30' and name  like '%ck';
select * from t where age like '30%' and name = 'jack';

2. 对于where constant + order by index column时可以尝试创建联合索引。

select * from t where age = '30' order by name;

MySQL是默认启用索引下推策略，可以通过optimizer_switch变量控制是否开启

SET global optimizer_switch = 'index_condition_pushdown=off';#关闭ICP策略
SET global optimizer_switch = 'index_condition_pushdown=on';#开启ICP策略

    MRR技术也是MySQL5.6版本开始引入的，当一个表很大并且没有缓存在bufferpool中时，由于二级索引和主键的排列顺序一般情况下是不一样的，在二级索引上使用范围扫描回表读取行数据时会导致产生大量的随机I/O，通过MRR优化，MySQL会通过索引扫描收集相关行数据的主键，将主键值的集合存储到read_rnd_buffer中，然后在buffer中对主键进行排序，最后利用排好序的主键再回表查询。同时，如果缓冲池不够大的话，频繁的离散读还会导致缓存中的页频繁的被替换出缓冲池，然后又不断的被读入缓冲池，若按照主键顺序进行访问的话，可以减少数据页的读取，降低数据页被频繁替换出入缓冲池的情况。

    MRR优化的目的就是为了减少磁盘的随机访问，并将随机I/O转化顺序I/O，降低查询过程中的I/O开销，同时减少缓冲池中数据页被替换的频次。

举个栗子（例子），还是用上一节的t表，SQL语句如下：   

select * from t where age >30 and age <80;                                                                       

从下图可以看出这条SQL采用MRR优化策略。

可以通过以下命令来开启或者关闭MRR策略。

#表示依据基于成本的算法选择是否启用MRR优化，如果发现优化后的成本过高就不使用MRR优化
SET global optimizer_switch = 'mrr =on, mrr_cost_based =on';
#表示总是开启MRR优化。
SET global optimizer_switch = 'mrr =on, mrr_cost_based =off';

    在上篇文章中我们介绍了B+Tree以及B+Tree索引的种类，今天这篇文章又介绍了索引内部的一些优化策略，比如使用覆盖索引、索引下推、MRR等，最后我们对索引的知识做一个总结。

• 减少磁盘扫描，提高检索效率，避免了全表扫描。

• 提高排序和分组的效率。

• 将随机IO转化为顺序IO。

• 提高部分聚合函数的效率，比如min()，max()等。

• 在经常用于排序和分组查询的字段上建立索引，可以避免了内存排序和随机I/O。

• 在选择性较高的字段上建立索引，查看选择性公式select count(distinct a)/count(*) from t1，越接近1越好，一般超过33%就算是比较高效的索引了。

•  如果没有强烈的业务需求，建议建立自增主键，这样的主键占用空间小，顺序写入，减少页分裂。

• 利用较短的键值作为索引性能比较好，可能的话尽量使用整数类型。

• 对于where条件中涉及多个字段时可以考虑建立联合索引，建议将选择性高的列放到

  索引最左列，SQL查询时满足最左原则。

• 对于select后面经常用到的字段可以考虑创建索引，查询时使用覆盖索引查询，避免回表。

• 索引字段尽量设置为NOT NULL，NULL值会更加运算的复杂度。

• 如果有 order by 的场景，尽量利用索引的有序性，避免出现using filesort 的情况，影响查询性能，请参看上一章的联合索引部分。

• SQL投产前查看执行计划，SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

• SQL语句中尽量避免使用左模糊或者全模糊查询，无法利用B+Tree 最左前缀匹配特性。

• 考虑针对较长字符串型列使前缀索引，区分度可以使用 count(distinct left(列名, 索引长度))/count(*)来确定，请参看上一章的前缀索引部分。

• 业务上具有唯一特性的字段，即使是组合字段，也建议建成唯一索引，数据库层面避免了脏数据的产生，对insert的影响可以忽略（阿里巴巴开发手册要求）。

• 在表查询中，建议明确字段，不要使用 * 作为查询的字段列表。

• 索引不宜过多，一般建议不超过6个，由于索引的创建和维护是有代价的，所以请不要创建不必要的索引。

• 定期清理冗余索引、未使用过得索引以及查看全表扫描的SQL等，具体监控手段请参见之前的文章<<MySql监控分析视图-sys schema>>

• 通过索引扫描的行数超过全表的20%-30%时，引擎会认为走全表扫描更有效。

• 使用联合索引时没有遵循最左原则。

• where后面出现 or条件 ，且没有建立单列索引会导致失效。

• 对索引使用了函数计算。

• 统计信息不真实(严重不真实)，导致执行计划错误。

• 访问小表时，更倾向于全表扫描。

• Where条件中对索引列使用左模糊或者全模糊查询。

    作为数据库DBA来说，性能调优是一个永恒的话题，索引调优也只是其中的一部分。要想写一个高效的查询，必须要理解schema设计、索引设计等，后面我还会为大家带来跟多的性能调优的知识，关注我，更精彩!

------------------------------------------------------------------------------

原创不易，每篇文章都是作者工作之余熬夜整理与创作，如果觉得文章对您有帮助的话，关注、转发、分享、点在看对作者来说都是一种鼓励，您的鼓励将是我持续发表高质量文章的最大动力。

关注MySQL

数据库技术栈

MySQL