在索引字段上使用函数，该字段的索引将会被抑制。如下案例：
查看表结构：
点击(此处)折叠或打开
mysql> show create table test06 \G
*************************** 1. row ***************************
Table: test06
Create Table: CREATE TABLE `test06` (
`id` bigint(11) NOT NULL DEFAULT '0',
`u_id` bigint(11) NOT NULL,
`openid` varchar(100) DEFAULT NULL,
`unionid` varchar(100) DEFAULT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`create_time` datetime NOT NULL,
KEY `idx_test03_id` (`id`),
KEY `idx_test03_name` (`username`),
KEY `idx_test06_crea_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
使用函数进行查询：
点击(此处)折叠或打开
mysql> select count(*) from test06 where date(create_time)=curdate();
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (1.00 sec)
不使用函数：
点击(此处)折叠或打开
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_keyskey | key_len | ref| rows | Extra|
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMP| test06 | ref | idx_test06_crea_time | idx_test06_crea_time | 5 | const | 1| Using index |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
