IMCI全文索引使用說明 - PolarDB

適用範圍

您的叢集版本需滿足以下條件：

MySQL 8.0.1，且核心小版本需為8.0.1.1.52及以上版本。
MySQL 8.0.2，且核心小版本需為8.0.2.2.32及以上版本。

文法說明

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

文法格式

建立表時定義全文索引：

CREATE TABLE table_name (
    column_name Data_Type COMMENT "imci_fts(type=VALUE [,KEY=VALUE])"
) COMMENT 'columnar=1';

使用ALTER修改列以添加/修改索引：
```
ALTER TABLE table_name MODIFY column_name Data_Type COMMENT "imci_fts(type=VALUE [,KEY=VALUE])";
```
重要
修改COMMENT可能會觸發倒排索引的重建，大表操作建議在業務低峰期執行。

使用ALTER修改列以刪除索引：

ALTER TABLE table_name MODIFY column_name Data_Type COMMENT 'imci_fts(enable=0)';

參數說明：

需在表級COMMENT中設定'columnar=1'以建立 IMCI 列存表，全文索引通過列的COMMENT配置，格式為imci_fts(TYPE=VALUE[, KEY=VALUE]...)，其中TYPE為必填，KEY為可選，多個KEY用英文逗號分隔。

分詞器類型（type 參數）：

分詞器	`type` 值	說明
token	0 （預設）	按空格、標點等非字母數字字元進行分詞，適用於英文或格式化文本。
ngram	1	按固定長度切分字元（由 `len` 參數控制），適用於任意語言的模糊比對。
jieba	2	基於詞庫的中文分詞器，適用於中文語義化搜尋。
ik	3	另一款常用的中文分詞器。
json	4	通過 JSONPath 運算式（需配合 `expr` 參數）從 JSON 欄位中提取內容建立索引。
whole	5	不進行切分，將整個列作為詞項，主要用於等值(如=或in等)加速。
mysql ngram	6	相容 MySQL 的 ngram 分詞器。

配置參數（KEY-VALUE）說明：

參數名 (KEY)	預設值	取值說明	適用分詞器類型（`type`）
enable	1	`1`：建立倒排索引（預設） `0`：刪除倒排索引	所有
type	0	指定分詞器類型（見上表）	所有
len	-	`ngram` 分詞器的分詞長度，取值範圍 `[1, 256)`	`type=1` (ngram)、`type=6` (mysql ngram)
mode	0	分詞器模式： jieba (`type=2`): `0`：精準模式（預設） `1`：全模式 `2`：搜尋引擎模式 ik (`type=3`): `0`：智能模式（預設） `1`：最細粒度模式 json (`type=4`): `0`：舊版本數組模式（預設） `1`：數組模式 `2`：索引值模式，如 `".id"`、`"[].id"` 等	`type=2, 3, 4`
score	0	是否支援排序： `0`：禁止產生詞頻（TF）、文檔頻率（DF）等，忽略排序（預設） `1`：開啟，支援 `MATCH ... AGAINST` 的相關性評分排序	所有
seg_size	0	指定倒排索引段大小： `0`：使用系統變數 `imci_fts_build_segment_size` 的值其他值：指定具體段大小	所有
pack_cnt_min	0	倒排索引構建的資料單元最小數目： `0`：使用系統變數 `imci_fts_build_packcnt_min` 的值其他值：指定具體段大小	所有
pack_cnt_max	0	倒排索引構建的資料單元最大數目： `0`：使用系統變數 `imci_fts_build_packcnt_max` 的值其他值：指定具體段大小	所有
stop_word	0	是否支援停用詞： `0`：不支援（預設） `1`：支援	所有
case_sensitive	0	是否大小寫敏感： `0`：大小寫不敏感（預設） `1`：大小寫敏感 `2`：根據列的 `collate` 來決定（相容 MySQL 模式） `3`：根據列的 `collate` 來決定（完全由 `collate` 決定）	所有
phrase	0	是否支援記錄位置資訊用於短語查詢： `0`：不記錄位置資訊（預設） `1`：記錄位置資訊	所有
synonym	0	是否支援同義字： `0`：不支援（預設） `1`：支援，查詢模式 `2`：支援，索引模式（需要重建倒排索引）	所有

