搜過關(guān)鍵詞、做過站內(nèi)搜索的人大概都見過這種場景用戶在搜索框里輸入“手機”數(shù)據(jù)庫里同時存在“手機”、“智能手機”、“手機殼”、“舊手機回收”這些記錄用一條最簡單的WHERE name LIKE %手機%查出來你根本沒法控制誰排在前面。我曾經(jīng)在一個后臺管理系統(tǒng)里因為搜索結(jié)果排序太“傻”被運營同事追著改了好幾次需求——不是商品不對是順序不對。比如用戶明明在找新發(fā)布的機型熱門爆款卻排在第三屏之外點擊率直接受影響。MySQL 的ORDER BY本身很簡單難的是它和LIKE組合在一起時怎么讓結(jié)果按你的業(yè)務(wù)意圖排序。這背后其實是一套“給記錄打分再按分?jǐn)?shù)排”的權(quán)重模型也就是標(biāo)題里說的“根據(jù) LIKE 查找關(guān)鍵字段設(shè)置權(quán)重排序”。這套東西不需要上搜索引擎不需要引入外部組件純靠 SQL 就能實現(xiàn)非常適合中小型項目、后臺管理、以及那些數(shù)據(jù)量還沒大到必須上 Elasticsearch 的業(yè)務(wù)場景。這篇文章我就從一個實際開發(fā)者的角度把權(quán)重排序的原理、SQL 寫法、性能優(yōu)化思路、還有我踩過的坑一次講清楚。適合正在做搜索功能的后端開發(fā)者、需要優(yōu)化查詢排序的 DBA 入場也適合剛接觸 SQL 的初學(xué)者把它當(dāng)一篇進階實戰(zhàn)筆記看。1. 先想清楚LIKE 查詢?yōu)槭裁葱枰皺?quán)重排序”很多人寫搜索接口時習(xí)慣先寫過濾條件把數(shù)據(jù)圈出來再隨手加個ORDER BY create_time DESC。數(shù)據(jù)量小的時候沒什么感覺等記錄多了、業(yè)務(wù)方開始提“能不能讓更匹配的排前面”你才發(fā)現(xiàn)默認(rèn)排序根本不能滿足需求。1.1 默認(rèn)排序的缺陷MySQL 里不帶ORDER BY的查詢結(jié)果順序取決于存儲引擎怎么掃描數(shù)據(jù)。MyISAM 可能按插入順序InnoDB 大多時候按主鍵順序但這是個“物理順序”和用戶要的“相關(guān)順序”是兩碼事。即使你加了ORDER BY create_time DESC也只是把時間最新的頂?shù)角懊妗？捎脩羲阉鳌八{牙耳機”時他真正想看的是“名稱里正好包含藍牙耳機”的商品而不是三天前上架標(biāo)題里帶“藍牙”的無關(guān)商品。這就是默認(rèn)排序的核心缺陷它沒有考慮“查詢關(guān)鍵詞在記錄中的命中程度”。1.2 權(quán)重排序的本質(zhì)先算分再排序權(quán)重排序的思路說起來很樸素把每條記錄的“匹配相關(guān)度”換算成分?jǐn)?shù)然后用這個分?jǐn)?shù)去驅(qū)動排序。比如一條記錄的名稱是“2024新款智能手機”搜索詞是“手機”。這條記錄里“手機”命中了且命中位置在末尾它是前綴匹配、后綴匹配還是中間匹配命中的字段是商品名稱、商品描述還是品牌標(biāo)簽匹配的完整程度是高是低把這些維度量化成數(shù)值加在一起就得到了“分?jǐn)?shù)”。最后ORDER BY score DESC, id DESC誰分高誰排前面。這才是權(quán)重排序的完整鏈路先圈定結(jié)果集再給結(jié)果集逐行打分最后按分值排列。2. 一套能直接用的權(quán)重評分模型我自己最常用的是“分層打分法”。一個搜索詞在一條記錄上的匹配情況拆成幾個獨立維度分別打分再匯總。這套模型不一定是最優(yōu)解但方向正確、擴展容易而且每一步都能用 SQL 直接算出來。2.1 第一層匹配形態(tài)決定基礎(chǔ)分值先看最簡單的維度關(guān)鍵詞是以什么形態(tài)出現(xiàn)在字段里的。完全相等即name 手機這是最高匹配相關(guān)度百分百。前綴匹配name LIKE 手機%表示字段以關(guān)鍵詞開頭。比如“手機支架”。后綴匹配name LIKE %手機表示字段以關(guān)鍵詞結(jié)尾。比如“二手手機”。中間匹配name LIKE %手機%關(guān)鍵詞藏在字段中間。比如“智能手機配件”。這里有一個基礎(chǔ)的認(rèn)知前綴匹配的置信度要高于后綴匹配后綴匹配又要高于純粹的中間包含。為什么你可以想想 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這個模型里我把完全相等的分值設(shè)為 100前綴 80后綴 60中間 40。分值之間的差距要拉開否則最后排序時區(qū)分度不夠。當(dāng)然分值可以根據(jù)業(yè)務(wù)調(diào)整比如商品庫沒有完全相等場景時可以把前綴匹配拉到 90。2.2 第二層匹配位置越靠前得分越高同樣是中間匹配name 智能手機殼和name 手機智能殼命中位置完全不同但用第一層模型打分時得分是一樣的。這不夠精細(xì)于是引入第二維度關(guān)鍵詞在字段中出現(xiàn)的位置。SQL 里可以用LOCATE()函數(shù)拿到關(guān)鍵詞的起始位置返回的是從 1 開始的索引。位置越靠前說明關(guān)鍵詞越靠前用戶的視覺權(quán)重越高。比如LOCATE(手機, 智能手機殼)返回 2而LOCATE(手機, 手機智能殼)返回 1。位置分怎么設(shè)計我常用的是ROUND(100 / LOCATE(手機, name))如果關(guān)鍵詞在第一位位置分是 100在第二位位置分是 50在第五位位置分是 20。這個函數(shù)天然把“越靠前得分越高”這個邏輯變成數(shù)學(xué)關(guān)系。要注意的是LOCATE在沒找到時返回 0所以這個計算要放進CASE WHEN分支里確保只對確實匹配的記錄計算。位置分還有一個更細(xì)膩的玩法用LENGTH(name)參與計算。關(guān)鍵詞命中的字段越短說明關(guān)鍵詞占整個字段的比重越大相關(guān)度越高。比如同樣是“蘋果”命中了“蘋果”和“蘋果筆記本電腦”前者明顯更聚焦。2.3 第三層多關(guān)鍵詞疊加與字段差異真實業(yè)務(wù)里搜索詞往往不是單一關(guān)鍵詞。用戶會搜“紅色連衣裙”也可能搜“2024 新款”。這時可以把搜索詞按空格拆成多個關(guān)鍵詞每個關(guān)鍵詞都按前面的維度打分最后加總。多關(guān)鍵詞的 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;除了關(guān)鍵詞維度字段維度也很關(guān)鍵。同一個關(guān)鍵詞出現(xiàn)在商品標(biāo)題里和出現(xiàn)在商品描述里權(quán)重應(yīng)該不一樣。我在實際項目中標(biāo)題命中通常給 1.5 倍加權(quán)描述命中給 0.5 倍加權(quán)品牌命中給 2 倍加權(quán)。做法是在前面所有維度的分值上乘以字段系數(shù)。2.4 第四層綜合評分與排序語句模板把以上幾個維度合在一起就是一個完整的綜合評分語句SELECT id, name, ( -- 標(biāo)題命中基礎(chǔ)分 位置分整體乘 1.5 權(quán)重 ( 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 權(quán)重 ( 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;這里有個關(guān)鍵設(shè)計容易被忽略O(shè)RDER BY score DESC, id DESC里的第二個字段。當(dāng)分?jǐn)?shù)相同時必須有唯一字段作為兜底排序否則分頁查詢時 LIMIT 的 offset 會造成數(shù)據(jù)重復(fù)或遺漏。這個坑我在第 5 節(jié)詳細(xì)講。3. 性能瓶頸與索引策略權(quán)重排序不是免費的午餐。LIKE查詢本身就是性能敏感的操作再疊加CASE WHEN計算如果表數(shù)據(jù)量上了幾十萬甚至百萬級接口就可能從毫秒級掉到秒級。這里必須先聊性能。3.1 前綴匹配為什么能走索引很多人以為LIKE一定走不了索引這是錯的。InnoDB 的 B 樹索引支持最左前綴匹配所以name LIKE 手機%這個條件可以用(name)上的索引做范圍掃描。它的原理類似于按字典查找先定位到“手”字開頭的第一條記錄然后向后掃描直到不再以“手機”開頭為止。這個范圍的記錄數(shù)通常不多性能可控。真正會讓索引失效的是前導(dǎo)通配符LIKE %手機%。它沒法從索引樹的某個節(jié)點開始定位只能全量掃描。數(shù)據(jù)量大時這種條件基本等于把整張表過一遍。所以設(shè)計權(quán)重排序時要區(qū)分兩個階段WHERE階段負(fù)責(zé)圈定范圍盡量用能走索引的條件哪怕粗一點ORDER BY階段負(fù)責(zé)精確打分排序這里的LIKE是逐行計算的索引幫不上忙重點在于減少參與計算的行數(shù)。3.2 大表場景下的三條優(yōu)化路線如果你表的規(guī)模已經(jīng)不小我建議按順序考慮三條優(yōu)化路線。第一條是拆結(jié)構(gòu)化字段。把商品名稱里的核心關(guān)鍵詞抽成獨立字段比如brand、category、model查詢時對這些字段做精確匹配而不是全表LIKE。品牌字段用匹配能完美走索引排序時再對名稱字段做加權(quán)。這是性價比最高的方式但需要業(yè)務(wù)側(cè)配合維護字段。第二條是限定查詢邊界。業(yè)務(wù)上一般存在天然邊界比如商品只屬于某個店鋪、某個類目、某個時間范圍。在WHERE里先加store_id ?或status 1這樣的強過濾條件把掃描范圍從全表縮小到一個分區(qū)再跑LIKE權(quán)重排序性能會明顯改善。第三條是后臺任務(wù)預(yù)處理排序結(jié)果。如果搜索結(jié)果的排序不需要百分百實時可以每分鐘由后臺任務(wù)跑一次權(quán)重排序把排名前 N 的結(jié)果固化到一張表前端查詢只讀這張排序結(jié)果表。這樣把 CPU 密集型的排序計算從請求鏈路里剝離出去響應(yīng)速度會非常穩(wěn)定。我特別想強調(diào)一個容易被忽略的點在大數(shù)據(jù)量下ORDER BY score DESC會導(dǎo)致 MySQL 生成一個巨大的臨時文件來做 filesort。如果參與排序的行有幾萬條就會有磁盤 I/O。優(yōu)化時可以用EXPLAIN看查詢計劃如果Extra列出現(xiàn)Using temporary; Using filesort就要留意性能問題了。4. 實操案例商品搜索的權(quán)重排序完整實現(xiàn)光講理論容易飄下面用一個完整案例串起來。這個案例是我在某電商后臺模塊里做過的一個簡化版本場景非常典型商品搜索需要把“高匹配、熱門、新品”三個因素同時考慮進去。4.1 表結(jié)構(gòu)設(shè)計與初始數(shù)據(jù)我建了一張商品測試表字段不多但足夠演示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;插幾條有代表性的測試數(shù)據(jù)INSERT INTO product_demo (name, description, sales_count, created_at) VALUES (手機, 最新款旗艦手機支持5G全網(wǎng)通, 10000, 2024-03-01 10:00:00), (智能手機, AI拍照手機大電池長續(xù)航, 5000, 2024-02-15 09:30:00), (手機殼, 硅膠手機殼適用多款機型, 2000, 2024-01-20 08:00:00), (二手手機, 九成新二手手機低價出售, 800, 2024-03-10 11:20:00), (智能手環(huán), 運動健康監(jiān)測手環(huán)非手機產(chǎn)品, 3000, 2024-02-01 12:00:00), (筆記本電腦, 輕薄本辦公本適合程序員, 1500, 2024-03-05 14:30:00);注意看這些數(shù)據(jù)里有完全匹配的“手機”有前綴匹配的“手機殼”有后綴匹配的“二手手機”有中間匹配的“智能手機”還有完全無關(guān)的“智能手環(huán)”、“筆記本電腦”。一上來就能把各種情況都測到。4.2 核心查詢 SQL 與結(jié)果驗證先跑一條最樸素的查詢不加任何權(quán)重處理SELECT id, name FROM product_demo WHERE name LIKE %手機% OR description LIKE %手機% ORDER BY sales_count DESC;結(jié)果是“手機”銷量 10000排第一“智能手機”銷量 5000排第二“手機殼”銷量 2000排第三“二手手機”銷量 800排第四“智能手環(huán)”銷量 3000反而因為描述里沒匹配到手機而被排除在外……仔細(xì)看name沒有“手環(huán)”匹配description里有“手機”嗎我寫的是“非手機產(chǎn)品”所以它被匹配上了。這就是一個混亂的場景匹配語義不對排序更是亂?，F(xiàn)在加上權(quán)重評分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)。當(dāng)LOCATE返回 0 時NULLIF會把 0 轉(zhuǎn)成NULLROUND(100 / NULL)會得到NULL但因為外層處于CASE WHEN ... THEN分支只有匹配的記錄才會計算位置分。這個寫法避免了除以零的報錯。查出來的結(jié)果順序是手機、手機殼、智能手機、二手手機、智能手環(huán)。這個順序是不是比剛才合理得多“手機”完全匹配排第一“手機殼”前綴命中且位置靠前排第二“智能手機”是中間命中排第三“二手手機”后綴命中排第四描述里帶“非手機產(chǎn)品”的手環(huán)雖然被圈進來了但得分最低排最后。這就是權(quán)重排序想達到的效果。4.3 翻車記錄與修正過程第一次跑上面這條 SQL 時我遇到一個很尷尬的問題“智能手機”排到了“手機殼”前面。原因是位置分占比太大了。LOCATE(手機, 智能手機)返回 2位置分 50而LOCATE(手機, 手機殼)返回 1位置分 100?；A(chǔ)分上“智能手機”是中間匹配 40 分“手機殼”前綴匹配 80 分兩者差距 40 分但位置分差距是 50 分直接把基礎(chǔ)分差距抵消還反超了。這暴露了一個權(quán)重設(shè)計問題位置分的量級不該和基礎(chǔ)分的量級完全一致。我的修正方法是給位置分加一個系數(shù)讓它只占小頭ROUND(30 / LOCATE(手機, name))也就是說位置分滿分是 30只作為微調(diào)因子而不是主導(dǎo)因子。這樣修改后基礎(chǔ)分 80 的“手機殼”依然穩(wěn)穩(wěn)排在基礎(chǔ)分 40 的“智能手機”前面位置分只用來調(diào)整同檔位內(nèi)部的名次。這是權(quán)重排序里一個非常重要的經(jīng)驗各維度的分值與系數(shù)決定了排序的主導(dǎo)邏輯一定要想清楚誰是大頭、誰是小頭。另一個修正點是為ORDER BY加上唯一鍵兜底。第一次測試時我直接ORDER BY score DESC發(fā)現(xiàn)“智能手機”和“二手手機”的分?jǐn)?shù)恰好一樣結(jié)果兩次查詢出來的前后順序不一樣。加上sales_count DESC作為第二排序條件后結(jié)果才穩(wěn)定。這個細(xì)節(jié)在后面做分頁時尤其重要。5. 常見問題與排查技巧實錄權(quán)重排序的 SQL 寫起來不難但線上跑起來問題往往出現(xiàn)在你沒預(yù)料到的地方。下面這些問題都是我實際碰到過、并且花時間排查過的。5.1 排序結(jié)果不穩(wěn)定 / 分頁重復(fù)癥狀是前端分頁加載時第 1 頁出現(xiàn)過的記錄第 2 頁又出現(xiàn)了或者排序順序每次刷新都變。原因幾乎都是ORDER BY后面的字段組合不唯一。當(dāng)兩條記錄的評分完全相同MySQL 返回它們的相對順序是不確定的這個“不確定”在分頁場景下會被放大。解決辦法有兩個一是給ORDER BY補充一個唯一字段比如ORDER BY score DESC, id DESC二是給參與排序的字段建立一個聯(lián)合索引讓排序盡量走索引避免 filesort。如果你發(fā)現(xiàn)自己加了id之后仍然不穩(wěn)定那多半是score表達式里用了隨機函數(shù)或者依賴了不穩(wěn)定的數(shù)據(jù)源。檢查一下 SQL 里有沒有RAND()或容易變化的時間函數(shù)。5.2 中文匹配在 utf8_general_ci 下走偏這是個很容易踩的字符集坑。utf8_general_ci和utf8_unicode_ci在排序和匹配規(guī)則上有差異特別是中文場景下general_ci會對某些中文字符的匹配做“寬松處理”導(dǎo)致LIKE匹配出一些“看起來不應(yīng)該匹配”的記錄。如果你的表和數(shù)據(jù)庫使用的是utf8_general_ci而業(yè)務(wù)要求精確區(qū)分中文建議把字段改成utf8mb4_unicode_ci或utf8mb4_0900_ai_ci。我遇到過最詭異的一次搜索“蘋果”時LIKE %蘋果%居然匹配出了“青蘋”相關(guān)的記錄。排查半天發(fā)現(xiàn)是排序規(guī)則把“果”字和某個擴展字符視為等價。所以建表時字符串字段的 collation 要選對別在權(quán)重排序上線后才來折騰數(shù)據(jù)遷移。5.3 EXPLAIN 顯示全表掃描怎么辦EXPLAIN的type列是ALLrows列是幾十萬意味著WHERE條件沒把掃描范圍縮下來。核心原因是name LIKE %手機%這種前通配條件走不了索引。我的處理優(yōu)先級是如果沒有前導(dǎo)通配必須可用改寫為name LIKE 手機% OR name LIKE %手機把第一個條件放在前面讓優(yōu)化器至少有機會用上索引如果確實需要中間匹配那就別指望數(shù)據(jù)庫幫你扛住全表掃描回到第 3 節(jié)的優(yōu)化路線拆字段、加過濾邊界、做預(yù)處理如果業(yè)務(wù)方允許把搜索限定到最近三個月的數(shù)據(jù)加一個created_at范圍條件讓掃描行數(shù)降兩個數(shù)量級。5.4 ORDER BY 與 GROUP BY 同時用的問題有次我需要統(tǒng)計關(guān)鍵詞命中商品數(shù)再按權(quán)重排序直接寫了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, 計算權(quán)重得分 AS score FROM product_demo WHERE name LIKE %手機% ) t GROUP BY category_id ORDER BY max_score DESC, cnt DESC;這個寫法既保住了分組統(tǒng)計又讓排序按權(quán)重走實測結(jié)果很穩(wěn)。它的核心思路是任何邏輯先用子查詢算好再在外層做聚合不要指望一句 SQL 解決所有問題。6. 什么時候該放棄 LIKE 權(quán)重排序權(quán)重排序不是銀彈。我在前面強調(diào)過這套方案適合中小數(shù)據(jù)量但如果你已經(jīng)優(yōu)化到極限查詢還是超過 500ms就該考慮換方案了。6.1 數(shù)據(jù)量閾值判斷怎么判斷閾值我的經(jīng)驗是單表記錄數(shù)超過 50 萬行且每次搜索都要用LIKE %關(guān)鍵詞%做前通配匹配時基本就到了 MySQL 硬扛的極限。這時候即便你拆字段、加緩存效果也有限因為每次搜索都可能掃描幾萬行做評分計算。還有一個判斷維度是搜索并發(fā)。如果你的搜索接口每秒請求量超過 20 次且每次查詢都是幾百毫秒數(shù)據(jù)庫的連接池很快就會被占滿。這時候權(quán)重排序的性能問題會直接拖垮整個服務(wù)的穩(wěn)定性。6.2 轉(zhuǎn)型方案對比如果要換方案我的建議是分兩步走。第一步上 MySQL 的全文索引。MySQL 5.7 之后支持中文ngram全文解析器可以建FULLTEXT索引配合MATCH ... AGAINST做相關(guān)度排序。它的優(yōu)勢是語法簡單性能比LIKE %關(guān)鍵詞%高很多劣勢是相關(guān)度算法是固定的不容易按業(yè)務(wù)自定義權(quán)重。適合那些“能用就行不想維護太多基礎(chǔ)組件”的項目。第二步如果業(yè)務(wù)對搜索質(zhì)量要求高比如需要同義詞、拼寫糾錯、個性化排序那 MySQL 已經(jīng)不適合你了。這時候要引入獨立搜索引擎或者專用的搜索中間件。數(shù)據(jù)同步可以用消息隊列保證增量更新。這個改造的成本不低但換來的搜索體驗也是LIKE方案沒法比的。我個人在實際操作中的體會是引入外部搜索組件要趁早越晚技術(shù)債越重。最后再分享一個小技巧。如果你一時半會兒沒法改表結(jié)構(gòu)又想提升LIKE權(quán)重排序的性能可以試試給name字段加一個“前綴冗余列”專門存去掉前導(dǎo)空格后的前 10 個字符并對這一列建普通索引。查詢時優(yōu)先用前綴匹配去圈定結(jié)果集再在圈定的結(jié)果集里做中間匹配的評分。這樣做的效果是把全表掃描變成索引掃描幾萬行的結(jié)果集內(nèi)部怎么排序都不用怕了?；氐揭婚_始的需求——用ORDER BY給LIKE的匹配結(jié)果加權(quán)重排序本質(zhì)是給數(shù)據(jù)庫查詢注入一點“搜索引擎思維”。想清楚匹配形態(tài)、位置、字段這三個維度的加權(quán)邏輯寫出的 SQL 就能在性能可控的前提下讓搜索結(jié)果貼合業(yè)務(wù)意圖。我這個方案不一定適合所有場景但作為從零開始的參考模板你可以直接拿去改造然后再根據(jù)線上效果調(diào)系數(shù)。