MS SQL2005 分页分析及优化
MSSQL分页方式说明:
目前我所知的有以下几种方式
第一种方式和第二种方实际上是比较类似的.
优点:排序方式比较随意
缺点:
第一种方式有大量的IO开销.
第二种方式则会开销内存,但当表数据量比较大的时候性能会直线下降.
所以这两种方式都不适合做大数据量的分页.
第三种方式:性能次之,可操作较差
优点:排序方式比较随意
缺点:资源开销比较大,数据库会承担不小的运算压力,所以也不适合做大表分页.
第四种方式:性能平均,可操作性尚可
优点:排序相对比较随意,各分页情况下速度平均,属于不是最快也不是最慢.
缺点:没有明显缺点.
第五种方式:性能较好,可操作性良好
优点:排序相对比较随意,代码简洁,适用面广.
缺点:尾页速度比较慢(需针对优化).
第六种方式:性能最好,可操作性比较差
优点:速度快.
缺点:尾页速度比较慢(需针对优化),对排序键有要求.
PS:以上内容居于以前测试结果说得.
测试用库DB_PagingTest,测试用表:Paing_New
主键:IDDesc
总记录@RecordCount:10000331
分页尺寸@PageSize:30
总页数@PageCount:333345
请求页@AbsolutePage
分页情况分析:
请求页等于第一页,这种情况是最简单的.复制内容到剪贴板
代码:
SelectTOP@PageSize*From[Paing_New]OrderBYIDDesc 情况2:请求页小于总页数/2复制内容到剪贴板
代码:
WITHCTEAS
(
SELECTTOP@AbsolutePage*@PageSize
*
ROW_NUMBER()Over(OrderByIDDesc)as_RowNumber
FROM[Paing_New]
)
SELECT
*
FROMCTE
WHERE_RowNumber>(@AbsolutePage-1)*@PageSize); 情况3:
请求页大于等于总页数/2
理论上请求页等于总页数/2的时候应该也有优化方法.复制内容到剪贴板
代码:
WITHCTEAS
(
SELECTTOP@RecordCount-(@AbsolutePage-1)*@PageSize
*,
ROW_NUMBER()Over(OrderBYIDAsc)as_RowNumber
FROM[Paing_New]
)
SELECT
*
FROMCTE
WHERE_RowNumber>(@RecordCount-@AbsolutePage*@PageSize)OrderBYIDDesc; 情况4:
请求页等于总页数复制内容到剪贴板
代码:
WITHCTEAS
(
SELECTTOP@RecordCount-(@AbsolutePage-1)*@PageSize
*,
ROW_NUMBER()Over(OrderBYIDAsc)as_RowNumber
FROM[Paing_New]
)
SELECT
*
FROMCTEOrderBYIDDesc; 数据测试结果:
第30条,即1页,CPU时间=0毫秒，占用时间=1毫秒,实际执行时间=0毫秒;
第1W条,即334页,CPU时间=0毫秒，占用时间=3毫秒,实际执行时间=0毫秒;
第10W条,即3334页,CPU时间=31毫秒，占用时间=26~28毫秒,实际执行时间=16~33毫秒;
第100W条,即3334页,CPU时间=250~260毫秒，占用时间=250~260毫秒,实际执行时间=250~260毫秒;
第5000130条(中间页),即166671页,CPU时间=1200~1300毫秒，占用时间=1200~1300毫秒,实际执行时间=1200~1300毫秒;
第5000160条(中间页),即166672页,CPU时间=3400~3600毫秒，占用时间=3400~3600毫秒,实际执行时间=3400~3600毫秒;
第9000331条,即300012页,CPU时间=266~281毫秒，占用时间=273~285毫秒,实际执行时间=266~296毫秒;
第9900331条,即330012页,CPU时间=31~32毫秒，占用时间=29~30毫秒,实际执行时间=30~33毫秒;
第9999331条,即333312页,CPU时间=0毫秒，占用时间=2~3毫秒,实际执行时间=0毫秒;
第10000331条(尾页),即333345页,CPU时间=0毫秒，占用时间=1毫秒,实际执行时间=0毫秒;
PS:关于时间的说明,CPU时间和占用时间为MSSQL的统计结果,实行时间是人为技术所得;
分页方案优点:
对分页多数情况进行了针对优化,并且可以对非主键和顺序编号等情况进行分页.
开始和结尾速度都非常快,因为选择的记录集相对较少.
分页方案缺点:
请求页在总页数中间的时候速度比较慢.
结论:
对于使用ID为主键索引的分页,还是使用传统的ID大于或小于这种方式最好.
对于分页主键不明确的,使用CTE的方式比较好.