专栏首页老男孩成长之路SQL 中判断条件的先后顺序,会引起索引失效么?

SQL 中判断条件的先后顺序,会引起索引失效么?

在群里看到一个基础题,有关索引的使用。

题目就在这里,有的朋友说选B,有的选C,有的说题目不严谨,还有的说没答案,都是错误的。

讨论了很久,有两个共性的问题,值得拿出来说下:

  • a=1 and b=1 和 b=1 and a=1 会有效利用 idx(b,a) 吗?
  • b=1 还会利用索引 idx(a,b)吗?

实践出真知,我就试着上机操作下。

create database factory ;

use factory 
go 

create table dbo.workflow ( flowid int, flowamount int, flowcount int )

go 

先回答第一个问题,判断条件的顺序会影响索引使用吗

这儿模拟题目中的 idx(b,a) 索引结构

create index idx_amt_id on dbo.workflow(flowamount,flowid)

模拟 a=1 and b=1 的查询

select * from dbo.workflow 
where flowid = 1 and flowamount = 1 

模拟 b=1 and a=1 的查询

select * from dbo.workflow 
where flowamount = 1 and flowid = 1 

可以看到,当表新建,还没有数据时,优化器根本不会去判断用不用索引,而是直接全表扫描。反正就一个数据页。

当我们加点数据时,再看看反应:

这里不得不再提下 tally table 的用法,实在看不下去利用循环来生成测试数据的方法

DECLARE @BEGIN DATETIME = '2010-01-01'

                ,@END DATETIME = '2017-10-30'

DECLARE @INC INT ;

SELECT @INC = DATEDIFF(DAY,@BEGIN,@END)



; WITH 

    L0 AS ( 

            SELECT * FROM (VALUES(1),(2),(3)) AS T(C) )

,    L1 AS (

            SELECT a.C,b.C AS BC FROM L0 AS a cross join L0 AS b )

,    L2 AS (

            SELECT a.C,b.C AS BC FROM L1 AS a cross join L1 AS b )

,    L3 AS (

            SELECT a.C,b.C AS BC FROM L2 AS a cross join L2 AS b )

,    L4 AS (

            SELECT a.C,b.C AS BC FROM L3 AS a cross join L3 AS b )

,    L5 AS (

            SELECT a.C,b.C AS BC FROM L4 AS a cross join L4 AS b )

insert into  dbo.workflow (flowid,flowamount,flowcount)            

SELECT TOP 50000 RNK , RNK * 10, RNK + 20 

FROM 

(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RNK 

FROM L5

) M 

此时表里有5万条数据,再看上面两条查询的执行计划:

这儿模拟题目中的 idx(b,a) 索引结构

create index idx_amt_id on dbo.workflow(flowamount,flowid)

模拟 a=1 and b=1 的查询

select * from dbo.workflow 
where flowid = 1 and flowamount = 1 

模拟 b=1 and a=1 的查询

select * from dbo.workflow 
where flowamount = 1 and flowid = 1 

很明显,都会走索引 idx(b,a) 这种模式,与 b 在前和 a 在前无关。优化器可以优化这部分表达式的重组。

但,是不是所有条件表达式都没有先后顺序要求呢?肯定不是

只有在相等条件判断时,先后顺序不重要,一旦有表达式用于非等判断,顺序就很重要了,如下:

select * from dbo.workflow 
where flowamount > 39 and flowid = 1 


select * from dbo.workflow 
where flowid = 1 and flowamount > 39  

这里优化器提示(绿色字体部分),建立一个相等判断条件的索引在前,非等判断字段在后的索引 (flowid,flowamount)。所以本质上,索引结构中字段先后不受制于查询中相等判断条件表达式字段的顺序,而受制于非等条件判断表达式。即非等判断字段(flowamount>39)需要放在相等判断字段(flowid=1)的后面。

create index idx_id_amtr on dbo.workflow(flowid,flowamount)


