- 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 默认是开启的，能够通过下列命令进行敞开、启用、查看
# 敞开ICPSET optimizer_switch = 'index_condition_pushdown=off'; # 开启ICPSET 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: 1Duration: 0.00043725 Query: select * from student where age=16 and name like '陈%' and major='软件工程'*************************** 2. row ***************************Query_ID: 2Duration: 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 公布！