MySQL 全文搜尋效能優化實戰：FULLTEXT 索引建立、自然語言模式與布林模式完整指南

為什麼不直接用 LIKE？

SELECT * FROM articles WHERE content LIKE '%keyword%'：

• 無法使用索引 → 全表掃描。
• 大量資料極慢，無相關性排序。
• 不支援詞幹、同義詞、鄰近度搜尋。

FULLTEXT 索引原理

• 建立倒排索引：詞項 → 文件 ID 映射，儲存詞頻、位置資訊。
• 支援自然語言模式（預設）：計算相關性分數，依分數降序排序。
• 支援布林模式：+ 必含、- 必不含、* 萬用字元、"" 精確片語、>/< 權重調整。
• MySQL 5.6+ InnoDB 支援 FULLTEXT（舊版僅 MyISAM）。

實戰建立步驟

-- 1. 建立範例表
CREATE TABLE reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 插入測試資料
INSERT INTO reviews (content) VALUES
('Gingerboy has a new single out called Throwing Rocks. It\'s great!'),
('Hello all, I really like the new Madonna single. One of the hottest tracks currently playing...'),
('Have you heard the new band Hotter Than Hell? They have five members and they burn their instruments when they play in concerts.');

-- 3. 建立 FULLTEXT 索引
ALTER TABLE reviews ADD FULLTEXT INDEX ft_content (content);

-- 4. 驗證索引
SHOW INDEX FROM reviews WHERE Key_name = 'ft_content';

自然語言模式查詢（預設）

SELECT id, content,
       MATCH(content) AGAINST('new single' IN NATURAL LANGUAGE MODE) AS relevance
FROM reviews
WHERE MATCH(content) AGAINST('new single' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

• 自動忽略停用詞（stopwords：the, and, is...）。
• 50% 門檻：若搜尋詞出現在超過 50% 文件中，視為常見詞自動忽略（僅自然語言模式）。
• 最小詞長：預設 4 字元（ft_min_word_len），可調整需重建索引。

布林模式查詢（精準控制）

-- 必須包含 new，必須包含 single，排除 Madonna
SELECT * FROM reviews
WHERE MATCH(content) AGAINST('+new +single -Madonna' IN BOOLEAN MODE);

-- 精確片語搜尋
SELECT * FROM reviews
WHERE MATCH(content) AGAINST('"Throwing Rocks"' IN BOOLEAN MODE);

-- 萬用字元（詞幹搜尋）
SELECT * FROM reviews
WHERE MATCH(content) AGAINST('play*' IN BOOLEAN MODE);
-- 匹配 play, playing, played...

-- 權重調整
SELECT * FROM reviews
WHERE MATCH(content) AGAINST('>new 

EXPLAIN SELECT * FROM reviews
WHERE MATCH(content) AGAINST('+new +single' IN BOOLEAN MODE);
-- type=fulltext, key=ft_content, rows=1 (理想)