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）、パーツと主キーのメモリー使用量の集計を含む主キーのカラム（index-analysis-06）、スキップインデックスのサイズとカラムサイズの比較（index-analysis-07）、主キーを迂回するクエリ（index-analysis-08）、パーティションプルーニングの有効性（index-analysis-09）、テーブルごとの WHERE 条件パターン（index-analysis-10）。
• reference.md — 背景情報（ORDER BY の設計ガイドライン、アンチパターン、スキップインデックスの選定）。推奨事項の説明が必要な場合にのみ読みます。

クエリパックの実行方法

1. このスキルのディレクトリにある各パックファイルを読みます（スキルローダーがディレクトリのパスを出力します）。
2. ステートメントはファイル全体ではなく、1 つずつ実行します。ステートメントは ; で終わり、-- @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前後、または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;

1つのテーブルで、実際にフィルターされている列を使用頻度順に示します。

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;

1つのテーブルに関係するクエリのグラニュール選択性です。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の列を持つテーブル。問題1件につき1行とし、Criticalを先頭にします。Evidenceには結果行の数値を引用します。
3. 問題のないチェック：問題行がなかったチェックIDを1行で列挙します。
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も読み込みます