MySQL 索引条件下推 Index Condition Pushdown

保持热爱奔赴山海

发布于 2019-09-17 11:43:03

1.4K3

发布于 2019-09-17 11:43:03

文章被收录于专栏：饮水机管理员饮水机管理员

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存储引擎，这样最大限度的过滤掉无关的行。

执行计划如下图：