SQL Server的分頁優化及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 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行。為什麼結果集不大，花費的時間卻相差這麼多呢？檢視執行計劃，發現走的是另外的索引，而非時間索引。

把這個疑問放到了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 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)可以強制查詢因此進行索引查詢