SQL Server的分頁優化及Row_Number()分頁存在的問題

jopen 9年前發布 | 21K 次閱讀 SQL Server 數據庫服務器

最近有項目反應，在服務器CPU使用較高的時候，我們的事件查詢頁面非常的慢，查詢幾條記錄竟然要4分鐘甚至更長，而且在翻第二頁的時候也是要這么多的時間，這肯定是不能接受的，也是讓現場用SQLServerProfiler把語句抓取了上來。

用ROW_NUMBER()進行分頁

我們看看現場抓上來的分頁語句：

select top 20 a.*,ag.Name as AgentServerName,,d.Name as MgrObjTypeName,l.UserName as userName 
from eventlog as a 
    left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
    left join addrnode as c on b.AddrId=c.Id 
    left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
    left join eventdir as e on a.EventBm=e.Bm 
    left join agentserver as ag on a.AgentBm=ag.AgentBm 
    left join loginUser as l on a.cfmoper=l.loginGuid 
where a.OrderNo not in  (
    select top 0 OrderNo  
    from eventlog  as a 
        left join mgrobj as b on a.MgrObjId=b.Id 
        left join addrnode as c on b.AddrId=c.Id  
    where 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' and b.AddrId in ('02109000',……,'02109002') order by AlarmTime desc ) and 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' and b.AddrId in ('02109000',……,'02109002') order by AlarmTime DESC

這是典型的使用兩次top來進行分頁的寫法，原理是：先查出pageSize*(pageIndex-1)(T1)的記錄數，然后再Top出PageSize條不在T1中的記錄，就是當前頁的記錄。這種查詢效率不高主要是使用了not in。參考我之前文章《程序猿是如何解決SQLServer占CPU100%的》提到的：“對于不使用SARG運算符的表達式，索引是沒有用的”。

那么改為使用ROW_NUMBER分頁：

WITH cte AS(
    select a.*,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userName,b.AddrId
            ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
        from eventlog as a WITH(FORCESEEK) 
            left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
            left join addrnode as c on b.AddrId=c.Id 
            left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
            left join eventdir as e on a.EventBm=e.Bm 
            left join agentserver As ag on a.AgentBm=ag.AgentBm 
            left join loginUser as l on a.cfmoper=l.loginGuid 
        where a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId in ('02109000',……,'02109002') ) SELECT * FROM cte WHERE RowNo BETWEEN 1 AND 20;

執行時間從14秒提升到5秒，這說明Row_Number分頁還是比較高效的，而且這種寫法比top top分頁優雅很多。

“欺騙”查詢引擎讓查詢按你的期望去查詢

但是為什么查詢20條記錄竟然要5秒呢，尤其在這個表是加上了時間索引的情況下——參考《程序猿是如何解決SQLServer占CPU100%的》中提到的索引。

我嘗試去掉這句AND b.AddrId in ('02109000',……,'02109002')，結果不到1秒就把538條記錄查詢出來了，而加上地點限制這句，結果是204行。為什么結果集不大，花費的時間卻相差這么多呢？查看執行計劃，發現走的是另外的索引，而非時間索引。

SQL Server的分頁優化及Row_Number()分頁存在的問題

把這個疑問放到了SQLServer群上，很快，高桑給了回復：要想達到跟去掉地點限制這句的效果，就使用AdddrId+'' in。

什么意思？一時沒看明白，是高桑沒看懂我的語句？很快，有人補充，要欺騙查詢引擎。“欺騙”?還是不懂，不過我照做了，把上述cte的語句原封不動的Copy出來，然后把這句AND b.AddrId in ('02109000',……,'02109002')更改為了AND b.AddrId+'' in ('02109000',……,'02109002')，一點執行，神了！！！不到1秒就執行完了。在把執行計劃一對，果然走的是時間索引：

SQL Server的分頁優化及Row_Number()分頁存在的問題

后來回味了一下，記起之前看到的查詢引擎優化原理，如果你的條件中帶有運算符或者使用函數等，則查詢引擎會放棄優化，而執行表掃描。腦袋突然轉過來了，在使用b.AddrId+''前查詢引擎嘗試把mgrObj表加入一起做優化，那么兩個表聯查，會導致預估的記錄數大大增加，而使用了b.AddrId+''，查詢引擎則會先按時間索引把記錄刷選出來，這樣就達到了效果，即強制先做cte在執行in條件，而不是在cte中進行in條件刷選。原來如此！有時候，查詢引擎過度的優化，會導致相反的效果，而你如果能夠知道優化的原理，那么就可以通過一些小的技巧讓查詢引擎按你的期望去進行優化。

ROW_NUMBER()分頁在頁數較大時的問題

事情到這里，還沒完。后面同事又跟我反應，查詢到后面的頁數，又卡了！what?我重新執行上述語句，把時間范圍放到2011-12-01到2014-12-26，記錄數限制為為19981到20000，果然，查詢要30秒左右，查看執行計劃，都是一樣的，為什么？

高桑懷疑是key lookup過多導致的，建議先分頁取出rid 再做key lookup。不懂這么一句是什么意思。把執行計劃和IO打印出來：

SQL Server的分頁優化及Row_Number()分頁存在的問題

看看IO，很明顯，主要是越到后面的頁數，其他的幾個關聯表讀取的頁數就越多。我推測，在Row_Number分頁的時候，如果有表連接，則按排序一致到返回的記錄數位置，前面的記錄都是要參與表連接的，這就導致了越到后面的分頁，就越慢，因為要掃描的關聯表就越多。

難道就沒有了辦法了嗎？這個時候宋桑英勇的站了出來：“你給表后加一個forceseek提示可破”。這真是猶如天籟之音，馬上進行嘗試。

使用forceseek提示可以強制表走索引

查了下資料：

SQL Server2008中引入的提示ForceSeek，可以用它將索引查找來替換索引掃描

那么，就在eventlog表中加上這句看看會怎樣?

SQL Server的分頁優化及Row_Number()分頁存在的問題

果然，查詢計劃變了，開始提示，缺少了包含索引。趕緊加上，果然，按這個方式進行查詢之后查詢時間變為18秒，有進步！但是查看IO，跟上面一樣，并沒有變少。不過，總算學會了一個新的技能，而宋桑也很熱心說晚上再幫忙看看。

把其他沒參與where的表放到cte外面

根據上面的IO，很快，又有人提到，把其他left join的表放到cte外面。這是個辦法，于是把除eventlog、mgrobj、addrnode的表放到外面，語句如下：

WITH cte AS(
    select a*,b.AddrId,b.Name as MgrObjName,b.MgrObjTypeId          
            ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
        from eventlog as a
            left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
            left join addrnode as c on b.AddrId=c.Id 
        where a.AlarmTime>='2011-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId+'' in ('02109000',……,'02109002') ) SELECT a.* ,ag.Name as AgentServerName ,d.Name as MgrObjTypeName,l.UserName as userName FROM cte a left join eventdir as e on a.EventBm=e.Bm left join mgrobjtype as d on a.MgrObjTypeId=d.Id left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid WHERE RowNo BETWEEN 19980 AND 20000;

果然有效，IO大大減少了，然后速度也提升到了16秒。

表 'loginuser'。掃描計數 1，邏輯讀取 63 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'agentserver'。掃描計數 1，邏輯讀取 1617 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'mgrobjtype'。掃描計數 1，邏輯讀取 126 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'eventdir'。掃描計數 1，邏輯讀取 42 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'addrnode'。掃描計數 1，邏輯讀取 119997 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'eventlog'。掃描計數 1，邏輯讀取 5027 次，物理讀取 3 次，預讀 5024 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'mgrobj'。掃描計數 1，邏輯讀取 24 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

我們看到，addrNode表還是掃描計數很大。那還能不能提升，這個時候，我想到了，先把addrNode、mgrobj、mgrobjtype三個表聯合查詢，放到一個臨時表，然后再和eventlog做inner join，然后查詢結果再和其他表做left join，這樣還能減少IO。

使用臨時表存儲分頁記錄在進行表連接減少IO

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj
SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName 
    INTO tmpMgrObj  
    FROM dbo.mgrobj m
        INNER JOIN dbo.addrnode a ON a.Id=m.AddrId
    WHERE AddrId IN('02109000',……,'02109002');
WITH cte AS(
    select a.*,b.AddrId,b.MgrObjTypeId          
            ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
            ,ag.Name as AgentServerName
    ,d.Name as MgrObjTypeName,l.UserName as userName
        from eventlog as a
            INNER join tmpMgrObj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm
            left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
            left join agentserver As ag on a.AgentBm=ag.AgentBm 
            left join loginUser as l on a.cfmoper=l.loginGuid 
    WHERE AlarmTime>'2011-12-01 00:00:00' AND AlarmTime<='2014-12-26 23:59:59' ) SELECT * FROM cte WHERE RowNo BETWEEN 19980 AND 20000 IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

這次查詢僅用了10秒。我們來看看IO：

表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'mgrobj'。掃描計數 1，邏輯讀取 24 次，物理讀取 2 次，預讀 23 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'addrnode'。掃描計數 1，邏輯讀取 6 次，物理讀取 3 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
----------
表 'loginuser'。掃描計數 0，邏輯讀取 24 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'eventlog'。掃描計數 93，邏輯讀取 32773 次，物理讀取 515 次，預讀 1536 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'tmpMgrObj'。掃描計數 1，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'mgrobjtype'。掃描計數 1，邏輯讀取 6 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'agentserver'。掃描計數 1，邏輯讀取 77 次，物理讀取 2 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

除了eventlog之外，其他的表的IO大大減少，有木有？

inner join和left join的區別

但是，多執行幾次測試，發現上述語句還是有一點問題：查詢第一頁的時候，也竟然要用5秒，而查詢時間在當前一個月份的，也接近5秒。這是為什么呢？這個時候，宋桑再伸援手，提供了另外一個SQL語句，在查詢前面幾頁的時候1秒就出來了，而后面的頁數，則變化不大。我仔細比較了兩個語句，原來我用的是inner join，而宋桑給的是left join。這兩個有什么區別呢。仔細對比查詢計劃之后發現，使用inner join的時候，查詢引擎會先執行inner join而非子查詢，而使用left join則查詢引擎先執行子查詢。因此如果使用了inner join會導致在查詢1個月的數據時，沒有有效利用了時間索引。最終，我研究出來的語句如下，在查詢最新數據或者前面幾頁的數據，能夠在1秒左右出來，而查詢后面的頁數，在10秒左右，基本解決了問題。

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj
SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeName
    INTO tmpMgrObj  
    FROM dbo.mgrobj m
        INNER JOIN dbo.addrnode a ON a.Id=m.AddrId
        INNER JOIN dbo.mgrobjtype t ON m.MgrObjTypeId=t.Id
    WHERE AddrId+'' IN('02109000',……,'02109002');
SELECT tmp.*
    ,ag.Name AS AgentServerName
    , l.UserName AS userName
FROM    ( 
    SELECT    a.* ,b.MgrObjTypeName  , b.AddrId
        ,ROW_NUMBER() OVER ( ORDER BY AlarmTime DESC ) AS RowNo
    FROM
        (SELECT    * 
            FROM      eventlog
            WHERE     AlarmTime >= '2011-12-01 00:00:00' AND AlarmTime <= '2014-12-26 23:59:59') AS a LEFT JOIN tmpMgrObj AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm ) tmp LEFT JOIN eventdir AS e ON tmp.EventBm = e.Bm LEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBm LEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuid WHERE tmp.RowNo BETWEEN 1 AND 20; IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

其他優化參考

在另外的群上討論時，發現使用ROW_NUMBER分頁查詢到后面的頁數會越來越慢的這個問題的確困擾了不少的人。

有的人提出，誰會這么無聊，把頁數翻到幾千頁以后?一開始我也是這么想的，但是跟其他人交流之后，發現確實有這么一種場景，我們的軟件提供了最后一頁這個功能，結果……當然，一種方法就是在設計軟件的時候，就去掉這個最后一頁的功能；另外一種思路，就是查詢頁數過半之后，就反向查詢，那么查詢最后一頁其實也就是查詢第一頁。

還有一些人提出，把查詢出來的內容，放到一個臨時表，這個臨時表中的加入自增Id的索引，這樣，可以通過辨別Id來進行快速刷選記錄。這也是一種方法，我打算稍后嘗試。但是這種方法也是存在問題的，就是無法做到通用，必須根據每個表進行臨時表的構建，另外，在超大數據查詢時，插入的記錄過多，因為索引的存在也是會慢的，而且每次都這么做，估計CPU也挺吃緊。但是不管怎么樣，這是一種思路。

你有什么好的建議？不妨把你的想法在評論中提出來，一起討論討論。

總結

現在，我們來總結下在這次優化過程中學習到什么內容：

在SQLServer中，ROW_NUMBER的分頁應該是最高效的了，而且兼容SQLServer2005以后的數據庫
通過“欺騙”查詢引擎的小技巧，可以控制查詢引擎部分的優化過程
ROW_NUMBER分頁在大頁數時存在性能問題，可以通過一些小技巧進行規避
- 盡量通過cte利用索引
- 把不參與where條件的表放到分頁的cte外面
- 如果參與where條件的表過多，可以考慮把不參與分頁的表先做一個臨時表，減少IO
inner join會優先于子查詢，而left join不會
使用with(forceseek)可以強制查詢因此進行索引查詢

來源：碼到功成

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1420524994421.html

SQL Server 數據庫服務器

SQL Server的分頁優化及Row_Number()分頁存在的問題

用ROW_NUMBER()進行分頁

“欺騙”查詢引擎讓查詢按你的期望去查詢

ROW_NUMBER()分頁在頁數較大時的問題

使用forceseek提示可以強制表走索引

把其他沒參與where的表放到cte外面

使用臨時表存儲分頁記錄在進行表連接減少IO

inner join和left join的區別

其他優化參考

總結

相關經驗

相關資訊

相關文檔

目錄