mysql中 REGEXP 和 LIKE 的效率问题？

看到一个帖子，是讨论 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 左端不固定，那么查询就不会用上索引，效率就很不好，如果你有更好的方法，我们可以邮件讨论。