草庐IT

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

信仰adc 2023-03-28 原文

介绍

概念介绍

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

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

优化效果

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

适用条件

  1. 当需要访问完整的表行时，ICP 用于 range、 ref、 eq_ref、ref_or_null​类型的访问数据方法
  2. 不支持主建索引的 ICP；
  3. ICP 可用于InnoDB 和MyISAM表，包括分区InnoDB和 MyISAM表
  1. 注意：5.6 版本不适用分区表查询，5.7 版本后可以用于分区表查询
  1. 对于InnoDB表，ICP 仅用于二级索引。ICP 的目标是减少整行读取的次数，从而减少 I/O 操作。对于 InnoDB聚簇索引，完整的记录已经读入InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O
  2. 在虚拟生成列上创建的二级索引不支持 ICP
  1. 注意：InnoDB 支持虚拟生成列的二级索引
  1. 不能下推引用子查询的条件
  2. 不能下推引用存储函数的条件，存储引擎不能调用存储函数
  3. 触发条件不能下推

原理

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


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

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

未使用 ICP 的情况下
  1. 存储引擎读取索引记录
  2. 根据索引中的主键值，定位并读取完整的行记录
  3. 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件
使用 ICP 的情况下
  1. 获取下一行的索引数据（但不是完整的表行数据）。
  2. 测试WHERE适用于此表的条件部分，并且可以仅使用索引列进行检查。如果不满足条件，则继续处理下一行的索引数据。
  3. 如果满足条件，则使用索引数据定位该行表数据并读取全表行且将结果集返回给 MySQL server。
  4. WHERE条件测试适用于此表的条件的其余部分，根据测试结果接受或拒绝该行。
  5. 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)

结论

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






































性能分析ConditionspanstylecolorMySQL存储引擎性能优化index索引下推MySQL数据库yyds干货盘点

