MySQL 大数据量分页优化方案

Dylan

发布时间：2026-10-13 | 4人浏览过

原创

在 MySQL 数据库中，当表数据量达到百万级以上时，传统的 LIMIT offset, count 分页方式会因扫描大量无用数据而导致查询效率急剧下降。本文通过构建测试环境，演示如何利用 ROW_NUMBER() 窗口函数结合存储过程实现高效的深分页，确保即使在数据量增长时也能保持稳定的查询响应时间。

优化目标与原理

标准分页查询如 SELECT * FROM table LIMIT 100000, 10 需要数据库扫描前 100,000 条记录并丢弃，只返回最后 10 条。随着偏移量增大，I/O 开销线性增加。优化思路是先在子查询中生成每行的序号（Row Number），仅通过筛选序号区间来定位数据，避免全表大范围扫描。

准备测试环境

为了直观对比效果，我们需要创建一个包含基础字段和文本描述的测试表，并插入若干条模拟数据以验证逻辑的正确性。

第1步：创建数据库与数据表

目的：建立用于测试的分页场景。入口：MySQL 命令行或客户端工具。动作：执行以下 SQL 语句创建库和表。

-- 切换或使用测试数据库
USE TestForPaging;
GO

-- 创建数据表 SomeData
CREATE TABLE SomeData
(
    id INT PRIMARY KEY,
    name VARCHAR(30) NULL,
    description TEXT
);
GO

第2步：插入初始测试数据

目的：填充少量数据以验证基础查询语法。动作：插入 5 条示例记录。

-- 插入示例数据
INSERT INTO SomeData VALUES(1,'num1','第1条');
INSERT INTO SomeData VALUES(2,'num2','第2条');
INSERT INTO SomeData VALUES(3,'num3','第3条');
INSERT INTO SomeData VALUES(4,'num4','第4条');
INSERT INTO SomeData VALUES(5,'num5','第5条');
GO

完成判断：执行 SELECT COUNT(*) FROM SomeData; 应返回 5，表示数据插入成功。

核心分页逻辑实现

利用 ROW_NUMBER() 为结果集按指定排序生成唯一行号，再在外层查询中截取需要的行号范围。

第3步：理解单行行号生成

目的：掌握如何获取每条数据的排名位置。入口：直接在 SELECT 语句中使用窗口函数。参数：OVER(ORDER BY id DESC) 定义排序规则。

-- 查看带数据级别的记录
SELECT name,
       description,
       ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData;
GO

完成判断：输出结果中，id 最大的记录 dataLevel 为 1，依次递减。

第4步：筛选指定行号区间

目的：模拟分页中的“取第N页”操作。动作：将生成行号的查询作为子查询，外层使用 BETWEEN 过滤。

-- 查看指定的数据级别间的数据条目（例如第2到第4条）
SELECT dataLevel, name, description
FROM (
    SELECT name,
           description,
           ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
    FROM SomeData
) AS datawithleverl
WHERE dataLevel BETWEEN 2 AND 4;
GO

封装通用分页存储过程

将上述逻辑封装为存储过程，以便在应用程序中直接调用，支持动态排序字段。

第5步：创建存储过程 GetDataPaged

目的：实现可复用的分页接口。参数说明： @startRowIndex: 起始索引（从0开始，实际计算时需调整）。 @maximumRows: 每页显示条数。 @sort: 排序字段名，默认为 'id'。

-- 实现查看指定的数据级别间的数据条目的存储过程
CREATE PROCEDURE GetDataPaged
(
    @startRowIndex INT,
    @maximumRows INT,
    @sort VARCHAR(50)
)
AS
BEGIN
    -- 确保指定 sort 参数不为空，默认按 id 排序
    IF LEN(@sort) = 0
        SET @sort = 'id';

    -- 动态构建查询逻辑
    -- 注意：此处为静态 SQL 示例，生产环境中建议处理 SQL 注入风险
    DECLARE @sql NVARCHAR(MAX);
    
    -- 使用 EXEC sp_executesql 执行动态 SQL 以支持变量排序
    SET @sql = N'
    SELECT dataLevel, name, description 
    FROM (
        SELECT name,
               description,
               ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@sort) + ' DESC) AS dataLevel 
        FROM SomeData
    ) AS datawithleverl 
    WHERE dataLevel > (@startRowIndex * 10) 
      AND dataLevel <= (@startRowIndex * 10 + @maximumRows)';

    EXEC sp_executesql @sql, 
                       N'@startRowIndex INT, @maximumRows INT', 
                       @startRowIndex, @maximumRows;
END;
GO

注意：原文代码中直接使用变量拼接可能导致错误，上述脚本修正了 SQL Server 风格（基于输入草稿的 `GO` 关键字判断）的实现细节。若严格应用于 MySQL，需改用 MySQL 的预处理语句或纯 SQL 写法，但鉴于原稿结构，这里保留了其逻辑意图。在实际 MySQL 环境中，更常见的优化是使用“延迟关联”或主键范围跳跃，但若必须保留行号逻辑，需注意 MySQL 8.0+ 才完全支持窗口函数。