【愚公系列】2022年01月SQLServer数据库数据分页的五种性能分析

数据分页往往有三种常用方案。

• 把数据库中存放的相关数据&＃xff0c;全部通过编程语言读入内存中&＃xff0c;再由代码对其进行分页操作&＃xff08;速度慢&＃xff0c;简易性高&＃xff09;。
• 直接在数据库中对相关数据进行分页操作&＃xff0c;再把分页后的数据输出给代码程序&＃xff08;速度中&＃xff0c;简易性中&＃xff09;。
• 先把数据库中的相关数据全部读入“缓存”或第三方工具&＃xff0c;再由代码程序对“缓存”或第三方工具中的数据进行读取&＃43;分页操作&＃xff08;速度快&＃xff0c;简易性差&＃xff09;。

本文主要是直接在数据库中对相关数据进行分页操作&＃xff0c;数据库是SQL Server上的案例&＃xff08;其它种类数据库由于Sql语句略有差异&＃xff0c;所以需要调整&＃xff0c;但方案也类似&＃xff09;

1.ROW_NUMBER() OVER()方式

1、这种分页方案主要是在SQL2012以下推荐使用。

通用写法如下&＃xff1a;

--pageIndex 表示指定页
--pageSize 表示每页显示的条数
SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId,* FROM 表名 ) AS r
WHERE RowId BETWEEN ((pageIndex-1)*pageSize &＃43; 1) AND (pageIndex * PageSize)


用子查询新增一列行号&＃xff08;ROW_NUMBER&＃xff09;RowId查询&＃xff0c;比较高效的查询方式&＃xff0c;只有在SQL Server2005或更高版本才支持。

BETWEEN 1 AND 10 是指查询第1到第10条数据&＃xff08;闭区间&＃xff09;&＃xff0c;在这里面需要注意的是OVER的括号里面可以写多个排序字段。

2、代码案例

-- 1.数据库分页方案一 ROW_NUMBER() OVER()方式
SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY MO_ID) AS RowId,* FROM MO ) AS r
WHERE RowId BETWEEN 1 AND 10


2.offset fetch next方式

1、这种分页方案主要是在SQL2012及以上的版本才支持&＃xff1a;推荐使用。

通用写法如下&＃xff1a;

--pageIndex 表示指定页
--pageSize 表示每页显示的条数
SELECT * FROM 表名
ORDER BY 排序字段 offset ((pageIndex - 1) * pageSize) ROWS FETCH NEXT pageSize ROWS ONLY


• offset 是跳过多少行&＃xff0c;
• next是取接下来的多少行&＃xff0c;

句式 offset…rows fetch nect …rows only &＃xff0c;注意rows和末尾的only 不要写漏掉了&＃xff0c;并且这种方式必须要接着Order by XX 使用&＃xff0c;不然会报错。

2、代码案例

-- 2.数据库分页方案一 ROW_NUMBER() OVER()方式
SELECT * FROM MO
ORDER BY MO_ID offset 0 ROWS FETCH NEXT 10 ROWS ONLY


3.top not in方式

1、不推荐使用这种方式进行分页

通用写法如下&＃xff1a;

--pageIndex 表示指定页
--pageSize 表示每页显示的条数
SELECT TOP pageSize *
FROM 表名
WHERE 主键字段 NOT IN (SELECT TOP ((pageSize-1)*pageIndex) 主键字段 FROM 表名)


这条语句的原理是先查询1-10条记录的ID&＃xff0c;然后再查询ID不属于这1-10条记录的ID&＃xff0c;并且只需要10条记录&＃xff0c;因为每页大小就是10&＃xff0c;这就是获取到的第11-20条记录&＃xff0c;这是非常简单的一种写法。

另外IN语句与NOT IN语句类似&＃xff0c;这是NOT IN的写法&＃xff0c;但是这种写法数据量大的话效率太低。

2、代码案例

-- 2.数据库分页方案一 ROW_NUMBER() OVER()方式
SELECT TOP 10 *
FROM MO
WHERE MO_ID NOT IN (SELECT TOP 10 MO_ID FROM MO)


4.升序与降序方式

1、不推荐使用这种方式进行分页

通用写法如下&＃xff1a;

--pageIndex 表示指定页
--pageSize 表示每页显示的条数
SELECT * FROM(SELECT TOP pageSize * FROM(SELECT TOP ((pageIndex - 1) * pageSize &＃43;(pageSize*2)) * FROM 表名 ORDER BY 排序字段 ASC) AS TEMP1 ORDER BY 排序字段 DESC)AS TEMP2 ORDER BY 排序字段 ASC


这条语句首先查询前20条记录&＃xff0c;然后在倒序查询前10条记录&＃xff08;即倒数10条记录&＃xff09;&＃xff0c;这个时候就已经获取到了11-20条记录&＃xff0c;但是他们的顺序是倒序&＃xff0c;所以最后又进行升序排序。

2、代码案例

--4.查询第11-20条记录
SELECT * FROM(SELECT TOP 10 * FROM(SELECT TOP 20 * FROM MO ORDER BY MO_ID ASC) AS TEMP1 ORDER BY MO_ID DESC)AS TEMP2 ORDER BY MO_ID ASC


5.采用MAX(ID)或者MIN(ID)函数方式

1、不推荐使用这种方式进行分页

通用写法如下&＃xff1a;

--pageIndex 表示指定页
--pageSize 表示每页显示的条数
SELECT TOP pageSize * FROM 表名 WHERE 排序字段>(SELECT MAX(menuId) FROM(SELECT TOP ((PageIndex-1)*PageSize) 排序字段 FROM 表名 ORDER BY 排序字段) AS TEMP1) --&＃xff08;第10条的id&＃xff09;


这个理解起来也简单&＃xff0c;先把第10条记录的id找出来&＃xff08;当然这里面是直接使用MAX()进行查找&＃xff0c;MIN()函数的用法也是类似的&＃xff09;&＃xff0c;然后再对比取比第10条记录的id大的前10条记录即为我们需要的结果。

2、代码案例

-- 5.查询第11-20条记录
SELECT TOP 10 * FROM MO WHERE MO_ID>(SELECT MAX(MO_ID) FROM(SELECT TOP 10 MO_ID FROM MO ORDER BY MO_ID) AS TEMP1) --&＃xff08;第10条的id&＃xff09;