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

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

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 用于在事务发生时保存事务的日期和时间

我的桌子里有超过100万条记录和声明

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

需要很长时间。

编辑:

看看这篇关于“ 为什么 MySQL 的 DATETIME 可以而且应该被避免”的博文

72982 次浏览
小开

解释是什么意思? (运行 EXPLAINSELECT * FROM transactionlist，其中 date (TranDateTime) =’2008-08-17’)

如果因为 date ()函数而不使用索引，那么范围查询应该运行得很快:

SELECT * FROM transactionlist where TranDateTime > =’2008-08-17’AND TranDateTime <’2008-08-18’

小开

我不知道 mySql 的具体细节，但是只索引整个 date 字段有什么坏处呢？

那就搜索:

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

如果索引是 b-tree 或其他合理的索引，那么应该很快就能找到这些索引。

小开
最佳答案

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

我会这样做:

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

这应该能告诉你2008-08-17发生了什么。

小开

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

小开

不要根据函数创建索引(如果在 mysql 中也可以的话) ，而是让 where 子句进行范围比较。比如:

TransDateTime >’2008-08-17在哪里 00:00:00’和 TranDateTime < ’2008-08-1711:59:59’)

这允许 DB 使用 TranDateTime 上的索引(有一个，对吗?)来执行选择。

小开

ValeriyKravchuk 对这个特性的要求非常严格，在 MySQL 站点上说要使用这种方法。

”与此同时，可以使用字符列将 DATETIME 值存储为字符串，只索引前 N 个字符。只要在 MySQL5中谨慎使用触发器，就可以基于这个想法创建一个相当健壮的解决方案。”

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

小开

不能仅在日期部分创建索引。是否有必要这样做？

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

你会发现的

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

是有效率的，并做你想要的。

小开

我不知道 mySQL 的细节，但是只索引整个 date 字段有什么坏处呢？

如果对 * 树、散列使用函数魔法，... 将不复存在，因为要获取值，必须调用函数。但是，因为您不知道未来的结果，所以必须对表进行全面扫描。

没什么好补充的。

也许你的意思是像计算(计算?)索引... 但到目前为止，我只在 Intersystems Caché 看到过这个。我不认为在关系数据库(AFAIK)中存在这种情况。

在我看来，一个好的解决方案如下(更新的 clintp 示例) :

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没有区别(我通常使用这种格式)。

小开

创建一个只包含日期 convert(datetime, left(date_field,10))的新字段，然后对其进行索引。

小开

一个非常好的解决方案是使用时间戳作为时间，而不是使用日期时间。 它被存储为 INT 并且被索引得足够好。 我个人在事务表中遇到过这样的问题，有大约一百万条记录，而且速度很慢，最后我指出这是由于索引字段(日期时间)不好造成的。 现在它跑得很快。

小开

Datetime LIKE something% 也不会捕获索引。

用这个: WHERE datetime _ field > = curdate () ;
可以捕捉到索引,
和覆盖 < strong > 今天: 00:00:00直到今天: 23:59:59
成交。

小开

另一种选择(版本 < em > 5.7.3 及以上)是根据 datetime 列创建生成的/虚拟列，然后对其进行索引。

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

如果需要修改表，或者正在编写新的表，请考虑将日期和时间存储在具有相应类型的单独列中。通过使用更小的键空间和更少的存储空间(与从 datetime 派生的仅日期列相比) ，可以获得性能。这也使得在复合键中使用它变得可行，甚至可以在其他列之前使用。

就观察所而言:

+-------------------+------------------+------+-----+---------+----------------+
| 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       |                |
+-------------------+------------------+------+-----+---------+----------------+

提交答案