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

时间:2022-06-14 15:48:59

本文出处: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%'这个无法直接使用索引查找的条件进行过滤。
最终一步就可以筛选出来结果。

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

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

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

  

  第二个例子是后面自己想的,为了验证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优化上的区别

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

  关闭ICP之后的执行计划

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

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

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

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

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

  引用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的前提两个查询条件包被索引覆盖,但是次选条件无法直接使用索引查找,如果次选条件没有被索引覆盖,是无法得知次选条件的值的,也就无从 索引条件下推优化了。

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

  

Multi-Range Read(MRR)

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

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

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

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

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

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

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

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的机会

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

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

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

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

  浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read(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 索引多范围查找)查询优化

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

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