テキスト索引による全文検索 - ClickHouse Documentation

ClickHouse のテキスト索引 (“転置索引” とも呼ばれます) は、文字列データに対して高速な全文検索機能を提供します。この索引は、カラム内の各トークンを、そのトークンを含む行に対応付けます。トークンは、トークン化と呼ばれる処理によって生成されます。たとえば、ClickHouse では、英語の文 “All cat like mice.” はデフォルトで [“All”, “cat”, “like”, “mice”] にトークン化されます (末尾のピリオドは無視される点に注意してください) 。より高度なトークナイザーも利用でき、たとえばログデータ向けのものがあります。

テキスト索引の作成

テキスト索引を作成するには、まず対応する実験的機能の設定を有効にします。

SET allow_experimental_full_text_index = true;

テキスト索引は、String、FixedString、Array(String)、Array(FixedString)、および Map 型のカラム (mapKeys および mapValues の map 関数経由) に対して、次の構文で定義できます。

CREATE TABLE tab
(
    `key` UInt64,
    `str` String,
    INDEX text_idx(str) TYPE text(
                                -- 必須パラメーター:
                                tokenizer = splitByNonAlpha|splitByString(S)|ngrams(N)|array
                                -- オプションパラメーター:
                                [, preprocessor = expression(str)]
                                -- オプションの高度なパラメーター:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, max_cardinality_for_embedded_postings = M]
                                [, bloom_filter_false_positive_rate = R]
                            ) [GRANULARITY 64]
)
ENGINE = MergeTree
ORDER BY key

トークナイザー引数。tokenizer 引数では、使用するトークナイザーを指定します。

splitByNonAlpha は、ASCII の英数字以外の文字で文字列を分割します (関数 splitByNonAlpha も参照) 。
splitByString(S) は、ユーザー定義の区切り文字列 S で文字列を分割します (関数 splitByString も参照) 。区切り文字は省略可能なパラメータで指定できます。たとえば、tokenizer = splitByString([', ', '; ', '\n', '\\']) のようにします。なお、各文字列は複数の文字で構成できます (例の ', ' など) 。明示的に指定しない場合のデフォルトの区切り文字リスト (たとえば tokenizer = splitByString) は、単一の空白文字 [' '] です。
ngrams(N) は、文字列を同じ長さの N-gram に分割します (関数 ngrams も参照) 。 ngram の長さは、2 から 8 までの省略可能な整数パラメータで指定できます。たとえば、tokenizer = ngrams(3) のようにします。明示的に指定しない場合のデフォルトの ngram サイズ (たとえば tokenizer = ngrams) は 3 です。
array はトークン化を行いません。つまり、各行の値がそのまま 1 つのトークンになります (関数 array も参照) 。
sparseGrams(min_length, max_length, min_cutoff_length) — sparseGrams 関数と同じアルゴリズムを使って、文字列を min_length のすべての ngram と、それより長く max_length 以下のいくつかの ngram に分割します。min_cutoff_length を指定した場合、長さが min_cutoff_length 以上の N-gram のみが索引に保存されます。固定長の N-gram だけを生成する ngrams(N) とは異なり、sparseGrams は指定した範囲内で可変長の N-gram の集合を生成するため、テキストコンテキストをより柔軟に表現できます。たとえば、tokenizer = sparseGrams(3, 5, 4) は入力文字列から 3-gram、4-gram、5-gram を生成し、そのうち 4-gram と 5-gram だけを索引に保存します。

splitByString トークナイザーは、区切り文字を左から右の順に適用します。そのため、あいまいさが生じることがあります。たとえば、区切り文字列 ['%21', '%'] を指定すると、%21abc は ['abc'] としてトークン化されます。一方、区切り文字列の順序を ['%', '%21'] に入れ替えると、['21abc'] が出力されます。多くの場合は、より長い区切り文字が先にマッチするようにするのが望ましいでしょう。これは通常、区切り文字列を長い順に渡すことで実現できます。区切り文字列がたまたまプレフィックスコードを構成している場合は、任意の順序で渡せます。

