1. 一张订单查询耗尽数据库IO慢问题是这么浮出水面的事情发生得很突然。某业务系统的订单列表页在下午三点左右开始出现明显的卡顿页面接口从平均200毫秒涨到了接近4秒数据库服务器的IO等待指标一路飘红。监控平台上慢查询日志刷得飞快几乎每秒钟都有几条超过2秒的SQL被记录。我接手这套系统时第一反应不是改代码而是先搞清楚一个最基本的问题这些慢SQL到底是在什么样的数据量级、什么样的索引结构下跑出来的。查了一圈之后发现最大的那张订单表已经积累了接近5000万行数据却不包含任何一个针对查询条件组合设计的索引只有主键索引和两个单列索引。其中订单状态这个字段的区分度极低只有几个固定取值用它单独做索引选择性太差优化器大概率会直接放弃走索引而选择全表扫描。这个场景在真实项目里太常见了。很多团队上线初期数据量小查询随便写都可以秒回于是索引设计就变成了“等出问题再补”的事情。等到表膨胀到千万级甚至亿级以后一条原本毫秒级的查询突然就变成了灾难。在做任何调优之前第一步永远是把问题量化。我在这个项目里做的事情顺序是先打开慢查询日志和全局日志开关把耗时超过500毫秒的SQL全部捞出来按执行次数和平均耗时的乘积排序找出最值得优化的前十个。这一步非常关键因为数据库的瓶颈往往不是单一SQL造成的而是大量中等耗时SQL叠加成的IO压力。只盯着最慢的那一条去优化可能永远无法缓解整体问题。提示排查慢SQL时不要只看单次最慢的语句要按“次数 x 耗时”来排序。一个执行了一万次、平均300毫秒的查询远比一个执行了一次、耗时10秒的查询更值得优先优化。捞出来的SQL里有一个查询特别典型它同时关联了订单表、用户表和商品表并且对订单状态、下单时间范围、用户ID做了三个条件的过滤。原文写法大致如下SELECT o.order_id, u.nickname, p.product_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id LEFT JOIN products p ON o.product_id p.product_id WHERE o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01 AND u.user_type 2 ORDER BY o.created_at DESC LIMIT 20;这条SQL单次执行在那个数据量下需要2.8秒而接口高峰期每分钟会被调用上百次。三条关联表都没有合适的索引支撑其中订单表在WHERE条件上毫无索引可用users表也只在主键上有索引关联时的user_type过滤没法下推回表量巨大。拿到问题SQL之后我没有急着写CREATE INDEX而是先做了一件老DBA都会做的事用EXPLAIN看执行计划。执行计划是数据库告诉你的“我打算怎么跑这条SQL”的说明书不读说明书就乱加索引跟不看地图乱开车没有区别。2. 读懂执行计划里的潜台词type、key、rows和Extra的联动关系EXPLAIN输出里最核心的指标是type、key、rows和Extra。这四个字段组合在一起能直接告诉你当前SQL是不是在裸奔。type字段反映了访问类型最优到最差依次是system、const、eq_ref、ref、range、index、ALL。其中ALL就是全表扫描是性能最差的情况index意味着全索引扫描比全表好一点但也不算高效range代表索引范围扫描这通常是范围查询可以接受的下限ref和eq_ref则是等值关联时的理想状态。我见过不少开发同学一看到语句里用了索引就以为高枕无忧实际上如果type是index或者Extra里有Using filesort性能依然堪忧。rows字段是优化器估算的需要扫描的行数。这个数字不是精确值但能反映数量级。key字段告诉你实际使用到的索引名。Extra字段更是戏多Using where说明存储引擎层没过滤完需要服务层再过滤Using temporary代表用了临时表通常意味着GROUP BY或DISTINCT没能利用索引Using filesort代表文件排序说明ORDER BY没能走索引顺序Using index代表覆盖索引不回表这是最理想的状态。我实际执行EXPLAIN时这条主要SQL的结果是这样的表typekeyrowsExtraordersALLNULL约4000万Using where; Using filesortusersrefPRIMARY1每行Using whereproductsrefPRIMARY1每行NULL看到orders表是ALL且rows估算逼近整表行数Extra里还有Using filesort基本可以判定这个查询的每一步都在做最笨的事情。全表扫描已经够糟还要对4000万行的中间结果排序取前20条代价可想而知。那么问题来了为什么orders表会走到全表扫描其实不只是没有索引这么简单。WHERE条件里status、created_at、user_id三个字段即便其中user_id上有单列索引原表里确实有查询里却没有直接限定user_id的等值条件而是在关联后的users表上加了user_type条件。这样一来orders表的user_id没法提前变成一个固定值优化器认为单独走user_id索引没有什么好处。注意EXPLAIN里出现key为NULL且type为ALL时不只是“没建索引”这么简单还要检查现有索引是否真的能被这条SQL用上。WHERE条件的写法、关联条件的顺序、字段上的函数包裹都会让索引失效。从这条SQL的执行过程来看核心问题可以拆成三个部分第一订单表缺少一个能帮WHERE条件快速定位行的复合索引第二排序字段created_at没有和过滤条件组合进同一个索引第三关联时的users表过滤条件回表查询比例偏高。接下来要做的就是针对这三个问题逐一给出索引策略同时优化掉低效的排序方式。3. 复合索引的设计逻辑列序选择、覆盖索引与排序的三角权衡很多人一听到“建索引”第一反应是“给WHERE条件里每个字段各建一个单列索引”。这个思路在数据量小的时候没毛病数据量大了以后优化器每次只能选择其中一个索引最有选择性然后对其他条件逐个回表过滤效率并没有本质提升。真正有效的做法是建复合索引把多个过滤条件组合在一棵B树里。但复合索引的列顺序至关重要因为它遵循最左前缀原则查询条件必须从索引最左列开始匹配跳列会导致后续列失效。针对订单表我最终设计的索引是这样一个ALTER TABLE orders ADD INDEX idx_status_time_user (status, created_at, user_id);列顺序的选择逻辑是这样的status筛选后能快速缩小范围放在最左边created_at是一个范围条件放在其次user_id作为进一步过滤条件放在最后。这样设计之后WHERE status 1 AND created_at ... AND created_at ... 就可以通过索引直接定位到status1的索引段再在段内用created_at做范围扫描把扫描范围从4000万行压缩到几万行。但这个设计还差一步。原查询里ORDER BY created_at DESC如果created_at列已经在复合索引里并且是等值匹配之后的范围检索Order By是否能用索引排序答案是在这个索引结构下排序依然不能用。原因是ORDER BY字段前面有status 1的等值条件这样status等于1的所有行的created_at已经是按索引顺序排列的直接用索引顺序取出并反向遍历就行。等等这里有个细节。如果WHERE里只用了status一个等值条件那么索引的第二个字段created_at天然就是有序的ORDER BY created_at就能直接用索引顺序不需要额外排序。但我们的WHERE里created_at本身也是一个范围条件这时created_at已经是范围扫描的边界第三个字段user_id才是范围扫描过程中顺序不定的一列。让我重新整理一下逻辑以idx_status_time_userstatus, created_at, user_id为例当查询条件为 status 1 AND created_at x AND created_at y 时范围扫描落在索引的中间片段上。此时扫描到的created_at本身是按索引顺序递增的所以如果按ORDER BY created_at DESC取数方向与扫描顺序相反优化器可以直接反向读取索引块来避免filesort。但MySQL 8.0之前的版本对索引反向扫描支持并不好很多时候还是会出现Using filesort。我实测的情况是在MySQL 8.0下反序索引扫描已经被良好支持Extra里没有再出现Using filesort类型的排名从ALL变成了rangerows估算从4000万降到了18万。但这还没完。要注意一个重要的隐藏成本回表。查询需要SELECT出order_id、created_at还有关联字段user_id、product_id。在19万行范围内回表19万次每行一次随机IO这个代价在高峰期依然不可忽略。于是接下来一步是考虑让索引覆盖查询。覆盖索引的意思是查询需要的所有列都包含在索引结构中这样读取索引块后不需要回表直接返回数据。对于这个场景可以在复合索引里把需要的列加进去把索引变成这样ALTER TABLE orders ADD INDEX idx_status_time_cover (status, created_at, user_id, order_id);order_id是主键InnoDB的二级索引会自动携带主键值不需要额外加。真正的覆盖列是order_id不对二级索引默认包含主键order_id可以不用单独加进去。需要覆盖的核心是要避免回表但在实际业务里很多时候SELECT的列很多想把所有列都塞进索引不现实。所以更务实的策略是把WHERE和ORDER BY完全用索引解决SELECT的多个字段允许回表但要确保回表发生在已经缩小的数据集上。经验覆盖索引不是必须追求的目标。真正要追求的是“回表面”足够小。把数据从4000万缩到2万后回表2万次的成本完全可以接受但如果回表发生在4000万的扫描结果上则必然拖垮数据库。这个订单查询在加了复合索引之后执行计划发生了明显变化表typekeyrowsExtraordersrangeidx_status_time_user约18万Using index conditionusersrefPRIMARY1每行Using whereproductsrefPRIMARY1每行NULL这里还出现了Using index condition代表触发了MySQL的索引条件下推。这个特性允许存储引擎层在使用索引扫描时直接把某些查询条件下推到索引层判断减少回表次数。整体来看这条SQL单次执行已经从2.8秒降到了280毫秒左右基本解决了主要问题。4. 查询重写是第二战场函数包裹、隐式转换与关联顺序的陷阱索引建完之后并不意味着万事大吉。我常说一句话索引只能帮你快速找到数据但如果SQL本身写法有问题索引再完美也发挥不出来。这个项目的后续优化就是在剩余的一批中慢SQL里不断踩坑和重写。第一个坑是函数包裹索引列。某条统计SQL写了WHERE DATE(created_at) 2024-10-01看起来没什么问题但对created_at列施加DATE函数后索引就失效了性能直接跌回全表扫描。正确写法是改成范围查询WHERE created_at 2024-10-01 00:00:00 AND created_at 2024-10-02 00:00:00这里面的底层原因是B树索引按列值排序存储一旦对列值做了函数变换索引里的顺序就无法直接匹配变换后的值优化器只能放弃索引。类似的还有在索引列上做算术运算、字符串拼接等。第二个坑是隐式类型转换。某表的mobile字段是VARCHAR类型查询却传入了整数条件WHERE mobile 13800138000MySQL会把VARCHAR列隐式转换为数字再比较导致索引失效。实际排查时EXPLAIN结果里type为ALL而写成WHERE mobile 13800138000之后就变成了ref。这类问题在代码里往往非常隐蔽因为应用层的参数类型从没刻意指定SQL拼出来就是数字字面量。第三个坑是关联顺序和驱动表选择。在优化器能正确判断时它会自动选择小表驱动大表但一旦SQL里用了LEFT JOIN且位置颠倒或者关联表的统计信息不准确优化器可能选错驱动表。我在某条报表SQL里看到orders表作为被驱动表时本应按照索引去逐行匹配但驱动表选取出现问题后它变成了全表扫描。解决办法是改写关联顺序把选择性强的过滤条件所在表作为驱动表并在被驱动表的关联列上确保索引存在。提示排查隐式转换和函数包裹问题时最直接的方法是查看EXPLAIN结果里key是否为NULL以及rows是否忽然变成全表行数。一旦出现这种情况优先检查索引列有没有被函数、算术或类型转换“污染”。另外一个比较经典的重写场景是分页深翻页。原业务有一个管理后台的列表页用户翻到第1000页时SQL生成了LIMIT 20000, 20MySQL需要扫描前20020行然后丢弃前20000行。这个写法在大数据量下即使有索引也会随着页数加深而越来越慢。这个项目的处理方式是改成基于游标的翻页不跳页只做“下一页”的场景用上一次返回的最后一条记录的ID做条件过滤WHERE created_at last_seen_created_at AND status 1 ORDER BY created_at DESC LIMIT 20;配合复合索引这个改写让深翻页的查询时间从1.2秒降到了20毫秒以内而且数据量越大优化效果越明显。对于必须支持随机跳页的场景可以考虑延迟关联先只查出主键ID再用主键关联回原表取完整数据。5. 索引选择的双子策略选择性优先还是空间优先在给字段设计索引的时候经常要做一个权衡是优先选择性字段区分度还是优先索引体积。这个决策直接影响索引的存储成本和查询收益。先明确一个基本概念区分度。区分度可以用 COUNT(DISTINCT column) / COUNT(*) 来估算。区分度越高表示每个索引值对应的行数越少过滤效果越好。性别字段区分度只有2状态字段通常也只有3到5个取值这种字段单独建索引选择性和直接全表扫没什么区别。但现实中没有那么绝对。像status这种低区分度字段单独建索引没用可一旦放到复合索引的最左列配合后续高区分度字段反而可以快速收敛范围。这是很多调优实践的常用套路低选择性字段当“粗筛”高选择性字段当“精筛”。我还见过一个特别有意思的案例某日志场景的表业务查询条件主要是app_id和时间范围其中app_id的区分度不高不低一个app_id能对应几百万行日志但总表行数有几个亿。如果只建app_id单列索引索引选择性仍然不高如果只建时间索引单日时间范围可能命中几万行加上app_id过滤后降到几百行。最终方案是建了(app_id, create_time)复合索引让时间的范围定位在一棵更小的子树里查询速度直接提升了两个数量级。挑选索引时还须考虑写入成本。每次插入、更新、删除都要同步维护所有二级索引。索引数量越多写放大越高。在这套订单系统里我观察到orders表本身已经有两个单列索引加上复合索引索引总量变成了四个。写入性能在优化后依然稳定是因为该表的写入量并不算极端但如果在高并发写入场景每增加一个索引都要提前压测评估写入延迟。经验索引设计要克制。每增加一个索引都是一次以读换写的交易。建议用“查询收益假设计算”来决策如果一个索引能让查询耗时从2秒降到200毫秒而相关查询每天只执行十次那么收益有限但如果每天执行上万次这个索引就必须建。6. 从单点到全局优化结果验证、慢查询防线与回退预案单条SQL优化完成不等于调优结束。我在这套系统上做的最后一件事是把优化成果量化记录并建立一套防止回潮的机制。验证环节我在一个只读的备库上复现了优化前的慢SQL记录执行时间然后加索引后同环境再跑一次记录对比结果。同时用EXPLAIN记录优化后的type、rows、Extra把这些信息固化到文档里方便后续同事看到这条SQL时知道它的设计意图。这个动作看起来小但在人员流动频繁的团队里非常值得做能避免别人看到索引后觉得多余而误删。慢查询防线方面我在监控系统里把慢查询阈值从2秒降到800毫秒并设置了告警规则任何一条SQL单次执行超过1秒或者执行次数超过每分钟50次且单次超过500毫秒就触发页面端通知。这一步非常实用因为很多性能问题不是爆发式的而是透过大量小慢SQL累积出来的。降阈值后几乎每天都能在问题变严重之前看到苗头。回退预案这个东西团队内部经常忽略。线上加索引在MySQL 8.0里通常支持在线DDL不会锁表但也不排除某些老版本或特殊引擎下索引构建会引发主从延迟。我在正式环境操作的时候会先估算索引构建时间选择业务低峰期执行并提前写好回退SQL。回退预案的核心内容只有一条如果需要把索引删掉应该执行哪条语句、会不会影响当前正在运行的查询万一影响如何降级这些都要提前想清楚。注意在线DDL不代表完全无风险。极大表加索引可能消耗大量临时空间和IO导致主库性能抖动甚至主从延迟。建议先在备库执行加索引并观察延迟确认安全后再在切换后的主库执行。在整个项目里我最终的成果是慢查询数量从每天超过3000条降到了个位数订单相关接口的P99延迟从3.8秒降到了260毫秒数据库IO利用率从70%多降到了25%左右。这些数字没有玄学成分每一步都能从执行计划、索引结构和SQL重写里找到对应关系。回到最初那个问题为什么一张订单表会拖垮整个服务表面上是查询慢实质上是索引策略缺失和SQL写法退化长期累积的结果。调优不是某个晚上的灵光一现而是把问题量化、把执行计划读懂、把索引设计权衡清楚再配合持续监控和一线验证一步步逼近数据库真正的高效运行状态。