首页 > 数据库 > 性能分析 | MySQL Index Condition Pushdown(ICP)

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

时间：2023-02-10 22:32:50浏览次数：43  
标签：Index b0e7 11ed 52540071e14c 0065D5183400BC0CBAC4C9510475790E2BFCEBB8 168 MySQL 

介绍

概念介绍

索引下推(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的基本体系架构如图：

性能分析 | MySQL Index Condition Pushdown(ICP)_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的访问和各层之间的交互次数







































标签：Index,b0e7,11ed,52540071e14c,0065D5183400BC0CBAC4C9510475790E2BFCEBB8,168,MySQL,
From： https://blog.51cto.com/u_13626676/6049395

相关文章

  • MySQL数据库系统部署使用
    拓扑图:推荐步骤: 在centos01上安装MySQL数据库服务，生成服务器配置文件，添加系统服务优化命令初始化MySQL，设置访问密码登录MySQL数据库 在centos01的MySQL服务器上创建数据......
  • MySQL - B+树
    B+树一个m阶的B+树具有如下几个特征：有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。所有的叶子结点中包含......
  • Incorrect HTTP method for uri [/test_index/mapping/?pretty=true] and method [PUT
    在es中使用PUT/test_indexPUT/test_index/mapping/{"properties":{"name":{"type":"text"},"age":{"type":"integer"}}}......
  • MySQL--binlog2sql 安装及使用
    安装：依赖python3，请提前安装开源地址：https://github.com/danfengcao/binlog2sql$curlhttps://bootstrap.pypa.io/get-pip.py-oget-pip.py #下载安装脚本$sudop......
  • 记一次在Linux-Ubuntu上的mysql8安装
    踩坑网上的信息干货量太少了，都错的了解到centos系和ubuntu系的Linux还是有较大的区别的，命令都有不同，yum和apt，包管理器都不一样新开的Linux服务器跟裸奔一样，啥啥命令都没......
  • MySQL数据库系统部署使用
    推荐步骤: 在centos01上安装MySQL数据库服务，生成服务器配置文件，添加系统服务优化命令初始化MySQL，设置访问密码登录MySQL数据库 在centos01的MySQL服务器上创建数据库，数......
  • Mysql数据库备份恢复
    Mysql数据库备份恢复​推荐步骤：​使用mysqldump对数据库，数据库中表，一个数据库多个表，所有数据库进行备份，模拟数据丢失恢复备份的数据​配置mysql数据库开启二进制日志功能，配......
  • docker 下MySQL主从读写分离配置
    主从同步机制：同步基于耳机子机制，主服务器使用二进制来记录数据库的变动状况，从服务器通过读取和执行日志文件来保存主服务的数据一致首先要保障主从的版本一致或相近1......
  • MySQL 5.7.27 详细下载安装配置教程
    以下MySQL5.7.27详细下载安装配置教程【很早之前也记录过一篇MySQL8.0的安装配置过程，如果是8.0可参考下面的另一篇文章】MySQL的下载安装与Navicat配置（超详细）_乞力马扎......
  • Mysql 的三种 join方式
    mysql只有三种join方式参考：https://www.jianshu.com/p/76c90b03b7bdmysql只有三种join方式，但通过组合可以达到各种效果而已join就是innerjoinleftjoinrigh......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99