索引下推如何进行数据过滤？

实验环境
GreatSQL 8.0.25 InnoDB

1.索引下推介绍

• 索引下推，英文全称（Index Condition Pushdown）简称 ICP 。
• MySQL5.6 版本推出的用于优化查询的功能。
• 某些特定索引条件下，ICP 可减少存储引擎查询回表的次数。
  

2.适用条件

• 当需要访问全表记录时，ICP 用于 range、ref、eq_ref 和 ref_or_null 访问方法。
• ICP 可以用于 InnoDB 和 MyISAM 表，包括分区 InnoDB 和 MyISAM 表。
• 对于InnoDB表，ICP 仅用于二级索引。ICP 的目标是减少整行记录读取的次数，从而减少I/O操作。对于InnoDB 聚集索引，完整的记录已经被读取到 InnoDB 缓冲区，在这种情况下使用 ICP 就不会减少I/O。
• 虚拟列上创建的二级索引，不支持 ICP。
• 使用子查询的SQL 不支持 ICP。
• 调用存储过程的SQL 不支持 ICP，因为存储引擎无法调用位于 MySQL Server 层中的存储过程。
• 触发器 不支持 ICP。
  

3.如何启用
ICP 默认是开启的，可以通过下列命令进行关闭、启用、查看

1. # 关闭ICP
   
2. SET optimizer_switch = 'index_condition_pushdown=off';
   
3. # 开启ICP
   
4. SET optimizer_switch = 'index_condition_pushdown=on';
   
5. # 查看ICP当前状态
   
6. show VARIABLES like '%optimizer_switch%'

复制代码

4.ICP 如何工作
不使用 ICP 优化时的查询步骤

• 获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。
• 然后通过where条件判断当前数据是否符合条件，符合返回数据。
  

使用 ICP 优化时的查询步骤

• 获取下一行的索引信息。
• 检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。
• 用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据
  

5.实验测试
表结构如下

1. CREATE TABLE `student` (
   
2.   `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
   
3.   `uid` int NOT NULL COMMENT '学号',
   
4.   `age` int NOT NULL COMMENT '年龄',
   
5.   `name` char(32) NOT NULL COMMENT '姓名',
   
6.   `sex` char(4) NOT NULL COMMENT '性别',
   
7.   `grade` int NOT NULL COMMENT '年级',
   
8.   `class` varchar(32) NOT NULL COMMENT '班级',
   
9.   `major` varchar(64) NOT NULL COMMENT '专业',
   
10.   PRIMARY KEY (`id`),
    
11.   KEY `idx_anm` (`age`,`name`,`major`)
    
12. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

复制代码

现有一个需求，查询年龄16、姓陈、学习软件工程的同学信息

1. # 启用ICP
   
2. [root@GreatSQL][test]>explain select * from student where age=16 and name like '陈%' and major='软件工程';
   
3. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
   
4. | id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
   
5. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
   
6. |  1 | SIMPLE      | student | NULL       | range | idx_anm       | idx_anm | 390     | NULL |    1 |    33.33 | Using index condition |
   
7. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
   
8. 1 row in set, 1 warning (0.00 sec)
   
9. 
   
10. # 不启用ICP
    
11. [root@GreatSQL][test]>explain select /*+ no_icp (student) */ * from student where age=16 and name like '陈%' and major='软件工程';
    
12. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    
13. | id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    
14. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    
15. |  1 | SIMPLE      | student | NULL       | range | idx_anm       | idx_anm | 390     | NULL |    1 |    33.33 | Using where |
    
16. +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    
17. 1 row in set, 1 warning (0.00 sec)

复制代码

启用 ICP 解析出来的 Extra 是 Using index condition ，不启用 ICP 解析出来的 Extra 是 Using where
其他查询结果基本一样,看不出有效率差别，可以通过开启profiling进行查看

1. [root@GreatSQL][test]>set profiling=1;
   
2. Query OK, 0 rows affected, 1 warning (0.00 sec)
   
3. 
   
4. [root@GreatSQL][test]>select * from student where age=16 and name like '陈%' and major='软件工程';
   
5. +----+--------+-----+--------+-----+-------+-------+--------------+
   
6. | id | uid    | age | name   | sex | grade | class | major        |
   
7. +----+--------+-----+--------+-----+-------+-------+--------------+
   
8. |  1 | 100001 |  16 | 陈红   | 男  |     4 | 3     | 软件工程     |
   
9. +----+--------+-----+--------+-----+-------+-------+--------------+
   
10. 1 row in set (0.00 sec)
    
11. 
    
12. (Tue Jan  4 15:51:50 2022)[root@GreatSQL][test]>select /*+ no_icp (student) */ * from student where age=16 and name like '陈%' and major='软件工程';
    
13. +----+--------+-----+--------+-----+-------+-------+--------------+
    
14. | id | uid    | age | name   | sex | grade | class | major        |
    
15. +----+--------+-----+--------+-----+-------+-------+--------------+
    
16. |  1 | 100001 |  16 | 陈红   | 男  |     4 | 3     | 软件工程     |
    
17. +----+--------+-----+--------+-----+-------+-------+--------------+
    
18. 1 row in set (0.00 sec)
    
19. 
    
20. [root@GreatSQL][test]>show profiles\G;
    
21. *************************** 1. row ***************************
    
22. Query_ID: 1
    
23. Duration: 0.00043725
    
24.    Query: select * from student where age=16 and name like '陈%' and major='软件工程'
    
25. *************************** 2. row ***************************
    
26. Query_ID: 2
    
27. Duration: 0.00048500
    
28.    Query: select /*+ no_icp (student) */ * from student where age=16 and name like '陈%' and major='软件工程'
    
29. 2 rows in set, 1 warning (0.00 sec)
    
30. 
    
31. ERROR:
    
32. No query specified

复制代码

使用了 ICP 的 Duration 要比没有使用的时间稍短一些，多次测试效率对比结果都一样，从测试来看，使用 ICP 优化的查询效率会好一些。

6.查询流程
没有开启 ICP

• 根据最左原则先找到 age=16 的记录。
• 然后找出所有符合like '陈%'的行记录，然后再根据步骤1查出来的数据回表，根据主键过滤符合条件的记录
• 然后找出所有符合 major='软件工程' 再根据步骤2查出所有符合条件的记录
• 步骤1查询过程，每个符合 age=16 的记录都要先进行回表操作。
  

开启 ICP

• 根据最左原则先找到 age=16 的记录。
• 查看索引过滤掉不符合 like '陈%' 的数据
• 查看索引过滤掉不符合 major='软件工程' 的数据
• 步骤1查询过程，先不进行回表操作，先通过索引找出符合2、3条件的情况，如何不符合则直接进行下一个步骤查询，故回表次数会少一些。
  

7.ICP 图解

插图来源 mariadb.com ，仅做笔记分享，非商业用途。

图1：没有启用ICP查询过程图片

图2：启用ICP查询过程图片

说明：图2的几个X是由于在索引层就进行数据过滤了，故不需要再进行回表。

8.更多内容查看官网
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

Enjoy GreatSQL