Mysql的优化REGEXP(Mysql optimization for REGEXP)

该查询（用不同的名称来代替“杰克”）发生多次在我的慢查询日志。为什么？

用户表有很多字段（超过这三个我已经选中），约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:

如果必须使用正则表达式风格的WHERE子句，你肯定会被缓慢的查询问题的困扰。对于正则表达式风格的搜索工作，MySQL不得不每个值与正则表达式你的名字列比较。而且，你的查询已经翻了一番还通过看你的用户名栏的麻烦。

这意味着MySQL不能采取任何索引的优势，这是所有DBMS如何加快大表的查询。

有一些事情你可以试试。所有这些都涉及告别REGEXP。

其中一个是这样的：

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

如果你创建你的名字和用户名列的索引，这应该是体面快。它会寻找与“杰克”开头的所有名称/用户名。请注意，

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

将寻找“杰克”结尾的名字，但将是缓慢的喜欢你的正则表达式式搜索。

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

可能更好的方法：

问问你的用户在他们的名字分解为给定的名和姓字段，并分别进行搜索。
创建当用户需要它，从而降低你的速度慢的正则表达式样式的查询的频率，只有被使用一个单独的“搜索所有用户”功能。
分手时他们的名字使用某种预处理编程'的一个单独的名称，字表自己。搜索名称字表，而正则表达式。
弄清楚如何使用MySQL全文搜索此功能。

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

Answer 2:

我达到了50％的加速只需通过添加fieldname where子句中！=“”。它使MySQL使用索引。

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

不是一个完美的解决方案，但帮助。

Answer 3:

添加“喜欢”在前面

从

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

的COS，只有当U [R搜索短语你知道起什么，否则全文索引是该解决方案的作品。

文章来源: Mysql optimization for REGEXP