执行计划一夜之间变了？别查代码了，是统计信息在“说谎“

大家好我是小耶写功课只是为了我踩过的坑你们别再踩了有个经典的凌晨惊魂场景某条核心SQL跑了半年都没问题每天几十万次执行响应时间稳定在5毫秒以内。某天凌晨三点监控告警疯狂弹窗——这条SQL突然飙到5秒CPU打满整个系统雪崩。DBA赶到现场第一反应是看代码有没有人改。没有。看索引有没有人删。没有。看数据量有没有暴涨。也没有。最后查下来原因让人无语统计信息过期了。数据库优化器拿着一份过时的情报给这条SQL选了一条错误的执行计划。今天把执行计划突变的底层原理、排查手段和预防机制一次讲清楚。一、先搞懂几个概念执行计划Execution Plan数据库执行一条SQL的具体步骤。先走哪个索引、先JOIN哪张表、用什么JOIN方式Nested Loop、Hash Join、Merge Join这些决策组合起来就是执行计划。优化器Optimizer数据库里的决策引擎负责为每条SQL选择最优的执行计划。它不跑SQL只猜哪种执行方式最快。统计信息Statistics优化器做决策的依据。包括表的总行数、每列的数据分布最大值、最小值、NULL占比、直方图、索引的选择性不同值的数量等。本质上就是优化器眼中的数据库快照。基数估计Cardinality Estimation优化器预估每一步会返回多少行数据。预估准了执行计划就优预估偏了就可能选错索引、选错JOIN顺序。CBOCost-Based Optimizer基于成本的优化器。优化器根据统计信息计算每种执行计划的成本CPU消耗、IO次数、内存占用选成本最低的那个。理解了这些概念就能回答一个核心问题为什么执行计划会突然变二、执行计划为什么会背叛你统计信息过期优化器拿到的是过期情报这是最常见的原因。统计信息不是实时更新的大多数数据库是定期收集或手动触发。假设你有一张订单表平时100万行统计信息也是按这个量级收集的。某天大促数据量涨到500万但统计信息还没更新。优化器依然认为表里只有100万行——于是选择了全表扫描因为它觉得100万行全表扫比走索引快。实际上500万行全表扫描直接卡死。统计信息 ≠ 实时数据它是一份延迟的快照。数据倾斜平均值骗了优化器即使统计信息是新的也可能因为数据分布不均匀而误导优化器。比如一个订单表的status列99%的数据是COMPLETED1%是PENDING。如果统计信息只记录了平均分布没有收集直方图优化器会认为每个状态的占比差不多。当你查询status COMPLETED时优化器预估返回1000行总行数10万的1/100实际返回99000行——走索引反而比全表扫慢几十倍。索引变化新增索引不一定是好事开发同学看到慢SQL第一反应是加索引。加完索引后统计信息更新优化器重新评估所有可用索引可能选出一个更差的执行计划。加索引 ≠ SQL变快它只是给优化器多了一个选择而这个选择可能是错的。参数变更看似无关的配置调整optimizer_mode从ALL_ROWS改为FIRST_ROWSoptimizer_features_enable版本升级后行为变化statistics_level从TYPICAL改为BASIC停止收集部分统计信息这些参数调整不会立刻生效但下一次硬解析时优化器的决策逻辑可能完全改变。三、执行计划突变的排查步骤第一步确认是不是执行计划变了-- Oracle SELECT * FROM v$sql_plan WHERE sql_id your_sql_id; -- MySQL (8.0) EXPLAIN FORMATTREE SELECT ...; -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- 对比历史执行计划 -- Oracle: DBMS_XPLAN.DISPLAY_AWR(your_sql_id)重点对比访问路径全表扫 vs 索引扫描、JOIN顺序、JOIN方式、预估行数 vs 实际行数。第二步检查统计信息是否过期-- Oracle查看表的统计信息收集时间 SELECT table_name, last_analyzed, num_rows, blocks FROM user_tables WHERE table_name YOUR_TABLE; -- MySQL查看InnoDB表统计信息 SHOW TABLE STATUS LIKE your_table; -- PostgreSQL查看统计信息 SELECT last_analyze, last_autoanalyze, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname your_table;如果last_analyzed是几天甚至几周前而这段时间数据变化超过10%基本可以判定统计信息过期。第三步对比预估行数和实际行数这是判断优化器是否误判的关键指标。-- 执行SQL时开启实际执行统计 -- Oracle: EXPLAIN PLAN FOR ... 然后 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- MySQL: EXPLAIN ANALYZE SELECT ...; -- PostgreSQL: EXPLAIN ANALYZE SELECT ...;如果某一步的预估行数estimated rows和实际行数actual rows相差10倍以上说明基数估计严重失真执行计划很可能选错了。第四步检查是否有绑定变量窥探问题绑定变量第一次执行时优化器会窥探变量值来生成执行计划。后续执行直接复用这个计划即使变量值的数据分布差异很大。比如第一次传的是status PENDING只有100行优化器选了索引扫描。后面传的是status COMPLETED99000行还是走索引——但全表扫描反而更快。四、预防执行计划突变的4种手段手段一合理设置统计信息收集策略不要完全依赖自动收集根据业务特点定制策略适用场景收集频率自动收集 默认阈值数据变化平稳的普通表系统自动触发手动定时收集数据批量导入/删除的表每天凌晨或批量操作后锁定统计信息历史归档表数据不变化收集一次后锁定收集直方图数据分布严重倾斜的列按需收集-- Oracle: 手动收集统计信息含直方图 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA, TABLE_NAME, method_opt FOR COLUMNS SIZE AUTO skewed_column); -- MySQL: 手动分析表 ANALYZE TABLE your_table; -- PostgreSQL: 手动分析 ANALYZE your_table;手段二使用执行计划基线Plan BaselineOracle提供了SQL Plan ManagementSPM可以把好的执行计划锁定下来即使统计信息变化也不让优化器切换到更差的计划。-- Oracle: 创建执行计划基线 DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id your_sql_id); END;金仓数据库也提供了类似的执行计划管理能力。通过DBMS_SPM兼容包可以将经过验证的优秀执行计划固定下来避免因统计信息变化导致的性能波动。同时支持执行计划演化evolve在确认新计划更优后才自动切换。手段三SQL Profile / OutlineSQL Profile是优化器的纠正器。当发现某条SQL的执行计划不理想时可以创建一个SQL Profile告诉优化器这条SQL按这个方式执行。-- Oracle: 使用SQL TUNING ADVISOR DECLARE l_tuning_task VARCHAR2(30); BEGIN l_tuning_task : DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id your_sql_id); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_tuning_task); DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name l_tuning_task); END;手段四监控统计信息变化建立监控机制在统计信息过期前主动预警-- 找出统计信息超过7天未更新的表 SELECT table_name, last_analyzed, ROUND((SYSDATE - last_analyzed), 1) as days_since_analyze FROM user_tables WHERE last_analyzed SYSDATE - 7 ORDER BY last_analyzed;建议在监控系统中加入以下告警项核心表统计信息超过X天未更新单表数据变化量超过上次统计的10%执行计划发生变更对比AWR报告五、总结执行计划突变的本质是优化器拿着一份过时的地图给你指了一条错误的路。代码没改、索引没动SQL突然变慢——不要急着翻代码先查统计信息。排查执行计划问题按这个顺序来对比执行计划确认是不是执行计划变了不是SQL本身的问题检查统计信息last_analyzed多久了数据变化量超过10%了吗预估 vs 实际基数估计偏差超过10倍优化器就失明了绑定变量第一次执行的变量值可能不适合后续的变量值预防胜于治疗统计信息收集策略 执行计划基线 变化监控三管齐下让慢SQL扼杀在摇篮里。小耶在手SQL不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~