字段運用函數索引被抑制

2024-09-07 22:12:39

字體：大中小

來源：轉載

供稿：網友

在索引字段上使用函數，該字段的索引將會被抑制。如下案例：
查看表結構：
點擊(此處)折疊或打開

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_keys    key | key_len              | ref     | rows | Extra              |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMP        | test06 | ref | idx_test06_crea_time | idx_test06_crea_time | 5       | const | 1    | Using index |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+

（編輯：武林網）

上一篇：SQL 增加列、修改列、刪掉列

下一篇：Percona XtraDB Cluster的優勢和缺點介紹