MATCH AGAINST

説明

この式は全文インデックス上でテキスト検索を行うために使用されます。AGAINST は検索文字列を受け取り、文字セットの比較方法に従ってインデックス内で検索を実行します。テーブル内の各行データに対して、MATCH の戻り値は検索文字列と行内データとの関連性、つまり検索文字列内のテキストとデータテーブル内のテキストとの類似度を示します。

構文

MATCH (column_set) AGAINST (query_expr [mode_flag])

column_set:
    column_name [, column_name ...]

mode_flag:
    IN NATURAL LANGUAGE MODE
    | IN BOOLEAN MODE

パラメータの説明

mode_flag

• IN NATURAL LANGUAGE MODE：デフォルト値で、自然言語検索モードを使用して検索することを指定します。このモードでは、クエリ式（query_expr）を形態素解析して単語（token）の集合を生成し、インデックス内の単語（token）と照合して検索を実行します。クエリ式とインデックス内の単語のいずれか一方が一致すれば、マッチングに成功したとみなされます。同時に、単語頻度に基づく方法（Okapi BM25）を用いて、マッチングに成功した行の関連性をランキング（ソート）します。

  デフォルトまたは IN NATURAL LANGUAGE MODE 指定子を指定した場合、MATCH AGAINST は NATURAL LANGUAGE モードを使用して全文検索を実行します。NATURAL LANGUAGE モードでは、AGAINST は検索文字列を受け取り、文字セットの比較方式に従ってインデックス内を検索します。テーブル内の各行データに対して、MATCH の戻り値は検索文字列と行内データの関連度、つまり検索文字列内のテキストとデータテーブル内のテキストの類似度を表します。

• IN BOOLEAN MODE：ブールモードを使用して検索することを指定します。現在のバージョンでは、最も一般的な3種類のブール演算子と、それらの演算子のネストがサポートされています。具体的には以下の通りです：

  • +：AND を表し、共通部分集合を示します。

  • -：否定を表し、差集合を示します。

  空の演算子：単独で使用する場合、OR を表し、和集合を示します。例えば、A B は A OR B を意味します。演算子を混在させると、存在する文の関連性は高まりますが、OR の意味は失われます。例えば、+A B の場合、A が必須であり、文中の A と B の関連性を計算します。

  • ()：演算子のネストを表します。外側に演算子がない場合、OR の意味を持ちます。例えば、+A (ネストされた句) の場合、A またはネストされた句のいずれか一方が必要であることを示します。

    説明

    OceanBaseデータベースV4.3.5では、V4.3.5 BP1以降のバージョンから IN BOOLEAN MODE がサポートされています。

    例：

    1. 出力される文にはcomputerが含まれていなければなりません。

       SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
       

    2. 出力される文にはcomputerが含まれていなければならず、weatherは含まれていてはなりません。

       SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);
       

    3. 出力される文にはcomputerが含まれていなければならず、oceanbaseの方がよりマッチします。

       SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
       

使用方法

MATCH AGAINST 式は、テキストマッチングを示すだけでなく、SQLのさまざまな句に現れる場合、いくつかの他の意味も含みます：

• プロジェクション関連性：

  • フルテキスト検索は、単語頻度に基づくランキングをサポートしており、MATCH AGAINST 式を使用して関連性のプロジェクションを表現できます。
  • 関連性とは、マッチするデータ行と MATCH AGAINST のクエリ（Query）との間の関連度を示します。
  • 関連性は 0 以上の DOUBLE 型データであり、0 はデータ行がクエリ（Query）と無関係であることを示し、数値が大きいほど関連性が強いことを意味します。

• フィルタリングセマンティクス：

  • WHERE 句に現れる MATCH AGAINST 式は、他のすべてのフィルタ条件と AND で結合されている場合、フィルタリングセマンティクスを含み、マッチしないデータ行をフィルタリングすることを示します。

    例：

    SELECT id, digest, detail
    FROM t1
    WHERE MATCH (detail) AGAINST ('oceanbase');
    

    これは次のように等価です：

    SELECT id, digest, detail
    FROM t1
    WHERE MATCH (detail) AGAINST ('oceanbase') > 0;
    

• ソートセマンティクス：

  • WHERE 句において、他のフィルタ条件と AND で結合された MATCH AGAINST 式は、ソートセマンティクスを含みます。
  • フルテキスト検索の結果を MATCH AGAINST の関連順位ランキングで降順にソートすることを示します。
  • 複数の MATCH AGAINST 式が存在する場合、最初の MATCH AGAINST の関連性に従ってソートされます。

  例：

  SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
  FROM t1
  WHERE MATCH (detail) against ('oceanbase');
  

  これは次のように等価です：

  SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
  FROM t1
  WHERE MATCH (detail) AGAINST ('oceanbase')
  ORDER BY relevance DESC;
  

