auditing-table-statistics

审计表统计信息

审计优化器表统计信息是否过时、列覆盖是否缺失以及行数是否偏移，以诊断由统计信息过时或不完整导致的查询性能不佳。使用 SHOW STATISTICS 进行只读 SQL 分析，检查表级和列级统计信息的新鲜度，无需访问 DB Console。

**与 profiling-statement-fingerprints 互补：**此技能用于诊断优化器统计信息问题；如需识别历史上的慢查询，请参阅 profiling-statement-fingerprints。

前置条件

• 拥有目标表任意权限的 SQL 连接
• 已启用自动统计信息收集（默认）：sql.stats.automatic_collection.enabled = true

相关技能：profiling-statement-fingerprints 用于历史查询分析，triaging-live-sql-activity 用于实时排查。

核心概念

CockroachDB 特定默认设置：

• 行数变化约 20% 时触发自动收集（sql.stats.automatic_collection.fraction_stale_rows）
• 自动收集涵盖索引列组（默认启用 sql.stats.multi_column_collection.enabled = true）；对非索引列的临时多列统计信息需要手动执行 CREATE STATISTICS
• 大型表（>10M 行）的自动收集可能会延迟
• 过时阈值：超过 7 天（OLTP）或 30 天（OLAP）时刷新，或行数偏移超过 20-30%

请参阅 references/statistics-thresholds.md，了解针对特定工作负载的指导。

核心诊断查询

查询 1：识别统计信息过时或缺失的表

查找统计信息过时或完全没有统计信息的表，并按过时程度排序。

WITH table_stats AS (
  SELECT
    table_catalog,
    table_schema,
    table_name,
    column_names,
    row_count,
    created,
    now() - created AS stats_age
  FROM [SHOW STATISTICS FOR TABLE database_name.*]  -- Replace database_name
  WHERE column_names = '{}'  -- Table-level stats only (empty array)
)
SELECT
  table_schema || '.' || table_name AS full_table_name,
  row_count,
  created AS stats_created_at,
  stats_age,
  CASE
    WHEN created IS NULL THEN 'Missing statistics'
    WHEN stats_age > INTERVAL '30 days' THEN 'Very stale (>30d)'
    WHEN stats_age > INTERVAL '7 days' THEN 'Stale (>7d)'
    ELSE 'Fresh'
  END AS staleness_status
FROM table_stats
WHERE stats_age > INTERVAL '7 days' OR created IS NULL  -- Adjust threshold
ORDER BY stats_age DESC NULLS FIRST
LIMIT 50;

自定义：

• 将 database_name.* 替换为特定的架构模式（例如，mydb.public.*）
• 调整过时阈值：OLTP 使用 INTERVAL '7 days'，OLAP 使用 '30 days'
• 增大 LIMIT 以查看更多表

关键列：

• staleness_status：快速分类统计信息的新鲜度
• stats_age：自上次收集以来的确切时长
• row_count：最近已知的表大小

查询 2：审计特定表的统计信息

显示单个表的所有统计信息，包括表级和逐列详情。

SELECT
  column_names,
  row_count,
  distinct_count,
  null_count,
  created,
  now() - created AS stats_age,
  CASE
    WHEN histogram_id IS NOT NULL THEN 'Yes'
    ELSE 'No'
  END AS has_histogram
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
ORDER BY
  CASE WHEN column_names = '{}' THEN 0 ELSE 1 END,  -- Table-level first
  created DESC;

自定义：

• 将 database_name.schema_name.table_name 替换为完全限定的表名

关键列：

• column_names：空 {} 表示表级，单个元素表示列级
• distinct_count：用于选择性估算的基数
• null_count：用于 IS NULL 谓词的 NULL 值数量
• has_histogram：分布数据是否可用

解读：

• 第一行（column_names = '{}'）显示表级 row_count
• 后续行显示逐列统计信息
• 缺少的列表示尚未收集其统计信息

查询 3：检测行数偏移

将当前表行数与缓存的统计信息进行比较，以识别显著偏移。

WITH current_count AS (
  SELECT count(*) AS actual_rows
  FROM database_name.schema_name.table_name  -- Replace with target table
),
stats_count AS (
  SELECT row_count, created
  FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
  WHERE column_names = '{}'  -- Table-level stats
  ORDER BY created DESC
  LIMIT 1
)
SELECT
  c.actual_rows,
  s.row_count AS stats_rows,
  s.created AS stats_created_at,
  now() - s.created AS stats_age,
  ABS(c.actual_rows - s.row_count) AS drift_absolute,
  ROUND(
    ABS(c.actual_rows - s.row_count)::NUMERIC /
    NULLIF(s.row_count, 0) * 100,
    2
  ) AS drift_pct,
  CASE
    WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.30 THEN 'High drift (>30%)'
    WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.20 THEN 'Medium drift (>20%)'
    WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.10 THEN 'Low drift (>10%)'
    ELSE 'Minimal drift (<10%)'
  END AS drift_status