select * from dbo.workflow 
where flowamount > 39 and flowid = 1 


select * from dbo.workflow 
where flowid = 1 and flowamount > 39  

再看两者的执行计划:

这里就走了我们刚才新建的索引 idx_id_amtr

第二个问题,b=1 还会利用索引 idx(a,b)吗

在上面的示例中,建立 index(flowamount,flowid) 的索引,那么对应到要解决的问题,便是 where flowid = 1 会走 index(flowamount,flowid)的索引吗?

select * from dbo.workflow 
where  flowid = 1 

由此可见 b=1 是不会利用索引 idx(a,b) 了。

注意,或许 oracle, mysql, pg, 等其他数据库会有不同,大家可以尝试实际操作下,再一起来讨论。各自优化器的算法不同,优化略微有些诧异。不必过于纠结。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 中判断条件的先后顺序,会引起索引失效么?

    题目就在这里,有的朋友说选B,有的选C,有的说题目不严谨,还有的说没答案,都是错误的。

    Lenis
  • mysql┃多个角度说明sql优化,让你吊打面试官!

    mysql的优化是我们经常都会提到的一个话题,也是重中之重,在很多大厂中会有专门的DBA来做这件事情,甚至更过分的是连应届生的招聘岗位要求上都写了需要懂...

    moon聊技术
  • 2018-07-20 oracle优化:避免全表扫描

    例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

    Albert陈凯
  • 如何通过索引让 SQL 查询效率最大化

    运⾏时间为0.699s,你能看到查询效率还是⽐较低的。当我们对user_id字段创建索引之后,运⾏时间为 0.047s,不到原来查询时间的1/10。

    王小明_HIT
  • 19 条效率至少提高 3 倍的 MySQL 技巧

    MySQL 对于 IN 做了相应的优化,即将 IN 中的常量全部存储在一个数组里面,而且这个数组是排好序的。

    良月柒
  • 史上最全存储引擎、索引使用及SQL优化的实践

    整个MySQL Server由以下组成 : Connection Pool :连接池组件 Management Services & Utilities...

    海仔
  • Java高频面试题- 每日三连问?【Day7】 — 数据库篇

    优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。

    浩说编程
  • Oracle SQL性能优化40条,值得收藏

    语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划”

    数据和云
  • 面试MySQL,看这篇文章就够了!

    当游戏前端开发能力不断提升后,有的小伙伴已经开始不满足了,将魔爪伸向后端开发,立志做一个全栈游戏开发程序员!分享一篇MySQL的好文,加油吧!程序员!

    张晓衡
  • 19条mysql优化mark下

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select...

    V站CEO-西顾
  • Mysql索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    仙士可
  • Mysql索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    北溟有鱼QAQ
  • Mysql合理建立索引,索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    宣言言言
  • 项目中至少提高3倍的19条MySQL优化

    本文我们来谈谈项目中常用的MySQL优化方法,巧用这19条技巧,至少提高3倍效率,具体如下:

    格姗知识圈
  • MYSQL锁学习笔记

    MYSQL是在大小公司中使用率极高的开源的关系型数据库,以其良好的易用性和在分布式场景下的高性能而著称,也是所有新手在数据库入门时的产品首选。最近因为听了公司的...

    眯眯眼的猫头鹰
  • 项目中让 MySQL 速度提升 3 倍的 19 种优化方式

    本文总结了 19 条关于 MySQL 的优化方案,本文的优化方案都是基于 “ MySQL-索引-BTree 类型 ” 。希望对你有帮助,码字不易,如果觉得有用,...

    JiekeXu之路
  • 巧用这19条MySQL优化,效率至少提高3倍

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select...

    JAVA葵花宝典
  • 必须收藏的19条MySQL优化,效率至少提高3倍!!!

    type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

    用户5224393
  • 19条MySQL优化,效率至少提高3倍

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select...

    Rookie

扫码关注云+社区

领取腾讯云代金券