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, 等其他数据库会有不同，大家可以尝试实际操作下，再一起来讨论。各自优化器的算法不同，优化略微有些诧异。不必过于纠结。