MySQL实现分页查询的多种写法与性能对比

一、引言

在Web应用开发中，分页查询是处理大规模数据集的核心技术。无论是电商平台的商品列表、社交媒体的动态流，还是后台管理系统的数据表格，分页查询都直接影响用户体验和系统性能。MySQL作为主流关系型数据库，提供了多种分页实现方式，但不同方案在数据量、索引设计、并发场景下的性能差异显著。本文ZHANID工具网将系统梳理MySQL分页查询的6种主流实现方案，结合性能测试数据与真实案例，为开发者提供可落地的技术选型参考。

二、MySQL分页查询的核心机制

分页查询的本质是将结果集分割为多个子集，每次仅返回指定区间的数据。MySQL通过LIMIT offset, size或LIMIT size OFFSET offset语法实现基础分页功能，其底层执行流程包含两个关键阶段：

1. 定位阶段：根据OFFSET值跳过指定数量的记录

2. 提取阶段：返回后续size条记录

当数据量较小时（如千级以下），这种机制效率较高；但当数据量达到百万级时，OFFSET值增大将导致数据库扫描大量无效数据，形成性能瓶颈。例如，在未优化的情况下，查询第10万页数据（LIMIT 100000, 20）可能耗时超过30秒。

三、分页查询的6种实现方案与性能对比

方案1：基础LIMIT OFFSET分页

语法示例：

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 20 OFFSET 100000;

技术特点：

• 最简单的分页实现方式

• 必须配合ORDER BY保证结果稳定性

• 深度分页时性能急剧下降

性能测试数据（基于1000万条订单数据）：

典型问题：

• 某电商平台采用此方案后，用户访问第500页商品时出现超时错误

• 并发查询时导致数据库连接池耗尽

优化建议：

• 限制最大可访问页码（如不超过1000页）

• 添加缓存层存储热门页数据

方案2：基于主键的游标分页

语法示例：

-- 首次查询
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 20;

-- 后续查询（假设上次返回的最后一条记录ID为100000）
SELECT * FROM orders 
WHERE id < 100000 
ORDER BY id DESC 
LIMIT 20;

技术特点：

• 完全避免OFFSET扫描

• 需要业务表存在自增主键或唯一索引

• 支持无限深度分页

性能测试数据：

工程实践案例：

• 微信朋友圈采用此方案实现动态流分页，支持用户滚动加载数万条记录

• 蚂蚁金服交易系统通过组合create_time和transaction_id实现复合游标分页

注意事项：

• 必须保证排序字段的唯一性，否则可能漏数据

• 删除操作会影响游标连续性，需特殊处理

方案3：覆盖索引优化分页

语法示例：

-- 先通过覆盖索引获取主键
SELECT id FROM orders 
ORDER BY create_time DESC 
LIMIT 100000, 20;

-- 再通过主键关联获取完整数据
SELECT * FROM orders 
WHERE id IN (100001,100002,...,100020);

技术特点：

• 利用索引包含字段避免回表

• 分两阶段执行降低单次查询复杂度

• 需要精心设计复合索引

性能对比（百万级数据）：

索引设计建议：

-- 复合索引设计（排序字段+查询字段）
CREATE INDEX idx_order_query ON orders(create_time DESC, user_id, status);

方案4：子查询优化分页

语法示例：

SELECT * FROM orders o
WHERE (
  SELECT COUNT(*) FROM orders 
  WHERE create_time > o.create_time 
  OR (create_time = o.create_time AND id > o.id)
) < 200000
ORDER BY create_time DESC, id DESC
LIMIT 20;

技术特点：

• 通过子查询精确计算排名

• 避免OFFSET扫描

• 查询语法复杂

性能测试：

• 在10万级数据中表现优异（0.12s）

• 数据量超过500万时，子查询成本显著增加

适用场景：

• 需要精确控制分页位置的业务

• 数据更新频率低的报表系统

方案5：预计算分页数据

实现方案：

1. 创建分页信息表：

CREATE TABLE order_pagination (
  page_num INT PRIMARY KEY,
  min_id BIGINT,
  max_id BIGINT,
  record_count INT,
  update_time TIMESTAMP
);

2. 通过定时任务更新分页信息

3. 查询时直接定位：

