文档章节

MySQL Index Condition Pushdown

IT--小哥
 IT--小哥
发布于 2017/09/11 18:45
字数 1231
阅读 23
收藏 0

#程序员薪资揭榜#你做程序员几年了?月薪多少?发量还在么?>>>

一、Index Condition Pushdown简介

ICP(index condition pushdown)是mysql利用索引(二级索引)元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是:存储引擎在访问索引的时候检查筛选字段在索引中的where条件(pushed index condition,推送的索引条件),如果索引元组中的数据不满足推送的索引条件,那么就过滤掉该条数据记录。ICP(优化器)尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过过滤不相关的数据,仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行,而不是返回所有数据给server层,然后后再根据where条件进行过滤。

二、ICP开启和关闭时数据访问和提取过程对比

优化器没有使用ICP时,数据访问和提取的过程如下:

1):MySQL Server发出读取数据的命令,这是在执行器中执行如下代码段,通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。

if (in_first_read)
    {
      in_first_read= false;
      error= (*qep_tab->read_first_record)(qep_tab); //设定合适的读取函数,如设定索引读函数/全表扫描函数
    }
    else
      error= info->read_record(info);

2、3):进入存储引擎,读取索引树,在索引树上查找,把满足条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要进行进行步骤④,通常有IO。

6):从存储引擎返回查找到的多条元组给MySQL Server,MySQL Server在⑦得到较多的元组。

7、8):⑦到⑧依据WHERE子句条件进行过滤,得到满足条件的元组。注意在MySQL Server层得到较多元组,然后才过滤,最终得到的是少量的、符合条件的元组。

 

优化器使用ICP时,server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。

数据访问和提取过程如下:

1)    storage engine从索引中读取下一条索引元组。

2)    storage engine使用索引元组评估下推的索引条件。如果没有满足where条件,storage engine将会处理下一条索引元组(回到上一步)。只有当索引元组满足下推的索引条件的时候,才会继续去基表中读取数据。

3)    如果满足下推的索引条件,storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4)    server层评估没有被下推到storage engine层的where条件,如果该行数据满足where条件则使用,否则丢弃。

三、ICP测试

3.1  对比执行计划的差别

联合索引的第一个条件可以使用索引,第二个不能使用索引

root@localhost:mysql.sock  15:33:47 [test]>explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

|  1 | SIMPLE      | person | NULL       | range | idx_p_a       | idx_p_a | 7       | NULL |    1 |    33.33 | Using index condition |

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

1 row in set, 1 warning (0.11 sec)

关闭ICP后

root@localhost:mysql.sock  15:35:42 [test]>set optimizer_switch = "index_condition_pushdown=off";

Query OK, 0 rows affected (0.00 sec)



root@localhost:mysql.sock  15:39:48 [test]>explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | person | NULL       | range | idx_p_a       | idx_p_a | 7       | NULL |    1 |    33.33 | Using where |

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

where条件包含索引字段但用不到索引

root@localhost:mysql.sock  15:39:49 [test]>explain select  *   from person  where age > 40;

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)



root@localhost:mysql.sock  15:41:03 [test]>set optimizer_switch = "index_condition_pushdown=on";

Query OK, 0 rows affected (0.00 sec)



root@localhost:mysql.sock  15:41:09 [test]>explain select  *   from person  where age > 40;

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

结论:

需要index condition pushdown 的query通常索引的字段出现where子句里面都是范围查询。比如:

select * from tb where tb.key_part1 < x and tb.key_part2 = y       
select * from tb where tb.key_part1 = x andtb.key_part2 like '%yyyy%'
select * from tb where tb.key_part1 > x and tb.key_part1 < y and tb.key_part1 > xx and tb.key_part2 < yy

但是需要注意的是:
1. 如果索引的第一个字段的查询就是没有边界的比如 key_part1 like '%xxx%',那么不要说ICP,就连索引都会没法利用。
2.
如果select的字段全部在索引里面,那么就是直接的index scan了,没有必要什么ICP

