關於Index Condition Pushdown特性

ICP簡介

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHEREcondition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

 

也就說:利用索引(二級索引)來過濾一部分where條件

 

測試

 

導入資料庫

 

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar jxf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -uroot -p < employees.sql

 

 

表結構

 

mysql> show create table employees \G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `index_bh` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

一些表數據

mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G
*************************** 1. row ***************************
@@optimizer_switch like '%index_condition_pushdown%': 1
1 row in set (0.00 sec)

mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G
*************************** 1. row ***************************
@@optimizer_switch like '%index_condition_pushdown%': 1
1 row in set (0.00 sec)

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                |
+--------------------+
1 row in set (0.01 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.17 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

 

建立索引

 

alter table employees add index index_bh (`birth_date`,`hire_date`);

 

查詢分析

 

mysql> explain select *   from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%';
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | range | index_bh      | index_bh | 3       | NULL | 46318 | Using where |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select *   from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%';
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows  | Extra                              |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
|  1 | SIMPLE      | employees | range | index_bh      | index_bh | 3       | NULL | 46318 | Using index condition; Using where |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
1 row in set (0.01 sec)

 

執行查詢

 

mysql> show profiles;                                                                                                                 +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00278025 | desc employees                                                                                                                                       |
|        2 | 0.00049775 | show create table employees                                                                                                                          |
|        3 | 0.07444550 | select *   from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' |
|        4 | 0.00027500 | SET optimizer_switch='index_condition_pushdown=off'                                                                                                  |
|        5 | 0.12347025 | select *   from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+

 

 

從結果可以看出來開啟ICP之後確實快不少

 

啟用ICP之後,可以用索引來篩選 datediff(hire_date,birth_date)>12300 記錄,不需要讀出整條記錄

 

ICP原理

 

如下圖所示(圖來自MariaDB)

 

1、優化器沒有使用ICP時

 

在存儲引擎層,首先讀取索引元組(index tuple),然後使用(index tuple)在基表中(base table)定位和讀取整行數據

 

到伺服器層,匹配where條件,如果該行數據滿足where條件則使用,否則丟棄
 

指針向下一行移動,重復以上過程

2、使用ICP的時候

 

如果where條件的一部分能夠通過使用索引中的字段進行過濾,那麼伺服器層將把這部分where條件Pushdown到存儲引擎層

 

到存儲引擎層,從索引中讀取索引元組(index tuple),使用索引元組進行判斷,如果沒有滿足where條件,則處理下一條索引元組(index tuple),隻有當索引元組滿足條件的時候,才會去基表中讀取數據

ICP的使用條件

 

1、隻能用於二級索引(secondary index)

 

2、explain顯示的執行計劃中type值(join 類型)為range、 ref、 eq_ref或者ref_or_null。且查詢需要訪問表的整行數據,即不能直接通過二級索引的元組數據獲得查詢結果(索引覆蓋)

 

3、ICP可以用於MyISAM和InnnoDB存儲引擎,不支持分區表(5.7將會解決這個問題)

 

4、ICP的加速效果取決於在存儲引擎內通過ICP篩選掉的數據的比例

You May Also Like