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

[MySql] 五分钟带你搞懂MySQL索引下推

[复制链接]
查看43 | 回复13 | 2021-9-13 01:13:10 | 显示全部楼层 |阅读模式
目次

假如 你在口试 中,听到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索引下推

可以看到只回表了一次。

除此之外我们还可以看一下实验 计划,看到

  1. 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. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
复制代码

索引下推利用 条件

  • 只能用于
    1. range
    复制代码
    1.  ref
    复制代码
    1.  eq_ref
    复制代码
    1. ref_or_null
    复制代码
    访问方法;
  • 只能用于
    1. InnoDB
    复制代码
    和 
    1. MyISAM
    复制代码
    存储引擎及其分区表;
    1. 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";
复制代码

总结

本篇文章就到这里了,渴望 可以或许 给你带来帮助,也渴望 您可以或许 多多关注脚本之家的更多内容!


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

本帖子中包含更多资源

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

x
回复

使用道具 举报

avatar 123457753 | 2021-9-18 17:23:11 | 显示全部楼层
宇宙第一贴诞生了!
回复

使用道具 举报

avatar 心素如菊棺 | 2021-9-19 06:48:39 | 显示全部楼层
有节操!
回复

使用道具 举报

avatar 风男人1984 | 2021-9-19 11:39:00 | 显示全部楼层
admin楼主的病已经好了百分之六十二了!
回复

使用道具 举报

avatar 刘晨阳1 | 2021-9-20 12:16:41 | 显示全部楼层
支持一下,下面的保持队形!
回复

使用道具 举报

avatar yhzdmb342 | 2021-9-26 18:51:31 | 显示全部楼层
admin楼主,我告诉你一个你不知道的的秘密,有一个牛逼的网站,他卖的服务器是永久的,我们的网站用 服务器都是在这家买的,你可以去试试。访问地址:http://fwq.mxswl.com
回复

使用道具 举报

avatar 绚丽源自寂寞悍 | 2021-10-3 08:42:42 | 显示全部楼层
顶顶更健康!
回复

使用道具 举报

avatar 晴空万里659 | 2021-10-4 12:03:15 | 显示全部楼层
admin楼主的文笔不错!
回复

使用道具 举报

avatar 保佑缎 | 2021-10-4 13:21:59 | 显示全部楼层
楼上的能详细介绍一下么?
回复

使用道具 举报

哥回复的不是帖子,是寂寞!
回复

使用道具 举报

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

本版积分规则