为了方便大家交流,本人开通了微信公众号,和QQ群1(291519319)和QQ群2(659336691)。喜欢技术的一起来交流吧

© 著作权归作者所有

IT--小哥
粉丝 48
博文 182
码字总数 189144
作品 0
东城
数据库管理员
私信 提问
加载中

评论(0)

MySQL 查询优化之 Index Condition Pushdown

MySQL 查询优化之 Index Condition Pushdown Index Condition Pushdown限制条件 Index Condition Pushdown工作原理 ICP的开启与关闭 使用ICP示例 是MySQL使用索引从表中检索行数据的一种优化...

osc_yny7gjj7
2018/09/03
1
0
【MySQL】性能优化之 Index Condition Pushdown

一 概念介绍 Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。 a 当关闭ICP时,index 仅仅是data access 的一种访问方式,存储...

osc_o7tmguzk
2018/01/28
2
0
MySQL 索引条件下推 Index Condition Pushdown

MySQL 索引条件下推 Index Condition Pushdown 出现在MySQL5.6及之后的版本中,能大幅提升查询效率,原因如下: 内容摘录自《深入理解MariaDB和MySQL》 下面使实验,使用官方提供的employees...

lirulei90
2018/01/12
0
0
一起学习Mysql索引三(ICP,索引条件下推)

上一篇文章一起学习Mysql索引二(索引的高性能策略)中我们提到了Mysql5.7版本的一个改进: "索引条件下推"(index condition pushdown)。 那么,今天就让我们来揭开它的神秘面纱。 从ICP(...

281824088
03/11
0
0
MySQL索引与Index Condition Pushdown

大约在两年前,我写了一篇关于MySQL索引的文章。最近有同学在文章的评论中对文章的内容提出质疑,质疑主要集中在联合索引的使用方式上。在那篇文章中,我说明联合索引是将各个索引字段做字符...

whc20011
2016/11/02
25
0

没有更多内容

加载失败,请刷新页面

加载更多

二、netcore跨平台之 Linux部署nginx代理webapi

原文: 二、netcore跨平台之 Linux部署nginx代理webapi 上一章,我们讲了在linux上安装netcore环境,以及让netcore在linux上运行。 这一章我们开始讲在linux上配置nginx,以及让nginx反向代理...

osc_jo2m8l1r
23分钟前
10
0
CAD怎么转PDF文件?使用这款编辑器一键转换、批量转换

CAD怎么转换成PDF文件呢?小伙伴们不妨使用这款CAD编辑器,把CAD文件一键、批量转换成PDF文件哦。 有许多小伙伴应该都知道,为了满足各种学习、工作的需求,文件之间是经常需要相互转换格式的...

真不莲
24分钟前
19
0
详解Microsoft.AspNetCore.CookiePolicy

原文: 详解Microsoft.AspNetCore.CookiePolicy 详解Asp.Net Core中的Cookie策略 目录 详解Asp.Net Core中的Cookie策略 功能介绍 使用Cookie策略 从UseCookiePolicy方法入手 实现IResponseCo...

osc_0vd38ylb
25分钟前
16
0
怎么找到自己收藏过的思维导图模板?迅捷画图教你详细步骤!

怎么找到自己收藏过的思维导图模板?大家在刷视频的时候,遇到自己喜欢的视频,都会点个微信或者关注,用电脑看网页的时候,遇到有意思的网站,也会点击添加书签进行收藏,方便以后阅读或者是...

赛利亚大姐大
26分钟前
6
0
Microsoft.AspNetCore.Authentication.Cookies从入门到精通 (一)

原文: Microsoft.AspNetCore.Authentication.Cookies从入门到精通 (一) Microsoft.AspNetCore.Authentication.Cookies从入门到精通 (一) 目录 Microsoft.AspNetCore.Authentication.Cook......

osc_t5nbj8ds
26分钟前
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部