Create Fulltext Index

MatrixOne 支持全文索引，允许用户对表中的文本数据进行高效的全文检索。全文索引适用于包含 char、varchar、text、json 和 datalink 数据类型的列，特别适合对英语以及 CJK（中文、日文、韩文）语言的文本数据进行优化搜索。

语法说明

MatrixOne 支持全文索引，允许用户对表中的文本数据进行高效的全文检索。全文索引适用于包含 char、varchar、text、json 和 datalink 数据类型的列，特别适合对英语以及 CJK（中文、日文、韩文）语言的文本数据进行优化搜索。

语法结构

启用全文索引

全文索引默认启用。你可以直接创建和使用全文索引，无需额外配置。

选择 boolean 模式的相关性算法

可选 BM25 或 TF-IDF：

set ft_relevancy_algorithm= BM25|TF-IDF;--默认使用 TF-IDF 算法进行检索。

创建全文搜索引

CREATE FULLTEXT INDEX <index_name> 
ON <table_name> (col1, col2, ...) 
[WITH PARSER (default | ngram | json | gojieba)];

• index_name: 你希望为全文索引指定的名称。

• table_name: 要在其上创建索引的表名。

• (col1, col2, …): 包含在全文索引中的列列表。

• WITH PARSER: 可选。指定用于索引的解析器。可用的选项有：

  • default: 默认解析器。

  • ngram: 支持 n-gram 分词的解析器。

  • json: 专门用于 JSON 数据的解析器。

  • gojieba: 用于中文（CJK）文本分词的解析器，基于结巴（Jieba）分词库。该解析器对中文、日文和韩文文本进行词级别的分词，相比 ngram 能提供更准确的中文全文搜索。支持包括布尔短语匹配在内的所有搜索模式。

WITH PARSER gojieba

gojieba 解析器使用结巴（Jieba）中文分词库对中文（CJK）文本进行词级别的分词，而非字符级别或 ngram 级别的分词。这为全文搜索和相关性排名生成了语义上更有意义的词元。

gojieba 解析器适用于所有全文搜索模式：

• 自然语言模式（Natural Language Mode）：对结巴分词后的词语执行自然语言搜索。

• 布尔模式（Boolean Mode）：支持布尔运算符（+、-、*、~、""），并使用结巴分词后的词元。布尔模式下的中文短语查询会通过 gojieba 重新分词，使其能够匹配索引中按词存储的行。

你可以通过独立的 CREATE FULLTEXT INDEX 语句或在 CREATE TABLE 定义中内联使用 gojieba：

-- 独立创建索引
CREATE FULLTEXT INDEX ftidx ON src (body, title) WITH PARSER gojieba;

-- 在 CREATE TABLE 中内联
CREATE TABLE src (
    id BIGINT PRIMARY KEY,
    body VARCHAR(200),
    title TEXT,
    FULLTEXT (title, body) WITH PARSER gojieba
);

Gojieba 中文搜索示例：

DROP DATABASE IF EXISTS gojieba_demo;
CREATE DATABASE gojieba_demo;
USE gojieba_demo;

CREATE TABLE src (
    id BIGINT PRIMARY KEY,
    body VARCHAR(200),
    FULLTEXT(body) WITH PARSER gojieba
);
INSERT INTO src VALUES
    (0, 'SGB11型号的检验报告在对素材文件进行搜索时'),
    (1, '使用全文索引会肥胖的原因都是因为摄入脂肪多导致的吗测试背景说明');

-- 自然语言搜索
SELECT id FROM src WHERE MATCH(body) AGAINST('肥胖的原因都是因为摄入脂肪多导致的吗' IN NATURAL LANGUAGE MODE);

-- 带中文短语的布尔搜索
SELECT id FROM src WHERE MATCH(body) AGAINST('+SGB11型号的检验报告' IN BOOLEAN MODE);

DROP DATABASE gojieba_demo;

使用全文索引进行搜索

MATCH (col1, col2, ...) AGAINST (expr [search_modifier]);

• (col1, col2, …): 要搜索的列。

• expr: 搜索表达式或关键字。

