如何在多列MySQL中加快SELECT ..LIKE查询? [英] How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

问题描述

我有一个MySQL表，对此我经常进行SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'查询.任何索引都可以帮助加快速度吗?

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?

表中有几百万条记录.如果有什么方法可以加快搜索速度，是否会严重影响数据库文件对磁盘的使用以及INSERT和DELETE语句的速度? (从未执行过UPDATE)

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 INSERT and DELETE statements? (no UPDATE is ever performed)

更新:发布后，我很快就看到了很多有关LIKE在查询中使用方式的信息和讨论.我想指出的是，解决方案必须必须使用LIKE '%text%'(也就是说，我要查找的文本在前面加上％通配符).由于多种原因，包括安全性，数据库也必须是本地的.

Update: Quickly after posting, I have seen a lot of information and discussion about the way LIKE is used in the query; I would like to point out that the solution must use 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.

推荐答案

索引不会加快查询的速度，因为对于文本列，索引通过从左开始索引N个字符来工作.当您喜欢'％text％'时，它不能使用索引，因为在文本之前可以有可变数量的字符.

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.

您应该做的是根本不使用这样的查询.相反，您应该使用MySQL支持MyISAM表的类似FTS(全文搜索)的工具.自己为非MyISAM表创建这样的索引系统也很容易，您只需要一个单独的索引表，即可在实际表中存储单词及其相关ID.

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 5.6+的InnoDB表.

Full text search available for InnoDB tables with MySQL 5.6+.

这篇关于如何在多列MySQL中加快SELECT ..LIKE查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！