SQL 中判斷條件的先后順序，會引起索引失效么？

點擊藍色“有關(guān)SQL”關(guān)注我喲

在群里看到一個基礎(chǔ)題，有關(guān)索引的使用。

題目就在這里，有的朋友說選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)?索引結(jié)構(gòu)

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?

可以看到，當(dāng)表新建，還沒有數(shù)據(jù)時，優(yōu)化器根本不會去判斷用不用索引，而是直接全表掃描。反正就一個數(shù)據(jù)頁。

當(dāng)我們加點數(shù)據(jù)時，再看看反應(yīng)：

這里不得不再提下 tally table 的用法，實在看不下去利用循環(huán)來生成測試數(shù)據(jù)的方法

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萬條數(shù)據(jù)，再看上面兩條查詢的執(zhí)行計劃：

這兒模擬題目中的?idx(b,a)?索引結(jié)構(gòu)

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 在前無關(guān)。優(yōu)化器可以優(yōu)化這部分表達式的重組。

但，是不是所有條件表達式都沒有先后順序要求呢？肯定不是

只有在相等條件判斷時，先后順序不重要，一旦有表達式用于非等判斷，順序就很重要了，如下：

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


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

這里優(yōu)化器提示（綠色字體部分），建立一個相等判斷條件的索引在前，非等判斷字段在后的索引 (flowid,flowamount)。所以本質(zhì)上，索引結(jié)構(gòu)中字段先后不受制于查詢中相等判斷條件表達式字段的順序，而受制于非等條件判斷表達式。即非等判斷字段（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??

再看兩者的執(zhí)行計劃：

這里就走了我們剛才新建的索引 idx_id_amtr

第二個問題，b=1 還會利用索引 idx(a,b)嗎？

在上面的示例中，建立 index(flowamount,flowid) 的索引，那么對應(yīng)到要解決的問題，便是 where flowid = 1 會走 index(flowamount,flowid)的索引嗎？

select?*?from?dbo.workflow?
where??flowid?=?1?

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