在今天的文章里，我想給你展示下，當(dāng)你想對(duì)特定查詢創(chuàng)建索引設(shè)計(jì)時(shí)，如何把你的工作和思考過(guò)程傳達(dá)給查詢優(yōu)化器。下面就一起來(lái)探討一下吧！

有問(wèn)題的查詢
我們來(lái)看下列查詢：

 DECLARE @i INT = 999
 SELECT
   SalesOrderID, 
   SalesOrderDetailID,
   CarrierTrackingNumber, 
   OrderQty, 
   LineTotal
 FROM Sales.SalesOrderDetail
 WHERE ProductID < @i
 ORDER BY CarrierTrackingNumber
 GO

如你所見(jiàn)，這里用了一個(gè)本地變量與一個(gè)不等于謂語(yǔ)來(lái)從Sales.SalesOrderDetail表來(lái)獲取一些記錄。當(dāng)你執(zhí)行那個(gè)查詢，看它的執(zhí)行計(jì)劃時(shí)，你會(huì)發(fā)現(xiàn)它有一些嚴(yán)重的問(wèn)題：

• SQL Server需要掃描Sales.SalesOrderDetail表的整個(gè)非聚集索引，因?yàn)闆](méi)有支持的非聚集索引。對(duì)這個(gè)掃描，查詢需要1382個(gè)邏輯讀，運(yùn)行時(shí)間近800毫秒。
• 查詢優(yōu)化器在查詢計(jì)劃里引入了篩選器（Filter）運(yùn)算符，它進(jìn)行逐行比較用來(lái)檢查符合的行（ProductID < @i）
• 因?yàn)镺RDER BY CarrierTrackingNumber，在執(zhí)行計(jì)劃里一個(gè)排序（Sort）運(yùn)算符被引入。
• 排序運(yùn)算符蔓延到了TempDb，因?yàn)椴徽_的基數(shù)計(jì)算（Cardinality Estimation）。用了帶了本地變量與不等于謂語(yǔ)的組合，SQL Server從表的基數(shù)硬碼估計(jì)30%的行。在我們的情況里估計(jì)行數(shù)是36395（121317 * 30%）。實(shí)際上查詢返回120621行，這意味這排序（Sort）運(yùn)算符必須蔓延到TempDb，因?yàn)檎?qǐng)求的內(nèi)存授予太小了。

現(xiàn)在我問(wèn)你——你能改善這個(gè)查詢么？你的建議是什么？休息下，想個(gè)幾分鐘。不修改查詢本身，你如何改善這個(gè)查詢？

我們來(lái)調(diào)試查詢！
當(dāng)然，我們要做索引相關(guān)的調(diào)整來(lái)改善。沒(méi)有支持的非聚集索引，那只能是查詢優(yōu)化器唯一可以使用計(jì)劃來(lái)運(yùn)行我們的查詢。但對(duì)這個(gè)指定查詢，什么是好的非聚集索引呢？一般來(lái)說(shuō)，我通過(guò)看搜索謂語(yǔ)來(lái)考慮可能的非聚集速印。在我們的例子里，搜索謂語(yǔ)如下：

WHERE ProductID < @i

我們請(qǐng)求在ProductID列過(guò)濾的行。因此我們想在那個(gè)列創(chuàng)建支持的非聚集索引。我們建立索引：

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
 GO

在非聚集索引創(chuàng)建后，我們需要驗(yàn)證下改變，因此我們?cè)俅螆?zhí)行剛才的查詢代碼。結(jié)果如何捏？查詢優(yōu)化器并沒(méi)有使用我們剛創(chuàng)建的非聚集索引！我們?cè)谒阉髦^語(yǔ)上創(chuàng)建了支持的非聚集索引，查詢優(yōu)化器沒(méi)有引用它？通常人們對(duì)此就無(wú)轍了。其實(shí)我們可以提示查詢優(yōu)化器來(lái)使用非聚集索引，來(lái)更好的理解“為什么”查詢優(yōu)化器沒(méi)有自動(dòng)選擇索引：

 DECLARE @i INT = 999
 
 SELECT
  SalesOrderID, 
  SalesOrderDetailID,
  CarrierTrackingNumber, 
  OrderQty, 
  LineTotal
FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
WHERE ProductID < @i
 ORDER BY CarrierTrackingNumber
 GO

當(dāng)你現(xiàn)在看執(zhí)行計(jì)劃時(shí)，你會(huì)看到下列的野性——一個(gè)并行計(jì)劃：

查詢花費(fèi)了370109個(gè)邏輯讀！運(yùn)行時(shí)間基本和剛才的一樣。這里到底發(fā)生了什么？當(dāng)你仔細(xì)看執(zhí)行計(jì)劃，你會(huì)發(fā)現(xiàn)查詢優(yōu)化器引入了書(shū)簽查找，因?yàn)閯偛艅?chuàng)建的非聚集索引，對(duì)于查詢來(lái)說(shuō)，不是一個(gè)覆蓋非聚集索引。查詢?cè)竭^(guò)了所謂的臨界點(diǎn)（Tipping Point），因?yàn)槲覀冇卯?dāng)前的搜索謂語(yǔ)來(lái)獲得幾乎所有行。因此用非聚集索引和書(shū)簽查找來(lái)組合沒(méi)有意義。