現時点では、中国語などの非西洋言語のテキストに対してテキスト索引を構築することは推奨されていません。現在サポートされているトークナイザーでは、索引サイズが非常に大きくなり、クエリ時間も長くなる可能性があります。今後は、このようなケースをより適切に処理できる、言語固有の専用トークナイザーを追加する予定です。

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

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

戻り値

+-tokens--------------------------+
| ['abc','bc ','c d',' de','def'] |
+---------------------------------+

プリプロセッサ引数。省略可能な引数 preprocessor は、トークン化の前に入力文字列を変換する式です。プリプロセッサ引数の典型的な用途は次のとおりです。

大文字と小文字を区別しないマッチングを可能にするために、入力文字列を小文字化 (または大文字化) すること。たとえば lower、lowerUTF8 です。最初の例を参照してください。
UTF-8 の正規化。たとえば normalizeUTF8NFC、normalizeUTF8NFD、normalizeUTF8NFKC、normalizeUTF8NFKD、toValidUTF8 です。
不要な文字や部分文字列を削除または変換すること。たとえば extractTextFromHTML、substring、idnaEncode です。

プリプロセッサ式は、型 String または FixedString の入力値を、同じ型の値に変換する必要があります。例:

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))

また、プリプロセッサ式は、そのテキスト索引が定義されているカラムのみを参照する必要があります。非決定論的関数は使用できません。関数 hasToken、hasAllTokens、および hasAnyTokens は、検索語をトークン化する前に、まずプリプロセッサで変換します。例:

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

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

と同じです:

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

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

その他の引数。ClickHouse のテキスト索引は、セカンダリ索引として実装されています。ただし、他のスキップ索引とは異なり、テキスト索引のデフォルトの GRANULARITY は 64 です。この値は経験的に選ばれたもので、ほとんどのユースケースにおいて、速度と索引サイズのバランスが良好です。上級ユーザーは別の索引 granularity を指定することもできます (非推奨です) 。

省略可能な高度なパラメーター

以下の高度なパラメーターのデフォルト値は、ほぼすべての状況で問題なく機能します。これらを変更することは推奨しません。省略可能なパラメーター dictionary_block_size (デフォルト: 128) は、Dictionary ブロックのサイズを行数で指定します。省略可能なパラメーター dictionary_block_frontcoding_compression (デフォルト: 1) は、Dictionary ブロックで圧縮に front coding を使用するかどうかを指定します。省略可能なパラメーター max_cardinality_for_embedded_postings (デフォルト: 16) は、posting list を Dictionary ブロックに埋め込むカーディナリティのしきい値を指定します。省略可能なパラメーター bloom_filter_false_positive_rate (デフォルト: 0.1) は、Dictionary のブルームフィルターの偽陽性率を指定します。

テーブルの作成後に、テキスト索引をカラムに追加したり削除したりできます。

ALTER TABLE tab DROP INDEX text_idx;
ALTER TABLE tab ADD INDEX text_idx(s) TYPE text(tokenizer = splitByNonAlpha);

テキスト索引の使用

SELECT クエリでテキスト索引を使用するのは簡単で、一般的な文字列検索関数は自動的にこの索引を利用します。索引が存在しない場合、以下の文字列検索関数は低速な総当たりスキャンにフォールバックします。

対応している関数

SELECTクエリのWHERE句でテキスト関数を使用する場合は、テキスト索引を利用できます。

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

`=` と `!=`

= (equals) と != (notEquals ) は、指定した検索語全体に一致します。例:

SELECT * from tab WHERE str = 'Hello';

テキスト索引は = と != をサポートしていますが、等値検索と不等値検索が有効なのは、索引が各行の値全体を保存する array トークナイザーを使用する場合のみです。

`IN` and `NOT IN`

IN (in) とNOT IN (notIn) は、関数equalsおよびnotEqualsと似ていますが、検索語のすべてに一致するか (IN) 、どれにも一致しない場合 (NOT IN) に使用します。例:

SELECT * from tab WHERE str IN ('Hello', 'World');

