如何在MySql中的DATETIME字段的日期部分创建索引

如何在DATETIME字段的日期部分创建索引？

mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDateTime      | datetime         | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

TranDateTime用于在事件发生时保存事务的日期和时间

我的表中有超过1,000,000条记录和声明

SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' 

需要很长的时间.

编辑:

看一下这篇关于" 为什么MySQL的DATETIME可以而且应该避免 "的博客文章

1> Michael John..：

---

如果我没记错的话,那将会运行一个完整的表扫描,因为你正在通过一个函数传递列.MySQL将乖乖地为每一列运行该函数,绕过索引,因为查询优化器无法真正知道函数的结果.

我会做的是:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

这应该会给你2008-08-17发生的一切.

---

我知道这是一个陈旧的答案,但我不得不指出,因为MySQL使用字符串比较'DATETIME`; 您的查询返回正确的结果,并且不包含"TranDateTime = 2008-08-18 00:00:00"的行.

2> Mike Tunnicl..：

---

我并不是说听起来很可爱,但一种简单的方法是添加一个只包含日期部分和索引的新列.

3> MarkR..：

---

您无法仅在日期部分创建索引.你有什么理由吗？

即使您可以仅在日期部分创建索引,优化器也可能仍然不会将其用于上述查询.

我想你会发现的

SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'

是有效的,做你想要的.

4> Liran Brimer..：

---

另一个选项(与5.7.3及更高版本相关)是基于datetime列创建生成/虚拟列,然后对其进行索引.

CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_daetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;