Index Condition Pushdown測試

1、基本原理描述
ICP(Index Condition Pushdown Optimization):官方從5.6開始引入（percona5.6，mariadb5.5），
它是針對mysql通過索引取資料場景的優化，ICP之前，儲存引擎通過索引定位到資料行，把他們返回
給server層，然後server層通過where條件（不能使用索引列的）做過濾；啟用ICP後，如果where條件
可以通過索引列過濾，server層就“push this part of where condition down to storage engine”
引擎通過索引條目判斷是否需要讀某些行。ICP降低了儲存引起訪問底層表的次數和server層訪問引擎
層的次數。
root:3406:(none)>set optimizer_switch= 'index_condition_pushdown=off';
root:3406:popo>explain select * from test where v1='a' and v2 like '%a%' ;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | v1 | v1 | 33 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

root:3406:(none)>set optimizer_switch= 'index_condition_pushdown=on';
root:3406:popo>explain select * from test where v1='a' and v2 like '%a%';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test | ref | v1 | v1 | 33 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

2、充分必要條件
1] 訪問表方式：range,ref,eq ref, ref of null
2] 引擎必須是innodb或者myisam（5.6不支援分割槽表，5.7支援）
3] innodb引擎表 ICP只能用在二級索引上（ICP是通過減少全記錄讀的次數來減少IO操作的，聚集索引
   已經是完整的行讀入innodbbuffer了，ICP失去了意義【個人理解：server層-（1）->引擎層-(2)->基礎表；
   ICP通過減少步驟2的IO，從而提升了效率，而在聚集索引的時候步驟2的IO是不能被減少的，所以ICP在這個
   場景失去意義】）
4] 覆蓋索引情況，ICP失效

當ICP發生的時候，explain的 extra列顯示 Using index condition

3、場景測試

CREATE TABLE `test2` (
`id` int(11) NOT NULL,
`v1` varchar(50) DEFAULT NULL,
`v2` varchar(50) DEFAULT NULL,
`v3` varchar(50) DEFAULT NULL,
`v4` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `v1` (`v1`,`v2`),
KEY `v3` (`v3`,`v4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

資料讀取觀察：
root:3406:popo>set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.03 sec)

root:3406:popo>
root:3406:popo>show status like 'hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 11111 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 9     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 7     |
+----------------------------+-------+
18 rows in set (0.00 sec)

root:3406:popo>set optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.01 sec)

root:3406:popo>show status like 'hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 9     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 7     |
+----------------------------+-------+
18 rows in set (0.00 sec)

Handler_read_key ：The number of requests to read a row based on a key.
If this value is high, it is a good indication that your tables are properly
indexed for your queries.
ICP情況下該值明顯小很多

profile 觀察：
insert into test2 values(1,'a1,'b1','c1','d1');
insert into test2 select (@cnt:=@cnt+1) id,concat('a',@cnt),concat('b',@cnt),concat('c',@cnt),concat('d',@cnt) from test2,(select @cnt:=(select count(*)
from test2)) t;
select count(*) from test2; --262144

root:3406:popo>set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.03 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.02 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.03 sec)

root:3406:popo>set optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

root:3406:popo>
root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.01 sec)

root:3406:popo>select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%';
Empty set (0.01 sec)

root:3406:popo>
root:3406:popo>select count(*) from test2 where v1 like 'a11%';
+----------+
| count(*) |
+----------+
| 11111 |
+----------+
1 row in set (0.01 sec)

root:3406:popo>show profiles;
+----------+------------+--------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                    |
+----------+------------+--------------------------------------------------------------------------+
|        1 | 0.00007025 | show warnings                                                            |
|        2 | 0.00010925 | set optimizer_switch='index_condition_pushdown=off'                      |
|        3 | 0.02803975 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        4 | 0.02678925 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        5 | 0.02644700 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        6 | 0.02641950 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        7 | 0.00008150 | set optimizer_switch='index_condition_pushdown=on'                       |
|        8 | 0.01002500 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        9 | 0.01015550 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       10 | 0.01012625 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       11 | 0.01041800 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       12 | 0.00978425 | select count(*) from test2 where v1 like 'a11%'                          |
+----------+------------+--------------------------------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)

可以看到使用ICP特性在這個場景下速度比原來快了2.6倍。

具體對比差異發現在主要在sending data階段,ICP節省了時間.
進一步測試：
root:3406:popo>select count(*) from test2 where v1 like 'a11111%';
+----------+
| count(*) |
+----------+
|       11 |
+----------+
root:3406:popo>show profiles;
+----------+------------+--------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                    |
+----------+------------+--------------------------------------------------------------------------+
|        6 | 0.02641950 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        7 | 0.00008150 | set optimizer_switch='index_condition_pushdown=on'                       |
|        8 | 0.01002500 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|        9 | 0.01015550 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       10 | 0.01012625 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       11 | 0.01041800 | select sql_no_cache * from test2 where v1 like 'a11%' and v2 like '%ff%' |
|       12 | 0.00978425 | select count(*) from test2 where v1 like 'a11%'                          |
|       13 | 0.00009125 | set optimizer_switch='index_condition_pushdown=off'                      |
|       14 | 0.00032050 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
|       15 | 0.00024500 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
|       16 | 0.00024575 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
|       17 | 0.00006775 | set optimizer_switch='index_condition_pushdown=on'                       |
|       18 | 0.00022625 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
|       19 | 0.00021775 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
|       20 | 0.00021525 | select * from test2 where v1 like 'a11111%' and v2 like '%ff%'           |
+----------+------------+--------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

可以看到當第一列索引效率高的時候，兩者差異並不明顯。

4、結論和使用場景：

綜合3的測試：得出結論：
ICP能夠在節省sending data環節的時間，從而有更好的效率；同時也標誌著只有在索引過濾效率
低的時候才會有更明顯的效能提升，當前欄位過濾性好（如v1 like 'a11111%'），ICP的優化效率是
不顯著的.

5、bug問題：資料構造測試
http://imysql.cn/2013/04/28/problem-with-mysql5.6-index-condition-pushdown.html
測試未能重現

參考：
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/mariadb/index-condition-pushdown/
http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Handler_read_key

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20625855/viewspace-1455474/，如需轉載，請註明出處，否則將追究法律責任。

Index Condition Pushdown測試

相關文章