## 前言 [上一篇文章](http://mysql.taobao.org/monthly/2015/11/07/)?提過，我們在之后的文章中會從 optimizer 的選項出發，系統的介紹 optimizer 的各個變量，包括變量的原理、作用以及源碼實現等，然后再進一步的介紹優化器的工作過程（SQL 語句扁平化處理、索引選擇、代價計算、多表連接順序選擇以及物理執行等內容），本期我們先看一下眾所周知的 ICP，官方文檔請參考[這里](https://dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html)。 ## ICP 測試 首先，咱們來看一下打開 ICP 與關閉 ICP 之間的性能區別，以下是測試過程： 準備數據： ~~~ create table icp(id int, age int, name varchar(30), memo varchar(600)) engine=innodb; alter table icp add index aind(age, name, memo); --let $i= 100000 while ($i) { --eval insert into icp values($i, 1, 'a$i', repeat('a$i', 100)) --dec $i } ~~~ PS: MySQL 有一個叫profile的東東，可以用來監視 SQL 語句在各個階段的執行情況，咱們可以使用這個工具來觀察 SQL 語句在各個階段的運行情況，關于 profile 的詳細說明可以參考[官方文檔](http://dev.mysql.com/doc/refman/5.7/en/show-profile.html)。 打開 ICP 的性能測試： ~~~ set profiling=on; set optimizer_switch="index_condition_pushdown=on”; （default enabled） select * from icp where age = 1 and memo like '%9999%'; mysql> show profile cpu,block io for query 7; +----------------------+-----------+-----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+-----------+-----------+------------+--------------+---------------+ | executing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 3.225383 | 3.507467 | 0.037994 | 0 | 0 | +----------------------+-----------+-----------+------------+--------------+---------------+ mysql> show session status like '%handler%';show session status like '%handler%'; +----------------------------+--------+ | Handler_read_next | 19 | | Handler_read_rnd_next | 30 | +----------------------------+--------+ 18 rows in set (0.00 sec) ~~~ 關閉 ICP 的性能測試： ~~~ mysql> set optimizer_switch="index_condition_pushdown=off”; mysql> select * from icp where age = 1 and memo like '%9999%'; mysql> show profile cpu, block io for query 20; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | Sending data | 15.327345 | 17.443348 | 0.165975 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 15 rows in set, 1 warning (0.00 sec) mysql> show session status like '%handler%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_read_next | 100019 | | Handler_read_rnd_next | 47 | +----------------------------+--------+ 18 rows in set (0.01 sec) ~~~ 測試結論：由以上測試情況可以看到，在二級索引是復合索引且前面的條件過濾性較低的情況下，打開 ICP 可以有效的降低 server 層和 engine 層之間交互的次數，從而有效的降低在運行時間。 ## ICP 原理 5.6 之前，在 SQL 語句的執行過程中，server 層通過 engine 的 api 獲取數據，然后再進行 where_cond 的判斷（具體判斷邏輯在:?`evaluate_join_record`），每一條數據都需要從engine層返回server層做判斷。我們回顧一下上面把 ICP 關掉的測試，可以看到?`Handler_read_next`?的值陡增，其原因是第 1 個字段區分度不高，且 memo 字段無法使用索引，造成了類似 index 掃描的的情況，性能較低。 5.6 之后，在利用索引掃描的過程中，如果發現 where_cond 中含有這個 index 相關的條件，則將此條件記錄在 handler 接口中，在索引掃描的過程中，只有滿足索引與handler接口的條件時，才會返回到 server 層做進一步的處理，在前綴索引區分度不夠，其它字段區分度高的情況下可以有效的減少 server & engine之間的開銷，提升查詢性能。 ## ICP 源碼實現 我們在上小節提到，index condition down 所用的條件是記在handler接口中的，咱們分析一下“記錄”的過程是如何實現的。 首先，優化器計算代價后會生成一個 JOIN_TAB 的左支樹，每一個 JOIN_TAB 包含相關表的指針、表的讀取方式、訪問表所包含的索引等信息，優化器會在`make_join_readinfo`?中對JOIN_TAB中表的訪問方式進行相應的修正，并進一步將 where cond 中和索引相關的條件記錄到 table 的句柄中，堆棧如下： ~~~ #0 make_cond_for_index (cond=0x2b69680179e8, table=0x2b6968012100, keyno=0, other_tbls_ok=true) #1 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30) #2 in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295) #3 in JOIN::optimize (this=0x2b6968017db0) #4 in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true) ~~~ 其次，?`make_cond_for_index`?是一個遞歸的過程，對 where_cond中的每一個條件進行判斷，對滿足條件的 cond 重新組合成一個新的cond，最后將新的 cond 掛在table->file 下面（table->file 指的是操作物理表的接口函數，此變量為thd下私有的，不共享，共享的是tab->table->s），詳細參考`make_cond_for_index`?的詳細實現，設置的堆棧如下： ~~~ #0 ha_innobase::idx_cond_push (this=0x2b696800e810, keyno=0, idx_cond=0x2b69680179e8) #1 0x0000000000a60a55 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30) #2 0x0000000000a6362f in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295) #3 0x0000000000d9b8bd in JOIN::optimize (this=0x2b6968017db0 #4 0x0000000000a5b9ae in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true) ~~~ 再次，server 層根據生成的 JOIN_TAB 讀取engine層的內容，在engine讀取的時候，會進行`index_condition_pushdown`的調用，即 ICP 的調用，堆棧如下： ~~~ #0 Item_func_like::val_int (this=0x2b6978005a28) #1 0x0000000001187b66 in innobase_index_cond (file=0x2b696800e810) #2 0x0000000001393566 in row_search_idx_cond_check (mysql_rec=0x2b69680129f0 <incomplete sequence \361>, prebuilt=0x2b69680130f8, rec=0x2b692b56e4cf "\200", offsets=0x2b697008d450) #3 0x0000000001397e2b in row_search_for_mysql (buf=0x2b69680129f0 <incomplete sequence \361>, mode=2, prebuilt=0x2b69680130f8, match_mode=1, direction=0) #4 0x00000000011696b9 in ha_innobase::index_read (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence \361>, key_ptr=0x2b697800a660 "", key_len=5, find_flag=HA_READ_KEY_EXACT) #5 0x00000000006ecc58 in handler::index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) #6 0x00000000006d6bb4 in handler::ha_index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) #7 0x00000000009a1870 in join_read_always_key (tab=0x2b697800a1b8) #8 0x000000000099d480 in sub_select (join=0x2b6978005df0, join_tab=0x2b697800a1b8, end_of_records=false) #9 0x000000000099c6c0 in do_select (join=0x2b6978005df0) #10 0x00000000009980a4 in JOIN::exec (this=0x2b6978005df0) #11 0x0000000000a5bac0 in mysql_execute_select (thd=0x32801a0, select_lex=0x3282eb0, free_join=true) ~~~ 可見在 ICP 的判斷是調用相關item的函數的，雖然同是調用 server 層的函數，但是沒有 ICP 的調用需要根據主建找到記錄，然后再匹配，而有了 ICP 可以省略一次主鍵查找數據的過程，進而提升效率。 ## ICP 使用限制及問題 * 只支持 select 語句； * 5.6 中只支持 MyISAM 與 InnoDB 引擎; * ICP的優化策略可用于range、ref、eq_ref、ref_or_null 類型的訪問數據方法； * 不支持主建索引的 ICP； * 當 SQL 使用覆蓋索引時但只檢索部分數據時，ICP 無法使用，詳細的分析可以參考?[bug#68554](http://bugs.mysql.com/bug.php?id=68554)?中 Olav Sandst?的分析，代碼實現部分可以參考?`make_join_readinfo`； * 在查詢的時候即使正確的使用索引的前Ｎ個字段（即遵循前綴索引的原則），還是會用到 ICP，無故的多了 ICP 相關的判斷，這應該是一個退化的問題，例： ~~~ mysql> explain select * from icp where age=1 and name = 'a1'; +----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | icp | ref | aind | aind | 38 | const,const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+ 1 row in set (3.26 sec) ~~~ PS: engine condition pushdown 是 NDB 使用的，其它引擎不支持。