SQL優化案例—— RowNumber分頁 - zc_0101－IT工程師數位筆記本

文章出處

將業務語句翻譯成SQL語句不僅是一門技術，還是一門藝術。

下面拿我們程序開發工程師最常用的ROW_NUMBER()分頁作為一個典型案例來說明。

先來看看我們最常見的分頁的樣子：
WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY (A.CreateTime ) AS OrderNo ,
        Table_A.ID ,    --主鍵
        Table_A.其它字段
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT * FROM CTE
WHERE   OrderNo BETWEEN 1 AND 50;
的確，這樣的寫法很符合我們的思維邏輯，并且我們在RecID上建立非聚集索引，那么它的效率看上去也是不錯的。當然根據這條SQL，最佳索引實踐應該是:
CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
INCLUDE(Table_A.其它字段)
但是，這真的是最佳的了嗎？當SQL的Where條件變多，Table_A.其它字段變得越來越多，OVER()子句中的OrderBy字段越來越多或者變成Order By ColumnA/ColumnB這樣的計算表達式，這條語句變得越來越不堪重負，最終性能問題凸現出來，另外，作為DBA，我們總是盡量維持索引的簡單性、可重用度，而不想建立成為某個語句專用的索引。舉例來說，在Include中，我們總不能把Table_A.其它字段中的所有字段都放進去吧，個數少還行，如果遇上幾十個字段或者有大容量字符字段，維護成本將大大增加，那將是我們不愿意看到的。
這個時候就要求我們看看是否能對語句做出一些優化了。

在上面的SQL中，我們看它的執行計劃，我已經建立了索引，該索引并未Include SELECT列表中的其它字段：
CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)

根據上圖的執行計劃，可以看到，WHERE條件走的是我剛剛建立的索引，下面的鍵查找與其并行，我們先不討論該執行計劃的具體細節，下面我們來設想幾個問題：

在WHERE條件簡單，并且索引合適，統計信息正確的前提下，SQL Server可以很容易獲得那50行，并且回到聚集索引中找到屬于它的其它字段的數據，這是SQL Server的智能編譯的結果，也是我們希望看到的返回方式。

但是，在WHERE條件較為復雜，多個WHERE條件均為范圍字段或者狀態字段時，執行計劃也許并沒有我們想象的那么智能了，比如它可能采用這樣的方式：

當SQL Server無法準確的取出你要的那些行時，那么它便會取回全部的行數后，再去聚集索引中找回屬于它的其它字段的數據，當where條件可以返回幾十萬數據時，你可以想象它的效率有多低，它會仍然使用上文中類似的執行計劃，這顯然不是我們希望看到的。

我們想看到的是什么？

1、根據WHERE條件和排序規則，先取出那50條數據所屬的主鍵。

SELECT ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
Table_A.ID --主鍵

INTO #1
FROM Table_A WITH ( NOLOCK )
WHERE RecID = 220051

2、利用上個步驟中返回的主鍵，去原始表取回這50條記錄的其它字段數據。

SELECT B.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN #1 B ON A.ID=B.ID
WHERE   B.OrderNo BETWEEN 1 AND 50;

那么，上面兩個步驟合在一起：


WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
        Table_A.ID    --主鍵
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT CTE.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN CTE ON A.ID=CTE.ID
WHERE   CTE.OrderNo BETWEEN 1 AND 50;

很好，現在我們再來看一下這個SQL的執行計劃：