LIMIT offset,size在百万数据下越来越慢，因为MySQL必须扫描并排序offset+size行后丢弃前offset行，如LIMIT 1000000,20实际读取1000020行，99.99%被浪费，IO和CPU开销剧增；OFFSET无法利用索引随机定位，本质是顺序跳过，且SELECT *加剧回表、传输与内存开销。
为什么LIMIT offset, size在百万数据下会越来越慢
因为MySQL必须先扫描并排序offset + size行，再丢弃前offset行。比如LIMIT 1000000, 20，实际要读1000020行，其中99.99%被丢弃。IO和CPU都浪费在无效数据上。
常见错误现象包括：第1页毫秒级，第500页卡顿数秒，EXPLAIN显示type = ALL或Extra = Using filesort；更糟的是，当ORDER BY字段没索引，还会触发磁盘排序。
- 偏移量越大，扫描行数线性增长，不是常数时间
- SELECT * 加剧问题：加载不需要的字段 → 网络传输+内存拷贝+回表IO三重开销
- OFFSET跳过操作无法利用索引定位，本质是顺序跳过，不是随机访问
延迟关联（Covering Index + JOIN）怎么写才有效
核心是让子查询只走索引（不回表），主查询再按ID批量回表。这要求子查询结果集必须只含索引列（最好是主键），且排序字段必须包含在覆盖索引中。
错误写法：SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20 —— 全表扫描+文件排序。
正确写法：
ALTER TABLE orders ADD INDEX idx_create_time_id (create_time, id); SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC, id DESC LIMIT 1000000, 20 ) AS tmp ON o.id = tmp.id;
- 索引必须包含
ORDER BY字段+主键（如create_time, id），否则子查询无法避免filesort - 子查询
SELECT id必须严格匹配索引最左前缀，不能多选其他字段 - JOIN后主查询走主键
PRIMARY，是聚簇索引查找，快于全表扫描 - 如果业务允许升序分页，用
create_time ASC, id ASC索引效果更稳（InnoDB对ASC索引优化更好）
游标分页（Cursor-based Pagination）适用哪些场景
它不依赖OFFSET，而是用上一页最后一条记录的排序字段值作为下一页起点，时间复杂度恒定O(size)，与页码无关。
典型错误是直接用WHERE id > ?但没加ORDER BY，导致结果不可控或漏数据。
正确用法（以id DESC为例）：
-- 第1页 SELECT * FROM orders ORDER BY id DESC LIMIT 20; -- 假设返回的最小id是 987654321 -- 第2页（关键：WHERE + 同向ORDER BY） SELECT * FROM orders WHERE id < 987654321 ORDER BY id DESC LIMIT 20;
- 必须保证
WHERE条件和ORDER BY方向一致（都DESC或都ASC），否则索引失效 - 排序字段必须有唯一性保障（如主键），否则相同值会导致跳页或重复
- 不支持跳转到任意页（比如“跳到第1000页”），只适合无限滚动、下拉加载等连续翻页场景
- 前端需缓存上一页末尾的游标值（如
last_id），不能靠页码计算
容易被忽略的细节和边界情况
很多优化方案在测试环境跑得飞快，上线后却出问题，往往栽在这几个点上：
-
ORDER BY字段有NULL值：MySQL默认把NULL排在最前（ASC）或最后（DESC），若业务未处理，游标分页可能漏掉NULL行 - 复合索引顺序错位：比如
WHERE status = 1 AND create_time BETWEEN ...+ORDER BY create_time，索引应为(status, create_time)，而非(create_time, status) - 数据更新导致游标失效：分页过程中，新插入记录可能挤入当前游标范围，造成“幻读”或重复；解决方案是加时间戳辅助（如
WHERE (create_time, id) ） - 字符集或collation影响排序：不同collation下
ORDER BY name结果可能不一致，导致游标错位，尤其在多语言数据中
