PolarDB:IMCI フルテキストインデックス ユーザーガイド

PolarDB IMCI では、テーブルの作成時、または DDL で列の COMMENT を変更する際に、列の COMMENT を指定することで、転置インデックスを作成、変更、または削除できます。

1. フルテキストインデックスの作成

テキスト検索が必要な列にフルテキストインデックスを作成し、ビジネスニーズに基づいてトークナイザーを選択します。

1. テストテーブルの作成：カラムストアインデックスを持つテーブルを作成します。

   CREATE TABLE t1 (
       id INT PRIMARY KEY,
       title VARCHAR(32) COMMENT "imci_fts(type=2)"
   )CHARSET utf8mb4 COMMENT 'columnar=1';

2. (オプション) フルテキストインデックスの変更：ALTER TABLE 文を使用して title 列にフルテキストインデックスを追加し、Jieba トークナイザーの検索エンジンモードを指定します。

   ALTER TABLE t1 MODIFY title VARCHAR(32) COMMENT "imci_fts(type=2,mode=0)";

3. (オプション) トークン化結果の確認：トークナイザーを選択する前に、dbms_imci.fts_tokenize 関数を使用して、さまざまなトークナイザーがテキストをどのように処理するかをプレビューできます。

   CALL dbms_imci.fts_tokenize("I am PolarDB");
   -- 結果：["i", "am", "polardb"]  
   
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=1");
   -- 結果：["i ", " a", "am", "m ", " p", "po", "ol", "ar", "rd", "db"]
   
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2");
   -- 結果：["PolarDB"]
   
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2,mode=1");
   -- 結果：["polardb"]

2. 全文検索クエリの実行

インデックスが作成されると、IMCI はバックグラウンドでインデックスを構築します。構築が完了すると、MATCH...AGAINST または最適化された LIKE 文を使用してテキストクエリを実行できます。

