在 innoDB 中优化 MySQL LIKE '%string%' 查询
有这张桌子:
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%'
表是 InnoDB，(所以现在没有 FULLTEXT)为了优化这样的查询，哪个索引是最好的?
The table is InnoDB, (so no FULLTEXT for now) which would be the best index to use in order to optimize such query?
我们尝试了一个简单的:
We've tried a simple :
ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);
但是一个解释查询显示需要扫描整个表如果我们的查询 where LIKE 'whatever%' 代替，则该索引执行良好，否则没有任何价值.
But an explain query shows that need to scan the whole table if our queries where LIKE 'whatever%' instead, this index performs well, but otherwise has no value.
是否有针对 innoDB 的优化?
Is there anyway to optimize this for innoDB ?
谢谢！
推荐答案
索引是从字符串的开头到结尾构建的.当您使用 LIKE 'whatever%' 类型子句时，MySQL 可以使用这些基于起始的索引非常快速地查找 whatever.
Indexes are built from the start of the string towards the end. When you use LIKE 'whatever%' type clause, MySQL can use those start-based indexes to look for whatever very quickly.
但是切换到 LIKE '%whatever%' 会删除字符串开头的锚点.现在无法使用基于起始的索引，因为您的搜索词不再锚定在字符串的开头——它浮动"在中间的某个地方，整个字段都必须被搜索.任何 LIKE '%... 查询都不能使用索引.
But switching to LIKE '%whatever%' removes that anchor at the start of the string. Now the start-based indexes can't be used, because your search term is no longer anchored at the start of the string - it's "floating" somewhere in the middle and the entire field has to be search. Any LIKE '%... query can never use indexes.
如果您所做的只是浮动"搜索，那么您就使用全文索引，因为它们专为这种用途而设计.
That's why you use fulltext indexes if all you're doing are 'floating' searches, because they're designed for that type of usage.
重要提示:InnoDB 现在 从 5.6.4 版本开始支持全文 索引.因此，除非您无法升级到至少 5.6.4，否则没有什么可以阻止您使用 InnoDB *AND 全文搜索.
Of major note: InnoDB now supports fulltext indexes as of version 5.6.4. So unless you can't upgrade to at least 5.6.4, there's nothing holding you back from using InnoDB *AND fulltext searches.
相关文章