這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)如何調(diào)優(yōu)SQL Server查詢,文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述,閱讀完這篇文章希望大家可以有所收獲。
創(chuàng)新互聯(lián)公司網(wǎng)站建設(shè)提供從項(xiàng)目策劃、軟件開發(fā),軟件安全維護(hù)、網(wǎng)站優(yōu)化(SEO)、網(wǎng)站分析、效果評(píng)估等整套的建站服務(wù),主營(yíng)業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站建設(shè)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè),成都App制作以傳統(tǒng)方式定制建設(shè)網(wǎng)站,并提供域名空間備案等一條龍服務(wù),秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。創(chuàng)新互聯(lián)公司深信只要達(dá)到每一位用戶的要求,就會(huì)得到認(rèn)可,從而選擇與我們長(zhǎng)期合作。這樣,我們也可以走得更遠(yuǎn)!在今天的文章里,我想給你展示下,當(dāng)你想對(duì)特定查詢創(chuàng)建索引設(shè)計(jì)時(shí),如何把你的工作和思考過程傳達(dá)給查詢優(yōu)化器。下面就一起來探討一下吧!
有問題的查詢我們來看下列查詢:
DECLARE @i INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @i ORDER BY CarrierTrackingNumber GO
如你所見,這里用了一個(gè)本地變量與一個(gè)不等于謂語來從Sales.SalesOrderDetail表來獲取一些記錄。當(dāng)你執(zhí)行那個(gè)查詢,看它的執(zhí)行計(jì)劃時(shí),你會(huì)發(fā)現(xiàn)它有一些嚴(yán)重的問題:
SQL Server需要掃描Sales.SalesOrderDetail表的整個(gè)非聚集索引,因?yàn)闆]有支持的非聚集索引。對(duì)這個(gè)掃描,查詢需要1382個(gè)邏輯讀,運(yùn)行時(shí)間近800毫秒。 查詢優(yōu)化器在查詢計(jì)劃里引入了篩選器(Filter)運(yùn)算符,它進(jìn)行逐行比較用來檢查符合的行(ProductID < @i) 因?yàn)镺RDER BY CarrierTrackingNumber,在執(zhí)行計(jì)劃里一個(gè)排序(Sort)運(yùn)算符被引入。 排序運(yùn)算符蔓延到了TempDb,因?yàn)椴徽_的基數(shù)計(jì)算(Cardinality Estimation)。用了帶了本地變量與不等于謂語的組合,SQL Server從表的基數(shù)硬碼估計(jì)30%的行。在我們的情況里估計(jì)行數(shù)是36395(121317 * 30%)。實(shí)際上查詢返回120621行,這意味這排序(Sort)運(yùn)算符必須蔓延到TempDb,因?yàn)檎?qǐng)求的內(nèi)存授予太小了。
現(xiàn)在我問你——你能改善這個(gè)查詢么?你的建議是什么?休息下,想個(gè)幾分鐘。不修改查詢本身,你如何改善這個(gè)查詢?
我們來調(diào)試查詢!當(dāng)然,我們要做索引相關(guān)的調(diào)整來改善。沒有支持的非聚集索引,那只能是查詢優(yōu)化器可以使用計(jì)劃來運(yùn)行我們的查詢。但對(duì)這個(gè)指定查詢,什么是好的非聚集索引呢?一般來說,我通過看搜索謂語來考慮可能的非聚集速印。在我們的例子里,搜索謂語如下:
WHERE ProductID < @i
我們請(qǐng)求在ProductID列過濾的行。因此我們想在那個(gè)列創(chuàng)建支持的非聚集索引。我們建立索引:
CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID) GO
在非聚集索引創(chuàng)建后,我們需要驗(yàn)證下改變,因此我們?cè)俅螆?zhí)行剛才的查詢代碼。結(jié)果如何捏?查詢優(yōu)化器并沒有使用我們剛創(chuàng)建的非聚集索引!我們?cè)谒阉髦^語上創(chuàng)建了支持的非聚集索引,查詢優(yōu)化器沒有引用它?通常人們對(duì)此就無轍了。其實(shí)我們可以提示查詢優(yōu)化器來使用非聚集索引,來更好的理解“為什么”查詢優(yōu)化器沒有自動(dòng)選擇索引:
DECLARE @i INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotalFROM 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)化器引入了書簽查找,因?yàn)閯偛艅?chuàng)建的非聚集索引,對(duì)于查詢來說,不是一個(gè)覆蓋非聚集索引。查詢?cè)竭^了所謂的臨界點(diǎn)(Tipping Point),因?yàn)槲覀冇卯?dāng)前的搜索謂語來獲得幾乎所有行。因此用非聚集索引和書簽查找來組合沒有意義。
不去想為什么查詢優(yōu)化器不選擇剛才創(chuàng)建的非聚集索引,我們已經(jīng)把自己的思路表達(dá)給了查詢優(yōu)化器本身,通過查詢提示進(jìn)行了詢問了查詢優(yōu)化器,為什么非聚集索引沒被自動(dòng)選擇。如我剛開始說的:我不想考慮太多。
使用非聚集索引解決這個(gè)問題,在非聚集索引的葉子層,我們必須對(duì)從SELECT列表的請(qǐng)求的額外列進(jìn)行包含。你可以再次看下書簽查找來看下在葉子層哪些列當(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)行了查詢來驗(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)在也沒有運(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è)葉子層(使用剩余謂語(residual predicate)作為搜索謂語)。
這個(gè)執(zhí)行計(jì)劃并不壞!我們只需要763個(gè)邏輯讀,現(xiàn)在的運(yùn)行時(shí)間已經(jīng)降至600毫秒。和剛才的相比已經(jīng)有25%的改善!但是:查詢優(yōu)化器建議我們一個(gè)更好的非聚集索引,通過缺少索引建議(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語句,SQL Server都要去維護(hù)你的非聚集索引。對(duì)于你的數(shù)據(jù)庫(kù),你剛創(chuàng)建了“單純”浪費(fèi)空間的索引。當(dāng)另一方面,你已經(jīng)通過消除丟失索引建議,滿足了查詢優(yōu)化器。但這不是目的:目的是創(chuàng)建會(huì)被再次使用的索引。
結(jié)論:永不相信查詢優(yōu)化器!
上述就是小編為大家分享的如何調(diào)優(yōu)SQL Server查詢了,如果剛好有類似的疑惑,不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí),歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
本文名稱:如何調(diào)優(yōu)SQLServer查詢-創(chuàng)新互聯(lián)
瀏覽地址:http://jinyejixie.com/article32/dpsisc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供軟件開發(fā)、電子商務(wù)、App設(shè)計(jì)、做網(wǎng)站、定制開發(fā)、網(wǎng)站導(dǎo)航
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容