メインコンテンツへスキップ
メインコンテンツへスキップ

テキストインデックスによる全文検索

テキストインデックス（inverted indexes とも呼ばれます）は、テキストデータに対する高速な全文検索を可能にします。 テキストインデックスは、トークンから、それぞれのトークンを含む行番号への対応関係を格納します。 トークンは、トークナイゼーションと呼ばれる処理によって生成されます。 たとえば、ClickHouse のデフォルトトークナイザーは、英語の文 "The cat likes mice." をトークン ["The", "cat", "likes", "mice"] に変換します。

例として、1 つのカラムと 3 行を持つテーブルを考えます。

1: The cat likes mice.
2: Mice are afraid of dogs.
3: I have two dogs and a cat.

対応するトークンは以下のとおりです：

1: The, cat, likes, mice
2: Mice, are, afraid, of, dogs
3: I, have, two, dogs, and, a, cat

通常、検索は大文字小文字を区別しない形で行うため、トークンを小文字化します。

1: the, cat, likes, mice
2: mice, are, afraid, of, dogs
3: i, have, two, dogs, and, a, cat

また、「I」「the」「and」などの、ほぼすべての行に出現する語も削除します。

1: cat, likes, mice
2: mice, afraid, dogs
3: have, two, dogs, cat

（概念的には）テキスト索引には次のような情報が含まれます。

afraid : [2]
cat    : [1, 3]
dogs   : [2, 3]
have   : [3]
likes  : [1]
mice   : [1]
two    : [3]

検索トークンが与えられると、この索引構造によって一致するすべての行を高速に検索できます。

テキスト索引の作成​

テキスト索引は ClickHouse バージョン 26.2 以降で一般提供 (GA) されています。 これらのバージョンでは、テキスト索引を使用するために特別な設定は不要です。 本番環境では ClickHouse バージョン 26.2 以上の使用を強く推奨します。

注記

テキスト索引は、compatibility 設定に関係なく、ClickHouse バージョン >= 26.2 であれば使用できます。

テキスト索引を作成するには、次の構文を使用します。

CREATE TABLE table
(
    key UInt64,
    str String,
    INDEX text_idx(str) TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | asciiCJK
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )
)
ENGINE = MergeTree
ORDER BY key

テキスト索引は、次の型のカラムに定義できます。

Nullable(T) 型および LowCardinality() 型のカラムもサポートされており、Array(Nullable(String または FixedString)) も含まれます。

別の方法として、既存のテーブルにテキスト索引を追加するには、次のようにします：

ALTER TABLE table
    ADD INDEX text_idx(str) TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | asciiCJK
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )

既存のテーブルに索引を追加する場合、既存テーブルのパーツに対してその索引をマテリアライズすることを推奨します (そうしないと、索引のないパーツでの検索は低速な総当たりスキャンにフォールバックしてしまいます) 。

ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;

テキスト索引を削除するには、次のコマンドを実行します。

ALTER TABLE table DROP INDEX text_idx;

トークナイザー 引数 (必須) 。tokenizer 引数でトークナイザーを指定します。

  • splitByNonAlpha は、英数字ではない ASCII 文字で文字列を分割します (関数 splitByNonAlpha を参照) 。
  • splitByString(S) は、ユーザー定義のセパレーター文字列 S で文字列を分割します (関数 splitByString を参照) 。 セパレーターはオプション引数で指定できます。たとえば tokenizer = splitByString([', ', '; ', '\n', '\\']) のように指定します。 各セパレーター文字列は複数文字から構成できる点に注意してください (この例では ', ') 。 セパレーターリストを明示的に指定しない場合 (たとえば tokenizer = splitByString) 、デフォルトのセパレーターリストは空白 1 文字 [' '] です。
  • asciiCJK は、Unicode の単語境界規則 (Unicode Text Segmentation (UAX #29) に類似) を使用して文字列をトークンに分割します。ASCII の英数字とアンダースコアは、コネクタ (文字には ASCII :、同種の文字には . と ') とともにトークンを構成します。CJK 文字を含む非 ASCII の Unicode 文字は 1 文字のトークンになります。
  • ngrams(N) は、文字列を同じ長さの N-gram に分割します (関数 ngrams を参照) 。 N-gram の長さは 1 から 8 までの整数をオプション引数として指定できます。たとえば tokenizer = ngrams(3) のように指定します。 N-gram のサイズを明示的に指定しない場合 (たとえば tokenizer = ngrams) 、デフォルトのサイズは 3 です。
  • sparseGrams(min_length, max_length, min_cutoff_length) は、min_length 以上 max_length 以下 (両端を含む) の長さを持つ可変長の n-gram に文字列を分割します (関数 sparseGrams を参照) 。 min_length と max_length は、明示的に指定しない場合はそれぞれ 3 と 100 がデフォルト値です。 パラメータ min_cutoff_length を指定すると、長さが min_cutoff_length 以上の n-gram のみが返されます。 ngrams(N) と比較して、sparseGrams トークナイザーは可変長の N-gram を生成するため、元のテキストをより柔軟に表現できます。 たとえば、tokenizer = sparseGrams(3, 5, 4) は内部的には入力文字列から 3-, 4-, 5-gram を生成しますが、返されるのは 4-gram と 5-gram のみです。
  • array はトークナイズ処理を行いません。つまり、各行の値全体が 1 つのトークンになります (関数 array を参照) 。

利用可能なすべてのトークナイザーは system.tokenizers に一覧表示されています。

注記

splitByString トークナイザーは、左から右へ順にセパレーターを適用します。 これにより曖昧さが生じる場合があります。 たとえば、セパレーター文字列を ['%21', '%'] と指定すると、%21abc は ['abc'] にトークナイズされますが、両方のセパレーター文字列を入れ替えて ['%', '%21'] とすると、出力は ['21abc'] になります。 多くの場合、より長いセパレーターが優先的にマッチすることが望ましいです。 これは一般に、セパレーター文字列を長さの降順で指定することで実現できます。 セパレーター文字列が prefix code を構成している場合は、任意の順序で指定できます。

トークナイザーが入力文字列をどのように分割したかを確認するには、tokens 関数と tokensForLikePattern 関数を使用できます。

例:

SELECT tokens('abc def', 'ngrams', 3);

結果：

['abc','bc ','c d',' de','def']

非 ASCII 入力の扱い。 テキスト索引は、任意の言語や文字セットのテキストデータに対して構築できます。 非 ASCII テキストについては、CJK 文字を含む Unicode の単語境界を正しく処理できる asciiCJK トークナイザーの使用を推奨します。 :::

プリプロセッサ 引数 (オプション)。プリプロセッサとは、トークナイズの前に入力文字列に適用される式を指します。

プリプロセッサ 引数の典型的なユースケースには次のようなものがあります。

  1. 小文字化/大文字化、またはケースフォールディングを行い、大文字小文字を区別しないマッチングを有効にします。例: lower、lowerUTF8、caseFoldUTF8。
  2. UTF-8 正規化。例: normalizeUTF8NFC、normalizeUTF8NFD、normalizeUTF8NFKC、normalizeUTF8NFKD、normalizeUTF8NFKCCasefold、toValidUTF8。
  3. アクセント記号などの不要な文字や部分文字列の削除または変換。例: extractTextFromHTML、substring、idnaEncode、translate、removeDiacriticsUTF8。

preprocessor 式は、String 型または FixedString 型の入力値を、同じ型の値に変換しなければなりません。 テキストインデックスが Nullable(T) 型または LowCardinality(T) 型のカラム上に作成されている場合、preprocessor 式は Nullable または LowCardinality の値も受け付ける必要があります (つまり、例外をスローしてはなりません) 。

例:

  • INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))
  • INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))
  • INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col)))
  • INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = removeDiacriticsUTF8(caseFoldUTF8(col)))

