搜过关键词、做过站内搜索的人，大概都见过这种场景：用户在搜索框里输入“手机”，数据库里同时存在“手机”、“智能手机”、“手机壳”、“旧手机回收”这些记录，用一条最简单的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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;插几条有代表性的测试数据：
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 转成NULL，ROUND(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列是ALL，rows列是几十万，意味着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 就能在性能可控的前提下，让搜索结果贴合业务意图。我这个方案不一定适合所有场景，但作为从零开始的参考模板，你可以直接拿去改造，然后再根据线上效果调系数。
