MySQL：关于ICP特性的简析

作者简介：高鹏，笔名八怪。《深入理解MySQL主从原理》图书作者，同时运营个人公众号“MySQL学习”，持续分享遇到的有趣case以及代码解析！

一、ICP简述

ICP：全称为Index Condition Pushdown，是MySQL 5.6引入的一项优化策略。简单的来说就是将本该在MySQL进行过滤的条件下推到Innodb引擎层去做。但是这种策略和我们平时说的使用到了索引实际上是不同的，我们平时说的用到了索引一般指的是使用到了索引进行定位和访问，但是这里却是一种过滤操作。严格意义上来讲和MySQL层的过滤区别并不大，但是由于这里过滤发生在Innodb层，并且还没有进行回表和加行锁操作（for update），因此优点有如下几点：

• 减少了回表操作
• 减少了回表后主键加锁（for update），但是对于查询索引而言加锁没有变化。
• 减少了返回给MySQL层数据的数据

如果在执行计划中出现了Using index condition则说明进行了下推操作，如果想禁用ICP特性则简单设置一下即可，如下：

set optimizer_switch='index_condition_pushdown=off';

下推过滤的操作是需要下推的条件和进行Innodb层定位的条件同时包含在同一个组合索引才能完成，举个列子比如索引包含（a ，b，c）三列，如果是（where a=** and c like ‘%*%’），那么下推才能完成。

当然还有很多其它的限制这里不列出来了，可以自己参考一下官方手册：

• 8.2.1.5 Index Condition Pushdown Optimization

二、WHERE条件解析

为了方便讨论，我们使用下面的列子：

mysql>  show create table bgicp \G
*************************** 1. row ***************************
       Table: bgicpCreate Table: 
