目录一、慢查询从哪里来先建立定位链路再谈优化一慢的定义与慢查询日志1、采集口径几个开关决定你能看见什么2、聚合先修调用量大的慢再修单次极慢的二一条语句在数据库内部的完整旅程三治理闭环定位不是终点二、读懂执行计划每一列都在回答一个问题一关键列地图每列回答什么二type访问类型的阶梯三Extra计划之外的重要信号四估算与真实三种手段互相印证三、索引的底层B 树、聚簇索引与回表一B 树为磁盘页设计的数据结构二聚簇索引与二级索引两棵树两种叶子三回表二级索引的隐藏成本四、索引设计联合索引、覆盖与下推一最左前缀联合索引的匹配规则二覆盖索引让回表消失三索引条件下推把过滤下沉到引擎层四索引失效的常见现场五优化器为什么不走你的索引五、排序与分页两类最顽固的慢查询一排序的代价两种执行方式二让排序消失ORDER BY 与索引对齐三深分页偏移量的真实语义四深分页的两种修法六、一次完整的治理实战从慢日志到执行计划再到修复一读现场证据先于假设二修复推演让索引接管过滤与排序三防回归把结论固化下来七、总结一条主线与三道防线可参考的文章与资料干货分享感谢您的阅读一条返回 10 行的查询扫描了一百一十四万行一条明明建了索引的语句在执行计划里赫然写着全表扫描一个翻到第一万页的列表接口把整个数据库拖入泥潭。这三个现场看似无关背后却是同一套知识在起作用数据在磁盘上如何组织、优化器如何选择路径、每一种访问方式的真实代价是多少。关于慢查询团队里流传最广的做法是哪个字段慢就给哪个字段加索引。这个做法偶尔有效更多时候无效还有些时候有害——索引加了写入变慢了查询却没快因为优化器根本没用它或者用了也照样要逐行回表。凭感觉加索引之所以不可靠是因为它跳过了整个因果链没有回答这条语句到底慢在哪一步也没有验证改动之后访问路径是否真的变了。这篇文章要把这条因果链完整走一遍先建立慢查询的发现与定位链路搞清楚慢查询日志和执行计划各自回答什么问题再深入索引的底层结构理解 B 树、聚簇索引与回表这三个概念如何决定了每一种查询的代价然后进入索引设计的核心——联合索引的列序、覆盖索引与条件下推接着处理两类最常见的顽疾排序与深分页最后用一个完整的实战案例把方法论串起来。读完之后面对任何一条慢语句你都应当能够按同一套链路给出定位、解释与修复而不是撞运气。一、慢查询从哪里来先建立定位链路再谈优化一慢的定义与慢查询日志慢必须先有口径。同样一条耗时 200 毫秒的语句出现在后台报表里无人在意出现在下单主链路上就是事故。工程上的通行做法是给数据库设一个全局阈值超过阈值的语句被记录下来再结合业务链路的耗时预算判断轻重。承担这个记录职责的就是慢查询日志。1、采集口径几个开关决定你能看见什么慢查询日志由几个参数共同决定采集范围。slow_query_log是总开关线上应当常开——它的开销远小于它提供的信息价值。long_query_time是阈值默认 10 秒几乎等于不采集实践中常设为 0.5 到 1 秒对延迟敏感的核心库甚至可以阶段性调到 0.1 秒提前几周暴露那些正在缓慢恶化的语句。log_queries_not_using_indexes会把没有使用索引的语句也记录下来无论快慢配合log_throttle_queries_not_using_indexes限制每分钟的记录条数避免刷屏。min_examined_row_limit则从另一个方向降噪扫描行数低于该值的语句不记录把注意力留给真正有分量的目标。单条日志的字段要会读。Query_time是总耗时注意它包含了等锁时间Lock_time单独列出等锁部分如果这个值高方向就不是索引而是锁冲突Rows_sent是返回给客户端的行数Rows_examined是引擎实际扫描的行数。最有价值的判读就藏在最后两者的比值里返回 10 行、扫描一百多万行说明访问路径在做大量无效功——这正是索引问题的典型指纹。2、聚合先修调用量大的慢再修单次极慢的慢日志是流水账直接逐条看会陷入两个误区一是被单次最慢的语句吸引全部注意力而它可能一天只执行一次二是忽略那些单次只有 1 秒多、但每分钟执行几百次的语句后者对数据库的总压力远大于前者。正确的做法是聚合把语句中的字面值替换成占位符归并成模板也叫语句指纹再按总耗时 单次均值 × 执行次数排序从头部开始治理。pt-query-digest是这类聚合的经典工具mysqldumpslow是自带的轻量替代8.0 的performance_schema中events_statements_summary_by_digest表则可以在不开慢日志的情况下直接查询聚合结果。聚合还有一个容易被忽略的价值区分必然慢与偶发慢。同一模板的所有样本都慢说明访问路径本身有问题方向是索引与语句改写只有零星样本慢方向就完全不同——锁等待、缓冲池抖动、主机资源争抢此时去改索引是南辕北辙。区分两者的操作很简单聚合报告里看该模板的耗时分布均值与九十五分位都高是必然慢均值正常而最大值离群是偶发慢。偶发慢的排查工具也不同Lock_time字段、SHOW ENGINE INNODB STATUS里的锁等待、performance_schema的等待事件这些线索都指向执行环境而不是执行计划。本文聚焦必然慢但把两者分开是所有排查的第一步——用对方向比用力更重要。二一条语句在数据库内部的完整旅程要判断慢在哪一步得先知道一条语句从进门到出门经过哪些环节。以 MySQL 为例连接器负责管理连接与校验权限解析器做词法与语法分析生成解析树预处理器检查表和列是否存在、展开视图与别名优化器基于统计信息决定用哪个索引、以什么顺序连接哪些表执行器按计划逐行调用存储引擎接口最后由 InnoDB 引擎在缓冲池与 B 树上完成真正的读写。顺带一提老版本中的查询缓存在 8.0 已被整体移除——命中率低、失效风暴代价高它的存在本身就是一个负优化。每个环节都有自己的慢法。连接器层面是连接数打满与连接泄漏解析层面是拼接出来的语句无法复用执行计划导致的硬解析开销优化器层面是统计信息过期或区分度误判导致选错索引执行器与引擎层面则是扫描行数过大、回表次数过多、缓冲池命中率低。定位慢查询的第一步就是把问题归到正确的环节——本文的主战场是后两类它们占了慢查询的绝大多数也是索引设计能直接解决的部分。这里必须厘清两个容易混淆的口径。慢查询日志记录的是真实执行的结果属于事后观测EXPLAIN展示的是优化器打算怎么执行属于事前预测。两者可能不一致优化器的估算基于采样统计真实数据分布可能与采样偏差很大。所以定位时两者都要看——慢日志告诉你哪条语句慢、慢了多少、扫了多少行执行计划告诉你它为什么慢、慢在哪一步。只看其中一个结论都可能跑偏。三治理闭环定位不是终点把慢查询当成一次性任务修完就走三个月后同样的问题会换一张表重演。成熟的做法是把整个过程固化成闭环发现监控告警与慢日志、聚合模板归并与排序、解释执行计划分析、归因索引、排序、分页还是统计信息、优化建索引或改写语句、验证对比扫描行数与耗时、固化把经验变成上线规范。闭环里最常被省略的是最后两步。验证的标准动作是优化前后各跑一次真实参数的语句对比Rows_examined与耗时并确认执行计划在多组参数下稳定——只在开发库的小表上看一眼快了就收工等于没有验证因为小表上什么访问路径都快。固化的标准动作是把规则写进流程新表评审必须附带这张表将要承接的查询清单新查询上线前必须附带执行计划。做到这两条慢查询的增量才会真正下降。二、读懂执行计划每一列都在回答一个问题EXPLAIN的输出有十几列逐列背释义是低效的学法。更有效的方式是把每一列理解成一个问题的答案并且知道读的顺序不是从左到右而是先看type、rows、Extra这三处信息密度最高的地方再用其余列补全细节。一关键列地图每列回答什么id与select_type回答语句被拆成了几步、谁先谁后id相同自上而下执行id越大越先执行一旦看到DEPENDENT SUBQUERY要立刻警惕——它意味着子查询会对外层的每一行执行一次行数一大就是灾难。table回答这一步在访问哪张表包括物化的派生表derived。possible_keys与key回答候选了哪些索引、实际选了哪个候选有值而实际为空说明优化器权衡之后放弃了索引这不是故障而是一次它认为划算的选择背后的成本账我们在第四部分细算。key_len是被低估的一列它回答联合索引真正用到了几列。按参与定位的列的类型字节数累加一个INT4 字节VARCHAR(64)在 utf8mb4 下最多 64×4 再加 2 字节长度前缀可空列再加 1 字节标志位。拿计算结果和key_len对照就能精确知道范围条件之后的列有没有参与定位——这是验证联合索引设计是否生效的最直接手段。rows与filtered回答预计扫描多少行、谓词过滤后剩下百分之多少务必记住这是基于统计信息的估算值要与慢日志里的真实Rows_examined互相印证。二type访问类型的阶梯type描述的是访问这张表的方式它天然构成一个从快到慢的阶梯。const与system通过主键或唯一索引等值定位最多一行代价近乎常数。eq_ref连接场景下对驱动表的每一行被驱动表按主键或唯一索引精确定位一行是连接的理想形态。ref普通二级索引的等值访问可能命中多行。range索引上的一个连续区间BETWEEN、比较运算、IN都会落在这里。index遍历整棵二级索引——虽然名字里有 index但它是全索引扫描只是因为二级索引比聚簇索引窄、顺序读代价低于全表扫描才排在ALL之前。ALL全表扫描聚簇索引整棵遍历。两条判读经验值得写在墙上。第一线上高频查询的底线是range出现index或ALL必须能说出原因——要么表确实很小要么就是待修的问题。第二type必须和rows一起读才有结论小表全扫不一定是问题大表的ref也可能因为命中几十万行而慢得离谱。阶梯给的是定性rows给的是定量两者相乘才是代价。三Extra计划之外的重要信号type说明怎么访问表Extra说明访问之外还要额外做什么。几个高频信号必须形成条件反射。Using index是好消息查询所需的列全部由二级索引提供零回表这就是覆盖索引生效的标志。Using index condition是中性信号索引条件下推在工作部分过滤被提前到了回表之前——它说明下推在补救但如果语句仍慢应考虑调整联合索引列序让该列直接参与定位。Using where表示引擎取回的行还要在服务层再过滤一遍它与ALL组合出现时意味着过滤条件完全没有进入索引。需要重点治理的是两个Using filesort表示需要额外排序内存放不下时会落盘做归并数据量一大代价陡增Using temporary表示需要临时表常见于GROUP BY、DISTINCT与复杂去重。两者同时出现时先消临时表再消排序——临时表往往连带一次全量物化是更重的那一个。消除它们的通用思路都一样让索引的有序性替执行期的补救动作干活这条思路在第五部分展开。四估算与真实三种手段互相印证EXPLAIN是预测预测可能失真。8.0.18 起提供的EXPLAIN ANALYZE会真正执行语句输出每个算子的实际耗时、实际行数与循环次数拿它与估算对照统计信息失真的问题一眼可见。更进一步的optimizer_trace能展示优化器的完整决策过程候选路径各自的成本数字、为什么放弃某个索引、排序选择了哪种模式。日常定位用EXPLAIN足够疑难杂症——尤其是为什么不走我的索引这类问题——就需要后两者出场。三种手段加上慢日志的真实扫描行数构成了从预测到实证的完整证据链。三、索引的底层B 树、聚簇索引与回表执行计划里的每一个词——ref、range、回表、覆盖——都对应着 B 树上的一种具体动作。不理解树的结构读计划就只能停留在背口诀的层面理解了结构所有优化手段都可以自己推导出来。一B 树为磁盘页设计的数据结构InnoDB 的数据按 16KB 的页组织页是磁盘读写的最小单位。索引结构的设计目标由此确定让一次查找触碰的页尽量少。B 树的解法是极致地压低树高——非叶子节点不存数据只存键值与子页指针一个 16KB 的页可以容纳上千个键这样三到四层的树就能覆盖千万到十亿行一次主键点查从根走到叶只需要三四次页访问而根节点与多数内部节点常驻缓冲池真正落到磁盘的读取往往只有一两次。对比其他候选结构B 树的胜出是工程权衡的结果。哈希索引等值查找是常数时间但完全不支持范围查询与排序二叉搜索树每个节点只有两个分支千万行的树高达二十多层每层都可能是一次磁盘读跳表的节点粒度与磁盘页对不齐。B 树还有一个决定性的细节所有数据都在叶子层叶子之间用双向链表串联于是范围扫描变成了定位区间起点然后沿链表顺序遍历——顺序读恰好是磁盘最擅长的动作。键值有序存放是索引一切能力的前提后面所有失效场景的判定也都可以还原为这个写法是否破坏了对有序性的利用。有序也有维护成本理解它才能理解索引不能滥建。每一次插入、更新、删除所有相关索引树都要同步调整页写满触发页分裂把一半记录搬到新页两个页都留下空洞对二级索引的非唯一变更InnoDB 用变更缓冲把随机写攒起来延迟合并但这只是缓解不是豁免。一张表上的每一条索引都在为每一次写入收税这就是单表索引一般不超过五六条这类经验值背后的账本——不是树装不下而是写放大与缓冲池占用会逐条累积。二聚簇索引与二级索引两棵树两种叶子InnoDB 的表本身就是一棵按主键组织的 B 树这棵树叫聚簇索引叶子节点里存的是完整的行记录找到主键就等于找到了数据。你手工创建的其余索引都是二级索引每个二级索引是另一棵独立的 B 树按索引列的值排序但叶子节点里存的不是整行数据而是索引列的值加上主键值。二级索引的叶子存主键而不是物理地址这个设计有两个直接推论它们共同构成了主键设计的准则。第一主键要短主键值会被复制进每一棵二级索引的每一个叶子节点一个 8 字节的自增整型和一个 36 字节的字符串形态的随机标识符在有五六个二级索引的表上体积差异会被放大数倍进而影响缓冲池里能装下多少索引页。第二主键要趋势递增聚簇索引按主键有序存放随机主键会让插入位置在整棵树上乱跳频繁触发页分裂留下大量半满的页自增主键则永远追加在最右侧页写满即止。用短的自增整型做主键不是编码习惯而是从聚簇结构一步步推出来的结论。三回表二级索引的隐藏成本沿二级索引查到的只是主键要拿整行就必须回到聚簇索引再做一次从根到叶的点查——这个动作叫回表。单看一次回表不算贵但它是逐行发生的WHERE city 杭州命中一万行就是一万次聚簇索引点查。更麻烦的是顺序问题二级索引按 city 有序这批行的主键在聚簇索引里却是乱序的一万次点查落到缓冲池之外时就是一万次随机读。固态盘缩小了随机读与顺序读的差距但缓冲池的反复淘汰、页的解析开销依然真实存在。回表解释了执行计划里很多反直觉的现象。为什么优化器有时放着索引不用而选择全表扫描因为它算过账命中比例高到一定程度逐行随机回表的总代价会超过一次顺序的全表扫描此时全扫反而是更快的路径。为什么SELECT *被反复告诫少用因为查询列越多越不可能被索引覆盖回表就越无法避免。减少回表的三条路——覆盖索引、只取需要的列、索引条件下推——本质是同一个思想让二级索引自己能回答的问题尽量多去主键树上跳的次数尽量少。这三条路正是下一部分的主角。四、索引设计联合索引、覆盖与下推单列索引解决不了真实业务的查询形态——真实查询几乎总是多条件组合加排序。索引设计的核心技能是联合索引把多个列按正确的顺序组织进同一棵树让定位、过滤、排序尽可能都在树上完成。一最左前缀联合索引的匹配规则联合索引(city, age, name)的叶子层排列规则是先按 city 排序city 相同的记录内部按 age 排序city 与 age 都相同的再按 name 排序。这个排列决定了它能回答哪些问题条件必须从最左列开始、连续地命中才能收窄成树上的一个区间。city 杭州能定位一个连续区间city 杭州 AND age 21把区间进一步收窄三列全等值是最理想的形态。而一旦跳过左列——比如只有age 21——所有 age 等于 21 的记录散落在每个 city 段内部树上不存在一个连续区间能框住它们索引对定位就失效了。范围条件是另一个关键分界city 杭州 AND age 20 AND name 陈立中age 的范围条件之后name 的有序性被打断——在 age 大于 20 的整个区间里 name 不再有序所以 name 无法参与区间定位只能作为过滤条件使用。由此得出联合索引的列序口诀等值列在前排序列居中范围列殿后。同理可以推出LIKE的边界LIKE 杭%是前缀匹配等价于一个范围区间可以利用索引LIKE %州前导通配有序性帮不上任何忙索引整体失效。设计完成后不必靠记忆判断到底用上了几列——看key_len按列类型字节数一加便知。两个边角情形值得补充。其一IN列表在优化器眼里是多个等值区间的并集而不是范围所以city IN (杭州,上海) AND age 21里 age 仍然可以参与定位引擎会对每个 city 值分别下钻——但列表过长时区间数量爆炸8.0 用范围优化器的内存上限约束它超限后退化为索引扫描。其二8.0.13 引入的跳跃扫描让缺最左列不再绝对失效当最左列基数很低比如只有几个枚举值时优化器会把它展开成逐值循环相当于替你补上了最左条件但它只在低基数前导列下有利可图不能成为设计时省略最左列的理由。二覆盖索引让回表消失回表的根源是索引里没有查询需要的全部列。反过来如果查询涉及的所有列——包括SELECT列表、WHERE条件、ORDER BY排序列——全部包含在某个二级索引里引擎在索引上就能拿到全部答案回表整个消失执行计划的Extra会亮出Using index。这就是覆盖索引。别忘了二级索引叶子天然携带主键值所以查询里的主键列不需要额外加进索引定义。覆盖的收益经常是数量级的同样命中一万行回表版本是一万次随机点查覆盖版本是索引叶子上的一段顺序遍历。但覆盖索引不是免费的索引更宽意味着每次写入要维护更大的树、占用更多缓冲池。所以它的定位是为高频读查询定制的空间换时间应当明确列出目标查询、验证收益后再建而不是给每张表无差别地铺。还要警惕一个反向破坏一条本来被覆盖的查询因为某次需求随手加了一个SELECT列覆盖悄然失效、回表重新出现——这类回归恰恰要靠上线前重跑执行计划来拦截。长字符串列还有一个与覆盖相互冲突的技巧前缀索引。对邮箱、地址这类长列INDEX (email(12))只把前 12 个字符放进树里索引体积和写入代价大幅下降前缀长度的选法有明确算法——统计不同前缀长度下的去重率COUNT(DISTINCT LEFT(email, n)) / COUNT(*)取逼近完整列区分度的最小 n。但要清楚它的代价前缀不是完整值引擎判断相等时必须回表核对全文因此前缀索引永远无法充当覆盖索引也无法支撑排序。它与覆盖索引分别站在省空间与省回表两端选哪个取决于这条查询的瓶颈在哪一侧。三索引条件下推把过滤下沉到引擎层考虑WHERE city 杭州 AND name LIKE 张%索引为(city, name)。city 等值之后如果还有范围或者跳列name 无法参与区间定位——在没有条件下推的年代5.6 之前引擎把 city 命中的每一条索引记录都回表取整行再交给服务层去判断 name 条件一万条命中、九千八百多条回表后被丢弃全部是无效功。索引条件下推5.6 起默认开启改变了这个分工name 就在索引记录里引擎在遍历索引时先判断name LIKE 张%只有通过的记录才回表——回表次数从一万次直接降到一百二十次执行计划的Extra显示Using index condition。下推的本质是一条朴素的原则凡是索引里已经有的列过滤就应该发生在回表之前。它也纠正了一个常见误解——联合索引里不参与定位的列并非无用它们正是下推过滤赖以工作的输入。当然下推是补救不是设计如果某个列的过滤对性能至关重要更好的做法是调整列序让它直接参与定位。四索引失效的常见现场失效场景可以罗列很多条但它们服从同一条规律让引擎无法直接利用键值的有序性。对索引列施加函数或运算WHERE DATE(create_time) 2026-09-10——索引存的是 create_time 的原始值不是函数结果正确改法是闭开区间create_time 2026-09-10 AND create_time 2026-09-11。隐式类型转换——字符串列用数字比较phone 13800000000等价于对列套了转换函数索引作废反过来数字列用字符串比较则安全但没必要冒险。隐式字符集转换——utf8 表和 utf8mb4 表做连接连接列要做字符集转换同样等价于套函数这类问题藏得深、危害大统一字符集是唯一正解。其余几个现场前导通配的LIKE %建国前面已经解释OR两侧只要有一侧没有索引整体就退化为全扫改法是补齐索引或拆成UNION ALL否定条件!、NOT IN不是绝对失效而是范围太宽时优化器判定不值得走索引跳过联合索引最左列前面已推导。与其背清单不如养成一个动作习惯每次改动语句或索引后重跑执行计划检查三件事——key是否还在、type是否退化、rows是否暴涨。清单会漏证据不会。五优化器为什么不走你的索引明明有索引却不用是最高频的困惑多数时候答案是优化器算了一笔账认为不用更便宜。这笔账有三个输入。第一是统计信息InnoDB 对索引页做采样估算每个索引的基数不同值的个数第二是行数估算用基数推算谓词能命中多少行体现为计划里的rows与filtered第三是代价比较索引定位加逐行回表的随机读代价对比全表顺序扫描的代价取低者。三个典型的误判现场对应三种处置。统计信息过期大批量写入或删除之后采样还是旧的rows估算严重失真ANALYZE TABLE重新采样通常立竿见影。区分度太低性别、状态这类列基数只有个位数命中一半的行还要逐行回表不如顺序全扫——这种列本就不该单独建索引要用也是放进联合索引的后位。范围过宽加取整行区间命中占比高、回表次数逼近总行数时优化器宁选全扫摊平成本处置是收窄谓词、减少返回列或用覆盖索引把回表从公式里消除。至于FORCE INDEX把它当止血带而不是处方它绕过的是成本估算而不是成本本身数据分布再次漂移时被固定死的计划会反过来变成新的慢查询。长期解永远是修统计、修索引、修语句让优化器自己算出正确答案。五、排序与分页两类最顽固的慢查询一排序的代价两种执行方式ORDER BY无法利用索引时引擎要做显式排序即Extra里的Using filesort。它有两种执行方式分岔点是参数max_length_for_sort_data单行需要返回的内容不超过它走全字段排序——把排序列与全部返回列一起放进sort_buffer排完直接输出一次遍历完成代价在内存占用超过它则退到行号排序——sort_buffer里只放排序列与主键排完按序逐个回表取整行省了内存却多付一轮随机回表。sort_buffer_size装不下待排数据时两种方式都会切块写磁盘临时文件再做多路归并代价进一步上台阶会话级optimizer_trace里的sort_mode与number_of_tmp_files可以精确观测这一切。理解取舍即可看懂引擎的倾向MySQL 尽量用内存换磁盘装得下就选全字段。带LIMIT的排序还有第三条路当只需要前 N 行且 N 足够小时优化器会改用优先队列——维护一个 N 大小的堆扫一遍数据始终只留最优的 N 条既不需要装下全量数据也不需要临时文件optimizer_trace里对应filesort_priority_queue_optimization字段。这解释了为什么同一条排序语句LIMIT 20很快、LIMIT 100000, 20却很慢前者是一个 20 大小的堆后者的堆要装下十万零二十条优先队列的优势荡然无存。但所有这些机制都是补救。最好的排序是不排序——让读取顺序天然等于目标顺序这件事只有索引能做到。二让排序消失ORDER BY 与索引对齐索引对排序的贡献分三种情形。完全对齐WHERE city 杭州 ORDER BY age配合索引(city, age)city 等值锁定后区间内 age 天然有序读出即有序filesort彻底消失。半对齐WHERE city IN (杭州,上海) ORDER BY age每个 city 区间内部 age 各自有序但两段之间仍需归并排序无法完全消除——IN的多值在这里起到了类似范围的破坏作用。方向冲突ORDER BY create_time DESC, id ASC两列方向相反普通升序联合索引无法同时满足8.0 的降序索引(create_time DESC, id ASC)可以按混合方向定义让这类排序也回到读出即有序的轨道。把排序纳入考虑后联合索引列序的完整口诀就齐了等值列在前排序列居中范围列殿后。排序列一旦被放在范围列后面有序性在范围处断裂排序就必然回到执行期补救。换句话说排序从来不是执行期的问题而是设计期的问题——Using filesort出现的那一刻该反思的是索引定义。三深分页偏移量的真实语义LIMIT 100000, 10的语义常被误解为跳过十万行。执行层面不存在跳过引擎沿着访问路径逐行读取服务层逐行计数数到第十万行之前的全部丢弃然后返回十行。如果这条路径走的是二级索引十万零十行中的每一行都可能伴随一次回表。有效产出不足万分之一代价却与偏移量严格成正比——页码越深单页耗时越长这就是深分页的数学本质。深分页还有两个隐蔽的连带伤害。其一当优化器估算出十万次回表的成本高于全表扫描时计划会退化成ALL加filesort一个深页请求不但自己慢还会用无关页污染缓冲池拖慢全库其他查询。其二是翻页漂移翻页期间有新数据插入偏移基准整体后移用户会看到重复或遗漏的记录——偏移分页在持续变动的数据上语义本身就不稳定。顺带一条工程忠告爬虫与导出任务最喜欢打深页接口层应当设置偏移上限。分页还常常连带一个隐藏成本页码控件需要总数于是每次翻页都跟着一条SELECT COUNT(*)。在 InnoDB 上这条语句没有捷径——多版本并发控制下每个事务看到的行数可能不同引擎无法维护一个全局计数只能实打实地遍历一棵树优化器会挑最小的那棵索引来数。表一大数总数比取那一页还慢。工程上的解法都在数据库之外列表页改成上一页/下一页从根上取消总数需求确需展示的场景用计数表或缓存维护近似值并接受它的短暂滞后。四深分页的两种修法第一种是延迟关联思路是先在最窄的树上翻页再回表。用子查询在覆盖索引(create_time, id)上完成十万行的遍历——只数索引记录、零回表——拿到目标页的十个主键再用这十个主键连接原表取整行。回表次数从十万零十次压缩到十次页深带来的代价虽然仍在索引遍历还是要数十万条但已经降到当前交互形态下的最低值。它适合必须支持任意跳页的后台与报表场景。第二种是游标分页也叫键集分页思路是消灭偏移。记住上一页最后一条记录的排序键值下一页直接用条件定位WHERE (create_time, id) (2026-09-10 12:00:00, 88231) ORDER BY create_time DESC, id DESC LIMIT 10。条件直接落在索引区间上type是range代价与页深完全无关——第一页与第一万页同样快且天然免疫翻页漂移。两个实现要点排序键必须唯一化通常在时间列后追加主键消除同一秒内多条记录的并列歧义交互上它只支持上一页/下一页不支持跳页。选型判据由此清晰能改交互就用游标分页必须跳页就用延迟关联两者也常常共存——前台信息流走游标后台导出走延迟关联加偏移上限。六、一次完整的治理实战从慢日志到执行计划再到修复把前面的方法论压在一条真实形态的语句上走一遍。订单表trade_order约两千万行主键id自增已有索引idx_user (user_id)。慢日志聚合报告的头部是这条模板日均执行四万次SELECT * FROM trade_order WHERE user_id ? AND status 1 ORDER BY create_time DESC LIMIT 20;一读现场证据先于假设慢日志样本显示Query_time约 1.8 秒Lock_time接近零——先排除锁的方向。Rows_sent是 20Rows_examined是 31 万返回与扫描之比达到一比一万五典型的访问路径浪费。执行计划印证了猜测type为refkey是idx_userrows估算 30 万Extra里Using where; Using filesort双双在列。翻译成人话这个用户有 31 万条订单引擎沿idx_user找到全部 31 万条逐条回表取整行在服务层过滤status 1把幸存的几万行全部做一次排序最后只留 20 行。回表、过滤、排序三重浪费叠加每一重都在放大代价。二修复推演让索引接管过滤与排序按等值列在前、排序列居中的口诀重建联合索引idx_user_status_time (user_id, status, create_time)。两个等值条件user_id与status在树上收窄出一个精确区间区间内create_time天然有序且方向单一ORDER BY create_time DESC变成对这个区间的反向遍历——引擎从区间末尾倒着读 20 条即止。重跑执行计划type仍是ref但rows估算降到数百filesort消失慢日志里Rows_examined实测 20耗时从 1.8 秒降到 2 毫秒量级。扫描行数从 31 万降到 20这就是验证环节要留下的证据。还有一步收尾经常被跳过旧索引idx_user的每个能力——按 user_id 定位——新索引作为其最左前缀已完全覆盖应当择机下线省下一棵树的写入维护与缓冲池占用。8.0 提供了安全的下线路径先ALTER TABLE … ALTER INDEX idx_user INVISIBLE把索引设为不可见优化器立即停止使用它但树仍在维护观察一到两个完整业务周期务必覆盖月末、大促这类低频高峰确认没有任何查询劣化后再真正删除一旦有语句受影响一条语句就能把索引恢复可见代价远低于重建。如果这条查询的返回列可控比如列表页只需要订单号、金额、时间、状态更进一步的形态是把SELECT *改为明确列清单并评估覆盖索引连那 20 次回表也一并省掉但要在索引宽度与读收益之间算账列表页字段多而杂时不必强求覆盖。三防回归把结论固化下来这次治理的产出不应只是一条变快的语句而是三条可执行的规矩。给这张表补上查询清单文档注明idx_user_status_time服务于哪几条查询形态防止后人看不懂就再加一条索引给深分页风险打预防针——这条接口未来若支持翻页直接按游标分页设计排序键就用现成的(create_time, id)把上线新查询必须附执行计划、改动索引必须对比前后Rows_examined写进团队的评审卡点。七、总结一条主线与三道防线回望全文所有技术点其实被一条主线贯穿B 树把数据按键值有序地组织在磁盘页上一切优化手段——联合索引的列序、覆盖索引、条件下推、排序对齐、游标分页——都在做同一件事就是顺着这份有序性安排访问路径让每次查询触碰的页面最少、回表次数最少、执行期补救的排序最少。而执行计划是检验这一切是否成立的唯一凭证type给出访问方式的定性rows给出规模的定量Extra暴露计划外的补救动作key_len验证联合索引的真实利用程度。落到工程上是三道防线。设计期让慢查询少出生先列查询清单再设计索引等值前、排序中、范围后高频查询配覆盖索引主键短且趋势递增。定位期按链路走不凭感觉慢日志聚合找头部扫描与返回之比找浪费执行计划找原因EXPLAIN ANALYZE与optimizer_trace处理疑难。验证期留下证据真实量级数据、优化前后的扫描行数对比、多组参数下计划稳定性确认。做到这三道防线慢查询就从一个反复救火的顽疾变成一个有口径、有流程、有验收标准的常规工程问题。可参考的文章与资料官方文档执行计划与优化MySQL 8.4 参考手册EXPLAIN 输出格式 —— 各列取值与含义的权威定义MySQL 8.4 参考手册优化概览 —— 从语句到 schema 的完整优化章节MySQL 8.4 参考手册EXPLAIN ANALYZE —— 真实执行的算子级耗时与行数MySQL 8.4 参考手册优化器追踪 —— optimizer_trace 的开启与解读MySQL 8.4 参考手册慢查询日志 —— 采集参数与日志字段说明索引结构与设计MySQL 8.4 参考手册InnoDB 索引的物理结构 —— B 树页组织与聚簇索引MySQL 8.4 参考手册多列索引 —— 最左前缀规则的官方表述MySQL 8.4 参考手册索引条件下推 —— ICP 的适用条件与限制MySQL 8.4 参考手册降序索引 —— 混合方向排序的索引化MySQL 8.4 参考手册ORDER BY 优化 —— filesort 触发条件与排序模式MySQL 8.4 参考手册LIMIT 查询优化 —— 分页语句的优化器行为Use the Index, Luke面向开发者的索引教程 —— 跨数据库的索引原理与反例集统计信息与诊断工具MySQL 8.4 参考手册InnoDB 持久化统计信息 —— 采样页数与统计固化Percona Toolkitpt-query-digest —— 慢日志聚合分析的标准工具MySQL 8.4 参考手册语句摘要统计 —— performance_schema 的模板聚合深入阅读《高性能 MySQL第四版》 —— 索引、查询优化与 schema 设计的系统论述MySQL 源码文档优化器成本模型 —— 成本常数与代价公式Vitess 博客主键与随机标识符的性能对比实验 —— 随机主键页分裂代价的实测数据