使用樣本

一：建立全文索引

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

建立測試表：建立一個包含列存索引的表。

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

（可選）修改全文索引：通過ALTER TABLE語句將title列添加全文索引，並指定使用 jieba 分詞器的搜尋引擎模式。
```
ALTER TABLE t1 MODIFY title VARCHAR(32) COMMENT "imci_fts(type=2,mode=0)";
```

（可選）驗證分詞效果：在選擇分詞器前，可使用 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 語句執行文字查詢。

使用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補充掃描，保證結果完整性。

加速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 查詢已被成功加速。

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%")                                    |
+----+----------------------+------+-----------------------------------------------------------------+

三：管理與監控索引

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

監控索引構建進度和狀態：

-- 查看所有倒排索引
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]';

刪除全文索引：如果不再需要某個列的全文索引，可以通過修改列的COMMENT將其刪除。
```
ALTER TABLE t1 MODIFY title VARCHAR(255) COMMENT 'imci_fts(enable=0)';
```

四：核心配置參數說明

參數名	層級	說明
imci_enable_fts	Global	是否允許在列存節點上建立倒排索引。 ON（預設）：開啟，支援字串與JSON等類型。 OFF：關閉。
imci_enable_fts_query	Global / Session	是否允許在列存節點上使用倒排索引。 ON：開啟。 OFF（預設）：關閉。
imci_fts_build_pack_cnt_min	Global	倒排索引構建時控制每一倒排索引的列存資料區塊最小數目。取值範圍為：0-8192，預設值為8，其中0表示暫時不構建。
imci_fts_build_pack_cnt_max	Global	倒排索引構建時控制每一倒排索引的列存資料區塊最大數目。取值範圍為：0-8192，預設值為128。
imci_fts_build_segment_size	Global	倒排索引構建時控制每一倒排索引的段大小。取值範圍為：0-4294967295，預設值為536870912（512MB），單位位元組。
imci_fts_lru_cache_capacity	Global	倒排索引字典緩衝空間。取值範圍：[DBNodeClassMemory1/10~DBNodeClassMemory1/2]，預設值為[DBNodeClassMemory*10/100]。
imci_enable_fts_pruner	Global / Session	是否開啟倒排索引預過濾最佳化。 ON（預設）：開啟。 OFF：關閉。
imci_convert_like_to_match	Global / Session	是否開啟將`like`轉換為`match`並利用倒排索引進行加速。 ON：開啟。 OFF（預設）：關閉。 ONLY_MATCH：開啟且忽略`like`運算式；
imci_enable_query_fts_like	Global / Session	是否開啟將`match`轉換為`like`。 ON：開啟。 OFF（預設）：關閉。
imci_enable_match_expr_fallback	Global / Session	是否開啟倒排索引的尚未構建增量資料查詢。 ON（預設）：開啟。 OFF：關閉。
imci_fts_build_fts_cnt	Global	倒排索引構建的並發數。取值範圍：0-512，預設值：4。說明當前參數僅適用於以下版本： MySQL 8.0.1，且核心小版本需為8.0.1.1.54及以上版本。 MySQL 8.0.2，且核心小版本需為8.0.2.2.34及以上版本。
imci_fts_user_dict_table	Global	配置全文索引的自訂字典，格式：庫名/表名。說明當前參數僅適用於以下版本： MySQL 8.0.1，且核心小版本需為8.0.1.1.54及以上版本。 MySQL 8.0.2，且核心小版本需為8.0.2.2.34及以上版本。
imci_fts_user_dict_update	Global	是否重新載入`imci_fts_user_dict_table`指定的自訂字典。 ON（預設）：開啟。 OFF：關閉。說明當前參數僅適用於以下版本： MySQL 8.0.1，且核心小版本需為8.0.1.1.54及以上版本。 MySQL 8.0.2，且核心小版本需為8.0.2.2.34及以上版本。
imci_convert_json_overlap_to_match	Global/Session	控制是否開啟`json_overlaps`使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_convert_json_contains_to_match	Global/Session	控制是否開啟`json_contains`使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_convert_json_extract_to_match	Global/Session	控制是否開啟`json_extract`使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_convert_equal_to_match	Global/Session	控制是否開啟`=`運算式使用等值索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_convert_in_to_match	Global/Session	控制是否開啟`IN`運算式使用等值索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_convert_ne_to_match	Global/Session	控制是否開啟`!=`運算式使用等值索引加速功能。 ON：開啟。 OFF（預設）：關閉。
imci_enable_fts_synonym	Global / Session	是否支援同義字。 ON（預設）：開啟。 OFF：關閉。
imci_enable_fts_parse_term_with_and	Global / Session	是否支援查詢字串分詞後用 `AND` 求值。 ON（預設）：開啟。 OFF：關閉，OR 求值。
imci_enable_fts_parse_term_with_phrase	Global / Session	是否支援 `BOOLEAN` MODE 下查詢字串分詞後嚴格依賴位置。 ON：開啟。 OFF（預設）：關閉。
imci_fts_stop_word_table	Global	配置全文索引的自訂停止詞，格式：`庫名/表名`。詳見自訂停止詞。
imci_fts_stop_word_update	Global	是否重新載入`imci_fts_stop_word_table`指定的自訂停止詞。 ON（預設）：開啟。 OFF：關閉。
imci_fts_synonym_table	Global	配置全文索引的自訂同義字，格式：`庫名/表名`。詳見自訂同義字。
imci_fts_synonym_update	Global	是否重新載入`imci_fts_synonym_table`指定的自訂同義字。 ON（預設）：開啟。 OFF：關閉。

