ClickHouse プロジェクション機能で集計クエリを高速化する

# ClickHouse プロジェクション機能

ClickHouse プロジェクション機能が、バージョン 22.2 で production ready となりました。

この機能を使うと、SELECTクエリを書き換えることなく、集計等のクエリを高速化することができます。
今までは、集計を行うマテリアライズド・ビューを作成し、そちらをSELECTするようにクエリを書き換える必要がありました。

プロジェクションを使うと、実テーブルに集計結果をあわせて格納することができ、SELECTクエリも自動的に集計済みのデータを使うようになり、高速化されます。


## ダミーデータ準備

ページを閲覧したログを記録しておき、ページ別の閲覧数を集計する想定でサンプルデータを作ります。
簡単なテーブルなので1000万レコードほど用意します。

```sql
CREATE TABLE site_view (
  event_time DateTime,
  path       String
)
ENGINE=MergeTree
ORDER BY event_time;
```

```sql
INSERT INTO site_view
SELECT
  toDateTime('2022-01-01 00:00:00') + INTERVAL rand() % (number+1) SECOND AS event_time,
  concat('/page/', toString(rand() % 100), '.html') path
FROM system.numbers
LIMIT 10000000;
```

## 集計

ログデータをそのまま集計するとこのようになります。

```sql
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
```

```sql
SELECT path, count()
FROM site_view
WHERE toDate(event_time) = '2022-01-01'
GROUP BY path
ORDER BY count() DESC;
```
なんどか実行すると、全件では 0.130 sec. 前後、1日分では 0.013 sec. くらいでした。

実行計画は以下のようになります。

```sql
EXPLAIN
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;

┌─explain───────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                           │
│   Sorting (Sorting for ORDER BY)                                                  │
│     Expression (Before ORDER BY)                                                  │
│       Aggregating                                                                 │
│         Expression (Before GROUP BY)                                              │
│           SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│             ReadFromMergeTree                                                     │
└───────────────────────────────────────────────────────────────────────────────────┘
```

## プロジェクションを作成する

このテーブルに対して、プロジェクションで集計を高速化します。
toDate(event_time)、path 単位で集計を行います。

```sql
ALTER TABLE site_view
ADD PROJECTION site_view_by_path (
SELECT
  toDate(event_time) event_date,
  path,
  count() view
GROUP BY
  event_date,
  path
);
```

PROJECTIONを作ると今後追加されるデータについて集計データが用意されますが、既存のデータについては用意されません。
既存データ分は次のようにして作成します。

```sql
ALTER TABLE site_view
  MATERIALIZE PROJECTION site_view_by_path 
SETTINGS mutations_sync = 1;
```

手元の環境では 0.649 sec. ほどで処理が完了しました。

## プロジェクションによる高速化

最初と同じクエリを再度実行してみます。

```sql
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
```

こちらのクエリは 0.005 sec. くらいになりました。
以前が 0.130 sec. ですので、かなり高速化できました。

```sql
SELECT path, count()
FROM site_view
WHERE toDate(event_time) = '2022-01-01'
GROUP BY path
ORDER BY count() DESC;
```

こちらは 0.006 sec. くらいになりました。
以前が 0.013 sec. なので、対象となるデータが少なければ効果は低めになるようです。

EXPLAINで実行計画を確認すると、projection が使用されていることを確認できます。

```sql
EXPLAIN
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;

┌─explain──────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                          │
│   Sorting (Sorting for ORDER BY)                                                 │
│     Expression (Before ORDER BY)                                                 │
│       SettingQuotaAndLimits (Set limits and quota after reading from storage)    │
│         ReadFromStorage (MergeTree(with Aggregate projection site_view_by_path)) │
└──────────────────────────────────────────────────────────────────────────────────┘
```

## クエリの書き換え

プロジェクションはクエリの自動書き換えによって高速化するため、クエリの指定の仕方は少し注意が必要です。
例えば 2022-01-01 のみのクエリを WHERE toDate(event_time) = '2022-01-01' ではなく、以下のように指定するとプロジェクションは使われません。

```sql
EXPLAIN
SELECT path, count()
FROM site_view
WHERE event_time >= '2022-01-01 00:00:00' AND event_time < '2022-01-02 00:00:00'
GROUP BY path
ORDER BY count() DESC;

┌─explain─────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                             │
│   Sorting (Sorting for ORDER BY)                                                    │
│     Expression (Before ORDER BY)                                                    │
│       Aggregating                                                                   │
│         Expression (Before GROUP BY)                                                │
│           Filter (WHERE)                                                            │
│             SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│               ReadFromMergeTree                                                     │
└─────────────────────────────────────────────────────────────────────────────────────┘
```

## マテリアライズド・ビューとの差

プロジェクションは、元のテーブルのパート単位で、集計結果を保持します。
このため、パーティションが細かく別れていたり、パート数が多い場合は、マテリアライズド・ビューに比べて若干性能が落ちるかもしれません。