FROM current_count c, stats_count s;

自定义：

• 在两个 CTE 中替换表名
• 根据工作负载容忍度调整偏移阈值（30%、20%、10%）

关键列：

• drift_pct：当前行数与缓存行数之间的百分比差异
• drift_status：用于确定处理优先级的分类
• stats_age：统计信息上次刷新的时长

解读：

• 偏移 >30%：建议紧急刷新，优化器估算可能非常不准确
• 偏移 20-30%：如果遇到性能问题，可考虑刷新
• 偏移 10-20%：监控变化趋势，可能很快触发自动收集
• 偏移 <10%：正常波动，无需采取措施

查询 4：识别缺失列级统计信息

查找缺少统计信息的表列，重点关注常用于 WHERE/JOIN 子句的列。

WITH table_columns AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_schema = 'schema_name'  -- Replace
    AND table_name = 'table_name'    -- Replace
    AND is_hidden = 'NO'             -- Exclude internal columns
),
stats_columns AS (
  SELECT UNNEST(column_names) AS column_name
  FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
  WHERE column_names != '{}'  -- Exclude table-level stats
)
SELECT
  tc.column_name AS missing_column,
  'No statistics available' AS status
FROM table_columns tc
WHERE tc.column_name NOT IN (SELECT column_name FROM stats_columns)
ORDER BY tc.column_name;

自定义：

• 将 schema_name、table_name 和 database_name 替换为目标表

解读：

• 返回的列没有优化器统计信息
• 优先为以下位置使用的列创建统计信息：
  • WHERE 子句谓词（WHERE user_id = 123）
  • JOIN 条件（JOIN orders ON users.id = orders.user_id）
  • GROUP BY / ORDER BY 表达式

**操作：**生成 CREATE STATISTICS 命令（参见查询 7）

查询 5：直方图覆盖率分析

识别有无直方图数据的列，以优化范围查询。

SELECT
  UNNEST(column_names) AS column_name,
  created,
  now() - created AS stats_age,
  CASE
    WHEN histogram_id IS NOT NULL THEN 'Has histogram'
    ELSE 'Missing histogram'
  END AS histogram_status
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE column_names != '{}'  -- Exclude table-level stats
ORDER BY
  CASE WHEN histogram_id IS NULL THEN 0 ELSE 1 END,  -- Missing first
  created DESC;

自定义：

• 替换 database_name.schema_name.table_name

关键列：

• histogram_status：表示分布数据是否可用
• stats_age：距上次更新直方图的时间

解读：

• 有直方图：优化器可以估算范围扫描的选择率（BETWEEN、>、<）
• 缺少直方图：优化器会假设数据均匀分布（准确度较低）
• 自动收集会创建直方图；缺少直方图表示列非常新，或已禁用收集

查询 6：多列统计信息检测

识别相关列已有的多列（复合）统计信息。

SELECT
  column_names,
  created,
  now() - created AS stats_age,
  row_count,
  ARRAY_LENGTH(column_names, 1) AS column_count
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE ARRAY_LENGTH(column_names, 1) > 1  -- Multi-column only
ORDER BY created DESC;

自定义：

• 替换 database_name.schema_name.table_name

关键列：

• column_names：相关列的数组
• column_count：复合统计信息中的列数

解读：

• 存在：已有多列统计信息——可能是针对索引列组自动收集的，也可能是手动创建的
• 不存在：尚无多列统计信息；对于未建立索引的相关列（例如 city + state + zip），可能需要手动创建
• 常见用例：为索引未涵盖的相关列手动创建统计信息

多列统计信息的创建模式请参见 references/create-statistics-examples.md。

查询 7：生成 CREATE STATISTICS 建议

为统计信息过时或缺失的表生成可直接运行的 CREATE STATISTICS 命令。

WITH stale_tables AS (
  SELECT
    table_schema,
    table_name,
    created,
    now() - created AS stats_age
  FROM [SHOW STATISTICS FOR TABLE database_name.*]
  WHERE column_names = '{}'
    AND (created IS NULL OR now() - created > INTERVAL '7 days')  -- Adjust threshold
)
SELECT
  table_schema || '.' || table_name AS full_table_name,
  stats_age,
  'CREATE STATISTICS __auto__ FROM ' || table_schema || '.' || table_name || ';' AS create_command
