首页 文章

REGEXP的Mysql优化

提问于
浏览 次
4

在我的慢查询日志中，此查询（使用不同的名称而不是“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

这是我能做的最好的吗？我能解决什么？

mysql regex optimization indexing query-optimization

3 回答

  • 2

    如果你必须使用regexp-style WHERE 子句，你肯定会受到慢查询问题的困扰 . 要使regexp样式的搜索起作用，MySQL必须将名称列中的每个值与regexp进行比较 . 而且，通过查看用户名列，您的查询也增加了麻烦 .

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

    你可以尝试一些事情 . 所有这些都涉及到REGEXP说再见 .

    一个是这样的：

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

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

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

    将查找以'jack'结尾的名称，但会像你的正则表达式搜索一样慢 .

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

    可能更好的方法：

    • 要求您的用户将其名称分解为给定名称和姓氏字段，并单独搜索 .

    • 创建一个单独的"search all users"功能，仅在用户需要时才使用，从而减少慢速正则表达式查询的频率 .

    • 使用某种预处理程序将自己的名称分解为单独的名称 - 单词表 . 在没有正则表达式的情况下搜索名称 - 单词表 .

    • 了解如何使用MySQL全文搜索此功能 .

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

    回复于
  • -3

    我只是通过在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;

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

    回复于
  • 13

    在前面添加“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

    因为，只有当你搜索短语时，才知道你知道的是什么，否则全文索引就是解决方案 .

    回复于

相关问题