翻到第 10000 页接口就超时：MySQL 深分页复盘，延迟关联把 3 秒降到 30ms

翻到第 10000 页接口就超时MySQL 深分页复盘延迟关联把 3 秒降到 30ms导读列表接口前面几页飞快翻到深处就卡死这是深分页的经典症状。LIMIT 100000, 10看着人畜无害实际是 MySQL 先捞 10 万行再丢 99990 行。这篇用一次线上事故复盘explain 怎么看、延迟关联怎么写、为什么 3 秒能掉到 30ms。先说明这个场景零工平台的全部岗位列表用户一页页往下翻。数据量 200 万行左右翻到 10000 页每页 10 条时接口稳定超时。当时线上的 SQL 长这样SELECTid,title,salary,address,company_name,create_timeFROMjob_positionWHEREstatus1ORDERBYcreate_timeDESCLIMIT100000,10;这个 SQL 的问题不是写错了是 MySQL 执行计划决定了它必须先找到第 100000 行。InnoDB 里LIMIT offset, size的语义是扫描 offsetsize 行然后丢掉前 offset 行。翻得越深扫描越多这是 O(n) 的躲不掉。先用 EXPLAIN 把病根坐实EXPLAINSELECTid,title,salary,address,company_name,create_timeFROMjob_positionWHEREstatus1ORDERBYcreate_timeDESCLIMIT100000,10;执行计划大致是这样------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | job_position | NULL | ALL | NULL | NULL | NULL | NULL | 1987532 | 10.00 | Using filesort | ------------------------------------------------------------------------------------------------------------------------两个红灯type ALL全表扫、Extra Using filesort排序没走索引。status 1占 99% 的行建单列索引也没用——优化器一算回表还不如全扫。create_time排序没索引可用每次查询都在内存里 filesort 200 万行。这能不慢吗。优化一覆盖索引 延迟关联思路是分两步先在索引里只查主键完成排序和分页索引树小扫得快再用主键回表取完整行。让排序、过滤、分页全部在一个覆盖索引里完成-- 联合索引(status, create_time DESC, id)ALTERTABLEjob_positionADDINDEXidx_status_time(status,create_time,id);SELECTp.id,p.title,p.salary,p.address,p.company_name,p.create_timeFROM(SELECTidFROMjob_positionWHEREstatus1ORDERBYcreate_timeDESC,idDESCLIMIT100000,10)tJOINjob_position pONp.idt.idORDERBYp.create_timeDESC,p.idDESC;内层子查询只走idx_status_time覆盖索引不碰数据行取 10 个主键外层用主键回表拿 10 行完整数据。回表次数从 10 万次降到 10 次这是提速的核心。优化二彻底干掉 offset——游标分页如果产品允许下一页式翻页不带页码跳转更狠的做法是不要 offset直接用上次最后一条的create_time和id做条件把找第 N 行变成找比某值大的行走索引天然快-- 客户端记录上一页最后一条的 (create_time, id)SELECTid,title,salary,address,company_name,create_timeFROMjob_positionWHEREstatus1AND(create_time,id)(:lastCreateTime,:lastId)-- 元组比较走联合索引ORDERBYcreate_timeDESC,idDESCLIMIT10;元组比较(create_time, id) (?, ?)是 MySQL 对联合索引的经典用法排序和过滤共用同一个索引每一页都只扫 10 行左右翻到第 1 万页和第 1 页一样快。代价是失去了直接跳第 N 页的能力——产品上很多列表其实不需要跳页用加载更多替代完全可行。踩坑实录加了联合索引深分页还是慢现象按上面方案建了idx_status_time并改成延迟关联结果深分页只从 3 秒降到 2.6 秒远没达到预期。说好的 30ms 呢排查过程先EXPLAIN子查询key idx_status_time已经用上了type index索引扫描rows却显示 190 万——索引全扫。问题在status 1的选择性99% 的行都是 status1联合索引里 status 这列几乎不筛数据MySQL 还是得扫 190 万条索引记录才能数到第 10 万行。索引扫描比全表快但没质变。定位思路status这种几乎全是同一个值的列放索引第一位等于没放。真正让深分页变快的不是 status 过滤而是让排序键create_time主导索引让 MySQL 能顺着 create_time 的顺序数到目标位置。我把索引顺序调整(create_time DESC, id)status 过滤放最后或干脆靠 WHERE 的 semi-join 处理。最终解决建idx_time_id (create_time DESC, id)专门喂排序延迟关联的子查询ORDER BY create_time DESC, id DESC LIMIT 100000, 10直接走这个索引顺序扫描从头数 10 万条索引记录每条只读一个主键非常快再回表 10 行。实测第 10000 页稳定 30ms 左右。至于status 1的过滤交给外层 JOIN 后的 WHERE 处理即可——反正分页目标是这个集合内的。-- 最终方案排序索引 延迟关联CREATEINDEXidx_time_idONjob_position(create_timeDESC,id);SELECTp.id,p.title,p.salary,p.address,p.company_name,p.create_timeFROM(SELECTidFROMjob_positionORDERBYcreate_timeDESC,idDESCLIMIT100000,10)tJOINjob_position pONp.idt.idORDERBYp.create_timeDESC,p.idDESC;深分页 关键词搜索别忘了一起优化上面讲的是纯列表翻页。实际线上更常见的是深分页 搜索条件的组合比如搜’保安’ 翻到第 5000 页。这种场景有个陷阱加了AND title LIKE %保安%之后排序索引(create_time, id)里没地方放搜索条件MySQL 要么回表过滤再排序要么对结果集 filesort。我的处理原则搜索条件如果命中不到 10% 的行先让它缩范围如果搜出来还是几十万行搜索词本身就该走全文索引或 ES别硬扛 MySQL 深分页。零工平台的岗位搜索我们最后就是关键词走 Elasticsearch分页走search_after等价于游标MySQL 只承担已知 id 列表回表取详情。分层之后深分页慢查询的帽子彻底摘掉了。可以直接抄的结论EXPLAIN看三样typeALL 是红灯、Extrafilesort 是红灯、rows预估扫描行数深分页的病根是先扫后丢优化方向是少扫覆盖索引只取主键 延迟关联回表排序字段单独建索引create_time DESC, id别让低选择性过滤列占索引首位产品允许就上下一页式游标分页元组比较彻底告别 offset翻多深都快索引不是越多越好status这种高重复值列别放索引前面先想清楚谁是排序主力。深分页是那种看似简单、一深就死的问题但解法也就三板斧覆盖索引、延迟关联、游标。先 EXPLAIN 再动手别一上来就FORCE INDEX。