请选择 进入手机版 | 继续访问电脑版

[MySql] 一篇文章读懂什么是MySQL索引下推(ICP)

[复制链接]
查看148 | 回复28 | 2021-9-13 00:44:41 | 显示全部楼层 |阅读模式
目次

一、简介

ICP(Index Condition Pushdown)是在MySQL 5.6版本上推出的查询优化策略,把本来由Server层做的索引条件检查下推给存储引擎层来做,以降低回表和访问存储引擎的次数,进步 查询服从 。

二、原理

为了明确 ICP是怎样 工作的,我们先相识 下没有使用 ICP的环境 下,MySQL是怎样 查询的:

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

使用 ICP的环境 下,查询过程如下:

  • 读取索引记录(不是完备 的行记录);
  • 判断 WHERE条件部分能否 用索引中的列来做检查,条件不满足 ,则处理下一行索引记录;
  • 条件满足 ,使用 索引中的主键去定位并读取完备 的行记录(就是所谓的回表);
  • 存储引擎把记录交给Server层,Server层检测该记录是否满足 WHERE条件的别的 部分。

三、实践

先创建一张表,并插入记录

  1. CREATE TABLE user (
  2. id int(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
  3. name varchar(32) COMMENT "姓名",
  4. city varchar(32) COMMENT "城市",
  5. age int(11) COMMENT "年龄",
  6. primary key(id),
  7. key idx_name_city(name, city)
  8. )engine=InnoDB default charset=utf8;
  9. insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25), ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65), ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);
复制代码

查看一下表记录

  1. mysql> select * from user;
  2. +----+----------+-----------+------+
  3. | id | name | city | age |
  4. +----+----------+-----------+------+
  5. | 1 | ZhaoDa | BeiJing | 20 |
  6. | 2 | QianEr | ShangHai | 21 |
  7. | 3 | SunSan | GuanZhou | 22 |
  8. | 4 | LiSi | ShenZhen | 24 |
  9. | 5 | ZhouWu | NingBo | 25 |
  10. | 6 | WuLiu | HangZhou | 26 |
  11. | 7 | ZhengQi | NanNing | 27 |
  12. | 8 | WangBa | YinChuan | 28 |
  13. | 9 | LiSi | TianJin | 29 |
  14. | 10 | ZhangSan | NanJing | 30 |
  15. | 11 | CuiShi | ZhengZhou | 65 |
  16. | 12 | LiSi | KunMing | 29 |
  17. | 13 | LiSi | ZhengZhou | 30 |
  18. +----+----------+-----------+------+
  19. 13 rows in set (0.00 sec)
复制代码

注意 ,这张表里创建了团结 索引(name, city),假设我们想查询如下语句:

  1. select * from user where name="LiSi" and city like "%Z%" and age > 25;
复制代码

3.1 不使用 索引下推

在不使用 索引下推的环境 下,根据团结 索引“最左匹配”原则,只有name列能用到索引,city列由于是含糊 匹配,是不能用到索引的,此时的实行 过程是如许 的:

  1. 存储引擎根据(name, city)团结 索引,找到name值为LiSi的记录,共4条记录;
  2. 然后根据这4条记录中的id值,逐一举行 回表扫描,去聚簇索引中取出完备 的行记录,并把这些记录返回给Server层;
  3. Server层吸收 到这些记录,并按条件name="LiSi" and city like "%Z%" and age > 25举行 过滤,终极 留下("LiSi", "ZhengZhou", 30)这条记录。

画张图看一下:

一篇文章读懂什么是MySQL索引下推(ICP)

未使用 使用 索引条件下推

3.2 使用 索引下推

使用 索引下推的环境 下,实行 过程是如许 的:

  • 存储引擎根据(name, city)团结 索引,找到name='LiSi'的记录,共4条;
  • 由于团结 索引中包含city列,存储引擎直接在团结 索引中按city like "%Z%"举行 过滤,过滤后剩下2条记录;
  • 根据过滤后的记录的id值,逐一举行 回表扫描,去聚簇索引中取出完备 的行记录,并把这些记录返回给Server层;
  • Server层根据WHERE语句的别的 条件age > 25,再次对行记录举行 筛选,终极 只留下("LiSi", "ZhengZhou", 30)这条记录。

画张图看一下:

一篇文章读懂什么是MySQL索引下推(ICP)

使用 索引条件下推

别的 ,从实行 计划里也可以看到使用 了索引下推(Extra里表现 Using index condition)

  1. mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25;
  2. +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
  5. | 1 | SIMPLE | user | NULL | ref | idx_name_city | idx_name_city | 99 | const | 4 | 7.69 | Using index condition; Using where |
  6. +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
  7. 1 row in set, 1 warning (0.00 sec)
复制代码

四、使用 条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法;
  • 只能用于InnoDB和 MyISAM存储引擎及其分区表;
  • 对InnoDB存储引擎来说,索引下推只实用 于二级索引(也叫辅助索引);
  1. tip:索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于InnoDB的聚簇索引来说,完整的行记录已经加载到缓存区了,索引下推也就没什么意义了。
复制代码
  • 引用了子查询的条件不能下推;
  • 引用了存储函数的条件不能下推,由于 存储引擎无法调用存储函数。

五、相干 体系 参数

索引条件下推默认是开启的,可以使用 体系 参数optimizer_switch来控制器是否开启。

查看默认状态:

  1. mysql> select @@optimizer_switch\G;
  2. *************************** 1. row ***************************
  3. @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
  4. 1 row in set (0.00 sec)
复制代码

切换状态:

  1. set optimizer_switch="index_condition_pushdown=off";
  2. set optimizer_switch="index_condition_pushdown=on";
复制代码

总结

到此这篇关于什么是MySQL索引下推(ICP)的文章就先容 到这了,更多相干 MySQL索引下推(ICP)内容请搜刮 脚本之家从前 的文章或继续欣赏 下面的相干 文章盼望 大家以后多多支持脚本之家!


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

avatar 甜心猫舍 | 2021-9-19 23:00:34 | 显示全部楼层
态度决定一切,不错!
回复

使用道具 举报

avatar 想不到918 | 2021-9-30 08:32:51 | 显示全部楼层
勤奋灌水,天天向上!
回复

使用道具 举报

avatar 信森好帝 | 2021-10-1 03:54:10 | 显示全部楼层
admin楼主会死的很有节奏的!
回复

使用道具 举报

avatar 倪丹军 | 2021-10-6 22:56:49 | 显示全部楼层
论坛的帖子越来越有深度了!
回复

使用道具 举报

avatar 山东大鲤鱼 | 2021-10-6 23:45:29 | 显示全部楼层
支持楼上的!
回复

使用道具 举报

avatar 都市夜归人PLA | 2021-10-9 09:03:46 | 显示全部楼层
这个帖子好无聊啊!
回复

使用道具 举报

avatar 恋爱的棉被缀 | 2021-10-10 17:00:49 | 显示全部楼层
看帖回帖一条路!
回复

使用道具 举报

avatar 螃蟹357 | 2021-10-10 17:30:30 | 显示全部楼层
admin楼主的帖子提神醒脑啊!
回复

使用道具 举报

avatar hedeafmz28 | 2021-10-10 17:43:39 | 显示全部楼层
强,我和我的小伙伴们都惊呆了!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则