数据库慢查询治理：从索引原理到执行计划的优化实践
一、慢查询的隐性成本
一个执行时间 500ms 的查询，在低峰期可能不会引起注意，但在高峰期可能成为数据库性能的瓶颈。更麻烦的是，慢查询的影响会像滚雪球一样扩大——一个慢查询占用了大量 Buffer Pool 页，导致其他查询的缓存命中率下降，进而产生更多慢查询，形成恶性循环。
慢查询治理的难点不在于"发现慢查询"（slow_query_log谁都会开），而在于"理解为什么慢"和"确定怎么优化"。同一个慢查询，可能是因为缺少索引，也可能是因为索引选择错误，还可能是因为数据分布倾斜导致优化器误判。只有理解了数据库索引的底层结构和查询优化器的工作机制，才能做出正确的优化决策。
本文将从 B+ 树索引的物理结构出发，拆解 MySQL 查询优化器的执行计划选择逻辑，给出系统性的慢查询治理方案。
二、B+ 树索引的物理结构：为什么索引能加速查询
MySQL InnoDB 的索引基于 B+ 树实现。理解 B+ 树的物理结构，是理解索引优化原理的前提。
graph TD subgraph "B+ 树索引结构（3层）" R["Root Page<br/>key: 100, 200, 300"] R --> L1["Level 1: key < 100"] R --> L2["Level 1: 100 ≤ key < 200"] R --> L3["Level 1: 200 ≤ key < 300"] R --> L4["Level 1: key ≥ 300"] L1 --> D1["Leaf: [1,5,23,45,67,89]"] L2 --> D2["Leaf: [100,123,145,167,189]"] L3 --> D3["Leaf: [200,223,245,267,289]"] L4 --> D4["Leaf: [300,323,345,367,389]"] D1 <-->|双向链表| D2 D2 <-->|双向链表| D3 D3 <-->|双向链表| D4 end subgraph "索引查找过程" E["SELECT * FROM t WHERE id = 145"] --> F["Root: 145 < 200 → L2"] F --> G["L2: 100 ≤ 145 < 200 → D2"] G --> H["D2: 二分查找 → 找到 145"] end style R fill:#fff3e0 style D2 fill:#e8f5e9 style H fill:#e8f5e9B+ 树的关键特性：
三层结构：Root Page → Intermediate Pages → Leaf Pages。InnoDB 的 Page 大小默认 16KB，一个 Page 大约存储 1000 个键值（假设 key 为 8 字节 int）。三层 B+ 树可以索引约 10 亿行数据（1000 × 1000 × 1000）。这意味着任何一次索引查找最多 3 次 I/O。
叶子节点双向链表：范围查询（WHERE id BETWEEN 100 AND 200）只需定位到起始叶子节点，然后沿链表顺序扫描，无需回溯上层节点。这是 B+ 树比 B 树更适合数据库索引的核心原因。
聚簇索引 vs 二级索引：聚簇索引（主键索引）的叶子节点存储完整的行数据，二级索引的叶子节点存储主键值。通过二级索引查找数据时，需要先在二级索引上找到主键值，再回到聚簇索引查找行数据——这就是"回表"操作。回表的代价是额外的 B+ 树查找，一次回表等于一次随机 I/O。
索引选择性与基数：索引的选择性 =COUNT(DISTINCT col) / COUNT(*)。选择性越高，索引过滤效果越好。性别字段的选择性约 0.5（只有两个值），不适合建索引；用户 ID 的选择性约 1.0（每个值唯一），是最理想的索引列。
三、慢查询治理框架：从发现到优化的完整链路
"""slow_query_analyzer.py —— 慢查询分析与优化建议引擎""" import re from dataclasses import dataclass, field from enum import Enum from typing import Optional class QueryIssue(Enum): FULL_TABLE_SCAN = "full_table_scan" # 全表扫描 INDEX_MISS = "index_miss" # 缺少索引 INDEX_MISUSE = "index_misuse" # 索引使用不当 FILESORT = "filesort" # 文件排序 TEMPORARY_TABLE = "temporary_table" # 临时表 RANGE_SCAN_TOO_LARGE = "range_scan_too_large" # 范围扫描行数过多 SUBQUERY_DEPENDENT = "subquery_dependent" # 相关子查询 class OptimizationAction(Enum): CREATE_INDEX = "create_index" MODIFY_INDEX = "modify_index" REWRITE_QUERY = "rewrite_query" FORCE_INDEX = "force_index" PARTITION_TABLE = "partition_table" ARCHIVE_DATA = "archive_data" @dataclass class ExplainRow: """EXPLAIN 输出一行""" id: int select_type: str table: str partitions: Optional[str] type: str # ALL, index, range, ref, eq_ref, const... possible_keys: str key: str # 实际使用的索引 key_len: int # 索引长度（字节） ref: str rows: int # 预估扫描行数 filtered: float # 过滤比例 extra: str # Using index, Using filesort, Using temporary... @dataclass class SlowQuery: """慢查询记录""" query: str execution_time_ms: float rows_examined: int rows_sent: int explain_rows: list[ExplainRow] = field(default_factory=list) @dataclass class OptimizationSuggestion: """优化建议""" issue: QueryIssue action: OptimizationAction description: str sql: Optional[str] = None # 建议执行的 SQL（如 CREATE INDEX） estimated_improvement: str = "" # 预估改善效果 class SlowQueryAnalyzer: """慢查询分析器——基于 EXPLAIN 结果自动诊断问题""" def analyze(self, slow_query: SlowQuery) -> list[OptimizationSuggestion]: """分析慢查询，返回优化建议列表""" suggestions = [] for row in slow_query.explain_rows: # 检查 1：全表扫描 if row.type == "ALL": suggestions.append(self._diagnose_full_scan(row, slow_query)) # 检查 2：索引使用不当（possible_keys 有值但 key 为空） if row.possible_keys and not row.key: suggestions.append(self._diagnose_index_not_used(row, slow_query)) # 检查 3：文件排序 if "Using filesort" in row.extra: suggestions.append(self._diagnose_filesort(row, slow_query)) # 检查 4：临时表 if "Using temporary" in row.extra: suggestions.append(self._diagnose_temporary_table(row, slow_query)) # 检查 5：范围扫描行数过多 if row.type == "range" and row.rows > 10000: suggestions.append(self._diagnose_large_range(row, slow_query)) # 检查 6：相关子查询 if row.select_type == "DEPENDENT SUBQUERY": suggestions.append(self._diagnose_dependent_subquery(row, slow_query)) return [s for s in suggestions if s is not None] def _diagnose_full_scan( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断全表扫描，建议创建索引""" # 从 WHERE 子句中提取可能的索引列 where_columns = self._extract_where_columns(query.query) if where_columns: index_columns = ", ".join(where_columns) return OptimizationSuggestion( issue=QueryIssue.FULL_TABLE_SCAN, action=OptimizationAction.CREATE_INDEX, description=f"表 {row.table} 全表扫描，扫描 {row.rows} 行", sql=f"CREATE INDEX idx_{row.table}_{'_'.join(where_columns)} " f"ON {row.table} ({index_columns})", estimated_improvement=f"预计扫描行数从 {row.rows} 降至 < 100", ) return OptimizationSuggestion( issue=QueryIssue.FULL_TABLE_SCAN, action=OptimizationAction.CREATE_INDEX, description=f"表 {row.table} 全表扫描，扫描 {row.rows} 行，无法自动提取索引列", ) def _diagnose_index_not_used( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断索引未被使用""" return OptimizationSuggestion( issue=QueryIssue.INDEX_MISUSE, action=OptimizationAction.FORCE_INDEX, description=f"表 {row.table} 有可用索引 ({row.possible_keys}) 但未使用，" f"可能是优化器判断全表扫描更快", sql=f"SELECT ... FROM {row.table} FORCE INDEX ({row.possible_keys}) ...", estimated_improvement="强制使用索引可能减少扫描行数，但需验证", ) def _diagnose_filesort( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断文件排序，建议创建覆盖 ORDER BY 的复合索引""" order_columns = self._extract_order_by_columns(query.query) if order_columns: # 复合索引应包含 WHERE 条件列 + ORDER BY 列 where_columns = self._extract_where_columns(query.query) all_columns = where_columns + [c for c in order_columns if c not in where_columns] index_columns = ", ".join(all_columns) return OptimizationSuggestion( issue=QueryIssue.FILESORT, action=OptimizationAction.CREATE_INDEX, description=f"表 {row.table} 使用文件排序，ORDER BY 列未走索引", sql=f"CREATE INDEX idx_{row.table}_sort " f"ON {row.table} ({index_columns})", estimated_improvement="消除 filesort，排序在索引扫描中完成", ) return OptimizationSuggestion( issue=QueryIssue.FILESORT, action=OptimizationAction.REWRITE_QUERY, description="文件排序无法通过索引优化，考虑减少排序数据量", ) def _diagnose_temporary_table( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断临时表""" return OptimizationSuggestion( issue=QueryIssue.TEMPORARY_TABLE, action=OptimizationAction.REWRITE_QUERY, description=f"表 {row.table} 使用临时表，通常由 GROUP BY + 无索引引起", sql=None, estimated_improvement="为 GROUP BY 列创建索引可消除临时表", ) def _diagnose_large_range( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断范围扫描行数过多""" return OptimizationSuggestion( issue=QueryIssue.RANGE_SCAN_TOO_LARGE, action=OptimizationAction.ARCHIVE_DATA, description=f"范围扫描 {row.rows} 行，数据量过大", estimated_improvement="考虑数据归档或分区表，减少单次扫描数据量", ) def _diagnose_dependent_subquery( self, row: ExplainRow, query: SlowQuery ) -> OptimizationSuggestion: """诊断相关子查询，建议改写为 JOIN""" return OptimizationSuggestion( issue=QueryIssue.SUBQUERY_DEPENDENT, action=OptimizationAction.REWRITE_QUERY, description="相关子查询对外层每行都执行一次，性能极差", sql="将 IN (SELECT ...) 改写为 JOIN", estimated_improvement="子查询改写为 JOIN 通常可提升 10-100 倍", ) def _extract_where_columns(self, query: str) -> list[str]: """从 SQL 中提取 WHERE 子句的列名（简化实现）""" where_match = re.search(r"WHERE\s+(.+?)(?:GROUP|ORDER|LIMIT|$)", query, re.IGNORECASE) if not where_match: return [] where_clause = where_match.group(1) # 提取 col = ... 或 col IN (...) 中的列名 columns = re.findall(r"(\w+)\s*(?:=|IN|>|<|>=|<=|LIKE|BETWEEN)", where_clause, re.IGNORECASE) return list(dict.fromkeys(columns)) # 去重保序 def _extract_order_by_columns(self, query: str) -> list[str]: """从 SQL 中提取 ORDER BY 子句的列名""" order_match = re.search(r"ORDER\s+BY\s+(.+?)(?:LIMIT|$)", query, re.IGNORECASE) if not order_match: return [] order_clause = order_match.group(1) columns = re.findall(r"(\w+)", order_clause) # 过滤掉 ASC/DESC return [c for c in columns if c.upper() not in ("ASC", "DESC")]四、索引优化的代价：写入性能与存储空间的权衡
索引对写入的影响：每个索引在 INSERT/UPDATE/DELETE 时都需要同步维护。一张表有 5 个索引，每次 INSERT 就需要更新 6 棵 B+ 树（1 个聚簇索引 + 5 个二级索引）。在高写入场景下，索引数量应控制在 3-5 个以内，超过 5 个索引的表，写入性能会显著下降。
复合索引的最左前缀原则：复合索引(a, b, c)只能支持a、a,b、a,b,c三种查询模式。查询条件只有b或b,c时无法使用该索引。这意味着复合索引的列顺序至关重要——区分度高的列放前面，范围查询的列放后面。
索引统计信息的时效性：MySQL 优化器依赖索引统计信息（Cardinality、Rows）来选择执行计划。统计信息不是实时更新的，而是通过采样估算。当数据分布发生显著变化时（如大批量导入数据），统计信息可能过时，导致优化器选择错误的索引。手动执行ANALYZE TABLE可以更新统计信息。
适用边界：此治理方案适用于 MySQL 5.7+/8.0+ 的 InnoDB 引擎。PostgreSQL 的查询优化器基于成本估算（CBO）且支持部分索引，优化策略有所不同。对于分库分表场景，慢查询治理还需要考虑跨分片的查询合并与排序问题。
五、总结
慢查询治理的核心是"理解执行计划，而非盲目加索引"。B+ 树的物理结构决定了索引的加速原理，优化器的成本估计算法决定了索引的选择逻辑。落地建议：开启slow_query_log并设置合理阈值（200ms），定期用EXPLAIN分析慢查询，根据扫描类型和 Extra 信息定位问题根因。索引不是越多越好，每个索引都有写入代价。用数据说话，只加必要的索引。