CREATE TABLE `bgicp` (
  `id` int(11) NOT NULL,  
  `a1` int(11) DEFAULT NULL,  
  `a2` varchar(20) DEFAULT NULL,  
  `a3` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `a1` (`a1`,`a2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from bgicp;
+----+------+------+------+
| id | a1   | a2   | a3   |
+----+------+------+------+
|  1 |    1 | g1   | g1   |
|  2 |    2 | g2   | g2   |
|  3 |    2 | g3   | g3   |
|  4 |    4 | g4   | g4   |
|  5 |    5 | g5   | g5   |
|  6 |    6 | g6   | g6   |
|  7 |    6 | g7   | g7   |
|  8 |    6 | g7   | g8   |
|  9 |    9 | g9   | g9   |
| 10 |   10 | g10  | g10  |
+----+------+------+------+

mysql> desc select * from bgicp where a1=6 and a2 like '%7';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | bgicp | NULL       | ref  | a1            | a1   | 5       | const |    3 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

这里的where条件会存储在st_select_lex.m_where_cond 中。下图就是这种关系：

以下部分为证明，可以不用关注：

这里实际上我们可以通过gdb进行一下验证，断点可以放到st_select_lex::prepare上，如下：

and符号：

(gdb) p ((Item_cond *)m_where_cond)->functype()
$43 = Item_func::COND_AND_FUNC
(gdb) p ((Item_cond_and *)m_where_cond)->list->elements
$64 = 2 
（这是AND中的元素个数，这里是2，如上图）

=符号：

(gdb) p ((Item_cond_and *)m_where_cond)->list->first->info
$47 = (void *) 0x7ffe7c007690
(gdb) p ((Item_cond*)((void *) 0x7ffe7c007690))->functype()
$48 = Item_func::EQ_FUNC

a = 6 条件：

(gdb) p ((Item_func_eq*)((void *) 0x7ffe7c007690))->args[1]
$49 = (Item *) 0x7ffe7c006888
(gdb) p ((Item_int *)$49)->value
$50 = 6 （这是值6）
(gdb)  p ((Item_func_eq*)((void *) 0x7ffe7c007690))->args[0]
$59 = (Item *) 0x7ffe7c007570
(gdb) p ((Item_field *)$59)->field_name
$60 = 0x7ffe7c0067c0 "a1"
（这是字段a1）

like符号：

(gdb)  p ((Item_cond_and *)m_where_cond)->list->first->next->info
$51 = (void *) 0x7ffe7c006b60
(gdb)  p ((Item_cond*)((void *) 0x7ffe7c006b60))->functype()
$52 = Item_func::LIKE_FUNC

a2 like '%7' 条件：

(gdb) p ((Item_func_like *)((void *) 0x7ffe7c006b60 ))->args[1]
$54 = (Item *) 0x7ffe7c006aa8
(gdb)  p ((Item_string *)$54)->str_value
$55 = {m_ptr = 0x7ffe7c006aa0 "%7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0, m_is_alloced = false} 
（这是值 %7）
(gdb) p ((Item_func_like *)((void *) 0x7ffe7c006b60 ))->args[0]
$57 = (Item_field *) 0x7ffe7c007830
(gdb) p ((Item_field *)$56)->field_name
$58 = 0x7ffe7c0069e0 "a2" 
（这是字段a2）

通过这种方法我们也可以查看下推的具体条件是什么，下面我们来看看

三、ICP条件下推

实际上整个下推完成后如下图，也会是（a2 like '%7'）这个条件进行了下推：

以下是下推的证明，可以不用关注：

条件下推的时候会调用ha_innobase::idx_cond_push函数进行下推，我们来看看上面的语句具体下推了什么条件到Innodb层。我们还是通过上面debug方式来进行，断点可以设置在ha_innobase::idx_cond_push上。具体的查看方式如下：

(gdb) p ((Item_cond *)pushed_idx_cond)->functype()
$67 = Item_func::LIKE_FUNC
(这是like 操作)
(gdb) p ((Item_func_like *)((void *) $66 ))->args[1]
$68 = (Item *) 0x7ffe7c006aa8
(gdb) p ((Item_string *)$68)->str_value
$69 = {m_ptr = 0x7ffe7c006aa0 "%7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0, m_is_alloced = false}
（这是字符串'%7'）
(gdb) p ((Item_func_like *)((void *) $66 ))->args[0]
$70 = (Item *) 0x7ffe7c007830
(gdb) p ((Item_field *)$70)->field_name
$71 = 0x7ffe7c99618f "a2"
（这是字段a2）

四、ICP条件下推的使用

如上图，一旦（a2 like '%7'）条件下推过后，Innodb层就可以使用它了，使用流程大概如下：

• Innodb扫描一条数据（Innodb层）：本例中就是根据条件“a1=6” 获取一行数据。
• 对索引记录加行锁（Innodb层）：比如for update语句是需要加行锁的。
• 根据下推条件进行数据过滤（Innodb层）：本例中就是根据条件“a2 like '%7'” 进行数据的过滤。
• 进行回表操作，并且对回表后的主键记录加行锁（Innodb层）：比如for update 语句是需要回表后对主键加行锁的。
• 返回数据给MySQL层，并且进行过滤（MySQL层）：这里也就是进行其他条件的where过滤了，如果不符合条件还会进行提前解锁这行记录（RR，RC都可以），参考末尾栈帧1。

上面就是ICP使用的过程了，我们可以发现对于这种流程来讲，我们最开始总结的优点都体现出来了，它确实有机会提高查询效率。

五、范围条件下推

实际上除了上面的情况如果我们执行：

 select * from bgicp  where a1>2 and a1<6;

依然会出现ICP，这个时候下推后需要判断是我们的结束条件<6，这里可以通过上面的方法如下获取下推条件：

(gdb) p ((Item_cond_and *)pushed_idx_cond)->list->first->info
$31 = (void *) 0x7fff90007580
(gdb) p ((Item_cond*)((void *) 0x7fff90007580))->functype()
$32 = Item_func::GT_FUNC (大于)
(gdb) p ((Item_func_eq*)((void *) 0x7fff90007580))->args[1]
$33 = (Item *) 0x7fff900068a0
(gdb) p ((Item_int *)$33)->value
$34 = 2（数字2）
(gdb) p ((Item_cond_and *)pushed_idx_cond)->list->first->next->info
$35 = (void *) 0x7fff90007840
(gdb) p ((Item_cond*)((void *) 0x7fff90007840))->functype()
$36 = Item_func::LT_FUNC（小于）
(gdb) p ((Item_func_eq*)((void *) 0x7fff90007840))->args[1]
$37 = (Item *) 0x7fff90006ac8
(gdb) p ((Item_int *)$37)->value
$38 = 6（数字6）

这个过程会通过row_search_mvcc->row_search_idx_cond_check->innobase_index_cond完成，如果我们没有开启ICP或者没有ICP下推的条件那么会直接不做判断，即row_search_idx_cond_check函数会直接返回。而在innobase_index_cond中我们可以发现使用正是我们前面说的下推的条件（pushed_idx_cond）。对于函数innobase_index_cond而言也比较简单， 范围条件的比较和like条件的比较，就在这个函数中实现和完成比较的。