1. 项目全景与优化思路拆解折腾了大半个月我们这个代号“39”的查询性能优化专项总算收尾了。背景其实挺典型的业务库的报表接口越跑越慢凌晨的批处理任务从半小时膨胀到三个小时一线运营点个页面要转圈七八秒投诉工单堆了一叠。公共的数据库团队一查慢查询日志里Top SQL全是那几张快照表和订单明细表有些查询单次执行能跑到十几秒连带把CPU和IO都顶满了。于是整个后端小组临时抽人组了个专项从定位到优化再到压测验证完整走了一遍。先说清楚这个专项到底解决什么问题核心就是查询性能优化针对的是数据库读链路中的慢SQL、索引失效、深分页、JOIN失控等一系列问题。输出物不是一份报告而是可以直接复用的方法集——包括瓶颈定位工具链、SQL改写套路、索引设计规范、架构级缓解手段以及一套回归压测方案。适合谁参考后端开发、专职DBA、数据分析师还有那些一个人在维护小团队数据库的全栈同学。就算你没遇到过这么极端的慢查询这套排查思路也能帮你养成“先定位再动手”的习惯不至于一上来就盲加索引。这个专项为什么值得单独复盘因为它最大的价值不在于某个优化点本身而在于整条“定位—分析—改写—验证”的闭环。很多团队遇到查询慢第一反应是“加索引”加了没用就“加缓存”缓存也兜不住就开始骂数据库。这种乱枪打鸟的做法运气好能糊弄过去运气不好反而会把问题越搞越复杂。我们在这次专项里踩过不少坑也验证了不少方法下面把整个过程拆开来讲全部基于我们实际操作的案例。1.1 启动之前先把边界画清楚这大概是整个专项里最重要的一步很多人却跳过去了。接到“查询性能优化”这个任务时第一件事不是打开慢查询日志而是把优化边界定义清楚。我们当时花了一天时间拉着业务方和DBA一起确认了三个问题哪些查询是真正需要治理的可接受的性能基线是什么优化到什么程度算验收通过边界不清晰后续所有工作都会扯皮。比如“订单明细页要变快”这个描述没法干活。我们最终把目标量化成了三条核心报表接口的p95响应时间从原来的6.8秒降到800毫秒以内慢查询日志中超过2秒的SQL条数降低90%以上凌晨批处理任务整体耗时从187分钟压到40分钟以内。有了这些数字后面每一步优化是否有效都能用数据说话而不是靠感觉“好像快了一点”。同时还要摸清数据底数。当时我们统计了几个关键指标订单明细表大概1.2亿行快照表接近4亿行核心表都以时间字段为主索引次要查询条件散落在客户ID、订单状态、渠道编号等字段上。这个底数摸清之后很多问题的原因其实已经浮出水面了——一张几亿行的表如果查询条件不能命中有效索引全表扫描的IO成本就是天文数字。1.2 瓶颈定位的分层排查顺序做性能优化最忌讳的就是跳着查问题东一榔头西一棒子。我们内部总结了一个排查顺序从外层到内层逐层收敛网络链路与连接池、缓存层、SQL语句本身、索引设计、数据模型、硬件资源。这个顺序不是随便定的越外层的问题修起来成本越低排查也越简单所以先做排除法。举个具体的例子。专项里接到一个反馈说某个列表接口时快时慢慢的时候能到10秒。第一反应可能是SQL有问题但查了执行计划之后发现SQL本身没毛病索引也走了。继续往上排查最后定位到是数据库连接池的最大连接数被打满大量请求在排队等连接。业务高峰期一个慢查询把连接池拖垮连带所有正常查询一起遭殃。这就是典型的“堤坝溃口不在SQL层”的案例。我们调整了连接池参数并限流了慢查询问题就缓解了大半。所以每次遇到查询变慢先别急着改SQL。按照这个顺序逐层排除每层只需要花十几分钟做验证省下来的却是后面大量的无效优化工作。我们后来把这一套写成了排查清单挂在团队Wiki里新同学照着走一遍基本不会有太大偏差。1.3 优化手段的分层选型逻辑明确了问题清单之后接下来就是对策选型。优化手段的投入产出比差异极大我们的选型原则是能用低成本手段解决的绝不上重武器。整套方案分成了四个层级优先级从高到低排开层级手段典型场景成本收益L1SQL改写写法不合理、深分页低高L2索引设计查询条件无索引可用低高L3结构重构表结构不合理、冗余字段中中L4架构改造缓存、读写分离、分区表高视场景而定我们在专项里反复强调的是千万不要跳过L1和L2直接上L4。有一次狗急跳墙差点把一张大表做读写分离后来发现真正的瓶颈只是一条子查询写了不该写的关联条件改完SQL之后性能提升了几个数量级完全不需要动架构。反过来如果SQL和索引已经优化透了仍然扛不住流量那再考虑缓存或者分库分表也不迟。2. 瓶颈定位与工具链实战定位慢查询这事工具用对了能省一半时间。专项开搞的第一周我们几乎都在跟各种诊断工具打交道从慢查询日志到执行计划再到系统层的性能剖析一步步把可疑的SQL从茫茫多的请求里筛出来。2.1 慢查询日志的正确打开方式MySQL的慢查询日志是最基础的入口但很多人配置不对导致日志要么没开要么捞出来的全是垃圾。我们当时的配置思路是这样的slow_query_log开启long_query_time设置成1秒测试环境甚至设成0.5秒log_queries_not_using_indexes也打开。这个最后一项特别有用它能把那些没有索引可用的查询全部记录下来哪怕执行时间没超过阈值。很多全表扫描的SQL单次执行可能就几百毫秒但架不住高频调用累积起来的资源消耗才是大头。日志捞出来之后我们写了个简单的脚本做了下聚合排序重点关注两个维度的指标单次执行耗时和累计执行次数。一个SQL执行一次花5秒一天跑10次影响有限但一个SQL执行一次花200毫秒一天被调用50万次那才是真正的资源黑洞。很多优化只盯着执行计划里的慢查询忽略了高频低耗的查询这是一种很常见的盲区。注意慢查询日志本身有性能开销生产环境建议把long_query_time设置为1秒以上并且不要长期全量开启专项排查期间临时开启就够用了。2.2 EXPLAIN 不只是看 type 列EXPLAIN几乎是分析SQL性能的必修课但很多人只看type是不是ALL如果是ALL就认定没走索引然后开始加索引。这种判断太粗糙了。我们在这轮专项里整理了一套完整的分析流程看选择类型看可能用到的索引看实际选中的索引看估算扫描行数看Extra列里面的额外信息。举一次实际案例。我们优化过一条统计SQLEXPLAIN显示type已经是ref了索引也命中了一个联合索引理论上应该没问题。但rows列估算扫描行数是620万filtered只有0.5%这意味着索引筛选完之后还要回表拿600多万行数据再过滤一遍。问题出在联合索引的列顺序上等值条件放在了范围条件后面导致索引的筛选效率大打折扣。调整索引列顺序后扫描行数直接降到3万以下查询时间从9秒降到了0.4秒。如果只看type列这个问题根本发现不了。Extra列里常见的几个坑也要留意Using filesort说明排序没走索引Using temporary说明查询临时表被物化了Using index condition是索引下推生效了但还能优化Using where则意味着有部分过滤条件在存储引擎层之外处理。看到这些标识配合rows和filtered一起分析基本就能锁定问题根因。提示EXPLAIN只是估算统计信息过期时可能给出误导性的rows值。遇到明显与实际不符的情况先执行ANALYZE TABLE刷新统计信息再重新分析。2.3 Profile 与系统层分析SQL本身分析不清的时候就得往系统层面看看。我们用到了MySQL的performance_schema和SHOW PROFILE重点观察语句在哪个阶段耗时最高。比如Sending data阶段耗时高说明数据读取和传输是瓶颈Sorting result阶段耗时高说明排序操作吃掉了大量资源Waiting for table metadata lock则是典型的元数据锁等待背后往往有DDL操作卡住了查询。专项里碰到过一个诡异案例某条查询单看执行计划完全正常索引也走了扫描行数不高但实际执行就是慢。用SHOW PROFILE一查时间几乎全耗在Waiting for table metadata lock上。追踪下去发现之前有人半夜跑了一个大表的ALTER TABLE操作因为数据量大一直没走完导致所有后续查询都在等元数据锁释放。这种问题在SQL层面根本看不出来只能靠profile定位到锁等待然后再处理DDL阻塞。系统层面还能看看SHOW ENGINE INNODB STATUS里的行锁、间隙锁信息以及vmstat、iostat这些常规的CPU、IO指标。我个人的习惯是当慢查询日志和EXPLAIN两轮分析下来依然找不到原因时立刻切换到profile模式大概率能有所发现。3. SQL改写与执行计划调优定位到具体SQL之后最直接有效的动作就是改写SQL本身。这一轮优化里我们处理了不下三十条慢SQL其中相当一部分的问题不在于缺索引而在于查询写法本身就废性能。3.1 N1查询问题N1查询在业务代码里太常见了尤其是用ORM框架的项目。场景是这样的要查一批订单的状态代码里先查订单主表得到一个ID列表然后循环每个ID去数据库查一条明细。订单有500条就要执行500次查询再加上最初的那一次共501次数据库往返。这种写法在数据量小的时候没什么感知一旦列表页翻到几千条数据性能立刻崩塌。我们的改写方案是用一条JOIN或者IN查询替代循环。比如原来是循环执行N次SELECT * FROM order_detail WHERE order_id ?改成一次SELECT * FROM order_detail WHERE order_id IN ( ... )或者直接JOIN主表把数据库往返从N1降成1-2次。更稳妥的做法是使用关联子查询的分页模式避免IN列表过长。实测的效果相当直观某个运营端的批量查询接口原本跑一次要4.7秒改成单条JOIN之后降到310毫秒。这个优化甚至不需要改任何索引纯粹靠消除重复查询就拿到了95%以上的性能提升。代码里使用ORM的同学要特别注意框架的懒加载特性很容易产生N1问题打印SQL日志看一遍循环期间发出了多少条查询语句立刻就能暴露。3.2 隐式转换与函数包裹索引失效的另一个高频原因是对索引列做函数运算或者类型隐式转换。我们有一条SQL查询条件是WHERE create_date 2024-09-01而create_date字段是datetime类型。表面上看类型一致但MySQL在比较时会做隐式转换把字符串转成日期再比较。这种转换本身问题不大真正致命的是对索引列使用函数比如WHERE DATE(create_date) 2024-09-01这就完全破坏了索引的有序性导致优化器放弃索引。改写的原则很简单把函数运算从索引列上挪走。DATE(create_date) 2024-09-01改成create_date 2024-09-01 00:00:00 AND create_date 2024-09-02 00:00:00这样就能命中索引。同理WHERE order_no 1 10086这类对索引列做算术运算的写法也应该改成WHERE order_no 10085。注意如果实在无法避免函数包裹索引列可以尝试为函数表达式建立表达式索引MySQL 8.0支持函数索引但能用改写解决的坚决不加新索引索引越多写入开销越大。3.3 深分页的经典改写分页查询遇到深分页几乎是无解的物理问题。LIMIT 1000000, 20这类写法数据库需要把前面100万行全部扫描出来再丢弃只保留最后20行。扫描的代价全花在了根本不会返回给用户的数据上。我们的标准改写方案是延迟关联也叫覆盖索引子查询。思路是先通过覆盖索引拿到目标行的主键再回表去查询完整行数据。例如-- 低效写法 SELECT * FROM order_table ORDER BY create_time DESC LIMIT 200000, 20; -- 延迟关联改写 SELECT t1.* FROM order_table t1 INNER JOIN ( SELECT id FROM order_table ORDER BY create_time DESC LIMIT 200000, 20 ) t2 ON t1.id t2.id;内层子查询只查询主键和排序字段这两列完全命中覆盖索引MySQL就不需要回表扫描大量数据行拿到20个主键之后外层再回表取完整行IO代价大大降低。这条改写思路在我们批处理任务里的效果非常显著一个拉取增量数据的任务从37分钟直接压到了4分钟。如果再极端一点还可以把分页改成基于游标的方式前端从第200001条开始翻页时不再传页码而是传上一页最后一条记录的排序字段值用WHERE create_time 上次的值 ORDER BY create_time DESC LIMIT 20。这种方式理论上不管翻多少页性能都稳定。4. 索引设计从“加索引”到“会用索引”SQL改写只能解决一部分问题更多时候还是要靠索引把数据访问路径缩短。但这个环节最容易犯的毛病是想当然地加索引不看选择性、不看列顺序、不看业务查询模式。这一节把我们在专项中沉淀下来的索引设计经验详细展开。4.1 联合索引的列序选择联合索引的列顺序直接决定索引的筛选效率。核心原则就一句话把区分度最高的等值条件列放在最前面把范围条件和排序字段往后放。为什么因为联合索引在存储结构上是有序的最左前缀法则决定了只有从左往右依次匹配的列才能被高效利用。我当时处理过一条统计SQL查询条件是WHERE channel ? AND status ? AND created_at ?查询结果还要按created_at排序分页。最优索引设计是(channel, status, created_at)等值列优先范围列和排序列往后放。这样索引既能过滤channel和status又能直接从created_at位置查起还能避免额外的排序操作。怎么判断区分度跑一条SQL算不同值的比例就行SELECT COUNT(DISTINCT channel) / COUNT(*) AS channel_card, COUNT(DISTINCT status) / COUNT(*) AS status_card FROM order_table;哪个列的值分布更分散哪个列就放前面。比如说channel有50个值status只有3个值那么channel放前面过滤效果更好。不过这个原则有个前提——查询模式里存在等值条件。如果一条查询对channel是等值过滤对status是范围过滤那等值的channel仍然放第一status这种范围条件放第二反而是合理的。4.2 覆盖索引与索引下推覆盖索引是个容易忽略但性价比极高的手段。它的原理很直观如果查询需要的所有列都包含在索引里MySQL就不需要回表访问数据行直接从索引结构中就可以拿到全部数据。典型场景是统计类查询比如统计某渠道的订单数量CREATE INDEX idx_channel_status ON order_table(channel, status); SELECT COUNT(*) FROM order_table WHERE channel app AND status 2;这条查询在索引中就能完成过滤和计数EXPLAIN的Extra列会显示Using index表示全程没有回表。对几亿行的表来说省掉回表的代价是数量级的性能差异。我们在专项里给不少高频统计查询都补了这种覆盖索引效果立竿见影。索引下推则稍微隐蔽一点。MySQL 5.6引入了Index Condition Pushdown允许在索引遍历过程中就直接过滤掉不满足条件的记录减少回表次数。在EXPLAIN里表现为Extra列出现Using index condition。这个特性本来是好东西但如果下推条件本身包含选择性很差的字段比如对性别字段做下推优化器可能产生误判。遇到这种情况可以把下推条件从联合索引中挪出去再观察执行计划的变化。提示索引不是越多越好。每一个索引都会拖慢写入和更新速度还会占用额外存储空间。我们专项里的原则是单表索引数量控制在6个以内新加一个索引必须先分析它能不能覆盖至少2-3条高频查询否则宁可不加。4.3 索引失效场景速查表这一节整理一份索引失效和没走索引的常见场景表都是我们实际踩过或者排查时见过的场景示例原因对策LIKE模糊匹配前缀WHERE name LIKE %张%无法利用索引的有序性改为前缀匹配name LIKE 张%或上全文索引对索引列使用函数WHERE DATE(created_at)2024-09-01索引列被函数破坏改写为范围查询或使用函数索引隐式类型转换WHERE phone 13800138000phone是字符串类型不一致导致索引失效统一参数类型为字符串OR条件跨列WHERE a1 OR b2OR导致无法合并索引改写为UNION索引列参与运算WHERE price * 1.1 100索引列被运算包裹把运算挪到常量侧统计信息过期优化器选了错误的索引索引基数估算失真执行ANALYZE TABLE优化器放弃索引数据量小时全表扫描更划算索引读的成本高于全表扫检查参数index_condition_pushdown等这张表不需要背遇到EXPLAIN结果不对劲的时候拿出来对着看一遍基本能覆盖大部分排查场景。5. 架构级优化手段SQL改写和索引设计都做完了有些流量场景依然扛不住这时才轮到架构级手段。我们要说的是在对一个查询做架构优化之前一定要先确认SQL和索引层面已经没有优化空间了。否则架构改了、成本掏了问题反而没根治这是专项里反复出现的教训。5.1 缓存层双刃剑性能优化界流传一句话最好的查询就是不去查询。加缓存本质上是用空间换时间把经常被读取的热数据放到内存里减少数据库压力。我们当时对报表系统的几个高频依赖接口做了本地缓存改造缓存策略是Cache-Aside模式读取时先查缓存不命中再查数据库并回填缓存更新时先更新数据库再删除缓存。实测效果很好数据库QPS降了60%以上接口延迟从几百毫秒降到几十毫秒。但是缓存带来的坑也够写一篇长文。最典型的缓存一致性问题是更新数据库成功、删除缓存失败导致后续请求全部读到旧数据。当时的一个业务数据接口就因为这个出现过数据不一致排查了大半天才发现是缓存删除操作被吞了异常。后续改造中我们采取了延迟双删策略更新数据库后删除缓存sleep 几百毫秒后再删除一次兜底处理并发场景下的脏读概率。注意缓存方案一定要先想清楚缓存击穿、缓存雪崩和缓存穿透三个问题。热点key失效瞬间大量请求同时打到数据库这是击穿缓存整体失效导致数据库被打爆这是雪崩反复查询一个不存在的key每次都穿透到数据库这是穿透。不确定能不能处理这仨坑就不要轻易上缓存。5.2 读写分离与分区表的使用边界读写分离是很多团队逃不开的架构方案把读流量分流到从库主库专注写操作。但这里有个隐性成本主从延迟。专项中一个订单查询接口在读写分离后出现过一次严重事故运营后台刚下单就立刻去查订单结果从库还没同步到这条新数据页面一直显示“订单不存在”。业务方火冒三丈技术团队面红耳赤。我们的经验是读写分离只适用于对实时一致性要求不高的读场景比如报表统计、日志分析、用户行为列表。对于强一致场景要么强制走主库要么接受最终一致性的业务设计。如果没有明确的场景需求这个架构级改造不要轻易上。分区表则更适合时序类数据。我们订单表和快照表都做了时间范围分区查询语句里带created_at范围条件时优化器可以直接裁剪到具体分区极大减少扫描行数。但分区表也有大坑分区键必须出现在查询条件里且分区数不宜过多。分区过度会让单个查询跨上百个分区性能反而更差。我们内部的原则是单分区数据量保持在500万到2000万行之间太多就再拆太少就合并。5.3 预聚合与汇总表的思路最后一个架构级手段是预聚合本质上是“用离线计算换在线查询时间”。对于报表类、统计类查询算实时聚合有时候根本没必要——业务方要看的本来就是日维度的汇总数字完全可以在凌晨批处理或者实时流计算阶段就把中间结果算好落到一张汇总表里查询时直接查汇总表。举个实际改造的案例。我们有一张用户行为统计报表原始数据在明细表里每天几百万条记录。原来的SQL要GROUP BY用户、渠道、日期每跑一次都要扫描上亿行耗时十几分钟。后来我们做了一个小时级预聚合任务把同维度的统计结果提前算好写入汇总表。报表查询从扫描明细表改成直接查汇总表单次查询从11分钟压到了1.8秒。预聚合的成本在于数据延迟和存储冗余。实时性要求高的场景可以选分钟级或者秒级聚合但计算资源消耗会翻倍存储冗余通过设置合理的保留周期来控制比如明细表保留30天汇总表保留两年。权衡这三个指标找到业务可接受的平衡点就行。6. 压测与验收优化有没有效不能靠感觉优化做完最怕的就是“感觉快了不少”然后直接上线。这轮专项给自己立了条规矩每一个优化点都必须有压测数据佐证每一项性能指标都必须有前后对比。没有验收的优化等于白做。这一节是我的底线篇幅值得每个做性能优化的人认真看一遍。6.1 基线压测与前后对比压测的第一步是建立基线。在开始优化之前我们先对核心接口做了一轮压测记录下当时的QPS、p95/p99延迟、错误率、数据库CPU和IO等指标作为基准。没有基线数据后面所有优化都缺乏对照依据。压测工具我们用的是开源的sysbench和内部的压测平台。sysbench用来打磨数据库层面的基础性能具体的业务接口则通过模拟真实请求的方式压测。压测过程中不能只盯着平均值高百分位延迟才是用户真实体验的反映。p99达到2秒可能意味着有1%的用户要忍受两秒以上的卡顿这种体验问题平均值根本掩盖了。阶段QPSp95延迟p99延迟慢查询数/小时优化前12006.8s12.1s830SQL优化后28002.3s4.5s220索引优化后5200480ms980ms15架构优化后7600210ms390ms2上面这张表是专项中期某一轮压测的真实数据数值做了一定脱敏处理。可以看到每一层优化都有实实在在的收益SQL改写消除重复查询提升了一倍多的吞吐索引设计又把延迟降了一个数量级最后架构层面的缓存和汇总表让整体表现彻底稳定下来。数据不会骗人有这个完整的过程记录跟业务方和领导汇报时也有底气。6.2 灰度上线与执行计划基线管理优化上线不能一把梭。我们的标准流程是先在预发环境完成一轮全量压测确认无异常后再在生产环境按1%流量灰度观察。灰度期间重点盯三个指标接口错误率、慢查询日志和数据库主从延迟。只要观察期内这三个指标没有明显劣化再逐步扩大灰度比例到10%、50%最后全量。这个流程里还有一个很多团队忽略的动作执行计划基线管理。我们每次优化完一条核心SQL都会把优化前后的EXPLAIN输出存档包括key列、rows列、Extra列的快照放在专门的目录里。这些执行计划就是基线。后续就算没人动SQL数据库统计信息变化、数据量增长或者MySQL版本升级也可能导致执行计划突然发生变化性能随之暴跌。有了基线比对恢复时能快速定位是哪个环节变了。提示一次性能优化上线尽量只改一个变量。不要同时调整索引、改写SQL、改连接池参数否则出了问题很难归因到具体是哪一项导致性能回退。忍一忍一个个来效率反而是最高的。7. 典型问题排查实录最后这一章我整理了几个专项期间最典型的排查案例。这些问题在技术社区里被反复讨论过但纸上得来终觉浅实际碰到时的判断过程比答案本身更值得参考。7.1 问题一明明有索引优化器就是不选它一段时间的慢查询日志里频繁出现一条查询明明在相关字段上建立了索引EXPLAIN却显示全表扫描。排查思路是这样的先确认索引是否真的存在然后看统计信息是否过期。我们执行了SHOW INDEX FROM确认索引在再EXPLAIN发现rows估算值异常偏高比实际行数大了几十倍。这就是典型的统计信息失真。对索引列执行ANALYZE TABLE后统计信息刷新优化器立刻选中了正确索引查询从3.6秒降到40毫秒。这类问题的诱因通常是大量数据导入或删除操作后未及时更新统计信息。MySQL的innodb_stats_auto_recalc默认开启但大批量变更时不一定能及时触发所以针对高频表定期执行ANALYZE TABLE是有必要的。7.2 问题二ORDER BY LIMIT 引发的文件排序崩溃另一条SQL从性能上看没毛病WHERE条件过滤度高扫描行数少但ORDER BY的字段不在索引里导致每次查询都要Using filesort。数据量大时文件排序会临时落盘磁盘IO直接被打满。优化方式是调整联合索引把排序列包含进去让MySQL直接从索引的有序性中拿到排序结果从而消除filesort。这个案例的启示是对于排序需求固定的查询索引设计之初就应该把排序列纳入联合索引而不是事后补救。另外还可以考虑减小排序数据量比如只取主键排序后再回表这是前面深分页优化相同思路的延伸。7.3 问题三两表JOIN优化器选了错误驱动表JOIN查询的性能很大程度上取决于驱动表和被驱动表的选择。理想状态下MySQL会用小表驱动大表即先用小结果集去大表里匹配但在优化器估算不准的时候会选反。我们遇到过一个商家维度关联订单明细的查询两个表执行计划显示驱动表选反了导致被驱动表走了全表扫描查询跑了11秒。当时的排查步骤是先用STRAIGHT_JOIN强制指定驱动表顺序验证猜想确认问题后通过调整关联字段的索引分布让优化器走上正轨。还要检查两表关联字段的字符集和排序规则是否一致字符集不一致会导致索引失效这是JOIN性能问题里最隐蔽的坑之一。注意STRAIGHT_JOIN或FORCE INDEX等手段只能作为临时验证用不建议长期固化在代码里。数据库版本升级、数据分布变化之后人工指定的执行计划可能反而成为性能瓶颈。正确的做法是分析优化器为什么选错从根本上修正。7.4 问题四OR条件绕晕了索引WHERE status 1 OR channel app这条查询看似两个字段都有索引MySQL却可能选择全表扫描。原因很简单OR条件意味着满足任一分支即可优化器需要合并两个索引的结果并去重这个成本往往高于直接全表扫描。我们的应对策略是把OR查询改写成UNION。保持语义不变的情况下-- 原写法 SELECT * FROM order_table WHERE status 1 OR channel app; -- 改写后 SELECT * FROM order_table WHERE status 1 UNION ALL SELECT * FROM order_table WHERE channel app AND status 1;两条分支各自走索引再合并结果。注意UNION自带去重会多一次排序业务语义允许的情况下优先用UNION ALL并把重叠条件显式排除掉避免结果重复。7.5 问题五IN列表爆炸导致临时表与回表放大分页接口里传几百个ID的场景越来越多WHERE id IN (几百个值)的执行计划通常没问题但实际性能会因回表次数过多而恶化。IN列表被展开后优化器可能会构建临时表来存储这些值然后逐条匹配临时表结构和回表次数都变成性能短板。我们的处理办法是将大IN列表分片成多个小批次的查询每次只传50-100个值后续在业务代码里合并结果。实测同样的数据量大IN查询耗时4.5秒分片后总耗时只有800毫秒。另一个思路是如果ID来源本身就是一张子表尝试改写成JOIN让数据库内部完成匹配有时比IN更高效。写在最后这个专项折腾下来我个人最深的体会是查询性能优化不是一个动作而是一套方法论。它考验的不是你会多少工具和命令而是你能不能从现象出发一层层剥离表象找到真正的瓶颈然后用成本最低的手段解决问题。索引谁都会加SQL谁都会写但什么时候加、怎么设计、如何验证才是拉开差距的地方。如果只让我留一条建议那就是把每次优化的EXPLAIN快照、参数配置、压测数据都记录下来。优化完不是结束后面每一次数据量增长、版本升级、业务变化都可能让已优化的查询重新退化。有记录就有基线有基线就能快速响应这套资产比任何单一优化技巧都值钱。最后分享一个小技巧把EXPLAIN输出里key、rows、Extra三列当作一条SQL的“体检三项”下次遇到慢查询先不要讨论加不加索引先把这三项摆到桌面上大家基于同一份事实去讨论方案效率完全不同。这也是“39”专项里最大的收获希望能帮你少走点弯路。 
长河君 · 2026/10/2 3:39:19 · 阅读 1.2k 数据库查询性能优化实战：慢SQL定位、索引设计与架构改造全复盘
1. 项目全景与优化思路拆解折腾了大半个月我们这个代号“39”的查询性能优化专项总算收尾了。背景其实挺典型的业务库的报表接口越跑越慢凌晨的批处理任务从半小时膨胀到三个小时一线运营点个页面要转圈七八秒投诉工单堆了一叠。公共的数据库团队一查慢查询日志里Top SQL全是那几张快照表和订单明细表有些查询单次执行能跑到十几秒连带把CPU和IO都顶满了。于是整个后端小组临时抽人组了个专项从定位到优化再到压测验证完整走了一遍。先说清楚这个专项到底解决什么问题核心就是查询性能优化针对的是数据库读链路中的慢SQL、索引失效、深分页、JOIN失控等一系列问题。输出物不是一份报告而是可以直接复用的方法集——包括瓶颈定位工具链、SQL改写套路、索引设计规范、架构级缓解手段以及一套回归压测方案。适合谁参考后端开发、专职DBA、数据分析师还有那些一个人在维护小团队数据库的全栈同学。就算你没遇到过这么极端的慢查询这套排查思路也能帮你养成“先定位再动手”的习惯不至于一上来就盲加索引。这个专项为什么值得单独复盘因为它最大的价值不在于某个优化点本身而在于整条“定位—分析—改写—验证”的闭环。很多团队遇到查询慢第一反应是“加索引”加了没用就“加缓存”缓存也兜不住就开始骂数据库。这种乱枪打鸟的做法运气好能糊弄过去运气不好反而会把问题越搞越复杂。我们在这次专项里踩过不少坑也验证了不少方法下面把整个过程拆开来讲全部基于我们实际操作的案例。1.1 启动之前先把边界画清楚这大概是整个专项里最重要的一步很多人却跳过去了。接到“查询性能优化”这个任务时第一件事不是打开慢查询日志而是把优化边界定义清楚。我们当时花了一天时间拉着业务方和DBA一起确认了三个问题哪些查询是真正需要治理的可接受的性能基线是什么优化到什么程度算验收通过边界不清晰后续所有工作都会扯皮。比如“订单明细页要变快”这个描述没法干活。我们最终把目标量化成了三条核心报表接口的p95响应时间从原来的6.8秒降到800毫秒以内慢查询日志中超过2秒的SQL条数降低90%以上凌晨批处理任务整体耗时从187分钟压到40分钟以内。有了这些数字后面每一步优化是否有效都能用数据说话而不是靠感觉“好像快了一点”。同时还要摸清数据底数。当时我们统计了几个关键指标订单明细表大概1.2亿行快照表接近4亿行核心表都以时间字段为主索引次要查询条件散落在客户ID、订单状态、渠道编号等字段上。这个底数摸清之后很多问题的原因其实已经浮出水面了——一张几亿行的表如果查询条件不能命中有效索引全表扫描的IO成本就是天文数字。1.2 瓶颈定位的分层排查顺序做性能优化最忌讳的就是跳着查问题东一榔头西一棒子。我们内部总结了一个排查顺序从外层到内层逐层收敛网络链路与连接池、缓存层、SQL语句本身、索引设计、数据模型、硬件资源。这个顺序不是随便定的越外层的问题修起来成本越低排查也越简单所以先做排除法。举个具体的例子。专项里接到一个反馈说某个列表接口时快时慢慢的时候能到10秒。第一反应可能是SQL有问题但查了执行计划之后发现SQL本身没毛病索引也走了。继续往上排查最后定位到是数据库连接池的最大连接数被打满大量请求在排队等连接。业务高峰期一个慢查询把连接池拖垮连带所有正常查询一起遭殃。这就是典型的“堤坝溃口不在SQL层”的案例。我们调整了连接池参数并限流了慢查询问题就缓解了大半。所以每次遇到查询变慢先别急着改SQL。按照这个顺序逐层排除每层只需要花十几分钟做验证省下来的却是后面大量的无效优化工作。我们后来把这一套写成了排查清单挂在团队Wiki里新同学照着走一遍基本不会有太大偏差。1.3 优化手段的分层选型逻辑明确了问题清单之后接下来就是对策选型。优化手段的投入产出比差异极大我们的选型原则是能用低成本手段解决的绝不上重武器。整套方案分成了四个层级优先级从高到低排开层级手段典型场景成本收益L1SQL改写写法不合理、深分页低高L2索引设计查询条件无索引可用低高L3结构重构表结构不合理、冗余字段中中L4架构改造缓存、读写分离、分区表高视场景而定我们在专项里反复强调的是千万不要跳过L1和L2直接上L4。有一次狗急跳墙差点把一张大表做读写分离后来发现真正的瓶颈只是一条子查询写了不该写的关联条件改完SQL之后性能提升了几个数量级完全不需要动架构。反过来如果SQL和索引已经优化透了仍然扛不住流量那再考虑缓存或者分库分表也不迟。2. 瓶颈定位与工具链实战定位慢查询这事工具用对了能省一半时间。专项开搞的第一周我们几乎都在跟各种诊断工具打交道从慢查询日志到执行计划再到系统层的性能剖析一步步把可疑的SQL从茫茫多的请求里筛出来。2.1 慢查询日志的正确打开方式MySQL的慢查询日志是最基础的入口但很多人配置不对导致日志要么没开要么捞出来的全是垃圾。我们当时的配置思路是这样的slow_query_log开启long_query_time设置成1秒测试环境甚至设成0.5秒log_queries_not_using_indexes也打开。这个最后一项特别有用它能把那些没有索引可用的查询全部记录下来哪怕执行时间没超过阈值。很多全表扫描的SQL单次执行可能就几百毫秒但架不住高频调用累积起来的资源消耗才是大头。日志捞出来之后我们写了个简单的脚本做了下聚合排序重点关注两个维度的指标单次执行耗时和累计执行次数。一个SQL执行一次花5秒一天跑10次影响有限但一个SQL执行一次花200毫秒一天被调用50万次那才是真正的资源黑洞。很多优化只盯着执行计划里的慢查询忽略了高频低耗的查询这是一种很常见的盲区。注意慢查询日志本身有性能开销生产环境建议把long_query_time设置为1秒以上并且不要长期全量开启专项排查期间临时开启就够用了。2.2 EXPLAIN 不只是看 type 列EXPLAIN几乎是分析SQL性能的必修课但很多人只看type是不是ALL如果是ALL就认定没走索引然后开始加索引。这种判断太粗糙了。我们在这轮专项里整理了一套完整的分析流程看选择类型看可能用到的索引看实际选中的索引看估算扫描行数看Extra列里面的额外信息。举一次实际案例。我们优化过一条统计SQLEXPLAIN显示type已经是ref了索引也命中了一个联合索引理论上应该没问题。但rows列估算扫描行数是620万filtered只有0.5%这意味着索引筛选完之后还要回表拿600多万行数据再过滤一遍。问题出在联合索引的列顺序上等值条件放在了范围条件后面导致索引的筛选效率大打折扣。调整索引列顺序后扫描行数直接降到3万以下查询时间从9秒降到了0.4秒。如果只看type列这个问题根本发现不了。Extra列里常见的几个坑也要留意Using filesort说明排序没走索引Using temporary说明查询临时表被物化了Using index condition是索引下推生效了但还能优化Using where则意味着有部分过滤条件在存储引擎层之外处理。看到这些标识配合rows和filtered一起分析基本就能锁定问题根因。提示EXPLAIN只是估算统计信息过期时可能给出误导性的rows值。遇到明显与实际不符的情况先执行ANALYZE TABLE刷新统计信息再重新分析。2.3 Profile 与系统层分析SQL本身分析不清的时候就得往系统层面看看。我们用到了MySQL的performance_schema和SHOW PROFILE重点观察语句在哪个阶段耗时最高。比如Sending data阶段耗时高说明数据读取和传输是瓶颈Sorting result阶段耗时高说明排序操作吃掉了大量资源Waiting for table metadata lock则是典型的元数据锁等待背后往往有DDL操作卡住了查询。专项里碰到过一个诡异案例某条查询单看执行计划完全正常索引也走了扫描行数不高但实际执行就是慢。用SHOW PROFILE一查时间几乎全耗在Waiting for table metadata lock上。追踪下去发现之前有人半夜跑了一个大表的ALTER TABLE操作因为数据量大一直没走完导致所有后续查询都在等元数据锁释放。这种问题在SQL层面根本看不出来只能靠profile定位到锁等待然后再处理DDL阻塞。系统层面还能看看SHOW ENGINE INNODB STATUS里的行锁、间隙锁信息以及vmstat、iostat这些常规的CPU、IO指标。我个人的习惯是当慢查询日志和EXPLAIN两轮分析下来依然找不到原因时立刻切换到profile模式大概率能有所发现。3. SQL改写与执行计划调优定位到具体SQL之后最直接有效的动作就是改写SQL本身。这一轮优化里我们处理了不下三十条慢SQL其中相当一部分的问题不在于缺索引而在于查询写法本身就废性能。3.1 N1查询问题N1查询在业务代码里太常见了尤其是用ORM框架的项目。场景是这样的要查一批订单的状态代码里先查订单主表得到一个ID列表然后循环每个ID去数据库查一条明细。订单有500条就要执行500次查询再加上最初的那一次共501次数据库往返。这种写法在数据量小的时候没什么感知一旦列表页翻到几千条数据性能立刻崩塌。我们的改写方案是用一条JOIN或者IN查询替代循环。比如原来是循环执行N次SELECT * FROM order_detail WHERE order_id ?改成一次SELECT * FROM order_detail WHERE order_id IN ( ... )或者直接JOIN主表把数据库往返从N1降成1-2次。更稳妥的做法是使用关联子查询的分页模式避免IN列表过长。实测的效果相当直观某个运营端的批量查询接口原本跑一次要4.7秒改成单条JOIN之后降到310毫秒。这个优化甚至不需要改任何索引纯粹靠消除重复查询就拿到了95%以上的性能提升。代码里使用ORM的同学要特别注意框架的懒加载特性很容易产生N1问题打印SQL日志看一遍循环期间发出了多少条查询语句立刻就能暴露。3.2 隐式转换与函数包裹索引失效的另一个高频原因是对索引列做函数运算或者类型隐式转换。我们有一条SQL查询条件是WHERE create_date 2024-09-01而create_date字段是datetime类型。表面上看类型一致但MySQL在比较时会做隐式转换把字符串转成日期再比较。这种转换本身问题不大真正致命的是对索引列使用函数比如WHERE DATE(create_date) 2024-09-01这就完全破坏了索引的有序性导致优化器放弃索引。改写的原则很简单把函数运算从索引列上挪走。DATE(create_date) 2024-09-01改成create_date 2024-09-01 00:00:00 AND create_date 2024-09-02 00:00:00这样就能命中索引。同理WHERE order_no 1 10086这类对索引列做算术运算的写法也应该改成WHERE order_no 10085。注意如果实在无法避免函数包裹索引列可以尝试为函数表达式建立表达式索引MySQL 8.0支持函数索引但能用改写解决的坚决不加新索引索引越多写入开销越大。3.3 深分页的经典改写分页查询遇到深分页几乎是无解的物理问题。LIMIT 1000000, 20这类写法数据库需要把前面100万行全部扫描出来再丢弃只保留最后20行。扫描的代价全花在了根本不会返回给用户的数据上。我们的标准改写方案是延迟关联也叫覆盖索引子查询。思路是先通过覆盖索引拿到目标行的主键再回表去查询完整行数据。例如-- 低效写法 SELECT * FROM order_table ORDER BY create_time DESC LIMIT 200000, 20; -- 延迟关联改写 SELECT t1.* FROM order_table t1 INNER JOIN ( SELECT id FROM order_table ORDER BY create_time DESC LIMIT 200000, 20 ) t2 ON t1.id t2.id;内层子查询只查询主键和排序字段这两列完全命中覆盖索引MySQL就不需要回表扫描大量数据行拿到20个主键之后外层再回表取完整行IO代价大大降低。这条改写思路在我们批处理任务里的效果非常显著一个拉取增量数据的任务从37分钟直接压到了4分钟。如果再极端一点还可以把分页改成基于游标的方式前端从第200001条开始翻页时不再传页码而是传上一页最后一条记录的排序字段值用WHERE create_time 上次的值 ORDER BY create_time DESC LIMIT 20。这种方式理论上不管翻多少页性能都稳定。4. 索引设计从“加索引”到“会用索引”SQL改写只能解决一部分问题更多时候还是要靠索引把数据访问路径缩短。但这个环节最容易犯的毛病是想当然地加索引不看选择性、不看列顺序、不看业务查询模式。这一节把我们在专项中沉淀下来的索引设计经验详细展开。4.1 联合索引的列序选择联合索引的列顺序直接决定索引的筛选效率。核心原则就一句话把区分度最高的等值条件列放在最前面把范围条件和排序字段往后放。为什么因为联合索引在存储结构上是有序的最左前缀法则决定了只有从左往右依次匹配的列才能被高效利用。我当时处理过一条统计SQL查询条件是WHERE channel ? AND status ? AND created_at ?查询结果还要按created_at排序分页。最优索引设计是(channel, status, created_at)等值列优先范围列和排序列往后放。这样索引既能过滤channel和status又能直接从created_at位置查起还能避免额外的排序操作。怎么判断区分度跑一条SQL算不同值的比例就行SELECT COUNT(DISTINCT channel) / COUNT(*) AS channel_card, COUNT(DISTINCT status) / COUNT(*) AS status_card FROM order_table;哪个列的值分布更分散哪个列就放前面。比如说channel有50个值status只有3个值那么channel放前面过滤效果更好。不过这个原则有个前提——查询模式里存在等值条件。如果一条查询对channel是等值过滤对status是范围过滤那等值的channel仍然放第一status这种范围条件放第二反而是合理的。4.2 覆盖索引与索引下推覆盖索引是个容易忽略但性价比极高的手段。它的原理很直观如果查询需要的所有列都包含在索引里MySQL就不需要回表访问数据行直接从索引结构中就可以拿到全部数据。典型场景是统计类查询比如统计某渠道的订单数量CREATE INDEX idx_channel_status ON order_table(channel, status); SELECT COUNT(*) FROM order_table WHERE channel app AND status 2;这条查询在索引中就能完成过滤和计数EXPLAIN的Extra列会显示Using index表示全程没有回表。对几亿行的表来说省掉回表的代价是数量级的性能差异。我们在专项里给不少高频统计查询都补了这种覆盖索引效果立竿见影。索引下推则稍微隐蔽一点。MySQL 5.6引入了Index Condition Pushdown允许在索引遍历过程中就直接过滤掉不满足条件的记录减少回表次数。在EXPLAIN里表现为Extra列出现Using index condition。这个特性本来是好东西但如果下推条件本身包含选择性很差的字段比如对性别字段做下推优化器可能产生误判。遇到这种情况可以把下推条件从联合索引中挪出去再观察执行计划的变化。提示索引不是越多越好。每一个索引都会拖慢写入和更新速度还会占用额外存储空间。我们专项里的原则是单表索引数量控制在6个以内新加一个索引必须先分析它能不能覆盖至少2-3条高频查询否则宁可不加。4.3 索引失效场景速查表这一节整理一份索引失效和没走索引的常见场景表都是我们实际踩过或者排查时见过的场景示例原因对策LIKE模糊匹配前缀WHERE name LIKE %张%无法利用索引的有序性改为前缀匹配name LIKE 张%或上全文索引对索引列使用函数WHERE DATE(created_at)2024-09-01索引列被函数破坏改写为范围查询或使用函数索引隐式类型转换WHERE phone 13800138000phone是字符串类型不一致导致索引失效统一参数类型为字符串OR条件跨列WHERE a1 OR b2OR导致无法合并索引改写为UNION索引列参与运算WHERE price * 1.1 100索引列被运算包裹把运算挪到常量侧统计信息过期优化器选了错误的索引索引基数估算失真执行ANALYZE TABLE优化器放弃索引数据量小时全表扫描更划算索引读的成本高于全表扫检查参数index_condition_pushdown等这张表不需要背遇到EXPLAIN结果不对劲的时候拿出来对着看一遍基本能覆盖大部分排查场景。5. 架构级优化手段SQL改写和索引设计都做完了有些流量场景依然扛不住这时才轮到架构级手段。我们要说的是在对一个查询做架构优化之前一定要先确认SQL和索引层面已经没有优化空间了。否则架构改了、成本掏了问题反而没根治这是专项里反复出现的教训。5.1 缓存层双刃剑性能优化界流传一句话最好的查询就是不去查询。加缓存本质上是用空间换时间把经常被读取的热数据放到内存里减少数据库压力。我们当时对报表系统的几个高频依赖接口做了本地缓存改造缓存策略是Cache-Aside模式读取时先查缓存不命中再查数据库并回填缓存更新时先更新数据库再删除缓存。实测效果很好数据库QPS降了60%以上接口延迟从几百毫秒降到几十毫秒。但是缓存带来的坑也够写一篇长文。最典型的缓存一致性问题是更新数据库成功、删除缓存失败导致后续请求全部读到旧数据。当时的一个业务数据接口就因为这个出现过数据不一致排查了大半天才发现是缓存删除操作被吞了异常。后续改造中我们采取了延迟双删策略更新数据库后删除缓存sleep 几百毫秒后再删除一次兜底处理并发场景下的脏读概率。注意缓存方案一定要先想清楚缓存击穿、缓存雪崩和缓存穿透三个问题。热点key失效瞬间大量请求同时打到数据库这是击穿缓存整体失效导致数据库被打爆这是雪崩反复查询一个不存在的key每次都穿透到数据库这是穿透。不确定能不能处理这仨坑就不要轻易上缓存。5.2 读写分离与分区表的使用边界读写分离是很多团队逃不开的架构方案把读流量分流到从库主库专注写操作。但这里有个隐性成本主从延迟。专项中一个订单查询接口在读写分离后出现过一次严重事故运营后台刚下单就立刻去查订单结果从库还没同步到这条新数据页面一直显示“订单不存在”。业务方火冒三丈技术团队面红耳赤。我们的经验是读写分离只适用于对实时一致性要求不高的读场景比如报表统计、日志分析、用户行为列表。对于强一致场景要么强制走主库要么接受最终一致性的业务设计。如果没有明确的场景需求这个架构级改造不要轻易上。分区表则更适合时序类数据。我们订单表和快照表都做了时间范围分区查询语句里带created_at范围条件时优化器可以直接裁剪到具体分区极大减少扫描行数。但分区表也有大坑分区键必须出现在查询条件里且分区数不宜过多。分区过度会让单个查询跨上百个分区性能反而更差。我们内部的原则是单分区数据量保持在500万到2000万行之间太多就再拆太少就合并。5.3 预聚合与汇总表的思路最后一个架构级手段是预聚合本质上是“用离线计算换在线查询时间”。对于报表类、统计类查询算实时聚合有时候根本没必要——业务方要看的本来就是日维度的汇总数字完全可以在凌晨批处理或者实时流计算阶段就把中间结果算好落到一张汇总表里查询时直接查汇总表。举个实际改造的案例。我们有一张用户行为统计报表原始数据在明细表里每天几百万条记录。原来的SQL要GROUP BY用户、渠道、日期每跑一次都要扫描上亿行耗时十几分钟。后来我们做了一个小时级预聚合任务把同维度的统计结果提前算好写入汇总表。报表查询从扫描明细表改成直接查汇总表单次查询从11分钟压到了1.8秒。预聚合的成本在于数据延迟和存储冗余。实时性要求高的场景可以选分钟级或者秒级聚合但计算资源消耗会翻倍存储冗余通过设置合理的保留周期来控制比如明细表保留30天汇总表保留两年。权衡这三个指标找到业务可接受的平衡点就行。6. 压测与验收优化有没有效不能靠感觉优化做完最怕的就是“感觉快了不少”然后直接上线。这轮专项给自己立了条规矩每一个优化点都必须有压测数据佐证每一项性能指标都必须有前后对比。没有验收的优化等于白做。这一节是我的底线篇幅值得每个做性能优化的人认真看一遍。6.1 基线压测与前后对比压测的第一步是建立基线。在开始优化之前我们先对核心接口做了一轮压测记录下当时的QPS、p95/p99延迟、错误率、数据库CPU和IO等指标作为基准。没有基线数据后面所有优化都缺乏对照依据。压测工具我们用的是开源的sysbench和内部的压测平台。sysbench用来打磨数据库层面的基础性能具体的业务接口则通过模拟真实请求的方式压测。压测过程中不能只盯着平均值高百分位延迟才是用户真实体验的反映。p99达到2秒可能意味着有1%的用户要忍受两秒以上的卡顿这种体验问题平均值根本掩盖了。阶段QPSp95延迟p99延迟慢查询数/小时优化前12006.8s12.1s830SQL优化后28002.3s4.5s220索引优化后5200480ms980ms15架构优化后7600210ms390ms2上面这张表是专项中期某一轮压测的真实数据数值做了一定脱敏处理。可以看到每一层优化都有实实在在的收益SQL改写消除重复查询提升了一倍多的吞吐索引设计又把延迟降了一个数量级最后架构层面的缓存和汇总表让整体表现彻底稳定下来。数据不会骗人有这个完整的过程记录跟业务方和领导汇报时也有底气。6.2 灰度上线与执行计划基线管理优化上线不能一把梭。我们的标准流程是先在预发环境完成一轮全量压测确认无异常后再在生产环境按1%流量灰度观察。灰度期间重点盯三个指标接口错误率、慢查询日志和数据库主从延迟。只要观察期内这三个指标没有明显劣化再逐步扩大灰度比例到10%、50%最后全量。这个流程里还有一个很多团队忽略的动作执行计划基线管理。我们每次优化完一条核心SQL都会把优化前后的EXPLAIN输出存档包括key列、rows列、Extra列的快照放在专门的目录里。这些执行计划就是基线。后续就算没人动SQL数据库统计信息变化、数据量增长或者MySQL版本升级也可能导致执行计划突然发生变化性能随之暴跌。有了基线比对恢复时能快速定位是哪个环节变了。提示一次性能优化上线尽量只改一个变量。不要同时调整索引、改写SQL、改连接池参数否则出了问题很难归因到具体是哪一项导致性能回退。忍一忍一个个来效率反而是最高的。7. 典型问题排查实录最后这一章我整理了几个专项期间最典型的排查案例。这些问题在技术社区里被反复讨论过但纸上得来终觉浅实际碰到时的判断过程比答案本身更值得参考。7.1 问题一明明有索引优化器就是不选它一段时间的慢查询日志里频繁出现一条查询明明在相关字段上建立了索引EXPLAIN却显示全表扫描。排查思路是这样的先确认索引是否真的存在然后看统计信息是否过期。我们执行了SHOW INDEX FROM确认索引在再EXPLAIN发现rows估算值异常偏高比实际行数大了几十倍。这就是典型的统计信息失真。对索引列执行ANALYZE TABLE后统计信息刷新优化器立刻选中了正确索引查询从3.6秒降到40毫秒。这类问题的诱因通常是大量数据导入或删除操作后未及时更新统计信息。MySQL的innodb_stats_auto_recalc默认开启但大批量变更时不一定能及时触发所以针对高频表定期执行ANALYZE TABLE是有必要的。7.2 问题二ORDER BY LIMIT 引发的文件排序崩溃另一条SQL从性能上看没毛病WHERE条件过滤度高扫描行数少但ORDER BY的字段不在索引里导致每次查询都要Using filesort。数据量大时文件排序会临时落盘磁盘IO直接被打满。优化方式是调整联合索引把排序列包含进去让MySQL直接从索引的有序性中拿到排序结果从而消除filesort。这个案例的启示是对于排序需求固定的查询索引设计之初就应该把排序列纳入联合索引而不是事后补救。另外还可以考虑减小排序数据量比如只取主键排序后再回表这是前面深分页优化相同思路的延伸。7.3 问题三两表JOIN优化器选了错误驱动表JOIN查询的性能很大程度上取决于驱动表和被驱动表的选择。理想状态下MySQL会用小表驱动大表即先用小结果集去大表里匹配但在优化器估算不准的时候会选反。我们遇到过一个商家维度关联订单明细的查询两个表执行计划显示驱动表选反了导致被驱动表走了全表扫描查询跑了11秒。当时的排查步骤是先用STRAIGHT_JOIN强制指定驱动表顺序验证猜想确认问题后通过调整关联字段的索引分布让优化器走上正轨。还要检查两表关联字段的字符集和排序规则是否一致字符集不一致会导致索引失效这是JOIN性能问题里最隐蔽的坑之一。注意STRAIGHT_JOIN或FORCE INDEX等手段只能作为临时验证用不建议长期固化在代码里。数据库版本升级、数据分布变化之后人工指定的执行计划可能反而成为性能瓶颈。正确的做法是分析优化器为什么选错从根本上修正。7.4 问题四OR条件绕晕了索引WHERE status 1 OR channel app这条查询看似两个字段都有索引MySQL却可能选择全表扫描。原因很简单OR条件意味着满足任一分支即可优化器需要合并两个索引的结果并去重这个成本往往高于直接全表扫描。我们的应对策略是把OR查询改写成UNION。保持语义不变的情况下-- 原写法 SELECT * FROM order_table WHERE status 1 OR channel app; -- 改写后 SELECT * FROM order_table WHERE status 1 UNION ALL SELECT * FROM order_table WHERE channel app AND status 1;两条分支各自走索引再合并结果。注意UNION自带去重会多一次排序业务语义允许的情况下优先用UNION ALL并把重叠条件显式排除掉避免结果重复。7.5 问题五IN列表爆炸导致临时表与回表放大分页接口里传几百个ID的场景越来越多WHERE id IN (几百个值)的执行计划通常没问题但实际性能会因回表次数过多而恶化。IN列表被展开后优化器可能会构建临时表来存储这些值然后逐条匹配临时表结构和回表次数都变成性能短板。我们的处理办法是将大IN列表分片成多个小批次的查询每次只传50-100个值后续在业务代码里合并结果。实测同样的数据量大IN查询耗时4.5秒分片后总耗时只有800毫秒。另一个思路是如果ID来源本身就是一张子表尝试改写成JOIN让数据库内部完成匹配有时比IN更高效。写在最后这个专项折腾下来我个人最深的体会是查询性能优化不是一个动作而是一套方法论。它考验的不是你会多少工具和命令而是你能不能从现象出发一层层剥离表象找到真正的瓶颈然后用成本最低的手段解决问题。索引谁都会加SQL谁都会写但什么时候加、怎么设计、如何验证才是拉开差距的地方。如果只让我留一条建议那就是把每次优化的EXPLAIN快照、参数配置、压测数据都记录下来。优化完不是结束后面每一次数据量增长、版本升级、业务变化都可能让已优化的查询重新退化。有记录就有基线有基线就能快速响应这套资产比任何单一优化技巧都值钱。最后分享一个小技巧把EXPLAIN输出里key、rows、Extra三列当作一条SQL的“体检三项”下次遇到慢查询先不要讨论加不加索引先把这三项摆到桌面上大家基于同一份事实去讨论方案效率完全不同。这也是“39”专项里最大的收获希望能帮你少走点弯路。