字段使用函数索引被抑制

发布时间：2020-08-09 20:51:05 作者：StevenBeijing
来源：ITPUB博客阅读：166

在索引字段上使用函数，该字段的索引将会被抑制。如下案例：
查看表结构：

点击(此处)折叠或打开

使用函数进行查询：

点击(此处)折叠或打开

不使用函数：

点击(此处)折叠或打开

mysql> select count(*) from test06 where create_time=date_format(curdate(),'%Y-%m-%d');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)

可以看出：查询时间变快很多。
对比一下执行计划：

点击(此处)折叠或打开

mysql> explain select count(*) from test06 where date(create_time)=curdate();
+----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | test06 | index | NULL | idx_test06_crea_time | 5 | NULL | 2009559 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from test06 where create_time=date_format(curdate(),'%Y-%m-%d');
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMP | test06 | ref | idx_test06_crea_time | idx_test06_crea_time | 5 | const | 1 | Using index |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+

相关阅读