如何加速 SELECT .. LIKE 在 MySQL 中对多列的查询? [英] 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 个字符.当您执行 LIKE '%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+.

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