like vs regexp

有个开发同学问,这两条sql哪个跑得更快一些,简单测试一下(percona 5.5版本):

1SELECT * FROM like_reg_test WHERE feature LIKE '%luoxuan%';
2SELECT * FROM like_reg_test WHERE feature REGEXP 'luoxuan';
 
前提:表上无索引,为新建表,测试是在两条SQL跑多次再进行,每条SQL5次,取5次跑得平均值。
 
root@xxx 11:04:35>select COUNT(*) FROM like_reg_test;
+----------+
| COUNT(*) |
+----------+
|  2228225 |
+----------+
 
root@xxx 11:04:49>select * FROM like_reg_test WHERE feature LIKE '%luoxuan%';(重复跑5)
+----------+-------+---------------------+
| id       | gift  | feature             |
+----------+-------+---------------------+
| 10000000 | lijie | aaaaaaluoxuanbbbbbb |
+----------+-------+---------------------+
 
root@xxx 11:05:20>select (2.06+1.59+2.07+2.06+1.60)/5;
+------------------------------+
| (2.06+1.59+2.07+2.06+1.60)/5 |
+------------------------------+
|                     1.876000 |
+------------------------------+
 
root@xxx 11:05:54.SELECT * FROM like_reg_test WHERE feature REGEXP 'luoxuan';(重复跑5)
+----------+-------+---------------------+
| id       | gift  | feature             |
+----------+-------+---------------------+
| 10000000 | lijie | aaaaaaluoxuanbbbbbb |
+----------+-------+---------------------+
 
root@xxx 11:06:27>select (2.50+2.72+2.57+2.74+2.53)/5;
+------------------------------+
| (2.50+2.72+2.57+2.74+2.53)/5 |
+------------------------------+
|                     2.612000 |
+------------------------------+

有个老外做了更详细对比:like_vs_regexp


Post a Comment