MyException - 我的异常网
当前位置:我的异常网» MySQL » 浅析MySQL中的Index Condition Pushdown (ICP 目录

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

www.MyException.Cn  网友分享于:2013-08-22  浏览:0次
浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read(MRR 索引多范围查找)查询优化

 

本文出处:http://www.cnblogs.com/wy123/p/7374078.html
(保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)

 

 

ICP优化原理

Index Condition Pushdown (ICP),也称为索引条件下推,体现在执行计划的上是会出现Using index condition(Extra列,当然Extra列的信息太多了,只能做简单分析)
ICP原理通俗讲就是,查询过程中,直接在查询引擎层的API获取数据的时候实现"非直接索引"过滤条件的筛选,而不是查询引擎层查询出来之后在Server层筛选。
换句话说就是ICP在获取数据的同时实现了where的次选条件中无法直接使用索引的情况下的筛选,避免了没有ICP优化的时候分两个步骤的实现(获取数据的过程没有做次选条件的过滤)
如果是非ICP优化查询的话,是两步,第一步是获取数据,第二步是获取的数据进行条件筛选。
显然,相比后者,前者可以一步实现索引的查找Seek+filter,效率上更高。

适应的场景:
ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法

 

其实没有实例不太好理解这种优化策略,还是举两个实际列子吧。

 

ICP优化实例

第一个例子在网上非常多,也非常容易理解.具体表结构见上文(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%'这个无法直接使用索引查找的条件进行过滤。
最终一步就可以筛选出来结果。

  对比关闭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~),

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

 

1楼聂小小乜
myisam是堆表
Re: MSSQL123
@聂小小乜,谢谢,myisam 的话,已经渐渐开始脱离主流了引擎了,我没有关注过myisam

文章评论

程序员应该关注的一些事儿
程序员应该关注的一些事儿
我是如何打败拖延症的
我是如何打败拖延症的
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
我的丈夫是个程序员
我的丈夫是个程序员
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
程序员都该阅读的书
程序员都该阅读的书
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
中美印日四国程序员比较
中美印日四国程序员比较
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
10个调试和排错的小建议
10个调试和排错的小建议
Java程序员必看电影
Java程序员必看电影
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
编程语言是女人
编程语言是女人
 程序员的样子
程序员的样子
程序员的鄙视链
程序员的鄙视链
旅行,写作,编程
旅行,写作,编程
那些争议最大的编程观点
那些争议最大的编程观点
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
如何成为一名黑客
如何成为一名黑客
漫画:程序员的工作
漫画:程序员的工作
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员必看的十大电影
程序员必看的十大电影
每天工作4小时的程序员
每天工作4小时的程序员
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
代码女神横空出世
代码女神横空出世
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
一个程序员的时间管理
一个程序员的时间管理
总结2014中国互联网十大段子
总结2014中国互联网十大段子
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有