首页 / 知识

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

2023-04-17 07:22:00

How does one create an index on the date part of DATETIME field in MySql

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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条记录，并且该语句

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

需要很长的时间。

编辑：

看看这篇博客文章"为什么和应该避免MySQL的DATETIME"

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

我要做的是这样的：

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

那应该可以给您提供2008-08-17发生的一切。

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

您不能仅在日期部分上创建索引。您有理由吗？

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

我想你会发现

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

是高效的，并且可以做您想要的。

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

1
2
3
4
5

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;

我不了解mySql的细节，但是仅对整个日期字段编制索引有什么害处？

然后只需搜索：

1
2
3

select * from translist
where TranDateTime > '2008-08-16 23:59:59'
and TranDateTime < '2008-08-18 00:00:00'

如果索引是b树或其他合理的索引，则应该很快找到它们。

一个很好而有效的解决方案是使用时间戳记作为时间，而不是日期时间。
它存储为INT，并被很好地索引。
我个人在事务表上遇到了这样的问题，该事务表具有大约一百万条记录，并且运行缓慢，最后我指出这是由错误的索引字段(日期时间)引起的。
现在它运行非常快。

Valeriy Kravchuk在据说使用此方法的MySQL站点上针对此问题的功能请求。

"与此同时，您可以使用字符列将DATETIME值存储为字符串，仅索引前N个字符。通过在MySQL 5中谨慎使用触发器，您可以基于此思想创建一个合理而强大的解决方案。"

您可以编写一个非常容易的例程来添加此列，然后使用触发器使该列保持同步。该字符串列上的索引应该很快。

datetime LIKE something％也不会捕获索引。

使用此命令：WHERE datetime_field> = curdate();
那会抓住指数，
并覆盖今天：00：00：00直到今天：23：59：59
做完了

我不了解mySQL的细节，但是仅对整个日期字段编制索引有什么害处？

如果将功能魔术用于*树，则散列...将消失，因为要获取值，必须调用该函数。但是，由于您不知道前面的结果，因此必须对表进行完整扫描。

没有要添加的内容。

也许您的意思是类似计算(计算的)索引……但是到目前为止，我仅在IntersystemsCaché中看到了这一点。我认为关系数据库(AFAIK)中没有这种情况。

我认为，以下是一个好的解决方案(更新的clintp示例)：

1
2
3

SELECT * FROM translist
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
AND TranDateTime < '2008-08-18 00:00:00.0000'

我认为您使用00:00:00.0000还是00:00都没有区别(我通常以这种格式使用它)。

如果修改表是一种选择，或者您要编写一个新表，请考虑将日期和时间存储在具有相应类型的单独列中。通过减小键空间并减少存储量(与从datetime导出的仅日期的列相比)，可以提高性能。这也使得甚至在其他列之前也可以在复合键中使用。

在OP的情况下：

1
2
3
4
5
6
7
8
9
10
11
12
13

+-------------------+------------------+------+-----+---------+----------------+
| 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 | |
| TranDate | date | NO | | NULL | |
| TranTime | time | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+

而不是基于函数创建索引(如果在mysql中甚至可能)，请使where子句进行范围比较。就像是：