数据库查询优化中的动态采样与统计信息管理

我将为您讲解这个数据库查询优化中的重要主题。让我们从基本概念开始，逐步深入。

一、问题背景与核心概念

1.1 什么是统计信息？

统计信息是描述数据库中数据分布特征的元数据，主要包括：

• 表的行数（基数）
• 列的不同值数量（NDV）
• 列的最小值、最大值
• 数据分布直方图
• 列之间的相关性

1.2 统计信息的重要性

查询优化器需要统计信息来：

• 估算查询结果集大小
• 选择最优的连接顺序
• 选择最有效的访问路径
• 估算排序、聚合等操作的代价

1.3 传统统计信息的局限性

传统统计信息收集是静态的：

1. 需要手动执行统计信息收集命令
2. 数据变化后统计信息会过时
3. 全表扫描收集代价高昂
4. 无法适应实时变化的数据特征

二、动态采样的基本概念

2.1 什么是动态采样？

动态采样是在查询编译时实时收集统计信息的技术，特点包括：

• 运行时执行小规模采样查询
• 基于样本数据估算统计信息
• 不需要预先收集和维护统计信息

2.2 动态采样的优势

1. 实时性：反映最新的数据分布
2. 灵活性：按需采样，避免全量统计
3. 适应性：自动适应数据变化
4. 低成本：采样代价远小于全表统计

三、动态采样的实现机制

3.1 采样触发条件

数据库在以下情况下会触发动态采样：

-- 示例：当优化器发现缺少统计信息时
SELECT * FROM orders WHERE customer_id = 123;
-- 如果orders表的统计信息过时或缺失
-- 优化器会自动触发动态采样

具体触发条件：

1. 表没有统计信息
2. 统计信息过于陈旧（超过阈值）
3. 查询涉及的数据量很小
4. 使用并行查询时
5. 启用了动态采样参数

3.2 采样方法与算法

3.2.1 随机块采样

-- 内部实现类似（概念示意）：
SELECT COUNT(*) FROM (
  SELECT * FROM orders 
  TABLESAMPLE SYSTEM(1)  -- 采样1%的数据块
) sample;

实现步骤：

1. 随机选择数据块
2. 读取选中块的所有数据
3. 基于样本计算统计信息
4. 外推到全表

3.2.2 系统抽样

-- 更均匀的采样方式
SELECT * FROM orders 
TABLESAMPLE BERNOULLI(0.1)  -- 伯努利抽样，每行独立采样
ROWS 1000;                  -- 采样1000行

算法对比：

• 块采样：效率高，但可能引入偏差
• 行采样：更准确，但代价更高
• 分层采样：对倾斜数据更有效

3.3 采样精度控制

数据库通过采样级别控制精度：

-- Oracle示例：设置动态采样级别
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 4;
-- 级别说明：
-- 0: 禁用
-- 2: 对无统计信息的表采样
-- 4: 对无统计信息或统计信息过时的表采样
-- 10: 对所有表强制采样

四、统计信息管理系统

4.1 统计信息收集策略

4.1.1 自动收集任务

-- 自动收集统计信息的配置
DBMS_STATS.SET_GLOBAL_PREFS(
  'AUTOSTATS_TARGET', 'AUTO'  -- 自动收集目标
);

收集策略包括：

1. 增量统计：只收集变化部分
2. 并发收集：并行收集多个对象
3. 在线收集：不阻塞DML操作
4. 分区统计：只收集变化分区

4.1.2 智能收集决策

收集器会根据以下因素决定是否收集：

• 数据修改比例（DML变化量）
• 统计信息陈旧度
• 表的重要性（访问频率）
• 系统负载情况

4.2 统计信息维护

4.2.1 统计信息存储

-- 统计信息存储在系统表中
-- 示例结构：
CREATE TABLE sys_table_stats (
  table_id       NUMBER,
  num_rows       NUMBER,
  blocks         NUMBER,
  avg_row_len    NUMBER,
  last_analyzed  DATE
);

CREATE TABLE sys_column_stats (
  column_id      NUMBER,
  num_distinct   NUMBER,
  low_value      RAW(100),
  high_value     RAW(100),
  density        NUMBER
);

4.2.2 直方图管理

直方图类型：

1. 等高直方图：每个桶包含相同行数
2. 等宽直方图：每个桶覆盖相同值范围
3. 频率直方图：记录每个值的频次
4. Top-N直方图：只记录高频值

-- 创建直方图
DBMS_STATS.GATHER_TABLE_STATS(
  tabname     => 'ORDERS',
  method_opt  => 'FOR COLUMNS customer_id SIZE 254'
);

五、动态采样与统计信息的协同工作

5.1 工作流程

开始查询优化
    ↓
