PolarDB:IMCI フルテキストインデックス

PolarDB IMCI では、テーブル作成時に列の comment を指定するか、DDL を使用して列の comment を変更することにより、転置インデックスの作成、変更、削除が可能です。

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

検索対象の列にフルテキストインデックスを作成し、ビジネス要件に応じてトークナイザーを選択します。

1. インメモリ列指向インデックス (IMCI) を備えたテストテーブルを作成します。

   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"]  
   
   -- ngram トークナイザー
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=1");
   -- 結果： ["i ", " a", "am", "m ", " p", "po", "ol", "ar", "rd", "db"]
   
   -- jieba トークナイザー
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2");
   -- 結果： ["PolarDB"]
   
   -- jieba トークナイザー、完全モード
   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 フルテキストインデックス機能のタイトル'), 
   (17, 'データベースのタイトルとパフォーマンス最適化');
   -- title 列に「タイトル」を含むレコードをクエリ
   SELECT * FROM t1 WHERE MATCH(title) AGAINST("タイトル");

   EXPLAIN コマンドを実行して実行計画を表示します。FtsTableScan 演算子は、クエリがフルテキストインデックスを使用したことを示します。

   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("タイトル") AND id > 10;
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | 演算子                 | 名前 | 追加情報                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI 実行計画 (max_dop = 32, max_query_mem = 41230008320)     |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | 条件：(t1.id > 10)                                              |
   |  4 |     └─FtsTableScan     | t1   | ターム：("タイトル") フォールバック：(t1.title LIKE "%タイトル%") |
   +----+------------------------+------+-----------------------------------------------------------------+

   Fallback フィールドは、インデックス化されていない増分データに対して、システムが自動的に LIKE 述語を用いた補足スキャンを実行し、結果の完全性を確保することを示します。

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

   SET imci_convert_like_to_match = on;
   EXPLAIN SELECT * FROM t1 WHERE title  LIKE "%タイトル%";
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | 演算子                 | 名前 | 追加情報                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI 実行計画 (max_dop = 32, max_query_mem = 41230008320)     |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | 条件：(t1.title LIKE "%タイトル%")                                 |
   |  4 |     └─FtsTableScan     | t1   | ターム：("タイトル") フォールバック：(t1.title LIKE "%タイトル%") |
   +----+------------------------+------+-----------------------------------------------------------------+

   実行計画にも FtsTableScan 演算子が表示され、IMCI が LIKE クエリを正常に高速化したことが確認できます。

3. MATCH を LIKE に変換： フルテキストインデックスが利用できない場合、IMCI は MATCH クエリを LIKE クエリに変換し、クエリが引き続き実行可能であるとともに、可能な限り既存のインデックスを活用できるようにします。実行計画には全表スキャンが表示されます。

   SET imci_enable_query_fts_like = on;
   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("タイトル");
   +----+----------------------+------+-----------------------------------------------------------------+
   | ID | 演算子             | 名前 | 追加情報                                                      |
   +----+----------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement     |      | IMCI 実行計画 (max_dop = 32, max_query_mem = 41230008320)     |
   |  2 | └─Compute Scalar     |      |                                                                 |
   |  3 |   └─Table Scan       | t1   | 条件：(title LIKE "%タイトル%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

ステップ 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 テーブルと同じ構造および列名を持つテーブルを作成します。

   • type：トークナイザータイプ。Jieba（type 2）および ik（type 3）トークナイザーのみサポートされます。

   • word：エンティティ単語。

   • is_added：カスタム辞書への単語の追加（値 1）または削除（値 2）を指定します。

   CREATE TABLE my_fts_dict (
     `type` INT UNSIGNED NOT NULL COMMENT '2 for jieba, 3 for ik',
     `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'dict word',
     `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 2 for delete'
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts user dict';

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);
      または
      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 に設定することで、辞書の再読み込みをトリガーできます。

  SET GLOBAL imci_fts_user_dict_update = ON;

  説明

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