MySQL 全文搜尋最佳實戰：FULLTEXT 索引建立、相關性排序與布林操作符完整教學

FULLTEXT 索引 vs LIKE：效能與品質雙贏

從零建立全文搜尋系統

-- 1. 建表（InnoDB 5.6+ 支援 FULLTEXT）
CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT NOT NULL,
    author_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 插入測試資料
INSERT INTO articles (title, content, author_id) VALUES
('MySQL 索引優化實戰', '索引是查詢效能的關鍵。B+樹結構...', 1),
('全文搜尋入門', 'FULLTEXT 索引提供自然語言搜尋...', 2),
('資料庫鎖機制', '行鎖、表鎖、意向鎖詳解...', 3);

自然語言模式：智慧相關性排序

SELECT id, title,
       MATCH(title, content) AGAINST('索引 優化' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('索引 優化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

• 分數範圍 0~1，越高越相關。
• 自動忽略停用詞、50% 門檻過濾常見詞。
• 適合「搜尋框」預設模式。

布林模式：精準控制邏輯

-- 必須包含 索引，必須包含 優化，排除 鎖
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+索引 +優化 -鎖' IN BOOLEAN MODE);

-- 精確片語
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"B+樹結構"' IN BOOLEAN MODE);

-- 萬用字元（詞幹）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('優化*' IN BOOLEAN MODE);

-- 權重調整
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('>索引 <優化' IN BOOLEAN MODE);

查詢擴展模式（Query Expansion）

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('資料庫' WITH QUERY EXPANSION);
-- 先搜尋「資料庫」，取前幾筆高相關文件的高頻詞再擴展搜尋
-- 適合使用者查詢過短、需擴展發現相關內容

中文環境關鍵配置

-- my.cnf
[mysqld]
ft_min_word_len = 1          -- 最小詞長 1（預設 4，英文適用）
ngram_token_size = 2         -- ngram 解析器切分大小（2=bigram）

-- 建表指定 ngram 解析器
CREATE TABLE articles_cn (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ngram 解析器（MySQL 5.7.6+）將中文按固定長度切分（預設 bigram），無需詞典，支援 FULLTEXT。精度不如結巴/THULAC 分詞，但零配置、零維護。

效能調優清單

1. 大量匯入時先匯入資料、再建立 FULLTEXT 索引。
2. 定期 OPTIMIZE TABLE 整理索引碎片。
3. 查詢時 SELECT id, title, MATCH(...) AGAINST(...) AS score 同時取得分，避免重複計算。
4. 大量資料（億級）建議遷移 Elasticsearch/OpenSearch/Meilisearch。

避坑指南

• MATCH() AGAINST() 在 WHERE 與 SELECT 參數必須完全一致才能複用計算。
• 自然語言模式不支援 +/-/* 操作符。
• ft_stopword_file 自訂停用詞表，避免業務關鍵字被過濾。
• InnoDB FULLTEXT 索引變更即時生效，但大量寫入可能造成索引碎片，需監控 Innodb_ft_index_table、Innodb_ft_index_cache 狀態。