MySQL千万级数据分页查询性能瓶颈深度优化实践

面试官：“来说说，一千万的数据，你是怎么查询的？”
应聘者：“直接分页查询，使用 LIMIT 分页。”
面试官：“有实操过吗？”
应聘者：“肯定有呀！”

…一首《凉凉》或许已经在内心响起。很多人可能没有实际操作过千万级数据量的表，也不清楚直接分页查询会带来怎样的性能问题。今天，我们就基于 MySQL 5.7.26 来实际测试一下，并探讨优化方案。

准备数据

没有一千万数据怎么办？那就创建它。用代码逐条插入效率太低，这里采用存储过程批量插入，速度会快很多。

创建表

首先，我们创建一张用户操作日志表。

CREATE TABLE `user_operation_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

创建数据脚本

采用批量插入，每1000条提交一次，平衡效率与内存。

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
set @execData = '';
  WHILE i<=10000000 DO
set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
if i % 1000 = 0
then
set @stmtSql = concat(@execSql, @execData,";");
    prepare stmt from @stmtSql;
    execute stmt;
    DEALLOCATE prepare stmt;
    commit;
set @execData = "";
else
set @execData = concat(@execData, ",");
   end if;
  SET i=i+1;
  END WHILE;

END;;
DELIMITER ;

开始测试

测试环境配置为 Win10，标压 i5 处理器，SSD 硬盘。由于配置限制，本次测试实际插入了约 314.8万 条数据，占用磁盘 5GB（未建索引），耗时约 38 分钟。

先查看数据总量：

SELECT count(1) FROM `user_operation_log`

返回结果：3148000。三次查询时间分别为：14060 ms, 13755 ms, 13447 ms。可见，仅仅是 COUNT 操作在大数据量下也颇具挑战，这涉及到Java应用层与数据库的深度交互与性能调优。

普通分页查询

MySQL 分页查询语法为：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

• 第一个参数 offset：返回记录行的偏移量。
• 第二个参数 rows：返回记录行的最大数目。

我们来测试一个简单的分页：

SELECT * FROM `user_operation_log` LIMIT 10000, 10

三次查询时间分别为：59 ms, 49 ms, 50 ms。看起来很快？别忘了这是本地数据库。让我们更深入地探究规律。

相同偏移量，不同数据量

固定偏移量 10000，改变要查询的数据量 rows。

SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查询时间如下：

结论：在相同偏移量下，需要查询的数据量 (rows) 越大，花费时间越长。

相同数据量，不同偏移量

固定查询 100 条数据，改变偏移量 offset。

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

查询时间如下：

结论：查询相同数据量的情况下，偏移量 (offset) 越大，花费时间越长。这就是通常所说的“深度分页”问题。

如何优化

基于以上测试，我们明确了两个性能瓶颈：偏移量大 和 返回数据量大。下面分别进行优化。

优化偏移量大问题

采用子查询方式

思路：先定位到偏移位置的 id，再基于此 id 查询后续数据。

-- 原始慢查询
SELECT * FROM `user_operation_log` LIMIT 1000000, 10
-- 先查询起始id
SELECT id FROM `user_operation_log` LIMIT 1000000, 1
-- 使用子查询优化
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查询结果对比如下：

分析结论：

1. 第一条原始查询最慢。
2. 第三条子查询方式比第一条快很多，因为子查询 SELECT id 利用了主键索引，速度更快。

缺点：此方法要求 id 必须是连续递增的。对于 id 非连续的情况，可以使用以下写法，但注意分页查询只能放在子查询内部（某些 MySQL 版本不支持在 IN 子句中直接使用 LIMIT）。

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

采用 ID 限定方式

此方法要求最高：id 必须连续递增，且需要应用程序计算出每页的 id 范围。

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100
-- 或者
SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查询结果如下：

结论：这种方式速度极快，几乎是瞬时返回。注意：这里的 LIMIT 是为了限制条数，防止计算错误导致返回过多数据，并非用于分页偏移。

优化数据量大问题

即使解决了偏移量问题，单次查询返回的数据量过大也会严重影响性能。

SELECT * FROM `user_operation_log` LIMIT 1, 1000000
SELECT id FROM `user_operation_log` LIMIT 1, 1000000
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查询时间如下：

结论：减少不必要的列，可以显著提升查询效率，尤其是当网络传输成为瓶颈时（客户端与 MySQL 服务器分离的情况下，差异将更为明显）。第一条和第三条查询在本机测试中时间相近，是因为数据无需网络传输。

为什么禁止 SELECT * ？

写 SELECT * 简单无脑，它不香吗？主要基于两点考虑：

1. 增加数据库负担：SELECT * 需要数据库解析更多的对象、字段、权限、属性等信息。在 SQL 语句复杂、硬解析较多的情况下，会对数据库造成额外的负担。
2. 增大网络与内存开销：* 可能会无意中包含一些无用的大文本字段（如日志内容、图片MD5等），导致数据传输量急剧增加，特别是当应用程序与数据库不在同一台机器时，这种网络开销和客户端内存占用会非常明显。

总结

面对海量数据的分页查询，盲目使用 LIMIT offset, rows 会导致严重的性能问题。通过本文的实测与分析，我们可以得出以下优化思路：

1. 利用覆盖索引：优先使用只包含索引列的查询来定位数据范围。
2. 避免大偏移量：使用 WHERE id > {上一页最大id} 或基于子查询的方式替代 LIMIT offset。
3. 按需查询字段：严格避免 SELECT *，只获取业务需要的字段。

纸上得来终觉浅，绝知此事要躬行。建议读者根据文中提供的表结构和存储过程脚本，在自己的环境中实操一遍，感受会更加深刻。如果你有更好的优化方案或实践经验，欢迎在技术社区如云栈社区进行分享和交流。