MYSQL里使用正则的速度快还是使用like模糊查询语句快? MYSQL里使用正则的速度快还是使用like模糊查询语句快?最好可以举例子说明 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 测试一下不就知道了。explain select * from tb .... mysql中 REGEXP 和 LIKE 的效率问题? 下面简单做个测试:1,我们先来看表person的索引信息:mysql> show index from person;+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| person | 0 | PRIMARY | 1 | id | A | 2009 | NULL | NULL | | BTREE | | | person | 1 | descs | 1 | descs | A | 2009 | NULL | NULL | YES | BTREE | | | person | 1 | name | 1 | name | A | 1004 | NULL | NULL | YES | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+2,下面具体看看这两条SQL语句的执行过程。mysql> explain SELECT * FROM person WHERE descs REGEXP '^.{5}$';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '_____';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)显然,两个SQL语句的执行过程是一样的,当然其性能也是如此。会不会有其他情况,看下面:mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '^_____%';+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+| 1 | SIMPLE | person | range | descs | descs | 63 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+1 row in set (0.09 sec)mysql> mysql> mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '_____%';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '^_____';+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+| 1 | SIMPLE | person | range | descs | descs | 63 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '%_____%';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)测试结果很明显,如果 LIKE 左端不固定,那么查询就不会用上索引,效率就很不好,如果你有更好的方法,我们可以讨论。 正则要比like功能强大很多 LIKE 会略快一些。但显然LIKE的功能无法与REGEXP相比。 另外是索引的问题,LIKE有可能使用索引但REGEXP则很难。 在线等,高手继续进来指导下。。 在线急等,如果直接在mysql中去掉换行 求个前奏语句 如何写让多个相同的数据库自动升级的代码 请问这样的需求是用存储过程写还是用触发器写???初学者 SQL取得剛增加的主鍵值!!!! 请教update语句 mysql连接问题 Sql Assistant 智能提示工具 配置 SQLyog 中文字段出现乱码 postgreSQL中 group by 日期的问题 mysql多表联合查询排序分页的效率问题? mysql更新10万条数据卡主
explain select * from tb ....
下面简单做个测试:
1,我们先来看表person的索引信息:
mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person | 0 | PRIMARY | 1 | id | A | 2009 | NULL | NULL | | BTREE | |
| person | 1 | descs | 1 | descs | A | 2009 | NULL | NULL | YES | BTREE | |
| person | 1 | name | 1 | name | A | 1004 | NULL | NULL | YES | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2,下面具体看看这两条SQL语句的执行过程。mysql> explain SELECT * FROM person WHERE descs REGEXP '^.{5}$';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '_____';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
显然,两个SQL语句的执行过程是一样的,当然其性能也是如此。
会不会有其他情况,看下面:
mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '^_____%';
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | person | range | descs | descs | 63 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.09 sec)
mysql>
mysql>
mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '_____%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '^_____';
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | person | range | descs | descs | 63 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM person WHERE descs LIKE '%_____%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 2009 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
测试结果很明显,如果 LIKE 左端不固定,那么查询就不会用上索引,效率就很不好,如果你有更好的方法,我们可以讨论。
另外是索引的问题,LIKE有可能使用索引但REGEXP则很难。