浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read（MRR 索引多范围查找）查询优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read（MRR 索引多范围查找）查询优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6181字，纯文字阅读大概需要9分钟。

内容图文

第一个例子在网上非常多，也非常容易理解.具体表结构见上文（http://www.cnblogs.com/wy123/p/7366486.html）

下面用到的test_orderdetail表的索引为：create index idx_orderid_productname on test_orderdetail(order_id,product_name);
查询语句为：select * from test_orderdetail where order_id = 10900 and product_name like ‘%00163e0496af%‘;
显然，order_id = 10900是可以直接进行索引查找的，虽然product_name也包含在复合索引中，但是product_name like ‘%00163e0496af%‘是无法使用索引的
观察其执行计划，发现Extra中是Using index condition。

ICP在这里的优化原理就是，
在利用第一个条件 order_id = 10900 进行索引查找的过程中，同时使用product_name like ‘%00163e0496af%‘这个无法直接使用索引查找的条件进行过滤。
最终一步就可以筛选出来结果。

set optimizer_switch=‘index_condition_pushdown=off或者on‘

　　对比关闭ICP优化的情况
　　如果关闭ICP优化，执行计划的Extra显示为Using where，
　　意味着使用order_id = 10900进行索引查找之后，再对结果集进行product_name like ‘%00163e0496af%‘的筛选

　　第二个例子是后面自己想的，为了验证ICP的出现场景，以及确实优于非ICP优化的情况

这一次使用的表是test_order，test_order上的索引为create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
查询语句为：select * from test_order where user_id = 500 and create_date > ‘2015-1-1‘;
与上面的例子一样，第二个筛选条件是无法直接使用索引的

 　   首先看两者的执行计划在ICP优化上的区别

　　关闭ICP之后的执行计划

　　然后分别执在打开与关闭ICP的情况下，观察其执行过程中的profile信息

　　查看两个sql执行的详细信息，也即分别在打开与关闭ICP优化的情况下，如下，在stage/sql/Sending data环节有超过一个数量级的差异。
　　也就意味着通过ICP机制的优化，server 层和 engine 层之间数据交互的次数减少。

　　引用MySQL · 特性分析 · Index Condition Pushdown (ICP)中的一句话：
　　在二级索引是复合索引且前面的条件过滤性较低的情况下，打开 ICP 可以有效的降低 server 层和 engine 层之间交互的次数，从而有效的降低在运行时间。

　　最后，再思考一个问题，
　　对于select * from test_orderdetail where order_id = 10900 and product_name like ‘%00163e0496af%‘;这个查询，
　　如果order_id 包含在一个二级索引中，但是product_name 没有包含在这个二级索引中，MySQL会不会采用ICP的方式进行优化？
　　答案是否定的。
　　因为ICP的前提两个查询条件包被索引覆盖，但是次选条件无法直接使用索引查找，如果次选条件没有被索引覆盖，是无法得知次选条件的值的，也就无从 索引条件下推优化了。

Multi-Range Read（MRR）

非MRR优化下存在的问题：
首先了解一点背景知识：MySQL的Innodb表都是聚集索引表，没有显式指定聚集索引的情况下，会自动生成一个聚集索引。
在使用二级索引（或者说是非聚集索引）进行范围查询的条件下，二级索引会根据其B树结构的叶子节点存储的聚集索引进行数据的查找（回表操作），
但是符合条件的数据（二级索引超找的数据）有可能是随机分布在聚集索引B树的任何一个部分，这样就可能存在表上过多的随机IO。
当表非常大的时候，每一行的查找过程都需要在磁盘上随机进行，可能会对性能造成影响。

举个例子，
如下图，参考蓝线的移动轨迹，二级索引查找到的目标数据行的物理位置为1，2，3，4（主要的是以何种顺序去获取这四个位置的数据，可以随机的方式获取，也可以顺序的方式获取，讲究就在这一点）
在查找这四个位置的数据的时候，如果直接按照二级索引对应的聚集索引的顺序查找，
由于二级索引排序的情况下，其对应的聚集索引的顺序可能是随机的，那么其对应的数据的物理位置也就是随机的了
如果按照二级索引的顺去回表超找对应的数据行，那么这个过程就需要随机IO查找。
这种查询方式的缺点，可以理解为在查询这四行数据的过程中，在物理位置差异较大的情况下，需要磁头来回摆臂来实现（随机IO读取）。