テキスト検索の実行方法

• SQL 内の MATCH AGAINST にフィルタリングセマンティクスが含まれている場合、OceanBaseデータベースは全文インデックスをスキャンして MATCH AGAINST 式を計算した後、テーブルにアクセスしてクエリを実行できます。

• SQL 内の MATCH AGAINST にフィルタリングセマンティクスが含まれていない場合、OceanBaseデータベースは他の二次インデックスを使用してスキャンとフィルタリングをサポートし、主キーに基づいてランダムに全文インデックスにアクセスして MATCH AGAINST 式を計算してクエリを実行します。

• SQL の WHERE 句に MATCH AGAINST と AND で結合された複数のフィルタ条件が含まれており、他の二次インデックスにヒットできる場合、OceanBaseデータベースはコストに基づいて適切なインデックスを選択してスキャンを試みます。

• フルテキストインデックスをスキャンして MATCH AGAINST を計算する際、クエリに LIMIT が含まれている場合、OceanBaseデータベースはパフォーマンスを向上させるために、トップ k の計算を全文インデックスのスキャンにダウンコンパイルして実行することを試みます。

• クエリに OR で結合された MATCH AGAINST 述語が含まれている場合、自動的にIndex Merge計画の生成が試みられ、最終的にIndex Merge計画が選択されるかどうかはコスト競争の結果によって決定されます。

  説明

  OceanBaseデータベースV4.3.5では、V4.3.5 BP1バージョンからIndex Merge計画の生成がサポートされています。

例

1. サンプルテーブル test_tbl1 を作成し、全文インデックス ft_idx1_test_tbl1 と ft_idx1_test_tbl1、およびインデックス idx_test_tbl1 も同時に作成します。

   CREATE TABLE test_tbl1(id INT, ref_no INT, digest VARCHAR(512), detail VARCHAR(4096),
       FULLTEXT INDEX ft_idx1_test_tbl1(detail),
       FULLTEXT INDEX ft_idx2_test_tbl1(digest, detail),
       INDEX idx_test_tbl1 (id));
   

2. テーブル test_tbl1 にテストデータを挿入します。

   INSERT INTO test_tbl1 VALUES
       (1, 1234, 'fulltext', 'Try text retrieval with OceanBase fulltext index'),
       (2, 2345, 'log', 'OceanBase can halp with log analysis'),
       (3, 3456, 'order', 'Simple text retrieval scan will return result set in order of descending ranking in OceanBase'),
       (4, 4567, 'ranking', 'OceanBase will ranking relevance to query for matched result set'),
       (5, 5678, 'filter', 'Using text retrieval as a filter condition would be probably more efficient');
   

3. MATCH AGAINST 式を使用して関連性を投影します。

   SELECT id, digest, detail, MATCH (detail) AGAINST ('oceanbase') AS relevance
   FROM test_tbl1;
   

   実行結果は次のとおりです：

   +------+----------+-----------------------------------------------------------------------------------------------+--------------------+
   | id   | digest   | detail                                                                                        | relevance          |
   +------+----------+-----------------------------------------------------------------------------------------------+--------------------+
   |    1 | fulltext | Try text retrieval with OceanBase fulltext index                                              | 0.2989130434782609 |
   |    2 | log      | OceanBase can halp with log analysis                                                          | 0.3142857142857143 |
   |    3 | order    | Simple text retrieval scan will return result set in order of descending ranking in OceanBase |  0.240174672489083 |
   |    4 | ranking  | OceanBase will ranking relevance to query for matched result set                              | 0.2849740932642488 |
   |    5 | filter   | Using text retrieval as a filter condition would be probably more efficient                   |                  0 |
   +------+----------+-----------------------------------------------------------------------------------------------+--------------------+
   5 rows in set
   

4. MATCH AGAINST 式を使用してフィルタリングを実行します。

   SELECT id, digest, detail
   FROM test_tbl1
   WHERE MATCH (detail) AGAINST ('oceanbase');
   

   実行結果は次のとおりです：

   +------+----------+-----------------------------------------------------------------------------------------------+
   | id   | digest   | detail                                                                                        |
   +------+----------+-----------------------------------------------------------------------------------------------+
   |    2 | log      | OceanBase can halp with log analysis                                                          |
   |    1 | fulltext | Try text retrieval with OceanBase fulltext index                                              |
   |    4 | ranking  | OceanBase will ranking relevance to query for matched result set                              |
   |    3 | order    | Simple text retrieval scan will return result set in order of descending ranking in OceanBase |
   +------+----------+-----------------------------------------------------------------------------------------------+
   4 rows in set
   

