在大型 MySQL 生產數據庫中使用前導萬用字元進行 LIKE 查詢
根據 Amazon RDS 中的見解，大型 MySQL 生產數據庫中的以下查詢導致高負載（約 50.000.000 個條目）：
SELECT * FROM entities WHERE status='ready' AND user_id='81663729' AND (primary_name LIKE '%mysearch%' OR additional_names LIKE '%mysearch%') ORDER BY id DESC LIMIT 0, 100000負責它的列：
common_name: VARCHAR(255) additional_names: VARCHAR(2000) （注意：不幸的是，這是一個逗號分隔的字元串，每個條目包含零到數百個名稱，這是可怕的數據庫設計）
這個查詢表現不佳並不奇怪：前導萬用字元使索引或多或少不可用（
primary_name被索引，additional_names不是）。一些使用者在這個表中有很多條目（目前每個使用者最多有 2.000.000 個條目，條目數量正在增長） - 所以查詢中的 user_id 不會挽救它的性能。我的問題是什麼可能是解決這個問題的最佳方法。
方法一：MySQL FULLTEXT 索引
創建全文索引：
CREATE FULLTEXT INDEX domain ON entities (primary_name,additional_names);新查詢：
SELECT * FROM entities WHERE status='ready' AND user_id='81663729' AND MATCH (primary_name,additional_names) AGAINST ('mysearch') IN NATURAL LANGUAGE MODE ORDER BY id DESC LIMIT 0, 100000;Pro：最容易遷移和實施？
反對：不確定這是否符合要求？創建該索引可能需要大量時間。
方法二：MySQL JSON
第 1 步：使用 JSON 添加附加列
ALTER TABLE entities ADD additional_names_j JSON AFTER additional_names;第 2 步：所有新實體都作為 json 插入到新的 JSON 列以及舊列中。
第 3 步：可能會執行幾天的腳本確保所有舊實體在 JSON 列中都有其條目。
第 4 步：調整應用程序以使用 JSON 列。
第 5 步：刪除舊的附加名稱列。
詢問：
SELECT * FROM entities WHERE status='ready' AND user_id='81663729' AND 'mysearch' member of ORDER BY id DESC LIMIT 0, 100000;優點：搜尋 JSON 會更好，並且可以在沒有逗號爆炸之類的情況下提取名稱。
反對：不確定是否可以對 JSON 列進行部分匹配？此外，努力是相當高的。
方法 3：創建關係表和名稱表
這基本上意味著我們有一個帶有 id 和 name 的表和 am:n 關係表。
優點：將是漂亮和乾淨的設計。
Contra：必須與正在執行的應用程序並行存在並填充，在這種情況下，它將導致應用程序邏輯發生巨大變化。
我向專家提出的問題是：哪些方法能最好地解決這種情況——為什麼？
注意：該表以每秒約 5 個條目的速度增長，不允許停機。我願意接受任何其他方法來使這個查詢更快，我目前不知道！
方法 1：全文索引不能完成
LIKE條件所能做的所有事情。我看到阻止使用全文的最常見問題是 MySQL 的實現只索引整個單詞，所以如果您的搜尋是部分單詞，它將無濟於事。創建索引需要時間，但如果避免停機很重要，您絕對應該熟悉使用pt-online-schema-change或gh-ost。這些都是免費工具，允許更改架構（包括添加索引），同時不會阻止客戶端使用表。我們在上一份工作中使用 pt-osc 在高峰時段每週執行數百次模式更改。
方法 2：單獨添加 JSON 列將無濟於事，無需為要搜尋的表達式創建索引。你之前在 MySQL 中使用過 JSON 嗎？在您決定之前，您應該對您使用的 MySQL 版本進行概念驗證，看看它是否有效。在 MySQL 中使用 JSON 有很多限制和注意事項。我通常建議不要使用 JSON。
進一步閱讀：
- https://dev.mysql.com/doc/refman/8.0/en/json.html
- https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
- https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index
- https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
方法 3：這也稱為**反向索引。**它可以很好地工作，但是正如您所說，它需要一些工作來維護反向索引，在添加數據時添加值。上面的評論建議使用觸發器，它確實有效，但認為這也算作添加更多程式碼。
這些方法中的哪一種能最好地解決這種情況？
如果全文索引滿足您需要支持的搜尋需求，我更喜歡全文索引。優點是維護索引所需的程式碼最少，這是一個很大的優勢。
但每次我聽到*“哪個最好？”的問題時。*我知道這是個錯誤的問題。沒有適用於所有情況的“最佳”解決方案。您的項目有自己的要求，您需要選擇適合這些要求的解決方案。任何解決方案都可能最適合一個人的應用程序，但不適用於另一個應用程序。