FROM stale_tables
ORDER BY stats_age DESC NULLS FIRST
LIMIT 50;

自定义：

• 将 database_name.* 替换为模式匹配表达式
• 调整 INTERVAL '7 days' 过时阈值
• 增大 LIMIT 以获取更多建议

输出：

• create_command：可直接复制粘贴的 SQL 命令
• __auto__：使用自动列选择（推荐的默认设置）

执行：

• 执行前检查生成的命令
• 对大型表（>10M 行），在低流量时段运行
• 监控作业进度（作业监控请参见查询 6）

常见工作流

工作流 1：批量加载后的统计信息审计

**场景：**执行批量 INSERT/COPY/IMPORT 操作后，验证统计信息是否为最新。

步骤：

1. 识别受影响的表：

   -- List tables modified in last 24 hours
   SELECT DISTINCT table_schema || '.' || table_name AS full_table_name
   FROM [SHOW TABLES]
   WHERE table_schema = 'target_schema';  -- Replace
   

2. 检查行数偏差（查询 3）： 对每个受影响的表运行偏差检测查询。

3. 生成并执行刷新命令（查询 7）：

   CREATE STATISTICS __auto__ FROM schema_name.table_name;  -- From Query 7 output
   

4. 监控收集作业：

   SELECT job_id, status, fraction_completed, running_status
   FROM [SHOW JOBS]
   WHERE job_type = 'CREATE STATS'
     AND created > now() - INTERVAL '1 hour'
   ORDER BY created DESC
   LIMIT 10;
   

5. 验证刷新（查询 2）： 重新运行统计信息审计，确认 created 时间戳已更新。

**预期结果：**统计信息时长 <1 小时，drift_pct <5%。

工作流 2：诊断意外的查询计划变更

场景： 查询性能突然下降；EXPLAIN 显示计划发生变化。

步骤：

1. 从 profiling-statement-fingerprints 中确定受影响的查询： 查找延迟突增或计划哈希发生变化的查询。

2. 提取表引用： 解析查询文本，确定 FROM/JOIN 子句中的表。

3. 审核每个表的统计信息（查询 2）： 检查统计信息是否过时，以及行数是否为最新。

4. 比较历史行数与当前行数：

   -- Example: Check if table grew significantly
   SELECT row_count, created
   FROM [SHOW STATISTICS FOR TABLE users]
   WHERE column_names = '{}'
   ORDER BY created DESC
   LIMIT 5;  -- Last 5 collections
   

5. 刷新过时的统计信息（查询 7）： 对偏差较大的表执行 CREATE STATISTICS。

6. 验证计划稳定性： 重新运行 EXPLAIN，确认计划恢复为预期结构。

预期结果： 统计信息刷新后，计划哈希趋于稳定，延迟恢复至基线水平。

工作流 3：定期检查统计信息健康状况

场景： 定期审核，主动发现统计信息问题，避免性能下降。

步骤：

1. 运行集群范围的过时情况扫描（查询 1）：

   -- All databases
   SHOW STATISTICS FOR TABLE *.*;  -- Warning: May be slow on large clusters
   

2. 确定关键表的优先级： 优先关注来自 profiling-statement-fingerprints 的高流量表。

3. 检查是否已启用自动收集：

   SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should be true
   

4. 查看待处理的自动收集作业：

   SELECT job_id, description, status, fraction_completed
   FROM [SHOW JOBS]
   WHERE job_type = 'AUTO CREATE STATS'
     AND status IN ('pending', 'running')
   ORDER BY created DESC;
   

5. 生成批量刷新脚本（查询 7）： 将输出保存到文件，以便按计划执行。

6. 安排在维护时段刷新： 在低流量时段执行生成的 CREATE STATISTICS 命令。

频率： OLTP 每周一次，OLAP 每月一次。

安全注意事项

适用于生产环境的操作

SHOW STATISTICS：

• 影响： 只读，不影响集群
• 适用于生产环境： 是，可随时运行，不受限制

CREATE STATISTICS：

• 影响： 消耗大量 CPU/IO，执行非阻塞的表扫描
• 安全性：
  • 不会锁定表或阻塞写入
  • 会消耗资源（CPU、网络、磁盘 I/O）
  • 在大型表上收集统计信息时，可能影响查询性能
