MySQL Index Condition Pushdown(ICP)機能優化辦法實例。本站提示廣大學習愛好者：（MySQL Index Condition Pushdown(ICP)機能優化辦法實例）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL Index Condition Pushdown(ICP)機能優化辦法實例正文
一 概念引見
Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特征,是一種在存儲引擎層應用索引過濾數據的一種優化方法。
a 當封閉ICP時,index 僅僅是data access 的一種拜訪方法，存儲引擎經由過程索引回表獲得的數據會傳遞到MySQL Server 層停止where前提過濾。
b 當翻開ICP時,假如部門where前提能應用索引中的字段,MySQL Server 會把這部門下推到引擎層,可以應用index過濾的where前提在存儲引擎層停止數據過濾,而非將一切經由過程index access的成果傳遞到MySQL server層停止where過濾.
優化後果:ICP能削減引擎層拜訪基表的次數和MySQL Server 拜訪存儲引擎的次數,削減io次數，進步查詢語句機能。
二 道理
Index Condition Pushdown is not used:
1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
Index Condition Pushdown is used
1 Get the next row s index tuple (but not the full table row).
2 Test the part of the WHERE condition that applies to this table and can be checked using only index columns.
If the condition is not satisfied, proceed to the index tuple for the next row.
3 If the condition is satisfied, use the index tuple to locate and read the full table row.
4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
三 理論案例
a 情況預備
數據庫版本 5.6.16
封閉緩存
set query_cache_size=0;
set query_cache_type=OFF;
測試數據下載地址
b 當開啟ICP時
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 1 | 0.00060275 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
此時情形下依據MySQL的最左前綴准繩, first_name 可使用索引，last_name采取了like 隱約查詢，不克不及應用索引。
c 封閉ICP
mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> SET profiling = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 2 | 0.00097000 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
當開啟ICP時 查詢在sending data環節時光消費是 0.000189s
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000094 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000093 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000189 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
當封閉ICP時 查詢在sending data環節時光消費是 0.000735s
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000735 | 0.001000 | 0.000000 | 0 | 0 |
| end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
從下面的profile 可以看出ICP 開啟時全部sql 履行時光是未開啟的2/3，sending data 環節的時光消費前者僅是後者的1/4。
ICP 開啟時的履行籌劃 含有 Using index condition 標示 ，表現優化器應用了ICP對數據拜訪停止優化。
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
ICP 封閉時的履行籌劃顯示use where.
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using where |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
案例剖析
以下面的查詢為例封閉ICP 時,存儲引擎通前綴index first_name 拜訪表中225條first_name 為Anneke的數據,並在MySQL server層依據last_name like '%sig' 停止過濾
開啟ICP 時，last_name 的like '%sig'前提可以經由過程索引字段last_name 停止過濾，在存儲引擎外部經由過程與where前提的比較，直接過濾失落不相符前提的數據。該進程不回表,只拜訪相符前提的1筆記錄並前往給MySQL Server ,有用的削減了io拜訪和各層之間的交互。
ICP 封閉時 ，僅僅應用索引作為拜訪數據的方法。
ICP 開啟時 ，MySQL將在存儲引擎層 應用索引過濾數據，削減不用要的回表，留意 虛線的using where 表現假如where前提中含有無被索引的字段，則照樣要經由MySQL Server 層過濾。
四 ICP的應用限制
1 當sql須要全表拜訪時,ICP的優化戰略可用於range, ref, eq_ref, ref_or_null 類型的拜訪數據辦法 。
2 支撐InnoDB和MyISAM表。
3 ICP只能用於二級索引，不克不及用於主索引。
4 並不是全體where前提都可以用ICP挑選。
假如where前提的字段不在索引列中,照樣要讀取整表的記載到server端做where過濾。
5 ICP的加快後果取決於在存儲引擎內經由過程ICP挑選失落的數據的比例。
6 5.6 版本的不支撐分表的ICP 功效，5.7 版本的開端支撐。
7 當sql 應用籠罩索引時，不支撐ICP 優化辦法。
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_emp_fnln | idx_emp_fnln | 94 | const,const | 1 | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
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_emp_fnln | idx_emp_fnln | 94 | const,const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
