MySQL 單表多關鍵字模糊查詢：CONCAT 組合索引與全文搜索方案對比

MySQL 單表多關鍵字模糊查詢方案深度解析

需求：用戶輸入多個關鍵字 (空格分隔)，需在多個欄位 (如 title、tag、description) 中**同時包含所有關鍵字**的記錄。本文對比 CONCAT 組合查詢、全文索引、應用層分詞三大方案優劣與適用場景。

場景建模

CREATE TABLE `magazine` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tag` VARCHAR(500) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

用戶輸入："MySQL 優化 索引" → 分割為 3 個關鍵字。要求：每個關鍵字必須在 title/tag/description 任意欄位中出現 (OR 關係)，且**所有關鍵字都要滿足** (AND 關係)。

方案一：CONCAT 組合 + LIKE (簡單、無索引、全表掃描)

-- 動態構建 SQL (參數化防注入)
SELECT * FROM `magazine` 
WHERE CONCAT(`title`, ' ', `tag`, ' ', `description`) LIKE '%MySQL%'
  AND CONCAT(`title`, ' ', `tag`, ' ', `description`) LIKE '%優化%'
  AND CONCAT(`title`, ' ', `tag`, ' ', `description`) LIKE '%索引%';

• 優點：零改造、兼容所有版本、邏輯精確。
• 缺點：無法使用索引 (函數列導致全表掃描)、CONCAT 遇 NULL 結果為 NULL (需 COALESCE)、性能隨數據量線性下降。
• 適用：數據量 < 10萬、低頻查詢、臨時方案。

方案二：全文索引 (原生、支持相關性排序、中文需分詞)

-- 1. 創建全文索引 (5.6+ InnoDB 支持)
ALTER TABLE `magazine` ADD FULLTEXT INDEX `ft_all` (`title`, `tag`, `description`);

-- 2. 自然語言模式 (默認，相關性排序)
SELECT *, MATCH(`title`,`tag`,`description`) AGAINST('MySQL 優化 索引' IN NATURAL LANGUAGE MODE) AS relevance
FROM `magazine`
WHERE MATCH(`title`,`tag`,`description`) AGAINST('MySQL 優化 索引' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- 3. 布爾模式 (精確控制：+必含 -排除 *通配)
SELECT * FROM `magazine`
WHERE MATCH(`title`,`tag`,`description`) AGAINST('+MySQL +優化 +索引' IN BOOLEAN MODE);

• 中文分詞關鍵：MySQL 內建 ngram 解析器 (5.7.6+) 將中文按字/雙字拆分。

  ALTER TABLE `magazine` ADD FULLTEXT INDEX `ft_all` (`title`, `tag`, `description`) WITH PARSER ngram;

• 優點：有索引支撐、支持相關性排序、語法豐富 (布爾模式)。
• 缺點：ft_min_word_len (默認 4) 影響短詞；ngram 會產生大量 token 佔空間；停用詞表需維護；不支持「所有關鍵字在任意欄位」的精確 AND 邏輯 (布爾模式 +MySQL +優化 要求每個詞在索引欄位集合中出現，但無法區分具體欄位)。
• 適用：專業搜索、日誌分析、中文內容檢索。

方案三：應用層分詞 + 關鍵字表 / Elasticsearch (專業、高性能、架構複雜)

• 寫入時：分詞器 (IK Analyzer、jieba) 拆分 → 寫入關鍵字倒排表 (自建表或 ES)。
• 查詢時：分詞 → 取交集 → 回表查詳情。
• 優點：毫秒級響應、支持高亮、同義詞、模糊、拼音、權重調優。
• 缺點：引入外部依賴 (ES)、數據同步複雜、運維成本高。
• 適用：搜索型業務 (電商、內容平台)、高併發、複雜查詢需求。

方案四：生成欄位 + 普通索引 (8.0+ 虛擬列、5.7 觸發器維護)

-- 8.0 虛擬列 (不佔存儲，自動維護)
ALTER TABLE `magazine` 
ADD COLUMN `search_blob` TEXT GENERATED ALWAYS AS (CONCAT(`title`, ' ', `tag`, ' ', `description`)) VIRTUAL,
ADD FULLTEXT INDEX `ft_search` (`search_blob`) WITH PARSER ngram;

-- 或普通索引 (前綴) 配合 LIKE (仍無法解決 %keyword% 索引失效)
-- 此方案本質同方案一/二，不再贅述

方案對比決策表

推薦選型路徑

1. 數據量小/低頻/快速上線 → 方案一 CONCAT + LIKE (加 COALESCE 處理 NULL)。
2. 中等數據量/需相關性排序/中文搜索 → 方案二 FULLTEXT + ngram (調整 ngram_token_size=2 改雙字)。
3. 搜索核心業務/高併發/複雜需求 → 方案三 Elasticsearch/OpenSearch (業界標準)。
4. 混合方案：主表走 ES 搜索，命中 ID 回 MySQL 取詳情 (架構清晰、職責分離)。