我欠 SQL Server 優(yōu)化器一個道歉

前天的文章，寫了 SQL Server 的 Predicate Pushdown.

SQL Server 的弱雞 Predicate Pushdown

舉例想說明的是，子查詢能不能接收外層的條件判斷。

文中的例子沒有舉好，誤判了 SQL Server 不能進行 Predicate Pushdown, 實際上它的優(yōu)化器總是進行謂詞下推。

下面在原來的基礎(chǔ)上，再做一遍實驗與說明。

上例子，以 AdventureWorks2016 數(shù)據(jù)庫為背景，完成一段帶子查詢的SQL:

??SELECT?Prod.Name?AS?ProductName,?Sales.ModifiedDate
??FROM?Production.Product?Prod??
??INNER?JOIN?(
???SELECT?TOP?10?ProductID,?ModifiedDate,OrderQty
???FROM?Sales.SalesOrderDetail?Detail?
???ORDER?BY?ProductID,ModifiedDate?DESC?
??)?Sales?
?on?Sales.ProductID?=?Prod.ProductID?
?WHERE?Sales.ModifiedDate?BETWEEN?'2011-01-01'?AND?'2011-10-01'?AND?Sales.OrderQty=?2?

例子中，涉及到的兩張表，結(jié)構(gòu)與索引如下：

--表結(jié)構(gòu)

CREATE?TABLE?[Production].[Product](
?[ProductID]?[int]?IDENTITY(1,1)?NOT?NULL,
?[Name]?[dbo].[Name]?NOT?NULL,
?[ProductNumber]?[nvarchar](25)?NOT?NULL,
?...
?[DiscontinuedDate]?[datetime]?NULL,
?[rowguid]?[uniqueidentifier]?ROWGUIDCOL??NOT?NULL,
?[ModifiedDate]?[datetime]?NOT?NULL,
?CONSTRAINT?[PK_Product_ProductID]?PRIMARY?KEY?CLUSTERED?
(
?[ProductID]?ASC
)?
)?ON?[PRIMARY]

GO?



