定义
索引下推(index condition pushdown)
简称ICP,MySQL 5.6引入了索引下推优化,可以在对联合索引(二级索引 || 非主键索引)遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数(大家可能感觉这是正常的,但是mysql5.6之前都不是这样实现的)
案列说明
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
假设现在有个员工表, 里面有个联合索引:idx_name_age_position
(name
,age
,position
) `
情况1:SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'
- 在mysql5.5版本中查询流程如下
1.首先mysql server 层
调用存储引擎(比如innodb)
获取 name like 'LiLei'%
的第一条记录。
2.存储引擎(比如innodb)
找到满足的以LiLei为开头
的的第一条记录后,通过索引里B+Tree 的叶子结点里主键行回表操作,去主键索引中找到该条记录的完整数据,并返回给server 层
。
3.server
层拿到数据之后,判断该条记录的 age是否为 22,如果 age=22,就把该条记录返回给客户端,如果 age != 22,那就就丢弃该记录。
4.此时继续获取name like 'LiLei'%
的下一条记录,记录2,3, 4操作.
- 在mysql5.6版本中查询流程如下
1.首先mysql server 层
调用存储引擎(比如innodb)
获取 name like 'LiLei'%
的第一条记录,存储引擎
找到记录后不会急着回表,而是继续判断age是否为 22,如果 age=22,则进行回表操作(select * ),如果 age != 22,那就就丢弃该记录,不用回表了,继续执行取出下一条记录进行上面操作
(图片来自: https://www.tulingxueyuan.cn/)
优点
索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找Mysql没有用索引下推优化?
估计应该是Mysql认为范围查找过滤的结果集过大,like KK% 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like KK% 用了索引下推优化,当然这也不是绝对的,有时like KK% 也不一定就会走索引下推。
参考:
https://www.tulingxueyuan.cn/