网站做标签免费行情软件网站下载大全
(/≧▽≦)/~┴┴ 嗨~我叫小奥 ✨✨✨
👀👀👀 个人博客：小奥的博客
👍👍👍：个人CSDN
⭐️⭐️⭐️：传送门
🍹 本人24应届生一枚，技术和水平有限，如果文章中有不正确的内容，欢迎多多指正！
📜 欢迎点赞收藏关注哟！ ❤️
文章目录
- MySQL优化之索引下推
- 一、概念
- 二、验证
- 2.1 建表语句
- 2.2 关闭索引下推
- 2.3 打开索引下推
- 三、总结
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。