記一次SQLServer的分頁最佳化兼談談使用Row_Number()分頁存在的問題

標籤：

原文:記一次SQLServer的分頁最佳化兼談談使用Row_Number()分頁存在的問題

最近有項目反應，在伺服器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 RowNofrom 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行。為什麼結果集不大，花費的時間卻相差這麼多呢？查看執行計畫，發現走的是另外的索引，而非時間索引。

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

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

後來回味了一下，記起之前看到的查詢引擎最佳化原理，如果你的條件中帶有運算子或者使用函數等，則查詢引擎會放棄最佳化，而執行表掃描。腦袋突然轉過來了，在使用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列印出來：

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

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

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

查了下資料：

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

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

果然，查詢計劃變了，開始提示，缺少了包含索引。趕緊加上，果然，按這個方式進行查詢之後查詢時間變為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 RowNofrom eventlog as aleft 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 userNameFROM 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 tmpMgrObjSELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName INTO tmpMgrObj  FROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdWHERE 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 userNamefrom eventlog as aINNER join tmpMgrObj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBmleft 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 20000IF 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 tmpMgrObjSELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeNameINTO tmpMgrObj  FROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdINNER JOIN dbo.mgrobjtype t ON m.MgrObjTypeId=t.IdWHERE AddrId+'' IN('02109000',……,'02109002');SELECT tmp.*,ag.Name AS AgentServerName, l.UserName AS userNameFROM    ( SELECT    a.* ,b.MgrObjTypeName  , b.AddrId,ROW_NUMBER() OVER ( ORDER BY AlarmTime DESC ) AS RowNoFROM(SELECT    * FROM      eventlogWHERE     AlarmTime >= '2011-12-01 00:00:00' AND AlarmTime <= '2014-12-26 23:59:59') AS aLEFT JOIN tmpMgrObj AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm) tmp LEFT JOIN eventdir AS e ON tmp.EventBm = e.BmLEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBmLEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuidWHERE 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)可以強制查詢因此進行索引查詢

參考文章

• 曲演雜壇--蛋疼的ROW_NUMBER函數
  
• 為什麼超長列表資料的翻頁技術實現複雜

最後，感謝SQLServer群的高桑、宋桑、肖桑和其他群友的大力協助，這個杜絕吹水的群非常的棒，讓我這個程式猿學到了很多資料庫的知識！

如果您覺得閱讀本文對您有協助，請點一下“推薦”按鈕，您的“推薦”將是我最大的寫作動力！如果您想持續關注我的文章，請掃描二維碼，關注馬非碼的公眾號，我會將我的文章推送給您，並和您一起分享我日常閱讀過的優質文章。
   
本文著作權歸作者和部落格園共有，來源網址：http://www.cnblogs.com/marvin/歡迎各位轉載，但是未經作者本人同意，轉載文章之後必須在文章頁面明顯位置給出作者和原文串連，否則保留追究法律責任的權利。

記一次SQLServer的分頁最佳化兼談談使用Row_Number()分頁存在的問題