多MATCH-AGAINST子句导致MariaDB未使用FULLTEXT索引求助

问题描述 #

现有两张InnoDB表：

表lname建表语句 #

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` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

表sl建表语句 #

CREATE TABLE `sl` (
  `lid` binary(16) NOT NULL,
  `sid` int(11) NOT NULL,
  `laid` varchar(20) NOT NULL,
  `definition` text DEFAULT NULL,
  PRIMARY KEY (`lid`),
  KEY `sid` (`sid`),
  KEY `laid` (`laid`),
  FULLTEXT KEY `definition` (`definition`),
  CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
  CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

执行以下查询并查看执行计划：

EXPLAIN
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
       MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM   sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
OR     MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;

执行计划显示两张表的FULLTEXT索引均未被使用，sl表触发全表扫描：

+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
| id   | select_type | table           | type | possible_keys | key           | key_len | ref                              | rows   | Extra       |
+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
|    1 | SIMPLE      | sl              | ALL  | PRIMARY       | NULL          | NULL    | NULL                             | 130437 |             |
|    1 | SIMPLE      | lname           | ref  | lid           | lid           | 16      | lid                              | 1      | Using where |
+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+

但当WHERE子句仅保留其中一个MATCH条件时，对应的FULLTEXT索引能正常生效。使用MariaDB版本为10.5.19。

原因分析 #

MariaDB（以及MySQL）的优化器在处理跨表OR条件的全文查询时存在限制：

• FULLTEXT索引是表级别的，每个索引只能覆盖单表的全文检索条件。
• 当WHERE子句通过OR连接来自两个不同表的MATCH条件时，优化器无法同时利用两个表的全文索引生成执行计划——因为优化器只能选择一个表作为驱动表，而OR条件要求同时满足任一表的检索结果，无法通过单一索引覆盖整个查询逻辑，最终只能退化为全表扫描驱动表，再关联另一张表进行过滤。

解决方案 #

方案1：拆分查询用UNION ALL合并结果 #

将原查询拆分为两个独立的子查询，分别利用各自的FULLTEXT索引，再通过UNION ALL合并（第二个子查询增加过滤条件避免重复数据）：

SELECT 
  MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0

UNION ALL

SELECT 
  MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0
AND MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) = 0;

方案2：用UNION自动去重 #

如果不需要保留重复的结果集，直接使用UNION（会自动去重，性能略低于UNION ALL）：

SELECT 
  MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0

UNION

SELECT 
  MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;

方案3：强制利用单个FULLTEXT索引 #

如果其中一张表的全文检索结果集远小于另一张表，可以调整驱动表并强制使用对应FULLTEXT索引，减少扫描行数。例如优先使用lname表的全文索引：

EXPLAIN
SELECT 
  MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM lname FORCE INDEX (name)
INNER JOIN sl ON sl.lid = lname.lid
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0
OR MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0;

这种方式至少能用到一张表的FULLTEXT索引，避免全表扫描所有数据。

补充说明 #

MariaDB 10.5版本的优化器尚未支持跨表OR条件下的多FULLTEXT索引联合使用，后续版本可能会优化该逻辑，但目前拆分查询是最可靠的解决方式。

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