PolarDB：IMCI全文索引使用說明

PolarDB IMCI 可以在建立表時指定列comment或通過DDL修改列comment來建立或修改或刪除倒排索引。

一：建立全文索引

為需要進行文本檢索的列建立全文索引，並根據業務需求選擇分詞器。

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"]

二：執行全文檢索索引查詢

索引建立後，IMCI 會在後台完成構建。構建完成後，即可通過 MATCH...AGAINST 或最佳化的 LIKE 語句執行文字查詢。

1. 使用MATCH...AGAINST查詢：

   -- 插入樣本資料
   INSERT INTO t1 VALUES 
   (16, 'polarDB全文索引功能title'), 
   (17, '資料庫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%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   Fallback表示對於尚未被索引的增量資料，系統會自動使用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%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

三：管理與監控索引

查詢索引的構建狀態、中繼資料資訊，並在不再需要時刪除索引。

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

四：核心配置參數說明

建立自訂字典表

1. 首先需要定義一個與my_fts_dict表結構相同的表，列名稱需要和my_fts_dict表保持一致。

   • type：分詞器類型，僅支援jieba分詞器（類型為2）和 ik 分詞器（類型為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，來觸發重新載入imci_fts_user_dict_table指定的自訂字典。

  SET GLOBAL imci_fts_user_dict_update = ON;

  說明

  新自訂字典只對後面新構建的倒排索引生效，若需要對已經構建好的倒排索引生效則需要重建倒排索引。