profiling-statement-fingerprints

语句指纹分析

使用聚合 SQL 统计信息分析历史语句性能模式，识别缓慢、资源密集或容易出错的查询指纹。通过 crdb_internal.statement_statistics 按时间窗口分析延迟、CPU、争用、准入延迟和失败率——完全通过 SQL 操作，无需访问 DB Console。

**作为 triaging-live-sql-activity 的补充：**此技能分析历史模式；如需立即排查当前正在运行的查询，请参阅 triaging-live-sql-activity。

何时使用此技能

• 查找过去数小时、数天或数周内最慢的语句指纹
• 查找 CPU 消耗、争用或准入等待较高的查询
• 调查性能回退或执行计划变更
• 通过索引建议定位全表扫描或缺失的索引
• 按应用或数据库分析资源消耗
• 无需访问 DB Console，仅使用 SQL 进行历史分析

**如需立即响应事故：**使用 triaging-live-sql-activity 排查当前正在运行的查询，并取消失控的任务。 **如需进行事务级分析：**使用 profiling-transaction-fingerprints 在事务边界分析重试模式、提交延迟和语句组成。 **如需监控后台作业：**使用 monitoring-background-jobs 监控长时间运行的架构变更和未纳入语句统计的自动作业。

先决条件

• 连接 CockroachDB 集群的 SQL 连接
• 要查看整个集群，需具有 VIEWACTIVITY 或 VIEWACTIVITYREDACTED 集群权限
• 已启用语句统计信息收集（默认）：sql.stats.automatic_collection.enabled = true

检查收集状态：

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

有关 RBAC 设置，请参阅 triaging-live-sql-activity 权限参考（权限相同）。

核心概念

语句指纹与实时查询

**语句指纹：**将常量参数化后的规范化 SQL 模式（例如，SELECT * FROM users WHERE id = $1 与 SELECT * FROM users WHERE id = 123）

主要区别：

• **时间范围：**历史小时级时间桶与实时当前状态
• **粒度：**聚合的模式统计信息与单次执行实例

时间序列分桶

**aggregated_ts：**按 UTC 划分的小时级时间桶（例如，2026-02-21 14:00:00 表示 14:00–14:59 的执行记录） **数据保留：**受行数限制，而非时间限制。sql.stats.persisted_rows.max（默认值为 1,000,000）限制持久化的语句和事务行数；达到上限后，较早的时间桶会被压缩。实际保留的时间范围取决于工作负载的多样性。 **最佳实践：**始终按时间窗口筛选：WHERE aggregated_ts > now() - INTERVAL '24 hours'

聚合指标与抽样指标

指标类别	JSON 路径	范围	用途
聚合	statistics.statistics.*	所有执行	延迟、行数、执行次数
抽样	statistics.execution_statistics.*	由 sql.txn_stats.sample_rate 控制的概率抽样（默认值为 0.01）	CPU、争用、准入等待、内存/磁盘

**重要提示：**始终检查是否存在抽样指标：WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL

JSON 字段提取

运算符：

• ->：提取 JSON 对象（返回 JSON）
• ->>：提取为文本（返回文本）
• ::TYPE：转换为指定类型

示例：

