首页 新闻 搜索 专区 学院

最左匹配原则

0
悬赏园豆:20 [已关闭问题] 关闭于 2020-11-24 15:24

这里说 age 的筛选会没有走索引,但是我在自己尝试的时候发现会用到,这是为什么


而且索引也用到了索引下推。。。。

萌新J的主页 萌新J | 菜鸟二级 | 园豆:202
提问于:2020-11-24 11:39
< >
分享
所有回答(2)
0

“这里说 age 的筛选会没有走索引”

age肯定会走索引,你查询name,sex时才进行索引下推到存储引擎进行过滤,其实就是减少了数据到MySQL Server的数量,达到加快查询的作用。

架构师修炼之道 | 园豆:251 (菜鸟二级) | 2020-12-10 21:08

您好,关于索引下推这里我还是没看懂,这里不是没有用到索引下推么,为什么会显示使用了索引下推,如果关闭了索引下推,显示的 key_len 也会是71 啊

支持(0) 反对(0) 萌新J | 园豆:202 (菜鸟二级) | 2020-12-10 21:16

@萌新J: 确认有关闭吗?你是怎么配置关闭的?

支持(0) 反对(0) 架构师修炼之道 | 园豆:251 (菜鸟二级) | 2020-12-11 19:28

@架构师修炼之道: set optimizer_switch='index_condition_pushdown=off'; 我都试过好几次了,设置完还 show variables like 'optimizer_switch' 查看了

支持(0) 反对(0) 萌新J | 园豆:202 (菜鸟二级) | 2020-12-11 19:42
0

正好最近也在研究索引和索引下推,相互讨论下。可以参考下这边文章中的where条件提取规则:https://www.cnblogs.com/mengxinJ/p/14045520.html

一、这条sql语句select * from dept where age=12 and name like 'a%',虽然age条件在前,name在后,看似不满足最左侧原则,但这条语句在执行的过程中Mysql优化器会将该条语句优化为select * from dept where name like 'a%' and age=12,还是会走联合索引。
二、怎么走的索引?为什么会出现index using condition索引下推,这就要用where条件的提取规则解释了,我们在执行一条sql时,会将 Where 条件拆分为 Index Key(First Key & Last Key)、Index Filter 与 Table Filter,具体怎么拆分呢,先看下面的定义,然后在以你的sql为例,咱们拆一次。

index key:
用于确定 SQL 查询在索引中的连续范围(起始点 + 终止点)的查询条件,被称之为Index Key;由于一个范围,至少包含一个起始条件与一个终止条件,因此 Index Key 也被拆分为 Index First Key 和 Index Last Key,分别用于定位索引查找的起始点以终止点。
Index First Key:
  用于确定索引查询范围的起始点;提取规则:从索引的第一个键值开始,检查其在 where 条件中是否存在,若存在并且条件是 =、>=(like 也算>=),则将对应的条件加入Index First Key之中,继续读取索引的下一个键值,使用同样的提取规则;若存在并且条件是 >,则将对应的条件加入 Index First Key 中,同时终止 Index First Key 的提取;若不存在,同样终止 Index First Key 的提取。
 Index Last Key:
  用于确定索引查询范围的终止点,与 Index First Key 正好相反;提取规则:从索引的第一个键值开始,检查其在 where 条件中是否存在,若存在并且条件是 =、<=,则将对应条件加入到 Index Last Key 中,继续提取索引的下一个键值,使用同样的提取规则;若存在并且条件是 < ,则将条件加入到 Index Last Key 中,同时终止提取;若不存在,同样终止Index Last Key的提取

Index Filter:
Index Filter 用于索引范围确定后,确定 SQL 中还有哪些条件可以使用索引来过滤;提取规则:从索引列的第一列开始,检查其在 where 条件中是否存在,若存在并且 where 条件仅为 =,则跳过第一列继续检查索引下一列,下一索引列采取与索引第一列同样的提取规则;若 where 条件为 >=、>、<、<= 其中的几种,则跳过索引第一列,将其余 where 条件中索引相关列全部加入到 Index Filter 之中;若索引第一列的 where 条件包含 =、>=、>、<、<= 之外的条件,则将此条件以及其余 where 条件中索引相关列全部加入到 Index Filter 之中;若第一列不包含查询条件,则将所有索引相关条件均加入到 Index Filter之中

