MySQL之索引下推

mysql索引下推

前言

写博客是自己对知识梳理，目前是写给自己看，算是自己学习后的作业，也是为了养成一个良好的习惯。

一、什么是索引下推

1. 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询；
2. 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 ；
3. 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 ；
4. 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

二、测试数据

1. 创建测试表

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL COMMENT '用户名 ',
  `age` int(8) NOT NULL COMMENT '年纪',
	`address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除 默认否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';

2. 添加表数据

-- 初始化数据 
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李四', 22, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李五', 22, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李六', 23, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('张三', 24, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李期', 24, '中国');

三、不支持索引下推情况

1. 查看索引下推的状态

show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

3. 2. 关闭索引下推
      索引下推是mysql 5.6优化查询回表的功能，在5.6之前都不支持索引下推，笔者用的8.0，则需要先关闭索引下推：

set optimizer_switch='index_condition_pushdown=off';

3. 执行sql

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

分析一下以上sql执行的过程：

1. idx_name_age`组合索引 遵循最左匹配遇到非等值判断时匹配停止，name的范围查询则会使age这个条件就不会走索引；
2. 会先在name索引上顺序找到 符合条件的name和id数据；
3. 然后通过id在聚簇索引上回表找到对应的age数据，将结果存放在临时表中；
4. 最后在临时表中通过age条件来筛选数据。

以上过程会扫描4条记录，回表4次。
extra = Using where：表示优化器需要通过索引回表查询数据。

四、索引下推优化

1. 开启索引下推

set optimizer_switch='index_condition_pushdown=on';

3. 2. 执行sql

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

开启索引下推优化后再分析一下以上sql执行的过程：

1. 会先在idx_name_age索引上通过name的范围匹配扫描结果后将age条件下推再用age筛选一遍找到符合统计数据的id（即扫描4行数据）;
2. 再用id到聚簇索引上回表查询完整的数据（回表次数是2次）。
   以上过程会扫描4行数据，回表次数是2次。

extra = Using index condition 表示索引下推。

总结

1. 索引下推功能是mysql 5.6推出优化回表的操作，只支持向上兼容，低版本是不支持的；
2. 索引下推优化的只是回表次数，扫描行数还是一样的。