ClickHouseテーブルはかつて単一の主索引しか持つことができませんでした。

現在では、データを複製せずに主索引と同様の動作をする軽量な投影を介して、複数の索引を持つことができます。

簡単な概要を知りたい場合は、MarkがClickHouseで投影が二次索引として機能する方法を説明する動画をご覧ください：

https://www.youtube.com/watch?v=Fe6DqnWBs1I

投影が重要である理由

主索引はClickHouseがフィルター条件付きクエリを加速させるための核心メカニズムです。テーブルのソートキーの順序に従ってデータ行をディスクに書き込むことで、エンジンは関連するデータ範囲を迅速に特定できる疎な索引を維持します。しかし、この索引はテーブルのディスク上での物理的なソート順序に依存するため、各テーブルでは一つの主索引しか定義できませんでした。

主索引のフィルター条件とよく一致しないクエリを加速させるため、ClickHouseは投影を導入しました。投影とは、システムが自動的に維持し、ユーザーには透過的なテーブル構造のコピーで、異なるソート順序を採用しているため異なる主索引を持っています。これらの代替レイアウトは、これらのソート方式の恩恵を受けるクエリを大幅に加速できます。過去の主なコストはストレージコストにあり、投影は基テーブルのデータをディスク上に完全に複製していました。

二次索引としての軽量投影

しかし、25.6バージョンから、ClickHouseは機能的には二次索引と同等だが、完全なデータ行を複製しない、より軽量な投影を作成できるようになりました。この種の投影は完全なデータのコピーを保存するのではなく、自身のソートキーと基テーブル内の対応位置を指す_part_offsetポインタのみを保存し、ストレージオーバーヘッドを大幅に削減します。

適切なシナリオでは、ClickHouseはこの種の投影の主索引を二次索引として使用し、一致する行を特定します。実際の行データは引き続き基テーブルから読み取られます。複数の軽量投影を同時に有効にできるため、クエリに複数のフィルター条件が含まれている場合、すべての適用可能な投影を同時に利用できます。その中で特定のフィルター条件が基テーブル自体の主索引と一致する場合、その主索引も一緒に参加します。

partレベルの剪定からgranuleレベルの剪定へ

これまで、このメカニズムはpartレベルでのみ剪定が可能でした。つまり、データファイル全体の除外が可能でしたが、granuleレベルの細かい剪定はサポートしていませんでした。

今回のリリースでは、_part_offsetに基づく投影が本格的な二次索引へと進化し、granuleレベルの剪定をサポートするようになり、フィルタリングの粒度がより細かくなり、クエリパフォーマンスも大幅に向上しました。

例：複数の投影索引の組み合わせ使用

この点を説明するため、英国の不動産取引価格データセットを再度使用します。今回は、2つの軽量で_part_offsetに基づく投影を含むテーブルを定義しました：time_orderとlocation_order：

