MySQL优化之索引下推

MySQL优化之索引下推

一、概念

索引条件下推(Index Condition Pushdown)，简称ICP。MySQL5.6新添加，用于优化数据的查询。

比如说有这样的场景：

有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

SELECT * FROM table WHERE key1 = ‘z’ AND key1 LIKE ‘%a’;

其中的 key1 = 'z' 可以使用到索引，但是 key1 LIKE '%a' 却无法使用到索引，在以前版本的 MySQL 中，是按照下边步骤来执行这个查询的：

  • 先根据 key1 = 'z' 这个条件，从二级索引 idx_key1 中获取到对应的二级索引记录。
  • 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合
  • key1 LIKE '%a' 这个条件，将符合条件的记录加入到最后的结果集。

但是虽然key1 LIKE '%a'不能组成范围区间参与 range 访问方法的执行，但这个条件毕竟只涉及到了key1 列，所以MySQL 把上边的步骤改进了一下：

  • 先根据 key1 = 'z' 这个条件，定位到二级索引 idx_key1 中对应的二级索引记录。
  • 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 LIKE '%a' 这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
  • 对于满足 key1 LIKE '%a' 这个条件的二级索引记录执行回表操作。

我们知道，回表操作其实是一个随机 IO ，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。

二、验证

如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition 。

2.1 建表语句

#创建表 建立name, city, age三个字段的复合索引，sex是为了避免索引覆盖
CREATE TABLE user_index (
                      id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
                      name varchar(32)  COMMENT '姓名',
                      city varchar(32)  COMMENT '城市',
                      age int(11)  COMMENT '年龄',
                      sex int(1) default 0,
                      primary key(id),
                      key idx_name_city(name, city, age)
)engine=InnoDB default charset=utf8;
 
#插入数据
insert into user_index(name, city, age) values('ZhaoDa', 'BeiJing', 20),('QianEr', 'ShangHai', 21),('SunSan', 'GuanZhou', 22), ('LiSi', 'ShenZhen', 24), ('ZhouWu', 'NingBo', 25),  ('WuLiu', 'HangZhou', 26), ('ZhengQi', 'NanNing', 27), ('WangBa', 'YinChuan', 28), ('LiSi', 'TianJin', 29), ('ZhangSan', 'NanJing', 30), ('CuiShi', 'ZhengZhou', 65),  ('LiSi', 'KunMing', 29), ('LiSi', 'ZhengZhou', 30);

2.2 关闭索引下推

关闭索引下推，查看执行计划：

#关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+
|id|select_type|table     |partitions|type|possible_keys|key          |key_len|ref  |rows|filtered|Extra      |
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |user_index|null      |ref |idx_name_city|idx_name_city|99     |const|1   |10      |Using where|
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+

2.3 打开索引下推

打开索引下推，默认是开启的，再次查看执行计划：

#打开索引下推  默认是开启
set optimizer_switch='index_condition_pushdown=on';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+
|id|select_type|table     |partitions|type|possible_keys|key          |key_len|ref  |rows|filtered|Extra                |
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |user_index|null      |ref |idx_name_city|idx_name_city|99     |const|1   |10      |Using index condition|
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+

并且索引还是遵循最左匹配的。

三、总结

索引条件下推：简单来说就是把索引当中的所有数据全部用到减少回表的次数，达到优化的目的。

表现：查看执行计划时Extra会显示为Using index condition。