记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题

簧横 · 3 天前

最近有项目反应，在服务器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

复制代码

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

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

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

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;

复制代码

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

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

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

在另外的群上讨论时，发现使用ROW_NUMBER分页查询到后面的页数会越来越慢的这个问题的确困扰了不少的人。
有的人提出，谁会这么无聊，把页数翻到几千页以后?一开始我也是这么想的，但是跟其他人交流之后，发现确实有这么一种场景，我们的软件提供了最后一页这个功能，结果……当然，一种方法就是在设计软件的时候，就去掉这个最后一页的功能；另外一种思路，就是查询页数过半之后，就反向查询，那么查询最后一页其实也就是查询第一页。
还有一些人提出，把查询出来的内容，放到一个临时表，这个临时表中的加入自增Id的索引，这样，可以通过辨别Id来进行快速刷选记录。这也是一种方法，我打算稍后尝试。但是这种方法也是存在问题的，就是无法做到通用，必须根据每个表进行临时表的构建，另外，在超大数据查询时，插入的记录过多，因为索引的存在也是会慢的，而且每次都这么做，估计CPU也挺吃紧。但是不管怎么样，这是一种思路。
你有什么好的建议？不妨把你的想法在评论中提出来，一起讨论讨论。
总结

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

在SQLServer中，ROW_NUMBER的分页应该是最高效的了，而且兼容SQLServer2005以后的数据库
通过“欺骗”查询引擎的小技巧，可以控制查询引擎部分的优化过程
ROW_NUMBER分页在大页数时存在性能问题，可以通过一些小技巧进行规避
- 尽量通过cte利用索引
- 把不参与where条件的表放到分页的cte外面
- 如果参与where条件的表过多，可以考虑把不参与分页的表先做一个临时表，减少IO
- 在较大页数的时候强制使用hash join可以减少io，从而获得很好的性能
使用with(forceseek)可以强制查询因此进行索引查询

最后，感谢SQLServer群的高桑、宋桑、肖桑和其他群友的大力帮助，这个杜绝吹水的群非常的棒，让我这个程序猿学到了很多数据库的知识！
注：经网友提示，2015-01-07 09:15做以下更新：

可以在记录数超过10000条，则采用hash join强制进行hash连接，减少IO(感谢27楼riccc)

去掉最先给定的结果中采用left join而不是inner join的连接——left join的结果相当于没有用上addrId in ()的条件(感谢32楼夏浩)

参考文章

曲演杂坛--蛋疼的ROW_NUMBER函数
为什么超长列表数据的翻页技术实现复杂

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

账号		自动登录	找回密码
密码			立即注册

记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题

相关帖子

签约作者

记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题

相关帖子

相关推荐

签约作者