CREATE?TABLE?[Sales].[SalesOrderDetail](
?[SalesOrderID]?[int]?NOT?NULL,
?[SalesOrderDetailID]?[int]?IDENTITY(1,1)?NOT?NULL,
?[CarrierTrackingNumber]?[nvarchar](25)?NULL,
?[OrderQty]?[smallint]?NOT?NULL,
?[ProductID]?[int]?NOT?NULL,
?[SpecialOfferID]?[int]?NOT?NULL,
?[UnitPrice]?[money]?NOT?NULL,
?[UnitPriceDiscount]?[money]?NOT?NULL,
?[LineTotal]??AS?(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
?[rowguid]?[uniqueidentifier]?ROWGUIDCOL??NOT?NULL,
?[ModifiedDate]?[datetime]?NOT?NULL,
?CONSTRAINT?[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]?PRIMARY?KEY?CLUSTERED?
(
?[SalesOrderID]?ASC,
?[SalesOrderDetailID]?ASC
)WITH?(PAD_INDEX?=?OFF,?STATISTICS_NORECOMPUTE?=?OFF,?IGNORE_DUP_KEY?=?OFF,?ALLOW_ROW_LOCKS?=?ON,?ALLOW_PAGE_LOCKS?=?ON)?ON?[PRIMARY]
)?ON?[PRIMARY]
GO


--索引：

ALTER?TABLE?[Sales].[SalesOrderDetail]?ADD??CONSTRAINT?[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]?PRIMARY?KEY?CLUSTERED?
(
?[SalesOrderID]?ASC,
?[SalesOrderDetailID]?ASC
)?
GO

CREATE?NONCLUSTERED?INDEX?[IDX_MOD_QTY_PROD_1]?ON?[Sales].[SalesOrderDetail]
(
?[ModifiedDate]?ASC
)
INCLUDE?(?[OrderQty],?[ProductID])?
?
?
GO

?

這段 SQL 里，子查詢有了 Top N 這樣的邏輯，外層明明有條件篩選，也是不能下推到子查詢的. 因為這 Top ?N 就是要依據(jù) ModifiedDate 來判斷的。而外層的 ModifiedDate 的條件篩選，正好與之沖突。

從它的執(zhí)行計劃，可以看到，filter這一層已經(jīng)放到子查詢后面（見圖左邊）；子查詢使用了 index scan，沒有做任何的條件篩選(Predicate).

同樣，衍生下子查詢：

?
??SELECT?Prod.Name?AS?ProductName,?Sales.ModifiedDate
??FROM?Production.Product?Prod??
??INNER?JOIN?(
???SELECT?TOP?10?ProductID,?ModifiedDate,OrderQty
???FROM?Sales.SalesOrderDetail?Detail?
???WHERE?Detail.ModifiedDate?BETWEEN?'2011-01-01'?AND?'2011-10-01'??
???ORDER?BY?ProductID,ModifiedDate?DESC?
??)?Sales?
?on?Sales.ProductID?=?Prod.ProductID?
?WHERE??Sales.OrderQty=?2?

當我們留 OrderQty = 2 在外層查詢，那么它也是不能被下推到子查詢：

而什么樣的條件下，外層的條件篩選，能夠下推到子查詢呢？

?SELECT?Prod.Name?AS?ProductName,?Sales.ModifiedDate
?FROM?Production.Product?Prod?
?INNER?JOIN?(
???SELECT?ProductID,ModifiedDate,OrderQty,ROW_NUMBER()OVER(PARTITION?BY?ProductID,OrderQty?ORDER?BY?ModifiedDate?DESC)?AS?RNK
???FROM?Sales.SalesOrderDetail?Detail?
???)?Sales?
??ON?Sales.ProductID?=?Prod.ProductID?
WHERE?Sales.ModifiedDate?BETWEEN?'2011-01-01'?AND?'2011-10-01'??
?and?Sales.OrderQty?=?2?

像這樣的，子查詢沒有篩檢數(shù)據(jù)，就可以。

稍稍要注意的是，seek predicate 與 predicate 區(qū)別。

Seek Predicate 是索引訪問方式，這里的索引，以ModifiedDate作為鍵。OrderQty, ProductID放在葉子節(jié)點。

Predicate 是篩選條件， OrderQty 不能作為 SARG 鍵，所以只做篩選。

SARG: Search Argument-able

仔細研究 Predicate,? 跟在子查詢外，就是 Filter ，說明沒有被下推(Pushdown); 而用在子查詢內(nèi)，在索引上做了條件篩選，說明被下推了。

再來個更復(fù)雜的下推：

?SELECT?Prod.Name?AS?ProductName,?Sales.ModifiedDate
?FROM?Production.Product?Prod?
?INNER?JOIN?(

???
??SELECT?ProductID,ModifiedDate,OrderQty?AS?Quantity,ROW_NUMBER()OVER(PARTITION?BY?ProductID,OrderQty?ORDER?BY?ModifiedDate?DESC)?AS?RNK
??FROM?Sales.SalesOrderDetail?Detail?
???

??UNION?ALL?

??SELECT?*?
??FROM?(
???SELECT?ProductID,ModifiedDate,OrderQty?AS?Quantity,ROW_NUMBER()OVER(PARTITION?BY?ProductID,OrderQty?ORDER?BY?ModifiedDate?ASC)?AS?RNK
???FROM?Sales.SalesOrderDetail?Detail?
??)BTM2?
??WHERE?BTM2.RNK<=2




???)?Sales?
??ON?Sales.ProductID?=?Prod.ProductID?
WHERE?Sales.ModifiedDate?BETWEEN?'2011-01-01'?AND?'2011-10-01'??
?and?Sales.Quantity?=?2?

針對子查詢上半部分：

SELECT?ProductID,ModifiedDate,OrderQty?AS?Quantity,ROW_NUMBER()OVER(PARTITION?BY?ProductID,OrderQty?ORDER?BY?ModifiedDate?DESC)?AS?RNK
??FROM?Sales.SalesOrderDetail?Detail?

優(yōu)化器做了Predicate 下推，是可以理解的。

但優(yōu)化器對下半部分，也做了下推，但沒有全推，而是只推了 Quantity =2. 雖然子查詢的列名換了，但依舊還是被優(yōu)化器識別到了.

但 ModifiedDate 就不能被下推了，只能跟在子查詢后面做 Filter.

嗯，SQL Server Predicate Pushdown 很智能，也很強大。