MySQL中 regexp 和 like 性能比较

在MySQL中,我们一般使用like进行模糊查询匹配(关系型书数据库都是)。然而,我们有时候会用regexp正则匹配目标值,但是两者在查询性能上是否有差异呢?哪个性能更好呢?

答案其实很明确,like的性能肯定是优于regexp的。不过,今天我们可以来做个小测试来看看两者的具体表现。

☑️ 我们首先创建一张测试表。

drop table if exists users;

create table users
(
    id         serial primary key,
    username   varchar(255not null,
    password   varchar(255not null,
    email      varchar(255not null,
    category   varchar(255not null,
    created_at timestamp    not null default current_timestamp,
    updated_at timestamp    not null default current_timestamp on update current_timestamp
);

接着,生成一些测试数据。下面脚本中生成的数据量可能多,因此,在400万数据之后我便停止了存储过程。

drop procedure if exists generate_users;

create procedure generate_users()
begin
    declare i int default 0;
    set autocommit = 0;
    while i < 500000
        do
            insert into users (username, password, email, category)
            values (concat('user', i), 'password'concat('user', i, '@gmail.com'), 'user'),
                   (concat('admin', i), 'password'concat('admin', i, '@gmail.com'), 'admin'),
                   (concat('admin', i), 'password'concat('admin', i, '@163.com'), 'admin'),
                   (concat('super', i), 'password'concat('super', i, '@qq.com'), 'super'),
                   (concat('root', i), 'password'concat('root', i, '@126.com'), 'root'),
                   (concat('test', i), 'password'concat('test', i, '@outlook.com'), 'test'),
                   (concat('guest', i), 'password'concat('guest', i, '@hotmail.com'), 'guest'),
                   (concat('manager', i), 'password'concat('manager', i, '@yahoo.com'), 'manager');
            set i = i + 1;
            insert into users (username, password, email, category)
            values (concat('user', i), 'password'concat('user', i, '@gmail.com'), 'user'),
                   (concat('admin', i), 'password'concat('admin', i, '@gmail.com'), 'admin'),
                   (concat('admin', i), 'password'concat('admin', i, '@163.com'), 'admin'),
                   (concat('super', i), 'password'concat('super', i, '@qq.com'), 'super'),
                   (concat('root', i), 'password'concat('root', i, '@126.com'), 'root'),
                   (concat('test', i), 'password'concat('test', i, '@outlook.com'), 'test'),
                   (concat('guest', i), 'password'concat('guest', i, '@hotmail.com'), 'guest'),
                   (concat('manager', i), 'password'concat('manager', i, '@yahoo.com'), 'manager');
            set i = i + 1;

            if i % 10000 = 0 then
                commit;
            end if;
        end while;
end;

数据插入后。我们正式进行查询测试。比如,我们需要查询以@163.com结尾的100000后的100行记录。

select * from users where `email` regexp '@163.com$' limit 100000100;

经过多次测试,可以看到,该查询需要花费0.62秒左右的时间。

MySQL中 regexp 和 like 性能比较

🎳 但是like会表现的怎么样呢? 我们一起来看一下,

select * from users where `email` like '%@163.com' limit 100000100;

经多轮测试,可以看到,like匹配需要大约0.45秒的时间,明显由于regexp的查询。

MySQL中 regexp 和 like 性能比较

🎬我们的查询稍微复杂点了。

select * from users where `email` regexp '^a.*500.*gmail.com$' limit 100;

多轮测试后,regexp情况下需要花费0.35秒。MySQL中 regexp 和 like 性能比较

那么like需要多久呢?

select * from users where `email` like 'a%500%gmail.com' limit 100;

like则仅花费0.20秒左右的时间。MySQL中 regexp 和 like 性能比较

通过以上的测试,我们可以得出一些关于LIKE和REGEXP在MySQL中性能表现的结论。尽管REGEXP提供了更强大和灵活的匹配能力,它在处理复杂的模式匹配时是不可或缺的,但在我们的实验中,LIKE在执行效率方面展现出了明显的优势。这主要是因为LIKE操作通常更简单,而且更容易被数据库优化器利用,尤其是在模式的开始不是通配符的情况下。

然而,值得注意的是,这些测试结果并不意味着在所有情况下LIKE都是更优的选择。具体情况还取决于查询的具体需求、数据的分布,以及数据库的配置。在需要进行特别复杂或特定形式的字符串匹配时,REGEXP的强大功能是LIKE无法替代的。但是,当你面临的是简单的模式匹配需求时,LIKE不仅更易于理解和使用,而且如我们的测试所示,它往往能提供更快的响应时间。

综上所述,选择LIKE还是REGEXP应当基于你的具体需求,以及对查询性能和灵活性之间的平衡。在进行选择时,保持对性能的敏感性和对查询需求的清晰认识是非常重要的。最后,无论选择哪种方式,定期进行性能测试和调优总是值得推荐的做法,以确保你的数据库查询能够在不断变化的数据环境中保持高效和准确。


原文始发于微信公众号(小新数据库):MySQL中 regexp 和 like 性能比较

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/207560.html

(0)
小半的头像小半

相关推荐

发表回复

登录后才能评论
极客之家——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!