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语句，会得到以下结果：

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

这是我能做到的最好吗？有什么需要改进的地方吗？

- Pons

---

尝试 (name like '%jack%' and name REGEXP '[[:<:]]jack[[:>:]]' ) or ( username like '%jack%' and username REGEXP '[[:<:]]jack[[:>:]]' )。如果需要更好的性能，可以使用FULLTEXT索引或一些第三方工具。 - Imre L

正如@ImreL所建议的那样，如果在REGEXP之前添加LIKE％...％子句，那么它本质上就像一个过滤器，可以减少REGEXP尝试之前的潜在结果数量。我发现这可以将一个缓慢的2秒查询削减到0.3秒。 - fooquency

Answer 1

如果你必须使用正则表达式风格的WHERE子句，那么你一定会遇到慢查询问题。为了使正则表达式样式搜索生效，MySQL必须将name列中的每个值与正则表达式进行比较。而且，你的查询还通过查看username列使问题翻了一番。

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

有几种可尝试的方法，它们都涉及告别REGEXP。

其中之一是：

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

如果您在姓名和用户名列上创建索引，这应该是相当快的。它将寻找所有以“jack”开头的姓名/用户名。请注意，

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

这段代码将查找以“jack”结尾的名称，但像你的正则表达式搜索一样速度很慢。

你可以做的另一件事是弄清楚为什么你的应用程序需要能够搜索姓名或用户名的一部分。你可以从应用程序中删除此功能，或者想出更好的处理方式。

可能更好的方法：

1. 请求用户拆分其名称成名字和姓氏字段，并分别进行搜索。
2. 创建一个单独的“搜索所有用户”功能，仅在用户需要时使用，从而减少缓慢的正则表达式查询的频率。
3. 使用某种预处理程序，将他们的名称拆分成单独的名称单词表。无需使用正则表达式搜索名称单词表。
4. 弄清楚如何为此功能使用MySQL全文搜索。

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

- O. Jones

---

明白了，正则表达式搜索是您问题的正确解决方案。但它本质上是慢的。任何对非锚定文本列索引（即不从列中的第一个字符位置开始的索引）进行的搜索都会有同样的问题。所以，是的，LIKE '%jack%'像正则表达式式搜索一样慢。这是因为索引是有组织的，并且可以按顺序快速随机访问。想想在电话簿中查找“Jones”。您可以轻松地找到该名称的第一个人。但是如果您查找所有包含“one”字符的人，那么就需要很长时间。 - O. Jones

使用FULLTEXT索引更好吗？ - Pons

是的，使用全文搜索应该会有所帮助。我认为你会想要使用布尔模式。 - O. Jones

自从这个答案以来，正则表达式的性能有改善吗？在Postgres中，如果一个正则表达式有一个固定的前缀，它可以仅测试与该前缀匹配的索引条目，因此希望MySQL最终也会实现相同的功能。 - Andy

我刚在MariaDB 10.3上尝试了一下。使用RLIKE '^something.*'时索引没有被使用。 - O. Jones

Answer 2

我只是通过在where子句中添加fieldname！= ''就达到了50%的加速。这使得MySQL可以使用索引。

SELECT name, username, id 
FROM users 
WHERE name != '' 
    AND (name REGEXP '[[:<:]]jack[[:>:]]' or username REGEXP '[[:<:]]jack[[:>:]]') 
ORDER BY name 
LIMIT 0,5;

这不是完美的解决方案，但有所帮助。

- GuestUser666

---

这在很大程度上取决于表格/数据结构。 - stamster

Answer 3

在前面添加“LIKE”

从

SELECT cat_ID, categoryName FROM category WHERE cat_ID REGEXP '^15-64-8$' ORDER BY categoryName

到

SELECT cat_ID, categoryName FROM category WHERE cat_ID LIKE '15-64-8%' and cat_ID REGEXP '^15-64-8$' ORDER BY categoryName

当然，那只在您搜索以已知单词开头的短语时有效，否则全文索引是解决方案。

- C S N