前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下:
今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性。
主要作用有两个:
总之就是了提升MySQL查询性能。
创建一张用户表,造点数据验证一下:
CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(100) NOT NULL COMMENT '姓名', `age` tinyint NOT NULL COMMENT '年龄', `gender` tinyint NOT NULL COMMENT '性别', PRIMARY KEY (`id`), KEY `idx_name_age` (`name`,`age`) ) ENGINE=InnoDB COMMENT='用户表';
在 姓名和年龄 (name
,age
) 两个字段上创建联合索引。
查询SQL执行计划,验证一下是否用到索引下推:
explain select * from user where name='一灯' and age>2;
执行计划中的Extra列显示了Using index condition,表示用到了索引下推的优化逻辑。
查看索引下推的配置:
show variables like '%optimizer_switch%';
如果输出结果中,显示 index_condition_pushdown=on,表示开启了索引下推。
也可以手动开启索引下推:
set optimizer_switch="index_condition_pushdown=on";
关闭索引下推:
set optimizer_switch="index_condition_pushdown=off";
索引下推在底层到底是怎么实现的?
是怎么减少了回表的次数?
又减少了存储引擎和MySQL Server层的数据传输量?
在没有使用索引下推的情况,查询过程是这样的:
画两张图,就一目了然了。
下面这张图是回表查询的过程:
下面这张图是存储引擎返回给MySQL Server端的处理过程:
我们再看一下在使用索引下推的情况,查询过程是这样的:
现在是不是理解了索引下推的两个作用:
再附一张Explain执行计划详解图: