当前位置：首页 > news >正文

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

news 2026/6/26 1:58:37

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.0，row_evaluate_cost默认 0.1。优化器选择代价最小的方案。

2.2 optimizer_trace：透视优化器决策全过程

-- 开启 optimizer_trace SET SESSION optimizer_trace = 'enabled=on'; 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\G

trace 输出的关键节点：

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_name=table_name, index_name=idx_name, cardinality=row[1] or 0, rows_examined=0, actual_rows=0, estimation_ratio=0.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(f"EXPLAIN {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( f"SELECT 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']}, " f"actual_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_merge=off'; -- 方式3: 调整代价权重使索引更"便宜" SET SESSION optimizer_switch = 'engine_condition_pushdown=on';

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 事后发现。执行计划优化是数据驱动的工程问题，不是经验驱动的玄学。

查看全文

http://www.gsyq.cn/news/1590390.html