前言
说到MySQL的优化手段,不得不提的是MYSQL5.6中引入的一种特性,索引下推,英文是 index condition pushdown,一般简称为 ICP。这也是MySQL面试中经常被问到的一个考点,本文就此分享下索引下推是怎么一回事,它是如何对提高查询效率起到帮助的。
理解索引下推
索引下推就是指在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数来提高查询效率。
如何理解呢? 我们直接上栗子来解释:
-
新建用户表
CREATE TABLE user( `id` int NOT NULL AUTO_INCREMENT, `zipcode` varchar(20) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), index idx_zip_name(`zipcode`, `name`) ) ENGINE=INNODB; insert into user(zipcode, name, address) values ('100001', '陈旭阳', '杭州'), ('100001', '胡歌', '上海'), ('200002', '杨幂', '北京'), ('300002', '刘诗诗', '南京'); 复制代码
-
创建了基于邮编zipcode和名称name的联合索引idx_zip_name,用于查找某个邮编下,名字包含陈的人。
-
查询语句
select * from user where zipcode = '100001' and name like '%陈%' and address like '%余杭%'; 复制代码
-
查询条件根据zipcode精确查找,name和address全模糊匹配。
如果MySQL5.6以前, 没有索引下推特性,整个流程如下图:
编辑切换为居中
-
联合索引中知道100001的数据项,因为name是全模糊,无法走索引,address压根没有索引,他们无法在联合索引中过滤,怎么办?只能回到数据最全的聚簇索引上进行name like '%陈%' and address like '%余杭%'这个逻辑的过滤。
-
按照上面的方式,每次回到聚簇索引这个一个称做 “回表” 的过程,势必增加磁盘IO,从而影响查询性能。
那么有什么办法可以优化呢?
优化的目标就是尽量减少回表这一过程,我们发现联合索引上竟然有了name字段的信息,为什么我们不能充分利用呢? name like '%陈%'我们可以直接在索引树上进行判断name是不是包含陈,从而减少回表次数。这也正是MySQL5.6中优化的特性,如下图所示:
编辑切换为居中
-
根据查询条件name like '%陈%',陈旭阳包含了陈, 就无需回表了。
上面途中的索引都用表格表示,只是为了方便,实际上索引底层数据结构是B+数据,如果不了解的,可以阅读文章:一步步带你设计MySQL索引数据结构
一句话总结:索引下推(index condition pushdown,ICP),有效的减少了回表次数,提高了查询效率。
索引下推性能比较
竟然索引下推可以提高效率,那我们验证下。
-
沿用上面的user表
-
创建存储过程,添加数据
DELIMITER // CREATE PROCEDURE insert_user(max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO user(zipcode,name,address) VALUES ('10018', '陈旭阳', '杭州'); UNTIL i =max_num END REPEAT; COMMIT; END // DELIMITER ; 复制代码
-
调用存储过程
call insert_user(1000000); 复制代码
-
打开性能检查工具profiling
set profiling = 1; 复制代码
-
启用索引下推方式查询
select * from user where zipcode = '10018' and name like '%李%'; 复制代码
-
禁用索引下推方式查询
select /*+ no_icp(user) */ * from user where zipcode = '10018' and name like '%李%'; 复制代码
-
查看当前会话下的profiles
show profiles; 复制代码
-
结果如下:
-
有索引下推0.21s
-
没有索引下推花了2.6s
使用ICP性能提高还是比较明显的,特别是数据量大的情况下。
执行计划中的索引下推
如何判断你的SQL中是否使用了到索引下推的特性呢?
我们利用explain查看SQL的执行计划时,如果发现Extra字段中有Using index condition,即表示使用到了索引下推。
注意如果发现一直没有用上索引下推,需要检查下功能是否被禁用,默认是打开的。
-
可以通过设置系统变量optimizer_switch控制:index_condition_pushdown
# 打开索引下推 SET optimizer_switch = 'index_condition_pushdown=on'; # 关闭索引下推 SET optimizer_switch = 'index_condition_pushdown=off'; 复制代码
总结
本文介绍了索引下推特性,并简要分析了它的实现原理。虽然说,这个特性MySQL支持了,但是为什么我们还要学习理解它呢,因为它对于我们如何写SQL、如何创建索引还是起着指导作用的。如果本文对你有帮助的话,请留下一个赞吧。