SELECT o.* FROM orders o
JOIN order_pagination p ON o.id BETWEEN p.min_id AND p.max_id
WHERE p.page_num = 100000
LIMIT 20;

性能优势：

• 查询时间恒定在5-10ms

• 支持亿级数据分页

• 可扩展为多维度分页（按时间、用户等）

维护成本：

• 需要额外存储空间（约增加5%数据量）

• 数据变更时需同步更新分页表

方案6：SQL_CALC_FOUND_ROWS方案

语法示例：

SELECT SQL_CALC_FOUND_ROWS * FROM orders 
ORDER BY create_time DESC 
LIMIT 20 OFFSET 100000;

SELECT FOUND_ROWS() AS total;

技术特点：

• 单次查询获取数据+总数

• 需要MySQL配置allowMultiQueries=true

• 在8.0版本后性能下降

性能对比（百万级数据）：

淘汰原因：

• MySQL 5.7后官方已不推荐使用

• 在分布式环境中难以维护

• 性能劣于游标+总数独立查询组合

四、分页查询性能优化矩阵

五、真实场景解决方案

场景1：电商商品列表分页

需求：

• 支持按销量/价格/上架时间排序

• 需显示总商品数

• 用户可能跳转到任意页码

推荐方案：

-- 首次查询（获取总数）
SELECT COUNT(*) FROM products 
WHERE status = 'ON_SALE' 
AND category_id = 100;

-- 分页查询（游标+覆盖索引）
SELECT id, name, price FROM products 
WHERE status = 'ON_SALE' 
AND category_id = 100 
AND (
  (sort_field = 'sales' AND (sales, id) < (10000, 200000)) 
  OR 
  (sort_field = 'price' AND price > 50)
)
ORDER BY sort_field, id 
LIMIT 20;

场景2：日志系统深度分页

需求：

• 需查询3个月前的日志

• 支持按时间范围+日志级别筛选

• 日志量超5亿条

推荐方案：

1. 创建时间分区表：

CREATE TABLE system_logs (
  id BIGINT AUTO_INCREMENT,
  log_time DATETIME,
  level VARCHAR(20),
  message TEXT,
  PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (TO_DAYS(log_time)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  -- 更多分区...
);

2. 采用预计算+游标分页：

-- 预计算每日日志量
CREATE TABLE log_daily_stats (
  stat_date DATE PRIMARY KEY,
  total_count INT,
  min_id BIGINT,
  max_id BIGINT
);

-- 分页查询
SELECT l.* FROM system_logs l
JOIN (
  SELECT id FROM system_logs 
  WHERE log_time BETWEEN '2025-01-01' AND '2025-01-31'
  AND level = 'ERROR'
  ORDER BY log_time DESC, id DESC
  LIMIT 100000, 20
) AS tmp ON l.id = tmp.id;

六、性能调优最佳实践

1. 索引黄金法则：

• 分页查询必须建立在索引列上

• 复合索引设计应遵循最左前缀原则

• 示例：ORDER BY create_time DESC, id DESC应创建索引(create_time DESC, id DESC)

2. 查询重写技巧：

   -- 低效写法
   SELECT * FROM orders 
   WHERE user_id = 100 
   ORDER BY create_time DESC 
   LIMIT 100000, 20;
   
   -- 高效写法
   SELECT * FROM orders 
   WHERE user_id = 100 
   AND create_time < '2025-01-01 10:00:00' -- 添加游标条件
   ORDER BY create_time DESC, id DESC 
   LIMIT 20;

3. 连接池配置建议：

• 设置maxWait参数不超过3秒

• 监控慢查询日志（设置long_query_time=1）

• 使用EXPLAIN ANALYZE分析执行计划

4. 缓存策略：

• 对前100页数据实施Redis缓存

• 缓存键设计：page:category_id:sort_field:page_num

• 设置合理的TTL（如30分钟）

七、结论

MySQL分页查询的性能优化是一个系统工程，需要综合考虑数据规模、查询模式、业务需求等因素。对于中小规模数据（<10万条），基础LIMIT OFFSET方案足够；百万级数据推荐游标分页；亿级数据必须采用预计算或分区策略。在实际开发中，建议通过EXPLAIN工具分析查询执行计划，结合慢查询日志定位性能瓶颈，最终形成适合业务场景的分页解决方案。