MySQL 模糊查詢優化：LOCATE、POSITION、INSTR、FIND_IN_SET 比 LIKE 更高效的實戰技巧

MySQL 模糊查詢優化：LOCATE、POSITION、INSTR、FIND_IN_SET 比 LIKE 更高效的實戰技巧

在 MySQL 中，LIKE '%keyword%' 會導致全表掃描，無法利用索引，數據量大時性能極差。本文介紹四個字符串查找函數 LOCATE、POSITION、INSTR、FIND_IN_SET，它們在特定場景下比 LIKE 更高效，並提供實戰對比與選型建議。

一、 核心函數語法對比

二、 性能對比與適用場景

1. LOCATE / POSITION / INSTR

• 優勢：相比 LIKE '%kw%'，函數調用開銷略低，且在某些版本中優化器能更好估算成本。
• 適用：單字段包含匹配、需指定起始位置查找 (如解析日志)。
• 注意：同樣無法使用索引，本質仍是全表掃描，僅在 CPU 層面略快。不建議作為 LIKE 的長期替代方案。

2. FIND_IN_set —— 真正的性能殺手鐧

適用於 逗號分隔的枚舉值存儲 (如標籤、分類、權限列表)。

-- 表結構
CREATE TABLE test (id INT, tags VARCHAR(255)); -- 存儲 'tag1,tag2,tag3'

-- 查詢包含 'tag2' 的記錄
SELECT * FROM test WHERE FIND_IN_SET('tag2', tags) > 0;

優勢：

• 語義清晰，避免 LIKE '%,tag2,%' 的邊界匹配錯誤 (首尾無逗號)。
• 若第一參數為常數、第二列為 SET 類型，優化器使用位圖運算，極快。

局限：

• 字段值不能包含逗號，長度受限 (VARCHAR 65535)。
• 非標準化設計，不利於 JOIN、統計、索引優化。

三、 真正的優化方向：全文索引與架構調整

若業務高頻模糊查詢，應從架構層面解決：

1. 全文索引：ALTER TABLE t ADD FULLTEXT(col); SELECT * FROM t WHERE MATCH(col) AGAINST('keyword'); (MyISAM 5.6+/InnoDB 5.6+)。
2. 倒排索引/搜索引擎：Elasticsearch、Solr、MeiliSearch 專業處理複雜文本檢索。
3. 字段拆分/標籤表：將逗號分隔字段規範化為關聯表 entity_tags(entity_id, tag_id)，建立索引，JOIN 查詢極快。
4. 前綴索引：LIKE 'keyword%' 可用索引，僅後綴/中間模糊無法用索引。

四、 總結建議

• 臨時腳本、小表、低頻查詢：LOCATE/INSTR 代替 LIKE 碼更簡潔。
• 逗號分隔枚舉字段：優先用 FIND_IN_SET，長期規劃拆表。
• 核心業務高頻模糊搜：上全文索引或引入搜索引擎，勿在 SQL 層硬扛。