专业网站设计专业服务,wordpress菜单分列,瓦力工厂少儿编程加盟,网站推广要点(/≧▽≦)/~┴┴ 嗨~我叫小奥 ✨✨✨ #x1f440;#x1f440;#x1f440; 个人博客#xff1a;小奥的博客 #x1f44d;#x1f44d;#x1f44d;#xff1a;个人CSDN ⭐️⭐️⭐️#xff1a;传送门 #x1f379; 本人24应届生一枚#xff0c;技术和水平有限 个人博客小奥的博客 个人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)
)engineInnoDB default charsetutf8;#插入数据
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_switchindex_condition_pushdownoff;
#查看执行计划
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_switchindex_condition_pushdownon;
#查看执行计划
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。
