在 innoDB 中优化 MySQL LIKE '%string%' 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10354248/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Optimizing MySQL LIKE '%string%' queries in innoDB
提问by jmserra
Having this table:
有这个表：
CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
We would like to optimize the following query:
我们想优化以下查询：
SELECT id FROM example WHERE keywords LIKE '%whatever%'
The table is InnoDB, (so no FULLTEXT for now) which would be the best index to use in order to optimize such query?
该表是 InnoDB，（所以现在没有 FULLTEXT）哪个是优化此类查询的最佳索引？
We've tried a simple :
我们尝试了一个简单的：
ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);
But an explain query shows that need to scan the whole tableif our queries where LIKE 'whatever%' instead, this index performs well, but otherwise has no value.
但是解释查询显示， 如果我们的查询 LIKE 'whatever%' 代替，则需要扫描整个表，该索引执行良好，否则没有任何价值。
Is there anyway to optimize this for innoDB ?
反正有没有针对 innoDB 优化这个？
Thanks!
谢谢！
回答by Marc B
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 whateververy quickly.
索引是从字符串的开头到结尾构建的。当你使用LIKE 'whatever%'type 子句时，MySQL 可以使用那些基于 start 的索引来whatever非常快速地查找。
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.
但是切换到LIKE '%whatever%'会删除字符串开头的锚点。现在不能使用基于开始的索引，因为您的搜索词不再锚定在字符串的开头——它在中间的某个地方“浮动”，整个字段必须被搜索。任何LIKE '%...查询都不能使用索引。
That's why you use fulltext indexes if all you're doing are 'floating' searches, because they're designed for that type of usage.
这就是为什么如果您所做的只是“浮动”搜索，您就使用全文索引，因为它们是为这种类型的使用而设计的。
Of major note: InnoDB now supports fulltextindexes 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 *ANDfulltext searches.
主要注意事项：InnoDB 现在支持从 5.6.4 版开始的全文索引。因此，除非您不能升级到至少 5.6.4，否则没有什么可以阻止您使用 InnoDB * AND全文搜索。
回答by abulhol
I would like to comment that surprisingly, creating an index also helped speed up queries for like '%abc%'queries in my case.
我想评论一下，令人惊讶的是，like '%abc%'在我的情况下，创建索引也有助于加快查询速度。
Running MySQL 5.5.50on Ubuntu(leaving everything on default), I have created a table with a lot of columns and inserted 100,000dummy entries. In one column, I inserted completely random strings with 32 characters (i.e. they are all unique).
I ran some queries and then added an index on this column.
A simple
运行MySQL 5.5.50在Ubuntu（留在默认的一切），我创建了一个表有很多列，并且插入100,000虚拟条目。在其中一栏中，我插入了 32 个字符的完全随机字符串（即它们都是唯一的）。
我运行了一些查询，然后在此列上添加了一个索引。一个简单的
select id, searchcolumn from table_x where searchcolumn like '%ABC%'
returns a result in ~2 secondswithoutthe index and in 0.05 secondswiththe index.
返回~2 seconds不带索引和0.05 seconds带索引的结果。
This does not fit the explanations above (and in many other posts). What could be the reason for that?
这不符合上述（以及许多其他帖子）的解释。那可能是什么原因？
EDITI have checked the EXPLAIN output. The output says rows is 100,000, but Extra info is "Using where; Using index". So somehow, the DBMS has to search all rows, but still is able to utilise the index?
编辑我检查了 EXPLAIN 输出。输出显示行是100,000，但额外信息是“ Using where; Using index”。所以不知何故，DBMS 必须搜索所有行，但仍然能够利用索引？