检查统计信息可用性
    ↓
    ┌─────────────┐
    │ 统计信息    │
    │ 是否可用？  │
    └──────┬──────┘
           │
    ┌──────┴──────┐   否
    │ 使用现有    ├─────────┐
    │ 统计信息    │         │
    └─────────────┘         │
           │                ↓
           │        触发动态采样
           │                │
           │        收集样本数据
           │                │
           │        估算统计信息
           │                │
           └────────┐       │
                    ↓       ↓
               生成查询计划
                    ↓
               执行查询

5.2 示例场景分析

场景1：新表无统计信息

-- 新建表，没有任何统计信息
CREATE TABLE new_sales AS 
SELECT * FROM sales WHERE 1=0;

-- 插入大量数据
INSERT INTO new_sales SELECT * FROM sales;

-- 首次查询触发动态采样
EXPLAIN PLAN FOR
SELECT * FROM new_sales 
WHERE sale_date > DATE '2023-01-01';

-- 优化器会：
-- 1. 发现new_sales无统计信息
-- 2. 自动执行动态采样（如采样0.1%数据）
-- 3. 基于样本估算选择率
-- 4. 生成执行计划

场景2：统计信息过时

-- 表有旧统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES');
END;

-- 之后大量数据变化
DELETE FROM sales WHERE sale_date < DATE '2022-01-01';

-- 查询时优化器发现数据变化
SELECT /*+ DYNAMIC_SAMPLING(4) */ 
       COUNT(*) 
FROM sales 
WHERE product_id = 100;

-- 优化器检测到：
-- 1. 实际行数 vs 统计信息行数差异大
-- 2. 触发动态采样验证
-- 3. 使用更准确的估算值

六、高级优化技术

6.1 自适应统计信息

-- 自动识别需要直方图的列
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    'SH', 'SALES',
    'AUTO_STAT_EXTENSIONS', 'ON'
  );
END;

自适应功能包括：

1. 自动列组检测
2. 表达式统计信息
3. 增量统计信息维护
4. 实时基本统计信息

6.2 反馈机制

-- 执行计划反馈示例
-- 首次执行：基于不准确统计信息
SELECT * FROM orders 
WHERE status = 'SHIPPED' 
AND order_date > SYSDATE - 30;

-- 优化器发现：
-- 1. 估算行数：1000
-- 2. 实际行数：10000
-- 3. 差异过大，记录反馈

-- 下次相同查询：
-- 1. 使用反馈信息调整
-- 2. 可能触发重新硬解析
-- 3. 生成更优计划

七、性能与精度权衡

7.1 采样开销分析

采样代价 = 采样时间 + 计算时间

影响因素：
1. 采样比例：比例↑ → 精度↑ → 代价↑
2. 表大小：大小↑ → 代价↑
3. 系统负载：负载↑ → 代价↑
4. 采样方法：行采样 > 块采样

7.2 优化策略

7.2.1 分层采样策略

-- 对倾斜数据使用分层采样
-- 先识别数据分布特征
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1000;  -- 识别高频值

-- 对高频值单独采样
-- 对低频值使用不同采样率

7.2.2 自适应采样率

动态调整采样率：
1. 初始采样：小样本（0.1%）
2. 如果方差高 → 增加采样
3. 如果方差低 → 保持或减少
4. 置信区间控制：确保估算精度

八、实际应用建议

8.1 配置最佳实践

-- Oracle配置示例
BEGIN
  -- 启用自动统计信息收集
  DBMS_STATS.SET_GLOBAL_PREFS(
    'AUTOSTATS_TARGET', 'ORACLE'
  );
  
  -- 设置动态采样级别
  DBMS_STATS.SET_PARAM(
    'DYN_SAMPLING_ESTIMATE_CF', 4
  );
  
  -- 设置统计信息保留策略
  DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(30);
END;

8.2 监控与维护

-- 监控统计信息质量
SELECT 
  table_name,
  last_analyzed,
  stale_stats
FROM dba_tab_statistics
WHERE owner = 'SH'
  AND stale_stats = 'YES';

-- 检查统计信息使用情况
SELECT 
  sql_id,
  plan_hash_value,
  executions,
  buffer_gets
FROM v$sql
WHERE sql_text LIKE '%关键表%';

九、总结与要点

1. 动态采样是补充：不是替代传统统计信息，而是补充
2. 智能决策：现代数据库自动决定何时使用动态采样
3. 精度与代价平衡：在采样精度和开销间取得平衡
4. 反馈机制：基于执行反馈不断改进
5. 自适应优化：根据数据特征自动调整策略

通过动态采样与统计信息管理的协同工作，数据库能够在保证查询性能的同时，适应数据的实时变化，是现代数据库优化器的重要能力之一。