altinity-expert-clickhouse-index-analysis

索引有效性分析

通过 system.data_skipping_indices、system.projections、system.tables、system.parts 和 system.query_log 中的 ProfileEvents，回答“ORDER BY 键、跳数索引和投影是否与查询实际过滤的内容相匹配？”。 如果尚未确定连接模式、集群和时间窗口，请先运行 altinity-expert-clickhouse-connection。

查询包

• checks.sql — 10 项检查：跳数索引清单（index-analysis-01）、投影及其 ORDER BY 键（index-analysis-02，需要 system.projections）、按查询频率排列的表（index-analysis-03）、粒度选择性较差的查询（index-analysis-04）、经常用于过滤的列（index-analysis-05）、包含 part 和 PK 内存聚合的主键列（index-analysis-06）、跳数索引大小与列大小对比（index-analysis-07）、绕过主键的查询（index-analysis-08）、分区裁剪有效性（index-analysis-09）、每个表的 WHERE 条件模式（index-analysis-10）。
• reference.md — 背景资料（ORDER BY 设计指南、反模式、跳数索引选择）；仅在需要解释建议时阅读。

如何运行查询包

1. 从此技能的目录中读取每个查询包文件（技能加载器会打印目录路径）。
2. 一次运行一条语句，切勿运行整个文件。语句以 ; 结尾，并以 -- @check <id> <title> 标题开头；将 id 与结果一并保留。
3. 遵守 -- @requires：当指定表不存在、需要 keeper 但服务器没有 Keeper/ZooKeeper，或不满足版本条件时，跳过该语句。列出跳过的 id 及原因。
4. 如果存在集群宏，请按原样保留 {cluster}；否则应用连接技能的改写规则。其他任何 {placeholder} 都是模板变量：先替换为实际值，否则跳过该语句。
5. 出错时，记录检查 id 和错误的第一行，然后继续。仅当错误为 UNKNOWN_IDENTIFIER 时，运行 DESCRIBE TABLE system.<table> 并移除缺失列。
6. severity 列是该行的判定结果。照抄，不要重新评级。

解读规则

针对每个可疑表，按以下顺序处理：读取其键和索引（index-analysis-01、index-analysis-06，以及 SHOW CREATE TABLE），提取查询实际筛选的列（index-analysis-05、index-analysis-10），测量这些列的基数，然后判断是否匹配。

• 如果 pct_marks_selected 接近 100 且读放大率很高，就说明主键完全没有起到筛选作用，不管 ORDER BY 看起来如何。
• 按查询模式判断匹配情况：对 ORDER BY 前缀中的列进行筛选时，现有排序已能支持；筛选 ORDER BY 中没有的列时，可考虑使用跳数索引或投影；同时筛选时间范围和实体时，需要检查排序，因为时间列可能应该放在分区键中，而不是放在 ORDER BY 的首位；高基数列若排在 ORDER BY 首位，会导致无法跳过数据粒度，应调整列顺序。
• 按基数从低到高排列 ORDER BY 列，并将实际用于筛选的列保留在键中。时间列通常适合使用降低精度的形式，例如 toDate(ts)，除非分区键已经负责处理时间筛选。
• 只有当某列不在 ORDER BY 前缀中，且其取值与物理行顺序相关时，跳数索引才有帮助。对于随机分布的取值，它会先读取索引，然后仍然读取所有数据；对于已经是 ORDER BY 前缀的列，跳数索引则完全多余。
• 在 index-analysis-07 中，若 index_to_column_pct 接近或超过 100，说明索引的成本与其索引的列相当；除非已证实它能跳过数据粒度，否则应将其删除。同样的成本权衡也适用于投影，因为投影会复制其覆盖的数据：只有当 index-analysis-03 显示该模式足够常见，值得承担存储和合并成本时，才建议使用投影。
• 分区裁剪（index-analysis-09）与主键是不同的优化手段。如果 ORDER BY 设计良好但分区裁剪效果不佳，应检查分区键，或检查查询是否在筛选分区裁剪器无法使用的派生表达式。
• 在 EXPLAIN indexes = 1 输出中，PrimaryKey 条件为 true 表示没有发生筛选；Granules: X/Y 比值才代表实际选择性（X 相对 Y 越小越好）；Skip 步骤应在主键筛选的基础上，进一步减少分区片段和数据粒度。

深入分析语句

候选键列的基数。将 {columns} 作为逗号分隔的列表传入；基数最低的列应排在 ORDER BY 首位。

SELECT {columns} APPLY uniq
FROM {database}.{table}
WHERE {time_column} > now() - INTERVAL {days} DAY;

按使用频率排序，列出一个表中实际被筛选的列。

WITH
    arrayJoin(extractAll(query, '\\b(?:PRE)?WHERE\\s+(.*?)\\s+(?:GROUP BY|ORDER BY|UNION|SETTINGS|FORMAT|$)')) AS w,
    arrayFilter(x -> (position(lower(w), lower(extract(x, '\\.(`[^`]+`|[^\\.]+)$'))) > 0), columns) AS c,
    arrayJoin(c) AS filtered_column
SELECT filtered_column, count() AS usage_count
FROM system.query_log
WHERE event_time >= now() - INTERVAL {days} DAY
  AND type = 'QueryFinish'
  AND query ILIKE 'SELECT%'
  AND arrayExists(x -> x LIKE '%{table}%', tables)
GROUP BY filtered_column
ORDER BY usage_count DESC
LIMIT 30;

访问某个表的查询所对应的数据粒度选择性。pct_marks_selected 表示读取的数据标记占该表全部标记的比例。

SELECT
    normalized_query_hash,
    any(query) AS sample_query,
    round(avg(ProfileEvents['SelectedParts'])) AS avg_parts,
    round(avg(ProfileEvents['SelectedMarks'])) AS avg_marks,
    round(100.0 * sum(ProfileEvents['SelectedMarks']) / nullIf(sum(ProfileEvents['SelectedMarksTotal']), 0), 1) AS pct_marks_selected,
    round(avg(read_rows)) AS avg_read_rows,
    round(avg(query_duration_ms)) AS avg_duration_ms
FROM system.query_log
WHERE event_time >= now() - INTERVAL {days} DAY
  AND type = 'QueryFinish'
  AND query ILIKE 'SELECT%'
  AND arrayExists(x -> x LIKE '%{table}%', tables)
GROUP BY normalized_query_hash
ORDER BY avg_marks DESC
LIMIT 20;

报告格式

1. 标题：连接模式、集群或“单节点”、ClickHouse 版本、时间窗口。
2. 发现：使用包含 check、severity、object、evidence、recommendation 列的表格；每个发现占一行，按严重程度排列，Critical 优先。证据中应引用结果行中的数值。
3. 正常检查：用一行列出未返回问题行的检查 ID。
4. 已跳过和失败的检查：列出 ID 和原因，或首行错误信息。此部分不得省略。
5. 后续步骤：列出接下来要加载的技能和立即采取的行动。

后续技能

• 需要重新设计 ORDER BY、分区键或物化视图链 → 加载技能 altinity-expert-clickhouse-schema
• 完成索引工作后查询仍然很慢，或成本来自执行计划的其他部分 → 加载技能 altinity-expert-clickhouse-reporting
• 主键或标记缓存占用过大，或缓存命中率较低 → 加载技能 altinity-expert-clickhouse-memory
• 新的 ORDER BY 或投影需要重写现有数据 → 加载技能 altinity-expert-clickhouse-mutations；若还需处理额外的合并成本，则加载 altinity-expert-clickhouse-merges