limit 100000,20慢因需扫描、排序并丢弃前100000行，非直接定位；优化用游标分页（where id

为什么LIMIT 100000, 20会越来越慢

因为MySQL必须先扫描并排序前100020行，再丢弃前100000行——不是跳过，是真读、真排序、真丢弃。每多一页，IO和CPU开销就线性增长。更隐蔽的问题是：当offset过大时，查询优化器可能直接放弃走索引，转为全表扫描，哪怕你给ORDER BY字段建了索引。

常见错误现象包括：

• EXPLAIN显示type: ALL（全表扫描），而非range或index
• 相同SQL在页码较小时快（如LIMIT 100, 20），翻到第500页后耗时突增10倍以上
• 加了WHERE update_time > '2025-01-01'却仍慢，说明二级索引未被有效利用

覆盖索引 + 子查询是最通用的优化写法

核心思路是：先用只查主键的轻量查询定位目标ID范围，再用这些ID反查完整记录。这样避免了大偏移量下的重复回表和无效排序。

实操建议：

• 确保ORDER BY字段有索引，且该索引能覆盖排序+过滤条件（例如INDEX (status, create_time)用于WHERE status = 1 ORDER BY create_time）
• 子查询必须显式ORDER BY，否则结果不可靠；外层JOIN比IN更稳定（尤其ID数量多时）
• 不要省略ORDER BY中的方向一致性：如果子查询是ORDER BY id ASC，外层也得按id ASC关联

示例：

SELECT t.* FROM orders t
INNER JOIN (
  SELECT id FROM orders 
  WHERE status = 1 
  ORDER BY id ASC 
  LIMIT 100000, 20
) tmp ON t.id = tmp.id;

主键范围分页适合无限滚动场景

如果你的前端是“下拉加载更多”，而不是“跳转到第N页”，那么WHERE id > last_seen_id LIMIT 20是最快方案——它完全绕过了OFFSET，每次都是索引范围扫描。

MySQL

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

下载

但要注意几个硬约束：

• 主键必须单调递增（自增INT或BIGINT），不能是UUID或业务生成的非序号ID
• 数据不能频繁删除，否则会出现“漏数据”（比如ID=100001被删，下一页从100002开始就跳过了实际存在的100001之后的记录）
• 必须保证ORDER BY与范围条件一致，例如ORDER BY id DESC就要配合WHERE id

错误写法：SELECT * FROM orders WHERE create_time > '2025-05-01' ORDER BY id DESC LIMIT 20——这里create_time和id无顺序关系，无法用索引高效定位。

别忽略ORDER BY字段和索引顺序的匹配问题

即使你建了INDEX (a, b)，ORDER BY a ASC, b DESC仍可能触发Using filesort。MySQL的B+树索引天然只支持同向排序（全ASC或全DESC），混合方向会导致排序失效。

性能影响很直接：

• 一旦出现Using filesort，深分页性能断崖下跌，优化器大概率放弃索引
• 复合索引中，WHERE条件必须是索引最左前缀，否则ORDER BY部分无法复用索引
• 时间字段排序慎用DATETIME精度：如果大量记录create_time相同，MySQL需二次排序，加剧filesort开销

典型陷阱：INDEX (user_id, create_time)支持WHERE user_id = 123 ORDER BY create_time，但不支持WHERE create_time > '2025-01-01' ORDER BY user_id。