5. MATCH AGAINST 式を使用して検索とソートを実行します。

   SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
   FROM test_tbl1
   WHERE MATCH (detail) AGAINST ('oceanbase');
   

   または

   SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
   FROM test_tbl1
   WHERE MATCH (detail) AGAINST ('oceanbase')
   ORDER BY relevance DESC;
   

   実行結果は次のとおりです：

   +------+----------+--------------------+
   | id   | digest   | relevance          |
   +------+----------+--------------------+
   |    2 | log      | 0.3142857142857143 |
   |    1 | fulltext | 0.2989130434782609 |
   |    4 | ranking  | 0.2849740932642488 |
   |    3 | order    |  0.240174672489083 |
   +------+----------+--------------------+
   4 rows in set
   

テキスト検索実行計画の例

1. MATCH AGAINST にはフィルタリングの意味が含まれており、全文インデックス ft_idx1_test_tbl1 を使用してスキャンします。

   EXPLAIN SELECT id, digest, detail
           FROM test_tbl1
           WHERE MATCH (detail) AGAINST ('oceanbase')
               AND id = 3;
   

   実行結果は次のとおりです：

   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                  |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | =============================================================================                                                                               |
   | |ID|OPERATOR             |NAME                        |EST.ROWS|EST.TIME(us)|                                                                               |
   | -----------------------------------------------------------------------------                                                                               |
   | |0 |SORT                 |                            |1       |30          |                                                                               |
   | |1 |└─TEXT RETRIEVAL SCAN|test_tbl1(ft_idx1_test_tbl1)|1       |30          |                                                                               |
   | =============================================================================                                                                               |
   | Outputs & filters:                                                                                                                                          |
   | -------------------------------------                                                                                                                       |
   |   0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter(nil), rowset=16                                                                |
   |       sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase'), DESC])                                                                                       |
   |   1 - output([test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest], [MATCH(test_tbl1.detail) AGAINST('oceanbase')]), filter([test_tbl1.id = 3]), rowset=16 |
   |       access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0)                                            |
   |       is_index_back=true, is_global_index=false, filter_before_indexback[false],                                                                            |
   |       calc_relevance=true, match_expr(MATCH(test_tbl1.detail) AGAINST('oceanbase')),                                                                        |
   |       pushdown_match_filter(MATCH(test_tbl1.detail) AGAINST('oceanbase'))                                                                                   |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   15 rows in set
   

2. MATCH AGAINST にはフィルタリングの意味が含まれていますが、通常のセカンダリインデックス idx_test_tbl1 にヒットし、かつ idx_test_tbl1 インデックスのスキャンコストが低いため、インデックス idx_test_tbl1 を使用してスキャンします。

   EXPLAIN SELECT id
           FROM test_tbl1
           WHERE MATCH (detail) AGAINST ('oceanbase simple text retreival scan on fulltext index')
               AND id = 3;
   

   実行結果は次のとおりです：

   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                  |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ======================================================================                                                                                      |
   | |ID|OPERATOR          |NAME                    |EST.ROWS|EST.TIME(us)|                                                                                      |
   | ----------------------------------------------------------------------                                                                                      |
   | |0 |SORT              |                        |1       |103         |                                                                                      |
   | |1 |└─TABLE RANGE SCAN|test_tbl1(idx_test_tbl1)|1       |103         |                                                                                      |
   | ======================================================================                                                                                      |
   | Outputs & filters:                                                                                                                                          |
   | -------------------------------------                                                                                                                       |
   |   0 - output([test_tbl1.id]), filter(nil), rowset=16                                                                                                        |
   |       sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase simple text retreival scan on fulltext index'), DESC])                                          |
   |   1 - output([test_tbl1.id], [MATCH(test_tbl1.detail) AGAINST('oceanbase simple text retreival scan on fulltext index')]), filter([MATCH(test_tbl1.detail)  |
   |       AGAINST('oceanbase simple text retreival scan on fulltext index')]), rowset=16                                                                        |
   |       access([test_tbl1.__pk_increment], [test_tbl1.id]), partitions(p0)                                                                                    |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                           |
   |       range_key([test_tbl1.id], [test_tbl1.__pk_increment]), range(3,MIN ; 3,MAX),                                                                          |
   |       range_cond([test_tbl1.id = 3]), has_functional_lookup=true                                                                                            |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   16 rows in set
   

