1. 先别急着加索引慢查询背后藏着的三层真相1.1 慢SQL三分靠看七分靠查如何绕过表象定位真凶我接手过不少“慢查询优化”的活儿很多开发同学的第一反应都是“加索引”。但说句实在话索引只是优化链条里的最后一环甚至经常不是最关键的那一环。手上一张千万级的数据表一条关联查询跑了十几秒你噼里啪啦加完索引结果发现执行时间只降了10%那种挫败感我太熟悉了。为什么会这样因为一条SQL慢下来表象是“执行时间长”但背后通常是三层问题的叠加第一层是语句本身写得有问题导致优化器根本没机会利用索引第二层是表和索引的设计与数据分布不匹配索引建了选择性却低得可怜第三层才是真正的索引缺失或失效。这三层问题不拆开你加索引就是盲人摸象。我建议的排查顺序是固定的先看执行计划再看表统计信息然后做最小化实验最后才动手改索引。执行计划里最关键的几个字段——type、rows、filtered、Extra——能直接告诉你优化器打算怎么干活。比如type是ALL说明全表扫描这时候再考虑索引type是ref或者range说明索引其实走了但rows很大这时候问题可能在索引选择性上而不是索引存在性上。很多人在第一步就判断错了方向。1.2 数据分布、基数与统计信息索引判断的第一道门槛索引有没有效从根上讲取决于基数Cardinality。说白了就是某一列上不同值的数量。性别列只有“男”“女”两个值基数就是2你给这种列建索引大多数情况下优化器会直接无视它因为即使走索引也要回表查一半的数据还不如全表扫描来得痛快。这个过程优化器是精打细算过的它依赖的是统计信息而不是你以为的“这个字段经常在WHERE里出现”。但统计信息有一个容易被忽略的坑它不是实时更新的。MySQL里的InnoDB引擎统计信息的更新受innodb_stats_auto_recalc和innodb_stats_persistent控制默认情况下表数据变化超过一定比例才会自动重新计算。如果你刚导入了一批数据或者删掉了一大批旧数据统计信息可能还停留在老状态优化器基于过期统计信息做出的判断自然不准。遇到这种情况ANALYZE TABLE跑一下很多诡异的不走索引问题当场就解决了。实操里我还会额外看一个东西字段的NULL值占比和重复值分布。完全均匀分布的数据索引效果最好但现实中的数据往往是倾斜的比如订单表里的状态字段90%都是“已完成”你查那10%的“处理中”时索引可能还有点用查“已完成”时优化器大概率选择全表扫描。所以建索引之前先跑一条分组统计看看数据长什么样再决定策略这个习惯能帮你少走很多弯路。1.3 为什么“看着该走索引”却没走优化器决策里的隐形成本还有一种特别迷惑人的情况索引明明存在条件也符合最左前缀但优化器就是不走。我在处理一个用户中心的慢查询时就遇到过WHERE user_id 12345 AND status 1组合索引(user_id, status)也建得好好的但执行计划显示全表扫描。后来查下去才发现问题出在user_id列的类型是VARCHAR而传入的参数是整数。虽然在SQL里写WHERE user_id 12345看起来毫无问题但MySQL会做隐式类型转换把字符串列转成数字再比较。这个转换导致索引失效。这就是典型的“看着该走索引实际优化器在帮你擦屁股”。在调优的时候我会习惯性地把这条SQL的字段类型和参数类型逐一对一遍凡是条件两边类型不一致的先改掉再说因为隐式转换带来的索引失效是最容易被忽略、也最容易修的一类问题。2. 索引策略的底层博弈从B树到覆盖索引再到冗余取舍2.1 B树不是用来背的聚簇索引与二级索引的读写代价很多人面试时B树背得滚瓜烂熟但实际建索引时完全用不上。B树这套结构抽象出来就两句话数据按序排列查询走树路径。对于InnoDB来说整张表的数据本身就是一个B树主键索引就是聚簇索引叶子节点存的是整行数据其他索引叫二级索引叶子节点存的是主键值。这两者的差异直接决定了读写代价。走二级索引查一条记录需要先在二级索引的B树里找到主键然后拿着主键再到聚簇索引的B树里回表取出完整行。一次回表就是一次随机IO。你建了索引命中率很高但回表次数一多查询照样快不起来。所以性能突破的关键不一定在于“建了多少索引”而在于“能不能减少回表”。引申出来的一个实用原则是主键设计要考虑写入代价。如果你用一个超长的UUID作为主键二级索引里存的每个主键值都很长索引体积膨胀内存里能放下的索引页变少IO次数自然增加。而且UUID无序插入时B树频繁分裂写放大非常明显。现在的新项目我倾向于用自增主键或有序的雪花ID不是不能接受UUID而是要让索引这棵树的叶子节点尽量保持顺序写入。2.2 最左前缀的失效现场组合索引设计中的顺序陷阱组合索引的核心是最左前缀原则这个大家都听过但真正设计时很容易掉进顺序陷阱。最常见的错误有两种一是把区分度最高的列放前面二是只考虑查询条件不考虑排序和分组。先看第一种。WHERE a ? AND b ?如果a列的基数只有几十b列每种值对应几万行把a放前面就意味着索引第一层就过滤不掉多少数据优化器会认为这个索引没什么用。正确的做法通常是让过滤能力强的列靠左。但注意这不绝对如果条件里有范围查询比如a 1000 AND b ?那a放前面会让b的索引完全派不上用场因为范围右边的列无法用于定位。这种情况我会反过来把等值条件b放前面。第二种分布更隐蔽。一个报表查询条件只有WHERE create_time ?但ORDER BY user_id LIMIT 10如果你单独建(create_time)索引MySQL只能先查出满足时间条件的所有数据再做一次排序行数一多就是filesort。但如果把索引设计成(create_time, user_id)因为B树本身就是有序结构ORDER BY user_id在时间等值的情况下可以直接利用索引顺序省掉排序这对深分页场景收益极大。我见过太多人只盯着WHERE完全忘了ORDER BY结果SQL改了索引也没用。2.3 覆盖索引、索引下推与回表用一次查询说清三种优化手段用一个实际场景把这三个概念串起来。假设有一张订单表SQL是SELECT order_id, status, pay_time FROM order_record WHERE user_id 12345 AND status PAID;如果只有一个(user_id, status)的二级索引那么MySQL先通过索引找到匹配的主键再回表三次拿到order_id、status、pay_time。回表次数取决于匹配行数行数越多IO越多。改进方式有两种。第一种叫覆盖索引把索引扩展成(user_id, status, order_id, pay_time)。因为InnoDB的二级索引叶子节点包含了索引定义的所有列查询需要的字段都在索引里MySQL就不需要回表了直接扫索引就能返回结果。对于高频查询这种“用空间换一次随机IO”的代价非常划算。第二种叫索引下推Index Condition PushdownICP。如果只保留(user_id, status)索引但SQL里status本来就是索引的一部分MySQL在索引遍历过程中就会先过滤status而不是把每一条满足user_id的记录都回表后再过滤。5.6以后默认开启ICP很多人可能没注意过它的存在但它确实在默默减少回表次数。实操中我会用EXPLAIN的Extra列来确认看到Using index说明走了覆盖索引看到Using index condition说明走了ICP两者都是好事。如果看到Using where说明索引只负责定位了一部分剩下的过滤动作在做回表之后才发生这时候就要考虑能不能通过调整索引或改写SQL把过滤条件推给索引。2.4 索引冗余的代价写放大与维护成本如何量化索引不是越多越好这是我反复强调的一条。每一行数据插入的时候所有二级索引都要同步维护一份每次更新如果涉及索引列B树还要做节点分裂和结构调整。换句话说写操作的成本会随着索引数量成倍增长。很多时候你只看到了查询变快了没看到写入变慢了、锁等待变多了、binlog变大了。量化一个“冗余索引”其实很简单。假设已有索引(a, b, c)你再建一个(a, b)后者就是前者的最左前缀完全冗余。工具上可以用pt-duplicate-key-checker扫描但就算没有工具手动看一下SHOW INDEX FROM table也能分辨。我的原则是能复用的前缀绝不复建宁可把索引设计得宽一点也不要为单独查询堆一堆重复前缀的小索引。索引占用的不只是磁盘更是内存缓冲池的宝贵空间冗余索引会让整个实例的热点命中率下降最后所有查询一起变慢。3. 查询重写的实战套路把逻辑拆到优化器喜欢的形状3.1 隐式类型转换看似无害的条件如何干掉索引前面提到了一次隐式类型转换这里展开说说。MySQL的规则是如果一个字符串列和一个数字比较它会尝试把字符串转成数字。这个大坑的影响面非常广不仅仅是WHERE条件JOIN的关联条件也一样。我曾经优化过一个数仓抽数任务两个表关联字段一个VARCHAR一个BIGINT结果整个任务跑了40分钟改成统一类型后直接缩到4分钟。同类的问题还包括日期比较。比如你在一个DATETIME字段上做WHERE create_time 2024-01-01MySQL会把字符串隐式转成日期这个本身不是大问题。但如果你写的是WHERE DATE(create_time) 2024-01-01这就是在索引列上套了函数索引直接失效。正确写法是范围比较WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这个改写不需要函数参与索引能正常命中。类似的还有WHERE YEAR(create_time) 2024也应该改写成上述形式。总结起来就一句话索引列上尽量不要做任何运算不管是隐式的还是显式的都要把运算挪到参数一侧。3.2 OR/IN/EXISTS/JOIN的取舍逻辑与半连接改写很多人纠结OR和IN要不要改写EXISTS和JOIN哪个快。其实在MySQL 8.0之前IN和EXISTS的优化策略差异很大但8.0引入了半连接SEMI JOIN优化很多情况会自动转换。真正需要人工干预的是一些极端场景。先说OR。WHERE a 1 OR b 2在a、b各自有索引的情况下MySQL可能选择Index Merge也可能选择全表扫描。一个比较稳妥的做法是拆成UNION ALL让每一段SQL各自走索引再合并结果。如果两段结果集有交集再考虑用UNION去重。但这种改写的收益依赖数据分布如果每个条件都能过滤掉大部分数据收益明显如果条件本身就很宽泛改写也没意义。再说子查询和JOIN。我见过大量把JOIN改写成IN子查询的习惯试图“减少连接次数”。但在MySQL里JOIN和IN的优化路径并不一样。5.6之前相关子查询的执行方式是逐行驱动效率极差所以老一辈开发者总结出“能JOIN就JOIN别用IN”的经验。到了8.0子查询会被自动改造为半连接性能差距缩小但有些边缘场景还是会出幺蛾子。我的建议是先在执行计划里确认有没有出现materialize或者dependent这些关键词。dependent表示相关子查询往往是性能重灾区看到这个果断改写成临时表或JOIN。3.3 分页深翻页问题延迟关联与游标分页的取舍分页是每个业务系统都绕不开的点也是SQL优化里最容易被低估的一环。ORDER BY id LIMIT 10000, 20这种写法看着简单实际MySQL要先把前10020行取出来排序然后丢掉前10000行。数据量越大越往后翻这个“丢弃”的成本越高。等到OFFSET到了几十万查询时间飙到秒级完全不意外。一个有效的套路是延迟关联。所谓延迟关联就是先用覆盖索引快速定位到目标行的主键ID再通过ID去关联原表获取完整数据SELECT o.* FROM order_record o INNER JOIN ( SELECT id FROM order_record WHERE user_type 1 ORDER BY create_time DESC LIMIT 10000, 20 ) t ON o.id t.id;内层查询只扫二级索引不需要回表跳过的行数再多成本也远低于外层直接扫全表。这个改写我在多个项目里都用过深分页场景收益通常是数量级的。但如果产品本身是无限下拉流或者“上一页下一页”的模式更好的方案是游标分页。比如记录上一页最后一条数据的create_time和id下一页直接WHERE create_time ? OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC LIMIT 20。这样不管翻多深扫描范围始终只有20条。业务改造量稍大但性能上限高得多而且不会因为中间插了数据导致分页错乱。我的习惯是一次性列表用延迟关联滚动加载用游标分页。3.4 聚合与排序的优化空间临时文件和filesort的避让涉及GROUP BY、ORDER BY、DISTINCT的查询如果执行计划里的Extra列出现了Using temporary或Using filesort就要警惕了。这两个词都不一定致命但都意味着MySQL需要额外的内存或磁盘空间来完成计算。Using filesort不是真的用文件排序更准确的说法是“无法利用索引顺序完成排序需要额外的排序操作”。排序数据量小在内存里排数据量大会在tmpdir里建临时文件产生磁盘IO。优化方向很明确让排序字段参与索引而且排序顺序要和索引完全一致。比如索引是(a, b)你用ORDER BY a ASC, b ASC就能直接利用索引顺序如果你写ORDER BY b ASC, a ASC顺序不匹配优化器只能filesort。GROUP BY同理。GROUP BY a, b和索引(a, b)顺序一致时MySQL可以边扫边分组不需要临时表。另外很多人不知道MAX()、MIN()在索引前缀上可以直接取两端值根本不需要扫全表。比如SELECT MAX(id) FROM order_recordInnoDB直接读取索引最右侧叶子节点执行计划秒回。这种小细节在实际优化中经常能带来惊喜。4. 执行计划与性能验证用EXPLAIN之外的手段逼近真相4.1 EXPLAIN到底在看什么type、rows、Extra的读法执行计划是SQL优化的基础但很多人查了执行计划等于没查。EXPLAIN SELECT...的输出里我第一眼看type。从好到坏大致是system、const、eq_ref、ref、range、index、ALL。前面几种都算不错的访问方式到了index说明在扫全索引到了ALL就是全表扫描。一个合格的优化目标是把关键查询的type至少推到range以上。第二眼看rows这是优化器估算的需要扫描的行数。注意是估算值不是真实值。如果rows和实际数据量差距过大十有八九是统计信息过期了。这时候先ANALYZE TABLE再看计划通常比直接改SQL更有效。第三眼看Extra。这里的信息密度最高Using where表示在存储引擎层过滤完之后Server层又做了一次过滤Using index表示覆盖索引Using index condition表示ICPUsing temporary和Using filesort就是前面提到的问题信号Using join buffer则说明连接过程没有合适的索引需要用join buffer来存中间结果。这每一项都是下一步优化方向的直接线索。拿到执行计划先别急着改SQL把Extra里出现的每一个关键词查明白已经能解决一半问题。4.2 optimizer trace优化器为什么不听你的有一种让人血压飙升的情况你做了一个改写理论上规划得很好但执行计划还是老样子。这时候EXPLAIN看不出优化器的心理活动得请出隐藏工具optimizer trace。在MySQL中你可以针对当前会话开启优化器追踪SET optimizer_trace enabledon; SELECT * FROM order_record WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10; SELECT * FROM information_schema.OPTIMIZER_TRACE\G输出里会详细记录优化器每一步的思考过程包括它考虑了哪些索引、为什么淘汰了某个索引、最终选择了什么执行方案。我在一次真实调优中发现优化器拒绝某个索引的原因竟然是回表成本估算高于全表扫描而这个估算偏差来自主键长度过大。看到这份trace我立刻把主键从UUID改成了自增IDSQL一行没动查询时间从2秒降到了80毫秒。没有trace的话我可能还在纠结索引结构怎么改。optimizer trace的缺点是信息量大一张几千行的JSON不是人读的。实际会搜关键词considered_access_paths看索引候选cause看放弃原因rows_estimation看行数估算。这几个节点基本能覆盖大部分定位需求。生产环境不建议长期开启trace但针对一条慢SQL开一个会话得到结果后立刻关掉是完全没有问题的。4.3 线上压测与火焰图从单条SQL到系统吞吐的放大镜单条SQL优化完成不代表系统就快了。数据库瓶颈往往不在某一条SQL上而在多条SQL叠加出来的锁竞争、IO排队和网络延迟上。所以我把优化流程的最后一环放在压测和监控上。工具上sysbench和jmeter都能做压测但更贴近业务的做法是直接抓生产环境或预发环境的流量回放观察优化前后的吞吐和延迟分位数变化。如果怀疑SQL已经引发了系统层面的CPU或IO问题我会看火焰图。生成火焰图的方式很多perf record配合FlameGraph脚本是常见一套。但注意数据库实例的火焰图分析要比应用复杂因为MySQL内部线程多且状态机复杂我不会一上来就分析函数级细节而是先看整体CPU使用率分布在哪个子系统是排序、是临时表创建、还是行锁等待。如果火焰图里大面积出现与排序相关的函数那就说明还有大批量filesort在作祟需要回到SQL和索引层面再排查一轮。这里有一个我自己常用的经验不要只看95分位延迟要看99分位和最大延迟。慢SQL对系统的影响往往是“尾部延迟”被拖垮平均延迟看起来还行但每隔一段时间就卡一下这种间歇性抖动最容易被人忽视。通过监控工具把这些尾部请求一一揪出来结合慢查询日志时间线通常能精准定位是哪条SQL在特定数据分布下变成了定时炸弹。5. 一次近千万级订单查询的完整优化复盘5.1 业务场景与原始SQL问题出现在哪里模拟项目X是一个订单管理系统订单表order_record数据量在850万左右。业务上有一个核心查询用户在后台按商户、时间范围和状态筛选订单前端要做列表分页。原始SQL大致长这样SELECT * FROM order_record WHERE merchant_id 10086 AND status IN (PAID, REFUNDING) AND create_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200, 20;生产环境已经有一个索引idx_merchant_status_time(merchant_id, status, create_time)但查询时间平均在4.5秒高峰期接近8秒后端接口超时频繁。一开始团队的反应是“索引都有了啊为什么还慢”于是先把SQL拿到测试库一顿EXPLAIN。执行计划显示type为rangerows估算接近30万Extra里有Using filesort。三个信息一组合问题就清楚了虽然索引用上了但status条件是IN优化器只能利用索引到merchant_id这一层然后对status做范围扫描扫描范围覆盖了三个状态里所有符合条件的记录总量非常大。并且因为create_time也在索引里理论上排序可以直接利用但IN导致索引连续扫描的顺序没办法严格等于create_time顺序于是走了filesort。5.2 从索引调整到查询改写每一步改动与收益对照第一版改动把现有索引调整为idx_merchant_time(merchant_id, create_time)。为什么去掉status因为status的IN条件本身就破坏了最左前缀的等值定位能力把它放在中间既浪费空间又让排序优化无从谈起。去掉之后索引的等值前缀只有merchant_idcreate_time作为范围条件继续参与定位同时ORDER BY create_time DESC可以完全利用索引顺序Using filesort消失。这一版上线后查询时间降到约1.2秒。第二版改动改造分页。上面只解决了排序问题但LIMIT 200, 20还在MySQL仍然要扫前220条之前的记录。改成延迟关联写法SELECT o.* FROM order_record o INNER JOIN ( SELECT id FROM order_record WHERE merchant_id 10086 AND create_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200, 20 ) t ON o.id t.id;内层查询只需要扫描二级索引每行只取id和索引列不需要回表再配合idx_merchant_time的有序性排序成本大幅降低。这一版上线后查询时间降到了180毫秒左右收益接近数量级。第三版微调处理业务侧的筛选变化。后台还存在“只看退款中”的筛选场景这种场景下status其实是单一等值条件。我在模块化SQL时单独为这类场景保留了一个(merchant_id, status, create_time)索引。听起来有点打脸但这是合理的冗余两种查询模式分别使用不同的索引各取所需代价只是额外一份索引的写入成本。换来的收益是每种模式都能走最精准的执行路径。5.3 常见的优化误区与我的取舍经验复盘这次优化有几个误区值得单独拎出来说。第一个误区是“索引覆盖的字段越多越好”。我把SELECT *改成延迟关联后没有顺手再把查询列塞进索引。因为你一旦把所有需要的字段都塞进二级索引索引体积膨胀缓冲池命中率下降整体收益反而可能是负的。覆盖索引适合高频、固定、小字段集的查询不适合SELECT *这种宽查询。第二个误区是“执行计划变好就代表性能变好”。第一版改动后执行计划确实漂亮了但线上实测仍然有1秒左右的延迟因为大分页问题还在。SQL优化必须以线上响应时间为准不能只看EXPLAIN的输出。每次改完我都会在测试环境用相同的数据分布跑一遍真实查询记录耗时再在预发环境验证一轮最后才动生产。第三个误区是“不敢动索引怕影响其他查询”。这种谨慎是对的但不能因噎废食。正确做法是通过information_schema查询索引使用情况看看哪些索引的index_name在慢查询日志里根本没出现过或者从performance_schema的表table_io_waits_summary_by_index_usage里看每个索引的统计。数据会告诉你哪个索引该留哪个索引纯属心理安慰。从索引策略到查询性能这条链路里真正值钱的不是某一招而是一套稳定的排查方法先理解数据分布再读懂执行计划最后用实验验证每一步收益。我自己踩过不少坑最深的一个体会是索引是手段性能是结果但中间隔着一个叫作“理解你正在查询的数据”的鸿沟。跨越这个鸿沟你才能从“加索引碰运气”进化到“每一次改动都有明确预期”。如果你最近也被一条慢SQL折磨得焦头烂额不妨先把EXPLAIN结果贴出来对照着rows和Extra逐项分析大概率能找到比加索引更快的解法。等到你开始习惯用optimizer trace和压测数据来验证每一次判断时你就会发现SQL优化不是玄学而是一道可以稳定复现的数学题。 
长河君 · 2026/10/13 4:30:01 · 阅读 1.2k 慢查询优化实战：从索引策略到数据分布，千万级订单查询复盘
1. 先别急着加索引慢查询背后藏着的三层真相1.1 慢SQL三分靠看七分靠查如何绕过表象定位真凶我接手过不少“慢查询优化”的活儿很多开发同学的第一反应都是“加索引”。但说句实在话索引只是优化链条里的最后一环甚至经常不是最关键的那一环。手上一张千万级的数据表一条关联查询跑了十几秒你噼里啪啦加完索引结果发现执行时间只降了10%那种挫败感我太熟悉了。为什么会这样因为一条SQL慢下来表象是“执行时间长”但背后通常是三层问题的叠加第一层是语句本身写得有问题导致优化器根本没机会利用索引第二层是表和索引的设计与数据分布不匹配索引建了选择性却低得可怜第三层才是真正的索引缺失或失效。这三层问题不拆开你加索引就是盲人摸象。我建议的排查顺序是固定的先看执行计划再看表统计信息然后做最小化实验最后才动手改索引。执行计划里最关键的几个字段——type、rows、filtered、Extra——能直接告诉你优化器打算怎么干活。比如type是ALL说明全表扫描这时候再考虑索引type是ref或者range说明索引其实走了但rows很大这时候问题可能在索引选择性上而不是索引存在性上。很多人在第一步就判断错了方向。1.2 数据分布、基数与统计信息索引判断的第一道门槛索引有没有效从根上讲取决于基数Cardinality。说白了就是某一列上不同值的数量。性别列只有“男”“女”两个值基数就是2你给这种列建索引大多数情况下优化器会直接无视它因为即使走索引也要回表查一半的数据还不如全表扫描来得痛快。这个过程优化器是精打细算过的它依赖的是统计信息而不是你以为的“这个字段经常在WHERE里出现”。但统计信息有一个容易被忽略的坑它不是实时更新的。MySQL里的InnoDB引擎统计信息的更新受innodb_stats_auto_recalc和innodb_stats_persistent控制默认情况下表数据变化超过一定比例才会自动重新计算。如果你刚导入了一批数据或者删掉了一大批旧数据统计信息可能还停留在老状态优化器基于过期统计信息做出的判断自然不准。遇到这种情况ANALYZE TABLE跑一下很多诡异的不走索引问题当场就解决了。实操里我还会额外看一个东西字段的NULL值占比和重复值分布。完全均匀分布的数据索引效果最好但现实中的数据往往是倾斜的比如订单表里的状态字段90%都是“已完成”你查那10%的“处理中”时索引可能还有点用查“已完成”时优化器大概率选择全表扫描。所以建索引之前先跑一条分组统计看看数据长什么样再决定策略这个习惯能帮你少走很多弯路。1.3 为什么“看着该走索引”却没走优化器决策里的隐形成本还有一种特别迷惑人的情况索引明明存在条件也符合最左前缀但优化器就是不走。我在处理一个用户中心的慢查询时就遇到过WHERE user_id 12345 AND status 1组合索引(user_id, status)也建得好好的但执行计划显示全表扫描。后来查下去才发现问题出在user_id列的类型是VARCHAR而传入的参数是整数。虽然在SQL里写WHERE user_id 12345看起来毫无问题但MySQL会做隐式类型转换把字符串列转成数字再比较。这个转换导致索引失效。这就是典型的“看着该走索引实际优化器在帮你擦屁股”。在调优的时候我会习惯性地把这条SQL的字段类型和参数类型逐一对一遍凡是条件两边类型不一致的先改掉再说因为隐式转换带来的索引失效是最容易被忽略、也最容易修的一类问题。2. 索引策略的底层博弈从B树到覆盖索引再到冗余取舍2.1 B树不是用来背的聚簇索引与二级索引的读写代价很多人面试时B树背得滚瓜烂熟但实际建索引时完全用不上。B树这套结构抽象出来就两句话数据按序排列查询走树路径。对于InnoDB来说整张表的数据本身就是一个B树主键索引就是聚簇索引叶子节点存的是整行数据其他索引叫二级索引叶子节点存的是主键值。这两者的差异直接决定了读写代价。走二级索引查一条记录需要先在二级索引的B树里找到主键然后拿着主键再到聚簇索引的B树里回表取出完整行。一次回表就是一次随机IO。你建了索引命中率很高但回表次数一多查询照样快不起来。所以性能突破的关键不一定在于“建了多少索引”而在于“能不能减少回表”。引申出来的一个实用原则是主键设计要考虑写入代价。如果你用一个超长的UUID作为主键二级索引里存的每个主键值都很长索引体积膨胀内存里能放下的索引页变少IO次数自然增加。而且UUID无序插入时B树频繁分裂写放大非常明显。现在的新项目我倾向于用自增主键或有序的雪花ID不是不能接受UUID而是要让索引这棵树的叶子节点尽量保持顺序写入。2.2 最左前缀的失效现场组合索引设计中的顺序陷阱组合索引的核心是最左前缀原则这个大家都听过但真正设计时很容易掉进顺序陷阱。最常见的错误有两种一是把区分度最高的列放前面二是只考虑查询条件不考虑排序和分组。先看第一种。WHERE a ? AND b ?如果a列的基数只有几十b列每种值对应几万行把a放前面就意味着索引第一层就过滤不掉多少数据优化器会认为这个索引没什么用。正确的做法通常是让过滤能力强的列靠左。但注意这不绝对如果条件里有范围查询比如a 1000 AND b ?那a放前面会让b的索引完全派不上用场因为范围右边的列无法用于定位。这种情况我会反过来把等值条件b放前面。第二种分布更隐蔽。一个报表查询条件只有WHERE create_time ?但ORDER BY user_id LIMIT 10如果你单独建(create_time)索引MySQL只能先查出满足时间条件的所有数据再做一次排序行数一多就是filesort。但如果把索引设计成(create_time, user_id)因为B树本身就是有序结构ORDER BY user_id在时间等值的情况下可以直接利用索引顺序省掉排序这对深分页场景收益极大。我见过太多人只盯着WHERE完全忘了ORDER BY结果SQL改了索引也没用。2.3 覆盖索引、索引下推与回表用一次查询说清三种优化手段用一个实际场景把这三个概念串起来。假设有一张订单表SQL是SELECT order_id, status, pay_time FROM order_record WHERE user_id 12345 AND status PAID;如果只有一个(user_id, status)的二级索引那么MySQL先通过索引找到匹配的主键再回表三次拿到order_id、status、pay_time。回表次数取决于匹配行数行数越多IO越多。改进方式有两种。第一种叫覆盖索引把索引扩展成(user_id, status, order_id, pay_time)。因为InnoDB的二级索引叶子节点包含了索引定义的所有列查询需要的字段都在索引里MySQL就不需要回表了直接扫索引就能返回结果。对于高频查询这种“用空间换一次随机IO”的代价非常划算。第二种叫索引下推Index Condition PushdownICP。如果只保留(user_id, status)索引但SQL里status本来就是索引的一部分MySQL在索引遍历过程中就会先过滤status而不是把每一条满足user_id的记录都回表后再过滤。5.6以后默认开启ICP很多人可能没注意过它的存在但它确实在默默减少回表次数。实操中我会用EXPLAIN的Extra列来确认看到Using index说明走了覆盖索引看到Using index condition说明走了ICP两者都是好事。如果看到Using where说明索引只负责定位了一部分剩下的过滤动作在做回表之后才发生这时候就要考虑能不能通过调整索引或改写SQL把过滤条件推给索引。2.4 索引冗余的代价写放大与维护成本如何量化索引不是越多越好这是我反复强调的一条。每一行数据插入的时候所有二级索引都要同步维护一份每次更新如果涉及索引列B树还要做节点分裂和结构调整。换句话说写操作的成本会随着索引数量成倍增长。很多时候你只看到了查询变快了没看到写入变慢了、锁等待变多了、binlog变大了。量化一个“冗余索引”其实很简单。假设已有索引(a, b, c)你再建一个(a, b)后者就是前者的最左前缀完全冗余。工具上可以用pt-duplicate-key-checker扫描但就算没有工具手动看一下SHOW INDEX FROM table也能分辨。我的原则是能复用的前缀绝不复建宁可把索引设计得宽一点也不要为单独查询堆一堆重复前缀的小索引。索引占用的不只是磁盘更是内存缓冲池的宝贵空间冗余索引会让整个实例的热点命中率下降最后所有查询一起变慢。3. 查询重写的实战套路把逻辑拆到优化器喜欢的形状3.1 隐式类型转换看似无害的条件如何干掉索引前面提到了一次隐式类型转换这里展开说说。MySQL的规则是如果一个字符串列和一个数字比较它会尝试把字符串转成数字。这个大坑的影响面非常广不仅仅是WHERE条件JOIN的关联条件也一样。我曾经优化过一个数仓抽数任务两个表关联字段一个VARCHAR一个BIGINT结果整个任务跑了40分钟改成统一类型后直接缩到4分钟。同类的问题还包括日期比较。比如你在一个DATETIME字段上做WHERE create_time 2024-01-01MySQL会把字符串隐式转成日期这个本身不是大问题。但如果你写的是WHERE DATE(create_time) 2024-01-01这就是在索引列上套了函数索引直接失效。正确写法是范围比较WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这个改写不需要函数参与索引能正常命中。类似的还有WHERE YEAR(create_time) 2024也应该改写成上述形式。总结起来就一句话索引列上尽量不要做任何运算不管是隐式的还是显式的都要把运算挪到参数一侧。3.2 OR/IN/EXISTS/JOIN的取舍逻辑与半连接改写很多人纠结OR和IN要不要改写EXISTS和JOIN哪个快。其实在MySQL 8.0之前IN和EXISTS的优化策略差异很大但8.0引入了半连接SEMI JOIN优化很多情况会自动转换。真正需要人工干预的是一些极端场景。先说OR。WHERE a 1 OR b 2在a、b各自有索引的情况下MySQL可能选择Index Merge也可能选择全表扫描。一个比较稳妥的做法是拆成UNION ALL让每一段SQL各自走索引再合并结果。如果两段结果集有交集再考虑用UNION去重。但这种改写的收益依赖数据分布如果每个条件都能过滤掉大部分数据收益明显如果条件本身就很宽泛改写也没意义。再说子查询和JOIN。我见过大量把JOIN改写成IN子查询的习惯试图“减少连接次数”。但在MySQL里JOIN和IN的优化路径并不一样。5.6之前相关子查询的执行方式是逐行驱动效率极差所以老一辈开发者总结出“能JOIN就JOIN别用IN”的经验。到了8.0子查询会被自动改造为半连接性能差距缩小但有些边缘场景还是会出幺蛾子。我的建议是先在执行计划里确认有没有出现materialize或者dependent这些关键词。dependent表示相关子查询往往是性能重灾区看到这个果断改写成临时表或JOIN。3.3 分页深翻页问题延迟关联与游标分页的取舍分页是每个业务系统都绕不开的点也是SQL优化里最容易被低估的一环。ORDER BY id LIMIT 10000, 20这种写法看着简单实际MySQL要先把前10020行取出来排序然后丢掉前10000行。数据量越大越往后翻这个“丢弃”的成本越高。等到OFFSET到了几十万查询时间飙到秒级完全不意外。一个有效的套路是延迟关联。所谓延迟关联就是先用覆盖索引快速定位到目标行的主键ID再通过ID去关联原表获取完整数据SELECT o.* FROM order_record o INNER JOIN ( SELECT id FROM order_record WHERE user_type 1 ORDER BY create_time DESC LIMIT 10000, 20 ) t ON o.id t.id;内层查询只扫二级索引不需要回表跳过的行数再多成本也远低于外层直接扫全表。这个改写我在多个项目里都用过深分页场景收益通常是数量级的。但如果产品本身是无限下拉流或者“上一页下一页”的模式更好的方案是游标分页。比如记录上一页最后一条数据的create_time和id下一页直接WHERE create_time ? OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC LIMIT 20。这样不管翻多深扫描范围始终只有20条。业务改造量稍大但性能上限高得多而且不会因为中间插了数据导致分页错乱。我的习惯是一次性列表用延迟关联滚动加载用游标分页。3.4 聚合与排序的优化空间临时文件和filesort的避让涉及GROUP BY、ORDER BY、DISTINCT的查询如果执行计划里的Extra列出现了Using temporary或Using filesort就要警惕了。这两个词都不一定致命但都意味着MySQL需要额外的内存或磁盘空间来完成计算。Using filesort不是真的用文件排序更准确的说法是“无法利用索引顺序完成排序需要额外的排序操作”。排序数据量小在内存里排数据量大会在tmpdir里建临时文件产生磁盘IO。优化方向很明确让排序字段参与索引而且排序顺序要和索引完全一致。比如索引是(a, b)你用ORDER BY a ASC, b ASC就能直接利用索引顺序如果你写ORDER BY b ASC, a ASC顺序不匹配优化器只能filesort。GROUP BY同理。GROUP BY a, b和索引(a, b)顺序一致时MySQL可以边扫边分组不需要临时表。另外很多人不知道MAX()、MIN()在索引前缀上可以直接取两端值根本不需要扫全表。比如SELECT MAX(id) FROM order_recordInnoDB直接读取索引最右侧叶子节点执行计划秒回。这种小细节在实际优化中经常能带来惊喜。4. 执行计划与性能验证用EXPLAIN之外的手段逼近真相4.1 EXPLAIN到底在看什么type、rows、Extra的读法执行计划是SQL优化的基础但很多人查了执行计划等于没查。EXPLAIN SELECT...的输出里我第一眼看type。从好到坏大致是system、const、eq_ref、ref、range、index、ALL。前面几种都算不错的访问方式到了index说明在扫全索引到了ALL就是全表扫描。一个合格的优化目标是把关键查询的type至少推到range以上。第二眼看rows这是优化器估算的需要扫描的行数。注意是估算值不是真实值。如果rows和实际数据量差距过大十有八九是统计信息过期了。这时候先ANALYZE TABLE再看计划通常比直接改SQL更有效。第三眼看Extra。这里的信息密度最高Using where表示在存储引擎层过滤完之后Server层又做了一次过滤Using index表示覆盖索引Using index condition表示ICPUsing temporary和Using filesort就是前面提到的问题信号Using join buffer则说明连接过程没有合适的索引需要用join buffer来存中间结果。这每一项都是下一步优化方向的直接线索。拿到执行计划先别急着改SQL把Extra里出现的每一个关键词查明白已经能解决一半问题。4.2 optimizer trace优化器为什么不听你的有一种让人血压飙升的情况你做了一个改写理论上规划得很好但执行计划还是老样子。这时候EXPLAIN看不出优化器的心理活动得请出隐藏工具optimizer trace。在MySQL中你可以针对当前会话开启优化器追踪SET optimizer_trace enabledon; SELECT * FROM order_record WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10; SELECT * FROM information_schema.OPTIMIZER_TRACE\G输出里会详细记录优化器每一步的思考过程包括它考虑了哪些索引、为什么淘汰了某个索引、最终选择了什么执行方案。我在一次真实调优中发现优化器拒绝某个索引的原因竟然是回表成本估算高于全表扫描而这个估算偏差来自主键长度过大。看到这份trace我立刻把主键从UUID改成了自增IDSQL一行没动查询时间从2秒降到了80毫秒。没有trace的话我可能还在纠结索引结构怎么改。optimizer trace的缺点是信息量大一张几千行的JSON不是人读的。实际会搜关键词considered_access_paths看索引候选cause看放弃原因rows_estimation看行数估算。这几个节点基本能覆盖大部分定位需求。生产环境不建议长期开启trace但针对一条慢SQL开一个会话得到结果后立刻关掉是完全没有问题的。4.3 线上压测与火焰图从单条SQL到系统吞吐的放大镜单条SQL优化完成不代表系统就快了。数据库瓶颈往往不在某一条SQL上而在多条SQL叠加出来的锁竞争、IO排队和网络延迟上。所以我把优化流程的最后一环放在压测和监控上。工具上sysbench和jmeter都能做压测但更贴近业务的做法是直接抓生产环境或预发环境的流量回放观察优化前后的吞吐和延迟分位数变化。如果怀疑SQL已经引发了系统层面的CPU或IO问题我会看火焰图。生成火焰图的方式很多perf record配合FlameGraph脚本是常见一套。但注意数据库实例的火焰图分析要比应用复杂因为MySQL内部线程多且状态机复杂我不会一上来就分析函数级细节而是先看整体CPU使用率分布在哪个子系统是排序、是临时表创建、还是行锁等待。如果火焰图里大面积出现与排序相关的函数那就说明还有大批量filesort在作祟需要回到SQL和索引层面再排查一轮。这里有一个我自己常用的经验不要只看95分位延迟要看99分位和最大延迟。慢SQL对系统的影响往往是“尾部延迟”被拖垮平均延迟看起来还行但每隔一段时间就卡一下这种间歇性抖动最容易被人忽视。通过监控工具把这些尾部请求一一揪出来结合慢查询日志时间线通常能精准定位是哪条SQL在特定数据分布下变成了定时炸弹。5. 一次近千万级订单查询的完整优化复盘5.1 业务场景与原始SQL问题出现在哪里模拟项目X是一个订单管理系统订单表order_record数据量在850万左右。业务上有一个核心查询用户在后台按商户、时间范围和状态筛选订单前端要做列表分页。原始SQL大致长这样SELECT * FROM order_record WHERE merchant_id 10086 AND status IN (PAID, REFUNDING) AND create_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200, 20;生产环境已经有一个索引idx_merchant_status_time(merchant_id, status, create_time)但查询时间平均在4.5秒高峰期接近8秒后端接口超时频繁。一开始团队的反应是“索引都有了啊为什么还慢”于是先把SQL拿到测试库一顿EXPLAIN。执行计划显示type为rangerows估算接近30万Extra里有Using filesort。三个信息一组合问题就清楚了虽然索引用上了但status条件是IN优化器只能利用索引到merchant_id这一层然后对status做范围扫描扫描范围覆盖了三个状态里所有符合条件的记录总量非常大。并且因为create_time也在索引里理论上排序可以直接利用但IN导致索引连续扫描的顺序没办法严格等于create_time顺序于是走了filesort。5.2 从索引调整到查询改写每一步改动与收益对照第一版改动把现有索引调整为idx_merchant_time(merchant_id, create_time)。为什么去掉status因为status的IN条件本身就破坏了最左前缀的等值定位能力把它放在中间既浪费空间又让排序优化无从谈起。去掉之后索引的等值前缀只有merchant_idcreate_time作为范围条件继续参与定位同时ORDER BY create_time DESC可以完全利用索引顺序Using filesort消失。这一版上线后查询时间降到约1.2秒。第二版改动改造分页。上面只解决了排序问题但LIMIT 200, 20还在MySQL仍然要扫前220条之前的记录。改成延迟关联写法SELECT o.* FROM order_record o INNER JOIN ( SELECT id FROM order_record WHERE merchant_id 10086 AND create_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200, 20 ) t ON o.id t.id;内层查询只需要扫描二级索引每行只取id和索引列不需要回表再配合idx_merchant_time的有序性排序成本大幅降低。这一版上线后查询时间降到了180毫秒左右收益接近数量级。第三版微调处理业务侧的筛选变化。后台还存在“只看退款中”的筛选场景这种场景下status其实是单一等值条件。我在模块化SQL时单独为这类场景保留了一个(merchant_id, status, create_time)索引。听起来有点打脸但这是合理的冗余两种查询模式分别使用不同的索引各取所需代价只是额外一份索引的写入成本。换来的收益是每种模式都能走最精准的执行路径。5.3 常见的优化误区与我的取舍经验复盘这次优化有几个误区值得单独拎出来说。第一个误区是“索引覆盖的字段越多越好”。我把SELECT *改成延迟关联后没有顺手再把查询列塞进索引。因为你一旦把所有需要的字段都塞进二级索引索引体积膨胀缓冲池命中率下降整体收益反而可能是负的。覆盖索引适合高频、固定、小字段集的查询不适合SELECT *这种宽查询。第二个误区是“执行计划变好就代表性能变好”。第一版改动后执行计划确实漂亮了但线上实测仍然有1秒左右的延迟因为大分页问题还在。SQL优化必须以线上响应时间为准不能只看EXPLAIN的输出。每次改完我都会在测试环境用相同的数据分布跑一遍真实查询记录耗时再在预发环境验证一轮最后才动生产。第三个误区是“不敢动索引怕影响其他查询”。这种谨慎是对的但不能因噎废食。正确做法是通过information_schema查询索引使用情况看看哪些索引的index_name在慢查询日志里根本没出现过或者从performance_schema的表table_io_waits_summary_by_index_usage里看每个索引的统计。数据会告诉你哪个索引该留哪个索引纯属心理安慰。从索引策略到查询性能这条链路里真正值钱的不是某一招而是一套稳定的排查方法先理解数据分布再读懂执行计划最后用实验验证每一步收益。我自己踩过不少坑最深的一个体会是索引是手段性能是结果但中间隔着一个叫作“理解你正在查询的数据”的鸿沟。跨越这个鸿沟你才能从“加索引碰运气”进化到“每一次改动都有明确预期”。如果你最近也被一条慢SQL折磨得焦头烂额不妨先把EXPLAIN结果贴出来对照着rows和Extra逐项分析大概率能找到比加索引更快的解法。等到你开始习惯用optimizer trace和压测数据来验证每一次判断时你就会发现SQL优化不是玄学而是一道可以稳定复现的数学题。