MySQL, Oracle, SQL Server 三种主流数据库分页查询实现方案

结果与准备

在 Web 应用开发中，数据量较大时通常需要将结果集进行分页展示。不同关系型数据库由于设计哲学和历史原因，在实现分页的功能支持上存在显著差异。理解这些差异并选择高效的 SQL 语句，是保证后端响应速度的关键。

真实操作顺序：各数据库分页实现

第1步：使用 MySQL 的 LIMIT 进行分页

目的：快速获取指定范围的数据记录。 入口：MySQL 数据库环境。 动作：利用 LIMIT 子句直接限制返回的行数。LIMIT 可以接受一个或两个参数。若提供两个参数，第一个表示起始偏移量（从 0 开始），第二个表示最大返回行数。

• 返回前 10 行：SELECT * FROM table WHERE ... LIMIT 10;
• 返回前 10 行（显式指定偏移 0）：SELECT * FROM table WHERE ... LIMIT 0, 10;
• 返回第 11 到 20 行（偏移 10，每页 10 条）：SELECT * FROM table WHERE ... LIMIT 10, 20;

完成状态：SQL 语句执行后，仅返回符合偏移量和数量要求的数据集。这是最简洁且常用的方式。

第2步：使用 Oracle 的 ROWNUM 进行分页

目的：在无原生 LIMIT 语法的 Oracle 中，通过伪列控制数据提取区间。 入口：Oracle 数据库环境。 动作：Oracle 没有 LIMIT 关键字，而是使用系统自动分配给每行的伪列 ROWNUM。由于 ROWNUM 是在查询结果生成过程中分配的，不能直接在外部对 ROWNUM 使用大于条件，因此需要嵌套子查询。

推荐的高效写法（外层控制最小值，内层控制最大值）：

SELECT * FROM (
    SELECT A.*, ROWNUM RN 
    FROM (SELECT * FROM TABLE_NAME) A 
    WHERE ROWNUM <= 40 -- 内层限制最大行数
)
WHERE RN >= 21;       -- 外层限制最小行数

上述 SQL 的逻辑解读： 1. 最内层查询：SELECT * FROM TABLE_NAME，获取原始数据源。 2. 中间层查询：引入 ROWNUM 作为别名 RN，并通过 WHERE ROWNUM <= 40 先截取前 40 条数据。这一步至关重要，限制了全表扫描的范围，提升了效率。 3. 最外层查询：从已编号的结果集中，筛选出 RN >= 21 的记录，即得到第 21 到 40 条数据。

另一种写法是将排序逻辑放入子查询，例如按工资降序排列后取第 22 到 40 名：

SELECT * FROM (
    SELECT e.*, ROWNUM r 
    FROM (
        SELECT * FROM emp ORDER BY sal DESC
    ) e 
) e1 
WHERE e1.r > 21 AND e1.r <= 40;

注意：第一种方案（内层限上限，外层限下限）在大多数情况下效率更高，因为它在早期阶段就限制了数据流向外层处理的行数。

第3步：使用 SQL Server 的多重分页方案

目的：在 SQL Server 中根据版本和场景选择合适的分页手段。 入口：SQL Server 数据库环境。 动作：SQL Server 提供了多种实现方式，以下分析三种常见方案：

方案一：利用 NOT IN 和 SELECT TOP 思路是排除掉前面已经显示过的记录 ID。

-- 假设页大小为10，当前为第3页（跳过前20条）
SELECT TOP 10 * 
FROM TestTable 
WHERE (ID NOT IN (
    SELECT TOP 20 id 
    FROM TestTable 
    ORDER BY id
)) 
ORDER BY ID;

方案二：利用 ID 大于某个值和 SELECT TOP（推荐高效方案） 思路是先找到上一页最后一条记录的 ID，然后查询大于该 ID 的前 N 条记录。

-- 查找大于前20条记录中最大ID的前10条
SELECT TOP 10 * 
FROM TestTable 
WHERE (ID > (
    SELECT MAX(id) 
    FROM (
        SELECT TOP 20 id 
        FROM TestTable 
        ORDER BY id
    ) AS T
)) 
ORDER BY ID;

方案三：利用游标存储过程 这是一种较为通用但较老的方法，通过系统存储过程管理游标状态。

CREATE PROCEDURE XiaoZhengGe
@sqlstr nvarchar(4000),   -- 查询字符串
@currentpage int,         -- 第N页
@pagesize int             -- 每页行数
AS
BEGIN
    SET nocount on;
    DECLARE @P1 int, @rowcount int;
    
    -- 打开游标
    EXEC sp_cursoropen @P1 OUTPUT, @sqlstr, @scrollopt=1, @ccopt=1, @rowcount=@rowcount OUTPUT;
    
    -- 计算总页数
    SELECT CEILING(1.0 * @rowcount / @pagesize) AS '总页数';
    
    -- 调整当前页起始位置
    SET @currentpage = (@currentpage - 1) * @pagesize + 1;
    
    -- 获取当前页数据
    EXEC sp_cursorfetch @P1, 16, @currentpage, @pagesize;
    
    -- 关闭游标
    EXEC sp_cursorclose @P1;
    SET nocount off;
END

第4步：性能分析与选择建议

目的：根据实际数据量和业务需求确定最佳实践。 结论： 1. SQL Server 方案二（ID > MAX）：效率最高，适合有主键索引的情况，但需要拼接 SQL 或在应用层处理逻辑。 2. SQL Server 方案一（NOT IN）：效率次之，同样依赖索引，但在大数据量下性能下降明显。 3. SQL Server 方案三（游标）：效率相对较低，但代码结构统一，适用于通用存储过程封装。 优化建议： 无论哪种数据库，强烈建议在用于排序和过滤的字段（如 ID、更新时间等）上建立主键或索引。无索引的分页查询在大表场景下会导致全表扫描，严重拖慢系统响应速度。

完成判断

通过以上针对不同数据库特性的具体分析，开发者可以根据所在的技术栈选择最合适的分页 SQL 写法。对于大数据量场景，务必结合索引使用高效的分页算法，避免深层嵌套或不必要的排序操作导致性能瓶颈。

简要总结

以上就是 MySQL、Oracle 和 SQL Server 三种主流数据库分页查询语法的详细内容。深入理解 ROWNUM 的限制机制、LIMIT 的参数含义以及 SQL Server 的各种 TOP/NIN 方案，有助于写出更健壮、高效的数据库查询语句。更多关于数据库性能优化的资料请关注本站其它相关文章！