MySQL千萬級數(shù)據(jù)模糊搜索：如何不增加資源的情況下提升搜索效率？

提升mysql千萬級數(shù)據(jù)模糊搜索效率的策略

在MySQL 5.7版本中，針對千萬級數(shù)據(jù)進(jìn)行LIKE ‘%關(guān)鍵詞%’模糊搜索時，性能低下是常見問題。由于無法利用索引，查詢效率會顯著下降。本文探討如何在不增加服務(wù)器資源（內(nèi)存、外部中間件）的前提下，優(yōu)化MySQL模糊搜索的效率。

已知嘗試過MySQL分詞索引和自定義索引表，但因中文分詞復(fù)雜性和索引維護(hù)成本而放棄。內(nèi)存緩存方案也因Java堆內(nèi)存(512M)限制而不可行。

解決方案：構(gòu)建類似倒排索引的輔助表

一個有效的方案是創(chuàng)建輔助索引表，類似于倒排索引。該表存儲每個詞語與其后一個詞語，以及對應(yīng)的原始記錄主鍵ID。

例如，對于記錄“MySQL千萬級數(shù)據(jù)量如何一秒內(nèi)實(shí)現(xiàn)模糊搜索？”，索引表結(jié)構(gòu)如下：

搜索“模糊搜索”時，sql語句如下：

SELECT 原記錄主鍵ID FROM (SELECT 原記錄主鍵ID FROM 索引表 WHERE 當(dāng)前詞 = '模糊' AND 下一詞 = '搜索') a JOIN (SELECT 原記錄主鍵ID FROM 索引表 WHERE 當(dāng)前詞 = '搜索' AND 下一詞 IS NULL) b USING(原記錄主鍵ID);

通過索引表快速定位包含關(guān)鍵詞的記錄，再根據(jù)主鍵ID獲取原始數(shù)據(jù)，避免全表掃描，顯著提升效率。

注意事項(xiàng)：

此方案需要預(yù)先構(gòu)建和維護(hù)索引表，并根據(jù)實(shí)際情況調(diào)整表結(jié)構(gòu)和SQL語句。復(fù)雜搜索條件可能需要更復(fù)雜的索引表和查詢邏輯。 類似的倒排索引技術(shù)也應(yīng)用于例如everything軟件的高效搜索中，但其具體實(shí)現(xiàn)更為復(fù)雜。