metadata->>'db'                                              -- Database name
(statistics->'statistics'->>'cnt')::INT                      -- Execution count
(statistics->'statistics'->'runLat'->>'mean')::FLOAT8        -- Mean latency (seconds)
(statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9  -- CPU (convert nanos to seconds)

**单位：**延迟 = 秒，CPU/准入等待 = 纳秒（÷ 1e9），内存/磁盘 = 字节（除以 1048576 得到 MB）

完整架构请参阅 JSON 字段参考。

核心诊断查询

查询 1：平均运行延迟最高的语句

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'query' AS query_text,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,
  (statistics->'statistics'->'runLat'->>'max')::FLOAT8 AS max_run_lat_seconds,
  (metadata->>'fullScan')::BOOL AS full_scan,
  metadata->'index_recommendations' AS index_recommendations,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 > 1.0  -- > 1 second mean latency
ORDER BY (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 DESC
LIMIT 20;

**重点：**找出最慢的查询；检查 full_scan 和 index_recommendations，寻找优化机会。

查询 2：准入控制的影响

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'query' AS query_text,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 / 1e9 AS mean_admission_wait_seconds,
  (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 > 0
ORDER BY (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 DESC
LIMIT 20;

**解读：**准入等待时间较长，说明集群已达到资源限制（CPU、内存、I/O）。比率 > 1.0（等待时间 > 运行时间）表示队列严重拥堵。

查询 3：计划哈希多样性

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'query' AS query_text,
  COUNT(DISTINCT plan_hash) AS distinct_plan_count,
  array_agg(DISTINCT plan_hash ORDER BY plan_hash) AS plan_hashes,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '7 days'
GROUP BY fingerprint_id, metadata->>'db', metadata->>'query'
HAVING COUNT(DISTINCT plan_hash) > 1
ORDER BY COUNT(DISTINCT plan_hash) DESC, SUM((statistics->'statistics'->>'cnt')::INT) DESC
LIMIT 20;

**解读：**存在多个计划，说明架构变更、统计信息更新或路由变更导致计划不稳定。不同计划之间的性能可能存在显著差异。

查询 4：争用严重的语句

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  substring(metadata->>'query', 1, 150) AS query_preview,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds,
  ROUND(
    ((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9) /
    NULLIF((statistics->'statistics'->'runLat'->>'mean')::FLOAT8, 0) * 100, 2
  ) AS contention_pct_of_runtime,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0
ORDER BY (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 DESC
LIMIT 20;

**解读：**争用率 >20% 表示存在事务冲突或热点行访问。可通过批处理、更改事务边界或重新设计架构来解决。

查询 5：CPU 消耗较高的语句

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  substring(metadata->>'query', 1, 150) AS query_preview,
  (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 AS mean_cpu_seconds,
  (statistics->'statistics'->>'cnt')::INT AS total_executions,
  ROUND(
    ((statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9) *
    (statistics->'statistics'->>'cnt')::INT, 2
  ) AS estimated_total_cpu_seconds,
  (metadata->>'fullScan')::BOOL AS full_scan,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 > 0
ORDER BY estimated_total_cpu_seconds DESC
LIMIT 20;

重点：estimated_total_cpu_seconds 显示对集群的影响。平均 CPU 消耗较高通常与 full_scan = true 相关。

查询 6：检测内存和磁盘溢出

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  substring(metadata->>'query', 1, 150) AS query_preview,
  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
  (statistics->'execution_statistics'->'maxMemUsage'->>'max')::FLOAT8 / 1048576 AS max_mem_mb,
  (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,
  (statistics->'execution_statistics'->'maxDiskUsage'->>'max')::FLOAT8 / 1048576 AS max_disk_mb,
  metadata->>'stmtType' AS statement_type,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0  -- Has disk spills
ORDER BY (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 DESC
LIMIT 20;

**解读：**磁盘使用量 > 0 表示发生内存溢出（速度比内存操作慢约 100-1000 倍）。大型聚合、排序和哈希连接中常见此情况。可通过添加索引或增大 sql.distsql.temp_storage.workmem 来解决。

查询 7：易出错语句

SELECT
  fingerprint_id,
  metadata->>'db' AS database,
  substring(metadata->>'query', 1, 150) AS query_preview,
  (statistics->'statistics'->>'cnt')::INT AS total_executions,
  COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) AS failure_count,
  ROUND(
    COALESCE((statistics->'statistics'->>'failureCount')::INT, 0)::NUMERIC /
    NULLIF((statistics->'statistics'->>'cnt')::INT, 0) * 100, 2
  ) AS failure_rate_pct,
  aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'cnt')::INT > 10
  AND COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) > 0
ORDER BY failure_rate_pct DESC, failure_count DESC
LIMIT 20;

**常见原因：**约束冲突、查询超时、事务重试错误（40001）、权限被拒绝。

常见工作流

工作流 1：排查运行缓慢问题

1. **识别较慢的指纹：**使用 24h 时间窗口运行查询 1，重点关注 mean_run_lat_seconds > 5 且执行次数较多的语句
2. **检查是否存在全表扫描：**筛选 full_scan = true，检查 index_recommendations
3. **关联应用：**按 metadata->>'app' 分组，联系具有特定模式的团队
4. **交叉参考实时活动：**如果问题仍在持续，使用 triaging-live-sql-activity 取消失控查询

工作流 2：分析争用

1. **查找争用严重的语句：**运行查询 4，重点关注 contention_pct_of_runtime > 20%
2. **检查计划稳定性：**针对存在争用的指纹运行查询 3（计划变更会影响锁顺序）
3. **解决问题：**批量处理操作、使用 SELECT FOR UPDATE、对热点表进行分区、对架构进行反规范化

工作流 3：调试准入控制

1. **识别准入等待：**运行查询 2，计算等待比率
2. **关联 CPU 使用情况：**对相同时间窗口运行查询 5，并交叉参考指纹 ID
3. **分析时间模式：**按 aggregated_ts 分组，找出高峰时段
4. **排查：**短期方案：错开批处理作业；长期方案：增加容量、优化查询

工作流 4：排查内存溢出

1. **查找发生溢出的语句：**运行查询 6，重点关注 max_disk_mb > 100
2. **分析模式：**识别大型 GROUP BY、ORDER BY 和哈希连接
3. **解决问题：**添加索引、增大 workmem（谨慎操作）、重写查询、使用物化视图

安全注意事项

**只读操作：**所有查询都是针对生产环境批准使用的 crdb_internal.statement_statistics 执行的 SELECT 语句。

性能影响：

注意事项	影响	缓解措施
大型表	语句多样性高时会产生许多行	始终使用时间筛选条件和 LIMIT
JSON 解析	CPU 开销	使用特定时间窗口，避免紧密循环
时间窗口过宽	7 天的查询会产生更多行	默认使用 24h；仅在需要时扩大时间窗口

**隐私：**在多租户环境中，使用 VIEWACTIVITYREDACTED 对查询常量进行脱敏。

故障排查

问题	原因	修复方法
结果为空	没有数据或已禁用统计信息收集	检查 sql.stats.automatic_collection.enabled = true
column does not exist	JSON 字段拼写错误或版本不匹配	验证字段名称；检查 CockroachDB 版本
采样指标中为 NULL	指标未在该时间桶中采样	筛选：WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL
查询文本显示 <hidden>	使用了 VIEWACTIVITYREDACTED	这是预期行为；如果获得授权，请使用 VIEWACTIVITY
"invalid input syntax for type json"	JSON 路径格式错误	检查运算符：-> 用于 JSON，->> 用于文本
查询非常慢	表很大，且没有时间筛选条件	始终添加时间范围和 LIMIT
index_recommendations 为空	没有建议或索引已达最优	如果索引已存在，这是正常现象

重要注意事项

• 时间范围： 默认为 24h；分析趋势时扩大到 7d
• 采样指标： 并非所有执行都会被捕获；请检查样本数量（cnt）
• JSON 安全性： 使用防御性 NULL 检查；处理类型转换错误
• 隐私： 在生产环境中使用 VIEWACTIVITYREDACTED
• 性能： 始终包含时间筛选条件和 LIMIT
• 实时排查的补充： 与实时排查结合使用，以实现完整覆盖（历史 + 实时）
• 数据保留： 受行数上限 sql.stats.persisted_rows.max（默认值为 1,000,000）约束，而非 TTL；实际时间范围会随工作负载的多样性而变化
• 计划不稳定： 多个计划哈希表示优化器或架构发生了变化

参考资料

技能参考：

• JSON 字段架构和提取
• 指标目录和单位
• SQL 查询变体
• RBAC 和权限（与 triaging-live-sql-activity 共用）

CockroachDB 官方文档：

• crdb_internal
• 语句页面（DB Console）
• 监控和分析事务争用
• VIEWACTIVITY 权限

相关技能：

• triaging-live-sql-activity - 用于立即排查当前正在运行的查询
• profiling-transaction-fingerprints - 用于事务级分析，包括重试模式和提交延迟