SQL2008 高效分页分法

为什么要换种方式分页，一个字：太慢了

    分页要传入的参数，1：页号，2：行数

    分页要取到的数据， 1：总行数，2：单页数据

    本文的方式应该有不少老手在使用了，欢迎吐糟、拍砖！

 

1、先造点测试数据：

CREATE TABLE [Raw_UserInfo](
    [ID] [nvarchar](36) NOT NULL,
    [LoginName] [nvarchar](50) NULL,
    [RealName] [nvarchar](50) NULL,
    [Mobile] [nvarchar](50) NULL,
    [HousingAddr] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Raw_UserInfo] ADD  DEFAULT (newid()) FOR [ID]
GO


INSERT INTO  Raw_UserInfo(ID, LoginName,RealName,Mobile,HousingAddr) 
SELECT TOP (10 * 10000)     
     NEWID()
    , 'jingzhou' + STR( ABS( CHECKSUM(NEWID()))) 
    , '关云长' + STR(ABS(CHECKSUM(NEWID())))
    , '135' + STR(ABS(CHECKSUM(NEWID())))
    ,  '荆州' + STR(ABS(CHECKSUM(NEWID())))    
    FROM sys.columns AS c
    ,sys.columns AS c2 
    ,sys.columns AS c3
UNION ALL    
SELECT TOP (10 * 10000)     
     NEWID()
    , 'zilong' + STR(ABS(CHECKSUM(NEWID()))) 
    , '赵子龙' + STR(ABS(CHECKSUM(NEWID())))
    , '136' + STR(ABS(CHECKSUM(NEWID())))
    ,  '成都' + STR(ABS(CHECKSUM(NEWID())))    
    FROM sys.columns AS c
    ,sys.columns AS c2 
    ,sys.columns AS c3
    
UNION ALL    
SELECT TOP (10 * 10000)     
     NEWID()
    , 'zilong' + STR( ABS( CHECKSUM(NEWID()))) 
    , '张辽' + STR(ABS(CHECKSUM(NEWID())))
    , '137' + STR(ABS(CHECKSUM(NEWID())))
    ,  '汉都' + STR(ABS(CHECKSUM(NEWID())))    
    FROM sys.columns AS c
    ,sys.columns AS c2 
    ,sys.columns AS c3

UNION ALL    
SELECT TOP (10 * 10000)     
     NEWID()
    , 'xuzhu' + STR( ABS( CHECKSUM(NEWID()))) 
    , '许褚' + STR(ABS(CHECKSUM(NEWID())))
    , '139' + STR(ABS(CHECKSUM(NEWID())))
    ,  '汉都' + STR(ABS(CHECKSUM(NEWID())))    
    FROM sys.columns AS c
    ,sys.columns AS c2 
    ,sys.columns AS c3

2、传统的分页方式

DECLARE @pageIndex INT = 1
DECLARE @pageSize INT = 10
DECLARE @queryPar NVARCHAR(50) = '张辽'

SELECT COUNT(0) FROM raw.dbo.Raw_UserInfo
        WHERE  RealName  LIKE '%' + @queryPar + '%'

;WITH cte AS(
    SELECT ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr
    , rn = ROW_NUMBER() OVER(ORDER BY  ID) 
        FROM raw.dbo.Raw_UserInfo
            WHERE  RealName  LIKE '%' + @queryPar + '%'
)
SELECT ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr FROM cte
    WHERE rn > (@pageIndex - 1) 
        AND rn <= @pageIndex * @pageSize

3、换过的分页方式，标记的地方是要点

DECLARE @pageIndex INT = 1
DECLARE @pageSize INT = 10
DECLARE @queryPar NVARCHAR(50) = '张辽'

;WITH cte AS(
    SELECT *
    , rn = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ID) 
    FROM raw.dbo.Raw_UserInfo
            WHERE  RealName  LIKE '%' + @queryPar + '%'
),sc AS (
     SELECT TOP 1 cn = COUNT(0) OVER()  -- 开窗功能， 这一段非常固定， 请放心使用。对，位置就在这里，放在 cte 中功能效果一样，但不能提速。
        FROM cte
  )     -- 记录数
SELECT  ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr
    , sc.cn FROM cte
  CROSS APPLY sc
    WHERE cte.rn > (@pageIndex - 1)
        AND cte.rn <= @pageIndex * @pageSize

执行结果1： 在没加索引的情况的下，我们这种分页方式，几乎要快 100 倍

 

执行结果2：添加索引后，倍数没那么高，但也能到非常可观 4 倍

1 -- 添加缺失的索引
2 CREATE NONCLUSTERED INDEX idx_nc_realname
3 ON [dbo].[Raw_UserInfo] ([RealName])

 

 

争论点

1、原来是返回两个表的结果集，表1：总行数，表2：单页数据；现在只有一个表结果集，只不过在这个表的最后加一个名称为 cn 的列，我们可能需要改改 C# 取值的方式：

1 ds.Tables[0].ROWS[0][0].ToString()      -- 修改前
3 ds.Tables[0].ROWS[0]['cn'].ToString()   -- 修改后

 

2、修改后会多出单页数的行，有些同学总觉得别扭，它也就一页的行数，比如页大小是 20 行，这列就是 20 行，也就 1KB 的传输量，我们还是负担得起的。如果实在纠结，在 C# 取值后，可以把这一列删掉。