優化 InnoDB 中的 MySQL LIKE '%string%' 查詢

許多應用程式需要在文字正文中搜尋字串。 MySQL 提供了 LIKE 運算子來執行此類搜索，但是我們如何優化這些查詢以獲得最佳效能？

問題

考慮下表：

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

執行以下查詢時：

SELECT id FROM example WHERE keywords LIKE '%whatever%'

我們可能期望資料庫使用索引來加快搜尋速度。但是，如果我們使用以下方法在關鍵字列上新增一個簡單索引：

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

EXPLAIN 查詢顯示： MySQL 仍然需要掃描整個表格。

解決方案

MySQL 透過匹配搜尋字串的開頭來使用索引。對於像 LIKE 'whatever%' 這樣的查詢，可以使用索引，因為 'whatever' 錨定在字串的開頭。

但是，像 LIKE '%whatever%' 這樣的查詢沒有這樣的錨定。搜尋字詞在字串中「浮動」出現，迫使MySQL掃描整個欄位。

為了優化此類查詢，我們可以使用全文索引。這些索引是專門為「浮動」搜尋而設計的。 InnoDB 從 5.6.4 版本開始支援全文索引，使其成為優化 LIKE '%string%' 查詢的可行選項。