MySQL 全文索引实战：搜索功能的正确打开方式

开场白做搜索功能的时候很多人第一反应是 LIKE ‘%关键词%’数据量小的时候没问题数据一大直接全表扫描。我之前有个项目商品表的 LIKE 搜索在 50 万条数据时就要 3 秒以上根本没法用。后来上了全文索引查询时间降到毫秒级。不过全文索引也有自己的坑——中文分词、停用词、最小搜索长度这些搞不明白一样用不好。全文索引基础创建全文索引-- 建表时创建CREATETABLEarticles(idBIGINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(200),contentTEXT,FULLTEXTINDEXft_title_content(title,content));-- 已有表添加ALTERTABLEarticlesADDFULLTEXTINDEXft_title_content(title,content);-- 单列全文索引ALTERTABLEarticlesADDFULLTEXTINDEXft_content(content);基本查询-- 自然语言模式默认SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(数据库优化);-- 布尔模式SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(MySQL -OracleINBOOLEANMODE);-- 查询扩展模式SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(数据库WITHQUERY EXPANSION);MATCH 里的列必须和全文索引定义的列完全一致顺序也要一样。如果索引是(title, content)查询必须写MATCH(title, content)不能写MATCH(content, title)。三种搜索模式自然语言模式默认模式按相关性排序SELECT*,MATCH(title,content)AGAINST(数据库优化)ASscoreFROMarticlesWHEREMATCH(title,content)AGAINST(数据库优化)ORDERBYscoreDESC;相关性分数基于词频TF和逆文档频率IDFMySQL 内部计算不需要我们操心。搜索的是数据库和优化两个词的 OR 组合——包含任意一个词的记录都会返回。布尔模式精确控制搜索逻辑-- 必须包含 MySQL不能包含 OracleSELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(MySQL -OracleINBOOLEANMODE);-- 必须同时包含 MySQL 和 优化SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(MySQL 优化INBOOLEANMODE);-- 包含 MySQL 或 OracleSELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(MySQL OracleINBOOLEANMODE);-- 包含以数据开头的词SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(数据*INBOOLEANMODE);-- 必须包含 MySQL且优化权重更高SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(MySQL 优化INBOOLEANMODE);布尔模式的操作符操作符含义必须包含-不能包含无可选包含则加分*通配符只能后缀“”短语匹配 增减权重()分组~取反降低相关性短语匹配要特别注意-- 精确匹配数据库优化这个短语SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(数据库优化INBOOLEANMODE);查询扩展模式两步搜索先搜一次用结果中的词再搜一次。适合用户搜索词太少、结果不够的情况。SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST(数据库WITHQUERY EXPANSION);但这个模式容易返回不相关的结果慎用。中文分词问题这是全文索引最大的坑。MySQL 默认的分词器按空格和标点分词中文没有空格整段中文会被当成一个词-- content MySQL数据库优化实战-- 默认分词器会把MySQL数据库优化实战当成一个整体词-- 搜数据库搜不到SELECT*FROMarticlesWHEREMATCH(content)AGAINST(数据库);解决方案一ngram 分词器MySQL 5.7.6-- 建表时指定 ngram 分词器CREATETABLEarticles(idBIGINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(200),contentTEXT,FULLTEXTINDEXft_title_content(title,content)WITHPARSER ngram);-- 添加索引时指定ALTERTABLEarticlesADDFULLTEXTINDEXft_content(content)WITHPARSER ngram;ngram 分词器按固定长度把文本切成片段默认 n2二元分词数据库优化 → 数据, 据库, 库优, 优化ngram 的 n 值通过ngram_token_size参数配置范围 1-10默认 2。改这个参数需要重启 MySQL# my.cnf [mysqld] ngram_token_size 2n2 意味着搜索词至少 2 个字符才能命中。搜单个字比如数搜不到。解决方案二手动分词在插入数据前用应用层分词器比如 IK、jieba把中文文本切成词用空格拼接后存储-- 原文MySQL数据库优化实战-- 分词后存储MySQL 数据库 优化 实战INSERTINTOarticles(title,content)VALUES(MySQL数据库优化实战,MySQL 数据库 优化 实战);这种方式灵活分词质量高但增加了应用层复杂度。 ## 全文索引的配置参数 ### ft_min_word_len / innodb_ft_min_token_size 最小索引词长度默认 InnoDB 是 3MyISAM 是 4。长度小于这个值的词不会被索引。 ini# my.cnf[mysqld]innodb_ft_min_token_size1# InnoDBft_min_word_len1# MyISAM改成 1 之后需要重建全文索引ALTERTABLEarticlesDROPINDEXft_content;ALTERTABLEarticlesADDFULLTEXTINDEXft_content(content)WITHPARSER ngram;innodb_ft_cache_size全文索引缓存大小默认 32MB。批量插入大量数据时增大这个值可以提升索引构建速度。ft_query_expansion_limit查询扩展模式的最大扩展词数默认 20。全文索引 vs LIKE维度全文索引LIKE索引利用走全文索引前缀匹配走索引%xx%全表扫描搜索精度分词匹配支持相关性排序模糊匹配无排序中文支持需要 ngram 或手动分词直接支持写入性能需要维护索引写入较慢无额外开销存储开销索引占额外空间无额外空间简单说精确匹配用 LIKE搜索功能用全文索引。实战建议搜索场景用全文索引匹配场景用 LIKE——别拿全文索引当万能的中文必须用 ngram 分词器——否则搜不到东西布尔模式的 和 - 组合最实用——精确控制搜索结果注意最小搜索长度——ngram_token_size2 时搜单个字搜不到全文索引会降低写入性能——不适合频繁更新的表数据量特别大时考虑 Elasticsearch——MySQL 全文索引有上限亿级数据还是上 ES 吧小结全文索引是 MySQL 内置的搜索方案适合中小规模的搜索需求。关键点就三条中文用 ngram 分词器查询用布尔模式精确控制搜索长度注意最小值限制。如果你的搜索需求很复杂拼音搜索、同义词、高亮等还是得上 ElasticsearchMySQL 全文索引搞不定。相关阅读MySQL 官方文档 - Full-Text Searchngram 分词器全文索引布尔模式