また、preprocessor 式は、テキストインデックスが定義されているカラムまたは式のみを参照しなければなりません。

例:

  • INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = upper(lower(col)))
  • INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(lower(col), lower(col)))
  • 許可されない例: INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))

非決定的関数の使用は許可されていません。

関数 hasToken、hasAllTokens、hasAnyTokens は、トークン化する前に検索語句を変換するために preprocessor を使用します。

例えば、

CREATE TABLE table
(
    str String,
    INDEX idx(str) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, 'Foo');

は以下と同等です：

CREATE TABLE table
(
    str String,
    INDEX idx(lower(str)) TYPE text(tokenizer = 'splitByNonAlpha')
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, lower('Foo'));

この場合、プリプロセッサ式は配列の各要素を個別に変換します。

例：

CREATE TABLE table
(
    arr Array(String),
    INDEX idx arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(arr))

    -- This is not legal:
    INDEX idx_illegal arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = arraySort(arr))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(arr, 'foo');

Map 型カラム上のテキスト索引用プリプロセッサを定義するには、索引を Map のキーに対して作成するか、値に対して作成するかを決める必要があります。

例：

CREATE TABLE table
(
    map Map(String, String),
    INDEX idx mapKeys(map)  TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(mapKeys(map)))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(mapKeys(map), 'foo');

その他の引数 (オプション) 。

オプションの高度なパラメータ

以下の高度なパラメータのデフォルト値は、ほぼすべての状況で適切に機能します。 これらを変更することは推奨しません。

オプションのパラメータ dictionary_block_size (デフォルト: 512) は、Dictionary ブロックのサイズを行数で指定します。

オプションのパラメータ dictionary_block_frontcoding_compression (デフォルト: 1) は、Dictionary ブロックで圧縮方式として front coding を使用するかどうかを指定します。

オプションのパラメータ posting_list_block_size (デフォルト: 1048576) は、posting list ブロックのサイズを行数で指定します。

オプションのパラメータ posting_list_codec (デフォルト: none) は、posting list のコーデックを指定します:

  • none - posting list を追加の圧縮なしで保存します。
  • bitpacking - 差分 (デルタ) 符号化 を適用し、その後に bit-packing を適用します (いずれも固定サイズのブロックごと)。SELECT クエリを低速化するため、現時点では推奨されません。

索引の粒度。 テキスト索引は、ClickHouse 内では skip indexes の一種として実装されています。 ただし、他の skip 索引とは異なり、テキスト索引は無限の粒度 (1 億) を使用します。 これはテキスト索引のテーブル定義で確認できます。

例:

CREATE TABLE table(
    k UInt64,
    s String,
    INDEX idx(s) TYPE text(tokenizer = ngrams(2)))
ENGINE = MergeTree()
ORDER BY k;

SHOW CREATE TABLE table;

結果:

┌─statement──────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.table                                            ↴│
│↳(                                                                     ↴│
│↳    `k` UInt64,                                                       ↴│
│↳    `s` String,                                                       ↴│
│↳    INDEX idx s TYPE text(tokenizer = ngrams(2)) GRANULARITY 100000000↴│ <-- here
│↳)                                                                     ↴│
│↳ENGINE = MergeTree                                                    ↴│
│↳ORDER BY k                                                            ↴│
│↳SETTINGS index_granularity = 8192                                      │
└────────────────────────────────────────────────────────────────────────┘

非常に大きな索引の粒度により、テキスト索引はパーツ全体に対して作成されます。 明示的に指定された索引の粒度は無視されます。

テキスト索引の使用​