= と != の場合と同じ制約が適用されます。つまり、IN と NOT IN が意味を持つのは、array トークナイザーと組み合わせた場合に限られます。

`LIKE`、`NOT LIKE`、`match`

現在、これらの関数がフィルタリングにテキスト索引を使用するのは、索引のトークナイザーが splitByNonAlpha または ngrams の場合に限られます。

LIKE like、NOT LIKE (notLike) 、および match 関数をテキスト索引と組み合わせて使用するには、ClickHouse が検索語から完全なトークンを抽出できる必要があります。例:

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

support は、この例では support、supports、supporting などに一致する可能性があります。この種のクエリは部分文字列クエリであり、テキスト索引で高速化することはできません。 LIKE クエリでテキスト索引を活用するには、LIKE パターンを次のように書き換える必要があります。

SELECT count() FROM tab WHERE comment LIKE ' support %'; -- または `% support %`

support の左右に空白を入れることで、その語を token として抽出できるようになります。

`startsWith` と `endsWith`

LIKE と同様に、関数 startsWith と endsWith でテキスト索引を使用できるのは、検索語から完全なトークンを抽出できる場合に限られます。例:

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

この例では、トークンと見なされるのは clickhouse のみです。 support は support、supports、supporting などに一致しうるため、トークンではありません。 clickhouse supports で始まるすべての行を見つけるには、検索パターンの末尾に半角スペースを付けてください:

