如何加速 SELECT .. LIKE 在 MySQL 中对多列的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2042269/
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
How to speed up SELECT .. LIKE queries in MySQL on multiple columns?
提问by Tom
I have a MySQL table for which I do very frequent SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'queries. Would any kind of index help speed things up?
我有一个 MySQL 表，我对其进行了非常频繁的SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'查询。任何类型的索引都有助于加快速度吗？
There are a few million records in the table. If there is anything that would speed up the search, would it seriously impact disk usage by the database files and the speed of INSERTand DELETEstatements? (no UPDATEis ever performed)
表中有几百万条记录。如果有任何可以加速搜索的东西，它会严重影响数据库文件的磁盘使用以及INSERT和DELETE语句的速度吗？（没有UPDATE被执行过）
Update: Quickly after posting, I have seen a lot of information and discussion about the way LIKEis used in the query; I would like to point out that the solution mustuse LIKE '%text%'(that is, the text I am looking for is prepended and appended with a % wildcard). The database also has to be local, for many reasons, including security.
更新：发帖后很快，看到了很多关于LIKE查询中使用方式的信息和讨论；我想指出该解决方案必须使用LIKE '%text%'（即，我要查找的文本被预先添加并附加了 % 通配符）。出于多种原因，包括安全性，数据库也必须是本地的。
回答by reko_t
An index wouldn't speed up the query, because for textual columns indexes work by indexing N characters starting from left. When you do LIKE '%text%' it can't use the index because there can be a variable number of characters before text.
索引不会加快查询速度，因为对于文本列，索引是通过从左侧开始索引 N 个字符来工作的。当您执行 LIKE '%text%' 时，它不能使用索引，因为文本之前可能有可变数量的字符。
What you should be doing is not use a query like that at all. Instead you should use something like FTS (Full Text Search) that MySQL supports for MyISAM tables. It's also pretty easy to make such indexing system yourself for non-MyISAM tables, you just need a separate index table where you store words and their relevant IDs in the actual table.
您应该做的根本不是使用这样的查询。相反，您应该使用 MySQL 支持 MyISAM 表的 FTS（全文搜索）之类的东西。自己为非 MyISAM 表制作这样的索引系统也很容易，您只需要一个单独的索引表，您可以在实际表中存储单词及其相关 ID。
Update
更新
Full text search available for InnoDB tables with MySQL 5.6+.
全文搜索可用于 MySQL 5.6+ 的 InnoDB 表。
回答by Michael
An index won't help text matching with a leading wildcard, an index can be used for:
索引不会帮助文本与前导通配符匹配，索引可用于：
LIKE 'text%'
But I'm guessing that won't cut it. For this type of query you really should be looking at a full text search provider if you want to scale the amount of records you can search across. My preferred provider is Sphinx, very full featured/fast etc. Lucenemight also be worth a look. A fulltext index on a MyISAM table will also work, but ultimately pursuing MyISAM for any database that has a significant amount of writes isn't a good idea.
但我猜这不会削减它。对于这种类型的查询，如果您想扩展可以搜索的记录数量，您确实应该查看全文搜索提供程序。我的首选供应商是Sphinx，功能非常齐全/快速等。Lucene也值得一看。MyISAM 表上的全文索引也可以使用，但最终为任何具有大量写入的数据库使用 MyISAM 并不是一个好主意。
回答by Mitch Wheat
An index can notbe used to speed up queries where the search criteria starts with a wildcard:
索引不能用于加速搜索条件以通配符开头的查询：
LIKE '%text%'
LIKE '%text%'
An index can (and might be, depending on selectivity) used for search terms of the form:
索引可以（并且可能是，取决于选择性）用于以下形式的搜索词：
LIKE 'text%'
LIKE 'text%'
回答by OderWat
I would add that in some cases you can speed up the query using an index together with like/rlike if the field you are looking at is often empty or contains something constant.
我想补充一点，在某些情况下，如果您正在查看的字段通常为空或包含一些常量，您可以使用索引和 like/rlike 来加速查询。
In that case it seems that you can limit the rows which are visited using the index by adding an "and" clause with the fixed value.
在这种情况下，您似乎可以通过添加具有固定值的“and”子句来限制使用索引访问的行。
I tried this for searching 'tags' in a huge table which usually does not contain a lot of tags.
我试过这个是为了在一个通常不包含很多标签的大表中搜索“标签”。
SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''
SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''
If you have an index on tags you will see that it is used to limit the rows which are being searched.
如果您有标签索引，您将看到它用于限制正在搜索的行。
回答by lingceng
Maybe you can try to upgrade mysql5.1 to mysql5.7.
也许你可以尝试将mysql5.1升级到mysql5.7。
I have about 70,000 records. And run following SQL:
我有大约 70,000 条记录。并运行以下 SQL：
select * from comics where name like '%test%';
It takes 2000msin mysql5.1. And it takes 200msin mysql5.7 or mysql5.6.
在mysql5.1中需要2000ms。而在 mysql5.7 或 mysql5.6 中需要200ms。
回答by Joshua Pinter
Add a Full Text Index and Use MATCH() AGAINST().
添加全文索引并使用MATCH() AGAINST().
Normal indexes will not help you with likequeries, especially those that utilize wildcards on both sides of the search term.
普通索引不会帮助您进行like查询，尤其是那些在搜索词两侧使用通配符的查询。
What you can do is add a full text index on the columns that you're interested in searching and then use a MATCH() AGAINST()query to search those full text indexes.
您可以做的是在您有兴趣搜索的列上添加全文索引，然后使用MATCH() AGAINST()查询来搜索这些全文索引。
Add a full text index on the columns that you need:
ALTER TABLE table ADD FULLTEXT INDEX index_table_on_x_y_z (x, y, z);Then query those columns:
SELECT * FROM table WHERE MATCH(x,y,z) AGAINST("text")
在您需要的列上添加全文索引：
ALTER TABLE table ADD FULLTEXT INDEX index_table_on_x_y_z (x, y, z);然后查询这些列：
SELECT * FROM table WHERE MATCH(x,y,z) AGAINST("text")
From our trials, we found these queries to take around 1ms in a table with over 1 million records. Not bad, especially compared to the equivalent wildcard LIKE %text%query which takes 16,400ms.
从我们的试验中，我们发现这些查询在包含超过 100 万条记录的表中花费大约 1 毫秒。不错，尤其是与LIKE %text%需要 16,400 毫秒的等效通配符查询相比。
Benchmarks
基准
MATCH(x,y,z) AGAINST("text")takes 1ms
MATCH(x,y,z) AGAINST("text")需要 1ms
LIKE %text%takes 16400ms
LIKE %text%需要 16400 毫秒
16400x faster!
快 16400 倍！
回答by Bruno Manuel Rosas Marques
Another way:
其它的办法：
You can mantain calculated columns with those strings REVERSEd and use
您可以使用这些字符串 REVERSEd 来维护计算列并使用
SELECT x, y, z FROM table WHERE x LIKE 'text%' OR y LIKE 'text%' OR z LIKE 'text%' OR xRev LIKE 'txet%' OR yRev LIKE 'txet%' OR zRev LIKE 'txet%'
Example of how to ADD a stored persisted column
如何添加存储的持久列的示例
ALTER TABLE table ADD COLUMN xRev VARCHAR(N) GENERATED ALWAYS AS REVERSE(x) stored;
and then create an indexes on xRev, yRevetc.
然后创建上的索引xRev，yRev等等。
回答by Juri Sinitson
Another alternative to avoid full table scans is selecting substrings and checking them in the having statement:
另一种避免全表扫描的替代方法是选择子字符串并在 have 语句中检查它们：
SELECT
al3.article_number,
SUBSTR(al3.article_number, 2, 3) AS art_nr_substr,
SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2,
al1.*
FROM
t1 al1
INNER JOIN t2 al2 ON al2.t1_id = al1.id
INNER JOIN t3 al3 ON al3.id = al2.t3_id
WHERE
al1.created_at > '2018-05-29'
HAVING
(art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');
