主キーが使われないのはなぜですか？どうすれば確認できますか？

主キーの確認

主キーで並べ替えや絞り込みをしているつもりでも、クエリが想定より遅いことがあります。この記事では、主キーが実際に使われているかを確認する方法と、使われていない主な理由を紹介します。

テーブルの作成

次のようなシンプルなテーブルを考えます。

CREATE TABLE logs
(
    `code` LowCardinality(String),
    `timestamp` DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (code, toUnixTimestamp(timestamp))

ソートキーの2番目のエントリに toUnixTimestamp(timestamp) が含まれている点に注目してください。

データを投入する

このテーブルに1億行のデータを投入します:

INSERT INTO logs SELECT
 ['200', '404', '502', '403'][toInt32(randBinomial(4, 0.1)) + 1] AS code,
    now() + toIntervalMinute(number) AS timestamp
FROM numbers(100000000)

0 rows in set. Elapsed: 15.845 sec. Processed 100.00 million rows, 800.00 MB (6.31 million rows/s., 50.49 MB/s.)

SELECT count()
FROM logs

┌

基本的なフィルタリング

codeでフィルタすると、出力にスキャンされた行数 (49.15 thousand) が表示されます。これは、合計1億行の一部にすぎないことがわかります。

SELECT count() AS c
FROM logs
WHERE code = '200'

┌

さらに、EXPLAIN indexes=1 句を使って、索引が使用されていることを確認できます:

EXPLAIN indexes = 1
SELECT count() AS c
FROM logs
WHERE code = '200'

┌

スキャン対象となったグラニュール数 8012 が、合計 12209 の一部にすぎないことに注目してください。以下でハイライトされているセクションは、主キーが使用されていることを示しています。

PrimaryKey
  Keys: 
   code

グラニュールは ClickHouse におけるデータ処理の単位で、通常はそれぞれ 8192 行を含みます。グラニュールとその絞り込み方法の詳細については、こちらのガイドを参照することをお勧めします。

複数キーでのフィルタリング

code と timestamp を条件にフィルタリングするとします:

SELECT count()
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')

┌

この場合、2つのソートキーの両方が行の絞り込みに使われるため、読み取る必要があるグラニュールは 87 個のみです。

ソートでキーを使用する

ClickHouse は、効率的なソートのために順序キーを活用することもできます。具体的には、

optimize_read_in_order 設定が有効な場合 (デフォルト) 、ClickHouseサーバーはテーブルの索引を使用し、ORDER BY キーの順序でデータを読み取ります。これにより、LIMIT が指定されている場合でも、すべてのデータを読み取らずに済みます。そのため、大規模データに対する小さな LIMIT 付きのクエリは、より高速に処理されます。詳しくは、こちらおよびこちらを参照してください。

ただし、これには使用するキーが一致している必要があります。

たとえば、次のクエリを考えてみましょう。

SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

ここでは、EXPLAIN pipeline を使って、この最適化が適用されていないことを確認できます。

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

ここでの MergeTreeSelect(pool: ReadPool, algorithm: Thread) という行は、この最適化が使われていることを示すものではなく、通常の読み取りであることを示しています。これは、テーブルのソートキーに timestamp ではなく toUnixTimestamp(Timestamp) を使っているためです。この不一致を修正すれば、問題は解消されます。

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY toUnixTimestamp(timestamp) ASC
LIMIT 10

┌