mysql使用全文索引提高查询性能的策略

mysql全文索引仅支持char、VARCHAR和TEXT类型，需InnoDB（5.6+）或MyISAM引擎，且必须用MATCH() AGaiNST()查询；停用词、最小词长及缓存延迟是常见失效原因。

全文索引只对 CHAR、VARCHAR 和 TEXT 字段有效

MySQL 的 FULLTEXT 索引不支持数值、日期或二进制类型字段。如果在 int 或 DATETIME 列上执行 ALTER table ... ADD FULLTEXT，会静默失败或报错 Error 1214 (HY000): The used table type doesn't support FULLTEXT indexes（尤其在非 InnoDB/MyISAM 表上）。确认存储引擎是关键：SHOW CREATE TABLE your_table 查看引擎类型；InnoDB 从 5.6+ 支持全文索引，MyISAM 更早但不支持事务。

实操建议：

• 建表时显式指定 ENGINE=InnoDB，避免依赖默认引擎
• 对长文本字段（如 article_content）建索引前，先用 CHAR_LENGTH() 检查平均长度，过短（10MB）都不适合全文检索
• 不要对含大量 html 标签或 jsON 字符串的字段直接建全文索引——需先清洗或提取纯文本

MATCH() AGAINST() 是唯一触发全文索引的查询方式

写 WHERE content LIKE '%关键词%' 或 WHERE content regexp '关键词' 不会走全文索引，哪怕该列有 FULLTEXT 索引。必须用 MATCH(col) AGAINST('xxx' [IN NATURAL LANGUAGE MODE | IN Boolean MODE]) 才能命中。

两种模式差异明显：

• NATURAL LANGUAGE MODE（默认）：自动计算相关性得分，返回 MATCH()...AGAINST() 作为额外列，适合模糊匹配场景，但不支持通配符和逻辑操作符
• BOOLEAN MODE：支持 +（必须包含）、-（排除）、*（词干匹配），例如 AGAINST('+mysql -performance' IN BOOLEAN MODE)，但不返回相关性分数
• 注意：布尔模式下，小于 ft_min_word_len（默认 4）的词会被忽略，修改后需重建索引

select id, title, MATCH(content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 10;

停用词和最小词长导致“搜不到”是常见陷阱

MySQL 内置停用词表（如 the、is、in）不会被索引，且默认只索引长度 ≥ 4 的词。这意味着搜 'go'、'AI' 或 'c++' 会返回空结果，不是 SQL 写错了，而是根本没进索引。

验证方法：

• 查当前停用词表：SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;（InnoDB）
• 查最小词长：SHOW VARIABLES LIKE 'ft_min_word_len';
• 临时绕过停用词：在布尔模式中用双引号强制匹配短词，如 AGAINST('"go"' IN BOOLEAN MODE)（仅当 ft_min_word_len=2 且已重建索引才生效）

修改配置需重启 MySQL 并重建全文索引：ALTER TABLE articles DROP INDEX ft_content; ALTER TABLE articles ADD FULLTEXT(content);

全文索引更新延迟与 DML 性能权衡

InnoDB 全文索引不是实时更新的——新增/修改记录后，相关索引项会先进入缓存（innodb_ft_cache_size），等缓存满或事务提交后才合并到主索引。这意味着刚插入的数据可能查不到，尤其在高并发写入场景下。

调优要点：

• 增大 innodb_ft_cache_size（默认 32M）可减少合并频次，但会增加内存占用
• 频繁更新 + 实时检索需求强？考虑用外部搜索引擎（elasticsearch）替代，MySQL 全文索引更适合读多写少、允许秒级延迟的场景
• 避免在大表上频繁执行 OPTIMIZE TABLE——它会重建全文索引，锁表时间长，且 InnoDB 通常不需要

真正影响性能的，往往不是“有没有建全文索引”，而是“有没有避开停用词限制”、“有没有误用 LIKE 替代 MATCH”、以及“有没有意识到索引更新不是原子实时的”。这些细节比语法本身更决定落地效果。