在日常軟件開發(fā)、后臺管理、內(nèi)容檢索等業(yè)務(wù)場景中，MySQL模糊搜索是開發(fā)者高頻使用的功能。絕大多數(shù)人實(shí)現(xiàn)模糊匹配的方式都非常簡單，直接通過 LIKE 語句搭配通配符完成查詢，適配用戶輸入關(guān)鍵詞檢索內(nèi)容、模糊匹配名稱、備注信息等常規(guī)需求。在數(shù)據(jù)量較小的測試環(huán)境中，這種寫法完全沒有問題，查詢響應(yīng)速度快，不會出現(xiàn)任何異常。但一旦項(xiàng)目上線，數(shù)據(jù)表數(shù)據(jù)累積到數(shù)十萬、上百萬甚至千萬級別后，傳統(tǒng) LIKE 檢索的弊端就會徹底暴露，最直觀的表現(xiàn)就是接口響應(yīng)超時、頁面加載卡頓、數(shù)據(jù)庫CPU和IO占用飆升，嚴(yán)重時還會拖垮整個業(yè)務(wù)系統(tǒng)。
很多開發(fā)者對此十分困惑，明明已經(jīng)給查詢字段建立了普通索引，為什么模糊查詢依然效率極低？其實(shí)核心問題在于，傳統(tǒng) B+ 樹索引并不適配大部分模糊搜索場景，這也是 LIKE 檢索卡頓的根本原因。本文將通俗拆解傳統(tǒng) MySQL 模糊搜索的各類痛點(diǎn)、底層失效原理，同時手把手落地 MySQL 全文索引搭建方案，搭配配套優(yōu)化技巧，徹底解決大數(shù)據(jù)量下模糊檢索卡頓、低效問題。
一、傳統(tǒng)MySQL LIKE模糊檢索的核心痛點(diǎn)
在實(shí)際開發(fā)中，LIKE 模糊查詢主要分為三種寫法，不同寫法的性能差距極大，絕大多數(shù)卡頓問題都出在兩種高危寫法上，這也是線上慢查詢的重災(zāi)區(qū)。據(jù)行業(yè)統(tǒng)計(jì)，超30%的數(shù)據(jù)庫慢查詢問題，都源于不合理的模糊檢索語句。
第一種是前綴匹配查詢，也就是LIKE '關(guān)鍵詞%'。這種寫法是所有模糊查詢中性能最優(yōu)的，也是唯一能穩(wěn)定觸發(fā)普通B+樹索引的寫法。因?yàn)镸ySQL索引遵循最左前綴匹配原則，數(shù)據(jù)在索引樹中是按字段前綴有序排列的，數(shù)據(jù)庫可以直接通過索引快速定位前綴匹配的數(shù)據(jù)范圍，無需掃描全表，哪怕是百萬級數(shù)據(jù)，查詢速度也能維持在毫秒級。
第二種是后綴匹配查詢LIKE '%關(guān)鍵詞'和全模糊匹配查詢LIKE '%關(guān)鍵詞%'，這兩種是業(yè)務(wù)中最常用、也是問題最多的寫法。日常用戶搜索內(nèi)容、匹配備注、檢索文章關(guān)鍵詞等場景，基本都需要用到全模糊匹配，只要關(guān)鍵詞出現(xiàn)在字段任意位置都要命中結(jié)果。但正是這兩種寫法，會直接導(dǎo)致普通索引完全失效，無論字段是否建立索引，數(shù)據(jù)庫都會執(zhí)行全表掃描。
全表掃描是模糊檢索卡頓的核心元兇。所謂全表掃描，就是數(shù)據(jù)庫會逐行讀取數(shù)據(jù)表中的所有數(shù)據(jù)，逐條比對是否匹配關(guān)鍵詞，數(shù)據(jù)量越小，卡頓感知越弱，一旦數(shù)據(jù)突破十萬級，查詢耗時會成倍增長。百萬級數(shù)據(jù)下，一次普通的全模糊檢索耗時可達(dá)數(shù)秒，千萬級數(shù)據(jù)甚至?xí)_(dá)到十幾秒，完全無法滿足線上業(yè)務(wù)的響應(yīng)要求。
除此之外，傳統(tǒng) LIKE 檢索還有兩個隱形痛點(diǎn)。第一是資源消耗極高，全表掃描會大量占用數(shù)據(jù)庫磁盤IO和CPU資源，不僅當(dāng)前查詢卡頓，還會擠壓其他正常業(yè)務(wù)的數(shù)據(jù)庫請求，導(dǎo)致整體系統(tǒng)響應(yīng)變慢。第二是檢索能力有限，傳統(tǒng)模糊查詢僅能做簡單字符匹配，無法實(shí)現(xiàn)分詞匹配、權(quán)重排序、相關(guān)性匹配，搜索結(jié)果精準(zhǔn)度差，無法滿足精細(xì)化的檢索業(yè)務(wù)需求。
二、LIKE檢索索引失效的底層原理
想要徹底優(yōu)化模糊搜索，必須搞懂為什么前綴通配符會讓索引失效。MySQL普通索引默認(rèn)是B+樹結(jié)構(gòu)，我們可以把B+樹索引通俗理解為字典的拼音目錄，字典的目錄是按照首字母順序排列的，我們可以通過首字母快速找到對應(yīng)漢字，也就是前綴匹配。但如果我們不知道首字母，只知道中間或結(jié)尾的字符，就無法通過目錄定位，只能逐頁翻閱字典全文查找，這就是全表掃描的邏輯。
B+樹索引的核心特性就是有序存儲、最左匹配，索引節(jié)點(diǎn)中的數(shù)據(jù)是按照字段內(nèi)容從左到右有序排序的。當(dāng)我們使用關(guān)鍵詞%前綴匹配時，數(shù)據(jù)庫可以利用索引的有序性，快速劃定數(shù)據(jù)區(qū)間，跳過所有不匹配的數(shù)據(jù)，實(shí)現(xiàn)高效查詢。
而當(dāng)通配符 % 放在關(guān)鍵詞前面，也就是%關(guān)鍵詞和%關(guān)鍵詞%時，意味著匹配規(guī)則是“字段任意位置包含關(guān)鍵詞”，索引的有序性完全失去作用。數(shù)據(jù)庫優(yōu)化器無法通過索引判斷數(shù)據(jù)位置，只能放棄索引，直接掃描數(shù)據(jù)表的所有數(shù)據(jù)行完成匹配，索引徹底形同虛設(shè)。
很多開發(fā)者會陷入一個誤區(qū)：只要建了索引，所有查詢都會提速。實(shí)際上普通B+樹索引是為精準(zhǔn)匹配、范圍查詢、前綴匹配設(shè)計(jì)的，天生不適配全域模糊檢索。這也是為什么很多項(xiàng)目明明做了索引優(yōu)化，模糊查詢依然持續(xù)卡頓的核心原因。同時需要注意，在索引字段上做函數(shù)處理、嵌套查詢，也會間接導(dǎo)致索引失效，進(jìn)一步加劇檢索低效問題。
三、MySQL全文索引：低成本解決模糊檢索卡頓核心方案
針對傳統(tǒng) LIKE 檢索的短板，MySQL 官方提供了專屬的全文索引（FULLTEXT）解決方案，這也是適配MySQL原生模糊檢索優(yōu)化的最優(yōu)方案，無需引入第三方組件，低成本、高效率解決全模糊匹配卡頓問題。從MySQL5.6版本開始，InnoDB引擎正式支持全文索引，徹底改變了此前僅MyISAM引擎支持全文索引的局面，適配絕大多數(shù)線上項(xiàng)目環(huán)境。
全文索引和普通B+樹索引的設(shè)計(jì)邏輯完全不同，它不再依賴字符前綴匹配，而是基于分詞檢索原理，將文本字段的內(nèi)容拆分為獨(dú)立詞匯，建立詞匯索引庫，查詢時通過匹配詞匯實(shí)現(xiàn)高效檢索，完美適配任意位置關(guān)鍵詞匹配場景，徹底規(guī)避全表掃描問題。
3.1 全文索引適配范圍與字段要求
全文索引并非適配所有字段類型，僅支持文本類字段，包括 CHAR、VARCHAR、TEXT 三種常用類型，完全覆蓋名稱、簡介、備注、文章內(nèi)容等常規(guī)檢索字段。同時支持英文、中文等多語言檢索，能夠滿足絕大多數(shù)業(yè)務(wù)的文本模糊搜索需求。
相較于傳統(tǒng) LIKE 查詢，全文索引的優(yōu)勢十分明顯：百萬級數(shù)據(jù)量下，全模糊檢索耗時可從數(shù)秒壓縮至毫秒級，性能提升百倍以上；支持關(guān)鍵詞相關(guān)性排序，匹配度越高的內(nèi)容越靠前，搜索結(jié)果更貼合用戶需求；支持多關(guān)鍵詞聯(lián)合檢索、排除檢索，功能遠(yuǎn)優(yōu)于簡單的LIKE字符匹配。
3.2 全文索引實(shí)戰(zhàn)搭建步驟
全文索引的搭建方式簡單高效，分為建表時直接創(chuàng)建索引、已有數(shù)據(jù)表追加索引兩種場景，適配新項(xiàng)目開發(fā)和老項(xiàng)目優(yōu)化。
第一種：建表同步創(chuàng)建全文索引。在創(chuàng)建數(shù)據(jù)表時，直接指定FULLTEXT索引，綁定需要檢索的文本字段，適合新項(xiàng)目提前規(guī)劃優(yōu)化方案。核心語法簡潔，可針對單個字段或多個聯(lián)合字段建立全文索引，適配多字段聯(lián)合檢索場景。
第二種：已有數(shù)據(jù)表新增全文索引。這是老項(xiàng)目優(yōu)化最常用的方式，無需改動表結(jié)構(gòu)，僅通過一條ALTER語句即可快速創(chuàng)建索引，不影響原有業(yè)務(wù)數(shù)據(jù)。針對項(xiàng)目中需要頻繁模糊查詢的標(biāo)題、內(nèi)容、用戶名等字段，可單獨(dú)建立全文索引。
3.3 全文索引標(biāo)準(zhǔn)查詢用法
搭建完全文索引后，不能繼續(xù)使用 LIKE 語句查詢，需要使用 MySQL 專屬的MATCH() AGAINST()語法檢索，這是觸發(fā)全文索引的唯一標(biāo)準(zhǔn)寫法。該語法主要分為兩種檢索模式，適配不同業(yè)務(wù)場景。
第一種是自然語言模式，也是默認(rèn)模式，適用于常規(guī)關(guān)鍵詞搜索，自動匹配包含關(guān)鍵詞的所有數(shù)據(jù)，同時根據(jù)關(guān)鍵詞匹配度、出現(xiàn)頻次自動排序，優(yōu)先展示精準(zhǔn)度更高的內(nèi)容，適配用戶普通搜索場景。
第二種是布爾模式，靈活性更高，支持關(guān)鍵詞疊加、關(guān)鍵詞排除、精準(zhǔn)匹配等復(fù)雜規(guī)則，適配精細(xì)化檢索場景。比如可以實(shí)現(xiàn)“包含A關(guān)鍵詞、不包含B關(guān)鍵詞”的檢索效果，這是傳統(tǒng)LIKE語句無法實(shí)現(xiàn)的功能。
四、全文索引精細(xì)化優(yōu)化，規(guī)避性能短板
雖然全文索引能徹底解決全模糊檢索卡頓問題，但如果直接默認(rèn)使用，依然會出現(xiàn)檢索不準(zhǔn)、索引失效、檢索效率低等問題，需要針對性優(yōu)化細(xì)節(jié)，最大化發(fā)揮全文索引性能。
4.1 停用詞規(guī)則優(yōu)化
MySQL自帶默認(rèn)停用詞表，會自動過濾掉部分高頻短詞匯、無意義詞匯，避免索引冗余。但默認(rèn)規(guī)則會導(dǎo)致部分正常短關(guān)鍵詞檢索失效，比如單個漢字、常用虛詞等，出現(xiàn)明明數(shù)據(jù)存在卻檢索不到的情況。針對中文業(yè)務(wù)場景，可以通過修改數(shù)據(jù)庫配置，自定義停用詞規(guī)則，清空無用過濾規(guī)則，適配中文分詞檢索需求，保證關(guān)鍵詞檢索全覆蓋。
4.2 分詞長度適配調(diào)整
MySQL全文索引默認(rèn)有最小分詞長度限制，InnoDB引擎默認(rèn)最小檢索字符長度為4，這也是很多開發(fā)者遇到的常見問題：輸入短關(guān)鍵詞檢索無結(jié)果。針對中文單字、雙字關(guān)鍵詞檢索場景，需要手動調(diào)整數(shù)據(jù)庫分詞長度參數(shù)，適配短關(guān)鍵詞檢索需求，覆蓋所有常規(guī)搜索場景。
4.3 索引冗余優(yōu)化
很多開發(fā)者會對超長文本字段建立全文索引，導(dǎo)致索引體積過大、更新耗時增加。對于大文本字段，無需全量建立索引，可以通過業(yè)務(wù)層預(yù)處理，提取核心檢索關(guān)鍵詞單獨(dú)存儲，對短關(guān)鍵詞字段建立全文索引，大幅縮小索引體積，提升查詢和數(shù)據(jù)更新效率。同時避免重復(fù)創(chuàng)建索引，同一字段無需同時建立普通索引和全文索引，減少數(shù)據(jù)庫負(fù)載。
五、特殊場景補(bǔ)充優(yōu)化方案
MySQL原生全文索引可以解決90%以上的常規(guī)模糊檢索卡頓問題，但在超高并發(fā)、千萬級以上海量數(shù)據(jù)、超復(fù)雜文本檢索場景中，依然存在一定局限性，可搭配以下輕量化優(yōu)化方案，進(jìn)一步提升檢索性能。
5.1 業(yè)務(wù)語句優(yōu)化兜底
在無需全模糊匹配的場景下，優(yōu)先放棄全文索引，使用前綴匹配LIKE '關(guān)鍵詞%'搭配普通B+樹索引，查詢效率更高、資源消耗更低。同時嚴(yán)格杜絕SELECT *查詢，模糊檢索時只查詢業(yè)務(wù)需要的字段，減少數(shù)據(jù)傳輸開銷，提升響應(yīng)速度。
5.2 后綴匹配專屬優(yōu)化
針對僅需要后綴匹配的場景，無需啟用全文索引，可以采用空間換時間的方式，新增字段存儲原字段的反轉(zhuǎn)內(nèi)容，對反轉(zhuǎn)字段建立普通索引。將LIKE '%關(guān)鍵詞'的后綴匹配，轉(zhuǎn)化為反轉(zhuǎn)字段的前綴匹配，通過普通索引實(shí)現(xiàn)高速查詢，比全文索引更輕量化。
5.3 海量高并發(fā)場景進(jìn)階方案
對于千萬級以上數(shù)據(jù)、每秒千次以上檢索的超高并發(fā)場景，MySQL原生全文索引的性能會達(dá)到瓶頸，此時可采用分層優(yōu)化方案?；A(chǔ)檢索依然用MySQL全文索引兜底，保障基礎(chǔ)性能；核心高頻檢索模塊，可引入專業(yè)檢索中間件，實(shí)現(xiàn)分布式分詞、精準(zhǔn)排序、超高并發(fā)檢索，徹底擺脫數(shù)據(jù)庫檢索性能限制。
六、全文索引落地常見避坑要點(diǎn)
很多開發(fā)者搭建完全文索引后，依然出現(xiàn)查詢慢、檢索不準(zhǔn)、索引不生效等問題，大多是踩了官方規(guī)則的隱形坑，梳理高頻實(shí)戰(zhàn)誤區(qū)，幫助大家一次性落地最優(yōu)方案。
第一，區(qū)分引擎差異。MyISAM引擎的全文索引雖然兼容性更好，但事務(wù)支持差、數(shù)據(jù)安全性低，不建議線上使用，優(yōu)先統(tǒng)一使用InnoDB引擎全文索引，適配事務(wù)、崩潰恢復(fù)等線上需求。
第二，避免索引失效場景。全文索引同樣存在失效情況，在查詢條件中疊加大量復(fù)雜函數(shù)、多表關(guān)聯(lián)嵌套查詢、大量OR條件拼接，會導(dǎo)致數(shù)據(jù)庫優(yōu)化器放棄全文索引，重回全表掃描，檢索時盡量簡化查詢語句。
第三，批量數(shù)據(jù)更新后刷新索引。大批量新增、修改、刪除數(shù)據(jù)后，全文索引會存在短暫緩存延遲，可能出現(xiàn)檢索結(jié)果不更新的情況，可通過手動刷新索引或等待緩存更新，保證檢索結(jié)果實(shí)時準(zhǔn)確。
第四，控制索引字段數(shù)量。不建議一次性對多個無關(guān)字段建立聯(lián)合全文索引，會大幅增加索引體積，降低寫入性能，按需對核心檢索字段單獨(dú)建索引，平衡查詢和寫入效率。
來源：滬城114?http://blog.nxtcbmw.cn/
