?

?

本文出處：http://www.cnblogs.com/wy123/p/6694933.html?

?

第一次通過索引視圖優(yōu)化SQL語句，以及遇到的一些問題，記錄一下。

?

語句分析

最近開發(fā)遞交過來一個查詢統(tǒng)計的SQL，說是性能有問題，原本執(zhí)行需要4-5秒鐘，這個業(yè)務本身對性能要求又比較critical，期望是在1s之內(nèi) 在用盡各種辦法之后（執(zhí)行計劃，統(tǒng)計信息，索引，改寫SQL，臨時表拆分），依然沒有實質(zhì)性的改觀， 在觀察SQL本身的特點之后， 有以下幾個特點 　　1，查詢語句整體為多表join，但是每個表自身的數(shù)據(jù)并不是非常大，百萬級 　　2，查詢結果在主表上一個較大的時間范圍的數(shù)據(jù)進行Count的聚合操作 　　3，幾張表之間除了連接條件，主要是進行了一些比較復雜的邏輯運算（下面截圖可以看到，沒多少IO，CPU時間卻很高） ?? 　　　 不過表的連接方式都是inner join，主要性能點就在于表關聯(lián)之間的Hash join之間的邏輯運算，參考下圖（是執(zhí)行計劃的一部分）

　　通過統(tǒng)計信息發(fā)現(xiàn)，該SQL語句的物理IO并不高,說明索引沒有什么問題，通過索引改善IO可能改善空間很有限 　　時間主要花費在SQL語句的編譯和的Hash join 運算 　　嘗試改寫借助SQL之后（純改寫SQL和借助臨時表拆分語句），發(fā)現(xiàn)依然很難繞過Hash join，主要是表之間的邏輯運算最為耗時 　　最后想到可以先將運算好的的數(shù)據(jù)物理存儲起來，然后改寫查詢的SQL語句完成等價的查詢， ?? 　　避免每次查詢都做復雜的邏輯運算，應該可以有比較大的改善，于是就想到了索引視圖

　　

創(chuàng)建索引視圖改寫SQL

　　在提取出來原始的查詢SQL，創(chuàng)建索引視圖，并在索引視圖上創(chuàng)建unique cluster index和合理的nocluster index 　　通過索引視圖改寫原始的查詢統(tǒng)計SQL語句， 　　改寫后的SQL語句是一個索引視圖替代原始的4張表，與另外一個物理表join，發(fā)現(xiàn)效率上沒有任何改善， 　　觀察改寫后的SQL語句的執(zhí)行計劃，發(fā)現(xiàn)跟原始SQL一樣，并沒有走索引視圖上的索引，或者說是用到索引視圖，一時間覺得好心塞，實在沒招了 　　執(zhí)行計劃依舊是長長的一大段，然后依舊是好幾個Hash Join.參考下圖，執(zhí)行計劃跟本文第一個截圖一模一樣

　　按道理，索引視圖固化結果集，并且根據(jù)情況做了過濾，結果集是原始查詢的一部分而已， 　　用同樣的查詢條件從索引視圖做查詢統(tǒng)計，走索引視圖代價肯定要比原始SQL低，通過強制不展開with(noexpand)提示，證實了這個推斷 　　如下是強制不展開索引視圖的統(tǒng)計信息，可以看到完全達到了預期的1S之內(nèi)

　　

?

　　當然索引視圖也不是沒有代價，在一定程度上犧牲了數(shù)據(jù)寫入的效率和冗余存儲，來換取查詢的效率 　　之前簡單介紹過索引視圖的一些特點 http://www.cnblogs.com/wy123/p/6041122.html

?

索引視圖被展開(expand)的原因分析

　　最關鍵的問題在于，沒有強制不展開索引視圖（with(noexpand)提示）的情況下，為什么沒有走索引視圖呢？

　　這個問題確實郁悶了一陣子，整個半天都在想這個問題，因為索引視圖本身用的就少，出現(xiàn)此問題更是一頭霧水，不過Google一下，還有一大把類似的問題 　　如下是在google的時候查到的，原文是英文，大概意思如下，非直譯。

　　參考鏈接：http://dba.stackexchange.com/questions/26979/what-factors-go-into-an-indexed-views-clustered-index-being-selected/27039#27039

索引視圖的匹配（查詢用索引視圖替代而不走原始的基礎表）是一個相當昂貴的操作，因此優(yōu)化器試圖通過其他方式快速轉(zhuǎn)換（生成執(zhí)行計劃） 如果優(yōu)化器產(chǎn)生了一個相對優(yōu)化的執(zhí)行計劃，就可以盡早結束（不必繼續(xù)生成其他執(zhí)行計劃） 問題就在于：繼續(xù)生成其他執(zhí)行計劃的代價要大于已生成的執(zhí)行計劃的代價 聽起來有點別扭， 之前舉過這么一個例子，比如說是花3秒鐘找到一個相對優(yōu)化的執(zhí)行計劃，這個執(zhí)行計劃完成SQL的執(zhí)行需要2秒鐘 與花10秒鐘的時候找到一個最優(yōu)化的執(zhí)行計劃，盡管這個執(zhí)行計劃完成SQL的執(zhí)行可能只需要0.5秒鐘，雖然后者的執(zhí)行計劃更優(yōu)，但整體代價更大 優(yōu)化器的主要目標是盡快找到一個足夠好的執(zhí)行計劃（而非總是生成最好的執(zhí)行計劃）

使用索引視圖本身并不是一個昂貴的操作， 但是與潛在索引視圖中匹配邏輯查詢樹確實一個代價較大的行為，在查詢涉及的視圖在優(yōu)化器優(yōu)化之前已經(jīng)被展開 因此優(yōu)化器并不知道你的查詢是否未被了索引視圖，他看到的只是展開的查詢樹 這個通俗地講就是： 讓sqlserver知道，一個查詢，可以用索引視圖中的結果等價替代視圖邏輯中原始的基表，是一個代價較大的過程 因為SQL Server根據(jù)原始的基礎表，生成一種執(zhí)行計劃之后，就不去判斷是否可以用索引視圖做等價替代。

?　　當然白皮書里有更詳細的介紹，里面索引視圖相關的一些邏輯實現(xiàn)和分析http://www.cs.cmu.edu/~natassa/courses/15-823/current/papers/vldb00.pdf

?

最后多逼逼一句： 　　上面的白皮書實在看不懂，只是Google出來說詳細信息請看這個參考資料, 　　這里只是從現(xiàn)象去推斷優(yōu)化器在面對索引視圖的一些特性，了解到內(nèi)在機制的一些特點，可能潛在的問題，以及對應的解決辦法

總結

以上是生活随笔為你收集整理的SQL Server 优化---为什么索引视图（物化视图）需要with(noexpand)强制查询提示的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。