• search_modifier: 可选。指定搜索模式。可用的选项有：

  • IN NATURAL LANGUAGE MODE: 执行自然语言搜索。

  • IN BOOLEAN MODE: 执行布尔搜索，允许使用布尔运算符（如 +, -, *）。

与 JOIN 联合使用

全文索引的重写可应用于 INNER JOIN 查询。当参与 INNER JOIN 的表存在全文索引，且 MATCH() AGAINST() 条件引用了该表中与索引匹配的列时，优化器可以将查询重写为使用全文索引扫描。

• 包含全文索引的表可以位于 INNER JOIN 的任意一侧。

• 支持对同一扫描使用多个全文过滤条件，也支持对 JOIN 的两个子节点分别使用全文过滤条件。

• 支持嵌套的 INNER JOIN。

• 跨表 MATCH() 条件（将不同表的列混合在单个 MATCH 中）不会被重写。

• 外连接（LEFT JOIN、RIGHT JOIN）在当前阶段不会被重写。在外连接查询中使用 MATCH() AGAINST() 不会利用全文索引。

DROP DATABASE IF EXISTS ft_join_demo;
CREATE DATABASE ft_join_demo;
USE ft_join_demo;

CREATE TABLE articles (
    id VARCHAR(191) PRIMARY KEY,
    base_id VARCHAR(191),
    title VARCHAR(512),
    body LONGTEXT,
    FULLTEXT INDEX ft_idx(title, body)
);

CREATE TABLE categories (
    id VARCHAR(191) PRIMARY KEY,
    name VARCHAR(191),
    note TEXT,
    FULLTEXT INDEX cat_ft_idx(name, note)
);

INSERT INTO articles VALUES
    ('a1', 'b1', 'hello title', 'hello body'),
    ('a2', 'b2', 'other title', 'other body');

INSERT INTO categories VALUES
    ('b1', 'Category One', 'category hello note'),
    ('b2', 'Category Two', 'category other note');

-- 在左表上使用全文过滤条件的 INNER JOIN
SELECT a.id, c.name
FROM articles a JOIN categories c ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
ORDER BY a.id;

-- 在右表上使用全文过滤条件的 INNER JOIN
SELECT a.id, c.name
FROM categories c JOIN articles a ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
ORDER BY a.id;

-- 在 JOIN 两侧都使用全文过滤条件
SELECT a.id, c.name
FROM articles a JOIN categories c ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
  AND MATCH(c.name, c.note) AGAINST('category')
ORDER BY a.id;

DROP DATABASE ft_join_demo;

成员资格下推过滤器

全文搜索支持成员资格下推过滤器，可以在执行全文匹配之前使用关系谓词预过滤行。此优化将额外的 WHERE 子句条件应用于构建候选文档 ID 集合，然后用其过滤全文索引扫描。

下推根据源表主键类型使用不同的内部过滤器结构：

• 整数主键，小 ID 范围：使用密集 cbitmap 进行高效过滤。

• 整数主键，宽 ID 跨度（> 2^23）：使用紧凑的 CRoaring bitset 处理稀疏但大范围的 ID。

• Varchar（非整数）主键：使用 CBloomFilter，该过滤器是概率性的，可能会产生少量假阳性。

要启用基于 Bloom filter 的 varchar 主键表下推，将系统变量 fulltext_bloom_filter_pushdown 设置为 1（默认为 0）：

SET fulltext_bloom_filter_pushdown = 1;

下推对查询结果透明。谓词生成构建过滤器的候选文档 ID 集合，然后全文匹配只在这些候选行上操作。

成员资格下推示例：

DROP DATABASE IF EXISTS ft_membership_demo;
CREATE DATABASE ft_membership_demo;
USE ft_membership_demo;

CREATE TABLE docs (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(50) NOT NULL
);
INSERT INTO docs VALUES
    (1, 'Introduction to Machine Learning', 'machine learning is a subset of artificial intelligence', 'tech'),
    (2, 'Deep Learning Fundamentals',       'deep learning uses neural networks to learn from data', 'tech'),
    (3, 'Cooking with Machine Learning',    'using machine learning to recommend recipes', 'food'),
    (4, 'The Art of French Cooking',        'french cooking techniques from around the world', 'food');

