MySQL LIMIT偏移量大时慢是因为需扫描并丢弃前N行，无法索引定位起点；应改用游标分页，以排序字段值为起点配合索引范围查询，并避免COUNT(*)全表扫描。
MySQL LIMIT 偏移量大时为什么慢
因为 LIMIT 100000, 20 这类语句会让 MySQL 扫描前 10 万行再丢弃，实际执行的是“全扫 + 跳过”，不是跳到第 10 万行开始取。数据量越大，偏移量越大，性能越断崖式下跌。
常见错误现象：SELECT * FROM orders ORDER BY id DESC LIMIT 999999, 20 执行几秒甚至十几秒，而 LIMIT 0, 20 只要几毫秒。
- 使用场景：后台订单列表、日志检索、用户行为流水等需深度翻页的接口
- 根本原因不是“分页本身慢”，而是基于偏移量（offset）的查询无法利用索引定位起点
- 如果
ORDER BY字段无索引，问题会更严重——连排序都要临时文件
用游标分页（cursor-based pagination）替代 OFFSET
不依赖数字偏移，改用上一页最后一条记录的排序字段值作为下一页起点，例如用 id 或 created_at。这样每次查询都能走索引范围扫描。
实操建议：
立即学习“PHP免费学习笔记（深入）”；
- 确保排序字段有索引（如
INDEX idx_created_at (created_at DESC)） - 查询写成
WHERE created_at ，而不是 <code>LIMIT 10000, 20 - 前端需传回上一页末条的
created_at值（或组合主键，如id），不能只传页码 - 注意边界：如果存在相同时间戳，需加入
id防止漏/重，比如WHERE (created_at, id)
PHP 中如何安全生成游标参数
不要拼接字符串构造 WHERE 条件，尤其当游标值来自用户输入时。PHP 层要做类型校验和转义。
示例关键逻辑：
$cursor = $_GET['cursor'] ?? null;
if ($cursor) {
// 假设 cursor 是 base64_encode("2024-05-01 10:20:30|12345")
$parts = explode('|', base64_decode($cursor));
if (count($parts) === 2 && \DateTime::createFromFormat('Y-m-d H:i:s', $parts[0]) && is_numeric($parts[1])) {
$time = $parts[0];
$id = (int)$parts[1];
$stmt = $pdo->prepare(
"SELECT * FROM logs WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20"
);
$stmt->execute([$time, $id]);
}
}
容易踩的坑：
- 直接用
$_GET['cursor']拼 SQL → SQL 注入风险 - 没校验时间格式，导致 PDO 绑定失败或查出空结果
- 游标值未做
base64_encode就传给前端 → URL 中出现特殊字符或被截断 - 忘记在 ORDER BY 中保持和 WHERE 一致的字段顺序（DESC/ASC 必须匹配）
百万级数据下 count(*) 的替代方案
分页时显示“共 XXX 条”很常见，但 SELECT COUNT(*) FROM huge_table 在百万级表上可能比业务查询还慢，且无法缓存。
更务实的做法：
- 用近似值：查
SHOW TABLE STATUS LIKE 'logs'得到rows字段（MyISAM 准确，InnoDB 是估算值，误差通常在 40% 内） - 业务允许时，前端不显示总数，只提供“下一页”“上一页”按钮（游标分页天然适合）
- 如果必须精确总数，且更新不频繁，可单独维护一个计数表，用触发器或应用层异步更新
- 避免在分页接口里同时查数据 + 查总数 —— 两次全表扫描代价翻倍
复杂点在于：游标分页没法跳转任意页码（比如直接输入“第 500 页”），这是设计取舍。如果产品强依赖跳页，得接受 offset 分页在深分页时的性能代价，或加限制（如最多只允许翻到第 100 页）。
