大家好我是数据库小学妹 上个月我干过一件蠢事。生产环境一张六百万行的订单表测试时我在status字段加了索引上线前跑测试查询从8秒降到0.03秒。我信心满满提交了变更第二天慢查询日志里这条SQL赫然在列执行时间7.2秒。EXPLAIN一看type是ALL全表扫描。索引就在那儿优化器看都不看一眼。我当时第一反应不可能吧后来我又翻出另一个案例。一张八百万行的order_item表我同样在status上加了索引结果查询从0.3秒变成1.2秒慢了整整3倍。两次都是加索引一次不走索引一次走了反而更慢。问题到底出在哪我花了几天时间研究优化器的代价计算模型才算搞明白SQL怎么执行不是我们说了算是优化器说了算而它的判断基于自己的一套世界观。今天把笔记整理出来。搞懂它你才知道索引什么时候有用、什么时候帮倒忙。优化器到底在干什么每次你发一条SELECTMySQL不会直接执行而是先由查询优化器决定怎么执行最好。MySQL 用的是基于成本的优化器CBO它会把所有可能的执行方案列出来逐一计算代价选最便宜的那个。就拿这条 SQL 来说SELECT*FROMordersWHEREstatuspaidANDcreate_time2026-07-01ORDERBYcreate_timeDESCLIMIT20;假设表上有三个索引主键PRIMARY、status的普通索引idx_status、create_time的普通索引idx_create_time。优化器要比较的方案大致有这些用idx_status过滤再回表排序用idx_create_time范围扫描再回表过滤status两个索引做 Index Merge合并直接全表扫描在内存中过滤和排序每个方案优化器都会算一个代价。选代价最低的执行。代价不是执行时间而是一个抽象数值代表优化器认为这个方案要花多少力气。关键就在于优化器对力气的理解可能和实际硬件对不上。这就是所有问题的根源。代价模型的底层公式要搞清楚优化器为什么选错先要知道它怎么算代价。MySQL的查询代价由I/O代价和CPU代价两部分组成。I/O 代价指从磁盘或缓冲池读取数据页的成本MySQL用innodb_page_size默认 16KB来衡量页大小并估算需要读多少页。CPU 代价指在内存中评估 WHERE 条件、排序、关联等操作的计算量。我用具体数字算一遍。假设orders表有600万行每行平均200字节。全表扫描的 I/O 代价总数据量6,000,000 × 200 字节 ≈ 1,144 MB 总页数 1,144 MB / 16 KB ≈ 73,216 页 I/O 代价 73,216 × 1.0 73,216CPU代价评估六百万行的 WHERE 条件CPU代价 6,000,000 × 0.2 1,200,000全表扫描总代价大约127万。那走索引的代价呢假设idx_status过滤后只剩8000行。B树高度三层遍历索引页代价很小关键在回表索引扫描 I/O 代价 8,000 × 1.0 8,000 回表 I/O 代价 8,000 × 4.0 32,000 CPU 代价 8,000 × 0.2 1,600 索引方案总代价 41,60041600远小于127万。理论上优化器应该选索引。但我的情况是优化器估算 idx_status要扫120万行不是8000行。用120万代入回表 I/O 代价 1,200,000 × 4.0 4,800,000 总代价 5,000,000500万大于127万优化器当然选了全表扫描。统计信息过期行数估算错了代价计算跟着错。这就是我遇到的情况。optimizer trace看优化器怎么算的光看EXPLAIN只能看到结果看不到过程。想看优化器到底怎么算的得用optimizer trace。用法很简单SEToptimizer_traceenabledon;SEToptimizer_trace_max_mem_size1000000;-- 执行你的查询SELECT*FROMordersWHEREstatuspaidANDcreate_time2026-07-01ORDERBYcreate_timeDESCLIMIT20;-- 查看 trace 结果SELECT*FROMinformation_schema.OPTIMIZER_TRACE\Gtrace输出的JSON很长。重点看considered_execution_plans这一段每个方案的代价对比都在这{table:orders,range_analysis:{rows_estimation:[{table:orders,range:status paid,rows:1200000,cost:241060}],analyzing_range_alternatives:{range_scan_alternatives:[{index:idx_status,rows:1200000,cost:1440001,chosen:false,cause:cost}]}}}优化器估算idx_status扫120万行代价144万。全表扫描代价24万。一比六的差距索引直接被排除。我第一次看trace的时候发现优化器估算只要扫2000行实际扫了40万行差距200倍。那一刻我才真正理解统计信息有多重要。统计信息优化器做判断全靠统计信息统计信息不准选出来的执行计划自然有问题。MySQL InnoDB的统计信息有几个关键点基数决定索引选择性基数是索引列有多少个不同值越高越好InnoDB通过随机采样数据页来估算基数本身就不精确。数据频繁更新时更麻烦插入、删除、更新会改变数据分布但统计信息不会实时更新它只在ANALYZE TABLE或者数据变化达到阈值时才更新。我回去验证了一下那个加索引变慢的案例ANALYZETABLEorder_item;EXPLAINSELECT*FROMorder_itemWHEREorder_noORD202601150001ANDstatus1;跑完ANALYZE TABLE再看执行计划优化器切回了order_no索引查询恢复正常。统计信息过期是我遇到最多的优化器选错计划的原因。但统计信息修好之后我本以为没问题了。深入研究才发现代价模型还有三个盲区即便统计信息完全准确它照样可能选错。盲区一不区分顺序读和随机读的真实差距代价模型里顺序读一页的代价系数是1.0随机读一页是4.0。看起来区分了但差距只有四倍。实际硬件上SSD的顺序读吞吐量是随机读的十倍到五十倍机械硬盘更夸张差距在一百倍以上。这意味着优化器比较全表顺序扫和索引随机回表时严重低估了顺序读的优势。如果索引回表的行数超过某个阈值大约是总行数的百分之五到二十优化器会倾向于全表扫描。哪怕数据全在buffer pool里走索引实际上更快。我搞错过一次。一张一百万行的用户表有个状态字段选择性很低但查询过滤时偏偏条件就落在这个字段上。我以为是索引没生效后来才知道优化器根据代价模型判断走全表扫更划算——因为它算出来回表的代价比顺序扫一遍还高虽然实际上buffer pool已经缓存了大部分数据。盲区二不考虑 buffer pool 命中率代价模型假设所有I/O都要从磁盘读但hot data几乎全在buffer pool里读取代价接近于零。一个极端例子一千万行的表全在buffer pool里全表扫描只需在内存中顺序遍历实际耗时可能不到一秒。但优化器按磁盘I/O算出来的代价会非常高可能选一个代价更低但实际上慢得多的方案。代价模型按磁盘I/O算代价但数据可能在内存里。这就是为什么优化器的估算和实际执行时间经常对不上。盲区三列间相关性完全忽略假设表里有province和city两个字段。province有三十个不同值city有五百个优化器认为它们是独立的。但实际数据中province北京’的情况下city大概率是北京的某个区两列高度相关。当查询条件是WHERE province北京 AND city朝阳区时优化器按独立概率计算估算行数 总行数 × (1/30) × (1/500)实际行数远大于这个值因为北京的数据集中在少数几个city值上。优化器低估了行数选了索引方案结果回表代价远超预期。MySQL 8.0引入了直方图来改善单列数据分布的估算但列间相关性目前还是没有好的解决方案。索引条件下推ICPICP从MySQL 5.6开始支持。正常情况下联合索引只能用到最左前缀匹配到范围条件后索引就停了剩下的条件要到Server层去过滤。ICP让存储引擎自己在索引层做过滤不用把数据推到Server层再筛减少了回表和层间数据传输。-- 联合索引 idx_user_status(user_id, name, status)SELECT*FROMusersWHEREuser_id100ANDnameLIKE张%ANDstatus1;name是范围匹配索引在name这里停了。没有ICP的话所有name LIKE’张%’ 的数据都要回表再去Server层过滤status。开了ICP之后存储引擎在索引层就能把status 不等于1的过滤掉只把status1的回表。用EXPLAIN看Extra列出现Using index condition说明ICP生效了。不过ICP不是万能药。如果索引层过滤掉的行很少反而增加计算开销。看到Using index condition但查询没变快的情况多半是这个原因。Index Merge索引合并的陷阱代价模型不仅影响索引选择还影响Index Merge的决策。Index Merge是优化器把多个单列索引的结果合并起来用常见的有交集和并集。听起来挺好实际这是个坑高发区。EXPLAINSELECT*FROMordersWHEREcustomer_id500ANDorder_type3;-- Extra: Using intersect(idx_customer_id, idx_order_type)EXPLAIN显示它用了两个索引的交集。看着挺聪明但实际执行起来两个索引各扫出大量数据再做交集运算比一个合适的联合索引慢了好几倍。我当时不确定是不是Index Merge的问题关掉它试试看SEToptimizer_switchindex_mergeoff;-- 再跑一次查询对比执行时间关掉之后优化器被迫选了一个单索引查询反而快了。两个单列索引选择性都不高的时候Index Merge最容易出问题。优化器以为交集能大幅缩小结果集但实际两个索引扫出来的数据有大量重叠交集运算本身的开销抵消了缩小结果集的好处。解决办法是建合适的联合索引给优化器一个明确的好选择。怎么判断优化器选错了大家最关心的问题我怎么知道优化器选对了还是选错了我一般分三步。第一步看 EXPLAIN 的 type 和 rows。type是ALL且rows接近表总行数说明优化器选了全表扫。如果表上有合适的索引这就是可疑信号。type是ref或range但rows仍然很大超过总行数百分之十也值得警惕。第二步用 FORCE INDEX 对比实际执行时间。-- 原始 SQL让优化器自选SELECT*FROMordersWHEREstatuspaidANDcreate_time2026-07-01;-- 强制走索引SELECT*FROMordersFORCEINDEX(idx_status)WHEREstatuspaidANDcreate_time2026-07-01;对比两者的实际执行时间。如果FORCE INDEX明显更快并且你确认统计信息是最新的那优化器大概率选错了。第三步开 optimizer_trace 看代价明细。在analyzing_range_alternatives里找到每个索引方案的代价和table_scan的代价对比。如果索引方案代价更低但优化器还是选了table_scan说明有其他因素干扰。怎么纠正优化器的选择遇到优化器选错有几个处理思路。先跑一遍ANALYZE TABLE 表名大多数情况就够了。数据大量变更后养成习惯跑一次。MySQL默认会自动更新统计信息但触发阈值是表数据的10%变化。1000万行的表要变100万行才会触发。中间这段空窗期优化器一直在用错误信息做决策。如果统计信息准了还是选错可以考虑直方图。MySQL 8.0支持对数据分布不均匀但又不适合建索引的列比较有用ANALYZETABLEordersUPDATEHISTOGRAMONstatus;我之前给一个倾斜的status字段建了直方图后优化器对涉及该字段的查询计划全部修正了。optimizer hint 也可以考虑。MySQL 8.0 支持SELECT/* INDEX(orders idx_status) */*FROMordersWHEREstatuspaidANDcreate_time2026-07-01;hint写死在SQL里数据分布变了以后可能反而不合适适合临时救急不适合长期依赖。还有一招是检查索引设计。优化器选错很多时候是索引本身的问题单列索引太多、联合索引缺失、重复索引干扰判断。把多余的单列索引清理掉给优化器一个明确的好选择它反而更容易选对。避坑清单大批量变更后必须先跑ANALYZE TABLE。这是优化器选错方案最常见的原因。我见过太多人加了索引但不更新统计信息然后抱怨索引没用。养成习惯别等慢查询日志报警了才想起来。Index Merge看着聪明实际可能是性能杀手。看到EXPLAIN的 Extra里有intersect或union别高兴太早关掉它对比一下执行时间。两个条件字段各建一个单列索引优化器很容易走 Index Merge不如直接建联合索引给优化器一个明确的好选择。别盲目相信EXPLAIN的rows。rows是估算值不是实际扫描行数。对于数据倾斜严重的列比如状态字段百分之九十的行是同一个值rows估算可能偏差几个数量级。养成用COUNT(*) 验证实际行数的习惯。小表全表扫是正常的别强迫症。几百行的表全表扫比走索引快得多。优化器对小表选全表扫是正确行为不用干预。最后回到开头那个问题。加了索引为什么查询反而更慢优化器看到了新索引算了一下觉得这条路更便宜但它的估算基于过时的统计信息实际走起来才发现是条堵路。我以前以为优化器很聪明。学完之后发现它更像拿着旧地图的向导——地图准的时候带路很准地图旧了就带错路。理解代价模型的盲区之后你就知道什么时候该相信它什么时候该手动干预。你在实际工作中遇到过优化器自作聪明选错计划的情况吗后来怎么解决的来评论区聊聊。我是数据库小学妹咱们下篇见