startsWith(comment, 'clickhouse supports ')`

同様に、endsWith も先頭にスペースを付けて使用する必要があります。

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

`hasToken` and `hasTokenOrNull`

関数 hasToken と hasTokenOrNull は、指定された単一のトークンに対してマッチします。前述の関数とは異なり、これらは検索語をトークン化しません (入力は単一のトークンであることを前提としています) 。例:

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

関数 hasToken と hasTokenOrNull は、text 索引で使用できる関数の中で最も高性能です。

`hasAnyTokens` and `hasAllTokens`

関数 hasAnyTokens と hasAllTokens は、指定したトークンのいずれか、またはすべてに一致するかどうかを判定します。これら 2 つの関数は、検索トークンを、索引カラムで使われているものと同じトークナイザーでトークン化される文字列として、または検索前にトークン化されない、すでに処理済みのトークンの配列として受け取ります。詳細については、各関数のドキュメントを参照してください。例:

-- 文字列引数として渡された検索トークン
SELECT count() FROM tab WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM tab WHERE hasAllTokens(comment, 'clickhouse olap');

-- Array(String)として渡された検索トークン
SELECT count() FROM tab WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM tab WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

`has`

Array 関数 has は、文字列の配列内の単一のトークンとの一致を判定します。例:

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

`mapContains`

関数 mapContains (mapContainsKey のエイリアス) は、マップのキー内の単一のトークンにマッチします。例:

SELECT count() FROM tab WHERE mapContainsKey(map, 'clickhouse');
-- または
SELECT count() FROM tab WHERE mapContains(map, 'clickhouse');

`operator[]`

アクセス演算子operator[]は、テキスト索引と組み合わせて使用し、キーと値で絞り込むことができます。例:

SELECT count() FROM tab WHERE map['engine'] = 'clickhouse'; -- テキスト索引が定義されている場合に使用される

Array(T) と Map(K, V) でテキスト索引を使用する方法については、以下の例を参照してください。

テキスト索引における `Array` と `Map` のサポート例

Array(String) への索引作成

シンプルなブログプラットフォームでは、著者が投稿にキーワードを付けて内容を分類します。一般的な機能として、ユーザーはキーワードをクリックしたりトピックを検索したりして、関連するコンテンツを見つけられます。次のテーブル定義を考えてみましょう。

CREATE TABLE posts (
    post_id UInt64,
    title String,
    content String,
    keywords Array(String) COMMENT 'Author-defined keywords'
)
ENGINE = MergeTree
ORDER BY (post_id);

テキスト索引がない場合、特定のキーワード (例: clickhouse) を含む投稿を見つけるには、全件をスキャンする必要があります：

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- 低速なフルテーブルスキャン - すべての投稿のすべてのキーワードを検索する

プラットフォームの拡大に伴い、クエリはすべての行の keywords 配列をすべて調べる必要があるため、処理はますます遅くなります。このパフォーマンス上の問題を解決するには、keywords に対してテキスト索引を定義します。これにより、すべての keywords を事前処理する検索向けに最適化された構造が作成され、高速なルックアップが可能になります。

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);

重要: テキスト索引を追加した後、既存データに対しては索引を再構築する必要があります。

ALTER TABLE posts MATERIALIZE INDEX keywords_idx;

Map の索引付け

ログシステムでは、サーバーリクエストのメタデータがキー・バリューの組として保存されることがよくあります。運用チームは、デバッグ、セキュリティインシデントへの対応、監視のために、ログを効率的に検索できる必要があります。次のログテーブルを考えてみましょう。

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

テキスト索引がない場合、Map データの検索にはテーブル全体のスキャンが必要です。

レート制限に関するすべてのログを検索します:

SELECT count() FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- テーブルフルスキャンのため低速

特定のIPからのすべてのログを検索します。

SELECT count() FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- テーブルフルスキャンのため低速

ログ量が増えると、これらのクエリの実行は遅くなります。解決策は、Map のキーと値にテキスト索引を作成することです。フィールド名や属性タイプでログを検索する必要がある場合は、mapKeys を使ってテキスト索引を作成します。

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

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

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

重要: テキスト索引を追加したら、既存データに対して再構築する必要があります。

ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;

レート制限されたリクエストをすべて見つけます。

SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- 高速

特定のIPからのすべてのログを検索します:

SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- 高速

実装

索引レイアウト

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

各トークンをポスティングリストに対応付ける辞書
それぞれが行番号の集合を表すポスティングリストの集合

テキスト索引はスキップ索引であるため、これらのデータ構造は論理的には各インデックスグラニュールごとに存在します。索引の作成時には、3つのファイルが作成されます (パートごと) 。 辞書ブロックファイル (.dct) インデックスグラニュール内のトークンはソートされ、128トークンごとの辞書ブロックに格納されます (ブロックサイズはパラメータ dictionary_block_size で設定できます) 。辞書ブロックファイル (.dct) は、パート内のすべてのインデックスグラニュールにある辞書ブロックをまとめたものです。 インデックスグラニュールファイル (.idx) インデックスグラニュールファイルには、各辞書ブロックについて、そのブロックの先頭トークン、辞書ブロックファイル内での相対オフセット、およびそのブロック内の全トークンに対する bloom filter が含まれます。このスパースインデックス構造は、ClickHouse のスパースな主キー索引)に似ています。 bloom filter により、検索対象のトークンが辞書ブロックに含まれていない場合、その辞書ブロックを早い段階でスキップできます。 ポスティングリストファイル (.pst) すべてのトークンのポスティングリストは、ポスティングリストファイル内に順次配置されます。容量を節約しつつ、高速な積集合およびユニオン演算を可能にするため、ポスティングリストは roaring bitmaps として格納されます。ポスティングリストのカーディナリティが 16 未満の場合 (パラメータ max_cardinality_for_embedded_postings で設定可能) 、そのポスティングリストは辞書に埋め込まれます。

Direct read

特定の種類のテキストクエリは、“direct read” と呼ばれる最適化によって大幅に高速化できます。より具体的には、SELECTクエリでテキストカラムを射影 しない 場合、この最適化を適用できます。例:

SELECT column_a, column_b, ... -- 除外: column_with_text_index
FROM [...]
WHERE string_search_function(column_with_text_index)

ClickHouse の direct read 最適化では、基になるテキストカラムにアクセスせず、テキスト索引のみ (つまりテキスト索引のルックアップ) を使ってクエリに応答します。テキスト索引のルックアップで読み取るデータ量は比較的少ないため、ClickHouse の通常のスキップ索引よりもはるかに高速です (通常のスキップ索引では、スキップ索引のルックアップに続いて、対象となったグラニュールの読み込みとフィルタリングが行われます) 。 Direct read は 2 つの設定で制御されます。

設定 query_plan_direct_read_from_text_index (デフォルト: 1) は、direct read が通常有効かどうかを指定します。
設定 use_skip_indexes_on_data_read (デフォルト: 1) は、direct read のもう 1 つの前提条件です。compatibility < 25.10 の ClickHouse データベースでは、use_skip_indexes_on_data_read が無効になっている点に注意してください。そのため、compatibility 設定の値を引き上げるか、SET use_skip_indexes_on_data_read = 1 を明示的に設定する必要があります。

また、direct read を使用するには、テキスト索引が完全に materialize されている必要があります (そのためには ALTER TABLE ... MATERIALIZE INDEX を使用します) 。 サポートされる関数 direct read 最適化では、hasToken、hasAllTokens、hasAnyTokens 関数をサポートしています。これらの関数は、AND、OR、NOT 演算子で組み合わせることもできます。 WHERE clause には、追加の非テキスト検索関数フィルタ (テキストカラムまたは他のカラムに対するもの) を含めることもできます。その場合でも direct read 最適化は使用されますが、効果は低下します (適用されるのはサポートされているテキスト検索関数に対してのみです) 。クエリで direct read が使われていることを確認するには、EXPLAIN PLAN actions = 1 を付けてクエリを実行してください。たとえば、direct read を無効にしたクエリは次のとおりです。

EXPLAIN PLAN actions = 1
SELECT count()
FROM tab
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0;

戻り値

[...]
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 tab
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 1;

戻り値

[...]
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> が含まれます。このカラムが存在する場合は、direct read が使用されます。

例: Hacker News データセット

テキストを多く含む大規模なデータセットに対して、テキスト索引によってどの程度パフォーマンスが向上するかを見ていきましょう。ここでは、人気サイト 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 カラムにテキスト索引を追加し、その後これを実体化します:

-- 索引を追加する
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- 既存データの索引をマテリアライズする
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

それでは、hasToken、hasAnyTokens、hasAllTokens 関数を使ってクエリを実行してみましょう。以下の例では、標準的な索引スキャンと direct read 最適化の間にある大きな性能差を確認できます。

1. `hasToken` の使用

hasToken は、テキストに特定の単一トークンが含まれているかどうかを確認します。大文字と小文字を区別するトークン「ClickHouse」を検索します。 Direct read 無効 (標準スキャン) デフォルトでは、ClickHouse はスキップ索引を使ってグラニュールを絞り込み、その後、それらのグラニュールのカラムデータを読み取ります。この動作は、direct read を無効にすることで再現できます。

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

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

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

Direct read有効時 (高速索引読み取り) 次に、Direct readを有効にした状態 (デフォルト) で、同じクエリを実行します。

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

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

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

索引のみを読み取るため、direct readクエリは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, use_skip_indexes_on_data_read = 0;

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

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

Direct read が有効 (高速な索引読み取り)

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 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’ の両方を含むコメントを検索します。 Direct read 無効 (標準スキャン) direct read を無効にしていても、標準のスキップ索引は引き続き有効です。 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, use_skip_indexes_on_data_read = 0;

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

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

Direct read が有効 (索引の高速読み取り) Direct read は索引データを直接処理してクエリに応答するため、読み取り量は 147.46 KB のみです。

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

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

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

この “AND” 検索では、direct read 最適化は標準のスキップ索引スキャンと比べて 26 倍以上高速です (0.184 秒に対して 0.007 秒) 。

4. 複合検索: OR, AND, NOT, …

direct read 最適化は、複合ブール式にも適用されます。ここでは、‘ClickHouse’ OR ‘clickhouse’ を大文字と小文字を区別せずに検索します。 direct read 無効 (標準スキャン)

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

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

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

Direct read 有効 (高速な索引読み取り)

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

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

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

索引の結果を組み合わせることで、direct readクエリは34倍高速化され (0.450秒 vs 0.013秒) 、9.58 GBのカラムデータを読み取る必要もありません。このケースでは、hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) のほうが、より効率的で推奨される構文です。

テキスト索引のチューニング

現在、I/O を削減するために、テキスト索引のデシリアライズ済み Dictionary ブロック、ヘッダー、およびポスティングリスト用の cache があります。これらはそれぞれ設定 use_text_index_dictionary_cache、use_text_index_header_cache、use_text_index_postings_cache で有効化できます。デフォルトでは無効です。 cache を構成するには、以下のサーバー設定を参照してください。

サーバー設定

Dictionary ブロック cache の設定

Setting	Description	Default
text_index_dictionary_block_cache_policy	テキスト索引 Dictionary ブロック cache のポリシー名。	`SLRU`
text_index_dictionary_block_cache_size	cache の最大サイズ (バイト単位) 。	`1073741824`
text_index_dictionary_block_cache_max_entries	cache 内に保持できる、デシリアライズ済みの Dictionary ブロックの最大数。	`1'000'000`
text_index_dictionary_block_cache_size_ratio	テキスト索引 Dictionary ブロック cache における保護キューのサイズを、cache 全体のサイズに対する比率で指定します。	`0.5`

ヘッダーcache設定

設定	説明	デフォルト
text_index_header_cache_policy	テキスト索引ヘッダーcacheのポリシー名。	`SLRU`
text_index_header_cache_size	cacheの最大サイズ (バイト単位) 。	`1073741824`
text_index_header_cache_max_entries	cache内に保持できるデシリアライズ済みヘッダーの最大数。	`100'000`
text_index_header_cache_size_ratio	テキスト索引ヘッダーcache内の保護キューのサイズを、cache全体のサイズに対する比率で指定します。	`0.5`

