关于mysql:万答20索引下推如何进行数据过滤

48次阅读

共计 5254 个字符，预计需要花费 14 分钟才能阅读完成。

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

试验环境

GreatSQL 8.0.25 InnoDB

1. 索引下推介绍

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

2. 实用条件

  • 1. 当须要拜访全表记录时，ICP 用于 range、ref、eq_ref 和 ref_or_null 拜访办法。
  • 2.ICP 能够用于 InnoDB 和 MyISAM 表，包含分区 InnoDB 和 MyISAM 表。
  • 3. 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的指标是缩小整行记录读取的次数，从而缩小 I / O 操作。对于 InnoDB 汇集索引，残缺的记录曾经被读取到 InnoDB 缓冲区，在这种状况下应用 ICP 不会缩小 I /O。
  • 4. 虚构列上创立的二级索引，不反对 ICP。
  • 5. 应用子查问的 SQL 不反对 ICP。
  • 6. 调用存储过程的 SQL 不反对 ICP，因为存储引擎无奈调用位于 MySQL Server 中的存储过程。
  • 7. 触发器 不反对 ICP。

3. 如何启用

  • ICP 默认是开启的，能够通过下列命令进行敞开、启用、查看
# 敞开 ICP
SET optimizer_switch = 'index_condition_pushdown=off'; 
# 开启 ICP
SET optimizer_switch = 'index_condition_pushdown=on';
# 查看 ICP 以后状态
show VARIABLES like '%optimizer_switch%'

4.ICP 如何工作

不应用 ICP 优化时的查问步骤

  • 1. 获取下一行，首先读取索引信息，而后依据索引将整行数据读取进去。
  • 2. 而后通过 where 条件判断以后数据是否符合条件，合乎返回数据。

应用 ICP 优化时的查问步骤

  • 1. 获取下一行的索引信息。
  • 2. 查看索引中存储的列信息是否合乎索引条件，如果合乎将整行数据读取进去，如果不合乎跳过读取下一行。
  • 3. 用残余的判断条件，判断此行数据是否符合要求，符合要求返回数据

5. 试验测试

表构造如下

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增 id',
  `uid` int NOT NULL COMMENT '学号',
  `age` int NOT NULL COMMENT '年龄',
  `name` char(32) NOT NULL COMMENT '姓名',
  `sex` char(4) NOT NULL COMMENT '性别',
  `grade` int NOT NULL COMMENT '年级',
  `class` varchar(32) NOT NULL COMMENT '班级',
  `major` varchar(64) NOT NULL COMMENT '业余',
  PRIMARY KEY (`id`),
  KEY `idx_anm` (`age`,`name`,`major`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

# 启用 ICP
[root@GreatSQL][test]>explain select * from student where age=16 and name like '陈 %' and major='软件工程';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_anm       | idx_anm | 390     | NULL |    1 |    33.33 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 不启用 ICP
[root@GreatSQL][test]>explain select /*+ no_icp (student) */ * from student where age=16 and name like '陈 %' and major='软件工程';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | range | idx_anm       | idx_anm | 390     | NULL |    1 |    33.33 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

启用 ICP 解析进去的 Extra 是 Using index condition，不启用 ICP 解析进去的 Extra 是 Using where

其余查问后果根本一样, 看不出有效率差异，能够通过开启 profiling 进行查看

[root@GreatSQL][test]>set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@GreatSQL][test]>select * from student where age=16 and name like '陈 %' and major='软件工程';
+----+--------+-----+--------+-----+-------+-------+--------------+
| id | uid    | age | name   | sex | grade | class | major        |
+----+--------+-----+--------+-----+-------+-------+--------------+
|  1 | 100001 |  16 | 陈红   | 男  |     4 | 3     | 软件工程     |
+----+--------+-----+--------+-----+-------+-------+--------------+
1 row in set (0.00 sec)

(Tue Jan  4 15:51:50 2022)[root@GreatSQL][test]>select /*+ no_icp (student) */ * from student where age=16 and name like '陈 %' and major='软件工程';
+----+--------+-----+--------+-----+-------+-------+--------------+
| id | uid    | age | name   | sex | grade | class | major        |
+----+--------+-----+--------+-----+-------+-------+--------------+
|  1 | 100001 |  16 | 陈红   | 男  |     4 | 3     | 软件工程     |
+----+--------+-----+--------+-----+-------+-------+--------------+
1 row in set (0.00 sec)

[root@GreatSQL][test]>show profiles\G;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00043725
   Query: select * from student where age=16 and name like '陈 %' and major='软件工程'
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00048500
   Query: select /*+ no_icp (student) */ * from student where age=16 and name like '陈 %' and major='软件工程'
2 rows in set, 1 warning (0.00 sec)

ERROR:
No query specified

应用了 ICP 的 Duration 要比没有应用的工夫稍短一些，屡次测试效率比照后果都一样，从测试来看，应用 ICP 优化的查问效率会好一些。

6. 查问流程

没有开启 ICP

1. 依据 最左准则 先找到 age=16 的记录，而后回表，依据主键找出满足记录的行。

2. 而后找出所有合乎 like '陈 %' 的行记录，而后再依据步骤 1 查出来的数据，依据主键过滤符合条件的记录

3. 而后找出所有合乎 major='软件工程' 再依据步骤 2 查出所有符合条件的记录

4. 步骤 1 查问过程，每个合乎 age=16 的记录都要先进行回表操作。

开启 ICP

1. 依据 最左准则 先找到 age=16 的记录。

2. 查看索引过滤掉不合乎 like '陈 %' 的数据

3. 查看索引过滤掉不合乎 major='软件工程' 的数据

4. 步骤 1 查问过程，先不进行回表操作，先通过索引找出合乎 2、3 条件的状况，如何不合乎则间接进行下一个步骤查问，故回表次数会少一些。

7.ICP 图解

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

图 1：没有启用 ICP 查问过程

图 2：启用 ICP 查问过程

阐明：图 2 的几个 X 是因为在索引层就进行数据过滤了，故不须要再进行回表。

8. 更多内容查看官网

  • https://dev.mysql.com/doc/ref…

Enjoy GreatSQL :)

文章举荐：

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12，MGR 整个集群挂掉后，如何能力主动选主，不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』：《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15，都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili：
https://space.bilibili.com/13…

微信 &QQ 群：
可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

正文完
mysql
发表至： mysql
2022-01-18
 0
关于mysql:MySQL-中的重做日志和二进制日志
关于mysql:MySQL-57和80性能测试比拼结局有点意外
关于mysql:特性介绍-MySQL测试框架-MTR-系列教程四语法篇
关于mysql:MySQL-Workbench-使用教程1-安装