为何MariaDB简单MATCH-AGAINST查询未使用FULLTEXT索引？

表结构 #

CREATE TABLE `lname` (
  `lnameid` binary(16) NOT NULL,
  `lid` binary(16) NOT NULL,
  `name` varchar(200) NOT NULL,
  `namerank` int(11) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`lnameid`),
  KEY `lid` (`lid`),
  FULLTEXT KEY `name` (`name`),
  CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`slid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

• 表中约有230,000行数据，name列所有行均为非NULL值。

执行的查询及EXPLAIN结果 #

执行的查询语句：

EXPLAIN SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms FROM lname;

返回的EXPLAIN结果：

+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
|    1 | SIMPLE      | lname           | ALL  | NULL          | NULL | NULL    | NULL | 228370 |       |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+

用户疑问 #

看到possible_keys为NULL（且Extra为空），是否意味着我的FULLTEXT索引未被使用？该查询执行起来较慢。使用环境为Linux下的MariaDB 10.5.19版本。

是的，这确实说明你的FULLTEXT索引没有被使用。当前查询执行的是全表扫描（type: ALL），需要遍历所有22万多行数据来计算每个name值与搜索词的匹配得分，这就是查询慢的原因。

索引未生效的原因 #

FULLTEXT索引的核心作用是快速定位到包含目标搜索词的行，而不是为全表计算匹配得分做优化。你的查询没有添加任何过滤条件，数据库无法通过FULLTEXT索引缩小扫描范围，只能全表遍历计算每一行的得分。

优化方案 #

1. 仅获取有匹配的行
   如果你的需求是只保留有匹配得分（得分>0）的记录，可以修改查询，加入过滤条件：

SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms 
FROM lname
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;

这样数据库会利用FULLTEXT索引快速筛选出匹配的行，避免全表扫描，大幅提升查询速度。

2. 预计算得分
   如果确实需要获取所有行的得分，可以将得分预计算并存储到表中的score列：

• 通过触发器在插入/更新name列时自动计算并更新score值；
• 或者通过定时任务定期批量更新全表的score值。
  之后查询直接读取score列即可，无需实时计算匹配得分。

3. 优化内存配置
   检查MariaDB的innodb_buffer_pool_size配置，确保缓冲池足够大以缓存整个lname表，减少磁盘IO开销，提升全表扫描的速度。

内容的提问来源于stack exchange，提问作者andynewman