首页 > 数据库技术 > 详细

MySQL 索引条件下推 Index Condition Pushdown

时间:2018-01-13 00:06:17      阅读:112      评论:0      收藏:0      [点我收藏+]

标签:lec   ace   const   row   date   实验   条件   mys   匹配   

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

标签:lec   ace   const   row   date   实验   条件   mys   匹配   

原文:http://blog.51cto.com/lee90/2060449

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号