MRR多范围读取优化的目的是通过对记录的读取请求进行排序，然后再读取数据行的时候以顺序IO的方式进行，避免随机IO
究竟是对哪个字段排序？个人认为可以理解成二级索引范围查找到的对应的聚集索引的key值进行排序。
有序扫描的过程可以认为是：

（1）通过非聚集索引找到目标数据的聚集索引的key值
（2）对通过二级索引找到的目标数据的聚集索引的key值排序，此时聚集索引与物理位置一一对应。
（3）（回表的过程）通过二级索引对应的有序的聚集索引，执行一个有序的磁盘扫描来获取数据，从而来加快读取数据的速度。

顺序读磁盘通常会更快，当然也不是说这种方式的效率总是较高的，凡事有利必有弊，也有例外的情况

1，如果扫描的是一个较小的数据范围，并且目标数据已经在磁盘的缓存当中，MRR的唯一影响是为了缓冲/排序额外的增加了一些CPU开销。
2，order by *** LIMIT n查询，当n值比较小的时候，可能会变的更慢，
　  原因是 MRR试图通过顺序读盘的方式（来或取数据），可能一开始读取到的数据并非总是排在（order by ***）符合前N条的。
3，MRR是一个实现过程，个人理解，极端情况下，如果MySQL不知道目标数据的行数，
　  如果仅仅只有一行，依然要进行排序操作，然后回表读取数据行，这种情况下也是得不偿失的。

　　打开MRR优化
　　set global optimizer_switch = ‘mrr=on,mrr_cost_based=off‘;

　　启用MRR优化的前提是要进行书签超找，也即要回表，如果不需要回表的话，二级索引本身就可以查询出来需要的字段了，没有随机IO的机会的所谓了。

　　如下截图，如果去掉order_status，也就意味着无需回表查询，那么就不会出现MRR优化了。

　　同时，一旦出现MRR优化，查询出来的结果的顺序，必然是按照聚集索引来排序的，这个原理应该是不难理解的。

　　当然MRR优化也有在表关联情况下的优化措施，原理大同小异。

总结：

　　　　Index Condition Pushdown（索引条件下推）和Multi-Range Read（多范围读）都是MySQL为了提高查询优化而备用的选项，属于MySQL5.6里面的新特性。
　　　　无奈楼主接触MySQL不久，见识不够，很是觉得新鲜，高手勿喷。
　　　　两者的共同的特点都是在使用索引超找（或者索引范围扫描）的过程中的一些优化措施。
　　　　这些优化措施可以在二级索引查找（索引范围扫描）的过程中优化查询动作的行为，
　　　　当然这些优化措施并非总是万能的，允许用户显式打开或者关闭，给用户充分的自由，然而自由也并非完全没有问题，这也要求用户在做相关优化的时候需要进行充分的权衡和考虑。

参考：

　　　　https://mariadb.com/kb/en/mariadb/multi-range-read-optimization/
　　　　http://blog.itpub.net/22664653/viewspace-1673682/
　　　　http://blog.itpub.net/22664653/viewspace-1678779/
 　　　   http://mysql.taobao.org/monthly/2015/12/08/

 　　　　以及各种网上搜索……

最后，mariadb官方这几张图非常赞，对理解问题很有帮助，先盗下来，备用（无耻一笑，O(∩_∩)O~），

突然又想到做人了，为什么一定要直来直去呢，很多时候是欲速则不达，迂回一下，暂时停下来，好好计划计划再出发，未必是坏事。

浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read（MRR 索引多范围查找）查询优化

标签：错误   排序   b树   范围查询   tag   提高   引擎   name   margin   

本文系统来源：http://www.cnblogs.com/lonelyxmas/p/7503282.html

内容总结

以上是互联网集市为您收集整理的浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read（MRR 索引多范围查找）查询优化全部内容，希望文章能够帮你解决浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read（MRR 索引多范围查找）查询优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端