mysql 5.6引入index condition pushdown

5.6引入index condition pushdown，允許在儲存引擎層透過索引列提前過濾掉資料，從而減少伺服器層訪問的資料量，同時也減少儲存引擎訪問基表的次數；
使用ICP可有效降低handler_read_next，explain輸出的extra列顯示using index condition；
可用於innodb和MyISAM，適用於range, ref, eq_ref和ref_or_null，只可用於二級索引；
5.7起可用於分割槽表；


工作原理
Non-ICP

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.

ICP


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 Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

 

開啟條件
5.6預設開啟，可通設定optimizer_switch的index_condition_pushdown=off關閉；

案例
CREATE TABLE `cast_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `person_role_id` int(11) DEFAULT NULL,
  `note` varchar(250),
  `nr_order` int(11) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id_note` (`role_id`,`note`)
) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8;

mysql> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

5.5.30
mysql-5.5> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 11553718
        Extra: Using where
1 row in set (0.01 sec)

mysql> show status like 'Hand%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 0       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 8346769 |
..............................

5.6.10
mysql-5.6> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using index condition
1 row in set (0.00 sec)

mysql-5.6> 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          | 266   |
..............................

缺陷
5.6無法同覆蓋索引一起使用
使用覆蓋索引，但是handler_read_next飆升，即沒有使用到ICP

mysql-5.6> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql-5.6> SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%';
+----------------------------+---------+
| 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          | 8346769 |
..............................

參考資料
https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html