2.3 MySQL中的索引优化

MySQL自带一些索引优化方法，比较常见的是ICP和MRR，本节主要介绍这两个优化方法。

2.3.1 ICP

索引条件下推（Index Condition Pushdown，ICP）是针对MySQL通过索引从表中查询行数据的优化。在没有ICP的情况下，存储引擎遍历索引以定位表中匹配的行数据，并将这些行数据返回给 MySQL服务，MySQL服务对这些行再次进行where条件的过滤。启用ICP后，在取出索引的同时，MySQL服务将where条件下推到存储引擎。存储引擎使用索引项来评估推入的索引条件，只有满足这个条件，才从表中读取行。启用ICP可以减少存储引擎访问表的次数和MySQL服务访问存储引擎的次数。

ICP的适用范围如下。

● 当需要访问全表时，ICP适用于range、ref、eq_ref和ref_or_null访问方法。

● 可以用于InnoDB表和MyISAM表，包括分区InnoDB表和MyISAM表。

● 对于InnoDB表，ICP只用于辅助索引，ICP的目标是减少整行读取的次数，从而减少I/O操作，对于InnoDB聚集索引，完整的记录已经被读入InnoDB缓冲区中，在这种情况下使用ICP并不会减少I/O操作。

● 在虚拟列上创建的二级索引不支持ICP。

● 引用子查询的条件不能使用ICP。

● 引用存储过程、触发器的条件不能使用ICP。

下面创建一张测试表t3，并写入测试数据：

如果要通过字段a和字段b查询字段c的值，但是字段b的值只知道最后一个字母，那么SQL语句如下：

关闭ICP，操作如下：

查看执行计划：

打开ICP：

再次查看执行计划：

可以看到，Extra变成了Using index condition，这正是MySQL使用ICP时Extra输出的内容。

下面对上面的实验进行分析。上面的查询可以使用索引来扫描a=1的记录，在没有ICP的情况下，必须检索所有满足a=1的行，同时进行b like＇%j＇的过滤；在使用ICP的情况下，MySQL在读取完整行之前就会检查b like＇%j＇的部分，这样可以避免读取满足a=1但不满足b like＇%j＇的行。

2.3.2 MRR

当MySQL的表很大并且没有存储在存储引擎的缓存中时，使用二级索引上的范围扫描读取行会导致对表的许多随机磁盘访问。先使用MRR（Multi-Range Read）优化，MySQL会尝试通过只扫描索引并收集相关行的键来减少范围扫描的磁盘访问次数，然后对键进行排序，最后按主键的顺序从表中检索数据。MRR的目的是减少磁盘访问次数。

需要注意的是，在虚拟列上创建的二级索引不支持MRR优化。

optimizer_switch 中有两个参数用于控制 MRR：mrr 和 mrr_cost_based。mrr 控制MRR是否开启，在mrr=on的情况下，mrr_cost_based表示优化器是否尝试在使用和不使用MRR或尽可能使用MRR之间做出基于成本的选择。

当使用MRR时，explain输出中的Extra变为Using MRR。