---
ICP特性是我們DBA在實際的SQL最佳化過程中經常能夠遇到的，但是某些出現ICP的地方看起來並不那麼直觀，為我們產生了一定的困擾。本文透過MySQL 5.7.22 和Innodb引擎來解釋一下各種情況下ICP到底下推了什麼條件到引擎層。

###一、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: bgicp
Create 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 中。下圖就是這種關係：

![where item.jpg](https://upload-images.jianshu.io/upload_images/7398834-d8a98845c66a05fc.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

######以下部分為證明，可以不用關注：

這裡實際上我們可以透過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'）這個條件進行了下推：

![icp2.jpg](https://upload-images.jianshu.io/upload_images/7398834-04f440ac7b566434.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

######以下是下推的證明，可以不用關注：

條件下推的時候會呼叫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使用的過程了，我們可以發現對於這種流程來講，我們最開始總結的優點都體現出來了，它確實有機會提高查詢效率。

###五、其他的下推時機

實際上下推操作並非總是如例子中所描述的那樣，其實總結一下

######以下是原始碼呼叫部分，可以不用關注：

這個過程會透過row_search_mvcc->row_search_idx_cond_check->innobase_index_cond完成，如果我們沒有開啟ICP或者沒有ICP下推的條件那麼會直接不做判斷，即row_search_idx_cond_check函式會直接返回。

對於函式innobase_index_cond而言也比較簡單，但是他會先做一個對type=range方式的結束位置的判斷如下：
```

```

###其他
棧幀1：

```
#0  lock_rec_unlock (trx=0x7fffd7803b10, block=0x7fff44598370, rec=0x7fff44fd40fc "\200", lock_mode=LOCK_X)
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:4365
#1  0x0000000001bbb70a in row_unlock_for_mysql (prebuilt=0x7ffe70af2ec0, has_latches_on_recs=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:3278
#2  0x0000000001a52954 in ha_innobase::unlock_row (this=0x7ffe70af20f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9237
#3  0x00000000014e3379 in rr_unlock_row (tab=0x7ffe70b02460) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:821
#4  0x0000000001582226 in evaluate_join_record (join=0x7ffe70afe778, qep_tab=0x7ffe70b02460) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1705
#5  0x0000000001581372 in sub_select (join=0x7ffe70afe778, qep_tab=0x7ffe70b02460, end_of_records=false)

```

MySQL：關於ICP特性的說明（未完）

相關文章