MariaDB 中如何使用全文索引？

推荐答案

在 MariaDB 中，全文索引（Full-Text Index）用于加速对文本数据的搜索操作。以下是创建和使用全文索引的步骤：

1. 创建全文索引：

   • 在创建表时定义全文索引：

     CREATE TABLE articles (
         id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
         title VARCHAR(200),
         body TEXT,
         FULLTEXT (title, body)
     );

   • 在已有表上添加全文索引：

     ALTER TABLE articles ADD FULLTEXT (title, body);

2. 使用全文索引进行搜索：

   • 使用 MATCH() 和 AGAINST() 函数进行全文搜索：

     SELECT * FROM articles
     WHERE MATCH(title, body) AGAINST('search term');

3. 全文索引的配置：

   • 可以通过修改 ft_min_word_len 和 ft_max_word_len 系统变量来调整全文索引的最小和最大单词长度。

本题详细解读

1. 全文索引的创建

全文索引可以加速对文本数据的搜索操作，特别是在处理大量文本数据时。全文索引可以在创建表时定义，也可以在已有表上添加。

• 创建表时定义全文索引：

  CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title, body)
  );

  在这个例子中，FULLTEXT (title, body) 表示对 title 和 body 列创建全文索引。

• 在已有表上添加全文索引：

  ALTER TABLE articles ADD FULLTEXT (title, body);

  如果表已经存在，可以使用 ALTER TABLE 语句添加全文索引。

2. 使用全文索引进行搜索

全文索引的主要用途是加速文本搜索。MariaDB 提供了 MATCH() 和 AGAINST() 函数来进行全文搜索。

• 基本搜索：

  SELECT * FROM articles
  WHERE MATCH(title, body) AGAINST('search term');

  这个查询会返回所有 title 或 body 列中包含 'search term' 的记录。

• 布尔模式搜索：

  SELECT * FROM articles
  WHERE MATCH(title, body) AGAINST('+search -term' IN BOOLEAN MODE);

  布尔模式允许使用 + 和 - 等操作符来指定必须包含或排除的单词。

3. 全文索引的配置

MariaDB 提供了一些系统变量来配置全文索引的行为：

• ft_min_word_len：指定全文索引的最小单词长度，默认值为 4。
• ft_max_word_len：指定全文索引的最大单词长度，默认值为 84。

可以通过以下命令查看和修改这些变量：

SHOW VARIABLES LIKE 'ft_%';
SET GLOBAL ft_min_word_len = 3;

修改这些变量后，需要重建全文索引才能生效：

REPAIR TABLE articles QUICK;

4. 注意事项

• 全文索引仅适用于 MyISAM 和 InnoDB 存储引擎。
• 全文索引不支持前缀索引。
• 全文索引的搜索性能受数据量和索引配置的影响。

通过合理使用全文索引，可以显著提高文本搜索的效率。