MySQL 执行计划深度解析：从 Optimizer Trace 到索引选择逆转

MySQL 执行计划深度解析从 Optimizer Trace 到索引选择逆转一、优化器为什么故意不走索引线上一条查询WHERE create_time BETWEEN ... AND status 1 ORDER BY id LIMIT 100联合索引idx_create_time_status完美匹配但优化器偏偏选了全表扫描。DBA 手动FORCE INDEX后查询从 12 秒降到 50ms但三天后优化器又叛变了。这不是优化器的 Bug而是代价估算的理性决策——只是基于了错误的统计信息。理解优化器的决策逻辑必须深入optimizer_trace逐层拆解代价计算过程。本文从EXPLAIN到optimizer_trace再到索引选择逆转的实战建立一套系统化的执行计划分析方法论。二、优化器代价计算的底层机制2.1 代价模型的三个层次MySQL 优化器的代价计算分为三层表级代价扫描全表 vs 索引扫描的 IO 代价范围代价索引范围扫描的行数估算排序代价是否需要 filesort排序缓冲区是否够用核心公式简化版全表扫描代价 数据页数 × io_block_read_cost 行数 × row_evaluate_cost 索引扫描代价 索引B树层级 × io_block_read_cost 估算行数 × row_evaluate_cost 回表代价io_block_read_cost默认 1.0row_evaluate_cost默认 0.1。优化器选择代价最小的方案。2.2 optimizer_trace透视优化器决策全过程-- 开启 optimizer_trace SET SESSION optimizer_trace enabledon; SET SESSION optimizer_trace_max_mem_size 1048576; -- 执行目标查询 SELECT order_id, amount, status FROM orders WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31 AND status 1 ORDER BY id LIMIT 100; -- 查看 trace 结果 SELECT trace FROM information_schema.OPTIMIZER_TRACE\Gtrace 输出的关键节点flowchart TB A[SQL 解析] -- B[逻辑变换] B -- C[条件提取与下推] C -- D[索引候选评估] D -- E[计算各索引代价] E -- F{比较代价} F --|全表代价更低| G[选择全表扫描] F --|索引代价更低| H[选择索引扫描] G -- I[评估是否需要 filesort] H -- I I -- J[生成最终执行计划]2.3 trace 输出的关键字段解读{ steps: [ { join_optimization: { table: orders, range_analysis: { table_scan: { rows: 5000000, cost: 525000 }, potential_range_indices: [ { index: idx_create_time_status, ranges: [create_time 2025-01-01 AND create_time 2025-01-31], index_dives_for_eq_ranges: true, rows: 800000, cost: 243000 } ], chosen_range_access: { index: idx_create_time_status, cost: 243000 } }, best_access_path: { chosen_access_method: ref, rows: 800000 } } } ] }关键信息rows是优化器估算的扫描行数cost是计算出的代价。如果rows与实际差距过大说明统计信息失真。三、执行计划逆转与统计信息校准实践3.1 统计信息失真的诊断流程import pymysql from dataclasses import dataclass from typing import List, Optional, Tuple import logging logger logging.getLogger(__name__) dataclass class IndexStats: 索引统计信息 table_name: str index_name: str cardinality: int # 索引唯一值数(ndv) rows_examined: int # 优化器估算扫描行数 actual_rows: int # 实际扫描行数 estimation_ratio: float # 估算/实际 比率 property def is_skewed(self) - bool: 估算偏差超过 3 倍视为失真 return self.estimation_ratio 3.0 or self.estimation_ratio 0.33 class ExecutionPlanAnalyzer: 执行计划分析器, 对比优化器估算与实际执行差异 def __init__(self, mysql_config: dict): self.mysql_config mysql_config def _get_connection(self): return pymysql.connect(**self.mysql_config) def get_index_stats(self, table_name: str) - List[IndexStats]: 获取表的所有索引统计信息 sql SELECT INDEX_NAME, CARDINALITY, SEQ_IN_INDEX FROM information_schema.STATISTICS WHERE TABLE_SCHEMA %s AND TABLE_NAME %s ORDER BY INDEX_NAME, SEQ_IN_INDEX stats [] try: with self._get_connection() as conn: with conn.cursor() as cur: cur.execute(sql, (self.mysql_config[database], table_name)) # 取每个索引的第一列 cardinality seen_indexes set() for row in cur.fetchall(): idx_name row[0] if idx_name in seen_indexes: continue seen_indexes.add(idx_name) stats.append(IndexStats( table_nametable_name, index_nameidx_name, cardinalityrow[1] or 0, rows_examined0, actual_rows0, estimation_ratio0.0, )) except pymysql.err.OperationalError as e: logger.error(f获取索引统计失败: {e}) return stats def compare_explain_vs_actual(self, sql: str) - Optional[dict]: 对比 EXPLAIN 估算行数与 Handler 读取行数 result { sql: sql, explain_rows: 0, handler_read_next: 0, estimation_ratio: 0.0, indexes_used: [], } try: with self._get_connection() as conn: # 1. EXPLAIN 获取估算行数 with conn.cursor() as cur: cur.execute(fEXPLAIN {sql}) explain_rows cur.fetchall() if explain_rows: result[explain_rows] explain_rows[0][9] or 0 # rows 列 result[indexes_used] [row[5] for row in explain_rows if row[5]] # 2. 执行前记录 Handler 状态 with conn.cursor() as cur: cur.execute(SHOW STATUS LIKE Handler_read_%) before {row[0]: int(row[1]) for row in cur.fetchall()} # 3. 执行查询 with conn.cursor() as cur: cur.execute(sql) cur.fetchall() # 4. 执行后记录 Handler 状态 with conn.cursor() as cur: cur.execute(SHOW STATUS LIKE Handler_read_%) after {row[0]: int(row[1]) for row in cur.fetchall()} # 计算实际读取行数 handler_read_next after.get(Handler_read_next, 0) - before.get(Handler_read_next, 0) handler_read_rnd_next after.get(Handler_read_rnd_next, 0) - before.get(Handler_read_rnd_next, 0) result[handler_read_next] handler_read_next handler_read_rnd_next if result[explain_rows] 0: result[estimation_ratio] result[handler_read_next] / result[explain_rows] except Exception as e: logger.error(f执行计划对比失败: {e}) return None return result def diagnose_skewed_indexes(self, table_name: str, threshold: float 3.0) - List[dict]: 诊断统计信息失真的索引 skewed [] stats self.get_index_stats(table_name) for stat in stats: # 获取实际的 ndv try: with self._get_connection() as conn: with conn.cursor() as cur: # 对索引列做 COUNT(DISTINCT) 获取真实 ndv cur.execute( fSELECT COUNT(DISTINCT {stat.index_name}) FROM {table_name} ) actual_ndv cur.fetchone()[0] or 1 if stat.cardinality 0: ratio actual_ndv / stat.cardinality if ratio threshold or ratio 1.0 / threshold: skewed.append({ index: stat.index_name, stats_ndv: stat.cardinality, actual_ndv: actual_ndv, ratio: round(ratio, 2), recommendation: ANALYZE TABLE if ratio threshold else 检查采样率, }) except Exception as e: logger.warning(f诊断索引 {stat.index_name} 失败: {e}) return skewed if __name__ __main__: analyzer ExecutionPlanAnalyzer({ host: 127.0.0.1, port: 3306, user: root, password: your_password, database: trade_core, }) # 诊断统计信息失真 skewed analyzer.diagnose_skewed_indexes(orders) for s in skewed: logger.warning(f索引 {s[index]} 统计失真: stats_ndv{s[stats_ndv]}, factual_ndv{s[actual_ndv]}, ratio{s[ratio]})3.2 强制索引与 Hint 的正确用法-- 方式1: FORCE INDEX 强制使用指定索引 SELECT order_id, amount FROM orders FORCE INDEX (idx_create_time_status) WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31 AND status 1 LIMIT 100; -- 方式2: SET optimizer_switch 禁用全表扫描优化 SET SESSION optimizer_switch index_mergeoff; -- 方式3: 调整代价权重使索引更便宜 SET SESSION optimizer_switch engine_condition_pushdownon;3.3 统计信息自动维护方案-- 对高写入表, 设置自动 ANALYZE 的行变更阈值 -- MySQL 8.0: 修改 innodb_stats_auto_recalc ALTER TABLE orders STATS_AUTO_RECALC 1; -- 手动触发, 使用更精确的采样页数 SET SESSION innodb_stats_persistent_sample_pages 128; ANALYZE TABLE orders; -- 查看当前统计信息 SHOW INDEX FROM orders;四、执行计划调优的边界与妥协4.1 FORCE INDEX 的维护债务FORCE INDEX是双刃剑。数据分布变化后强制索引可能从最优变为最差。生产环境应优先修复统计信息FORCE INDEX只作为临时止血手段并设置 7 天内修复统计信息的 TODO。4.2 optimizer_trace 的性能开销开启optimizer_trace会增加 5%-15% 的查询解析开销。生产环境不应全局开启只在诊断特定查询时 session 级别开启。optimizer_trace_max_mem_size默认 16KB复杂查询的 trace 可能超限需调大到 1MB。4.3 统计信息采样率的权衡innodb_stats_persistent_sample_pages默认 20 页。增大采样页数提高统计精度但 ANALYZE 耗时增加。对于 1 亿行的大表采样 128 页的 ANALYZE 可能需要 30 秒期间持有 MDL 读锁可能阻塞 DDL。建议在低峰期执行或使用innodb_stats_auto_recalc异步更新。4.4 禁用场景查询本身已使用最优索引无需分析数据量小于 10 万行的小表全表扫描可能比索引扫描更快频繁 DDL 的表统计信息随时失效分析无意义五、总结执行计划分析的核心不是看EXPLAIN输出的表面信息而是通过optimizer_trace逐层拆解代价计算过程定位统计信息失真的具体环节。优化器选错索引的本质是代价估算基于了不准确的统计信息解决方案的优先级应为修复统计信息 调整代价权重 FORCE INDEX 临时止血。生产环境应建立统计信息监控体系对估算偏差超过 3 倍的索引自动触发 ANALYZE而非依赖 DBA 事后发现。执行计划优化是数据驱动的工程问题不是经验驱动的玄学。