数据库查询优化中的多列统计与相关性分析

题目描述
多列统计与相关性分析是数据库查询优化器的重要功能，用于处理多列条件查询时的选择性估算问题。当查询条件涉及多个相关列时，如果优化器只使用单列统计信息，会导致代价估算严重偏差，从而选择低效的执行计划。本知识点讲解多列统计的原理、创建方式，以及如何通过相关性分析提高查询性能估算的准确性。

知识讲解

1. 问题背景：单列统计的局限性

• 单列统计信息：包括列的不同值数量（NDV）、数据分布直方图等
• 独立性假设问题：优化器默认认为各列之间相互独立，通过以下公式计算联合选择性：
  selectivity(col1=a AND col2=b) = selectivity(col1=a) × selectivity(col2=b)
• 实际案例：

  -- 假设country和city列高度相关
  SELECT * FROM customers 
  WHERE country = 'China' AND city = 'Beijing';
  

  如果单纯使用独立性假设，会严重低估真实的选择性（因为中国的客户大多集中在几个主要城市）

2. 多列统计的解决方案

• 扩展统计对象：收集多个列的组合统计信息
• 主要类型：
  • 多列直方图：记录列值组合的联合分布
  • 相关性统计：计算列间的相关系数
  • 函数依赖统计：识别列间的函数依赖关系

3. 多列统计的创建与管理

• 创建语法示例（MySQL）：

  ANALYZE TABLE customers UPDATE HISTOGRAM ON (country, city);
  

• 创建语法示例（PostgreSQL）：

  CREATE STATISTICS cust_geo_stats (dependencies) ON country, city FROM customers;
  ANALYZE customers;
  

• 查看统计信息：

  -- PostgreSQL查看依赖统计
  SELECT stxname, stxkeys, stxdependencies
  FROM pg_statistic_ext WHERE stxname = 'cust_geo_stats';
  

4. 相关性分析的核心指标

• 相关系数计算：
  • 取值范围：[-1, 1]，绝对值越大相关性越强
  • 公式：ρ = cov(X,Y) / (σ_X × σ_Y)
• 实际计算过程：
  1. 计算每列的均值μ和标准差σ
  2. 计算协方差cov(X,Y) = E[(X-μ_X)(Y-μ_Y)]
  3. 代入公式得到相关系数

5. 优化器如何使用多列统计

• 选择性估算改进：
  • 检测到列组合存在统计信息时，使用实际收集的联合分布
  • 对于部分相关列，使用条件概率公式：
    P(A and B) = P(A) × P(B|A)
• 执行计划选择：

  EXPLAIN SELECT * FROM sales 
  WHERE product_category = '电子产品' AND price > 5000;
  

  准确的选择性估算帮助优化器正确选择索引扫描还是全表扫描

6. 实践应用场景

• 高度相关列：如国家-省份、产品类别-品牌
• 函数依赖列：如邮政编码-城市、订单ID-订单日期
• 反相关列：如促销标志-高价格商品

7. 注意事项与最佳实践

• 统计维护成本：多列统计需要更多存储空间和计算资源
• 选择策略：只为确实存在强相关性的列创建多列统计
• 更新频率：数据分布变化大的表需要更频繁更新统计
• 验证效果：通过执行计划对比验证多列统计的实际效果

总结
多列统计与相关性分析通过捕获列间的真实关系，解决了优化器在独立性假设下的估算偏差问题。正确使用这一技术可以显著改善复杂查询的执行效率，是多表关联查询和复杂条件查询优化的重要手段。