搜过关键词、做过站内搜索的人大概都见过这种场景用户在搜索框里输入“手机”数据库里同时存在“手机”、“智能手机”、“手机壳”、“旧手机回收”这些记录用一条最简单的WHERE name LIKE %手机%查出来你根本没法控制谁排在前面。我曾经在一个后台管理系统里因为搜索结果排序太“傻”被运营同事追着改了好几次需求——不是商品不对是顺序不对。比如用户明明在找新发布的机型热门爆款却排在第三屏之外点击率直接受影响。MySQL 的ORDER BY本身很简单难的是它和LIKE组合在一起时怎么让结果按你的业务意图排序。这背后其实是一套“给记录打分再按分数排”的权重模型也就是标题里说的“根据 LIKE 查找关键字段设置权重排序”。这套东西不需要上搜索引擎不需要引入外部组件纯靠 SQL 就能实现非常适合中小型项目、后台管理、以及那些数据量还没大到必须上 Elasticsearch 的业务场景。这篇文章我就从一个实际开发者的角度把权重排序的原理、SQL 写法、性能优化思路、还有我踩过的坑一次讲清楚。适合正在做搜索功能的后端开发者、需要优化查询排序的 DBA 入场也适合刚接触 SQL 的初学者把它当一篇进阶实战笔记看。1. 先想清楚LIKE 查询为什么需要“权重排序”很多人写搜索接口时习惯先写过滤条件把数据圈出来再随手加个ORDER BY create_time DESC。数据量小的时候没什么感觉等记录多了、业务方开始提“能不能让更匹配的排前面”你才发现默认排序根本不能满足需求。1.1 默认排序的缺陷MySQL 里不带ORDER BY的查询结果顺序取决于存储引擎怎么扫描数据。MyISAM 可能按插入顺序InnoDB 大多时候按主键顺序但这是个“物理顺序”和用户要的“相关顺序”是两码事。即使你加了ORDER BY create_time DESC也只是把时间最新的顶到前面。可用户搜索“蓝牙耳机”时他真正想看的是“名称里正好包含蓝牙耳机”的商品而不是三天前上架标题里带“蓝牙”的无关商品。这就是默认排序的核心缺陷它没有考虑“查询关键词在记录中的命中程度”。1.2 权重排序的本质先算分再排序权重排序的思路说起来很朴素把每条记录的“匹配相关度”换算成分数然后用这个分数去驱动排序。比如一条记录的名称是“2024新款智能手机”搜索词是“手机”。这条记录里“手机”命中了且命中位置在末尾它是前缀匹配、后缀匹配还是中间匹配命中的字段是商品名称、商品描述还是品牌标签匹配的完整程度是高是低把这些维度量化成数值加在一起就得到了“分数”。最后ORDER BY score DESC, id DESC谁分高谁排前面。这才是权重排序的完整链路先圈定结果集再给结果集逐行打分最后按分值排列。2. 一套能直接用的权重评分模型我自己最常用的是“分层打分法”。一个搜索词在一条记录上的匹配情况拆成几个独立维度分别打分再汇总。这套模型不一定是最优解但方向正确、扩展容易而且每一步都能用 SQL 直接算出来。2.1 第一层匹配形态决定基础分值先看最简单的维度关键词是以什么形态出现在字段里的。完全相等即name 手机这是最高匹配相关度百分百。前缀匹配name LIKE 手机%表示字段以关键词开头。比如“手机支架”。后缀匹配name LIKE %手机表示字段以关键词结尾。比如“二手手机”。中间匹配name LIKE %手机%关键词藏在字段中间。比如“智能手机配件”。这里有一个基础的认知前缀匹配的置信度要高于后缀匹配后缀匹配又要高于纯粹的中间包含。为什么你可以想想 B 树的索引原理前缀匹配往往能利用索引进行高效范围扫描而且从语感上说“XX手机”通常比“XX手机XX”更接近搜索意图。用 SQL 表达就是CASE WHEN嵌套CASE WHEN name 手机 THEN 100 WHEN name LIKE 手机% THEN 80 WHEN name LIKE %手机 THEN 60 WHEN name LIKE %手机% THEN 40 ELSE 0 END这个模型里我把完全相等的分值设为 100前缀 80后缀 60中间 40。分值之间的差距要拉开否则最后排序时区分度不够。当然分值可以根据业务调整比如商品库没有完全相等场景时可以把前缀匹配拉到 90。2.2 第二层匹配位置越靠前得分越高同样是中间匹配name 智能手机壳和name 手机智能壳命中位置完全不同但用第一层模型打分时得分是一样的。这不够精细于是引入第二维度关键词在字段中出现的位置。SQL 里可以用LOCATE()函数拿到关键词的起始位置返回的是从 1 开始的索引。位置越靠前说明关键词越靠前用户的视觉权重越高。比如LOCATE(手机, 智能手机壳)返回 2而LOCATE(手机, 手机智能壳)返回 1。位置分怎么设计我常用的是ROUND(100 / LOCATE(手机, name))如果关键词在第一位位置分是 100在第二位位置分是 50在第五位位置分是 20。这个函数天然把“越靠前得分越高”这个逻辑变成数学关系。要注意的是LOCATE在没找到时返回 0所以这个计算要放进CASE WHEN分支里确保只对确实匹配的记录计算。位置分还有一个更细腻的玩法用LENGTH(name)参与计算。关键词命中的字段越短说明关键词占整个字段的比重越大相关度越高。比如同样是“苹果”命中了“苹果”和“苹果笔记本电脑”前者明显更聚焦。2.3 第三层多关键词叠加与字段差异真实业务里搜索词往往不是单一关键词。用户会搜“红色连衣裙”也可能搜“2024 新款”。这时可以把搜索词按空格拆成多个关键词每个关键词都按前面的维度打分最后加总。多关键词的 SQL 会膨胀得比较厉害但逻辑是清晰的SELECT *, (CASE WHEN name LIKE %红色% THEN 40 ELSE 0 END CASE WHEN name LIKE %连衣裙% THEN 40 ELSE 0 END) AS keyword_total FROM products WHERE name LIKE %红色% OR name LIKE %连衣裙% ORDER BY keyword_total DESC;除了关键词维度字段维度也很关键。同一个关键词出现在商品标题里和出现在商品描述里权重应该不一样。我在实际项目中标题命中通常给 1.5 倍加权描述命中给 0.5 倍加权品牌命中给 2 倍加权。做法是在前面所有维度的分值上乘以字段系数。2.4 第四层综合评分与排序语句模板把以上几个维度合在一起就是一个完整的综合评分语句SELECT id, name, ( -- 标题命中基础分 位置分整体乘 1.5 权重 ( CASE WHEN name 手机 THEN 100 WHEN name LIKE 手机% THEN 80 WHEN name LIKE %手机 THEN 60 WHEN name LIKE %手机% THEN 40 ELSE 0 END ROUND(100 / LOCATE(手机, name)) ) * 1.5 -- 描述命中整体乘 0.5 权重 ( CASE WHEN description LIKE %手机% THEN 40 ELSE 0 END ROUND(100 / LOCATE(手机, description)) ) * 0.5 ) AS score FROM products WHERE name LIKE %手机% OR description LIKE %手机% ORDER BY score DESC, id DESC;这里有个关键设计容易被忽略ORDER BY score DESC, id DESC里的第二个字段。当分数相同时必须有唯一字段作为兜底排序否则分页查询时 LIMIT 的 offset 会造成数据重复或遗漏。这个坑我在第 5 节详细讲。3. 性能瓶颈与索引策略权重排序不是免费的午餐。LIKE查询本身就是性能敏感的操作再叠加CASE WHEN计算如果表数据量上了几十万甚至百万级接口就可能从毫秒级掉到秒级。这里必须先聊性能。3.1 前缀匹配为什么能走索引很多人以为LIKE一定走不了索引这是错的。InnoDB 的 B 树索引支持最左前缀匹配所以name LIKE 手机%这个条件可以用(name)上的索引做范围扫描。它的原理类似于按字典查找先定位到“手”字开头的第一条记录然后向后扫描直到不再以“手机”开头为止。这个范围的记录数通常不多性能可控。真正会让索引失效的是前导通配符LIKE %手机%。它没法从索引树的某个节点开始定位只能全量扫描。数据量大时这种条件基本等于把整张表过一遍。所以设计权重排序时要区分两个阶段WHERE阶段负责圈定范围尽量用能走索引的条件哪怕粗一点ORDER BY阶段负责精确打分排序这里的LIKE是逐行计算的索引帮不上忙重点在于减少参与计算的行数。3.2 大表场景下的三条优化路线如果你表的规模已经不小我建议按顺序考虑三条优化路线。第一条是拆结构化字段。把商品名称里的核心关键词抽成独立字段比如brand、category、model查询时对这些字段做精确匹配而不是全表LIKE。品牌字段用匹配能完美走索引排序时再对名称字段做加权。这是性价比最高的方式但需要业务侧配合维护字段。第二条是限定查询边界。业务上一般存在天然边界比如商品只属于某个店铺、某个类目、某个时间范围。在WHERE里先加store_id ?或status 1这样的强过滤条件把扫描范围从全表缩小到一个分区再跑LIKE权重排序性能会明显改善。第三条是后台任务预处理排序结果。如果搜索结果的排序不需要百分百实时可以每分钟由后台任务跑一次权重排序把排名前 N 的结果固化到一张表前端查询只读这张排序结果表。这样把 CPU 密集型的排序计算从请求链路里剥离出去响应速度会非常稳定。我特别想强调一个容易被忽略的点在大数据量下ORDER BY score DESC会导致 MySQL 生成一个巨大的临时文件来做 filesort。如果参与排序的行有几万条就会有磁盘 I/O。优化时可以用EXPLAIN看查询计划如果Extra列出现Using temporary; Using filesort就要留意性能问题了。4. 实操案例商品搜索的权重排序完整实现光讲理论容易飘下面用一个完整案例串起来。这个案例是我在某电商后台模块里做过的一个简化版本场景非常典型商品搜索需要把“高匹配、热门、新品”三个因素同时考虑进去。4.1 表结构设计与初始数据我建了一张商品测试表字段不多但足够演示CREATE TABLE product_demo ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, description TEXT, sales_count INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插几条有代表性的测试数据INSERT INTO product_demo (name, description, sales_count, created_at) VALUES (手机, 最新款旗舰手机支持5G全网通, 10000, 2024-03-01 10:00:00), (智能手机, AI拍照手机大电池长续航, 5000, 2024-02-15 09:30:00), (手机壳, 硅胶手机壳适用多款机型, 2000, 2024-01-20 08:00:00), (二手手机, 九成新二手手机低价出售, 800, 2024-03-10 11:20:00), (智能手环, 运动健康监测手环非手机产品, 3000, 2024-02-01 12:00:00), (笔记本电脑, 轻薄本办公本适合程序员, 1500, 2024-03-05 14:30:00);注意看这些数据里有完全匹配的“手机”有前缀匹配的“手机壳”有后缀匹配的“二手手机”有中间匹配的“智能手机”还有完全无关的“智能手环”、“笔记本电脑”。一上来就能把各种情况都测到。4.2 核心查询 SQL 与结果验证先跑一条最朴素的查询不加任何权重处理SELECT id, name FROM product_demo WHERE name LIKE %手机% OR description LIKE %手机% ORDER BY sales_count DESC;结果是“手机”销量 10000排第一“智能手机”销量 5000排第二“手机壳”销量 2000排第三“二手手机”销量 800排第四“智能手环”销量 3000反而因为描述里没匹配到手机而被排除在外……仔细看name没有“手环”匹配description里有“手机”吗我写的是“非手机产品”所以它被匹配上了。这就是一个混乱的场景匹配语义不对排序更是乱。现在加上权重评分SELECT id, name, ( CASE WHEN name 手机 THEN 100 WHEN name LIKE 手机% THEN 80 WHEN name LIKE %手机 THEN 60 WHEN name LIKE %手机% THEN 40 ELSE 0 END ROUND(100 / NULLIF(LOCATE(手机, name), 0)) ) * 1.5 ( CASE WHEN description LIKE %手机% THEN 20 ELSE 0 END ROUND(100 / NULLIF(LOCATE(手机, description), 0)) ) * 0.3 AS score FROM product_demo WHERE name LIKE %手机% OR description LIKE %手机% ORDER BY score DESC, sales_count DESC;这里我用了一个小技巧NULLIF(LOCATE(手机, name), 0)。当LOCATE返回 0 时NULLIF会把 0 转成NULLROUND(100 / NULL)会得到NULL但因为外层处于CASE WHEN ... THEN分支只有匹配的记录才会计算位置分。这个写法避免了除以零的报错。查出来的结果顺序是手机、手机壳、智能手机、二手手机、智能手环。这个顺序是不是比刚才合理得多“手机”完全匹配排第一“手机壳”前缀命中且位置靠前排第二“智能手机”是中间命中排第三“二手手机”后缀命中排第四描述里带“非手机产品”的手环虽然被圈进来了但得分最低排最后。这就是权重排序想达到的效果。4.3 翻车记录与修正过程第一次跑上面这条 SQL 时我遇到一个很尴尬的问题“智能手机”排到了“手机壳”前面。原因是位置分占比太大了。LOCATE(手机, 智能手机)返回 2位置分 50而LOCATE(手机, 手机壳)返回 1位置分 100。基础分上“智能手机”是中间匹配 40 分“手机壳”前缀匹配 80 分两者差距 40 分但位置分差距是 50 分直接把基础分差距抵消还反超了。这暴露了一个权重设计问题位置分的量级不该和基础分的量级完全一致。我的修正方法是给位置分加一个系数让它只占小头ROUND(30 / LOCATE(手机, name))也就是说位置分满分是 30只作为微调因子而不是主导因子。这样修改后基础分 80 的“手机壳”依然稳稳排在基础分 40 的“智能手机”前面位置分只用来调整同档位内部的名次。这是权重排序里一个非常重要的经验各维度的分值与系数决定了排序的主导逻辑一定要想清楚谁是大头、谁是小头。另一个修正点是为ORDER BY加上唯一键兜底。第一次测试时我直接ORDER BY score DESC发现“智能手机”和“二手手机”的分数恰好一样结果两次查询出来的前后顺序不一样。加上sales_count DESC作为第二排序条件后结果才稳定。这个细节在后面做分页时尤其重要。5. 常见问题与排查技巧实录权重排序的 SQL 写起来不难但线上跑起来问题往往出现在你没预料到的地方。下面这些问题都是我实际碰到过、并且花时间排查过的。5.1 排序结果不稳定 / 分页重复症状是前端分页加载时第 1 页出现过的记录第 2 页又出现了或者排序顺序每次刷新都变。原因几乎都是ORDER BY后面的字段组合不唯一。当两条记录的评分完全相同MySQL 返回它们的相对顺序是不确定的这个“不确定”在分页场景下会被放大。解决办法有两个一是给ORDER BY补充一个唯一字段比如ORDER BY score DESC, id DESC二是给参与排序的字段建立一个联合索引让排序尽量走索引避免 filesort。如果你发现自己加了id之后仍然不稳定那多半是score表达式里用了随机函数或者依赖了不稳定的数据源。检查一下 SQL 里有没有RAND()或容易变化的时间函数。5.2 中文匹配在 utf8_general_ci 下走偏这是个很容易踩的字符集坑。utf8_general_ci和utf8_unicode_ci在排序和匹配规则上有差异特别是中文场景下general_ci会对某些中文字符的匹配做“宽松处理”导致LIKE匹配出一些“看起来不应该匹配”的记录。如果你的表和数据库使用的是utf8_general_ci而业务要求精确区分中文建议把字段改成utf8mb4_unicode_ci或utf8mb4_0900_ai_ci。我遇到过最诡异的一次搜索“苹果”时LIKE %苹果%居然匹配出了“青苹”相关的记录。排查半天发现是排序规则把“果”字和某个扩展字符视为等价。所以建表时字符串字段的 collation 要选对别在权重排序上线后才来折腾数据迁移。5.3 EXPLAIN 显示全表扫描怎么办EXPLAIN的type列是ALLrows列是几十万意味着WHERE条件没把扫描范围缩下来。核心原因是name LIKE %手机%这种前通配条件走不了索引。我的处理优先级是如果没有前导通配必须可用改写为name LIKE 手机% OR name LIKE %手机把第一个条件放在前面让优化器至少有机会用上索引如果确实需要中间匹配那就别指望数据库帮你扛住全表扫描回到第 3 节的优化路线拆字段、加过滤边界、做预处理如果业务方允许把搜索限定到最近三个月的数据加一个created_at范围条件让扫描行数降两个数量级。5.4 ORDER BY 与 GROUP BY 同时用的问题有次我需要统计关键词命中商品数再按权重排序直接写了ORDER BY score DESC GROUP BY category_id。这是语法错误因为GROUP BY之后非聚合列不能直接参与排序。我的解决方案是把评分和分组拆成两步先按商品算得分再子查询里做分组聚合。SQL 大概是SELECT category_id, MAX(score) AS max_score, COUNT(*) AS cnt FROM ( SELECT category_id, 计算权重得分 AS score FROM product_demo WHERE name LIKE %手机% ) t GROUP BY category_id ORDER BY max_score DESC, cnt DESC;这个写法既保住了分组统计又让排序按权重走实测结果很稳。它的核心思路是任何逻辑先用子查询算好再在外层做聚合不要指望一句 SQL 解决所有问题。6. 什么时候该放弃 LIKE 权重排序权重排序不是银弹。我在前面强调过这套方案适合中小数据量但如果你已经优化到极限查询还是超过 500ms就该考虑换方案了。6.1 数据量阈值判断怎么判断阈值我的经验是单表记录数超过 50 万行且每次搜索都要用LIKE %关键词%做前通配匹配时基本就到了 MySQL 硬扛的极限。这时候即便你拆字段、加缓存效果也有限因为每次搜索都可能扫描几万行做评分计算。还有一个判断维度是搜索并发。如果你的搜索接口每秒请求量超过 20 次且每次查询都是几百毫秒数据库的连接池很快就会被占满。这时候权重排序的性能问题会直接拖垮整个服务的稳定性。6.2 转型方案对比如果要换方案我的建议是分两步走。第一步上 MySQL 的全文索引。MySQL 5.7 之后支持中文ngram全文解析器可以建FULLTEXT索引配合MATCH ... AGAINST做相关度排序。它的优势是语法简单性能比LIKE %关键词%高很多劣势是相关度算法是固定的不容易按业务自定义权重。适合那些“能用就行不想维护太多基础组件”的项目。第二步如果业务对搜索质量要求高比如需要同义词、拼写纠错、个性化排序那 MySQL 已经不适合你了。这时候要引入独立搜索引擎或者专用的搜索中间件。数据同步可以用消息队列保证增量更新。这个改造的成本不低但换来的搜索体验也是LIKE方案没法比的。我个人在实际操作中的体会是引入外部搜索组件要趁早越晚技术债越重。最后再分享一个小技巧。如果你一时半会儿没法改表结构又想提升LIKE权重排序的性能可以试试给name字段加一个“前缀冗余列”专门存去掉前导空格后的前 10 个字符并对这一列建普通索引。查询时优先用前缀匹配去圈定结果集再在圈定的结果集里做中间匹配的评分。这样做的效果是把全表扫描变成索引扫描几万行的结果集内部怎么排序都不用怕了。回到一开始的需求——用ORDER BY给LIKE的匹配结果加权重排序本质是给数据库查询注入一点“搜索引擎思维”。想清楚匹配形态、位置、字段这三个维度的加权逻辑写出的 SQL 就能在性能可控的前提下让搜索结果贴合业务意图。我这个方案不一定适合所有场景但作为从零开始的参考模板你可以直接拿去改造然后再根据线上效果调系数。