RegEx与LIKE在MySql查询中的性能 [英] Performance of RegEx vs LIKE in MySql queries

问题描述

谣言是这样的:

SELECT * FROM lineage_string where lineage like '%179%' and  lineage regexp '(^|/)179(/|$)'

会比这更快:

SELECT * FROM lineage_string where lineage regexp '(^|/)179(/|$)'

任何人都可以确认吗?或知道测试这种查询速度的一种不错的方法. 谢谢

Can anyone confirm? Or know a decent way to test the speed of such queries. Thanks

推荐答案

它可能会更快，因为与常规表达式相比，可以更快地评估LIKE条件，因此，如果大多数行未通过测试，它可能会更快.但是，如果大多数行都成功，则速度会变慢，因为必须为成功的行而不是仅对两次进行成功的测试.它还取决于优化程序选择首先运行哪个表达式.

It is possible that it could be faster because the LIKE condition can be evaluated more quickly then the regular expression so if most rows fail the test it could be faster. However it will be slower if most rows succeed as two tests must be run for successful rows instead of just one. It also depends on which expression the optimizer chooses to run first.

如果您有类似这样的东西，则可以看到更大的加速速度:

An even bigger speedup can be witnessed if you have something like this:

SELECT * FROM (
   SELECT * FROM lineage_string
   WHERE lineage LIKE '179%'
) WHERE lineage regexp '^179(/|$)'

现在可以使用索引查找可能的行，因为LIKE'179％'是

Now an index can be used to find likely rows because LIKE '179%' is sargable. Many rows won't need to be checked at all.

一如既往，最好的确定方法是根据实际数据自行测量.

As always the best way to be sure is to measure it for yourself on your actual data.

这篇关于RegEx与LIKE在MySql查询中的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！