MySQL的MRR和和ICP

首先要明白辅助索引、聚簇索引、覆盖索引的概念

• 辅助索引：只包含索引列的值和书签（主键索引值），在查找到索引列时，还需要根据书签查找响应的聚簇索引获得列值，并且数据的存储并不是有序的。
• 聚簇索引：按照主键排序，并且存储数据行，查询到数据可直接得到其他列的数据。
• 覆盖索引：查询的列都在联合索引中，不需要回表查数据。

MRR（Multi-Range Read）

在使用辅助索引进行查询时，查询到的数据存储并不是有序的，所以此时的磁盘 IO 为随机 IO，开启MRR后，有下面两个优势：

• 查询到的辅助索引会放到缓存中，然后在缓存中根据主键ID进行排序，然后再根据排序好的顺序去磁盘中查询数据，此时为顺序IO，效率大大提升。
• 同时因为是顺序 IO，所以还可以充分利用磁盘预读。

如果可以完全基于索引元组中的信息（通过覆盖索引）来生成结果，那么MRR没有起到任何优化作用。

开启MRR：

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

explain的Extra列会显示：Using MRR

ICP（Index Condition Pushdown）

在不开启ICP时，在where条件使用辅助索引查询数据时，会先取出全部索引，然后用索引的主键ID回表查找实际记录，在查找每一行数据时对比where条件，不符合就丢掉，此时会浪费许多不必要的磁盘IO，开启ICP后，会在取出索引的时，将索引下推到存储引擎层，在存储引擎层就对比where，然后如果符合条件，再去磁盘查找对应的数据行，极大提高效率。

ICP可以用于InnoDB表和MyISAM表，包括分区的InnoDB和MyISAM表。 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少整行读取的次数，从而减少I/O操作。对于InnoDB聚集索引，完整的记录已经被读入InnoDB缓冲区。在这种情况下使用ICP不会减少I/O。

开启ICP：

SET optimizer_switch = 'index_condition_pushdown=on';

explain的Extra列会显示：Using index condition

版本支持

这两个特性都在5.6以上才支持