• 最佳实践：
  • 对行数超过 10M 的表，在低流量时段运行
  • 错开执行时间（避免同时对许多表创建统计信息）
  • 监控作业进度和资源利用率

资源消耗

小型表（少于 10K 行）： 影响可忽略，可随时执行

中型表（10K–10M 行）： 耗时数秒至数分钟，影响较小

大型表（超过 10M 行）： 耗时数分钟至数小时，请做好规划：

• 安排在维护时段执行
• 收集期间监控集群指标（CPU、磁盘 I/O）
• 使用 SHOW JOBS 跟踪进度

取消（如有需要）：

-- Find job ID
SELECT job_id, status, fraction_completed
FROM [SHOW JOBS]
WHERE job_type = 'CREATE STATS' AND status = 'running';

-- Cancel job (non-destructive, existing statistics remain)
CANCEL JOB 123456789012345678;

批量收集的最佳实践

避免集群过载：

• 并发收集统计信息的表最多为 3–5 个
• 等待完成后再开始下一批
• 在各批次之间监控集群健康指标

错峰执行脚本示例：

# Collect statistics in batches with delays
for table in table1 table2 table3; do
  cockroach sql -e "CREATE STATISTICS __auto__ FROM $table;" &
done
wait  # Wait for batch to complete

sleep 60  # Delay between batches

for table in table4 table5 table6; do
  cockroach sql -e "CREATE STATISTICS __auto__ FROM $table;" &
done
wait

详细的批量处理模式请参阅 references/create-statistics-examples.md。

故障排查

问题	可能原因	解决方法
SHOW STATISTICS 返回空结果	从未收集过统计信息	运行 CREATE STATISTICS __auto__ FROM table_name;
非空表的 row_count 显示为 0	统计信息不同步	刷新：CREATE STATISTICS __auto__ FROM table_name;
权限被拒绝错误	没有表权限	授予任意权限：GRANT SELECT ON table_name TO user;
CREATE STATISTICS 作业卡住	大表且写入量高	检查 SHOW JOBS 状态；考虑执行 CANCEL JOB，并在低流量时段重试
未触发自动收集	设置已禁用或未达到阈值	确认 sql.stats.automatic_collection.enabled = true，并检查行数偏差
统计信息已存在，但查询计划仍不理想	统计信息过时或缺少多列统计信息	刷新现有统计信息；为存在相关性的列创建多列统计信息（参见 Query 6）
偏差较大，但 created timestamp 很近	两次收集之间写入量极大	降低自动收集阈值或提高手动刷新频率

防御性查询模式

处理缺失的统计信息：

-- Use COALESCE for NULL created timestamps
SELECT COALESCE(created, '1970-01-01'::TIMESTAMP) AS stats_created_at
FROM [SHOW STATISTICS FOR TABLE table_name]
WHERE column_names = '{}';

避免在偏差计算中除以零：

-- Use NULLIF to prevent divide-by-zero errors
SELECT
  ABS(actual - stats)::NUMERIC / NULLIF(stats, 0) * 100 AS drift_pct
FROM ...;

关键注意事项

• 自动收集与手动收集： 保持启用自动收集作为基线；对于临时批量加载后的刷新和关键表，使用手动 CREATE STATISTICS
• 多列统计信息： 自动收集涵盖索引列组；对于查询中一起使用的非索引相关列，需要手动执行 CREATE STATISTICS（例如，CREATE STATISTICS city_state_stats ON city, state FROM addresses;）
• 大表（>10M 行）： 安排在维护窗口期间执行 CREATE STATISTICS；使用 SHOW JOBS WHERE job_type = 'CREATE STATS' 进行监控
• 过时阈值设置： OLTP：3-7 天，OLAP：14-30 天，混合型：关键表 3-7 天，归档表 30 天以上
• 权限： 任意表权限（SELECT、INSERT 等）均可查看统计信息

详细指南请参阅 references/create-statistics-examples.md 和 references/statistics-thresholds.md。

参考资料

CockroachDB 官方文档：

• SHOW STATISTICS - 完整语法和输出架构
• CREATE STATISTICS - 手动收集统计信息指南
• 基于成本的优化器 - 优化器如何使用统计信息
• 表统计信息 - 统计信息对查询规划的影响
• SHOW JOBS - 作业监控和管理

相关技能：

• profiling-statement-fingerprints - 识别慢查询模式
• triaging-live-sql-activity - 实时查询排查

补充参考资料：

• 统计信息阈值指南 - 特定工作负载的过时和偏差阈值
• CREATE STATISTICS 示例 - 全面的收集模式和批处理策略