MySQL Index Condition Pushdown 原理与解析
2024-10-20 21:09:12
Index Condition Pushdown(ICP)是针对mysql使用索引从表中检索行数据时的一种优化方法。
原理:
在没有ICP特性之前,存储引擎根据索引去基表查找并将数据返回给mysql server,mysql server再根据where条件进行数据过滤。
有了ICP之后,在取出索引的同时,判断是否可以根据索引中的列进行where条件过滤,也就是将where的部分过滤操作放在了存储引擎层。这样就会减少上层sql层对记录的获取。
当sql使用覆盖索引时,不支持ICP优化方法。
ICP优化支持range、ref、eq_ref、ref_or_null类型的查询。查询优化器会给出相应的提示:Using index condition。当开启ICP后,在执行计划的extra列会显示:Using index condition。
ICP支持innodb、myisam表。对于innodb表,ICP只是用于辅助索引。
在5.6中,ICP不支持分区表。这个问题在mysql 5.7中得到解决。
优化器使用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之前:
开启ICP之后,就变成:
默认是开启ICP的,手动开启/关闭ICP:
set
optimizer_switch =
'index_condition_pushdown=off'
;
set
optimizer_switch =
'index_condition_pushdown=on'
;
测试过程
1.环境准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
#mysql 5.6.25 #关闭结果缓存 mysql> set global query_cache_size=0; mysql> set query_cache_type= off ; #查看表结构 mysql> show create table employees\G *************************** 1. row *************************** Table : employees Create Table : CREATE TABLE `employees` ( `emp_no` int (11) NOT NULL , `birth_date` date NOT NULL , `first_name` varchar (14) NOT NULL , `last_name` varchar (16) NOT NULL , `gender` enum( 'M' , 'F' ) NOT NULL , `hire_date` date NOT NULL , PRIMARY KEY (`emp_no`), KEY `idx_first_last_name` (`first_name`,`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> |
2.开启ICP后进行测试
1
2
3
4
5
|
mysql> set profiling = 1; mysql> select * from employees where first_name= 'Anneke' and last_name like '%sig' ; mysql> explain select * from employees where first_name= 'Anneke' and last_name like '%sig' ; mysql> show profiles; mysql> show profile cpu,block io for query 1; |
3.关闭ICP后进行测试
1
2
3
4
5
6
|
mysql> set optimizer_switch= 'index_condition_pushdown=off' ; mysql> set profiling = 1; mysql> select * from employees where first_name= 'Anneke' and last_name like '%sig' ; mysql> explain select * from employees where first_name= 'Anneke' and last_name like '%sig' ; mysql> show profiles; mysql> show profile cpu,block io for query 1; |
4.结果比较
开启ICP后的执行计划:执行计划中extra部分的内容是"using index condition"
1
2
3
4
5
6
|
mysql> explain select * from employees where first_name= 'Anneke' and last_name like '%sig' ; + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 44 | const | 224 | Using index condition | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+ |
关闭ICP后的执行计划:执行计划中extra部分的内容是"using where"
1
2
3
4
5
6
|
mysql> explain select * from employees where first_name= 'Anneke' and last_name like '%sig' ; + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+ | 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 44 | const | 224 | Using where | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+ |
开启ICP后的profile内容:Sending data部分的值是0.000212s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> show profile cpu,block io for query 1; + ----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | + ----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000114 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000034 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000383 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000212 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | + ----------------------+----------+----------+------------+--------------+---------------+ |
关闭ICP后的profile内容:Sending data部分的值是0.010990s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> show profile cpu,block io for query 1; + ----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | + ----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000165 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000037 | 0.001000 | 0.000000 | 0 | 0 | | statistics | 0.000483 | 0.001000 | 0.000000 | 0 | 0 | | preparing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.010990 | 0.007999 | 0.002000 | 0 | 0 | | end | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | + ----------------------+----------+----------+------------+--------------+---------------+ |
其它:
当sql使用覆盖索引时,不支持ICP优化方法
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> explain select first_name,last_name from employees where first_name= 'Anneke' and last_name= 'Porenta' ; + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 94 | const,const | 1 | Using where ; Using index | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+ mysql> explain select * from employees where first_name= 'Anneke' and last_name= 'Porenta' ; + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 94 | const,const | 1 | Using index condition | + ----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+ |
最新文章
- 学习ASP.NET Core,你必须了解无处不在的“依赖注入”
- 关于jQuery中环境配置中的问题
- python语言技巧
- Day1 三级目录
- 获得N位数字字母随机组合
- Android sendMessage 与 obtainMessage (sendToTarget)比较
- 基于NodeJs的网页爬虫的构建(二)
- 经常使用的自己定义UI组件- 一:TimeView
- Apache与Tomcat区别联系
- JavaScript之<;noscript>;标签简介
- Webbrowser控件execcommand参数详解
- WPF学习(6)路由事件
- Yii2之事件
- 获取input标签的值
- cocos2d-x JS 利用重复动作实现动画播放(实现倒计时)
- 第一天---关于环境和java基础
- [RN] 03 - Resource Collection &; AWS Auth
- linux优化之全过程
- 【数组】Spiral Matrix II
- [JSOI2009]电子字典 hash
热门文章
- “makefile”写法详解,一步一步写一个实用的makefile,详解 sed &#39;s,$∗\.o[ :]*,\1.o $@ : ,g&#39; <; $@.
- nginx错误
- linux 学习2 常用命令
- windows下socket编程:区分shutdown()及closesocket()
- Python函数(七)-匿名函数
- 部署和调优 1.1 nfs部署和优化-1
- java 截取替换掉括号 包括括号中的内容
- 地图投影的N种姿势(转载)
- 如何关闭打开了多个activity的activity
- Solr搜索的排序打分规则探讨