做后端这几年我在 MySQL 全文索引上踩过的坑十个手指头都数不过来。最早接触MATCH() AGAINST()是给一个内容检索接口做优化表里文章数据二十多万条原来用LIKE %关键词%搜标题和正文接口平均耗时两秒多数据库 CPU 经常飙高。后来换成了全文索引花了两天时间把官方文档和各种报错记录啃了一遍最终把查询压到了几十毫秒但过程相当曲折——建索引用错上下文直接报错、中文内容搜出来是空的、短词被静默忽略、相关度排序不符合直觉……每一步都有对应的坑。这篇文章就把整个踩坑过程完整记录下来索引怎么建、MATCH() AGAINST()的三种模式怎么用、每条报错信息的含义和解决办法、以及几个关键系统参数的真实作用。无论你是刚听说全文索引还是已经在生产环境被中文搜索问题折磨过这篇都能给你一个可以直接抄作业的答案。1. 全文索引到底解决了什么问题先理解 LIKE 为什么不行1.1 我遇到的真实场景和性能瓶颈我负责的系统里有一张文章表字段不算多核心就是title和content两个文本列业务上需要支持用户输入任意关键词同时搜索标题和正文再按某种热度排序返回。早期数据量只有几千条的时候接口写得非常简单SELECT id, title FROM article WHERE title LIKE %关键词% OR content LIKE %关键词% ORDER BY click_count DESC;几千条数据跑起来没什么感觉顶多多花几十毫秒。但数据涨到二十万条之后这个查询开始原形毕露——加了ORDER BY click_count DESC之后连索引都没法走直接全表加文件排序。我做过一次压测单个关键词请求的平均响应时间在 2.3 秒左右数据库 CPU 瞬时能到 40% 以上。更难受的是这种查询一旦并发上来连接数马上被打满整个服务都跟着抖。1.2 LIKE 的索引失效原理为什么普通索引救不了你很多人第一反应是给title和content建普通索引觉得这样 LIKE 就能快。这里有个非常经典的误区MySQL 的 B 树索引对 LIKE 的支持是有限制的只有通配符不在开头时才能走索引。-- 这种写法可以走索引 SELECT id FROM article WHERE title LIKE MySQL%; -- 这种写法索引直接失效 SELECT id FROM article WHERE title LIKE %MySQL%;因为 B 树是按字段值的完整前缀排序的%MySQL%这种模式不知道字符串开头是什么只能把整列数据全部取出来挨个匹配。而在搜索场景里用户输入的关键词基本都出现在句子中间位置绝大多数查询都必须写成双侧通配符。也就是说只要业务是搜关键词而不是查前缀普通索引就一点忙都帮不上。全文索引不一样它的底层是倒排索引核心思路是先分词再建立关键词到文档的映射关系。查询的时候直接根据关键词定位到包含它的行不需要逐行扫描。这就像查字典一样你按拼音找字而不是从第一页翻到最后一页。2. 全文索引的建立方式与底层逻辑从建表到分词器2.1 建表建索引的完整语法MySQL 的全文索引支持在CHAR、VARCHAR、TEXT类型列上建立建表时可以直接声明CREATE TABLE article ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, FULLTEXT KEY ft_idx_title_content (title, content) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果是已经存在的表用ALTER TABLE补加索引或者用CREATE FULLTEXT INDEX都可以ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram; CREATE FULLTEXT INDEX ft_idx_content ON article (content) WITH PARSER ngram;这里提前说一个全文索引最重要的限制MATCH() AGAINST()里指定的列必须和全文索引定义的列完全一致包括顺序。索引建在(title, content)上那么查询也必须写MATCH(title, content)如果只写MATCH(content)MySQL 会直接报 1191 错误。这个我在后面的踩坑部分会再展开。2.2 倒排索引的核心原理先分词再反查理解全文索引绕不开倒排索引这个词。普通索引是记录 → 字段值的正向映射而倒排索引是反过来的它把字段内容切分成一个个词元然后建立词元 → 记录列表的映射。举个例子假设表里有三条记录idtitle1MySQL 全文索引实战2数据库索引优化指南3MySQL 索引原理总结分词之后倒排索引大致长这样词元记录列表mysql1, 3全文1索引1, 2, 3数据库2优化2查询MATCH(title) AGAINST(MySQL)时直接查mysql这个词元拿到记录列表[1, 3]再回表取数据。整个过程走的是词元查找时间复杂度跟表的总行数基本无关所以数据量越大全文索引相对 LIKE 的优势越明显。2.3 ngram 解析器中文检索的命门这里必须花大篇幅讲 ngram。MySQL 默认的全文索引解析器是按空格、标点一类分隔符来分词的这个机制对英文很自然因为英文单词天然用空格隔开。但中文不一样一句话里字与字之间没有空格默认解析器会把整句话当成一个超长词元存进去比如MySQL全文索引实战会被当成一个整体。后面你搜索引它发现索引表里根本没有索引这个独立词元自然就搜不到东西。解决办法就是给全文索引指定WITH PARSER ngram。ngram 解析器会把文本按固定长度连续切词。比如ngram_token_size 2时我们中国会被切成我们 们中 中国每个长度为 2 的连续字符组合都作为一个词元。这样做的好处是中文不再需要预置词典坏处是会产生大量无意义的交叉词元索引体积会明显变大。中文场景下ngram_token_size一般建议设成 2既能覆盖绝大多数双字词又不至于让索引膨胀到不可接受。提示ngram_token_size是建索引之前就要确定的全局参数它的值会影响所有使用 ngram 解析器的全文索引。修改后必须删除旧索引重建否则不生效。3. MATCH() AGAINST() 三种模式从自然语言到布尔表达式3.1 自然语言模式最简单也最容易被阈值坑先看最基础的写法SELECT id, title, MATCH(title, content) AGAINST(数据库) AS score FROM article WHERE MATCH(title, content) AGAINST(数据库);这种不写模式参数的写法实际是IN NATURAL LANGUAGE MODE也就是自然语言模式。它会返回一个相关度分数score数值越大代表匹配度越高。自然语言模式有两个隐藏规则需要注意。第一分词结果里长度小于innodb_ft_min_token_size的词元不参与匹配和检索InnoDB 默认值是 3。比如你搜PHP这种三个字符以内的词默认配置下会被静默忽略不会报错但就是没有结果。第二如果某个词在超过 50% 的行里都出现这个词会被当作没区分度的词直接忽略同样静默返回空结果。这个规则坑过非常多的人——你的关键词明明有数据查出来却是空集。后面我会专门写这个 50% 阈值的处理办法。3.2 布尔模式搜索语法的完全形态IN BOOLEAN MODE是实际项目里用得最多的模式因为它提供了完整的检索语法-- 必须包含数据库不能包含MySQL SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 -MySQL IN BOOLEAN MODE); -- MySQL必须出现在开头且优化可以加分 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(MySQL 优化 IN BOOLEAN MODE); -- 短词 任意字符通配 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据* IN BOOLEAN MODE); -- 精确短语 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(全文索引 IN BOOLEAN MODE);几个常见运算符的作用我用表格列一下运算符含义示例必须包含MySQL-必须排除-MySQL提高权重MySQL降低权重MySQL*通配符只能放词尾数据* 精确短语全文索引( )表达式分组(数据库 索引)~降低相关度类似软排除~MySQL布尔模式还有一个巨大的优点它不受 50% 阈值限制。如果一个词在大量行里出现自然语言模式可能搜不到但布尔模式照样能返回结果。所以生产环境里很多团队干脆完全用布尔模式反正语法灵活还能避免被阈值坑到。需要注意布尔模式返回的相关度分数没有自然语言模式那么有参考价值很多时候用它排序并不理想。如果你想靠相关度排序建议用自然语言模式如果你需要精确控制匹配规则再考虑布尔模式。3.3 查询扩展模式相关推荐的好东西搜索的坏东西第 三种模式是WITH QUERY EXPANSION也叫查询扩展。它会自动做两次检索第一次用原始关键词搜出一些结果然后从这些结果里提取高频词用这些新词再做一次搜索从而把语义相关但可能不包含原始关键词的文档也带出来。SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 WITH QUERY EXPANSION);这个模式适合找相似内容的场景比如给你正在看的文章推荐相关文章。但如果你做的是站内搜索对精准度要求高我不建议用它因为查询扩展会显著放大召回范围很容易带出一堆看起来有点关系但用户根本不想看的记录。4. 踩坑全记录从报错到空结果的完整排查链路这一部分是整篇文章的重心。下面每个坑都是我实际遇到过的我把报错信息、排查思路、最终解决办法都写清楚你可以按图索骥。4.1 坑一ERROR 1191索引列匹配不一致我第一次建完索引直接跑查询就撞上了这个错误ERROR 1191 (HY000): Cant find FULLTEXT index matching the column list当时的索引是建在(title, content)上的我写的查询却是SELECT id, title FROM article WHERE MATCH(content) AGAINST(数据库);问题就出在列列表不一致。MySQL 要求MATCH()中的列列表必须与全文索引定义的列完全匹配列的数量、顺序都不能变。我一开始以为只要其中一列有索引就行结果被教育了。解决办法很简单改成SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库);排查这种问题有个快速办法直接执行SHOW INDEX FROM article;查看FULLTEXT类型索引对应的列清单然后对照检查MATCH()的列。4.2 坑二ERROR 1210参数个数或上下文不对ERROR 1210 (HY000): Incorrect arguments to MATCH这个报错通常出现在两种场景。一种是你给MATCH()传了错误数量的参数另一种是某些表达式里不允许直接用MATCH() AGAINST()比如你想在ORDER BY里对MATCH()的结果做某种运算上下文不对就会报 1210。我踩到的具体原因是把MATCH()放在了GROUP BY子句里试图按相关度分组MySQL 不支持这种用法。遇到 1210 不要慌先检查两点第一MATCH()里是不是只写了索引列第二MATCH()是否用在了 MySQL 限制的上下文环境中。正常情况下WHERE、ORDER BY、SELECT列表里使用都没问题但GROUP BY和某些嵌套子查询里就要小心。4.3 坑三中文搜索出来是空的LIKE 却正常这个坑最折磨人。表里明明有大量包含数据库的文章用LIKE %数据库%能搜出一堆但换成MATCH(title, content) AGAINST(数据库)之后结果是零。我当时排查了很久一度怀疑是字符集问题后来才发现根因是没指定 ngram 解析器。默认解析器对中文的处理方式是整句作为一个词元索引里根本没有数据库这个独立词元。检查方法也很简单直接看全文索引的解析器SELECT INDEX_NAME, INDEX_TYPE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME article AND INDEX_TYPE FULLTEXT;确认索引没带 ngram 之后需要重建索引ALTER TABLE article DROP INDEX ft_idx_title_content; ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram;对于已经存在的表ALTER TABLE ... ADD FULLTEXT INDEX ... WITH PARSER ngram在 MySQL 5.7 和 8.0 都是可以直接执行的。改完之后记得用下面的语句验证分词结果SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;这个表能看到实际生成的词元。如果能看到数据库或者数据库这类词元说明解析器已经生效。4.4 坑四短词被静默忽略搜索结果莫名缺失还有一个典型的搜不到场景是搜索词太短。InnoDB 全文索引默认innodb_ft_min_token_size 3也就是说长度小于 3 的词元在建立索引时就被忽略了。比如用户搜PHP或Go如果按默认配置这些短词根本不会进入索引查询自然没结果。需要特别说明的是ngram 解析器下生效的是ngram_token_size参数innodb_ft_min_token_size主要影响默认解析器。如果你用ngram_token_size 2双字词是可以正常被索引和检索的但单个字依然搜不到。解决办法是把参数调小甚至改成 1[mysqld] innodb_ft_min_token_size 1 ngram_token_size 2改完必须重启 MySQL然后对所有全文索引做一次重建OPTIMIZE TABLE article;如果表比较大这个过程会比较慢建议在业务低峰期操作。另外调低ngram_token_size会让索引体积成倍增加如果只是偶尔需要单字搜索也可以考虑在布尔模式里用通配符补救未必非要全局修改参数。4.5 坑五默认停用词把常见词全过滤了MySQL 从设计之初就内置了一份英文停用词表比如a、an、are、is这些高频无意义词在索引阶段就被排除掉了。InnoDB 环境下由开关控制SHOW VARIABLES LIKE innodb_ft_enable_stopword;如果这个值是ON那默认的停用词表就会生效。中文场景下像的、了、是这类单字词如果长度满足条件理论上也可能被过滤。更麻烦的是当你的业务关键词恰好是停用词表里的英文单词时比如搜in或it结果必然为空。处理停用词有两种常用方式。第一种是直接关闭停用词功能[mysqld] innodb_ft_enable_stopword OFF第二种是自定义停用词表通过innodb_ft_server_stopword_table指定一张自定义表把真正需要过滤的词放进去其余全部放行。我做生产配置时一般选择自定义表因为全量关闭停用词会把很多没有检索价值的词也放进索引白白增加体积和噪音。4.6 坑六50% 阈值规则最隐蔽的空结果原因这个是让我记忆最深的一个坑。有一回线上反馈某个热门关键词搜不到结果我用自然语言模式复现确实一条都没返回。当时既有数据LIKE 也能查到索引也建了分词也正常百思不得其解。后来翻官方文档才发现是 50% 阈值规则如果一个搜索词出现在超过 50% 的行里MySQL 会认为这个词没有区分度直接当作停用词处理自然语言模式不返回任何结果。我当时那个关键词是系统近期的热门标签确实出现在大量文章里刚好命中这个规则。解决方式有两个。一是改用布尔模式因为这个规则只对自然语言模式生效SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(热门词 IN BOOLEAN MODE);二是在业务上想办法限制范围让命中比例降下来比如强制加上时间范围条件。第一种方式明显更直接这也是我在生产环境里更推荐布尔模式的原因之一它能绕开一堆隐性的过滤规则。4.7 坑七布尔模式下的特殊字符被当成运算符最后一个高频问题来自用户输入。用户在搜索框里输的内容是不可控的可能带、-、、*、这类特殊字符。当你把这些字符串原样拼进AGAINST()时MySQL 会按照布尔运算符去解析导致结果完全不符合预期。比如用户想搜C实际传入的搜索词是CMySQL 会把当作必须包含运算符后面跟的是空词整个查询行为会变得非常诡异。解决办法是对输入做清洗和转义。我通常的做法是把业务中不支持的运算符字符先移除再对必须保留的字符做转义处理或者在应用层对搜索词做白名单过滤只保留中文、字母、数字、空格和少数几个安全符号。记住任何用户输入都不应该直接进布尔搜索语法这个习惯能让你少很多事故。5. 全文索引的参数调优与相关度使用技巧5.1 关键系统参数的作用一览我整理了一张参数速查表做全文索引之前建议先看一遍当前值参数默认值作用修改建议ngram_token_size2ngram 分词长度中文用 2需要单字搜索可调 1但索引会暴涨innodb_ft_min_token_size3最小索引词元长度默认解析器下建议调小到 1~2innodb_ft_max_token_size84最大索引词元长度一般不用改innodb_ft_enable_stopwordON是否启用停用词过滤中文场景建议 OFF 或自定义表innodb_ft_cache_size8M全文索引缓存大小大量导入数据时可调大加快索引构建查看参数用这条命令SHOW VARIABLES LIKE %ft%; SHOW VARIABLES LIKE ngram_token_size;5.2 生产环境的推荐配置方案以我现在的标准做法为例如果是纯中文内容站我一般这样配[mysqld] ngram_token_size 2 innodb_ft_min_token_size 1 innodb_ft_enable_stopword OFF innodb_ft_cache_size 64M配合建索引语句ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram;这样配置之后双字词搜得准必要的时候单字也能搜停用词不会误伤业务关键词。代价是索引体积会比默认配置大一些但对于百万级以内的单表数据这个代价完全值得。5.3 怎么用 EXPLAIN 确认索引真的生效全文索引也有走不走索引的问题。用EXPLAIN看执行计划type列如果是fulltext说明走了全文索引如果看到All那就说明你的查询条件有问题索引没生效。EXPLAIN SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 IN BOOLEAN MODE)\G我在优化过程中反复用这条命令验证修改效果尤其是排查那些搜不到的问题时执行计划能快速告诉你问题出在索引层面还是数据层面。相关度排序方面自然语言模式下可以直接拿MATCH()返回值排序SELECT id, title, MATCH(title, content) AGAINST(数据库) AS score FROM article WHERE MATCH(title, content) AGAINST(数据库) ORDER BY score DESC;需要注意这样会把相关度排序和过滤条件耦合在一次查询里如果数据量非常大建议把匹配结果先放入临时表或子查询再在外层做业务排序避免 MySQL 为了排序额外消耗太多内存。6. 全文索引和 LIKE、外部搜索引擎怎么选6.1 三种方案的硬核对比很多人纠结到底用哪种方案我把核心差异整理成一张表维度LIKE %词%MySQL 全文索引外部搜索引擎如 Elasticsearch查询原理全表扫描倒排索引分布式倒排索引十万级数据响应秒级毫秒级毫秒级中文分词天然支持需要 ngram效果够用插件丰富分词效果好相关度排序不支持自然语言模式支持支持 BM25 等复杂排序部署运维成本零零高需要独立集群适合场景小表、后台管理百万以内单表搜索千万级以上、复杂搜索6.2 我的选型经验如果你只是单表几万到百万条数据业务搜索需求就是简单的关键词匹配MySQL 全文索引是最划算的选择不用引入额外组件运维压力为零。如果数据量到了千万级别或者需要拼音搜索、同义词、复杂打分、聚合统计这些能力那就老老实实上外部搜索引擎硬用 MySQL 全文索引撑大场面只会越到后面越痛苦。还有一个务实的小建议很多团队会把两者结合MySQL 全文索引作为主检索通道定期把数据同步到外部搜索引擎作为补充。但这个方案要付出双份存储和同步成本到底值不值得看你们业务对搜索体验的要求有多高。7. 最后再分享两个实战习惯第一个习惯是建完索引之后先查词元不要急着验查询。用information_schema.INNODB_FT_INDEX_CACHE看实际分词结果能够在第一时间发现解析器配置问题省去后面大把排查时间。第二个习惯是每次上线全文索引改动前先跑一遍现有搜索词的历史日志把高频词拿出来批量验证一遍。我遇到过很多次索引看起来一切正常但业务最多的那个词就是搜不到的情况提前用真实词验证比临时抱佛脚靠谱得多。全文索引本身不难难的是它默认的那些针对英文设计的规则——停用词、最小词长、50% 阈值——放在中文场景下全是坑。把这一篇里的问题都提前避开了你的MATCH() AGAINST()才能真正成为一把趁手的工具而不是一个定时炸弹。 
长河君 · 2026/9/17 9:41:01 · 阅读 1.2k MySQL全文索引实战：MATCH AGAINST用法、ngram分词与踩坑全记录
做后端这几年我在 MySQL 全文索引上踩过的坑十个手指头都数不过来。最早接触MATCH() AGAINST()是给一个内容检索接口做优化表里文章数据二十多万条原来用LIKE %关键词%搜标题和正文接口平均耗时两秒多数据库 CPU 经常飙高。后来换成了全文索引花了两天时间把官方文档和各种报错记录啃了一遍最终把查询压到了几十毫秒但过程相当曲折——建索引用错上下文直接报错、中文内容搜出来是空的、短词被静默忽略、相关度排序不符合直觉……每一步都有对应的坑。这篇文章就把整个踩坑过程完整记录下来索引怎么建、MATCH() AGAINST()的三种模式怎么用、每条报错信息的含义和解决办法、以及几个关键系统参数的真实作用。无论你是刚听说全文索引还是已经在生产环境被中文搜索问题折磨过这篇都能给你一个可以直接抄作业的答案。1. 全文索引到底解决了什么问题先理解 LIKE 为什么不行1.1 我遇到的真实场景和性能瓶颈我负责的系统里有一张文章表字段不算多核心就是title和content两个文本列业务上需要支持用户输入任意关键词同时搜索标题和正文再按某种热度排序返回。早期数据量只有几千条的时候接口写得非常简单SELECT id, title FROM article WHERE title LIKE %关键词% OR content LIKE %关键词% ORDER BY click_count DESC;几千条数据跑起来没什么感觉顶多多花几十毫秒。但数据涨到二十万条之后这个查询开始原形毕露——加了ORDER BY click_count DESC之后连索引都没法走直接全表加文件排序。我做过一次压测单个关键词请求的平均响应时间在 2.3 秒左右数据库 CPU 瞬时能到 40% 以上。更难受的是这种查询一旦并发上来连接数马上被打满整个服务都跟着抖。1.2 LIKE 的索引失效原理为什么普通索引救不了你很多人第一反应是给title和content建普通索引觉得这样 LIKE 就能快。这里有个非常经典的误区MySQL 的 B 树索引对 LIKE 的支持是有限制的只有通配符不在开头时才能走索引。-- 这种写法可以走索引 SELECT id FROM article WHERE title LIKE MySQL%; -- 这种写法索引直接失效 SELECT id FROM article WHERE title LIKE %MySQL%;因为 B 树是按字段值的完整前缀排序的%MySQL%这种模式不知道字符串开头是什么只能把整列数据全部取出来挨个匹配。而在搜索场景里用户输入的关键词基本都出现在句子中间位置绝大多数查询都必须写成双侧通配符。也就是说只要业务是搜关键词而不是查前缀普通索引就一点忙都帮不上。全文索引不一样它的底层是倒排索引核心思路是先分词再建立关键词到文档的映射关系。查询的时候直接根据关键词定位到包含它的行不需要逐行扫描。这就像查字典一样你按拼音找字而不是从第一页翻到最后一页。2. 全文索引的建立方式与底层逻辑从建表到分词器2.1 建表建索引的完整语法MySQL 的全文索引支持在CHAR、VARCHAR、TEXT类型列上建立建表时可以直接声明CREATE TABLE article ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, FULLTEXT KEY ft_idx_title_content (title, content) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果是已经存在的表用ALTER TABLE补加索引或者用CREATE FULLTEXT INDEX都可以ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram; CREATE FULLTEXT INDEX ft_idx_content ON article (content) WITH PARSER ngram;这里提前说一个全文索引最重要的限制MATCH() AGAINST()里指定的列必须和全文索引定义的列完全一致包括顺序。索引建在(title, content)上那么查询也必须写MATCH(title, content)如果只写MATCH(content)MySQL 会直接报 1191 错误。这个我在后面的踩坑部分会再展开。2.2 倒排索引的核心原理先分词再反查理解全文索引绕不开倒排索引这个词。普通索引是记录 → 字段值的正向映射而倒排索引是反过来的它把字段内容切分成一个个词元然后建立词元 → 记录列表的映射。举个例子假设表里有三条记录idtitle1MySQL 全文索引实战2数据库索引优化指南3MySQL 索引原理总结分词之后倒排索引大致长这样词元记录列表mysql1, 3全文1索引1, 2, 3数据库2优化2查询MATCH(title) AGAINST(MySQL)时直接查mysql这个词元拿到记录列表[1, 3]再回表取数据。整个过程走的是词元查找时间复杂度跟表的总行数基本无关所以数据量越大全文索引相对 LIKE 的优势越明显。2.3 ngram 解析器中文检索的命门这里必须花大篇幅讲 ngram。MySQL 默认的全文索引解析器是按空格、标点一类分隔符来分词的这个机制对英文很自然因为英文单词天然用空格隔开。但中文不一样一句话里字与字之间没有空格默认解析器会把整句话当成一个超长词元存进去比如MySQL全文索引实战会被当成一个整体。后面你搜索引它发现索引表里根本没有索引这个独立词元自然就搜不到东西。解决办法就是给全文索引指定WITH PARSER ngram。ngram 解析器会把文本按固定长度连续切词。比如ngram_token_size 2时我们中国会被切成我们 们中 中国每个长度为 2 的连续字符组合都作为一个词元。这样做的好处是中文不再需要预置词典坏处是会产生大量无意义的交叉词元索引体积会明显变大。中文场景下ngram_token_size一般建议设成 2既能覆盖绝大多数双字词又不至于让索引膨胀到不可接受。提示ngram_token_size是建索引之前就要确定的全局参数它的值会影响所有使用 ngram 解析器的全文索引。修改后必须删除旧索引重建否则不生效。3. MATCH() AGAINST() 三种模式从自然语言到布尔表达式3.1 自然语言模式最简单也最容易被阈值坑先看最基础的写法SELECT id, title, MATCH(title, content) AGAINST(数据库) AS score FROM article WHERE MATCH(title, content) AGAINST(数据库);这种不写模式参数的写法实际是IN NATURAL LANGUAGE MODE也就是自然语言模式。它会返回一个相关度分数score数值越大代表匹配度越高。自然语言模式有两个隐藏规则需要注意。第一分词结果里长度小于innodb_ft_min_token_size的词元不参与匹配和检索InnoDB 默认值是 3。比如你搜PHP这种三个字符以内的词默认配置下会被静默忽略不会报错但就是没有结果。第二如果某个词在超过 50% 的行里都出现这个词会被当作没区分度的词直接忽略同样静默返回空结果。这个规则坑过非常多的人——你的关键词明明有数据查出来却是空集。后面我会专门写这个 50% 阈值的处理办法。3.2 布尔模式搜索语法的完全形态IN BOOLEAN MODE是实际项目里用得最多的模式因为它提供了完整的检索语法-- 必须包含数据库不能包含MySQL SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 -MySQL IN BOOLEAN MODE); -- MySQL必须出现在开头且优化可以加分 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(MySQL 优化 IN BOOLEAN MODE); -- 短词 任意字符通配 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据* IN BOOLEAN MODE); -- 精确短语 SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(全文索引 IN BOOLEAN MODE);几个常见运算符的作用我用表格列一下运算符含义示例必须包含MySQL-必须排除-MySQL提高权重MySQL降低权重MySQL*通配符只能放词尾数据* 精确短语全文索引( )表达式分组(数据库 索引)~降低相关度类似软排除~MySQL布尔模式还有一个巨大的优点它不受 50% 阈值限制。如果一个词在大量行里出现自然语言模式可能搜不到但布尔模式照样能返回结果。所以生产环境里很多团队干脆完全用布尔模式反正语法灵活还能避免被阈值坑到。需要注意布尔模式返回的相关度分数没有自然语言模式那么有参考价值很多时候用它排序并不理想。如果你想靠相关度排序建议用自然语言模式如果你需要精确控制匹配规则再考虑布尔模式。3.3 查询扩展模式相关推荐的好东西搜索的坏东西第 三种模式是WITH QUERY EXPANSION也叫查询扩展。它会自动做两次检索第一次用原始关键词搜出一些结果然后从这些结果里提取高频词用这些新词再做一次搜索从而把语义相关但可能不包含原始关键词的文档也带出来。SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 WITH QUERY EXPANSION);这个模式适合找相似内容的场景比如给你正在看的文章推荐相关文章。但如果你做的是站内搜索对精准度要求高我不建议用它因为查询扩展会显著放大召回范围很容易带出一堆看起来有点关系但用户根本不想看的记录。4. 踩坑全记录从报错到空结果的完整排查链路这一部分是整篇文章的重心。下面每个坑都是我实际遇到过的我把报错信息、排查思路、最终解决办法都写清楚你可以按图索骥。4.1 坑一ERROR 1191索引列匹配不一致我第一次建完索引直接跑查询就撞上了这个错误ERROR 1191 (HY000): Cant find FULLTEXT index matching the column list当时的索引是建在(title, content)上的我写的查询却是SELECT id, title FROM article WHERE MATCH(content) AGAINST(数据库);问题就出在列列表不一致。MySQL 要求MATCH()中的列列表必须与全文索引定义的列完全匹配列的数量、顺序都不能变。我一开始以为只要其中一列有索引就行结果被教育了。解决办法很简单改成SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库);排查这种问题有个快速办法直接执行SHOW INDEX FROM article;查看FULLTEXT类型索引对应的列清单然后对照检查MATCH()的列。4.2 坑二ERROR 1210参数个数或上下文不对ERROR 1210 (HY000): Incorrect arguments to MATCH这个报错通常出现在两种场景。一种是你给MATCH()传了错误数量的参数另一种是某些表达式里不允许直接用MATCH() AGAINST()比如你想在ORDER BY里对MATCH()的结果做某种运算上下文不对就会报 1210。我踩到的具体原因是把MATCH()放在了GROUP BY子句里试图按相关度分组MySQL 不支持这种用法。遇到 1210 不要慌先检查两点第一MATCH()里是不是只写了索引列第二MATCH()是否用在了 MySQL 限制的上下文环境中。正常情况下WHERE、ORDER BY、SELECT列表里使用都没问题但GROUP BY和某些嵌套子查询里就要小心。4.3 坑三中文搜索出来是空的LIKE 却正常这个坑最折磨人。表里明明有大量包含数据库的文章用LIKE %数据库%能搜出一堆但换成MATCH(title, content) AGAINST(数据库)之后结果是零。我当时排查了很久一度怀疑是字符集问题后来才发现根因是没指定 ngram 解析器。默认解析器对中文的处理方式是整句作为一个词元索引里根本没有数据库这个独立词元。检查方法也很简单直接看全文索引的解析器SELECT INDEX_NAME, INDEX_TYPE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME article AND INDEX_TYPE FULLTEXT;确认索引没带 ngram 之后需要重建索引ALTER TABLE article DROP INDEX ft_idx_title_content; ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram;对于已经存在的表ALTER TABLE ... ADD FULLTEXT INDEX ... WITH PARSER ngram在 MySQL 5.7 和 8.0 都是可以直接执行的。改完之后记得用下面的语句验证分词结果SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;这个表能看到实际生成的词元。如果能看到数据库或者数据库这类词元说明解析器已经生效。4.4 坑四短词被静默忽略搜索结果莫名缺失还有一个典型的搜不到场景是搜索词太短。InnoDB 全文索引默认innodb_ft_min_token_size 3也就是说长度小于 3 的词元在建立索引时就被忽略了。比如用户搜PHP或Go如果按默认配置这些短词根本不会进入索引查询自然没结果。需要特别说明的是ngram 解析器下生效的是ngram_token_size参数innodb_ft_min_token_size主要影响默认解析器。如果你用ngram_token_size 2双字词是可以正常被索引和检索的但单个字依然搜不到。解决办法是把参数调小甚至改成 1[mysqld] innodb_ft_min_token_size 1 ngram_token_size 2改完必须重启 MySQL然后对所有全文索引做一次重建OPTIMIZE TABLE article;如果表比较大这个过程会比较慢建议在业务低峰期操作。另外调低ngram_token_size会让索引体积成倍增加如果只是偶尔需要单字搜索也可以考虑在布尔模式里用通配符补救未必非要全局修改参数。4.5 坑五默认停用词把常见词全过滤了MySQL 从设计之初就内置了一份英文停用词表比如a、an、are、is这些高频无意义词在索引阶段就被排除掉了。InnoDB 环境下由开关控制SHOW VARIABLES LIKE innodb_ft_enable_stopword;如果这个值是ON那默认的停用词表就会生效。中文场景下像的、了、是这类单字词如果长度满足条件理论上也可能被过滤。更麻烦的是当你的业务关键词恰好是停用词表里的英文单词时比如搜in或it结果必然为空。处理停用词有两种常用方式。第一种是直接关闭停用词功能[mysqld] innodb_ft_enable_stopword OFF第二种是自定义停用词表通过innodb_ft_server_stopword_table指定一张自定义表把真正需要过滤的词放进去其余全部放行。我做生产配置时一般选择自定义表因为全量关闭停用词会把很多没有检索价值的词也放进索引白白增加体积和噪音。4.6 坑六50% 阈值规则最隐蔽的空结果原因这个是让我记忆最深的一个坑。有一回线上反馈某个热门关键词搜不到结果我用自然语言模式复现确实一条都没返回。当时既有数据LIKE 也能查到索引也建了分词也正常百思不得其解。后来翻官方文档才发现是 50% 阈值规则如果一个搜索词出现在超过 50% 的行里MySQL 会认为这个词没有区分度直接当作停用词处理自然语言模式不返回任何结果。我当时那个关键词是系统近期的热门标签确实出现在大量文章里刚好命中这个规则。解决方式有两个。一是改用布尔模式因为这个规则只对自然语言模式生效SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(热门词 IN BOOLEAN MODE);二是在业务上想办法限制范围让命中比例降下来比如强制加上时间范围条件。第一种方式明显更直接这也是我在生产环境里更推荐布尔模式的原因之一它能绕开一堆隐性的过滤规则。4.7 坑七布尔模式下的特殊字符被当成运算符最后一个高频问题来自用户输入。用户在搜索框里输的内容是不可控的可能带、-、、*、这类特殊字符。当你把这些字符串原样拼进AGAINST()时MySQL 会按照布尔运算符去解析导致结果完全不符合预期。比如用户想搜C实际传入的搜索词是CMySQL 会把当作必须包含运算符后面跟的是空词整个查询行为会变得非常诡异。解决办法是对输入做清洗和转义。我通常的做法是把业务中不支持的运算符字符先移除再对必须保留的字符做转义处理或者在应用层对搜索词做白名单过滤只保留中文、字母、数字、空格和少数几个安全符号。记住任何用户输入都不应该直接进布尔搜索语法这个习惯能让你少很多事故。5. 全文索引的参数调优与相关度使用技巧5.1 关键系统参数的作用一览我整理了一张参数速查表做全文索引之前建议先看一遍当前值参数默认值作用修改建议ngram_token_size2ngram 分词长度中文用 2需要单字搜索可调 1但索引会暴涨innodb_ft_min_token_size3最小索引词元长度默认解析器下建议调小到 1~2innodb_ft_max_token_size84最大索引词元长度一般不用改innodb_ft_enable_stopwordON是否启用停用词过滤中文场景建议 OFF 或自定义表innodb_ft_cache_size8M全文索引缓存大小大量导入数据时可调大加快索引构建查看参数用这条命令SHOW VARIABLES LIKE %ft%; SHOW VARIABLES LIKE ngram_token_size;5.2 生产环境的推荐配置方案以我现在的标准做法为例如果是纯中文内容站我一般这样配[mysqld] ngram_token_size 2 innodb_ft_min_token_size 1 innodb_ft_enable_stopword OFF innodb_ft_cache_size 64M配合建索引语句ALTER TABLE article ADD FULLTEXT INDEX ft_idx_title_content (title, content) WITH PARSER ngram;这样配置之后双字词搜得准必要的时候单字也能搜停用词不会误伤业务关键词。代价是索引体积会比默认配置大一些但对于百万级以内的单表数据这个代价完全值得。5.3 怎么用 EXPLAIN 确认索引真的生效全文索引也有走不走索引的问题。用EXPLAIN看执行计划type列如果是fulltext说明走了全文索引如果看到All那就说明你的查询条件有问题索引没生效。EXPLAIN SELECT id, title FROM article WHERE MATCH(title, content) AGAINST(数据库 IN BOOLEAN MODE)\G我在优化过程中反复用这条命令验证修改效果尤其是排查那些搜不到的问题时执行计划能快速告诉你问题出在索引层面还是数据层面。相关度排序方面自然语言模式下可以直接拿MATCH()返回值排序SELECT id, title, MATCH(title, content) AGAINST(数据库) AS score FROM article WHERE MATCH(title, content) AGAINST(数据库) ORDER BY score DESC;需要注意这样会把相关度排序和过滤条件耦合在一次查询里如果数据量非常大建议把匹配结果先放入临时表或子查询再在外层做业务排序避免 MySQL 为了排序额外消耗太多内存。6. 全文索引和 LIKE、外部搜索引擎怎么选6.1 三种方案的硬核对比很多人纠结到底用哪种方案我把核心差异整理成一张表维度LIKE %词%MySQL 全文索引外部搜索引擎如 Elasticsearch查询原理全表扫描倒排索引分布式倒排索引十万级数据响应秒级毫秒级毫秒级中文分词天然支持需要 ngram效果够用插件丰富分词效果好相关度排序不支持自然语言模式支持支持 BM25 等复杂排序部署运维成本零零高需要独立集群适合场景小表、后台管理百万以内单表搜索千万级以上、复杂搜索6.2 我的选型经验如果你只是单表几万到百万条数据业务搜索需求就是简单的关键词匹配MySQL 全文索引是最划算的选择不用引入额外组件运维压力为零。如果数据量到了千万级别或者需要拼音搜索、同义词、复杂打分、聚合统计这些能力那就老老实实上外部搜索引擎硬用 MySQL 全文索引撑大场面只会越到后面越痛苦。还有一个务实的小建议很多团队会把两者结合MySQL 全文索引作为主检索通道定期把数据同步到外部搜索引擎作为补充。但这个方案要付出双份存储和同步成本到底值不值得看你们业务对搜索体验的要求有多高。7. 最后再分享两个实战习惯第一个习惯是建完索引之后先查词元不要急着验查询。用information_schema.INNODB_FT_INDEX_CACHE看实际分词结果能够在第一时间发现解析器配置问题省去后面大把排查时间。第二个习惯是每次上线全文索引改动前先跑一遍现有搜索词的历史日志把高频词拿出来批量验证一遍。我遇到过很多次索引看起来一切正常但业务最多的那个词就是搜不到的情况提前用真实词验证比临时抱佛脚靠谱得多。全文索引本身不难难的是它默认的那些针对英文设计的规则——停用词、最小词长、50% 阈值——放在中文场景下全是坑。把这一篇里的问题都提前避开了你的MATCH() AGAINST()才能真正成为一把趁手的工具而不是一个定时炸弹。