MySQL 索引條件下推 Index Condition Pushdown

標籤：filter   出現   int   title   png   內容   const   mil   資料庫   

MySQL 索引條件下推 Index Condition Pushdown 出現在MySQL5.6及之後的版本中，能大幅提升查詢效率，原因如下：

內容摘錄自《深入理解MariaDB和MySQL》

下面使實驗，使用官方提供的employees 測試資料庫示範。

> use employees ;

> 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`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

> alter table employees add index idx_lastname_firstname(last_name,first_name);

關閉ICP：

> set optimizer_switch='index_condition_pushdown=off';

> explain extended select * from employees where last_name='Action' and first_name LIKE '%sal' ;

***************************[ 1. row ]***************************

id            | 1

select_type   | SIMPLE

table         | employees

type          | ref

possible_keys | idx_lastname_firstname

key           | idx_lastname_firstname

key_len       | 18

ref           | const

rows          | 1

filtered      | 100.0

Extra         | Using where

查詢條件中的first_name 這個前面%匹配導致無法用到整個idx_lastname_firstname 索引的，只能根據last_name 欄位過濾部分資料，然後在裡面找出符合first_name列 %sal的行記錄。

但是，如果開啟ICP，則執行計畫如下：

> set optimizer_switch='index_condition_pushdown=on';

> explain extended select * from employees where last_name='Action' and first_name LIKE '%sal' \G

***************************[ 1. row ]***************************

id            | 1

select_type   | SIMPLE

table         | employees

type          | ref

possible_keys | idx_lastname_firstname

key           | idx_lastname_firstname

key_len       | 18

ref           | const

rows          | 1

filtered      | 100.0

Extra         | Using index condition

原理：

索引比較是在InnoDB儲存引擎層進行的。而資料表的記錄比較first_name條件是在MySQL引擎層進行的。開啟ICP之後，包含在索引中的資料列條件(即上述SQL中的first_name LIKE %sal') 都會一起被傳遞給InnoDB儲存引擎，這樣最大限度的過濾掉無關的行。

執行計畫如：

MySQL 索引條件下推 Index Condition Pushdown