SELECT クエリでテキスト索引を利用するのは容易で、一般的な文字列検索関数は自動的にその索引を活用します。 カラムまたはテーブルパーツに索引が存在しない場合、文字列検索関数は低速な総当たりスキャンにフォールバックします。

注記

テキスト索引の検索には hasAnyTokens と hasAllTokens 関数の使用を推奨します。詳しくは下記を参照してください。 これらの関数は、利用可能なすべてのトークナイザーおよびあらゆるプリプロセッサ式と連携して動作します。 他の対応関数は、歴史的にテキスト索引より先に存在していたため、多くの場合で従来の動作（例: プリプロセッサの未対応）を維持する必要があります。

サポートされている関数​

WHERE 句または PREWHERE 句でテキスト関数が使用されている場合、テキストインデックスを使用できます。

SELECT [...]
FROM [...]
WHERE string_search_function(column_with_text_index)

=​

= (equals) は、指定された検索語全体にマッチします。

例:

SELECT * from table WHERE str = 'Hello';

IN​

IN (in) は equals 関数と似ていますが、すべての検索語にマッチします。

例:

SELECT * from table WHERE str IN ('Hello', 'World');
注記

NOT IN (notIn) はテキスト索引ではサポートされていません。

LIKE および match​

注記

これらの関数がテキスト索引をフィルタリングに利用するのは、インデックスの トークナイザー が splitByNonAlpha、ngrams、または sparseGrams のいずれかである場合に限られます。

注記

NOT LIKE (notLike) はテキスト索引ではサポートされません。

テキスト索引で LIKE (like) および match 関数を使用するには、ClickHouse が検索語から完全なトークンを抽出できる必要があります。 ngrams トークナイザー を持つインデックスでは、ワイルドカードの間にある検索文字列の長さが ngram の長さ以上であれば、この条件を満たします。

splitByNonAlpha トークナイザー を持つテキスト索引の例:

SELECT count() FROM table WHERE comment LIKE 'support%';

support の例では、support、supports、supporting などにマッチし得ます。 この種のクエリは部分文字列クエリであり、テキスト索引によって高速化することはできません。

LIKE クエリでテキスト索引を活用するには、LIKE パターンを次のように書き換える必要があります。

SELECT count() FROM table WHERE comment LIKE ' support %'; -- or `% support %`

support の左右に空白を入れておくことで、その語をトークンとして抽出できるようにします。

幸い、ClickHouse が転置索引を利用して LIKE クエリを大幅に高速化できる特別なケースがあります。

詳しくは、LIKE/ILIKE パフォーマンスチューニングのセクションを参照してください。

startsWith と endsWith​

LIKE と同様に、関数 startsWith と endsWith は、検索語から完全なトークンを抽出できる場合にのみテキストインデックスを使用できます。 ngrams トークナイザーを使用するインデックスでは、ワイルドカードの間にある検索文字列の長さが ngram の長さ以上である場合に、この条件を満たします。

splitByNonAlpha トークナイザーを用いたテキストインデックスの例:

SELECT count() FROM table WHERE startsWith(comment, 'clickhouse support');

この例では、clickhouse だけがトークンとして扱われます。 support は support、supports、supporting などにマッチする可能性があるため、トークンとは見なされません。

clickhouse supports で始まるすべての行を検索するには、検索パターンの末尾にスペースを付けてください。

