MySQL的正则表达式比like更慢 [英] MySQL regexp much slow than like

问题描述

  SELECT data 
 FROM test 
 WHERE col REGEXPasdf_ [0-9] +
 LIMIT 1 
  
 $ 1 
 
 
 
 
 
 
 
 
 $ p>  SELECT data 
 FROM test 
 WHERE col LIKEasdf_％
 LIMIT 1 
  

... 1行设置（0.01秒）
$ b

regexp 可以给我确切的结果，如果我使用像 sql，我必须过滤数据。有没有一些方法可以改进？

顺便说一句：测试有200万行并且长大了。

请将您的 regexp 字符串更改为^ asdf_ [0-9] + 。

LIKE 被锚定（即 LIKE'asdf _％'说以asdf_开头的字符串），而 REGEXP 不是（ REGEXP'asdf_ [0-9] +' c> REGEXP'asdf_ [0-9] +'就像是说 LIKE'％asdf _％'。

我认为 REGEXP 仍然会比 LIKE 慢一些，但是希望线锚的开始会大大加快速度。

SELECT data 
  FROM test 
 WHERE col REGEXP "asdf_[0-9]+" 
 LIMIT 1

...1 row in set (1 min 43.12 sec)

SELECT data 
  FROM test 
 WHERE col LIKE "asdf_%" 
 LIMIT 1

...1 row in set (0.01 sec)

regexp can give me the exact result, I have to filter data if I use like sql. Is there some way to improve?

BTW: test has 2 million rows and grow up.

解决方案

Try changing your regexp string to "^asdf_[0-9]+".

LIKE is anchored (ie LIKE 'asdf_%' says "a string starting with asdf_"), whereas REGEXP is not (REGEXP 'asdf_[0-9]+' looks for that anywhere within the string).

Note that doing REGEXP 'asdf_[0-9]+' is like saying LIKE '%asdf_%'.

I think REGEXP will still be a bit slower than LIKE, but hopefully the start of line anchor will greatly speed it up.

这篇关于MySQL的正则表达式比like更慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！