1. 场景还原与问题定位这条SQL为什么慢SQL优化这事干得好CTO给你加鸡腿干不好生产环境直接教做人。我接手过一个后台管理项目的订单分页查询单次查询耗时稳定在3.2秒页面每次翻页都要转两圈才出数据业务方已经抱怨了好久。这是个典型的读多写少的统计查询表里累积了500多万条订单记录每天还会新增两三万条用户筛选条件有七八个组合维度排序还涉及金额和时间。第一件事肯定是打开慢查询日志确认问题范围。慢日志里这条SQL的execution_time稳定在3.2秒左右rows_examined已经飙到了四百多万。说白了数据库把几乎整张表都翻了一遍才凑出当前页的数据这在任何规模的系统里都属于典型的全表扫描信号。拿到这个结果我当时的判断顺序是这样先看是不是SQL写法本身写了不该写的笛卡尔积或者漏了关联条件。再看执行计划确认表关联顺序、索引使用情况、排序是否走了文件排序。最后才是考虑要不要调整表结构、加缓存或者改业务查询方式。实际检查下来关联条件没问题SQL本身也是朴实无华的三表联查。问题基本集中在索引和查询优化器身上。定位慢查询这事说到底就一个问题数据库要处理的数据行数和你实际需要返回的数据行数之间差距有多大。差距越大性能越差。3.2秒的罪魁祸首就是执行计划里出现了ALL类型的全表扫描外加ORDER BY字段没走索引触发filesort两层debuff叠在一起不慢才有鬼了。2. 执行计划分析先把EXPLAIN结果读明白关于执行计划我建议每个写SQL的人至少把EXPLAIN的十几个字段吃透六七个真的能救大命。当时定位问题我直接跑了EXPLAIN关键几个字段我是这样看的字段当时的实际值含义与问题select_typeSIMPLE普通查询不算复杂子查询排除了一部分问题tableorder_table驱动表是订单表体量最大明显不合理typeALL主驱动表全表扫描这是性能恶化最直接的证据possible_keysidx_user_id理论上有可用的索引keyNULL但实际上一条索引都没用上rows429万预估扫描行数几乎等于全表数据量ExtraUsing where; Using filesort有过滤条件排序走了文件排序双重打击这段信息基本把问题钉死了。驱动表选了数据量最大的订单表且没走任何索引还要对结果集做排序3.2秒是怎么来的就是这四百多万行一个个过滤、再拉到临时文件排序排出来的。再往下追为什么订单表作为驱动表却没走索引原因是查询条件里对user_id有等值过滤但优化器评估之后觉得即使走idx_user_id索引过滤完剩下的数据量还是很大倒不如直接全表扫来得省事。优化器的这个判断源于表统计信息的偏差也可能是数据分布确实太均匀导致索引区分度不高。但它这个选择放在整体SQL里就是灾难性的因为后续的关联、排序、分页全都被放大了成本。所以执行计划解读的核心不是看懂每个字段而是看懂数据库“为什么”这么选。它在挑剔什么、放弃什么你顺着这个思路去补索引或者重写SQL才能一击命中。我当时看完执行计划就一个判断这条SQL必须做的是降低驱动表扫描规模同时干掉filesort。3. 优化方案设计先从索引入手才是第一优先级很多同学一提到优化SQL第一反应是改业务逻辑、改代码、加Redis缓存但实际上对这条业务SQL来说第一优先级永远是索引。原因很简单查询要处理的数据集没变小之前任何外部手段都是治标不治本。当时的表结构关键字段大致是这样CREATE TABLE order_table ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, pay_amount DECIMAL(10, 2) NOT NULL DEFAULT 0, order_status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_id (user_id), KEY idx_created_at (created_at) );关联表分别有user_id、shop_id之类的索引单看没什么大问题。但注意一个细节排序和过滤的字段没有组合在一起。过滤用了order_status和created_at排序用了pay_amount索引设计却是各自为战导致排序必走filesort。我常用的索引设计思考路径是这样的把所有WHERE等值条件列出来哪些字段是高选择性的适合放索引左边。把ORDER BY字段列出来看看能不能塞进同一个索引避免排序。把GROUP BY / DISTINCT涉及的字段也纳入考量。最后再为覆盖索引补上SELECT需要的字段尽可能让索引里什么东西都有查询就不回表了。这条SQL需要的是一个组合索引同时覆盖过滤、排序和关联。经过反复推敲我把第一个版本的索引建成了这样ALTER TABLE order_table ADD INDEX idx_status_create_pay (order_status, created_at, pay_amount);这个索引的设计逻辑是把等值条件order_status放在最左边然后是用范围查询的created_at最后把排序字段pay_amount放进去这样排序理论上可以直接走索引顺序不用再额外做filesort。执行计划里possible_keys会出现这个索引type从ALL变成rangerows从四百多万降到几万Extra里不再出现Using filesort。但说实话单靠这一个索引还不够彻底。因为三表关联加分页的复杂度还在而且SELECT字段也包含了一些索引覆盖不到的内容。更头疼的是order_status是枚举值选择性很低单靠它过滤完还是有几十万行要处理。所以索引这步只是把这局棋从地狱难度拉回到普通难度后续还得继续调。4. 绕不开的细节类型转换、函数计算与字符集索引优化过程中我顺手排查了几个特别容易踩坑、但项目中几乎所有人都会犯的细节问题。这些问题单看不起眼叠加在一起就是量变引起质变。4.1 隐式类型转换让索引彻底失效检查过程中发现有个过滤条件是这样写的WHERE order_no 1234567890而表里的order_no是VARCHAR类型。数据库在比较时会先把字符类型的字段转换成数字类型再和右边的数字比较。一旦对索引列做了隐式类型转换索引就完全没法用了。这跟你在计算器上按了“括号没闭合”一样看似输入没问题实际运算结果完全不是你以为的那样。解决办法就是统一参数类型。应用层传入的是字符串就规规矩矩加引号或者干脆在SQL里把两边都转成同一种类型。这个坑我至少见人踩过三次每一次都是线上接口莫名变慢排查到凌晨才发现是引号问题。4.2 对索引字段做函数运算也一样有人会在查询里写WHERE DATE(created_at) 2024-05-01看着挺正常对吧实际上为了让这个条件生效数据库必须对created_at每一行都执行一次DATE函数计算完毕才能比较。函数计算破坏了索引的原始值索引自然就失效了。正确写法应该是用范围区间替代函数WHERE created_at 2024-05-01 00:00:00 AND created_at 2024-05-02 00:00:00这个改写保留了索引的有序性查询可以直接缩小定位区间。两者性能差距在百万级数据量下可以到几十倍以上不是闹着玩的。4.3 字符集不一致引发的关联隐式转换三表关联时我注意到订单表和用户表的user_id在定义上一个是BIGINT一个是VARCHAR。两个不同类型的字段做JOIN数据库同样会发生隐式转换。虽然关联字段上可能各自都有索引但转换之后索引就用不上了三表关联就退化成了一层层嵌套循环查。这种情况的执行计划里rows会虚高实际耗时更是只增不减。解决办法是保证关联字段的类型完全一致如果历史表已经不一致宁可先做表结构迁移也要统一。优化做到这里我对团队说的原话是“索引是SQL优化里最好拿的分但类型不一致、函数运算这些坑才是把分扣光的东西。” 先把这些坑填了再回头调整索引细节事半功倍。5. 深入优化覆盖索引与延迟关联索引组合拳打了第一波整体查询从3.2秒降到了零点几秒但还没到我的心理预期。业务方的要求是翻页必须“秒开”最多不能超过0.1秒。所以我又上了第二波优化覆盖索引和延迟关联。覆盖索引的意思是索引里已经包含了查询需要的所有字段查询过程中不必再回到聚簇索引里取数据。这就像你去图书馆找一本书目录页上已经印了全书内容你压根不需要去书架上把书拿下来翻页直接在目录看就够了物理IO省了一截。针对这条SQL的SELECT字段我把原索引扩展成了这样ALTER TABLE order_table ADD INDEX idx_status_create_pay_cover (order_status, created_at, pay_amount, order_no, user_id);这样一来过滤、排序、关联、投影所需的字段已经全部包含在索引中符合条件的行不再产生回表操作。不过这里有个前提索引的体积会变大插入和更新会有额外开销。对于订单表这种写多读少的场景加一个覆盖索引的代价是可控的但如果你的表每一秒都有大量写入就得更谨慎地权衡索引数量和写入性能的平衡。但光有覆盖索引还不够因为分页的LIMIT机制有个经典毛病偏移量越大扫描的无关数据越多。比如LIMIT 500000, 20数据库还是要数到50万行才开始取那20行。这个场景下我用了延迟关联的思路。先让查询只返回主键把关联和排序都压缩在索引上完成然后再用这些主键去关联原表取其他字段SELECT t1.id, t1.order_no, t1.pay_amount, t1.created_at, u.user_name FROM ( SELECT id FROM order_table WHERE order_status 1 AND created_at 2024-05-01 AND created_at 2024-06-01 ORDER BY pay_amount DESC LIMIT 500000, 20 ) t JOIN order_table t1 ON t.id t1.id JOIN user_table u ON t1.user_id u.id;这个写法的巧妙之处在于内层子查询在覆盖索引上完成过滤、排序和分页几乎不产生回表速度极快。外层再通过主键回表取必要字段最多取20条回表成本微乎其微。从执行计划看内层type是rangeExtra里干干净净外层的主键连接是eq_ref级别整个SQL的执行路径非常清爽。实测下来优化后的查询稳定在0.08秒左右整整比原来的3.2秒提升了40倍。这个结果让我相当满意因为不是靠堆机器硬件而是靠理解数据结构和查询行为换来的性能红利。6. 优化后的效果复盘与方案取舍优化做完不能拍拍屁股走人还得看整体效果和有没有引入新问题。我从三个维度做了复盘6.1 性能数据对比指标优化前优化后说明单次查询耗时3.2秒0.08秒提升约40倍rows_examined429万约2400行扫描量下降到原来的千分之一量级filesort出现消失排序走索引顺序回表次数高几乎为零覆盖索引减少了物理IO这个数据变化非常直观核心不是把单个环节提速而是让数据库需要处理的数据量从根本上变小。你让一个人搬一卡车砖跟让他搬一块砖效率差距当然惊人。6.2 索引维护代价评估新增的覆盖索引会让每次INSERT和UPDATE多维护一份索引数据。对这个订单系统来说每天新增两三万条写压力很小索引增加的写开销完全在可接受范围内。但如果是一个每秒写入上千条的热点表我就不会轻易加这么大的覆盖索引了而是考虑把查询字段拆到更小的宽表里让索引本身保持精简。6.3 是否有更粗暴的解法有人可能会说这查询这么慢直接上缓存不就行了。话是没错但缓存解决的是同一个查询的重复访问问题解决不了不同筛选条件组合下海量查询的多样性问题。几十上百个组合条件的接口你要缓存到什么时候才能全部预热而且数据更新后缓存一致性的复杂度也会跟着上来。相比之下把SQL本身优化干净是所有后续手段的基础。缓存可以加但那是建立在SQL本身已经足够快的前提之上。7. 常见问题与排查技巧实录做SQL优化做了这么多年踩过的坑和积累的经验足够写满几个本子。挑一些最常见的整理出来供大家参考。7.1 索引建了但没走是怎么回事这是被问到最多的问题。可能的原因有几种条件字段上用了函数或隐式转换这会让优化器失去对索引有效性的信任。数据分布太均匀优化器评估走索引和全表扫描的成本差不多甚至更贵。统计信息过期了表数据变化剧烈但没及时更新统计信息。这种情况可以用ANALYZE TABLE手动刷新别迷信所有东西自动维护。排查这类问题的方法就是把EXPLAIN结果里的type、possible_keys、key、rows四个字段排在一起看。key是NULL、possible_keys有值说明优化器放弃了可用索引再结合select_type和rows判断它为什么放弃。7.2 为什么我加了索引ORDER BY还是走filesort如果排序字段和过滤字段不在同一个索引里优化器基本不会选择用索引顺序来避免排序因为索引扫描路径和排序路径是冲突的。要让排序走索引必须保证排序字段包含在一个联合索引中并且过滤字段在它左边。还有一个常见问题是排序方向不一致一个是ASC一个是DESCMySQL 8.0之前这类排序没法完全走索引顺序需要8.0的降序索引才能解决。7.3 分页越深越慢有没有通用解法分页深的问题本质是OFFSET太大导致扫描了大量无用行。除了延迟关联还有一种做法是记录上次翻页的位置用WHERE条件代替OFFSET。例如记住了上一页最后一条的id或created_at下一页直接在WHERE里带上id 上一页最大id这样的条件天然就跳过了前面所有历史数据。但这要求业务形态可以接受“不带页码跳转”的交互否则还是用延迟关联更通用。7.4 并发情况下单条SQL变快但整体还是慢这种情况多半不是因为SQL本身而是数据库的全局资源被占满了。我排查时习惯先看数据库的活跃会话数、锁等待、IO延迟和缓冲池命中率。有时候一条大查询把IO或者CPU跑满其他小查询全部跟着排队你优化小查询是没用的得先解决大查询。把慢查询日志按耗时排序优先处理单次扫描行数最多的前几条往往就是突破口。7.5 覆盖索引到底该不该无脑加不该。覆盖索引本质上是空间换时间索引包含的字段越多占用的磁盘和内存就越多写入成本也越高。我一般只在读多写少、查询频率极高的表上加覆盖索引而且只覆盖核心查询真正需要的字段。如果一张表有几十个查询场景每个场景的覆盖字段都不同那全加一遍就是灾难。更合理的做法是选两三个最高频的查询场景做覆盖索引其余场景保持普通索引必要时用延迟关联兜底。8. 实操总结从这条SQL延伸出的几条方法论这条SQL优化到后面已经不是单点技巧的问题了背后沉淀下来的是我对SQL优化的一套完整判断路径。第一步永远是先确认扫描行数和执行计划数据说话不要凭感觉猜。第二步是检查索引设计是否符合查询模式联合索引的字段顺序不是拍脑袋定的要按“等值条件优先、排序字段紧跟、覆盖字段收尾”的原则来设计。第三步才是考虑改写SQL写法延迟关联、避免函数计算、统一字符集和类型。最后一步才是考虑外部手段比如缓存、读写分离、分表分库。而贯穿整个优化过程最核心的一句话是你要让数据库尽量少干活而不是让它把活干得越来越快。同一条SQL同样的索引为什么有的机器上快有的机器上慢很多时候不是机器不行而是你没把查询要处理的数据范围压缩到最小。另外还有一点很容易被忽略就是优化完一定要做回归测试。我之前遇到过一条SQL优化后单查很快但因为是高频接口每秒调用几十次索引占用内存攀升把数据库缓冲池的命中率拖低了反而影响整体业务响应。所以改索引和改SQL都别只盯着单条查询要结合周边环境一起评估。如果你手上也有一条从三秒到零点一秒的慢查询建议你把它当成一次深入学习数据库原理的机会按我上面这套“看执行计划、补联合索引、清类型隐患、用覆盖索引、延迟关联收尾”的五步走大概率能复现我在这个项目里的优化效果。跑通一次之后你对索引的理解会比看十篇理论文章都深刻。