数据库的分页查询一般由两个查询组成:
select count(*) from Goods
和
select * from ( select row_number() over(order by goodsId) as rowid, * from Goods ) as a where rowid >= @pageSize * (@pageIndex - 1) + 1 and rowid <= @pageSize * @pageIndex
第一个查询,获取符合条件的记录总数,用于计算总页数。第二个查询获取指定页上的记录。
两个查询需要执行两次,也就是做两次表扫描,这对效率是极大的损失。能不能只做一次表扫描?
row_number()函数在做排名时,肯定需要做一次表扫描,而排名完成后,肯定是知道有多少条记录的,如果这个内部值能输出到外部给调用者使用,那么两个查询就可以合并为一个查询,且仅需要一次表扫描了。
经过研究,row_number()函数并没有这个功能,但是同时发现 over()这个窗口函数既可以配合排名函数使用,也可以配合聚合函数使用。例如:
select count(*) over() as recordCount from Goods
那么,如果利用窗口函数over(),然后同时搭配row_number()排名函数和count()聚合函数,Sql Server的执行计划能否在内部自动优化为仅做一次表扫描?于是查询改为如下:
select * from ( select count(*) over() as recordTotalCount, row_number() over(order by goodsId) as rowid, * from Goods ) as a where rowid >= @pageSize * (@pageIndex - 1) + 1 and rowid <= @pageSize * @pageIndex
经过测试,由于在同一个查询中,都使用了over()窗口函数,Sql Server在生成执行计划时,自动优化为仅进行一次表扫描了