记一次SQLServer的分页优化兼谈谈使用Row

那么，就在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大大减少，有木有？

强制使用hash join

经网友提示，在大的页数时，可以强制使用hash join来减少IO，而且经过尝试，可以通过建立两个子查询来避免使用临时表。经过调整，最终优化的SQL语句如下：

SELECT *
	,ag.Name AS AgentServerName
	, l.UserName AS userName
FROM ( 
	SELECT a.*,ROW_NUMBER() OVER (ORDER BY AlarmTime DESC) AS RowNo
	, b.AddrName , b.Name AS MgrObjName
	FROM
	(SELECT * 
	FROM eventlog
	WHERE AlarmTime>= '2011-12-01 00:00:00' AND AlarmTime< '2014-12-26 23:59:59') AS a
	INNER HASH JOIN (
	SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeName
	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')
	) AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm
	
) tmp 
	LEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBm
	LEFT JOIN eventdir AS e ON tmp.EventBm = e.Bm
	LEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuid
WHERE tmp.RowNo BETWEEN 190001 AND 190020

在大的分页的时候，通过hash查询，不必扫描前面的页数，可以大大减少IO，但是，由于hash join是强制性的，所以使用的时候要注意，我这里应该是个特例。

查询分析器的提示：“警告: 由于使用了本地联接提示，联接次序得以强制实施。”

我们来看看对应情况下的IO：

表 'eventlog'。扫描计数 5，逻辑读取 5609 次，物理读取 34 次，预读 5636 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 3，逻辑读取 375 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'mgrobj'。扫描计数 5，逻辑读取 24 次，物理读取 8 次，预读 40 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'mgrobjtype'。扫描计数 1，逻辑读取 6 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'addrnode'。扫描计数 3，逻辑读取 18 次，物理读取 6 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'loginuser'。扫描计数 1，逻辑读取 60 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'eventdir'。扫描计数 1，逻辑读取 40 次，物理读取 0 次，预读 0 次，lob 逻辑读取 30 次，lob 物理读取 0 次，lob 预读 0 次。
表 'agentserver'。扫描计数 1，逻辑读取 1540 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

这次的IO表现非常的好，没有因为查询后面的页数增大而导致较大的IO，查询时间从没有使用hash join的50秒提升为只需12秒，查询时间的开销应该耗费了在hash查找上了。

再看看对应的查询计划，这个时候，主要是因为排序的开销较大。

我们再看看他的预估的和执行的区别，为什么会让排序占如此大的开销？

很明显，预估的时候只需对刷选的结果排序，但是实际执行是对前面所有的页数进行了排序，最终排序占了大部分的开销。那么，这种情况能破吗?请留下您的回复！

其他优化参考

在另外的群上讨论时，发现使用ROW_NUMBER分页查询到后面的页数会越来越慢的这个问题的确困扰了不少的人。

有的人提出，谁会这么无聊，把页数翻到几千页以后?一开始我也是这么想的，但是跟其他人交流之后，发现确实有这么一种场景，我们的软件提供了最后一页这个功能，结果……当然，一种方法就是在设计软件的时候，就去掉这个最后一页的功能；另外一种思路，就是查询页数过半之后，就反向查询，那么查询最后一页其实也就是查询第一页。

还有一些人提出，把查询出来的内容，放到一个临时表，这个临时表中的加入自增Id的索引，这样，可以通过辨别Id来进行快速刷选记录。这也是一种方法，我打算稍后尝试。但是这种方法也是存在问题的，就是无法做到通用，必须根据每个表进行临时表的构建，另外，在超大数据查询时，插入的记录过多，因为索引的存在也是会慢的，而且每次都这么做，估计CPU也挺吃紧。但是不管怎么样，这是一种思路。

你有什么好的建议？不妨把你的想法在评论中提出来，一起讨论讨论。

总结

现在，我们来总结下在这次优化过程中学习到什么内容：

最后，感谢SQLServer群的高桑、宋桑、肖桑和其他群友的大力帮助，这个杜绝吹水的群非常的棒，让我这个程序猿学到了很多数据库的知识！

注：经网友提示，2015-01-07 09:15做以下更新：

参考文章