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

[MySql] 五分钟让你快速弄懂MySQL索引下推

  [复制链接]
查看225 | 回复53 | 2021-9-13 00:31:39 | 显示全部楼层 |阅读模式
目次

前言

假如 你在口试 中,听到MySQL5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。

什么是索引下推

索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,进步 查询服从 。

索引下推优化的原理

我们先简单相识 一下MySQL大概的架构:

五分钟让你快速弄懂MySQL索引下推

MySQL服务层负责SQL语法剖析 、天生 实行 计划等,并调用存储引擎层去实行 数据的存储和检索。

索引下推的下推着实 就是指将部分上层(服务层)负责的变乱 ,交给了下层(引擎层)行止 理。

我们来详细 看一下,在没有使用 ICP的环境 下,MySQL的查询:

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

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

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

索引下推的详细 实践

理论比较抽象,我们来上一个实践。

使用 一张用户表tuser,表里创建团结 索引(name, age)。

五分钟让你快速弄懂MySQL索引下推

假如 如今 有一个需求:检索出表中名字第一个字是张,而且年事 是10岁的全部 效 户。那么,SQL语句是这么写的:

  1. select * from tuser where name like '张%' and age=10;
复制代码

假如你相识 索引最左匹配原则,那么就知道这个语句在搜刮 索引树的时间 ,只能用

复制代码
,找到的第一个满足 条件的记录id为1。

五分钟让你快速弄懂MySQL索引下推

那接下来的步骤是什么呢?

没有使用 ICP

在MySQL 5.6之前,存储引擎根据通过团结 索引找到

  1. name likelike '张%'
复制代码
的主键id(1、4),逐一举行 回表扫描,去聚簇索引找到完备 的行记录,server层再对数据根据
  1. age=10进行筛选
复制代码

我们看一下表示 图:

五分钟让你快速弄懂MySQL索引下推

可以看到必要 回表两次,把我们团结 索引的另一个字段age浪费了。

使用 ICP

而MySQL 5.6 以后, 存储引擎根据(name,age)团结 索引,找到

  1. name likelike '张%'
复制代码
,由于团结 索引中包含age列,以是 存储引擎直接再团结 索引里按照
  1. age=10
复制代码
过滤。按照过滤后的数据再逐一 举行 回表扫描。

我们看一下表示 图:

五分钟让你快速弄懂MySQL索引下推

可以看到只回表了一次。

除此之外我们还可以看一下实行 计划,看到Extra一列里

  1. Using index condition
复制代码
,这就是用到了索引下推。

  1. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  2. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  3. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  4. | 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition |
  5. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
复制代码

索引下推使用 条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法;
  • 只能用于InnoDB和 MyISAM存储引擎及其分区表;
  • 对InnoDB存储引擎来说,索引下推只实用 于二级索引(也叫辅助索引);

索引下推的目的 是为了减少回表次数,也就是要减少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";
复制代码

参考:

[1].《 MySQL技术黑幕 InnoDB存储引擎》

[2]. 《MySQL实战45讲》

[3]. MySQL索引下推(ICP)简单明白 及例子

[4]. 一文读懂什么是MySQL索引下推(ICP)

总结

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


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

本帖子中包含更多资源

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

x
回复

使用道具 举报

avatar 36号1 | 2021-9-19 12:23:20 | 显示全部楼层
十分赞同admin楼主!
回复

使用道具 举报

avatar 0Zombies0 | 2021-9-21 16:33:07 | 显示全部楼层
admin楼主今年多大了?
回复

使用道具 举报

avatar 计儿坏 | 2021-9-29 23:51:28 | 显示全部楼层
很经典,收藏了!
回复

使用道具 举报

avatar 123457044 | 2021-9-30 05:24:05 | 显示全部楼层
没人理我,好伤心啊!
回复

使用道具 举报

avatar 天下无敌522 | 2021-10-1 22:00:46 | 显示全部楼层
感觉不错!
回复

使用道具 举报

avatar 休闲时光8882017 | 2021-10-8 14:59:10 | 显示全部楼层
看了这么多帖子,第一次看到这么高质量内容!
回复

使用道具 举报

avatar 阿甘cx1982 | 2021-10-9 08:39:50 | 显示全部楼层
大神就是大神,这么经典!
回复

使用道具 举报

avatar 粘瓜莱 | 2021-10-10 20:54:18 | 显示全部楼层
最近精神病院在打折,admin楼主去看看吧?
回复

使用道具 举报

avatar 娣玲 | 2021-10-10 22:07:25 | 显示全部楼层
好无聊啊!
回复

使用道具 举报

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

本版积分规则