在索引字段上使用函數(shù)，該字段的索引將會被抑制。如下案例：
查看表結(jié)構(gòu)：
點擊(此處)折疊或打開
成都創(chuàng)新互聯(lián)專注于企業(yè)營銷型網(wǎng)站、網(wǎng)站重做改版、靈石網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5建站、商城開發(fā)、集團公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為靈石等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
-
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
使用函數(shù)進行查詢：
-
mysql> select count(*) from test06 where date(create_time)=curdate();
-
+----------+
-
| count(*) |
-
+----------+
-
| 0 |
-
+----------+
-
1 row in set (1.00 sec)
不使用函數(shù)：
-
mysql> select count(*) from test06 where create_time=date_format(curdate(),'%Y-%m-%d');
-
+----------+
-
| count(*) |
-
+----------+
-
| 0 |
-
+----------+
-
1 row in set (0.03 sec)
可以看出：查詢時間變快很多。
對比一下執(zhí)行計劃：
-
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 |
-
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
網(wǎng)站欄目：字段使用函數(shù)索引被抑制
URL網(wǎng)址：
http://weahome.cn/article/ggggpi.html
重慶分公司
重慶分公司
