面试题：MySQL 中 LIMIT 100000000, 10 和 LIMIT 10 的执行速度是否相同？

在 MySQL 中，LIMIT 100000000, 10 和 LIMIT 10 的执行速度 完全不同，且 LIMIT 10 明显更快。以下是详细分析：

LIMIT 10
- 直接返回前10行：MySQL 会从结果集的开头开始扫描，找到前10条数据后立即返回，无需跳过任何行。
- 扫描行数少：仅需处理10行数据，性能极高。
LIMIT 100000000, 10
- 必须跳过1亿行：MySQL 需要扫描并丢弃前100,000,000行数据，然后再返回接下来的10行。
- 扫描行数巨大：即使最终只返回10行，MySQL 仍需处理100,000,010行数据（100,000,000行偏移 + 10行结果），导致性能急剧下降。

LIMIT 10：
- MySQL 执行全表扫描，直接返回前10行，速度极快（毫秒级）。
LIMIT 100000000, 10：
- MySQL 必须扫描100,000,010行数据，性能可能从秒级下降到分钟级，甚至导致查询超时。

LIMIT 10：
- 利用索引快速定位前10行，性能接近无索引场景。
LIMIT 100000000, 10：
- 虽然索引可加速数据定位，但仍需定位到第100,000,001行的位置，性能仍远低于 LIMIT 10。
- 示例：若按主键索引排序，MySQL 需要遍历主键索引树至第100,000,001个节点，再取后续10行。

偏移量的代价
- MySQL 的 LIMIT offset, size 并非直接“跳到第 offset 行”，而是逐行扫描并丢弃前 offset 行，直到找到 size 条结果。
- 例如：LIMIT 100000000, 10 需要扫描 100000000 + 10 = 100000010 行，而 LIMIT 10 仅需扫描10行。
回表操作放大开销
- 若使用非主键索引（如 WHERE name = 'xxx'），MySQL 需通过索引定位主键 ID，再回表查询主键索引中的完整数据。
- LIMIT 100000000, 10 会导致100,000,010 次回表操作，而 LIMIT 10 仅需10次。
内存和磁盘 I/O 压力
- 大偏移量查询需要缓存大量中间结果（如临时表、排序缓冲区），可能导致内存不足，触发磁盘交换（swap），进一步降低性能。

虽然 LIMIT 100000000, 10 本身性能差，但可以通过以下方法优化：

使用游标分页（Cursor-based Pagination）
通过上一页最后一条记录的主键 ID 作为下一页的起点，避免 OFFSET。
示例： -- 传统分页（慢） SELECT * FROM table ORDER BY id LIMIT 100000000, 10; -- 优化后（快） SELECT * FROM table WHERE id > 100000000 ORDER BY id LIMIT 10;

  CREATE INDEX idx_name_id ON table (name, id);  -- 查询 name 和 id 时可覆盖索引

  SELECT a.* 
  FROM table a
  INNER JOIN (
      SELECT id FROM table WHERE name = 'xxx' ORDER BY id LIMIT 100000000, 10
  ) b ON a.id = b.id;

LIMIT 10：速度快，适合常规分页或小数据量查询。
LIMIT 100000000, 10：速度极慢，适合数据量小或偶尔执行的场景。
实际开发中：
- 避免大偏移量分页，优先使用游标分页。
- 确保查询字段有索引，减少扫描和回表开销。
- 结合业务需求，对深层分页进行特殊处理（如缓存、预加载）。

THE END