1. MATCH...AGAINST クエリの使用：

   -- サンプルデータの挿入
   INSERT INTO t1 VALUES 
   (16, 'polarDB full-text index feature title'), 
   (17, 'database title performance optimization');
   -- title 列に "title" が含まれる行を検索
   SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

   EXPLAIN を使用して実行計画を表示します。FtsTableScan オペレーターが表示された場合、クエリはフルテキストインデックスにヒットしています。

   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title") AND id > 10;
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | Operator               | Name | Extra Info                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | Cond：(t1.id > 10)                                              |
   |  4 |     └─FtsTableScan     | t1   | Term：("title") Fallback：(t1.title LIKE "%title%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   フォールバックは、まだインデックスが作成されていない増分データがある場合に、結果の完全性を保証するため、システムが自動的に LIKE による補足スキャンを使用することを示します。

2. LIKE クエリの高速化：既存のアプリケーションコードとの互換性を保つために、IMCI は特定の LIKE クエリを自動的に MATCH...AGAINST に変換して高速化することをサポートしています。

   SET imci_convert_like_to_match = on;
   EXPLAIN SELECT * FROM t1 WHERE title  LIKE "%title%";
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | Operator               | Name | Extra Info                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | Cond：(t1.title LIKE "%title%")                                 |
   |  4 |     └─FtsTableScan     | t1   | Term：("title") Fallback：(t1.title LIKE "%title%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   実行計画には FtsTableScan オペレーターも表示され、LIKE クエリが正常に高速化されたことを確認できます。

3. MATCH から LIKE への変換：フルテキストインデックスが存在しない場合、IMCI は既存のインデックスを可能な限り活用してクエリを正しく実行するため、MATCH を LIKE に変換することがあります。この場合、実行計画にはフルテーブルスキャンが表示されます。

   SET imci_enable_query_fts_like = on;
   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");
   +----+----------------------+------+-----------------------------------------------------------------+
   | ID | Operator             | Name | Extra Info                                                      |
   +----+----------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar     |      |                                                                 |
   |  3 |   └─Table Scan       | t1   | Cond：(title LIKE "%title%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

3. インデックスの管理と監視

インデックスのビルドステータスとメタデータをクエリし、不要になったインデックスを削除します。

1. インデックスのビルド進行状況とステータスの監視：

   -- すべての転置インデックスを表示
   SHOW imci indexes fulltext;
   SELECT * FROM information_schema.imci_fts_indexes;
   
   -- 特定の転置インデックスを表示
   SHOW imci indexes fulltext FOR [db_name].[table_name];
   SELECT * FROM information_schema.imci_fts_indexes 
   WHERE schema_name='[db_name]' AND table_name='[table_name]';
   
   -- 特定の転置インデックスのメタデータを表示
   SELECT * FROM information_schema.imci_fts_index_metas 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- 特定の転置インデックスのセグメントデータを表示
   SELECT * FROM information_schema.imci_fts_index_segs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- 特定の転置インデックスのカラムストアデータを表示
   SELECT * FROM information_schema.imci_fts_index_packs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';

2. フルテキストインデックスの削除：列のフルテキストインデックスが不要になった場合は、列の COMMENT を変更することで削除できます。

   ALTER TABLE t1 MODIFY title VARCHAR(255) COMMENT 'imci_fts(enable=0)';

4. 主要なシステム変数

カスタム辞書テーブルの作成

1. まず、my_fts_dict テーブルと同じ構造を持つテーブルを定義します。列名は my_fts_dict テーブルの列名と一致する必要があります。

   • type：トークナイザーのタイプ。Jieba トークナイザー (type 2) と IK トークナイザー (type 3) のみをサポートします。

   • word：辞書の単語。

   • is_added：カスタム辞書に単語を追加する (値 1) か、削除する (値 2) かを指定します。

   CREATE TABLE my_fts_dict (
     `type` INT UNSIGNED NOT NULL COMMENT '2 は Jieba、3 は IK',
     `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '辞書の単語',
     `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 は追加、2 は削除'
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='IMCI FTS ユーザー辞書';

2. カスタム辞書自体は InnoDB テーブルです。読み取りおよび書き込み操作は、通常のテーブルと同じです。

   1. 単語を追加します。

      INSERT INTO my_fts_dict VALUES(2, "PolarDB", 1)

   2. 単語を削除します。

      INSERT INTO my_fts_dict(type, word, is_added) VALUES(2, "PolarDB", 2);
      or
      DELETE FROM my_fts_dict WHERE type=2 AND word = "PolarDB";

カスタム辞書の設定

• カスタム辞書テーブルを作成した後、imci_fts_user_dict_table パラメーターを設定します。たとえば、test/my_fts_dict' です。

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• imci_fts_user_dict_table がすでに設定されている場合は、imci_fts_user_dict_update を ON に設定して、imci_fts_user_dict_table で指定されたカスタム辞書の再読み込みをトリガーすることもできます。

  SET GLOBAL imci_fts_user_dict_update = ON;

  説明

  新しいカスタム辞書は、それ以降に構築される転置インデックスにのみ有効になります。既存の転置インデックスに適用するには、転置インデックスを再構築する必要があります。

カスタムシノニムテーブルの作成

まず、my_synonym_dict テーブルと同じ構造のテーブルを定義します。カラム名は my_synonym_dict テーブルのカラム名と一致する必要があります。

• word ：シノニムリスト。単語はカンマで区切ります。

• type ：シノニムタイプ。

  • 0 は、リスト内のすべての単語が互いにシノニムであることを示します。例えば、"db,database,polardb" は "db" <=> "database" <=> "polardb" を意味します。"db"、"database"、または "polardb" を検索すると、"db"、"database"、および "polardb" に一致するすべてのデータが返されます。

  • 1 は、リストの最初の単語がプライマリタームであり、他の単語がそのシノニムであることを示します。例えば、"db,database,polardb" は "db" => "database", "polardb" を意味します。"db" を検索すると、"db"、"database"、および "polardb" に一致するすべてのデータが返されます。ただし、"database" を検索すると、"database" に一致するデータのみが返されます。

CREATE TABLE my_synonym_dict (
  `word` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'カンマ区切りのシノニム',
  `type` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0は等価、1はマッピング'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='IMCI FTS シノニム';

カスタムシノニムテーブル自体は InnoDB テーブルです。読み取りおよび書き込み操作は、通常のテーブルと同様です。

• 単語の追加

  INSERT INTO my_synonym_dict VALUES("db,database,polardb", 0);
  INSERT INTO my_synonym_dict VALUES("imci,clickhouse,duckdb", 1);

• 単語の削除

  DELETE FROM my_synonym_dict WHERE type=1 AND word = "imci,clickhouse,duckdb";

カスタムシノニムの設定

カスタムシノニムテーブルを作成した後、imci_fts_synonym_table パラメーターを設定します。例：test/my_synonym_dict。ここで、test はデータベース名、my_synonym_dict はテーブル名です。

SET GLOBAL imci_fts_synonym_table = 'test/my_synonym_dict';

imci_fts_synonym_table がすでに設定されている場合は、imci_fts_synonym_update を ON に設定して、imci_fts_synonym_table で指定されたカスタムシノニムの再読み込みをトリガーすることもできます。

SET GLOBAL imci_fts_synonym_update = ON;

カスタムストップワードテーブルの作成

まず、my_stop_words テーブルと同じ構造のテーブルを定義します。カラム名は my_stop_words のカラム名と一致している必要があります。

• word：ストップワード。

• is_added：カスタム辞書に単語を追加する (値 1) か、削除する (値 0) かを指定します。

CREATE TABLE my_stop_words (
  `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'ストップワード',
  `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 は追加、0 は削除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts stop word';

カスタムストップワードテーブル自体は InnoDB テーブルです。読み取り/書き込み操作は、通常のテーブルと同様です。

• 単語の追加

  INSERT INTO my_stop_words VALUES("am", 1);
  INSERT INTO my_stop_words VALUES("chinese", 1);

• 単語の削除

  INSERT INTO my_stop_words(word, is_added) VALUES("chinese", 0);
  -- または
  DELETE FROM my_stop_words WHERE is_added=1 AND word = "chinese";

カスタムストップワードの設定

カスタムストップワードテーブルを作成した後、imci_fts_stop_word_table パラメーターを設定します。例：test/my_stop_words。ここで、test はデータベース名、my_stop_words はテーブル名です。

SET GLOBAL imci_fts_stop_word_table = 'test/my_stop_words';

imci_fts_stop_word_table がすでに設定されている場合は、imci_fts_stop_word_update を ON に設定して、imci_fts_stop_word_table で指定されたカスタムストップワードの再読み込みを実行することもできます。

SET GLOBAL imci_fts_stop_word_update = ON;