MySQL 请问这两条语句谁更快一点

Question

select * from qs_exam_info where TRANS_DATE LIKE '2018-08-18%'

select * from qs_exam_info where date(TRANS_DATE) = '20180818'

数据库里面TRANS_DATE是按2018-10-16 00:14:17这样的格式存储的

Answer 1

如果 TRANS_DATE 为 varchar/char 类型，且该字段有索引，那么大部分情况下第一句比第二句更快

如果 TRANS_DATE 为 timestamp/datetime 类型，两句都用不到索引，全表扫描，所以效率差不多

Answer 2

不妨自己试试看结果,
一般来说, %后置的like会走索引, 而对条件字段使用函数不走索引.

没有索引的话, 对查询条件执行函数操作也会增加耗时.

Answer 3

正好本地有个数据表，帮你测试一下。
补充date字段无索引，数据格式datetime

mysql> select count(*) from table where date like '2018-08-18%';
+----------+
| count(*) |
+----------+
|     2128 |
+----------+
1 row in set (0.98 sec)

mysql> select count(*) from table where date(date) = '2018-08-18';
+----------+
| count(*) |
+----------+
|     2128 |
+----------+
1 row in set (1.21 sec)

mysql> select count(*) from table where date(date) = '2018-08-18';
+----------+
| count(*) |
+----------+
|     2128 |
+----------+
1 row in set (1.21 sec)

mysql> select count(*) from table where date like '2018-08-18%';
+----------+
| count(*) |
+----------+
|     2128 |
+----------+
1 row in set (0.98 sec)

mysql> select count(*) from table;
+----------+
| count(*) |
+----------+
|  2066946 |
+----------+
1 row in set (0.66 sec)

mysql> 

补充explain

mysql> explain select * from table where date like '2018-08-18%';
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | table                    | NULL       | ALL  | idx_date      | NULL | NULL    | NULL | 1853729 |    11.11 | Using where |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

mysql> explain select * from table where date(date) = '2018-08-18';
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | table                    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1853729 |   100.00 | Using where |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

Answer 4

1要快

2 where date(TRANS_DATE) where 中还要进行运算很耗时。

Answer 5

TRANS_DATE 是datetime的话没什么区别，建议这样写：

select * from qs_exam_info where TRANS_DATE >'2018-08-18' AND TRANS_DATE < '2018-08-19'

用between也是一样的

Answer 6

第一句比第二句更快

Answer 7

explain

Answer 8

1更快，2无法使用索引