前言

说到MySQL的优化手段，不得不提的是​​MYSQL5.6​​中引入的一种新特性，索引下推，英文是 ​​index condition pushdown​​，一般简称为 ​​ICP​​。这也是MySQL面试中经常被问到的一个考点，本文就此分享下索引下推是怎么一回事，它是如何对提高查询效率起到帮助的。

理解索引下推

索引下推就是指在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数来提高查询效率。

如何理解呢？ 我们直接上栗子来解释：

  1. 新建用户表
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​​，用于查找某个邮编下，名字包含陈的人。
  1. 查询语句
select * from user where zipcode = '100001' and name like '%陈%' and address like '%余杭%';
  • 查询条件根据​​zipcode​​精确查找，​​name​​和​​address​​全模糊匹配。

如果MySQL5.6以前, 没有索引下推特性，整个流程如下图：

你不得不知的MYSQL优化——索引下推_字段

  • 联合索引中知道​​100001​​的数据项，因为​​name​​是全模糊，无法走索引，​​address​​压根没有索引，他们无法在联合索引中过滤，怎么办？只能回到数据最全的聚簇索引上进行​​name like '%陈%' and address like '%余杭%'​​这个逻辑的过滤。
  • 按照上面的方式，每次回到聚簇索引这个一个称做 “回表” 的过程，势必增加磁盘IO，从而影响查询性能。

那么有什么办法可以优化呢？

优化的目标就是尽量减少回表这一过程，我们发现联合索引上竟然有了name字段的信息，为什么我们不能充分利用呢？ ​​name like '%陈%'​​我们可以直接在索引树上进行判断name是不是包含陈，从而减少回表次数。这也正是MySQL5.6中优化的特性，如下图所示：

你不得不知的MYSQL优化——索引下推_字段_02

  • 根据查询条件​​name like '%陈%'​​，​​陈旭阳​​包含了陈， 就无需回表了。

上面途中的索引都用表格表示，只是为了方便，实际上索引底层数据结构是B+数据

一句话总结：索引下推（index condition pushdown，ICP），有效的减少了回表次数，提高了查询效率。

索引下推性能比较

竟然索引下推可以提高效率，那我们验证下。

  1. 沿用上面的user表
  2. 创建存储过程，添加数据
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 ;
  1. 调用存储过程
call insert_user(1000000);
  1. 打开性能检查工具​​profiling​​
set profiling = 1;
  1. 启用索引下推方式查询
select * from user where zipcode = '10018' and name like '%李%';
  1. 禁用索引下推方式查询
select /*+ no_icp(user) */ * from user where zipcode = '10018' and name like '%李%';
  1. 查看当前会话下的profiles
show profiles;
  1. 结果如下：
  • 有索引下推​​0.21s​​

你不得不知的MYSQL优化——索引下推_联合索引_03

  • 没有索引下推花了​​2.6s​​

你不得不知的MYSQL优化——索引下推_mysql_04

使用ICP性能提高还是比较明显的，特别是数据量大的情况下。

执行计划中的索引下推

如何判断你的SQL中是否使用了到索引下推的特性呢？

我们利用​​explain​​查看SQL的执行计划时，如果发现​​Extra​​字段中有​​Using index condition​​，即表示使用到了索引下推。

你不得不知的MYSQL优化——索引下推_mysql_05

注意如果发现一直没有用上索引下推，需要检查下功能是否被禁用，默认是打开的。

  • 可以通过设置系统变量​​optimizer_switch​​控制：​​index_condition_pushdown​​
# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

总结

本文介绍了索引下推特性，并简要分析了它的实现原理。虽然说，这个特性MySQL支持了，但是为什么我们还要学习理解它呢，因为它对于我们如何写SQL、如何创建索引还是起着指导作用的。如果本文对你有帮助的话，请留下一个赞吧。