在 innoDB 中优化 MySQL LIKE '%string%' 查询 [英] Optimizing MySQL LIKE '%string%' queries in innoDB

问题描述

有这张桌子:

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.

这篇关于在 innoDB 中优化 MySQL LIKE '%string%' 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！