不去想為什么查詢優(yōu)化器不選擇剛才創(chuàng)建的非聚集索引，我們已經(jīng)把自己的思路表達(dá)給了查詢優(yōu)化器本身，通過(guò)查詢提示進(jìn)行了詢問(wèn)了查詢優(yōu)化器，為什么非聚集索引沒(méi)被自動(dòng)選擇。如我剛開(kāi)始說(shuō)的：我不想考慮太多。

使用非聚集索引解決這個(gè)問(wèn)題，在非聚集索引的葉子層，我們必須對(duì)從SELECT列表的請(qǐng)求的額外列進(jìn)行包含。你可以再次看下書(shū)簽查找來(lái)看下在葉子層哪些列當(dāng)前丟失：

• CarrierTrackingNumber
• OrderQty
• UnitPrice
• UnitDiscountPrice

我們重建那個(gè)非聚集索引：

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
 WITH
(
 DROP_EXISTING = ON
 )
GO

我們已經(jīng)做出了另1個(gè)改變，因此我們可以重新運(yùn)行了查詢來(lái)驗(yàn)證下。但是這次我們不加查詢提示，因?yàn)楝F(xiàn)在查詢優(yōu)化器會(huì)自動(dòng)選擇非聚集索引。結(jié)果如何捏？當(dāng)你看執(zhí)行計(jì)劃時(shí)，索引現(xiàn)在已被選擇。

SQL Server現(xiàn)在在非聚集索引上進(jìn)行了查找操作，但在執(zhí)行計(jì)劃里我們還有排序（Sort）運(yùn)算符。因?yàn)榛鶖?shù)計(jì)算30%的硬編碼，排序（Sort）還是要蔓延到TempDb。偶滴神！我們的邏輯讀已經(jīng)降到了757，但運(yùn)行時(shí)間還是近800毫秒。你現(xiàn)在應(yīng)該怎么做？

現(xiàn)在我們可以嘗試在非聚集索引的導(dǎo)航結(jié)構(gòu)直接包含CarrierTrackingNumber列。這是SQL Server進(jìn)行排序運(yùn)算符的列。當(dāng)我們?cè)诜蔷奂饕苯蛹恿诉@列（作為主鍵），我們就物理排序了那列，因此排序（Sort）運(yùn)算符應(yīng)該會(huì)消失。作為積極的副作用，也不會(huì)蔓延到TempDb。在執(zhí)行計(jì)劃里，現(xiàn)在也沒(méi)有運(yùn)算符關(guān)心錯(cuò)誤的基數(shù)計(jì)算。因此我們嘗試那個(gè)假設(shè)，再次重建非聚集索引：

 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
 WITH
(
   DROP_EXISTING = ON
 )
GO

從索引定義可以看到，現(xiàn)在我們已經(jīng)對(duì)CarrierTrackingNumber和ProductID列的數(shù)據(jù)物理預(yù)排序。當(dāng)你再次重新執(zhí)行查詢，在你查看執(zhí)行計(jì)劃時(shí)，你會(huì)看到排序（Sort）運(yùn)算符已經(jīng)消失，SQL Server掃描了非聚集索引的整個(gè)葉子層（使用剩余謂語(yǔ)（residual predicate）作為搜索謂語(yǔ)）。

這個(gè)執(zhí)行計(jì)劃并不壞！我們只需要763個(gè)邏輯讀，現(xiàn)在的運(yùn)行時(shí)間已經(jīng)降至600毫秒。和剛才的相比已經(jīng)有25%的改善！但是：查詢優(yōu)化器建議我們一個(gè)更好的非聚集索引，通過(guò)缺少索引建議（Missing Index Recommendations）！暫且相信下，我們創(chuàng)建建議的非聚集索引：

CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?]
ON [Sales].[SalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])
GO

當(dāng)你現(xiàn)在重新執(zhí)行最初的查詢，你會(huì)發(fā)現(xiàn)令人驚訝的事情：查詢優(yōu)化器使用“我們”剛才創(chuàng)建的非聚集索引，缺少索引建議已經(jīng)消失！

你剛剛創(chuàng)建了SQL Server從不使用的索引——除了INSERT，UPDATE和DELETE語(yǔ)句，SQL Server都要去維護(hù)你的非聚集索引。對(duì)于你的數(shù)據(jù)庫(kù)，你剛創(chuàng)建了“單純”浪費(fèi)空間的索引。當(dāng)另一方面，你已經(jīng)通過(guò)消除丟失索引建議，滿足了查詢優(yōu)化器。但這不是目的：目的是創(chuàng)建會(huì)被再次使用的索引。

結(jié)論：永不相信查詢優(yōu)化器！

小結(jié)

今天的文章有點(diǎn)爭(zhēng)議性，但我想你向你展示下，但你在創(chuàng)建索引時(shí)，查詢優(yōu)化器如何幫助你，還有查詢優(yōu)化器如何愚弄你。因此做出小的調(diào)整，就立即運(yùn)行你的查詢，驗(yàn)證改變非常重要。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助。

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。