优化 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%' 查询的可行选项。