一、统计信息：优化器的"决策依据"

1.1 统计信息的核心作用

• 数据分布感知：通过直方图（Histogram）记录列值的分布密度，例如某列中NULL值占比、高频值（Hot Value）分布。
• 基数估算（Cardinality Estimation）：预测查询结果集的行数，直接影响连接顺序、索引选择等决策。
• 成本模型输入：结合I/O、CPU、网络等代价参数，计算不同执行计划的成本值。

1.2 统计信息的类型与采集方式

• 单列统计信息：
  • 直方图：适用于高基数列（如用户ID），记录值分布区间。
  • 最值与密度：记录列的最小值、最大值、唯一值数量（NDV）。
• 多列统计信息：
  • 相关性分析：捕获列间依赖关系（如state与zip_code的关联性）。
• 采集方法：
  • 自动收集：Oracle的AUTO_SAMPLE_SIZE、PostgreSQL的autovacuum。
  • 手动触发：ANALYZE TABLE（MySQL）、DBMS_STATS.GATHER_TABLE_STATS（Oracle）。
  • 增量更新：基于变更数据量（如MySQL的stats_persistent）。

1.3 常见统计信息问题

• 过时统计：数据批量导入后未更新统计信息，导致基数低估。
• 选择性偏差：如WHERE status='active'的记录占比从10%变为90%，但统计未更新。
• 多列相关性缺失：优化器误判连接条件的选择性，选择低效的哈希连接而非嵌套循环。

二、查询优化器调优策略

2.1 优化器工作原理剖析

• 基于成本的优化（CBO）：
  • 生成候选执行计划（如全表掃描、索引掃描、排序合并连接）。
  • 通过统计信息计算各步骤的代价（如cost = seq_page_cost * 掃描页数）。
• 启发式规则：
  • 优先使用覆盖索引、规避笛卡尔积等。

2.2 调优方法论

1. 问题定位：
   • 通过EXPLAIN分析执行计划，识别全表掃描、低效连接等。
   • 检查统计信息是否准确（如SHOW INDEX（MySQL）或DBA_TAB_STATISTICS（Oracle））。
2. 统计信息优化：
   • 手动更新：对高频查询表执行全量统计收集。
   • 直方图调整：为倾斜列（如用户活跃度评分）创建直方图。
   • 采样率控制：对大表调整采样比例（如Oracle的ESTIMATE_PERCENT）。
3. 优化器参数调优：
   • 调整成本模型：如PostgreSQL的random_page_cost（影响I/O代价权重）。
   • 强制执行计划：使用提示（Hint）或存储轮廓（Outline）锁定高效计划。
4. 索引与查询重构：
   • 复合索引优化：确保查询条件覆盖索引前缀（如WHERE a=1 AND b=2的索引(a,b)）。
   • 规避函数操作：如WHERE YEAR(create_time)=2023导致索引失效，改用范围查询。

2.3 实战案例：电商订单查询优化

• 问题：订单表orders（1亿行）的WHERE user_id=123 AND status='paid'查询耗时10秒。
• 分析：
  • EXPLAIN显示全表掃描，实际user_id=123仅返回1行。
  • 统计信息显示status列的NDV为5，但paid占比仅0.1%，未被优化器识别。
• 解决：
  1. 手动更新统计信息：ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
  2. 创建复合索引：ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
• 效果：查询耗时降至0.02秒，执行计划改为索引范围掃描。

三、高级调优技巧

3.1 动态采样与自适应优化

• 动态采样：Oracle的OPTIMIZER_DYNAMIC_SAMPLING在统计缺失时临时采样。
• 自适应执行计划：SQL Server的Adaptive Query Processing在运行时切换计划。

3.2 监控与自动化

• 统计信息老化监控：
  • MySQL的information_schema.TABLE_STATISTICS记录更新时间。
  • Oracle的DBA_TAB_MODIFICATIONS跟踪表变更量。
• 自动化脚本：
  sql

  	-- MySQL示例：自动更新变更量超过10%的表
  	SELECT table_name
  	FROM information_schema.tables
  	WHERE table_schema = 'ecommerce'
  	AND (update_time < NOW() - INTERVAL 7 DAY OR
  	(SELECT SUM(rows_changed) FROM mysql.innodb_table_stats
  	WHERE table_name = t.table_name) > 0.1 * t.table_rows);

3.3 分布式数据库调优

• 全局统计信息：TiDB的ANALYZE TABLE收集跨分片统计。
• 数据倾斜处理：对热点分片单独优化（如增加副本或调整分片键）。

四、结语

数据库统计信息管理与查询优化器调优是一个持续迭代的过程。通过深入理解统计信息的生成机制、优化器的决策逻辑，并结合自动化监控与针对性调优，可以显著提升查询性能。未来，随着AI技术的融入（如机器学习驱动的成本模型），数据库调优将向智能化、自适应方向发展，但基础原理的掌握仍是解决问题的关键。