3. MATCH AGAINST にはフィルタリングの意味が含まれておらず、結果セットをフィルタリングすることができません。また、全文インデックスを使用したスキャンもできないため、メインテーブルをスキャンします。

   EXPLAIN SELECT id, digest, detail
           FROM test_tbl1
           WHERE MATCH (detail) AGAINST ('oceanbase')
               OR id = 3;
   

   実行結果は次のとおりです：

   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                  |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ====================================================                                                                                                        |
   | |ID|OPERATOR       |NAME     |EST.ROWS|EST.TIME(us)|                                                                                                        |
   | ----------------------------------------------------                                                                                                        |
   | |0 |TABLE FULL SCAN|test_tbl1|5       |503         |                                                                                                        |
   | ====================================================                                                                                                        |
   | Outputs & filters:                                                                                                                                          |
   | -------------------------------------                                                                                                                       |
   |   0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter([MATCH(test_tbl1.detail) AGAINST('oceanbase') OR test_tbl1.id = 3]), rowset=16 |
   |       access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0)                                            |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                           |
   |       range_key([test_tbl1.__pk_increment]), range(MIN ; MAX)always true, has_functional_lookup=true                                                        |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------+
   11 rows in set
   

4. MATCH AGAINST にはフィルタリングの意味が含まれておらず、結果セットをフィルタリングすることができません。また、全文インデックスを使用したスキャンもできないため、インデックス idx_test_tbl1 にヒットし、インデックス idx_test_tbl1 を使用してスキャンします。

   EXPLAIN SELECT id
           FROM test_tbl1
           WHERE MATCH (detail) AGAINST ('oceanbase')
               OR id = 3;
   

   実行結果は次のとおりです：

   +------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                   |
   +------------------------------------------------------------------------------------------------------------------------------+
   | ===================================================================                                                          |
   | |ID|OPERATOR       |NAME                    |EST.ROWS|EST.TIME(us)|                                                          |
   | -------------------------------------------------------------------                                                          |
   | |0 |TABLE FULL SCAN|test_tbl1(idx_test_tbl1)|5       |503         |                                                          |
   | ===================================================================                                                          |
   | Outputs & filters:                                                                                                           |
   | -------------------------------------                                                                                        |
   |   0 - output([test_tbl1.id]), filter([MATCH(test_tbl1.detail) AGAINST('oceanbase') OR test_tbl1.id = 3]), rowset=16          |
   |       access([test_tbl1.__pk_increment], [test_tbl1.id]), partitions(p0)                                                     |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                            |
   |       range_key([test_tbl1.id], [test_tbl1.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, has_functional_lookup=true |
   +------------------------------------------------------------------------------------------------------------------------------+
   11 rows in set
   

5. MATCH AGAINST にはフィルタリングの意味が含まれており、対応する全文インデックス ft_idx1_test_tbl1 にヒットしてスキャンされます。同時に、LIMIT はトップkのダウンドラフトインデックススキャンとして機能します。

   EXPLAIN SELECT id, digest, detail
           FROM test_tbl1
           WHERE MATCH (detail) AGAINST ('oceanbase')
               LIMIT 3;
   

   実行結果は次のとおりです：

   +------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                       |
   +------------------------------------------------------------------------------------------------------------------+
   | ===========================================================================                                      |
   | |ID|OPERATOR           |NAME                        |EST.ROWS|EST.TIME(us)|                                      |
   | ---------------------------------------------------------------------------                                      |
   | |0 |TEXT RETRIEVAL SCAN|test_tbl1(ft_idx1_test_tbl1)|4       |31          |                                      |
   | ===========================================================================                                      |
   | Outputs & filters:                                                                                               |
   | -------------------------------------                                                                            |
   |   0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter(nil), rowset=16                     |
   |       access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0) |
   |       is_index_back=true, is_global_index=false,                                                                 |
   |       calc_relevance=true, match_expr(MATCH(test_tbl1.detail) AGAINST('oceanbase')),                             |
   |       pushdown_match_filter(MATCH(test_tbl1.detail) AGAINST('oceanbase')),                                       |
   |       sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase'), DESC]), limit(3), offset(nil), with_ties(false)   |
   +------------------------------------------------------------------------------------------------------------------+
   13 rows in set
   

関連ドキュメント

全文検索