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 運算元，表示查詢已命中全文索引。

   mysql> 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 以進行加速。
   

   mysql> SET imci_convert_like_to_match = on;
   mysql> 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，執行計畫顯示為全表掃描。

   mysql> SET imci_enable_query_fts_like = on;
   mysql> 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)';

四：核心配置參數說明