MySQL5.6之Index Condition Pushdown(ICP,索引条件下推)

╰半夏微凉° 2022-04-16 03:12 225阅读 0赞

转载 :[https://blog.csdn.net/z69183787/article/details/53954914][https_blog.csdn.net_z69183787_article_details_53954914]

http://mdba.cn/2014/01/21/index-condition-pushdownicp%E7%B4%A2%E5%BC%95%E6%9D%A1%E4%BB%B6%E4%B8%8B%E6%8E%A8/

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（mysql 5.6版本以前）和没有使用ICP的数据访问和提取过程如下（插图来在[MariaDB Blog][]）：

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

1)    当storage engine读取下一行时，首先读取索引元组（index tuple），然后使用索引元组在基表中（base table）定位和读取整行数据。

2)    sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。

3)    执行1），直到最后一行数据。

[![index-access-2phases][]][index-access-2phases]

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下：

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

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

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

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

[![index-access-with-icp][]][index-access-with-icp]

而使用ICP时，如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）。存储引擎通过索引元组的索引列数据过滤不满足下推索引条件的数据行。

索引条件下推的意思就是筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。由此可见，ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。

注意一下ICP的使用条件：

1.  只能用于二级索引(secondary index)。
2.  explain显示的执行计划中type值（join 类型）为range、 ref、 eq\_ref或者ref\_or\_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
3.  ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

ICP的开启优化功能与关闭

MySQL5.6可以通过设置optimizer\_switch(\[global|session\],dynamic)变量开启或者关闭index\_condition\_push优化功能，默认开启。

mysql > set optimizer\_switch=’index\_condition\_pushdown=on|off’

用explain查看执行计划时，如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown。

在mysql5.6以前，还没有采用ICP这种查询优化，where查询条件中的索引条件在某些情况下没有充分利用索引过滤数据。假设一个组合索引（多列索引）K包含（c1,c2,…,cn）n个列，如果在c1上存在范围扫描的where条件，那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据（不管不管是唯一查找还是范围查找），索引记录没有被充分利用。即组合索引前面字段上存在范围查询，那么后面的部分的索引将不能被使用，因为后面部分的索引数据是无序。比如，索引key（a，b）中的元组数据为(0,100)、(1,50)、（1，100） ，where查询条件为 a < 2 and b = 100。由于b上得索引数据并不是连续区间，因为在读取（1，50）之后不再会读取（1，100），mysql优化器在执行索引区间扫描之后也不再扫描组合索引其后面的部分。

表结构定义如下：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>`person` (</code> <code>`person_id`&nbsp;</code><code>smallint</code><code>(5) unsigned&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>AUTO_INCREMENT,</code> <code>`postadlcode`&nbsp;</code><code>int</code><code>(11)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>`age` tinyint(4)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>`first_name`&nbsp;</code><code>varchar</code><code>(45)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code><code>,</code> <code>`last_name`&nbsp;</code><code>varchar</code><code>(45)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code><code>,</code> <code>`last_update`&nbsp;</code><code>timestamp</code>&nbsp;<code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>DEFAULT</code>&nbsp;<code>CURRENT_TIMESTAMP</code>&nbsp;<code>ON</code>&nbsp;<code>UPDATE</code>&nbsp;<code>CURRENT_TIMESTAMP</code><code>,</code> <code>PRIMARY</code>&nbsp;<code>KEY</code>&nbsp;<code>(`person_id`),</code> <code>KEY</code>&nbsp;<code>`idx_p_a` (`postadlcode`,`age`),</code> <code>KEY</code>&nbsp;<code>`idx_f_l` (`first_name`,`last_name`)</code> <code>) ENGINE=InnoDB&nbsp;&nbsp;</code><code>DEFAULT</code>&nbsp;<code>CHARSET=utf8</code> </td> 
 </tr> 
 </tbody> 
</table>

关闭ICP优化，Extra信息为“Using Where”

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>optimizer_switch =&nbsp;</code><code>"index_condition_pushdown=off"</code><code>;</code> <code>mysql&gt; explain&nbsp;</code><code>select</code>&nbsp;&nbsp;<code>*&nbsp;&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>person&nbsp;&nbsp;</code><code>where</code>&nbsp;<code>postadlcode&nbsp;</code><code>between</code>&nbsp;<code>300000&nbsp;</code><code>and</code>&nbsp;<code>400000&nbsp;</code><code>and</code>&nbsp;<code>age &gt; 40;</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+</code> <code>| id | select_type |&nbsp;</code><code>table</code>&nbsp;&nbsp;<code>| type&nbsp; | possible_keys |&nbsp;</code><code>key</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>| key_len | ref&nbsp; |&nbsp;</code><code>rows</code>&nbsp;<code>| Extra&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+</code> <code>|&nbsp; 1 | SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | person | range | idx_p_a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | idx_p_a | 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;&nbsp; 21 | Using&nbsp;</code><code>where</code>&nbsp;<code>|</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

