1. 这个需求背后的真实场景1.1 什么时候需要给 LIKE 查询加权排序先说我自己的一个项目经历。某次给一个内容管理系统做搜索功能表里存了几万条内容每个内容有标题、摘要、正文、标签四个字段。业务方最开始只提了一个需求用户在搜索框输入关键词能搜到相关结果就行。等第一版上线后使用的人反馈说搜索结果太“死”——明明标题里包含关键词的内容和只在正文里偶遇关键词的内容混在一起排序全看主键 ID。这其实就是典型的“查到了但排不好”问题。LIKE 查询本身只能回答“哪些行匹配”它不负责告诉你“谁更匹配”。但业务方通常需要的是“谁最相关”。比如用户搜“闪存盘”这个词标题直接叫“闪存盘对比评测”的内容和一篇正文里顺带提了一句“可能对闪存盘的兼容性有影响”的文章两者匹配程度显然不一样。如果排在前面的是后者用户大概率会觉得搜索结果质量差从而转身就走。所以“根据 LIKE 查找关键字段设置权重排序”这个需求本质上是给“匹配质量”一个量化分数再按分数排序。这里的核心思路很简单不同字段的重要程度不同匹配到重要字段时权重分更高。1.2 基础权重排序写法长什么样最朴素、最好理解的写法是直接在 ORDER BY 里放好几个布尔表达式SELECT title, summary, content FROM articles WHERE title LIKE %闪存盘% OR tags LIKE %闪存盘% OR summary LIKE %闪存盘% OR content LIKE %闪存盘% ORDER BY (title LIKE %闪存盘%) DESC, (tags LIKE %闪存盘%) DESC, (summary LIKE %闪存盘%) DESC;在 MySQL 中布尔表达式的结果是 1 或 0所以(title LIKE %闪存盘%)在匹配时等于 1不匹配时等于 0。ORDER BY 按照这个值从大到小排序相当于把“标题命中”的文章排在最前面其次是“标签命中”再往后是“摘要命中”。这种写法很直白但它有一个局限性排序只看“是否命中”不看“命中了几次”也不看“是否同时命中多个字段”。也就是说如果一篇文章标题和摘要都命中了“闪存盘”另一篇只有标题命中两者在排序上是一样的。实际业务里这种情况很常见所以这种基础写法只能应付最原始的排序需求通常还得多叠加一层权重计算。2. 核心实现单关键词多字段加权排序2.1 用 CASE WHEN 构造权重分等到我第二次优化搜索排序时发现单纯列多个布尔表达式不够用。原因很简单标题命中一条、摘要命中一条的内容和标题、摘要、正文都命中几条的内容相关度完全不同。这时候需要算总分。最常用的做法是借用 CASE WHEN 表达式的语法给每个字段分配一个固定权重SELECT id, title, summary, (CASE WHEN title LIKE %闪存盘% THEN 5 ELSE 0 END) (CASE WHEN tags LIKE %闪存盘% THEN 3 ELSE 0 END) (CASE WHEN summary LIKE %闪存盘% THEN 2 ELSE 0 END) (CASE WHEN content LIKE %闪存盘% THEN 1 ELSE 0 END) AS relevance FROM articles WHERE title LIKE %闪存盘% OR tags LIKE %闪存盘% OR summary LIKE %闪存盘% OR content LIKE %闪存盘% HAVING relevance 0 ORDER BY relevance DESC, id DESC;这里我经常用的权重分配是标题命中5 分标签命中3 分摘要命中2 分正文命中1 分为什么这么分配因为标题往往浓缩了整个内容的核心主题标签相当于作者手动给出的关键词。一个用户输入“闪存盘”如果标题、标签都直接命中那这篇内容大概率就是围绕闪存盘来写的。而正文里出现一次关键词很可能只是顺带一提所以权重低。注意SELECT 里对 relevance 字段的别名是给 HAVING 和 ORDER BY 用的。MySQL 有一个特性SELECT 子句中定义的列别名在 GROUP BY、ORDER BY、HAVING 子句里是可以直接引用的。这个特性在多数关系型数据库里不支持但 MySQL 支持这也是为什么 MySQL 做这种加权排序很方便的原因之一。2.2 权重分还能叠加命中次数如果业务方进一步说“标题里出现两次‘闪存盘’的应该比只出现一次的更强”那上面的写法就不够了。这时我见过两个思路。思路一简单一点统计字段内出现的次数SELECT id, title, (LENGTH(title) - LENGTH(REPLACE(title, 闪存盘, ))) / LENGTH(闪存盘) AS title_count FROM articles;这段计算的核心原理是REPLACE(title, 闪存盘, )会把“闪存盘”三个字全都替换为空串。替换前后的字符长度差就是所有“闪存盘”三个字的总字符数。再除以“闪存盘”本身的长度3个字符就得到了出现次数。这个计数方法不依赖 MySQL 内置的字符串搜索函数兼容性很好。然后将出现次数乘以权重SELECT id, title, (LENGTH(title) - LENGTH(REPLACE(title, 闪存盘, ))) / 3 * 5 (LENGTH(tags) - LENGTH(REPLACE(tags, 闪存盘, ))) / 3 * 3 (LENGTH(summary) - LENGTH(REPLACE(summary, 闪存盘, ))) / 3 * 2 (LENGTH(content) - LENGTH(REPLACE(content, 闪存盘, ))) / 3 * 1 AS relevance FROM articles HAVING relevance 0 ORDER BY relevance DESC, id DESC;思路二更严谨的做法是按“出现频率”加权。正文往往很长就算出现 5 次“闪存盘”相对于几千字的正文来说也只是一个次要话题标题很短出现 2 次可能整个标题都在讲闪存盘。所以用出现次数除以字段长度得到一个“密度”再用密度乘以权重这才是更准的相关度指标。两种思路没有绝对的对错重点是根据业务判断。我当时做的系统里内容正文普遍很长所以后来改用了频率加权效果确实比单纯计数好一些。3. 深入优化把权重方案做成可调参数3.1 权重的选取本质上是个业务决策很多初学者会问权重 5、3、2、1 是怎么定出来的有没有标准答案我的答案是没有标准只有经验参考。权重的比例关系反映的是“你对不同字段重要性的判断”。比如你做的是文章检索标题肯定最重要做的是商品搜索商品名称最重品牌其次做的是订单系统订单号、用户名的权重就需要重新分配。但有一点是共通的权重差不能太大也不能太小。如果标题权重给 10正文权重给 1那几乎所有标题命中的结果都会排在正文命中结果前面两者之间的相关性差异被人为拉得过大。这可能导致用户搜一个长尾关键词时正文里整段都在讲这个主题、标题反而没有包含的内容被排在很后面——这显然也不是好体验。我建议权重比率控制在 3:2:1 附近到 8:4:2 之间比较保险具体要看数据量级和文本长度。3.2 用 CROSS JOIN 方式维护权重映射别写死在 SQL 里当我接手的项目越来越大之后我慢慢意识到一个设计问题权重直接写在 SQL 里改一次权重就得改一次业务代码重新发布特别麻烦。更合理的方式是把权重放到一个配置表里用 JOIN 来取CREATE TABLE search_weight ( field_name VARCHAR(20) PRIMARY KEY, weight INT DEFAULT 1 ); INSERT INTO search_weight (field_name, weight) VALUES (title, 5), (tags, 3), (summary, 2), (content, 1);查询时配合条件聚合或者用 CASE WHEN 配合子查询来动态获取权重。MySQL 原生语法里没有特别优雅的“配置驱动”写法但可以做一个小技巧把配置表内容转换成变量避免多次查询。SET weight_title (SELECT weight FROM search_weight WHERE field_name title); SET weight_tags (SELECT weight FROM search_weight WHERE field_name tags); SELECT id, title, (CASE WHEN title LIKE %闪存盘% THEN weight_title ELSE 0 END) (CASE WHEN tags LIKE %闪存盘% THEN weight_tags ELSE 0 END) AS relevance FROM articles HAVING relevance 0 ORDER BY relevance DESC;虽然还是没有完全脱离 SQL但至少权重值存放在数据库里。运营人员不用改代码直接改配置表的内容就能调整排序效果。这种“数据驱动”的思路在搜索排序里非常实用尤其是没有专职搜索工程师、只有运维和开发一起维护的系统里。多关键词的时候这个思路同样成立。比如用户搜索“闪存盘 SSD”拆分后两个关键词各自计算一遍权重最终汇总。常见做法是每个关键词都算一遍总和再加起来SELECT id, title, (CASE WHEN title LIKE %闪存盘% THEN 5 ELSE 0 END) (CASE WHEN title LIKE %SSD% THEN 5 ELSE 0 END) (CASE WHEN summary LIKE %闪存盘% THEN 2 ELSE 0 END) (CASE WHEN summary LIKE %SSD% THEN 2 ELSE 0 END) AS relevance FROM articles HAVING relevance 0 ORDER BY relevance DESC, id DESC;这里要留意一个问题只给“闪存盘”命中权重、不给“SSD”命中权重会造成搜索结果偏向某一边。如果用户输入的是两个词通常意味着两个词同等重要或者接近同等重要。所以在多关键词场景下权重设计要提前多做几组测试不能想当然。4. 性能瓶颈LIKE 模糊查询的真正代价4.1 为什么不建议在大表里无脑 OR LIKE权重排序写得再漂亮也得考虑性能。MySQL 里的LIKE %关键词%如果通配符放在字符串两边索引是不起作用的。原因很直观索引本质上是按前缀顺序排列的而%关键词%要求字符串中间或结尾匹配B 树的索引结构和这种匹配方式对不上。所以数据库只能做全表扫描把每一行数据都拉出来用那行的字段值和关键词做逐字符匹配。如果是几万行的小表我实测过查询时间一般还能控制在几十毫秒到几百毫秒之间。但到几十万行加上多个字段 LIKE查询时间就可能膨胀到几秒。这个延迟对后台管理页面也许还能接受对前端搜索接口来说基本等于不可用。常规解法有几种改成LEFT LIKE前缀匹配以命中索引或者用全文索引或者引入外部搜索引擎。考虑到这篇文章讲的是 MySQL 内的做法我重点讲全文索引。4.2 全文索引下的权重排序替代方案MySQL 的 InnoDB 引擎从 5.6 版本开始支持中文全文索引吗严格说MySQL 默认的全文索引分词形式对中文不够友好需要借助 ngram 解析器。如果用 ngram 分词器建全文索引中文关键词是可以切分和匹配的。一个典型用法如下ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, tags, summary) WITH PARSER ngram;然后查询SELECT id, title, MATCH(title, tags, summary) AGAINST(闪存盘 IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH(title, tags, summary) AGAINST(闪存盘 IN BOOLEAN MODE) ORDER BY relevance DESC;MATCH AGAINST会自己计算相关性分值这个分值综合考虑了词频、字段长度、文档频率等因素比手写 CASE WHEN 更科学。全文索引还能用IN BOOLEAN MODE指定某些词必须出现、某些词禁止出现灵活性也高一些。但要注意全文索引的相关性排序无法精确控制“标题字段比正文字段权重高”这件事。MySQL 默认的全文索引相关性计算是把所有索引字段放在一起算的。如果想要字段级别权重的控制可以把标题、标签、摘要建成三个独立的全文索引然后在 SELECT 里分别计算 MATCH 分值再乘上自己的权重SELECT id, title, MATCH(title) AGAINST(闪存盘 IN BOOLEAN MODE) * 5 MATCH(tags) AGAINST(闪存盘 IN BOOLEAN MODE) * 3 MATCH(summary) AGAINST(闪存盘 IN BOOLEAN MODE) * 2 AS relevance FROM articles HAVING relevance 0 ORDER BY relevance DESC, id DESC;这种写法在数据量和匹配精度上比纯 LIKE 好了不少。但代价是索引维护成本和查询复杂度都上去了而且多 MATCH 子句的解析开销也不低。要不要这么做得看数据规模和排性能要求。5. 常见问题与排查技巧实录5.1 权重排序不生效HAVING 的坑我踩过的一个很典型的坑在 WHERE 里过滤关键词后SELECT 里计算好了 relevance 别名然后在 HAVING 里用relevance 0过滤结果发现查出来的行数和只写 WHERE 时不一样有时候甚至全部查不出来。排查后发现MySQL 对 HAVING 里的别名引用遵循的顺序是先计算 SELECT 再执行 HAVING这本身没问题。问题出在 WHERE 条件写得太严格比如 WHERE 子句里用title LIKE %闪存盘% OR content LIKE %闪存盘%然后 HAVING 里又要求relevance 0看起来没毛病但实际上如果内容命中在 tags 字段里而 WHERE 只考虑了 title 和 content那这行在 WHERE 阶段就被过滤掉了HAVING 里算出来永远是 0。所以WHERE 里的 OR 字段列表必须和权重计算涉及的字段完全保持一致。否则就会出现“数据明明有匹配但被半路枪毙”的情况。5.2 字符串里中文与时区编码导致 LIKE 匹配错乱中文系统的 LIKE 匹配偶尔会遇到诡异的结果比如搜“SSD”能匹配到“SSD固态硬盘”但搜“固态硬盘”匹配不到或者某些字符明明在字符串里LIKE 却查不到。这种问题大多数出在字符集和排序规则上。数据库连接字符集、表字符集、字段字符集三者要一致建议统一使用 utf8mb4。如果你用了 utf8mb3旧称 utf8部分生僻字、特殊符号就会出问题。此外 LIKE 匹配时MySQL 会根据排序规则判断大小写和重音。比如在 utf8mb4_general_ci 排序规则下大小写不敏感在 utf8mb4_bin 下大小写敏感。这个差异会影响搜索“ssd”是否能查到“SSD”。建议检查方式SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE collation_server; SHOW TABLE STATUS LIKE articles\G如果发现表和字段的排序规则不统一可以用ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin这类语句统一转换。不过这个操作对大表会产生全表重建比较重一般放在低峰期执行。5.3 ORDER BY 和 WHERE 都用到 LIKE能否走索引很多人问ORDER BY (title LIKE %闪存盘%) DESC这一列能不能建索引加速结论是不能。因为LIKE %x%既可以匹配字符串开头也可以匹配中间和结尾索引无法预判结果。MySQL 只能先扫描满足条件的行再对结果集排序。如果 WHERE 条件本身能命中索引比如有一个精确匹配的字段那部分扫描可以快一些但 ORDER BY 里的 LIKE 表达式本身无法利用索引。所以性能优化的方向不要尝试给 LIKE 表达式建索引而应该尝试以下两个思路。一是缩小扫描范围比如用 OR 里加一个“能走索引”的精确条件先缩小候选集再在候选集里做 LIKE 和加权。二是改用全文索引从根本上避免前缀匹配失效的问题。5.4 分页参数变化导致排序不稳定权重搜索场景下如果多个结果权重相同排序顺序可能会随着分页变化而跳变。原因是 MySQL 不会保证等值的记录顺序稳定。为了让分页稳定排序里除了 relevance建议再加上一个唯一字段比如主键 IDORDER BY relevance DESC, id DESC;加上之后即使在多并发或数据更新场景下同一页返回的内容也不会来回跳动。我遇到过一次线上反馈“搜索翻页时结果重复”排查后就是只按 relevance 排序大量结果权重相等数据库每次拿到的主键顺序不确定导致第一页和第二页有重叠。加了一个 ID 排序后问题就消失了。6. 一个可以快速复制的完整示例6.1 建表和初始化数据为了更直观我给一个可以直接跑的例子。假设我们做的是一个商品信息表字段包括商品名、品牌、标签、描述CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, brand_name VARCHAR(50) DEFAULT , tags VARCHAR(200) DEFAULT , description TEXT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO products (product_name, brand_name, tags, description) VALUES (闪存盘128GB USB3.0, 闪迪, U盘/闪存盘/便携, 高速传输闪存盘适合日常文件拷贝), (机械键盘青轴, 某品牌, 键盘/办公/外设, 青轴段落感明显适合打字), (闪存盘钥匙扣款64GB, 某品牌, U盘/闪存盘, 钥匙扣设计方便随身携带使用闪存盘存储), (移动硬盘1TB, 某品牌, 存储/移动硬盘, 大容量存储不适合低容量闪存盘场景);6.2 权重查询语句假设业务方认为商品名命中权重 5品牌命中 4标签命中 3描述命中 1。那么查询可以写成SELECT id, product_name, brand_name, tags, (CASE WHEN product_name LIKE %闪存盘% THEN 5 ELSE 0 END) (CASE WHEN brand_name LIKE %闪存盘% THEN 4 ELSE 0 END) (CASE WHEN tags LIKE %闪存盘% THEN 3 ELSE 0 END) (CASE WHEN description LIKE %闪存盘% THEN 1 ELSE 5 END) AS relevance FROM products WHERE product_name LIKE %闪存盘% OR brand_name LIKE %闪存盘% OR tags LIKE %闪存盘% OR description LIKE %闪存盘% HAVING relevance 0 ORDER BY relevance DESC, id DESC;在这个例子里德美闪存盘钥匙扣款既有商品名命中也有标签命中得分是 5 3 80053款在名称和描述命中得分是 5 1 6移动硬盘只有描述里出现了“闪存盘”得分是 1。所以搜索“闪存盘”时结果就能明显体现出“谁更相关”了。6.3 扩展到多关键词如果想支持“闪存盘 128GB”这种多关键词可以将示例稍微改一下每个关键词计算一套子得分再求和SELECT id, product_name, (CASE WHEN product_name LIKE %闪存盘% AND product_name LIKE %128GB% THEN 10 WHEN product_name LIKE %闪存盘% THEN 5 WHEN product_name LIKE %128GB% THEN 5 ELSE 0 END) (CASE WHEN tags LIKE %闪存盘% THEN 3 ELSE 0 END) AS relevance FROM products WHERE product_name LIKE %闪存盘% OR product_name LIKE %128GB% OR tags LIKE %闪存盘% HAVING relevance 0 ORDER BY relevance DESC, id DESC;这里加了一个复合条件商品名同时命中两个词给 10 分鼓励结果尽量匹配完整词组而不是只命中其中一个词。设计权重时要记住一个原则同时命中多个关键词应该比命中单个关键词得分更高否则用户搜长关键词时单字匹配的结果就会排在最前面语义不符。7. 几个严重值得收藏的实战建议7.1 关键词长度和权重的关系如果用户搜索关键词很短比如一个字“盘”那在标题、标签、正文里出现的频率会极高。这种情况下权重排序的意义会被稀释因为几乎所有行都会命中。我实际测过搜“盘”字时标题命中的结果数可能占到全表的 80%。这时候简单的字段权重排序基本没有区分度。针对短词建议把命中次数的统计权重加大或者引入“完全匹配优先”的规则字段值和关键词完全相等时给更高的分。(CASE WHEN title 闪存盘 THEN 10 WHEN title LIKE %闪存盘% THEN 5 ELSE 0 END)这个技巧对长短关键词都有用。“完全等于”比“模糊包含”更能代表精确相关。7.2 不要忘了 LIKE 的特殊字符转义LIKE 语句中的百分号%和下划线_是通配符。如果用户搜索的内容里恰好包含这些字符比如“Python_入门”LIKE 会把_当成“任意一个字符”来匹配结果可能匹配出来一堆“PythonX入门”之类的内容。更麻烦的是百分号会让 LIKE 匹配全表。处理办法是用 ESCAPE 转义WHERE title LIKE %Python\_入门% ESCAPE \\;或者干脆在拼 SQL 前用addcslashes这类函数把特殊字符转义掉。很多搜索引擎系统都踩过这个坑尤其是在做搜索日志分析时发现用户输入了含通配符的字符串导致查询超时。7.3 排序字段的选择会影响慢查询日志最后分享一个运维层面的经验。这种带权重排序的 SQL 往往是慢查询日志里的常客。原因不只是字符串的走复杂扫描还因为每一行都要执行多次 LIKE 和计算表达式。建议把这类查询单独归类设置独立超时时间和说明注释。如果是用程序框架拼接 SQL要注意别用 OR 把大量关键词拼在一起拆分为多次查询后合并结果有时候反而更快因为可以分别利用缓存和短查询优势。我在实际项目里的一个经验是先用相对高效的过滤条件比如加一个简单的状态字段、分类字段缩小候选集到几千行内再做加权排序。这个优化比任何索引奇技淫巧都管用也是我调整所有复杂 LIKE 搜索的第一动作。搜索排序不是一个“写完就完事”的功能而是一个需要持续看数据、调权重、验证效果的过程。愿所有被排序折磨过的人都能通过这套思路少走几步弯路。