Mysql不止CRUD，聊聊索引( 六 ) 我想很多人对mysql的认知可能就是CRUD

查看：explain select store_id,create_time from item where store_id > 332604504321036698 ;
文章插图
这里使用了全表扫描，没有走索引，然后我们把查询语句改为：explain select store_id from item where store_id > 332604504321036698 ;
文章插图
这样就变成了范围查询，走索引，因为索引中包含了需要查询的全部值，所以不需要再查询聚集索引，减少磁盘IO ，这样就可以提高速度。
2.7、Multi-Range Read优化Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。 Multi-RangeRead优化可适用于range ， ref ， eq_ref类型的查询。
Multi-Range Read的好处：

MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作

MRR的工作方式如下：

将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际的数据文件。

实验：采用前面2.1节导入的数据：
explain select * from foundation_item.item where store_id > 332604504249736122 and store_id < 332604504249736201;

文章插图
MySQL5.6版本开始支持Multi-Range Read（MRR）优化，通过参数 optimizer_switch 的标记来控制是否使用MRR ，当设置mrr=on时，表示启用MRR优化。 mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化。
例如设置：set optimizer_switch='mrr=on,mrr_cost_based=on';然后我们继续查看：
explain select * from foundation_item.item where store_id > 332604504249736122 and store_id < 332604504249736201;

文章插图
如上：MRR优化关闭后没有启动了
2.8、Index Condition Pushdown（ICP）优化MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能，优化支持range、ref、eq_ref、ref_or_null类型的查询，选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index condition提示。
实验：数据来源于2.1生成的数据：
首先我们创建一个组合索引：create index idx_email_id_address_state on foundation_commons.email (state,address,email_id);
explain SELECT * from foundation_commons.email where state =0 and address like '%D%' and email_id >='332604504249734136' ;

文章插图
以上实验只是为了显示这样的现象
1、当sql需要全表访问时， ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法。 2. 支持InnoDB和MyISAM表。 3. ICP只能用于二级索引，不能用于主索引。 4. 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。