前段时间帮某业务团队处理一个线上问题订单查询接口越来越慢用户点开“我的订单”页面要转圈2秒多才出数据客服那边已经收到一堆“页面卡死”的反馈。我接手之后翻了慢查询日志发现罪魁祸首是三条SQL其中一条平均执行时间1.8秒一天要跑几十万次。这个案例很有代表性几乎把数据库工程与SQL调优里常见的坑都踩了一遍。这篇文章就把这次完整过程拆开讲从执行计划分析到索引设计从SQL重写到表结构优化从参数调整到日常巡检最终把查询耗时压到了180毫秒左右正好是10倍的提升。后端开发、DBA、运维同学都可以对照着自己系统的慢查询来走一遍这个流程。优化思路不局限于某一种数据库我用的示例以MySQL为主但方法论放到PostgreSQL、SQL Server上一样成立。重点不是让你背几条调优口诀而是理解每一步选择背后的逻辑知道什么时候该加索引、什么时候该改SQL、什么时候该动表结构。1. 问题定位慢查询从哪里来1.1 第一眼先看执行计划再说别的拿到慢查询我从来不会先凭感觉去猜“是不是要加个索引”。第一步永远是看执行计划这是整个调优的基准线。MySQL里就是EXPLAINPostgreSQL可以EXPLAIN (ANALYZE, BUFFERS)SQL Server是SET STATISTICS IO ON加图形化执行计划。花十分钟看懂执行计划比瞎试一个小时SQL写法有效得多。我让开发同学把线上那条1.8秒的SQL拉出来简化之后长这样SELECT o.id, o.order_no, o.amount, u.name, u.phone, oi.product_name FROM orders o LEFT JOIN users u ON o.user_id u.id LEFT JOIN order_items oi ON o.id oi.order_id WHERE o.status 3 AND DATE(o.create_time) 2024-06-01 ORDER BY o.create_time DESC LIMIT 20;执行计划一跑问题立刻暴露出来orders表走的是全表扫描rows估算170万Extra里还挂着Using filesort。这基本就是说MySQL把整张订单表翻了一遍找出符合状态的记录再临时排序取前20条。这个操作每天重复几十万次不慢才怪。看执行计划时我一般只盯五个关键点type从好到差大致是const、eq_ref、ref、range、index、ALL。只要看到ALL就要问自己为什么没走索引。key实际用到的索引名如果为NULL说明没走索引。rows预估扫描行数这个数字和真实耗时强相关。filtered经过条件过滤后剩余比例太低说明大部分行都被筛掉了条件或索引有问题。ExtraUsing filesort和Using temporary是性能杀手Using index则是好消息说明是覆盖索引扫描。1.2 三类最常见的“慢SQL病根”我处理过的慢查询里有九成都能归到下面这三类问题里。第一类是索引明明存在但就是没走写SQL时用了隐式转换、函数包裹或者LIKE %xxx这种前导通配符导致优化器无法利用索引。第二类是关联查询驱动表选错优化器选了小表当被驱动表或者子查询被展开成效率很差的执行路径。第三类是大分页和排序LIMIT 100000, 20这种写法即使有索引也会让数据库先扫十万行再扔掉白白浪费IO。这三类问题可以独立存在也可能同时出现。我的建议是不要急着改SQL先把执行计划里的type、key、rows、Extra这几个字段截图记录下来作为优化前的基线。后面每改一步就重新跑一遍执行计划做对比。这样优化效果是实打实可以量化的而不是“感觉快了一点”。2. SQL重写最小改动拿最大收益2.1 让索引真正走起来隐式转换与函数包裹这条1.8秒的SQL里有一个非常典型的坑DATE(o.create_time) 2024-06-01。在create_time字段上套了DATE()函数之后MySQL优化器基本没有别的选择只能对全表每一行的create_time都调用一次函数再做比较索引自然就废了。这个写法看起来无害实际上等于告诉优化器“这个字段每次都要加工一下才能比较”。正确的写法是把条件改成范围查询让create_time能直接跟索引比对AND o.create_time 2024-06-01 00:00:00 AND o.create_time 2024-06-02 00:00:00这种写法还有一个额外的好处MySQL在range扫描时可以用索引去定位起始和结束位置只扫描中间那一段数据而不是全表。如果create_time上建有索引这个改动单靠SQL重写就能带来数量级的提升。同样常见的还有隐式类型转换。比如字段是VARCHAR但条件里传了数字WHERE phone 18812345678MySQL会在比较时把字段转成数字等于又包了一层函数索引同样失效。判断方法很简单看执行计划里key是否为NULL以及Extra里有没有Using where但没有走索引的迹象。遇到这类问题统一字段和条件的类型比强行改SQL写法更重要。2.2 改写关联与子查询从嵌套循环到合理Join继续看那条订单查询LEFT JOIN users u ON o.user_id u.id。orders表里如果user_id建了索引这个关联本身不慢但前提是驱动表的数据量要先降下来。驱动表是LEFT JOIN左侧的orders表现在orders表还在全表扫描这个关联压力就会被放大几百倍。优化驱动的思路很简单先过滤再关联。把orders表上过滤性最好的条件放到WHERE里让优化器先从orders表取出尽可能少的行再去匹配users和order_items。这里有一个很小的改写技巧就是先把符合条件的主键查出来再关联其他表取字段。这个手法叫延迟关联尤其适合大表回表代价高的场景SELECT o.id, o.order_no, o.amount, u.name, u.phone, oi.product_name FROM ( SELECT id, order_no, amount, user_id, create_time FROM orders WHERE status 3 AND create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00 ORDER BY create_time DESC LIMIT 20 ) o LEFT JOIN users u ON o.user_id u.id LEFT JOIN order_items oi ON o.id oi.order_id ORDER BY o.create_time DESC;这里的关键是子查询里已经用索引把20条目标记录锁定了再和users、order_items做关联时最多只需要匹配20次。相比之下原来的写法是先把几十万条关联结果算出来排序最后才取20条代价完全不在一个量级。在实际项目里IN子查询和EXISTS的取舍也经常被拿出来讨论。我的经验是MySQL 5.6以上版本的优化器已经会把大部分IN子查询改写成半连接性能差距没有传言中那么大但前提是子查询的表要有合适的索引。如果你发现一条IN子查询慢不要急着改成EXISTS先看执行计划里有没有出现Materialize或者Duplicate weedout如果出现了再考虑改写。2.3 分页排序延迟关联的妙用分页查询是慢查询重灾区。LIMIT 100000, 20这种写法数据库会把前100000行全部读出来然后丢掉只返回20行。在索引上扫描还好最怕的是还要回表取字段那就要做10万次回表IO开销直线上升。延迟关联同样可以解决这个问题。先只查出主键再回原表取完整字段SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 3 AND create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC;子查询只在索引上做排序和分页不需要回表等拿到20个主键后再一次性回表取行。这个操作从10万次回表降到了20次查询时间从秒级降到毫秒级是很常见的。代价是SQL变长了一点但换来的是数量级的性能提升完全值得。另外说一个细节如果分页只需要上一页和下一页这种相对位置而不是精确页码可以改用WHERE create_time 上一页最后一条记录的create_time ORDER BY create_time DESC LIMIT 20。这种“游标分页”连OFFSET都不需要数据库每次只从索引定位到的位置往下取20行是深分页场景下的终极解法。3. 索引工程不止是“加个索引”这么简单3.1 复合索引设计字段顺序决定命运看执行计划的时候我发现orders表其实已经有一个idx_status单列索引但优化器还是选择了全表扫描。为什么因为status3的数据接近全表的40%优化器觉得用索引回表还不如直接扫全表划算。这种单列索引在过滤性差的字段上几乎没有意义。真正解决问题的是复合索引。我们最终在orders表上建了(status, create_time)这个复合索引查询路径立刻从全表扫描变成了range扫描。这里有一个核心原则等值条件的字段放前面范围条件或排序字段放后面。WHERE status 3 AND create_time BETWEEN ... ORDER BY create_time DESC正好让这个索引既过滤了status又通过create_time做了排序连Using filesort都一并消除了。反向对比一下如果建的是(create_time, status)效果就差很多。因为create_time是范围条件一旦进入范围扫描后面的status字段就没法参与过滤了优化器需要从所有当日订单里筛status3的记录扫描范围大了不少。这两个索引看起来都是两个字段实际性能差距可能超过5倍。注意复合索引不要贪多。一个表建3到5个有明确用途的索引就够了每个索引都是写入时的负担。每次INSERT或UPDATE所有相关索引都要同步更新。索引越多写入越慢存储占用越大。3.2 索引失效场景与覆盖索引我再补充一个高频踩坑场景。有的开发同学加完索引发现查询还是慢一看执行计划type是refkey也用上了但Extra里有Using whererows还是很大。这种情况往往是索引能定位到的范围太宽还需要回表过滤大量行。解决办法是设计覆盖索引也就是让索引本身就包含查询需要的所有字段。比如统计某天成功订单数和金额SELECT COUNT(*), SUM(amount) FROM orders WHERE status 3 AND create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;如果索引是(status, create_time, amount)那么这个查询从头到尾只扫索引页不需要回表Extra里会显示Using index。在数据量几千万的表上覆盖索引扫描的数量级差距非常明显。常见的索引失效场景还有几个WHERE a 1 OR b 2很可能变成两个全表扫描再合并WHERE name LIKE %张%前导通配符导致索引失效两个表关联字段的字符集不一致也会触发隐式转换。这些情况在线上经常出现查的时候要特别留意。我处理过一个字符集不一致导致的关联慢查询把两边的排序规则统一后查询时间直接从4秒降到了0.3秒。3.3 索引维护与统计信息加索引不是一劳永逸。任何频繁执行增删改的表索引都可能产生碎片优化器依赖的统计信息也可能过期。MySQL里有一个很容易忽视的点如果自动采样统计信息跟不上数据变化优化器就会拿着一个很旧的估算值去做执行计划选择结果就是明明有更好的索引路径优化器偏偏选了一条坏路径。解决方案是定期做索引健康检查。对于大表不要动不动就OPTIMIZE TABLE这个操作在数据量大的时候会锁表或者占用大量IO线上很容易出事。更稳妥的做法是更新统计信息MySQL用ANALYZE TABLEPostgreSQL用ANALYZE这个操作轻量得多大部分场景下就能纠正优化器的判断。另一个经验是定期清理无用索引。很多人加索引时很积极上线后从来不评估索引的使用情况。MySQL的performance_schema.table_io_waits_summary_by_index_usage可以看到每个索引的读取和写入次数连续观察一周那些只有写入没有读取的索引就可以安排下线了。这会直接影响写入性能减少不必要的索引维护开销。4. 工程层面数据库配置与表结构优化4.1 表设计字段类型、冗余与归档SQL调优到了后期瓶颈往往不在某一条语句上而是表结构本身。原始的orders表里有个字段是user_phone VARCHAR(50)明明手机号11位就够了却留了50位的余量。这类问题单看SQL发现不了但会拖慢索引扫描速度——因为索引页能容纳的索引键变少了同样的数据量需要扫描更多的页。我通常会建议开发团队做一轮字段类型体检能用INT不用VARCHAR能用DATETIME不用VARCHAR存时间字符串IP地址用INT UNSIGNED存储而不是字符串。日期和数字在比较和排序时都比字符串快存储空间还更小。空间是小事关键是索引扫描的效率和排序的开销都跟字段宽度直接相关。另一个工程层面很重要但是经常被忽略的点是冷热数据分离。很多订单表过了两三年里面已经没有多少活跃数据了但因为历史订单要保留表越滚越大。我会建议把两年前的订单迁移到归档表主表永远只保留活跃周期内的数据。热数据量降下来索引和查询的自然变快这比任何SQL优化都直接有效。如果业务模型允许按时间做分区表也是一种办法查询时通过分区裁剪只扫对应时间段的分区。4.2 数据库参数调优与连接池数据库不只是SQL和索引连接池配置不当也能把本该很快的查询拖死。我遇到过一种情况单条SQL执行计划已经优化得很好了但线上接口依然很慢查下来发现是连接池的最大连接数太小请求全在排队等连接。这就是典型的工程配置问题不是SQL问题。MySQL侧我会重点看几个参数innodb_buffer_pool_size决定了缓存池能装多少数据页这个值设置得过小会导致大量数据频繁从磁盘读入IO是慢查询的放大器max_connections设太大容易导致数据库线程切换频繁设太小又会让连接排队。比较稳妥的做法是根据实际监控数据来调比如buffer pool的命中率低于95%就说明内存给少了。连接池这边重点是maxActive和minIdle的比值。maxActive要参考接口的QPS和单个查询耗时来估算不能拍脑袋。一个粗粒度的公式是连接数约等于QPS乘以单次查询耗时。如果QPS是500单次查询平均100毫秒那连接数至少要50再留出2到3倍的余量。我做过一次调整把应用连接池从20调到了60接口P95耗时直接降了30%这个案例说明慢的根源并不总是SQL本身。5. 实测复盘一次查询从1.8秒到180毫秒的全过程5.1 场景描述与基线数据前面说的都是方法论我拿这次的完整案例把整个过程串一遍。模拟某业务系统的“我的订单列表”接口数据库用的是MySQL 8.0订单总量大约800万行。原始SQL关联三张表包含用户昵称、商品名、订单状态、下单时间等字段。优化前的基线数据记录如下指标优化前接口P95耗时1.8秒订单表访问方式ALL全表扫描预估扫描行数约170万Extra信息Using filesort数据库CPU40%这条SQL每天在高峰期执行约30万次几乎每个用户打开订单列表都会触发。优化前的direct问题是status字段有单列索引但过滤性不足create_time字段被DATE()函数包裹导致索引失效排序和取前20条的逻辑让数据库白白算了很大一部分数据。5.2 每一步改动和效果对照整个优化分四步走每一步都有可量化的效果。第一步重写SQL去掉DATE()函数包裹把时间条件改成范围查询。这一步执行计划从全表扫描变成了range扫描但因为缺少合适的复合索引实际耗时降到1.1秒提升还不够明显。第二步在orders表上建复合索引(status, create_time)。这一步效果很直接type从range进一步变好rows从170万降到30万以内Using filesort消失了因为create_time的排序直接用到了索引。接口P95降到400毫秒左右。第三步改造关联查询引入延迟关联。先查出20个目标主键再去关联users和order_items避免关联大表后的全量回表。这一步把P95压到了250毫秒。第四步调整表结构和连接池。把冗余字段清理掉时间字段统一用DATETIME连接池从20调到60数据库侧把innodb_buffer_pool_size从4G调到16G服务器内存32G这个比例是安全的。最终P95稳定在180毫秒左右。优化步骤操作内容P95耗时优化前无1.8秒第一步SQL重写去掉函数包裹1.1秒第二步建立复合索引(status, create_time)400毫秒第三步延迟关联改写250毫秒第四步表结构与配置调整180毫秒从1.8秒到180毫秒正好是10倍的提升。每一步都不复杂但必须有前一步的执行计划做支撑。我特别想强调不要跳过第二步直接做第三步因为如果没有合适的索引延迟关联的子查询本身也会很慢。优化的顺序应该是先让单表访问路径最优再处理关联和回表最后才动工程配置。5.3 稳定性验证与后续运维优化上线不是把SQL改了就算完。我在灰度环境跑了三天对比了慢查询数量、平均耗时、CPU使用率以及连接池活跃数。上线到生产时也做了回滚预案旧的SQL写法保留在代码注释里万一有问题随时可以切回去。我看到很多团队在线上执行“大表加索引”直接在业务高峰期跑一个ALTER TABLE ADD INDEX结果导致长时间元数据锁业务写入全部阻塞。这里分享一个实操经验MySQL 5.6以上支持在线DDL加索引可以用ALGORITHMINPLACE, LOCKNONE但即便如此我也会在低峰期执行并且先用pt-osc或者gh-ost这类工具评估一下影响。像800万行的表加索引低峰期大概需要几分钟期间监控磁盘IO和主从延迟确保没有异常。6. 常见问题与排查技巧实录6.1 慢查询排查速查表用户案例见多了我发现慢查询的问题类型相对固定整理成速查表方便你快速定位现象可能原因排查方向执行计划typeALL缺少索引或索引因函数/隐式转换失效检查WHERE条件字段确认索引列是否被包裹有索引但keyNULL字段类型不一致、字符集不一致、前导通配符统一字段类型和排序规则改写LIKE条件ExtraUsing filesort排序字段不在索引中设计复合索引覆盖排序列rows很大但结果集很小没有利用过滤性好的条件调整WHERE条件顺序重写为延迟关联单条SQL不慢但接口慢连接池排队、锁等待、网络往返查看应用连接池活跃数SHOW PROCESSLIST检查锁数据库CPU飙升大量低效SQL或索引碎片慢日志分析TOP SQL清理无用索引索引存在但优化器不用统计信息过期ANALYZE TABLE必要时引导优化器6.2 几个高价值的排查技巧我每天排查慢查询基本就靠三件套慢查询日志、performance_schema和EXPLAIN。慢查询日志一定要开阈值建议设到100毫秒甚至更低。不要等到用户投诉了才去翻日志每天看一眼TOP SQL很多问题在发生之前就能发现。定位TOP SQL时MySQL 8.0可以直接查performance_schema里的events_statements_summary_by_digest表按平均耗时排序能看到每种SQL模板的执行次数和总耗时。这一点比单纯翻慢日志更高效因为慢日志只是抽样而数据库内置的统计是全面的。拿到的SQL再配合EXPLAIN ANALYZEMySQL 8.0.18以上支持看真实的执行时间和扫描行数就能精准定位瓶颈。有一个我踩过很多次坑的细节EXPLAIN显示rows是估算值不是真实值所以不能完全相信。尤其是在字段数据分布非常不均匀的情况下比如status3的数据占总量的50%但优化器还是按照均匀分布去估算就会选错执行计划。这时候要么更新统计信息要么用FORCE INDEX强制走某条索引。不过FORCE INDEX只建议临时使用因为数据分布变化后它会变成错误选择。最后一个经验是在复制环境上做执行计划对比。我不会直接在生产库上试各种SQL写法而是在一台从库或者专门的测试库上跑用相同的表结构和相同的数据量验证效果误差。这样既能拿到真实数据又不会把生产环境搞出问题。这个习惯帮我避免了好几次事故跟DDL操作一样凡是可能影响线上稳定性的操作都要先在别的地方验证过再上生产。把优化做完之后我还会做一件事把这些慢SQL和优化方案记录到团队的wiki里标明每条SQL为什么会慢、为什么这样改写、执行计划长什么样。这样做的好处是下次有其他同事遇到相似问题时直接查文档就能解决不用重复踩坑。数据库工程是一个持续沉淀的过程新一代的代码总会产生新的慢SQL没有一份“优化记录”做底子每次都要从零开始那才是真正的效率杀手。我自己的体会是遇到慢查询不要急着动手先把执行计划看懂把数据分布摸清。SQL调优最大的坑不是不会写SQL而是不看执行计划就凭感觉加索引。很多时候一个隐藏的类型转换或者一个函数包裹就能让一张索引完全失效。记住加索引是消耗写入性能的合理使用才是关键SQL改写和表结构优化往往比单纯加索引更有效因为它们从源头上减少了数据访问量。