开启ICP之后，Extra信息为“Using Index Condition”

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>optimizer_switch =&nbsp;</code><code>"index_condition_pushdown=on"</code><code>;</code> <code>mysql&gt; explain&nbsp;</code><code>select</code>&nbsp;&nbsp;<code>*&nbsp;&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>person&nbsp;&nbsp;</code><code>where</code>&nbsp;<code>postadlcode&nbsp;</code><code>between</code>&nbsp;<code>300000&nbsp;</code><code>and</code>&nbsp;<code>400000&nbsp;</code><code>and</code>&nbsp;<code>age &gt; 40;</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+</code> <code>| id | select_type |&nbsp;</code><code>table</code>&nbsp;&nbsp;<code>| type&nbsp; | possible_keys |&nbsp;</code><code>key</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>| key_len | ref&nbsp; |&nbsp;</code><code>rows</code>&nbsp;<code>| Extra&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+</code> <code>|&nbsp; 1 | SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | person | range | idx_p_a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | idx_p_a | 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;&nbsp; 21 | Using&nbsp;</code><code>index</code><code>condition |</code> <code>+</code><code>----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

参考资料：

### [The MySQL range access method explained][] （需要梯子） ###

### [MySQL 5.6: Index Condition Pushdown][MySQL 5.6_ Index Condition Pushdown] （需要梯子） ###

[ Index Condition Pushdown Optimization][Index Condition Pushdown Optimization]

转载： https://blog.csdn.net/zxm1306192988/article/details/80255747

优化关系 SQL 查询的一项基本技术是，将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块（例如视图），从而能够提早进行数据过滤以及有可能更好地利用索引。

这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可能减少必须在数据库分区之间传递的数据量。

此优化技术在 SQL 中被称为谓词下推(Predicate pushdown) 。   
基本策略是，始终将过滤表达式尽可能移至靠近数据源的位置。

事实主流的数据库基本上都支持这种方式的优化。其原则是在一个查询树中，对于任何选择都尽量早做选择，然后进行一步的处理.可见我们并不需要改写这个sql,优化器会自动去帮我们做这种优化.

如：   
有表：   
t\_student(student\_id, student\_name, student\_age)   
t\_score(score\_id, student\_id, course\_id, score\_number)

优化的sql:

EXPLAIN SELECT \* FROM t\_student t1 right JOIN (  
SELECT \* from t\_score WHERE score\_id=2  
) t2  
ON t1.student\_id=t2.student\_id;  
1  
2  
3  
4  
先看 id 为2的行，先查询 t\_score,由于有 score\_id=2 根据主键索引直接查到对应行；   
然后看 第一行，查询id为2 行生成的临时表，表中只有一条记录，所以查询类型为 system；   
最后运行 第二行，直接根据 上面查到行的 student\_id 根据 t1 表的主键索引直接找到。

优化前的sql:

EXPLAIN SELECT \*  
FROM t\_student,t\_score  
WHERE t\_score.student\_id=t\_student.student\_id  
AND t\_score.score\_id=2;  
1  
2  
3  
4

![20180509163919587][]  
可以看到先查询 t\_score 表，ref 为 const 类型，说明 使用了过滤条件 t\_score.score\_id=2 ；   
即 在 扫描 t\_score 表时就使用了过滤条件，而不是等到和 t\_student 表连接之后才过滤。   
这就说明 mysql 优化器使用了谓词下推技术 对我们的sql 语句进行了优化。

![70][]

[https_blog.csdn.net_z69183787_article_details_53954914]: https://blog.csdn.net/z69183787/article/details/53954914
[MariaDB Blog]: https://mariadb.com/kb/en/index-condition-pushdown/
[index-access-2phases]: /images/20220416/8130fc11e55645b69b80058ed6a432fe.png
[index-access-with-icp]: /images/20220416/afad0f49b31f45969593ea8157074c12.png
[The MySQL range access method explained]: http://mdba.cn/2014/01/21/index-condition-pushdownicp%E7%B4%A2%E5%BC%95%E6%9D%A1%E4%BB%B6%E4%B8%8B%E6%8E%A8/The%20MySQL%20range%20access%20method%20explained
[MySQL 5.6_ Index Condition Pushdown]: http://olavsandstaa.blogspot.co.uk/2011/04/mysql-56-index-condition-pushdown.html
[Index Condition Pushdown Optimization]: https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
[20180509163919587]: /images/20220416/61180156fb974dccbffc59eddb45fe10.png
[70]: /images/20220416/639a1c914bf248389b0f0eb27e719ada.png