如何在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'

需要很长的时间。

编辑：

看一下这篇博客文章"为什么并且应该避免使用MySQLs DATETIME"

解决方案

"解释"是什么意思？ (运行EXPLAIN SELECT * FROM transactionlist，其中date(TranDateTime)='2008-08-17')

如果由于date()函数未使用索引，则范围查询应运行得很快：

SELECT * FROM transactionlist，其中TranDateTime> ='2008-08-17'和TranDateTime <'2008-08-18'

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

然后只需搜索：

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

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

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

我要做的是这样的：

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

这应该可以为我们提供2008-08-17发生的所有事件以及恰好在2008-08-18 00:00:00发生的一切。如果存在问题，则可以将第二项更改为" 2008-08-17 23:59:59"，然后获得2008-08-17.

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

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

Where TranDateTime > '2008-08-17
  00:00:00' and TranDateTime <
  '2008-08-17 11:59:59')

这使数据库可以使用TranDateTime上的索引(有一个，对吗？)来进行选择。

Valeriy Kravchuk在有关使用此方法的MySQL网站上针对此问题的功能请求中。

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

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

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

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

我想你会发现

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

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

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

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

没有要添加的内容。

也许意思是类似计算(计算的)索引……但是到目前为止，我只在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都没有区别(我通常以这种格式使用它)。