有关性能分析 | MySQL Index Condition Pushdown(ICP)的更多相关文章

  1. Ruby 的数字方法性能 - 2

    我正在使用Ruby解决一些ProjectEuler问题，特别是这里我要讨论的问题25(Fibonacci数列中包含1000位数字的第一项的索引是多少？)。起初，我使用的是Ruby2.2.3，我将问题编码为:number=3a=1b=2whileb.to_s.length但后来我发现2.4.2版本有一个名为digits的方法，这正是我需要的。我转换为代码:whileb.digits.length当我比较这两种方法时，digits慢得多。时间./025/problem025.rb0.13s用户0.02s系统80%cpu0.190总计./025/problem025.rb2.19s用户0.0

  2. ruby - Ruby 性能中的计时器 - 2

    我正在寻找一个用ruby​​演示计时器的在线示例，并发现了下面的代码。它按预期工作，但这个简单的程序使用30Mo内存(如Windows任务管理器中所示)和太多CPU有意义吗？非常感谢deftime_blockstart_time=Time.nowThread.new{yield}Time.now-start_timeenddefrepeat_every(seconds)whiletruedotime_spent=time_block{yield}#Tohandle-vesleepinteravalsleep(seconds-time_spent)iftime_spent

  3. ruby-on-rails - 如果条件与 &&，是否有任何性能提升 - 2

    如果用户是所有者，我有一个条件来检查说删除和文章。delete_articleifuser.owner?另一种方式是user.owner?&&delete_article选择它有什么好处还是它只是一种写作风格 最佳答案 性能不太可能成为该声明的问题。第一个要好得多-它更容易阅读。您future的自己和其他将开始编写代码的人会为此感谢您。 关于ruby-on-rails-如果条件与&&，是否有任何性能提升，我们在StackOverflow上找到一个类似的问题：

  4. ruby - 如何找到我的 Ruby 应用程序中的性能瓶颈？ - 2

    我编写了一个Ruby应用程序，它可以解析来自不同格式html、xml和csv文件的源中的大量数据。我如何找出代码的哪些区域花费的时间最长？有没有关于如何提高Ruby应用程序性能的好资源？或者您是否有任何始终遵循的性能编码标准？例如，你总是用加入你的字符串吗？output=String.newoutput或者你会使用output="#{part_one}#{part_two}\n" 最佳答案 好吧，有一些众所周知的做法，例如字符串连接比“#{value}”慢得多，但是为了找出您的脚本在哪里消耗了大部分时间或比所需时间更多，您需要进行分

  5. 建模分析 | 平面2R机器人(二连杆)运动学与动力学建模(附Matlab仿真) - 2

    目录0专栏介绍1平面2R机器人概述2运动学建模2.1正运动学模型2.2逆运动学模型2.3机器人运动学仿真3动力学建模3.1计算动能3.2势能计算与动力学方程3.3动力学仿真0专栏介绍?附C++/Python/Matlab全套代码?课程设计、毕业设计、创新竞赛必备！详细介绍全局规划(图搜索、采样法、智能算法等)；局部规划(DWA、APF等)；曲线优化(贝塞尔曲线、B样条曲线等)。?详情：图解自动驾驶中的运动规划(MotionPlanning)，附几十种规划算法1平面2R机器人概述如图1所示为本文的研究本体——平面2R机器人。对参数进行如下定义：机器人广义坐标

  6. 网站日志分析软件--让网站日志分析工作变得更简单 - 2

    网站的日志分析，是seo优化不可忽视的一门功课，但网站越大，每天产生的日志就越大，大站一天都可以产生几个G的网站日志，如果光靠肉眼去分析，那可能看到猴年马月都看不完，因此借助网站日志分析工具去分析网站日志，那将会使网站日志分析工作变得更简单。下面推荐两款网站日志分析软件。第一款：逆火网站日志分析器逆火网站日志分析器是一款功能全面的网站服务器日志分析软件。通过分析网站的日志文件，不仅能够精准的知道网站的访问量、网站的访问来源，网站的广告点击，访客的地区统计，搜索引擎关键字查询等，还能够一次性分析多个网站的日志文件，让你轻松管理网站。逆火网站日志分析器下载地址：https://pan.baidu.

  7. STM32的HAL和LL库区别和性能对比 - 2

    LL库和HAL库简介LL：Low-Layer，底层库HAL：HardwareAbstractionLayer，硬件抽象层库LL库和hal库对比，很精简，这实际上是一个精简的库。LL库的配置选择如下：在STM32CUBEMX中，点击菜单的“ProjectManager”–>“AdvancedSettings”，在下面的界面中选择“AdvancedSettings”，然后在每个模块后面选择使用的库总结：1、如果使用的MCU是小容量的，那么STM32CubeLL将是最佳选择；2、如果结合可移植性和优化，使用STM32CubeHAL并使用特定的优化实现替换一些调用，可保持最大的可移植性。另外HAL和L

  8. ABB-IRB-1200运动学分析MATLAB RVC工具分析+Simulink-Adams联合仿真 - 2

    一、机器人介绍        此处是基于MATLABRVC工具箱，对ABB-IRB-1200型号的微型机械臂进行正逆向运动学分析，并利Simulink工具实现对机械臂进行具有动力学参数的末端轨迹规划仿真，最后根据机械模型设计Simulink-Adams联合仿真。 图1.ABBIRB 1200尺寸参数示意图ABBIRB 1200提供的两种型号广泛适用于各作业，且两者间零部件通用，两种型号的工作范围分别为700 mm 和 900 mm，大有效负载分别为 7 kg 和5 kg。 IRB 1200 能够在狭小空间内能发挥其工作范围与性能优势，具有全新的设计、小型化的体积、高效的性能、易于集成、便捷的接

  9. 关于Qt程序打包后运行库依赖的常见问题分析及解决方法 - 2

    目录一.大致如下常见问题：（1）找不到程序所依赖的Qt库version`Qt_5'notfound(requiredby（2）CouldnotLoadtheQtplatformplugin"xcb"in""eventhoughitwasfound（3）打包到在不同的linux系统下，或者打包到高版本的相同系统下，运行程序时，直接提示段错误即segmentationfault，或者Illegalinstruction(coredumped)非法指令（4）ldd应用程序或者库，查看运行所依赖的库时，直接报段错误二.问题逐个分析，得出解决方法：（1）找不到程序所依赖的Qt库version`Qt_5'

  10. ruby-on-rails - 如何使用 ruby​​-prof 和 JMeter 分析 Rails - 2

    我想使用ruby​​-prof和JMeter分析Rails应用程序。我对分析特定Controller/操作/或模型方法的建议方法不感兴趣，我想分析完整堆栈，从上到下。所以我运行这样的东西:RAILS_ENV=productionruby-prof-fprof.outscript/server>/dev/null然后我在上面运行我的JMeter测试计划。然而，问题是使用CTRL+C或SIGKILL中断它也会在ruby​​-prof可以写入任何输出之前杀死它。如何在不中断ruby​​-prof的情况下停止mongrel服务器？ 最佳答案

随机推荐