相关文章:
什么是最左原则、什么是索引下推?
什么是索引下推?(第二篇)
1. 概述
索引下推(index condition pushdown )简称ICP
,在Mysql5.6的版本上推出,用于优化查询。
-
在不使用ICP的情况下,在使用
非主键索引
(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。 -
在使用ICP的情况下,如果存在某些被索引的列的判断条件时,
MySQL服务器
将这一部分判断条件传递给存储引擎,然后由存储引擎
通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。简约描述下ICP,就是如果不使用ICP,需要把大量的原始数据从底层传递到上层进行过滤,而采用ICP,在底层就可以进行过滤,这样传递到上层的数据量就少了,好处就像减少了带宽占用是不是?必然能提高效率。
注意:仅限非聚簇索引上使用,默认开启
索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数
1.1 什么是最左原则
联合索引下才有最左匹配原则。
索引的目的其实就是为了提高数据查询的效率,联合索引也一样,使用联合索引时,一定要注意符合最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。
假如对字段 (a, b, c) 建立联合索引,如下查询语句可以使用到索引:
where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c
group by a
当然,像如下:
where b = xxx and a = xxx
where a = xxx and c = xxx and b = xxx
这种查询条件书写顺序不影响对联合索引的使用,因为执行 sql 的时候,MySQL优化器会帮我们调整 where 后 a,b,c 的顺序,让我们用上索引。
而还有一些语句是只能用到联合索引的一部分的。
where a = xxx and c = xxx 可以用到 a 列的索引,用不到 c 列索引。
where a like ‘xxx%’ and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。索引下推正是这个场景下的优化的产物。
where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
最需要注意类似下边的这些查询:
where b = xxx
where c = xxx
where a like '%xxx' -- 不满足最左前缀
where d = xxx order by a -- 出现非排序使用到的索引列 d
where a + 1 = xxx -- 使用函数、运算表达式及类型隐式转换等
这些查询语句,完全用不到 (a, b, c) 这个联合索引。
2. 举例
2.1 例1
在开始之前先先准备一张用户表(user),其中主要几个字段有:id、name、age、address
。建立联合索引(name,age)。
CREATE TABLE `user` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`AGE` int(11) DEFAULT NULL,
`NAME` varchar(20) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `name_age` (`NAME`,`AGE`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
假设有一个需求,要求匹配姓名第一个为陈的所有用户,sql语句如下:
EXPLAIN SELECT * FROM USER WHERE NAME LIKE '陈%';
根据 “最佳左前缀” 的原则,这里使用了联合索引(name,age)进行了查询,性能要比全表扫描肯定要高。
问题来了,如果有其他的条件呢?假设又有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:
EXPLAIN SELECT * from user where name like '陈%' and age=20;
这条sql语句应该如何执行呢?下面对Mysql5.6之前版本和之后版本进行分析。
Mysql5.6之前的版本
5.6之前的版本是没有索引下推这个优化的,因此执行的过程如下图:
二级索引会忽略age
这个字段,直接通过name
进行查询,在(name,age)这课树上查找到了两个结果,id分别为2,1,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次
:
Mysql5.6及之后版本
5.6版本添加了索引下推这个优化,执行的过程如下图:
InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次
。
验证
mysql5.7执行结果:
根据explain解析结果可以看出Extra的值为Using index condition
(ICP),表示已经使用了索引下推。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ------------- -------- ------- ------ ------ -------- -----------------------
1 SIMPLE user (NULL) range name_age name_age 88 (NULL) 1 100.00 Using index condition
总结
-
索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率。
-
关闭索引下推可以使用如下命令,配置文件的修改不再讲述了,毕竟这么优秀的功能干嘛关闭呢:
#关闭
SET optimizer_switch='index_condition_pushdown=off';
#开启
SET optimizer_switch='index_condition_pushdown=on';
关闭效果:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ------------- -------- ------- ------ ------ -------- -------------
1 SIMPLE user (NULL) range name_age name_age 88 (NULL) 1 100.00 Using where
参考:
《什么是索引下推》 参考主体
《MySQL联合索引与索引下推图文详解》
《什么是索引下推?》