做站内搜索的时候很多人第一反应就是LIKE %关键词%。我之前维护一个资讯类站点前期数据量不大这条SQL用起来还挺顺手。等文章量涨到几十万篇问题全出来了搜索越来越慢后台日志里全是慢查询告警更头疼的是查得还不准搜“优化”它能把包含“优化”两个字的所有文章全部端出来顺序乱七八糟。后来翻书查文档才把目光放到MySQL自带的全文搜索上。《MySQL必知必会》第18章正好是讲这个主题通过MATCH()和AGAINST()函数来做全文本搜索以及查询扩展的使用。这篇文章就把我学完并用在实际项目里的经验整理出来重点讲清楚自然语言搜索、布尔搜索、查询扩展三种玩法还有那些官方文档不会细说的坑。注意全文搜索和LIKE %关键词%是两套完全不同的机制。前者依赖全文索引数据量大时性能差距非常明显后者只能老老实实全表扫。1. 全文搜索解决的是什么场景1.1 不是所有搜索都能靠LIKE撑住LIKE %关键词%的问题在于前导通配符导致查询优化器没法走B树索引只能全表扫描。表小的时候扫描就扫描了几万行也没感觉一旦表到百万级、千万级每次搜索都是一次全表读数据库IO被打满响应时间直接翻几倍。我自己遇到的慢查询里有一半都是这种写法贡献的。全文搜索的底层和LIKE完全不一样它依赖的是全文索引。全文索引在MySQL里是一种倒排索引结构你可以把它想象成一本书最后的术语索引页每个词都带着一个页码列表表示这个词出现在哪些位置。搜索的时候系统不是去全文里肉眼扫描而是直接查词表拿到词对应的文档列表然后计算相关度排序。这个思路本质上是空间换时间建索引的时候多花一点存储和开销查的时候不用再扫全表。1.2 MATCH()和AGAINST()到底怎么配合MySQL全文搜索的查询语法很固定一眼就能认出来MATCH (col1, col2, ...) AGAINST (expr [search_modifier])MATCH()里面的列必须是你在FULLTEXT索引定义里声明的那些列。AGAINST()里放的是搜索关键词后面可以跟模式修饰符。搜索值会被MySQL自动分词然后进倒排索引里找匹配的文档。这里有个新手特别容易踩的坑FULLTEXT索引定义的是(title, content)查询时MATCH()里就必须写(title, content)不能只写title或者只写content。MySQL不支持对一个多列全文索引的部分列做查询写了也会直接报错或者查不到预期结果。原因也简单索引是按整组列建的分词映射搜单个列没法利用这个索引。三种搜索模式我先列出来后面逐个展开模式写法特点自然语言模式IN NATURAL LANGUAGE MODE默认模式按相关度自动排序布尔模式IN BOOLEAN MODE支持 - () 等操作符精细控制查询扩展WITH QUERY EXPANSION基于首次搜索词扩展提高召回率如果你什么都不写MySQL默认走自然语言模式。这也是日常最常用的模式。2. 环境准备全文索引的三种创建方式与边界限制2.1 建索引的三种姿势与适用场景使用全文搜索的前提是表上得有FULLTEXT全文索引。我见过不少同学直接执行MATCH查询报错说找不到索引才想起来还没建索引。建全文索引有三种方式效果是一样的看场景选第一种建表的时候直接声明。比如建一个新闻表CREATE TABLE news_articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, publish_date DATETIME NOT NULL, FULLTEXT KEY ft_title_content (title, content) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种表已经存在用ALTER TABLE追加ALTER TABLE news_articles ADD FULLTEXT KEY ft_title_content (title, content);第三种用CREATE INDEX创建CREATE FULLTEXT INDEX ft_title_content ON news_articles(title, content);我个人习惯建表时就规划好全文索引因为ALTER TABLE在大表上重建索引很费时间一次操作可能把线上业务拖垮。如果生产环境已经有大表建议在业务低峰期执行或者借助在线DDL工具处理先把索引建好再切流量。2.2 全文索引的边界限制和中文分词配置全文索引不是万能的它有严格的适用边界索引列必须使用CHAR、VARCHAR、TEXT这几类文本类型BLOB不行。InnoDB从5.6版本开始支持全文索引MyISAM一直支持但现在的生产环境基本都是InnoDB所以下面的讨论都以InnoDB为准。InnoDB的全文索引默认为英文等拉丁字符设计它按空格和标点分词。中文默认会被当成连续的字符搜索中文经常没有结果。解决办法是使用ngram分词解析器在创建索引时指定ALTER TABLE news_articles ADD FULLTEXT KEY ft_content_zh (content) WITH PARSER ngram;ngram解析器默认按二元切分比如“数据库优化”会切分成“数据”“据库”“库优”“优化”这几段搜索时也按同样方式切分词再去匹配这样中文搜索结果就能正常出来了。这个点对中文站点来说非常关键我在实际项目中就是靠它把中文搜索跑通的。注意全文索引的存储开销并不小。InnoDB除了维护主索引还要维护一组以fts_开头命名的内部辅助表用来保存分词后的词条、映射关系和统计信息。如果你的表本身有几十GB全文索引再占个几GB到十几GB都是很正常的规划磁盘空间时要把这笔账算进去。3. 自然语言模式实战从示例到排序原理3.1 一个可以照抄的示例和查询技巧拿前面的news_articles表做演示先插入几条数据INSERT INTO news_articles (title, content, publish_date) VALUES (MySQL索引优化实践, 在MySQL中合理使用索引可以显著提升查询性能本文将介绍B树索引和全文索引的使用场景..., 2024-03-01 09:00:00), (数据库存储引擎对比, InnoDB和MyISAM是MySQL最常用的存储引擎它们在事务支持和锁机制上有很大区别..., 2024-03-02 10:30:00), (全文搜索入门指南, 本文介绍如何在项目中使用全文搜索技术包括MATCH函数和AGAINST函数的基础用法..., 2024-03-03 14:20:00), (性能调优经验分享, 从慢查询日志开始分析数据库性能问题逐步定位瓶颈并给出优化建议..., 2024-03-04 16:45:00);现在搜索包含“MySQL”的文章SELECT id, title, MATCH(title, content) AGAINST(MySQL) AS relevance FROM news_articles WHERE MATCH(title, content) AGAINST(MySQL);返回结果会默认按相关度从高到低排列。第一行和第二行的相关度通常不一样因为title、content里出现次数和词的位置都会有差异。这里的relevance是一个非负浮点数0表示没有匹配到任何关键词数值越大相关性越高。但它不是一个百分比只是相对的排序依据。如果你只是想在SELECT列表里看相关度别忘了一定要同时把MATCH写到WHERE条件里。MySQL优化器很聪明WHERE里没有MATCH条件时它可能根本不去算相关度直接给你返回一个异常值。这个细节我在开发时踩过后来养成习惯要么不查相关度要查就一定在WHERE里也带MATCH。3.2 相关度排序背后的权重逻辑了解一点排序算法能帮你在业务上解释“为什么这个结果排在前面”。MySQL全文搜索的相关度计算大致遵循TF-IDF思想某个词在当前文档中出现得越多这个词对文档的权重越大反过来某个词在所有文档中出现得越普遍它的区分度就越低权重就越小。举个例子上面的数据里两篇文章都出现了“MySQL”同时它们也都提到了“数据库”那么“MySQL”的权重通常比“数据库”高因为“数据库”在文章集里太常见了出现10次和出现20次区别不大而“MySQL”出现的位置和次数更能代表这篇文档的主题。实际排序时搜索引擎会觉得第一篇文章更贴近“MySQL”这个主题这跟人脑判断基本一致。InnoDB和MyISAM在实现细节上略有差异但TF-IDF这个大方向是没错的。理解了这个原理你就知道为什么全文搜索不是简单的字符串包含匹配它比LIKE的“是否出现在这一行”高一个层次能把“哪篇更相关”算出来。3.3 停用词与最小词长两个隐形规则自然语言模式有两个隐形规则不搞清楚会被坑到。第一个是停用词规则。英文里的a、an、the、is这类高频但没什么区分度的词在分词阶段就被过滤掉了根本不会进索引。默认情况下这个词表是系统内置的可以自定义替换但一般不建议折腾。第二个是最小词长问题。InnoDB默认是3个字符MyISAM默认是4个。这意味着两个字母的英文单词比如go或者两个字符的中文词组在默认配置下根本不会被索引到自然也就搜不到。中文场景下这更致命所以我才在前面强调ngram解析器的重要性它不只是解决分词问题顺带也改变了词条的构成方式短词也能被索引。至于50%阈值问题主要存在于MyISAM引擎中如果某个词在超过50%的行中出现这个词在自然语言模式下会直接被忽略。比如一张表有100行其中60行都包含“文章”那么搜“文章”会返回空结果相关度是0。InnoDB没有这个限制但也要注意出现频率太高的词算出来的权重会很低排序时会沉底。4. 布尔模式用操作符精确控制搜索条件4.1 布尔模式的常用操作符与业务含义自然语言模式下你能做的只有“输入几个词系统帮你找相关文档”。但在真实业务里搜索需求往往更具体比如要求某个词必须出现某个词一定不能出现或者需要对词的权重做微调。这时候就要用布尔模式IN BOOLEAN MODE。布尔模式下的AGAINST表达式里可以带操作符我把常用的操作符整理成一张表操作符含义示例行为这个词必须出现MySQL 表示文档中必须有MySQL-这个词绝对不能出现-Oracle 表示文档中不能有Oracle提升这个词的权重索引 会提高词的排序权重降低这个词的权重缓存 会降低词的排序权重()把多个词组成一个子表达式(MySQL 索引) 表示必须包含MySQL或索引~否定这个词并降低排序权重~教程 表示包含教程的文档排后面“”匹配完整短语“全文搜索” 表示必须包含连续短语*通配符匹配词根搜索* 匹配以“搜索”开头的词4.2 组合搜索示例与使用经验用布尔模式写一个典型需求搜索新闻表要求文档里必须有“数据库”同时排除“教程”相关的内容另外如果文档里出现了“优化”希望它排在前面。SELECT id, title FROM news_articles WHERE MATCH(title, content) AGAINST(数据库 -教程 优化 IN BOOLEAN MODE);这里 数据库 把不包含“数据库”的文章全过滤掉-教程 把包含“教程”的排除优化 只是提升排序权重不影响是否返回。注意空格是操作符和词之间的分隔符表达式里词之间用空格隔开如果没写空格整个会被当成一个词去切分效果就变了。布尔模式在实际开发里有几个实用经验。第一它不会受50%阈值影响想在自然语言模式下搜高频词但被忽略时可以改用布尔模式强制匹配。第二通配符 * 只能放在词末尾写成“搜*”可以匹配“搜索”“搜索引擎”“搜索算法”这类前缀相同的词这在修饰词很多的场景非常实用。第三短语匹配用双引号把整个短语包起来比如“全文搜索”这就不会再拆分成“全文”和“搜索”两个词了。提示布尔模式默认不返回像自然语言模式那样的相关度数值它更多用来做过滤和粗略排序。如果需要把匹配程度展示给用户建议在业务里把自然语言模式和布尔模式分开处理别指望同一套SQL同时拿到两种能力。5. 查询扩展两步搜索让召回率明显提升5.1 查询扩展要解决的问题和典型场景自然语言模式解决的是“用户输入的关键词和文章里的词完全一致”的搜索场景。但实际中用户的表达千变万化用户搜一个东西可能用的是A说法文章里写的是B说法两个词之间根本没有字面上的重合。你搜“数据库”但真正有价值的文章标题是《InnoDB存储引擎调优笔记》它里面通篇都在讲数据库就是没出现“数据库”这三个字怎么办MySQL提供了一个叫WITH QUERY EXPANSION的模式中文叫查询扩展专门应对这种“词不同、义相近”的情况。它的核心思路是先用你输入的关键词去搜一轮从搜到的结果里提炼出更多相关的词再用这批扩展后的词去搜一轮把第一轮遗漏的文章捞回来。5.2 两步式搜索原理与示例用示例演示最好理解。还是用news_articles表搜索“数据库”SELECT id, title FROM news_articles WHERE MATCH(title, content) AGAINST(数据库 WITH QUERY EXPANSION);这一步的内部执行过程可以拆成两步理解第一步像自然语言搜索一样执行常规搜索找出所有包含“数据库”的文档。然后从第一轮返回的最相关文档里分析词频挑出那些除去停用词、出现频率较高的、和主题相关的词比如“存储引擎”“索引”“事务”等作为扩展词。第二步用“数据库”加上这些扩展词组成的搜索条件再去执行一次全文搜索。因为“存储引擎”“索引”这些词在第一轮结果里频繁出现第二轮的匹配范围就扩大了。那些没有直接写“数据库”但出现了“存储引擎”“索引”的文章也会被搜索出来。整个模式的效果类似于搜索引擎里的“你是不是还想找这些”。我在实际项目中调用这个模式最直观的感受是召回率明显提高原来一搜只有三五条用查询扩展能多出一倍甚至更多的结果。代价也很直接精度下降会混进来一些只在扩展词上有匹配、但与原始主题偏差较大的文章。5.3 查询扩展的适用边界与实战建议给查询扩展画个边界它适合用在用户搜索词较宽泛、内容语料词汇风格不统一的场景比如内容型站点的热门搜索、相关推荐、Tag联想。它绝对不适合用在精确检索场景比如电商后台的商品查找、订单检索那些地方要求的就是严格匹配查询扩展会把结果搅浑。使用上我有三条建议。第一查询扩展结果一定要配合LIMIT使用别一次性把全部结果都倒出来很多质量不高的结果会被带进来。第二如果业务上允许把“精确搜索”和“宽松搜索”做成两个入口默认走自然语言模式用户搜不到想要的结果时再给出“换种说法试试”的推荐。第三配合ngram解析器时效果更好中文短词也能进入扩展词集合召回率提升会更明显。6. 常见问题排查与调优心得6.1 高频问题排查速查表我把实际开发和上线过程中遇到的高频问题汇总成一张速查表方便你排查现象原因处理方式搜中文没结果LIKE却有默认分词器按空格切分中文加ngram解析器重建全文索引搜两个字的词没结果最小词长限制默认3字符调innodb_ft_min_token_size并重建索引MATCH列和索引定义不一致时异常多列索引必须整组列匹配检查MATCH里的列是否和FULLTEXT定义完全一致搜索很慢EXPLAIN显示typeALL没走全文索引或者索引失效用EXPLAIN确认typefulltext修改参数后没效果InnoDB全文索引需要重建ALTER TABLE ... DROP INDEX / ADD FULLTEXT自然语言搜高频词没结果出现频率过高导致权重趋近0改用布尔模式强制匹配查询扩展结果太杂扩展词引入大量低相关度文档加LIMIT或调整业务入口6.2 全文索引调优与运维心得最后聊聊调优和运维上的心得。全文索引不是一个建完就完事的静态结构。每一次INSERT、UPDATE、DELETE都会触发索引的增量更新对写入频繁的表会造成不小的额外开销。我有一个内容表全文索引建完以后写入性能大概下降了10%左右还能接受但如果你的表是高频写入型业务就要评估全文索引的成本别为了搜索功能牺牲主流程的写入性能。大表重建索引是个真正的耗时间操作。我遇到过一张两千万行的表ALTER TABLE加全文索引跑了将近半小时期间表被锁线上业务直接受影响。所以规划索引一定要趁早最好在数据量还不大的时候就建好。如果生产环境已经是大表宁可先通过备份-还原的方式在副本上建索引再切换流量也别直接在业务高峰期执行。用EXPLAIN是个好习惯。全文搜索理想情况下type列会显示fulltextrows也能看到词条对应的文档数量。每次写完搜索相关SQL我都习惯跑一下EXPLAIN确认确实走了全文索引而不是被优化器改成全表扫描。另外多列FULLTEXT索引的匹配查询在前面说过必须全列一致这个限制在开发时很容易被忽略。我建议团队内部统一约定全文索引的定义和查询模板保持一致哪个表配了全文索引就在公共查询组件里封装好避免业务代码里各写各的。还有一个原则MySQL全文搜索虽然好用也不是大型搜索引擎的替代品。几百万级别的内容型站点MySQL全文搜索完全能扛住如果数据量上亿或者需要实时多维度的全文检索那就该考虑更专业的搜索引擎了。技术选型就是拿时间和成本换用户体验先把自己的需求边界划清楚再做决定。我记得第一次完整跑通MATCH()和AGAINST()的全文搜索时最大的感触是数据库自带的这个能力比很多人想象中的强。一个站内搜索功能不需要额外引入重型的搜索引擎中间件几张全文索引就撑起来了。但也要承认它有不少细枝末节的限制比如多列匹配、最小词长、中文分词稍不留神就会掉坑里。这些坑我在实际项目中一个一个踩过来写出来就是希望后面的人能少走点弯路。最后再分享一个小技巧如果你现在用的是MySQL 5.7以上版本建议在创建全文索引时就直接用WITH PARSER ngram别等中文搜索出问题了再回头看。把ngram和查询扩展配合起来用搜索体验的改善会非常明显。全文搜索这东西用起来不难难的是把每个限制和原理都吃透希望这篇文章能帮你把这一步迈过去。