企业官网建设流程全解析

目录

一、慢查询从哪里来：先建立定位链路，再谈优化

（一）慢的定义与慢查询日志

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是 20，Rows_examined是 31 万：返回与扫描之比达到一比一万五，典型的访问路径浪费。执行计划印证了猜测：type为ref，key是idx_user，rows估算 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 Toolkit：pt-query-digest —— 慢日志聚合分析的标准工具
• MySQL 8.4 参考手册：语句摘要统计 —— performance_schema 的模板聚合

深入阅读

• 《高性能 MySQL（第四版）》 —— 索引、查询优化与 schema 设计的系统论述
• MySQL 源码文档：优化器成本模型 —— 成本常数与代价公式
• Vitess 博客：主键与随机标识符的性能对比实验 —— 随机主键页分裂代价的实测数据