MSSQL 2005 分页分析及优化
MSSQL 分页方式说明:
目前我所知的有以下几种方式
临时表
表变量
in, not in
SET ROWCOUNT
CTE
id >, id <
优缺点分析: 性能最低, 可操作性差
第一种方式和第二种方实际上是比较类似的.
优点: 排序方式比较随意
缺点:
第一种方式 有大量的 IO 开销.
第二种方式则会开销内存, 但当表数据量比较大的时候性能会直线下降.
所以这两种方式都不适合做大数据量的分页.
第三种方式: 性能次之, 可操作较差
优点: 排序方式比较随意
缺点: 资源开销比较大, 数据库会承担不小的运算压力, 所以也不适合做大表分页.
第四种方式: 性能平均, 可操作性尚可
优点: 排序相对比较随意, 各分页情况下速度平均, 属于不是最快也不是最慢.
缺点: 没有明显缺点.
第五种方式: 性能较好, 可操作性良好
优点: 排序相对比较随意, 代码简洁, 适用面广.
缺点: 尾页速度比较慢(需针对优化).
第六种方式: 性能最好, 可操作性比较差
优点: 速度快.
缺点: 尾页速度比较慢(需针对优化), 对排序键有要求.
PS: 以上内容居于以前测试结果说得.
测试用库 DB_PagingTest, 测试用表: Paing_New
主键: ID Desc
总记录 @RecordCount: 10000331
分页尺寸 @PageSize: 30
总页数 @PageCount: 333345
请求页 @AbsolutePage
分页情况分析:
@AbsolutePage == 1
@AbsolutePage < @PageCount/2
@AbsolutePage >= @PageCount/2
@AbsolutePage == @PageCount
情况 1:
请求页等于第一页, 这种情况是最简单的.
复制内容到剪贴板
代码:
Select TOP @PageSize * From [Paing_New] Order BY ID Desc
情况 2:
请求页小于总页数/2
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @AbsolutePage * @PageSize
*
ROW_NUMBER() Over (Order By ID Desc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE
WHERE _RowNumber > (@AbsolutePage - 1) * @PageSize);
情况 3:
请求页大于等于总页数/2
理论上 请求页等于总页数/2的时候应该也有优化方法.
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
*,
ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE
WHERE _RowNumber > (@RecordCount - @AbsolutePage * @PageSize) Order BY ID Desc;
情况 4:
请求页等于总页数
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
*,
ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE Order BY ID Desc;
数据测试结果:
第 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 的方式比较好.
目前我所知的有以下几种方式
临时表
表变量
in, not in
SET ROWCOUNT
CTE
id >, id <
优缺点分析: 性能最低, 可操作性差
第一种方式和第二种方实际上是比较类似的.
优点: 排序方式比较随意
缺点:
第一种方式 有大量的 IO 开销.
第二种方式则会开销内存, 但当表数据量比较大的时候性能会直线下降.
所以这两种方式都不适合做大数据量的分页.
第三种方式: 性能次之, 可操作较差
优点: 排序方式比较随意
缺点: 资源开销比较大, 数据库会承担不小的运算压力, 所以也不适合做大表分页.
第四种方式: 性能平均, 可操作性尚可
优点: 排序相对比较随意, 各分页情况下速度平均, 属于不是最快也不是最慢.
缺点: 没有明显缺点.
第五种方式: 性能较好, 可操作性良好
优点: 排序相对比较随意, 代码简洁, 适用面广.
缺点: 尾页速度比较慢(需针对优化).
第六种方式: 性能最好, 可操作性比较差
优点: 速度快.
缺点: 尾页速度比较慢(需针对优化), 对排序键有要求.
PS: 以上内容居于以前测试结果说得.
测试用库 DB_PagingTest, 测试用表: Paing_New
主键: ID Desc
总记录 @RecordCount: 10000331
分页尺寸 @PageSize: 30
总页数 @PageCount: 333345
请求页 @AbsolutePage
分页情况分析:
@AbsolutePage == 1
@AbsolutePage < @PageCount/2
@AbsolutePage >= @PageCount/2
@AbsolutePage == @PageCount
情况 1:
请求页等于第一页, 这种情况是最简单的.
复制内容到剪贴板
代码:
Select TOP @PageSize * From [Paing_New] Order BY ID Desc
情况 2:
请求页小于总页数/2
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @AbsolutePage * @PageSize
*
ROW_NUMBER() Over (Order By ID Desc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE
WHERE _RowNumber > (@AbsolutePage - 1) * @PageSize);
情况 3:
请求页大于等于总页数/2
理论上 请求页等于总页数/2的时候应该也有优化方法.
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
*,
ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE
WHERE _RowNumber > (@RecordCount - @AbsolutePage * @PageSize) Order BY ID Desc;
情况 4:
请求页等于总页数
复制内容到剪贴板
代码:
WITH CTE AS
(
SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
*,
ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
FROM [Paing_New]
)
SELECT
*
FROM CTE Order BY ID Desc;
数据测试结果:
第 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 的方式比较好.