重要

層級為 GLOBAL 的參數無法通過命令列直接修改，僅可通過控制台設定；在命令列中執行時，預設視為 SESSION 層級操作。

自訂字典

IMCI全文索引功能支援通過自訂表格來實現自訂字典功能，以擷取更貼近業務實際情境的分詞結果。

建立自訂字典表

首先需要定義一個與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';

自訂字典本身是一張InnoDB表，讀寫操作與普通表一樣。

添加詞條：

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

刪除詞條：

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;
```
說明
新自訂字典只對後面新構建的倒排索引生效，若需要對已經構建好的倒排索引生效則需要重建倒排索引。

自訂同義字

IMCI 全文索引功能支援通過自訂表格來實現自訂同義字功能。

建立自訂同義字表

首先需要定義一個與 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 'comma-separated synonyms',
  `type` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 for equivalent, 1 for mapping'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts synonym';

自訂字典本身是一張 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;

說明

若 synonym 為索引模式（如 COMMENT "imci_fts(type=0 synonym=1)"），更新同義字後只對倒排索引新增部分生效，若需要全部生效需要重建全文索引。

自訂停止詞

IMCI 全文索引功能支援通過自訂表格來實現自訂停止詞功能。

建立自訂停止詞表

首先需要定義一個與 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 'stop word',
  `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 0 for delete'
) 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;

說明

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

常見問題

Q1：如何選擇合適的分詞器？

英文或格式化文本：使用預設的type=0(token)，按空格和標點符號分詞。
中文精確搜尋：使用type=2(jieba) 或type=3(ik) 的預設模式。
JSON 內容檢索：使用 type=4 (json) 並配合 expr 參數指定要索引的 JSON 路徑。
可以通過dbms_imci.fts_tokenize函數預覽不同分詞器對文本的處理效果。

Q2：為什麼我的查詢沒有使用全文索引？

請確認 SET imci_enable_fts_query = ON; 已執行。這是最常見的原因。
檢查EXPLAIN的輸出，確認是否有FtsTableScan運算元。如果沒有，可能是查詢模式不匹配或系統評估後認為全表掃描成本更低。

Q3: LIKE查詢和MATCH...AGAINST有何區別？

LIKE '%keyword%' 在沒有全文索引時會導致全表掃描，效能極差。即使被 IMCI 加速，其功能也相對單一。
MATCH...AGAINST是專為全文檢索索引設計的文法，不僅效能高，未來還支援更複雜的布爾查詢、相關性排序等進階功能。建議新開發業務優先使用MATCH...AGAINST。