如何通过存储过程优化MySQL的百万级分页查询？-mysql教程-PHP中文网

存储过程内用limit offset, size最危险，因每次执行重解析、不缓存执行计划，大offset易致索引失效、全表扫描或文件排序，性能波动剧烈且难定位；应改用延迟关联或游标分页。

如何通过存储过程优化mysql的百万级分页查询？

直接在存储过程中写 LIMIT offset, size 是最危险的优化姿势——它把性能问题封装进数据库层，让慢查询更难定位、更难复现、更难压测。

为什么存储过程里用 LIMIT 会越调越慢

MySQL 存储过程每次执行都会重新解析 SQL，不缓存执行计划。一旦 offset 变大，优化器大概率放弃走索引，退化为全表扫描或文件排序。实测中，LIMIT 100000, 20 在千万级表上可能从 0.1 秒升到 3 秒以上，且波动剧烈。

子查询未限定字段（如写成 SELECT * FROM t WHERE ... LIMIT ...）会导致回表放大，IO 翻倍
参数类型与字段类型不一致（比如 @status 是字符串，但 status 字段是 TINYINT），触发隐式转换，让 idx_status_created 完全失效
ORDER BY 和 WHERE 用的不是同一组联合索引字段顺序，比如建的是 (status, create_time)，却写成 WHERE create_time > ? AND status = ?，只能用到 status 的等值部分

延迟关联必须这么写才有效

核心是“子查询只查主键 + 外层 JOIN 主键”，绕过回表和大偏移扫描。不能靠拼接 SQL，也不能用 IN (SELECT ...)。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

子查询必须只返回 id（或唯一主键），且 ORDER BY 和 WHERE 字段要落在同一个联合索引里，例如 (status, create_time)
外层必须用 INNER JOIN 关联原表，写成 JOIN (SELECT id FROM t WHERE status = ? ORDER BY status, create_time LIMIT ?, ?) AS tmp USING(id)
禁止在存储过程中用 CONCAT + EXECUTE 拼动态 SQL，否则监控工具看不到真实慢查询来源，执行计划也失效

CREATE PROCEDURE get_orders_by_status(IN p_status TINYINT, IN p_offset INT, IN p_size INT)
BEGIN
  SELECT o.* FROM orders o
  INNER JOIN (
    SELECT id FROM orders 
    WHERE status = p_status 
    ORDER BY status, create_time 
    LIMIT p_offset, p_size
  ) AS tmp USING(id);
END

游标分页更适合存储过程的天然特性

存储过程有状态保持能力，用 INOUT 参数传入上一页最后一条的 id 或 (create_time, id) 组合值，比应用层维护游标更可靠。

排序字段必须单调可比较：优先用自增 id；若用时间字段，必须补 id 防止毫秒重复，写成 ORDER BY create_time, id
WHERE 条件要写成 WHERE create_time > @last_time OR (create_time = @last_time AND id > @last_id)，避免漏/重数据
无法支持跳页（如“跳到第 500 页”），业务强依赖跳页时，别硬套游标模式