startsWith(comment, 'clickhouse supports ')`

同様に、endsWith を使用する場合は、先頭にスペース（空白）を付けてください。

SELECT count() FROM table WHERE endsWith(comment, ' olap engine');

hasToken および hasTokenOrNull​

注記

関数 hasToken は一見すると扱いやすそうに見えますが、デフォルト以外のトークナイザや preprocessor 式を使用する場合にいくつかの落とし穴があります。 代わりに関数 hasAnyTokens および hasAllTokens を使用することを推奨します。

関数 hasToken と hasTokenOrNull は、指定された 1 つのトークンとの照合を行います。

前述の関数とは異なり、検索語句をトークン化しません（入力が 1 つのトークンであることを前提とします）。

例:

SELECT count() FROM table WHERE hasToken(comment, 'clickhouse');

hasAnyTokens と hasAllTokens​

関数 hasAnyTokens と hasAllTokens は、指定されたトークンのいずれか、またはすべてにマッチします。

これら 2 つの関数では、検索トークンは、索引用カラムに対して使用されているものと同じトークナイザでトークナイズされる文字列として指定するか、あるいは検索前にトークナイズを行う必要のない、すでに処理済みのトークン配列として指定できます。 詳細は各関数のドキュメントを参照してください。

例:

-- Search tokens passed as string argument
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Search tokens passed as Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

hasPhrase​

関数 hasPhrase はフレーズに対して照合を行います。すべてのトークンが連続して、かつ検索文字列と同じ順序で現れる必要があります。

すべてのトークンがどこかに含まれていればよい hasAllTokens とは異なり、hasPhrase ではそれらが連続した並びとして現れる必要があります。 検索フレーズは、インデックスカラムに設定されたものと同じトークナイザーを使用してトークン化されます。 この関数では、splitByNonAlpha、splitByString、ngrams、asciiCJK のいずれかのトークナイザーが必要であることに注意してください。

例:

-- Matches: 'clickhouse' and 'olap' must appear consecutively in that order
SELECT count() FROM table WHERE hasPhrase(comment, 'clickhouse olap');

-- Does NOT match a row containing 'olap clickhouse' (wrong order)
-- Does NOT match a row containing 'clickhouse fast olap' (non-consecutive)

has​

配列関数 has は、文字列の配列に含まれる単一のトークンに対してマッチします。

例:

SELECT count() FROM table WHERE has(array, 'clickhouse');

mapContains​

mapContains 関数（mapContainsKey のエイリアス）は、検索対象の文字列から抽出されたトークンを map のキーと照合します。 動作は、String カラムに対する equals 関数と同様です。 テキストインデックスが使用されるのは、mapKeys(map) 式に対して作成されている場合のみです。

例:

SELECT count() FROM table WHERE mapContainsKey(map, 'clickhouse');
-- OR
SELECT count() FROM table WHERE mapContains(map, 'clickhouse');

mapContainsValue​

mapContainsValue 関数は、検索対象文字列から抽出されたトークンがマップの値に含まれるかどうかを照合します。 挙動は、String カラムに対する equals 関数と類似しています。 テキストインデックスは、mapValues(map) 式に対して作成されている場合にのみ使用されます。

例:

SELECT count() FROM table WHERE mapContainsValue(map, 'clickhouse');

mapContainsKeyLike と mapContainsValueLike​

関数 mapContainsKeyLike と mapContainsValueLike は、マップのすべてのキー（または値）を対象に、パターンとの照合を行います。

例:

SELECT count() FROM table WHERE mapContainsKeyLike(map, '% clickhouse %');
SELECT count() FROM table WHERE mapContainsValueLike(map, '% clickhouse %');

operator[]​

アクセス用の operator[] は、テキスト索引と組み合わせて使用することで、キーや値をフィルタリングできます。テキスト索引が使用されるのは、mapKeys(map) または mapValues(map)、あるいはその両方の式に対して作成されている場合のみです。

例：

SELECT count() FROM table WHERE map['engine'] = 'clickhouse';

テキスト索引と併用する Array(T) 型および Map(K, V) 型カラムの例を以下に示します。

Array(String) カラムのインデックス化​

ブログプラットフォームを想像してください。そこでは、著者がブログ記事にキーワードを付けてカテゴリ分けしています。 ユーザーには、トピックを検索したりクリックしたりすることで、関連コンテンツを見つけてほしいと考えています。

次のテーブル定義を考えてみてください：

CREATE TABLE posts
(
    post_id UInt64,
    title String,
    content String,
    keywords Array(String)
)
ENGINE = MergeTree
ORDER BY (post_id);

テキスト索引がない場合、特定のキーワード (例：clickhouse) を含む投稿を検索するには、すべてのエントリを走査する必要があります。

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- slow full-table scan - checks every keyword in every post

プラットフォームが成長するにつれて、クエリはすべての行の keywords 配列を走査しなければならないため、処理がますます遅くなっていきます。 このパフォーマンス上の問題を解消するために、カラム keywords に対してテキスト索引を定義します。

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Don't forget to rebuild the index for existing data

Map カラムのインデックス作成​

多くのオブザーバビリティのユースケースでは、ログメッセージは「コンポーネント」に分割され、タイムスタンプには日時型、ログレベルには enum 型など、適切なデータ型として保存されます。 メトリクスフィールドは、キーと値のペアとして保存するのが最適です。 運用チームは、デバッグ、セキュリティインシデント、監視のために、ログを効率的に検索する必要があります。

次のような logs テーブルを考えてみます:

CREATE TABLE logs
(
    id UInt64,
    timestamp DateTime,
    message String,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY (timestamp);

テキスト索引がない場合、Map データを検索するにはテーブル全体をフルスキャンする必要があります。

-- Finds all logs with rate limiting data:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- slow full-table scan

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- slow full-table scan

ログ量が増えるにつれて、これらのクエリは遅くなります。

解決策は、Map のキーおよび値に対してテキスト索引を作成することです。 フィールド名や属性タイプでログを検索したい場合は、mapKeys を使用してテキスト索引を作成します。

ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;

属性の実際の内容を検索する必要がある場合は、mapValues を使用してテキスト索引を作成します。

ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;

クエリの例:

-- Find all rate-limited requests:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- Finds all logs where any attribute includes an error:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast

JSONカラムのインデックス化​

JSON カラムでは、テキスト索引を 3 つの方法で利用できます。

  1. 特定のサブカラムに対するインデックス — 通常のカラムと同様に、既知の JSON パスにテキスト索引を作成します。これにより、そのパスの 値 がインデックス化されます。
  2. JSONAllPaths を使用したパスベースのインデックス — 各グラニュールに存在する すべてのパス をインデックス化し、クエリ対象のパスを含む可能性がないグラニュールをスキップします。Map カラムと同様です。
  3. JSONAllValues を使用した値ベースのインデックス — すべての JSON パスにまたがる すべての値 をインデックス化し、単一のインデックスで任意の JSON サブカラムに対する全文検索を高速化します。

特定のサブカラムに対する索引​

通常のカラムと同じ構文で、任意の JSON サブカラムにスキップ索引を作成できます。

索引式で JSON サブカラムを参照する方法は 2 つあります。

  • 型付きパス — JSON 型ヒントで宣言し、名前で直接アクセスします: json.a.
  • 動的パス (明示的なキャストあり) — :: キャスト構文を使用します: json.b::String.

索引定義の例:

CREATE TABLE sensor_data
(
    data JSON(sensor_id String),
    INDEX idx_sensor data.sensor_id TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_location data.location::String TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number , 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);

クエリ例:

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id = 'id_5';

結果:

...
    Indexes:
      Skip
        Name: idx_sensor
        Description: text
        Condition: (mode: All; tokens: ["5", "id"])
        Parts: 1/2
        Granules: 1/8

クエリ例:

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';

結果:

...
    Indexes:
      Skip
        Name: idx_location
        Description: text
        Condition: (mode: All; tokens: ["5", "room"])
        Parts: 1/2
        Granules: 1/8

JSONAllPaths によるパスベース索引​

Map カラムと同様に、JSON カラムにも JSONAllPaths を使用してテキスト索引を作成できます。 この索引は各グラニュールに含まれる JSON パスの集合を保持し、クエリ対象のパスが存在しないグラニュールをスキップするために利用します。

索引定義の例:

CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');

EXPLAIN indexes = 1 を使用すると、スキップ索引が使われていることを確認できます。 パスが1つのパートにしか存在しない場合、索引はもう一方のパートをスキップします。

例:

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';

結果:

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

どのパーツにもパスが存在しない場合、すべてのパーツとグラニュールがスキップされます。

例:

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;

結果:

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["nonexistent"])
        Parts: 0/2
        Granules: 0/2

IS NOT NULL でも索引が使用され、パスが存在しないグラニュールは (その場合、値は NULL になるため) スキップされます。

例:

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;

結果:

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

JSONAllValues を使用した値ベースの索引​

テキスト索引は、関数 JSONAllValues を介して JSON カラムの検索を高速化するために使用できます。

JSONAllValues は、JSON カラムのすべての値を Array(String) として返します。 文字列以外のデータ型の値 (例: 整数や配列) は、そのテキスト表現に変換されます。 JSONAllValues に対するテキスト索引は、各行のすべての JSON パスにまたがって、これらのテキスト表現を索引付けします。 この索引は、その後、個々の JSON サブカラムで絞り込むクエリを高速化できます。 クエリが特定のサブカラム (例: data.user_name = 'alice') で絞り込む場合、テキスト索引は、JSON 値のいずれにも検索トークンが含まれていない行 (およびグラニュール) をすばやくスキップできます。

注記

異なる JSON パスに同じトークンが含まれている場合、この索引は偽陽性を返すことがあります。 たとえば、行 1 が {"a": "hello", "b": "world"} を持ち、クエリで data.a = 'world' を検索する場合、テキスト索引は world がパス a ではなく b に属していることを区別できません。 このような場合、索引はその行をスキップせず、最終的な評価は実際のカラムデータに対するフィルタリングで行われます。 これは、索引が高速な事前フィルターとして機能する、他のテキスト索引の利用ケースと同じ動作です。

索引の作成​

索引の定義例:

CREATE TABLE events
(
    id UInt64,
    data JSON,
    INDEX json_idx JSONAllValues(data) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;
サポートされるクエリパターン​

索引を作成すると、JSONサブカラムに対するクエリは、String カラムと同じ関数、およびすべてのカラムで equals 関数を使用した場合に高速化できます。

サブカラムへのアクセス:

SELECT * FROM events WHERE data.user_name = 'alice';
SELECT * FROM events WHERE data.message LIKE '% error %';
SELECT * FROM events WHERE startsWith(data.status, 'fail');
SELECT * FROM events WHERE hasToken(data.title, 'clickhouse');

明示的な CAST によるサブカラムアクセス:

SELECT * FROM events WHERE hasAllTokens(data.message::String, 'connection timeout');
SELECT * FROM events WHERE data.status_code::UInt64 = 404;
SELECT * FROM events WHERE has(data.tags::Array(String), 'bug')

IN 演算子:

SELECT * FROM events WHERE data.level IN ('error', 'critical');

フレーズ検索​

テキスト索引は、hasPhrase 関数によるフレーズ検索をサポートしています。 フレーズ内のすべてのトークンは、ドキュメント内で連続して同じ順序で出現する必要があります。

テキスト索引は、フレーズ内のすべてのトークンのポスティングリストを交差させて候補となるグラニュールを特定することで、フレーズ検索を高速化します。 その後、ClickHouse はそれらのグラニュール内でトークンが正確に隣接していることを検証します。

hasPhrase は、トークナイザー splitByNonAlpha、splitByString、ngrams、asciiCJK でサポートされています。

フレーズ文字列は、索引に設定されたトークナイザーでトークン化されます。 フレーズ内のトークナイザーの区切り文字は無視されます。splitByNonAlpha トークナイザーでは、hasPhrase(text, 'quick+brown') は hasPhrase(text, 'quick brown') と同等です。

例​

CREATE TABLE tab (
    id UInt32,
    text String,
    INDEX idx(text) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO tab VALUES
    (1, 'weather in New York'),
    (2, 'New weather in York'),
    (3, 'weather in New Orleans');

SELECT id, text FROM tab WHERE hasPhrase(text, 'weather in New York');

結果:

   ┌─id─┬─text────────────────┐
1. │  1 │ weather in New York │
   └────┴─────────────────────┘

2 行目 ('New weather in York') は、トークンの順序が異なるため一致しません。 3 行目 ('weather in New Orleans') は、トークン 'York' が含まれていないため一致しません。

パフォーマンスチューニング​

ダイレクトリード​

特定の種類のテキストクエリは、「ダイレクトリード」と呼ばれる最適化によって大幅に高速化されます。

例：

SELECT column_a, column_b, ...
FROM [...]
WHERE string_search_function(column_with_text_index)

ClickHouse における ダイレクトリード 最適化は、基盤となるテキストカラムにアクセスせず、テキスト索引 (つまりテキスト索引ルックアップ) だけを用いてクエリに応答します。 テキスト索引ルックアップは比較的少量のデータしか読み込まないため、通常の ClickHouse のスキップ索引 (スキップ索引のルックアップの後に、残りのグラニュールの読み込みとフィルタリングを行う) よりもはるかに高速です。

ダイレクトリード は次の 2 つの設定で制御されます。

サポートされる関数

ダイレクトリード 最適化は、関数 hasToken、hasAllTokens、および hasAnyTokens をサポートします。 テキスト索引が array トークナイザー で定義されている場合、関数 equals、has、mapContainsKey、mapContainsValue に対しても ダイレクトリード がサポートされます。 これらの関数は、AND、OR、NOT 演算子で組み合わせることもできます。 WHERE または PREWHERE 句には、 (テキストカラムまたは他のカラムに対する) 追加の非テキスト検索関数によるフィルタを含めることもできます。この場合でも ダイレクトリード 最適化は使用されますが、その効果は低くなります (サポートされているテキスト検索関数にのみ適用されるためです) 。

あるクエリが ダイレクトリード を利用しているかを確認するには、そのクエリを EXPLAIN PLAN actions = 1 付きで実行します。 例として、ダイレクトリード を無効にしたクエリは

EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read

戻り値

[...]
Filter ((WHERE + Change column names to column identifiers))
Filter column: hasToken(__table1.col, 'some_token'_String) (removed)
Actions: INPUT : 0 -> col String : 0
         COLUMN Const(String) -> 'some_token'_String String : 1
         FUNCTION hasToken(col :: 0, 'some_token'_String :: 1) -> hasToken(__table1.col, 'some_token'_String) UInt8 : 2
[...]

一方、同じクエリを query_plan_direct_read_from_text_index = 1 を指定して実行すると

EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 1, -- enable direct read

戻り値

[...]
Expression (Before GROUP BY)
Positions:
  Filter
  Filter column: __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 (removed)
  Actions: INPUT :: 0 -> __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 UInt8 : 0
[...]

2つ目の EXPLAIN PLAN の出力には、仮想カラム __text_index_<index_name>_<function_name>_<id> が含まれます。 このカラムが存在する場合は、ダイレクトリード が使用されています。

WHERE 句のフィルタがテキスト検索関数のみで構成されている場合、クエリはカラムデータの読み取り自体を完全に回避でき、ダイレクトリード によって最大のパフォーマンス向上が得られます。 ただし、クエリ内の他の箇所でテキストカラムにアクセスしている場合でも、ダイレクトリード により依然としてパフォーマンス改善が見込めます。

ヒントとしての ダイレクトリード

ヒントとしての ダイレクトリード は、通常の ダイレクトリード と同じ原理に基づきますが、基礎となるテキストカラムは削除せずに、テキスト索引のデータから構築された追加フィルタを適用する点が異なります。 これは、テキスト索引のみを読んだ場合に誤検出 (false positive) が発生しうる関数に対して使用されます。

サポートされている関数は、like、startsWith、endsWith、equals、has、hasPhrase、mapContainsKey、mapContainsValue です。

この追加フィルタにより、他のフィルタと組み合わせて結果セットをさらに絞り込むための追加の選択性が得られ、他のカラムから読み取るデータ量を削減するのに役立ちます。

ヒントとしての ダイレクトリード は、query_plan_text_index_add_hint (デフォルトで有効) を設定することで制御できます。

ヒントを使用しないクエリの例:

EXPLAIN actions = 1
SELECT count()
FROM table
WHERE (col LIKE '%some-token%') AND (d >= today())
SETTINGS query_plan_text_index_add_hint = 0
FORMAT TSV

戻り値

[...]
Prewhere filter column: and(like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]

一方、同じクエリを query_plan_text_index_add_hint = 1 に設定して実行した場合は

EXPLAIN actions = 1
SELECT count()
FROM table
WHERE col LIKE '%some-token%'
SETTINGS query_plan_text_index_add_hint = 1

戻り値

[...]
Prewhere filter column: and(__text_index_idx_col_like_d306f7c9c95238594618ac23eb7a3f74, like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]

2つ目の EXPLAIN PLAN の出力では、フィルター条件に追加の連言条件 (__text_index_...) が加えられていることが分かります。 PREWHERE 最適化により、フィルター条件は3つの個別の連言条件に分解され、計算コストが低いものから順に適用されます。 このクエリでは、適用順序は __text_index_...、次に greaterOrEquals(...)、最後に like(...) となります。 この順序付けにより、テキスト索引と元のフィルター条件でスキップされるグラニュールに加えて、クエリの WHERE 句以降で使用される重いカラムを読み込む前に、さらに多くのデータグラニュールをスキップできるため、読み取るデータ量を一層削減できます。

LIKE/ILIKE クエリ​

LIKE/ILIKE クエリのパターンが %<alpha-numeric-characters-without-spaces>% で、テキスト索引のトークナイザーが splitByNonAlpha または array の場合、ClickHouse は転置索引を利用して LIKE/ILIKE クエリを大幅に高速化できます。これを実現するため、ClickHouse は一致するパターンを見つける際に、テーブル全体をスキャンする代わりに転置索引の Dictionary をスキャンします。

この最適化を有効にすると、LIKE/ILIKE クエリはテーブル全体のスキャンより大幅に高速になるはずです。ただし、パターンが Dictionary 内のほとんどのトークンに一致する場合は、テーブル全体のスキャンよりもパフォーマンスが低下することがあります。幸い、これを防ぐためのフォールバック機構が用意されています。

この最適化は、次の設定で制御されます。

フォールバック機構は、次の 2 つの設定で制御されます。

この最適化がサポートするのは、関数 like と ilike のみです。

キャッシュ​

メモリ内でテキストインデックスの一部をバッファリングするために、さまざまなキャッシュを使用できます（Implementation Details セクションを参照）。 現在、I/O を削減するために、テキストインデックスのデシリアライズ済みヘッダー、トークン、およびポスティングリスト用のキャッシュが用意されています。 これらは、設定 use_text_index_header_cache、use_text_index_tokens_cache、および use_text_index_postings_cache によって有効化できます。

デフォルトでは、すべてのキャッシュは無効になっています。 キャッシュをクリアするには、文 SYSTEM CLEAR TEXT INDEX CACHES を使用します。

キャッシュを構成するには、以下のサーバー設定を参照してください。

Tokens キャッシュの設定​

SettingDescription
text_index_tokens_cache_policyテキストインデックス用 Tokens キャッシュのポリシー名。
text_index_tokens_cache_sizeキャッシュの最大サイズ（バイト単位）。
text_index_tokens_cache_max_entriesキャッシュ内のデシリアライズ済み Token の最大数。
text_index_tokens_cache_size_ratioテキストインデックス用 Tokens キャッシュにおける、キャッシュ全体サイズに対する保護キューサイズの比率。

ヘッダーキャッシュの設定​

Setting説明
text_index_header_cache_policyテキストインデックスヘッダーキャッシュのポリシー名。
text_index_header_cache_sizeキャッシュの最大サイズ（バイト単位）。
text_index_header_cache_max_entriesキャッシュ内に保持されるデシリアライズ済みヘッダーの最大数。
text_index_header_cache_size_ratioテキストインデックスヘッダーキャッシュにおける、保護キューのサイズがキャッシュ全体のサイズに対して占める割合。

ポスティングリストキャッシュの設定​

Setting説明
text_index_postings_cache_policyテキストインデックスのポスティングリストキャッシュポリシー名。
text_index_postings_cache_sizeキャッシュの最大サイズ（バイト単位）。
text_index_postings_cache_max_entriesキャッシュ内のデシリアライズ済みポスティングの最大数。
text_index_postings_cache_size_ratioテキストインデックスのポスティングリストキャッシュにおける保護キューのサイズが、キャッシュ全体サイズに占める割合。

制限事項​

テキストインデックスには、現在次のような制限があります。

  • トークン数が非常に多いテキストインデックス（例: 100億トークン）をマテリアライズすると、大量のメモリを消費する可能性があります。テキスト インデックスのマテリアライズは、直接（ALTER TABLE <table> MATERIALIZE INDEX <index>）行われる場合もあれば、パーツのマージ時に間接的に行われる場合もあります。
  • 1つのパーツ内の行数が 4.294.967.296（= 2^32 ≒ 42億）を超える場合、そのパーツ上のテキストインデックスをマテリアライズすることはできません。テキストインデックスがマテリアライズされていない場合、クエリはそのパーツ内での低速な総当たり検索にフォールバックします。最悪の場合の見積もりとして、1つのパーツが String 型の単一カラムだけを持ち、MergeTree の設定項目 max_bytes_to_merge_at_max_space_in_pool（デフォルト: 150 GB）が変更されていないと仮定します。この場合、そのカラムの1行あたりの平均文字数が 29.5 文字未満であれば、上記の状況が発生します。実際には、テーブルは他のカラムも含んでいるため、閾値は（他のカラムの数、型、サイズに応じて）その何分の一にも小さくなります。

テキストインデックスと Bloom Filter ベースのインデックス​

文字列述語はテキストインデックスと Bloom Filter ベースのインデックス（インデックスタイプ bloom_filter, ngrambf_v1, tokenbf_v1, sparse_grams）を使って高速化できますが、両者は設計と想定されるユースケースが根本的に異なります。

Bloom Filter インデックス

  • 確率的データ構造に基づいており、偽陽性が発生する可能性があります。
  • 集合への所属、すなわち「カラムにトークン X が含まれる可能性があるか vs. X を含まないことが確実か」といった問いにのみ答えることができます。
  • クエリ実行時に大まかな範囲スキップを可能にするため、granule 単位の情報を保存します。
  • 適切にチューニングするのが難しいです（例についてはこちらを参照）。
  • 比較的コンパクトです（パートあたり数キロバイトから数メガバイト程度）。

テキストインデックス

  • トークンに対して決定論的な転置インデックスを構築します。インデックス自体が偽陽性を発生させることはありません。
  • テキスト検索ワークロード向けに特化して最適化されています。
  • 行レベルの情報を保持し、効率的なトークンのルックアップを可能にします。
  • 比較的大きくなります（パートあたり数十〜数百メガバイト）。

Bloom Filter ベースのインデックスは、フルテキスト検索をあくまで「副次的な結果」としてのみサポートします。

  • 高度なトークナイズや前処理をサポートしません。
  • 複数トークンの検索をサポートしません。
  • 転置インデックスに期待される性能特性を提供しません。

一方でテキストインデックスは、フルテキスト検索のために専用設計されています。

  • トークナイズおよび前処理を提供します。
  • hasAllTokens, LIKE, match などのテキスト検索関数を効率的にサポートします。
  • 大規模なテキストコーパスに対して、はるかに優れたスケーラビリティを持ちます。

実装の詳細​

各テキスト索引は、2 つの (抽象的な) データ構造から構成されます:

  • 各トークンをポスティングリストにマッピングする dictionary と、
  • 各々が行番号の集合を表す複数のポスティングリストの集合。

テキスト索引はパーツ全体に対して構築されます。 他のスキップ索引と異なり、テキスト索引はデータパーツをマージする際に再構築するのではなく、マージすることができます (下記参照) 。

索引の作成時には、3 つのファイルが生成されます (パーツごと) :

Dictionary blocks ファイル (.dct)

テキスト索引内のトークンはソートされ、各 512 トークンの dictionary ブロックに格納されます (ブロックサイズはパラメータ dictionary_block_size によって設定可能です) 。 Dictionary blocks ファイル (.dct) は、そのパーツ内のすべての索引グラニュールに含まれる dictionary ブロックから構成されます。

Index header ファイル (.idx)

Index header ファイルには、各 dictionary ブロックについて、そのブロックの最初のトークンと dictionary blocks ファイル内での相対オフセットが含まれます。

このスパース索引構造は、ClickHouse の sparse primary key index と類似しています。

ポスティングリスト ファイル (.pst)

すべてのトークンに対するポスティングリストは、ポスティングリスト ファイル内に連続して配置されます。 空間を節約しつつ高速な積集合および和集合演算を可能にするため、ポスティングリストは roaring bitmaps として格納されます。 ポスティングリストが posting_list_block_size より大きい場合、それは複数のブロックに分割され、ポスティングリスト ファイル内に連続して格納されます。

テキスト索引のマージ

データパーツがマージされるとき、テキスト索引を最初から再構築する必要はなく、代わりにマージ処理の別ステップで効率的にマージできます。 このステップでは、各入力パーツのテキスト索引のソート済み dictionary が読み込まれ、新しい統合 dictionary に結合されます。 ポスティングリスト内の行番号も、初期マージフェーズ中に作成される旧行番号から新行番号へのマッピングを用いて、マージ後のデータパーツ内での新しい位置を反映するよう再計算されます。 このテキスト索引のマージ方式は、_part_offset カラムを持つ projections がマージされる方法と似ています。 ソースパーツ内で索引がマテリアライズされていない場合、それは構築され、一時ファイルに書き出された後、他のパーツおよび他の一時索引ファイルの索引とともにマージされます。

デバッグ

テーブル関数 mergeTreeTextIndex は、テキスト索引をインスペクトするために使用できます。

例: Hackernews データセット​

大量のテキストを含む大規模データセットに対するテキスト索引のパフォーマンス向上を確認します。 人気サイト Hacker News 上のコメント 2,870 万行を使用します。 以下はテキスト索引を定義していないテーブルです:

CREATE TABLE hackernews (
    id UInt64,
    deleted UInt8,
    type String,
    author String,
    timestamp DateTime,
    comment String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    children Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);

約 2,870 万行のデータは S3 上の Parquet ファイルに格納されています。これらを hackernews テーブルに挿入しましょう。

INSERT INTO hackernews
    SELECT * FROM s3Cluster(
        'default',
        'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
        'Parquet',
        '
    id UInt64,
    deleted UInt8,
    type String,
    by String,
    time DateTime,
    text String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    kids Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32');

ALTER TABLE を使用して comment カラムにテキスト索引を追加し、その後マテリアライズします。

-- Add the index
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- Materialize the index for existing data
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

では、hasToken、hasAnyTokens、hasAllTokens 関数を使ってクエリを実行してみましょう。 次の例では、標準的な索引スキャンと直接読み取り最適化を比較した際の、劇的なパフォーマンス差を示します。

1. hasToken の使用​

hasToken は、テキストに特定の 1 つのトークンが含まれているかどうかを確認します。 ここでは大文字小文字を区別してトークン 'ClickHouse' を検索します。

ダイレクトリード無効化（標準スキャン） デフォルトでは、ClickHouse はスキップ索引を使ってグラニュールをフィルタリングし、その後それらのグラニュールのカラムデータを読み取ります。 この挙動は、ダイレクトリードを無効にすることで再現できます。

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.362 sec. Processed 24.90 million rows, 9.51 GB

ダイレクトリード有効時（Fast index read） ここでは、ダイレクトリードを有効にした状態（デフォルト）で、同じクエリを実行します。

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.008 sec. Processed 3.15 million rows, 3.15 MB

ダイレクトリードクエリは 45 倍以上高速で (0.362 秒 対 0.008 秒)、インデックスだけを読み取ることで処理するデータ量も大幅に削減されます (9.51 GB 対 3.15 MB)。

2. hasAnyTokens を使用する​

hasAnyTokens は、テキストに指定したトークンのうち少なくとも 1 つが含まれているかどうかを判定します。 ここでは、'love' または 'ClickHouse' のいずれかを含むコメントを検索します。

Direct read 無効 (標準スキャン) 

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 1.329 sec. Processed 28.74 million rows, 9.72 GB

ダイレクトリードの有効化 (索引の高速読み取り) 

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 0.015 sec. Processed 27.99 million rows, 27.99 MB

この一般的な「OR」検索では、高速化の度合いはさらに顕著です。 フルカラムスキャンを回避することで、クエリはほぼ89倍高速になります (1.329秒 vs 0.015秒) 。

3. hasAllTokens の使用​

hasAllTokens は、テキストが指定されたトークンをすべて含んでいるかどうかをチェックします。 ここでは、love と ClickHouse の両方を含むコメントを検索します。

ダイレクトリード無効 (標準スキャン) ダイレクトリードを無効化していても、標準のスキップ索引は引き続き機能します。 28.7M 行をわずか 147.46K 行まで絞り込みますが、それでもカラムから 57.03 MB を読み取る必要があります。

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.184 sec. Processed 147.46 thousand rows, 57.03 MB

ダイレクトリード有効 (高速な索引読み取り) ダイレクトリードでは索引データ上で処理を行うことでクエリに応答し、147.46 KB 分のデータしか読み取りません。

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.007 sec. Processed 147.46 thousand rows, 147.46 KB

この「AND」条件の検索では、ダイレクトリード最適化のほうが標準的なスキップ索引スキャンよりも 26 倍以上高速 (0.184s 対 0.007s) です。

ダイレクトリードの最適化は、複合ブール式にも適用されます。 ここでは、「ClickHouse」または「clickhouse」を対象に、大文字と小文字を区別しない検索を行います。

ダイレクトリード無効 (標準スキャン) 

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.450 sec. Processed 25.87 million rows, 9.58 GB

ダイレクトリードを有効化 (索引の高速読み取り) 

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.013 sec. Processed 25.87 million rows, 51.73 MB

索引からの結果を組み合わせることで、直接読み出しクエリは 34 倍高速 (0.450s 対 0.013s) になり、9.58 GB のカラムデータを読み取る必要がなくなります。 このケースでは、hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) が、より効率的で推奨される構文となります。

関連情報​

古い資料