CREATE FULLTEXT INDEX ftidx ON docs (title, body);

-- category 谓词在全文匹配之前预过滤行
SELECT id, title, category FROM docs
WHERE MATCH(title, body) AGAINST('machine learning') AND category = 'tech'
ORDER BY id;

DROP DATABASE ft_membership_demo;

示例

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    english_text TEXT,       -- 英文文本
    chinese_text TEXT,        -- 中文文本
    json_data JSON           -- JSON 数据
);
INSERT INTO example_table (id, english_text, chinese_text, json_data) VALUES
(1, 'Hello, world!', '你好世界', '{"name": "Alice", "age": 30}'),
(2, 'This is a test.', '这是一个测试', '{"name": "Bob", "age": 25}'),
(3, 'Full-text search is powerful.', '全文搜索很强大', '{"name": "Charlie", "age": 35}');

--使用 default 解析器创建全文索引
mysql> CREATE FULLTEXT INDEX idx_english_text ON example_table (english_text);
Query OK, 0 rows affected (0.03 sec)

--使用 ngram 解析器创建全文索引
mysql> CREATE FULLTEXT INDEX idx_chinese_text ON example_table (chinese_text) WITH PARSER ngram;
Query OK, 0 rows affected (0.02 sec)

--使用 json 解析器创建全文索引
mysql> CREATE FULLTEXT INDEX idx_json_data ON example_table (json_data) WITH PARSER json;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table example_table;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                               |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_table | CREATE TABLE `example_table` (
  `id` int NOT NULL,
  `english_text` text DEFAULT NULL,
  `chinese_text` text DEFAULT NULL,
  `json_data` json DEFAULT NULL,
  PRIMARY KEY (`id`),
 FULLTEXT `idx_english_text`(`english_text`),
 FULLTEXT `idx_chinese_text`(`chinese_text`) WITH PARSER ngram,
 FULLTEXT `idx_json_data`(`json_data`) WITH PARSER json
) |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--查询包含 "world" 的英文文本
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('world');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.02 sec)

--查询包含 "你好" 的中文文本
mysql> SELECT * FROM example_table WHERE MATCH(chinese_text) AGAINST('你好');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.01 sec)

--查询 JSON 数据中包含 "Alice" 的记录
mysql> SELECT * FROM example_table WHERE MATCH(json_data) AGAINST('Alice');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.01 sec)

--使用布尔模式进行搜索

mysql> set ft_relevancy_algorithm= "BM25";
Query OK, 0 rows affected (0.00 sec)

-- 1. 使用 "+" 运算符：必须包含 "test"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('+test' IN BOOLEAN MODE);
+------+-----------------+--------------------+----------------------------+
| id   | english_text    | chinese_text       | json_data                  |
+------+-----------------+--------------------+----------------------------+
|    2 | This is a test. | 这是一个测试       | {"age": 25, "name": "Bob"} |
+------+-----------------+--------------------+----------------------------+
1 row in set (0.01 sec)

-- 2. 使用 "-" 运算符：必须不包含 "This"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('+test -This' IN BOOLEAN MODE);
Empty set (0.00 sec)

-- 3. 使用 "*" 运算符：匹配以 "pow" 开头的单词
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('pow*' IN BOOLEAN MODE);
+------+-------------------------------+-----------------------+--------------------------------+
| id   | english_text                  | chinese_text          | json_data                      |
+------+-------------------------------+-----------------------+--------------------------------+
|    3 | Full-text search is powerful. | 全文搜索很强大        | {"age": 35, "name": "Charlie"} |
+------+-------------------------------+-----------------------+--------------------------------+
1 row in set (0.01 sec)

-- 4. 使用双引号 "" 运算符：匹配整个短语 "search is powerful"
mysql> mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('"search is powerful"' IN BOOLEAN MODE);
+------+-------------------------------+-----------------------+--------------------------------+
| id   | english_text                  | chinese_text          | json_data                      |
+------+-------------------------------+-----------------------+--------------------------------+
|    3 | Full-text search is powerful. | 全文搜索很强大        | {"age": 35, "name": "Charlie"} |
+------+-------------------------------+-----------------------+--------------------------------+
1 row in set (0.02 sec)