Table Filter:
这个就比较简单了,where 中不能被索引过滤的条件都归为此中;提取规则:所有不属于索引列的查询条件,均归为 Table Filter 之中

看到这儿是不是有点晕了,没事儿,拿你的sql为例子,咱拆一次你就懂了。
sql:select * from dept where age=12 and name like 'a%',
被sql优化器优化后:select * from dept where name like 'a%' and age=12

先提取index first key,没记住规则上去看下,按照联合索引(name,age)顺序,先看name在不在where里,ok在,然后看name的条件是否是>或>=(这里like等价于>=),ok条件符合规则,那么将name like 'a%'加入index first key,然后继续按照此规则读取下一列索引age,age的条件是=,也符合规则,再把age=12也加入index first key,到此index first key提取完成,里面是name like 'a%',age=12

下一步提取index last key,规则不说了没记住的话上去看下,还是按照联合索引顺序,先看name,发现name是>=不符合规则,跳过继续看age,age是=,符合规则,将age加入到index last key,到此index last key也提取完成,里面是age=12

下一步提取index filter(索引下推的关键),按照联合索引顺序,先看name,发现name是>=,触发规则(若 where 条件为 >=、>、<、<= 其中的几种,则跳过索引第一列,将其余 where 条件中索引相关列全部加入到 Index Filter 之中),将跳过name这一列,将age=12加入index filter,到此index filter提取完毕

最后一步提取table filter,由于这个sql没有用到非索引列,所以table filter为空

好了全部都提取完了,那这些提取出来的东西怎么用啊?这就要扯到mysql的服务层和引擎层的职责了,咱们上面所说的where提取工作是在mysql服务层完成的,在执行sql时,server层会将这些where条件推送至引擎层,引擎层根据这些条件遍历索引树,这里就有两个分支了,一个是5.6版本以前,一个是5.6之后,先说5.6以前没有索引下推的时候,是不区分index filter和table filter的,只会将index key推送至引擎层,引擎层根据index key遍历索引树确认范围后,逐条回表,每查一条返回至server层,然后再用index filter和table filter的条件进行过滤,而5.6加入索引下推后,会将index key和index filter一同推送至引擎层,引擎层首先根据index key遍历索引树确认范围,然后再用index filter对范围进行过滤,最终将过滤后的结果集逐一回表,回表后返回至server层,如果存在table filter,再用table filter进行过滤。
根据上述的规则,你这个sql语句index key:name like 'a%',age = 12,index filter:age=12,所以既会用到索引也会用到索引下推(我认为这里index filter其实没起作用,因为index key已经锁住所有的范围了),这就是为什么你的查询计划既会出现使用到索引也会出现使用到索引下推了。
以上是我个人愚见,其实按照这些规则,你去试一些sql的时候会发生与规则冲突的情况,比如就你这个sql,index key已经将结果集完全锁定了,为什么还会提示用到索引下推,我也在慢慢摸索,相互讨论吧。
菜鸡coding | 园豆:190 (初学一级) | 2020-12-15 13:48

我就是他 ╮(╯▽╰)╭ 这个问题是我当时学的时候提出来的,当时对最左匹配原则的范围查询以及索引下推有疑问,再加上网上的这个帖子与我实验的有出入,才发起的

支持(0) 反对(0) 萌新J | 园豆:202 (菜鸟二级) | 2020-12-15 14:02

@萌新J: 。。。。。。。我靠我靠我靠,要不说咱俩有缘分呢,我真没注意到

支持(0) 反对(0) 菜鸡coding | 园豆:190 (初学一级) | 2020-12-15 14:38
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册