SQL SERVER 分页的几种方式。

墨蓝 2024-02-19 13:15 41阅读 0赞

## 背景： ##

在日常工作中，经常需要解决分页的问题，这也是真分页必须要做的一件事情。经常查询，发现有很多的分页方法，为了了解他们的分页效率我做了一个实验。当然，跟自己的服务器性能也是有一定关系的。

## 准备： ##

1、我准备了一个数据库表，大概有78万的真是 数据(小编以此为例，以每页五十条数据为例，可以分出来15727页，我们搜索第15720页的数据)。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70]  
2、当然，也可以准备数据：

create database DBTest
    use DBTest
    
    --创建测试表
    create table pagetest
    (
    id int identity(1,1) not null,
    col01 int null,
    col02 nvarchar(50) null,
    col03 datetime null
    )
    
    --1万记录集
    declare @i int
    set @i=0
    while(@i<10000)
    begin
        insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()
        set @i=@i+1
    end

## sql写法（以每页五十条数据为例）： ##

--写法1，not in/top
    select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
    where id not in (
    	select top 78600 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id
    	)
    order by id
    --耗时110毫秒
    
    
    
    
    --写法2，not exists
    select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] pagetest
    where not exists 
    (
    	select 1 from 
    		(select top 786000 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id)a  
    	where a.id=pagetest.id
    )
    order by id
    --耗时：453毫秒
    
    
    
    
    --写法3，max/top
    select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
    where id>(
    	select max(id) from (select top 786000 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id)a
    		)
    order by id
    --耗时：313毫秒
    
    
    
    
    
    --写法4，row_number()
    select top 50 * from 
    (
    	select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
    )a
    where rownumber>786000
    --耗时：546毫秒
    
    
    
    select * from 
    (select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan])a
    where rownumber>786000 and rownumber<786051
    --耗时：530毫秒
    
    
    
    
    select * from 
    (select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan])a
    where rownumber between 786000 and 786051
    --耗时：500毫秒
    
    
    
    
    --写法5，在csdn上一帖子看到的，row_number() 变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号
    select *
    from (
        select row_number()over(order by tempColumn)rownumber,*
        from (select top 786051 tempColumn=0,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] where 1=1 order by id)a
    )b
    where rownumber>786000
    
    耗时：300毫秒

测试语句：

declare @begin_date datetime
    declare @end_date datetime
    select @begin_date = getdate()
    
    <.....待测试的sql代码.....>
    
    select @end_date = getdate()
    select datediff(ms,@begin_date,@end_date) as '毫秒'

## 总结 ##

（其实有点尴尬，测试数据总是不准，经过百度得知）：  
1.max/top,ROW\_NUMBER()都是比较不错的分页方法。相比ROW\_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同时适用于sql2000,access。  
2.not exists感觉是要比not in效率高一点点。  
3.ROW\_NUMBER()的3种不同写法效率看起来差不多。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ccbce9cd60bf490d87052c7b0710438d.png