MySQL索引部分生效原理是什么

MySQL 版本 5.6,Innodb 引擎
如果数据库表是按照 (a,b,c) 建的联合索引

select * from myTest where a=3  and c=7;//a使用到了,c没有
select * from myTest where a=3  and b>7 and c=3;//a,b使用到了,c没有。
select * from myTest where a=3  and b>=7 and c=3;//abc都使用到了

上面 3 句 sql 都可以使用到联合索引,但是第一个是 a 使用到了,c 没有
第二个是 a,b 使用到了,c 没有。
第三个是 abc 都使用到了。
我就是不太理解为什么索引部分字段失效的原理?有没有大佬简单说一下上面的原因呢?
我知道:首先按照 a 进行排序,然后在相同 a 值的情况下按照 b 进行排序,最后在相同 a 和 b 值的情况下按照 c 进行排序
但是我想知道为什么部分字段没有用到索引,原理是什么?

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
最佳答案

1 和 3 =》 联合索引的数据也是有按顺序存储进去的,如果想在联合索引中使用尽可能多的列,索条件中的各个列必须是联合索引中从最左边连续的列。

2 和 3 =》 记录都是按照索引列的值从小到大的顺序排好序的,如果对多个列同时进行范围查找的话,只有对索引最左边的那个列进行范围查找的时候才能用到 B + 树索引


至于 3 可以拆解成
a=3 and b>7 and c=3

a=3 and b=7 and c=3 (这个是能用到 abc 的)


推荐书籍:MySQL 是怎样运行的:从根儿上理解 MySQL - 第六章 和 第七章
会详细讲解这块

1年前 评论
讨论数量: 9

联合索引(a,b,c)相当于 a,ab,abc 这 3 个索引 -- 所以第一个你应该能明白了 至于后面 2 个的为什么一个用了 ab,一个用了 abc 我就不清楚了

1年前 评论

跟上面的兄弟说的一样,联合索引 (a,b,c) 相当于 a, ab, abc 这 3 个索引,所以:

第一个跳过了 b,只能匹配 a。

第二个 b > 7 是范围条件,对于范围条件查询,MySQL 无法再使用范围列后面的其他索引列,所以无法使用 abc ,匹配 ab。

第三个我不确定,感觉上应该是 MySQL 的优化器把查询条件拆分了,where 条件变成了 a = 3 and b = 7 and c= 3 and b > 7,前三个条件可以匹配到索引 abc。

1年前 评论
bluememory (楼主) 1年前
Imuyu 1年前
bluememory (楼主) 1年前

看起来就是等于的时候会用到索引

1年前 评论
九霄道长

索引构建上 首先按照 a 进行排序,然后在相同 a 值的情况下按照 b 进行排序,最后在相同 a 和 b 值的情况下按照 c 进行排序 具体可以看看 b+ 树的构建

不太了解的话可以先看看二叉搜索树

二叉搜索树,AVL 树 - VisuAlgo

这里可以动态插入,删除,还有动画.

1年前 评论

1 和 3 =》 联合索引的数据也是有按顺序存储进去的,如果想在联合索引中使用尽可能多的列,索条件中的各个列必须是联合索引中从最左边连续的列。

2 和 3 =》 记录都是按照索引列的值从小到大的顺序排好序的,如果对多个列同时进行范围查找的话,只有对索引最左边的那个列进行范围查找的时候才能用到 B + 树索引


至于 3 可以拆解成
a=3 and b>7 and c=3

a=3 and b=7 and c=3 (这个是能用到 abc 的)


推荐书籍:MySQL 是怎样运行的:从根儿上理解 MySQL - 第六章 和 第七章
会详细讲解这块

1年前 评论

第二个 为什么遇到 > 后面字段就使用不到索引? 因为 b>7 筛选出的结果范围太大,可能有 8 - 无穷尽,那么在 b 筛选后这个范围再去找符合 c 条件的数值,就和一行行扫描没啥区别了,也就是索引失效了吧。

我的理解是这样的。

1年前 评论