云原生数据库 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;

  说明

  新自定义词典只对后面新构建的倒排索引生效，若需要对已经构建好的倒排索引生效则需要重建倒排索引。

创建自定义同义词表

首先需要定义一个与 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;

创建自定义停止词表

首先需要定义一个与 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;