曲演雜壇--蛋疼的ROW_NUMBER函數

標籤：des   style   blog   http   color   使用   

使用ROW_NUMBER來分頁幾乎是家喻戶曉的東東了，而且這東西簡單易用，簡直就是程式員居家必備之殺器，然而ROW_NUMBER也不是一招吃遍天下鮮的無敵BUG般存在，最近就遇到幾個小問題，拿出來供大家娛樂下。

---======================================================

問題1：為什麼加WHERE條件就慢，不加反而快？

查詢SQL:

WITH Temp AS(SELECT * ,ROW_NUMBER()OVER(ORDER BY T2.C6 DESC) AS RIDFROM TB001 AS T1INNER JOIN TB002 AS T2ON T1.C1=T2.C1WHERE T1.C2>1000AND T2.C3<99999AND T1.C4=5)SELECT * FROM TempWHERE RID BETWEEN 0 AND 10

開發大哥很激動地問我，對上面類似的的查詢，如果沒有WHERE RID BETWEEN 0 AND 10的話，查詢在1秒內完成，如果有WHERE條件，執行超過30秒未結束，不帶WHERE條件返回300行左右資料，WHERE條件過濾後返回10行資料，返回的資料行長度較小，可以忽略由於返回資料大小對網路和顯示的影響，那問題出在那呢？稍微有點DBA經驗的人都會很快找到問題根源--執行計畫不對。

讓我們換個簡單的SQL來分析下

WITH Temp AS(SELECT * ,ROW_NUMBER()OVER(ORDER BY T1.C1 DESC) AS RIDFROM TB001 AS T1WHERE T1.C2>1000)SELECT * FROM TempWHERE RID BETWEEN 0 AND 10

讓我們揣測下上面查詢如何?，假設在T1.C1有索引IX_C1，在T1.C2上有索引IX_C2。

實現方式1：

A=>針對CTE內部的查詢，先利用索引IX_C2找出滿足條件T1.C2>1000的資料，得到結果集U1

B=>對結果集U1按T1.C1排序,計算出U1中每行RID列的值，得到結果集U2

C=>對結果集U2尋找滿足RID BETWEEN 0 AND 10過濾的行，得到結果集U3

D=>將結果集U3返回

實現方式2：

A=>利用索引IX_C1按ORDER BY T1.C1 DESC來依次訪問T1資料

B=>檢查步驟A得到的行是否滿足T1.C2>1000條件，將滿足條件的結果放入結果集U1中，然後一次遞增RID

C=>檢查步驟B得到的結果集UI，當得到足夠資料行(RID BETWEEN 0 AND 10)後停止步驟A和B

D=>將結果集U1返回

以上兩種方式都能得到正確的返回結果，但是那種更好呢？

對於實現方式1，假設表T1有100W資料，如果滿足T1.C2>1000的行只有20行，那麼使用索引IX_C2快速找出滿足條件的20行資料，然後對這20行資料排序也只會消耗很輕微的CPU資源；但如果滿足T1.C2>1000的行只有99W行,那麼排序就消耗大量CPU資源，從而導致查詢慢。

對於實現方式2，假設表T1有100W資料，按照索引IX_C1 倒序遍曆C1的值，如果遍曆前50行便能尋找到滿足T1.C2>1000的10行資料，那麼查詢可以很快結束，只消耗少量的邏輯讀；但如果需要遍曆前99W資料才能找到滿足T1.C2>1000的10行資料，那麼就會消耗大量的邏輯讀，從而導致查詢慢。

由此，我們不難得出一個結論：沒有絕對正確的執行計畫，只有相對高品質的執行計畫。

--==================================================================

我們知道，在SQL SERVER產生執行計畫時，會根據輸入的參數和統計資訊去預估一些步驟的影響行數和開銷，尋找開銷較小的執行計畫，對於本篇開頭提到的查詢，SQL SERVER很容易受到RID BETWEEN 0 AND 10的誘惑，選擇類似於實現方式2的的執行計畫，而資料分布情況又恰好是針對該方式最壞的情況，就出現了我們遇到的結果，查詢死慢死慢的。

類似的案例還有：

1. 查詢返回資料20行，然後在此查詢的基礎上增加ORDER BY 和TOP(10), 結果執行效率慢了很多，於是就產生了為什麼對20行資料排序取TOP會這麼慢的疑惑？

2. 查詢返回資料20行，在查詢中分別增加SELECT TOP(20)和SELECT TOP(10000),結果SELECT TOP(10000)的比SELECT TOP(20)快很多倍，我遇到的案例有SELECT TOP(10000)在5ms內完成，然後SELECT TOP(1)的十分鐘都沒有結果

 

以上案例都有相同的操作ORDER BY+TOP，ROW_NUMBER本質上也是ORDER BY+TOP，我們知道CPU資源是伺服器資源中最寶貴的資源，而對結果集排序又是一個很耗CPU資源的過程，SQL SERVER為節省CPU資源選擇了一個“它”認為比較合適的執行計畫，結果悲劇了。

--===============================================

針對哪位開發大哥的問題，我嘗試了各種寫法，在不動用暫存資料表和索引提示的情況下，我還真搞不定這SQL，於是我來了個邪惡小招數：

WITH Temp AS(SELECT * ,ROW_NUMBER()OVER(ORDER BY T2.C6 DESC) AS RIDFROM TB001 AS T1INNER JOIN TB002 AS T2ON T1.C1=T2.C1WHERE T1.C2>1000AND T2.C3<99999AND T1.C4=5)SELECT * FROM TempWHERE RID+0 BETWEEN 0 AND 10

學術派們要開始叫囂了，這種RID+0 BETWEEN 0 AND 10寫法不科學啊，效率低下，初級程式員不懂SQL寫的爛SQL啊。。。

使用RID+0來騙過查詢最佳化工具，讓“它”無法估算出BETWEEN 0 AND 10需要返回的行數，這樣“它”只能老老實實地“先”做CET內部的查詢.

PS： 我騙得過查詢最佳化工具，騙不過開發大哥，他一直認為這個寫法太BT，問了其他的DBA好幾次，就是不採納我的建議，悲催啊。

--==============================================

一個小建議：

不要見到類似WHEERE C1+10>20這種的就叫囂不好，就喊著不能走索引的口號，看看情境再說麼，萬一C1上就壓根沒有索引呢？

--===========================================================================

ROW_NUMBER在實現分頁行的確很好用，但是也不是所有情境都適用，這是一個真實的例子

一個查詢只有兩個參數@P1和@P2，代表取第@P1行到第@P2行之間的數

當@P1=0 AND @P2=1000時，消耗是這樣的：

表 ‘XXXDetail‘。掃描計數 186，邏輯讀取 4922 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。表 ‘XXX‘。掃描計數 1，邏輯讀取 809 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。SQL Server 執行時間:   CPU 時間 = 0 毫秒，佔用時間 = 73 毫秒。

當@P1=7241284 AND @P2=7240285時，消耗是這樣的：

表 ‘XXXDetail‘。掃描計數 1468817，邏輯讀取 35838994 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。表 ‘XXX‘。掃描計數 1，邏輯讀取 5983509 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 45926 毫秒，佔用時間 = 56816 毫秒。

真有份這麼多頁的，無語吧！！！
既然無語，我就不多做解釋，說多就是眼淚，看看就好。

--=============================================================================

打完收工，妹子附上