CREATE OR REPLACE TABLE property_sales.uk_property_data
(
    price UInt32,
    transaction_date Date,
    postal_code1 LowCardinality(String),
    postal_code2 LowCardinality(String),
    property_type Enum8(
      'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new_build UInt8,
    ownership_type Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    address_line1 String,
    address_line2 String,
    street LowCardinality(String),
    area LowCardinality(String),
    city LowCardinality(String),
    region LowCardinality(String),
    county LowCardinality(String),
    PROJECTION time_order (
        SELECT _part_offset ORDER BY transaction_date
    ),
    PROJECTION location_order (
        SELECT _part_offset ORDER BY city
    )
)
ENGINE = MergeTree
ORDER BY (postal_code1, postal_code2, address_line1, address_line2);

その後、ここで提供される説明（https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid）に従ってデータをロードします。

以下の図は、基テーブルとその2つの軽量で_part_offsetに基づく投影の構造を示しています：

① 基テーブルは(postal_code1, postal_code2, address_line1, address_line2)でソートされており、このソートは主索引を定義し、これらの列でフィルタリングされたクエリを非常に効率的にします。

② time_orderと③ location_order投影は、それぞれのソートキーと基テーブル内の行位置を指すための_part_offsetのみを保存し、データ複製を大幅に削減します。それらの主索引は基テーブルの二次索引として使用され、日付および/または都市でのフィルタリングクエリを加速します。

AWS m6i.8xlarge EC2インスタンス（32コア、128GBメモリ）でベンチマークを実行し、基盤ストレージはgp3 EBSボリューム（16k IOPS、最大スループット1000 MiB/s）を使用しました。

日付と都市列でフィルタリングするクエリを実行します。これらの列は基テーブルの主キーには含まれていないことに注意してください。

まず、投影サポートを無効にしてクエリを実行し、ベースラインパフォーマンスを取得します。同時に、クエリ条件キャッシュとPREWHEREを無効にし、完全に索引に基づくデータ剪定効果を分離します：

SELECT *
FROM property_sales.uk_property_data
WHERE (transaction_date = '2008-09-26') AND (city = 'BARNARD CASTLE')
FORMAT Null
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 0;

3回の実行中最速のものは0.077秒かかりました：

0 rows in set. Elapsed: 0.084 sec. Processed 30.73 million rows, 1.29 GB (363.92 million rows/s., 15.26 GB/s.)
Peak memory usage: 129.07 MiB.

0 rows in set. Elapsed: 0.076 sec. Processed 30.73 million rows, 1.29 GB (406.96 million rows/s., 17.07 GB/s.)
Peak memory usage: 129.29 MiB.

0 rows in set. Elapsed: 0.077 sec. Processed 30.73 million rows, 1.29 GB (398.51 million rows/s., 16.71 GB/s.)
Peak memory usage: 129.27 MiB.

これは全テーブルスキャン（約3000万行）であることがわかります。

次に、投影サポートを有効にしてクエリを再度実行します：

SELECT *
FROM property_sales.uk_property_data
WHERE (transaction_date = '2008-09-26') AND (city = 'BARNARD CASTLE')
FORMAT Null
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 1; -- default value

3回の実行中最速のものはわずか0.010秒かかりました：

0 rows in set. Elapsed: 0.010 sec. Processed 16.38 thousand rows, 644.86 KB (1.60 million rows/s., 63.06 MB/s.)
Peak memory usage: 4.89 MiB.

0 rows in set. Elapsed: 0.010 sec. Processed 16.38 thousand rows, 644.86 KB (1.69 million rows/s., 66.36 MB/s.)
Peak memory usage: 4.88 MiB.

0 rows in set. Elapsed: 0.011 sec. Processed 16.38 thousand rows, 644.86 KB (1.54 million rows/s., 60.57 MB/s.)
Peak memory usage: 4.89 MiB.

最終結果：0.077秒対0.010秒で、クエリ実行時間は約90%短縮されました。

同時に、今回実際にスキャンされた行数は約1.6万行であり、すべての約3000万行ではありませんでした。

EXPLAINを使用すると、ClickHouseが2つの投影の主索引を二次索引として使用し、基テーブルのgranuleを剪定していることが観察できます：

EXPLAIN projections = 1
SELECT *
FROM property_sales.uk_property_data
WHERE (transaction_date = '2008-09-26') AND (city = 'BARNARD CASTLE')
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 1; -- default value

┌─explain────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                          │
 2. │   Filter ((WHERE + Change column names to column identifiers))     │
 3. │     ReadFromMergeTree (property_sales.uk_property_data)            │
 4. │     Projections:                                                   │
 5. │       Name: time_order                                             │
 6. │         Description: Projection has been analyzed...               │
 7. │         Condition: (transaction_date in [14148, 14148])          │
 8. │         Search Algorithm: binary search                            │
 9. │         Parts: 5                                                   │
10. │         Marks: 7                                                   │
11. │         Ranges: 5                                                  │
12. │         Rows: 57344                                                │
13. │         Filtered Parts: 0                                          │
14. │       Name: location_order                                         │
15. │         Description: Projection has been analyzed...               │
16. │         Condition: (city in ['BARNARD CASTLE', 'BARNARD CASTLE'])  │
17. │         Search Algorithm: binary search                            │
18. │         Parts: 5                                                   │
19. │         Marks: 5                                                   │
20. │         Ranges: 5                                                  │
21. │         Rows: 40960                                                │
22. │         Filtered Parts: 0                                          │
    └────────────────────────────────────────────────────────────────────┘

EXPLAIN出力の第10行で、time_order投影（より正確にはその主索引）が最初に検索範囲を7つのgranule（つまり「Marks」）に絞り込んでいることがわかります。各granuleが8,192行のデータを含んでいるため、7 × 8192 = 57,344行をスキャンする必要があることを意味します（第12行参照）。これらの7つのgranuleは5つのデータpartに分散しているため（第9行）、エンジンは5つの対応するデータ範囲を読み取る必要があります（第11行）。

次に、第14行からlocation_order投影の主索引が適用されます。time_order投影から選択された7つのgranuleからさらに2つを除外します。最終的に、クエリは5つのgranuleのみをスキャンする必要があり、これらのgranuleは基テーブルの5つのpart内に5つのデータ範囲に存在します。なぜなら、これらのgranuleには日付と都市の両方のフィルター条件を満たす可能性のある行が含まれているためです。

この最適化プロセスを制御するために、2つの新しい設定パラメータが導入されました：

• max_projection_rows_to_use_projection_index：投影から推定される読み取り行数がこの値以下の場合、投影索引の使用を許可します。
• min_table_rows_to_use_projection_index：基テーブルから推定される読み取り行数がこの値以上の場合のみ、投影索引の使用が検討されます。

主要な結論

ClickHouseテーブルはかつて単一の主索引しか持つことができませんでした。

現在では、各々が主索引レベルの能力を持つ複数の索引を持つことができます。クエリに複数のフィルター条件が含まれている場合、ClickHouseはこれらの索引を同時に利用します。