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