性能分析 | MySQL Index Condition Pushdown(ICP)

介绍

概念介绍

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，ICP 是针对 MySQL 使用索引从表中检索行的情况的优化方式

关闭 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，MySQL 服务器会评估这些WHERE行的条件。
启用 ICP 后，如果 WHERE仅使用索引中的列可以评估部分条件，则 MySQL Server会推送这部分条件WHERE条件下降到存储引擎。然后，存储引擎使用索引条目评估推送的索引条件，只有在满足条件时才会从表中读取行，而非将所有通过index 推送的结果传递到MySQL server层进行where过滤

优化效果

ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数，减少io次数，提高查询语句性能和效率

适用条件

当需要访问完整的表行时，ICP 用于 range、 ref、 eq_ref、ref_or_null类型的访问数据方法
不支持主建索引的 ICP；
ICP 可用于InnoDB 和MyISAM表，包括分区InnoDB和 MyISAM表

注意：5.6 版本不适用分区表查询，5.7 版本后可以用于分区表查询

对于InnoDB表，ICP 仅用于二级索引。ICP 的目标是减少整行读取的次数，从而减少 I/O 操作。对于 InnoDB聚簇索引，完整的记录已经读入InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O
在虚拟生成列上创建的二级索引不支持 ICP

注意：InnoDB 支持虚拟生成列的二级索引

不能下推引用子查询的条件
不能下推引用存储函数的条件，存储引擎不能调用存储函数
触发条件不能下推

原理

先了解下MySQL的基本体系架构如图：

性能分析 | MySQL Index Condition Pushdown(ICP)

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理

简单对比一下使用和未使用 ICP 两种情况下,执行过程如下：

未使用 ICP 的情况下

存储引擎读取索引记录
根据索引中的主键值，定位并读取完整的行记录
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件

使用 ICP 的情况下

获取下一行的索引数据（但不是完整的表行数据）。
测试WHERE适用于此表的条件部分，并且可以仅使用索引列进行检查。如果不满足条件，则继续处理下一行的索引数据。
如果满足条件，则使用索引数据定位该行表数据并读取全表行且将结果集返回给 MySQL server。
WHERE条件测试适用于此表的条件的其余部分，根据测试结果接受或拒绝该行。
EXPLAIN使用索引条件下推时，输出显示 Using index condition在 Extra列中。它不显示Using index ，因为当必须读取完整的表行时，这不适用

具体实践

场景

使用Mysql 索引下推功能优化全模糊匹配

环境信息：5.7.17-log

准备数据表

表结构：

mysql> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_uuid` char(50) COLLATE utf8mb4_bin NOT NULL,
  `house_uuid_bin` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `name` varchar(250) COLLATE utf8mb4_bin NOT NULL,
  `password` varchar(250) COLLATE utf8mb4_bin NOT NULL,
  `score` varchar(250) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uuid_name` (`house_uuid_bin`,`name`),
  KEY `idx_1` (`password`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7345330 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

涉及的SQL 如下:

构建测试数据 结果如下：
mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
|  6272616 |
+----------+
1 row in set (12.21 sec)
mysql> select count(password) ,password from student group by password having count(password)>67;
+-----------------+-------------------------------------------+
| count(password) | password                                  |
+-----------------+-------------------------------------------+
|         4390912 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 |
|              68 | *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 |
|              68 | *15B4A9F089BEC4C84A24C5148B14A80C14651492 |
|              68 | *23E7A7428138939FBE2F69D23E5B87383EFD83C9 |
|              68 | *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C |
|              68 | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 |
|              68 | *7E9FDC7F61153649AB9A75CED26807DF74F86E65 |
|              68 | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB |
|              68 | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 |
|              68 | *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E |
|              68 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-----------------+-------------------------------------------+
11 rows in set (2.18 sec)

mysql> 


mysql> select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 100;

这张表上已经存在了一个组合索引 idx_1 (password,name) 组成

打开 ICP 的性能测试：

通过profile 查看执行过程中耗时情况

mysql> SET profiling = 1;     ###  profiling 监视 SQL 语句在各个阶段的执行情况
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set optimizer_switch="index_condition_pushdown=on";   ##  开启ICP default enabled
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;
+--------+--------------------------------------+--------------------------------------+-----------+-------------------------------------------+-----------------------+
| id     | student_uuid                         | house_uuid_bin                       | name      | password                                  | score                 |
+--------+--------------------------------------+--------------------------------------+-----------+-------------------------------------------+-----------------------+
| 596447 | 2454725d-a847-11ed-b0e7-52540071e14c | 24547273-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 624583 | 727281b1-a847-11ed-b0e7-52540071e14c | 727281ce-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 632766 | 8b04383d-a847-11ed-b0e7-52540071e14c | 8b04385c-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 636121 | 93e319af-a847-11ed-b0e7-52540071e14c | 93e319c0-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 639927 | c6caf01f-a847-11ed-b0e7-52540071e14c | c6caf041-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 647842 | e0827603-a847-11ed-b0e7-52540071e14c | e0827623-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |


mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                |
+----------+------------+----------------------------------------------------------------------------------------------------------------------+                                                                                           |
|       1  | 0.00089825 | SET profiling = 1                                                                                                    |
|       2  | 0.00091550 | set optimizer_switch="index_condition_pushdown=on"                                                                   |
|       3  | 0.01679200 | select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110      |
+----------+------------+----------------------------------------------------------------------------------------------------------------------+
mysql> show profile cpu,block io for query 7;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.001072 | 0.001046 |   0.000020 |            0 |             0 |
.........................................
| executing            | 0.000011 | 0.000008 |   0.000001 |            0 |             0 |
| Sending data         | 0.001428 | 0.001435 |   0.000000 |            0 |             0 |
| end                  | 0.000030 | 0.000024 |   0.000000 |            0 |             0 |
| query end            | 0.000045 | 0.000045 |   0.000000 |            0 |             0 |
.................................
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.01 sec)