ポスティングリスト cache の設定

Setting	Description	Default
text_index_postings_cache_policy	テキスト索引のポスティングリスト cache のポリシー名。	`SLRU`
text_index_postings_cache_size	cache の最大サイズ (バイト単位) 。	`2147483648`
text_index_postings_cache_max_entries	cache に保持できる、デシリアライズ済みポスティングの最大数。	`1'000'000`
text_index_postings_cache_size_ratio	テキスト索引ポスティングリスト cache の protected queue のサイズが、cache 全体のサイズに占める比率。	`0.5`

​テキスト索引の作成

​テキスト索引の使用

​対応している関数

​= と !=

​IN and NOT IN

​LIKE、NOT LIKE、match

​startsWith と endsWith

​hasToken and hasTokenOrNull

​hasAnyTokens and hasAllTokens

​has

​mapContains

​operator[]

​テキスト索引における Array と Map のサポート例

​Array(String) への索引作成

​Map の索引付け

​実装

​索引レイアウト

​Direct read

​例: Hacker News データセット

​1. hasToken の使用

​2. hasAnyTokens を使う

​3. hasAllTokens の使用

​4. 複合検索: OR, AND, NOT, …

​テキスト索引のチューニング

​サーバー設定

​Dictionary ブロック cache の設定

​ヘッダーcache設定

​ポスティングリスト cache の設定

​関連コンテンツ

テキスト索引の作成

テキスト索引の使用

対応している関数

`=` と `!=`

`IN` and `NOT IN`

`LIKE`、`NOT LIKE`、`match`

`startsWith` と `endsWith`

`hasToken` and `hasTokenOrNull`

`hasAnyTokens` and `hasAllTokens`

`has`

`mapContains`

`operator[]`

テキスト索引における `Array` と `Map` のサポート例

Array(String) への索引作成

Map の索引付け

実装

索引レイアウト

Direct read

例: Hacker News データセット

1. `hasToken` の使用

2. `hasAnyTokens` を使う

3. `hasAllTokens` の使用

4. 複合検索: OR, AND, NOT, …

テキスト索引のチューニング

サーバー設定

Dictionary ブロック cache の設定

ヘッダーcache設定

ポスティングリスト cache の設定

関連コンテンツ