用于REGEXP的MySQL优化

Question

在我的慢速查询日志中，此查询（使用不同的名称而不是“ jack”）发生了很多次。为什么？

Users表具有许多字段（超过我选择的这三个字段）和大约40.000行。

select name,username,id from Users where ( name REGEXP '[[:<:]]jack[[:>:]]' ) or ( username REGEXP '[[:<:]]jack[[:>:]]' ) order by name limit 0,5;

id是主要的，并且是自动递增的。
name有一个索引。
username具有唯一索引。

有时需要3秒钟！如果我在MySQL上解释选择，我会得到：

select type: SIMPLE
table: Users
type: index
possible keys: NULL
key: name
key len: 452
ref: NULL
rows: 5
extra: Using where

这是我能做的最好的吗？我该如何解决？

Answer 1

如果必须使用regexp-style
WHERE子句，则肯定会遇到查询缓慢的问题。为了使regexp样式的搜索有效，MySQL必须将name列中的每个值与regexp进行比较。而且，通过查看用户名列，您的查询使麻烦增加了一倍。

这意味着MySQL无法利用任何索引，这就是所有DBMS加快大型表查询的方式。

您可以尝试一些方法。所有这些都涉及告别REGEXP。

一个是这样的：

WHERE name LIKE CONCAT('jack', '%') OR username LIKE CONCAT('jack', '%')

如果您在名称和用户名列上创建索引，则应该会很快。它将查找以“ jack”开头的所有名称/用户名。注意

WHERE name LIKE CONCAT('%','jack') /* SLOW!!! */

会寻找以“ jack”结尾的名称，但会像您的正则表达式样式搜索一样缓慢。

您可以做的另一件事是弄清楚为什么您的应用程序需要能够搜索名称或用户名的一部分。您可以从应用程序中删除此功能，或者找出一些更好的html" target="_blank">方法来处理它。

可能的更好方法：

1. 要求您的用户将其名字分解为“给定名称”和“姓氏”字段，然后分别进行搜索。
2. 创建单独的“搜索所有用户”功能，该功能仅在用户需要时才使用，从而减少了慢速正则表达式样式查询的频率。
3. 您可以使用某种预处理程序将它们的名称分解成一个单独的名称-单词表。搜索没有正则表达式的名称单词表。
4. 弄清楚如何使用MySQL全文搜索来实现此功能。

所有这些都涉及一些编程工作。