mysql> show session status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
..........................
| Handler_read_next          | 430   |
| Handler_read_rnd_next      | 1083  |
| Handler_rollback           | 0     |
.................................
+----------------------------+-------+
18 rows in set (0.01 sec)

查看slow-log情况：

之前通过 group by 计算, 符合 password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' 条件的数据有 4390912条,在name 全模糊匹配的前提下, 本次查询竟然只需要检索100条数据，效率依然非常高的！！！

# Query_time: 0.009116  Lock_time: 0.001718 Rows_sent: 110  Rows_examined: 110
SET timestamp=1676009140;
select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;

查看执行计划

## 查看执行计划
mysql> desc select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows    | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_1         | idx_1 | 1002    | const | 3101330 |    11.11 | Using index condition |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

关闭 ICP 的性能测试：

通过profile 查看执行过程中耗时情况

mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;
+--------+--------------------------------------+--------------------------------------+-----------+-------------------------------------------+-----------------------+
| id     | student_uuid                         | house_uuid_bin                       | name      | password                                  | score                 |
+--------+--------------------------------------+--------------------------------------+-----------+-------------------------------------------+-----------------------+
| 596447 | 2454725d-a847-11ed-b0e7-52540071e14c | 24547273-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 624583 | 727281b1-a847-11ed-b0e7-52540071e14c | 727281ce-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 632766 | 8b04383d-a847-11ed-b0e7-52540071e14c | 8b04385c-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 636121 | 93e319af-a847-11ed-b0e7-52540071e14c | 93e319c0-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 639927 | c6caf01f-a847-11ed-b0e7-52540071e14c | c6caf041-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |
| 647842 | e0827603-a847-11ed-b0e7-52540071e14c | e0827623-a847-11ed-b0e7-52540071e14c | useer-168 | *0065D5183400BC0CBAC4C9510475790E2BFCEBB8 | db9a636ed082__EXP_168 |


mysql> show profile cpu,block io for query 7;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000176 | 0.000174 |   0.000000 |            0 |             0 |
| checking permissions | 0.000057 | 0.000053 |   0.000003 |            0 |             0 |
| Opening tables       | 0.000485 | 0.000487 |   0.000000 |            0 |             0 |
| init                 | 0.000054 | 0.000051 |   0.000000 |            0 |             0 |
| System lock          | 0.000047 | 0.000047 |   0.000000 |            0 |             0 |
| optimizing           | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| statistics           | 0.000208 | 0.000213 |   0.000000 |            0 |             0 |
| preparing            | 0.000141 | 0.000136 |   0.000000 |            0 |             0 |
| executing            | 0.000666 | 0.000670 |   0.000000 |            0 |             0 |
| Sending data         | 0.000121 | 0.000118 |   0.000000 |            0 |             0 |
| end                  | 0.000011 | 0.000010 |   0.000000 |            0 |             0 |
| query end            | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000030 | 0.000031 |   0.000000 |            0 |             0 |
| closing tables       | 0.000011 | 0.000010 |   0.000000 |            0 |             0 |
| freeing items        | 0.000048 | 0.000049 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000112 | 0.000112 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

mysql> show session status like '%handler%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
.................................
| Handler_read_next          | 25090464 |
| Handler_read_prev          | 0        |
| Handler_read_rnd           | 0        |
| Handler_read_rnd_next      | 2034       |
| Handler_rollback           | 0        |
.................................
+----------------------------+----------+
18 rows in set (0.00 sec)

mysql>

查看slow-log情况：

# Time: 2023-02-10T14:25:09.862957+08:00
# User@Host: dba_root[dba_root] @ localhost []  Id: 414667
# Query_time: 0.018923  Lock_time: 0.002574 Rows_sent: 18  Rows_examined: 712
SET timestamp=1676010309;
select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;

查看执行计划

mysql> desc select *  from student where password='*0065D5183400BC0CBAC4C9510475790E2BFCEBB8' and name like '%e%' limit 110;
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_1         | idx_1 | 1002    | const | 3101330 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结论

由以上测试情况可以看到，在二级索引是复合索引且前面的条件过滤性较低的情况下，打开 ICP后在存储引擎内部通过与where条件的对比，直接过滤掉不符合条件的数据。该过程不回表(Using index condition),只访问符合条件的条记录并返回给MySQL Server,可以有效的降低 server 层和 engine 层之间交互的次数，从而有效的降低在运行时间
关闭 index_condition_pushdown 后SQL执行过程,主要依靠复合索引的最左前导列和回表( Using where )方式进行过滤数据，增加了io的访问和各层之间的交互次数

秒客网

性能分析 | MySQL Index Condition Pushdown(ICP)

介绍

概念介绍

优化效果

适用条件

原理

未使用 ICP 的情况下

使用 ICP 的情况下

具体实践

场景

准备数据表

打开 ICP 的性能测试：

通过profile 查看执行过程中耗时情况

查看slow-log情况：

查看执行计划

关闭 ICP 的性能测试：

通过profile 查看执行过程中耗时情况

查看slow-log情况：

查看执行计划

结论

相关文章