在MySQL中索引datetime字段是个好主意吗？

Question

我正在设计一个大型数据库。在我的应用程序中，我将会有很多行数据，例如我目前有一个包含400万记录的表格。大多数查询都使用datetime子句来选择数据。在mysql数据库中对datetime字段建立索引是一个好主意吗？

Select field1, field2,.....,field15
from table where field 20 between now() and now + 30 days 

我希望保持我的数据库运行良好，查询顺畅。

另外，您认为我应该拥有什么样的想法来创建一个高效的数据库呢？

- Jaylen

---

field 20 是什么？ - AlikElzin-kilaka

"BETWEEN"是“包含”的意思。也就是说，它包括范围结束时的午夜或当天。（在NOW()的情况下，会多包括一秒钟。） - undefined

Answer 1

MySQL建议使用索引的原因很多，包括在条件之间消除行：http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html 如果您经常在查询中使用datetime列作为条件，那么它是一个非常适合创建索引的候选列。如果您的唯一条件是BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)，并且在条件中没有其他索引，MySQL可能需要在一些查询中进行全表扫描。我不确定在30天内会生成多少行数据，但只要它少于总行数的1/3（有人说是20%），使用该列的索引会更高效。

关于创建高效数据库的问题非常广泛。我建议确保数据库已经规范化，并为所有适当的列创建索引（即用于连接和where子句的列）。

- Explosion Pills

---

谢谢您的解释，非常有帮助。我相信我会对它进行更多过滤。我只是想确保索引日期时间字段是否是一个好主意，因为我们可能会有重复的日期时间。但是您的回答已经解释清楚了 :) 谢谢 - Jaylen

“+1”是一个用于索引策略中的好规则，特别适用于连接和筛选条件所使用的列。现在想想很明显，但之前我没有想到。 - GWed

但是，如果您使用日期范围查询数据，例如从“2017-01-01 11:20”到“2018-01-03 12:12”的数据范围，即使我对datetime列进行了索引，也不会使SELECT查询更快...只有在使用equal操作时，索引才能使查询更快。我的理解正确吗？ - user3595632

如果使用像DAY(datetime)或HOUR(datetime)这样的时间函数查询日期时间字段，索引是否有帮助或妨碍？ - cronoklee

@cronoklee - 不行。函数使条件不可搜索。 - undefined

Answer 2

这里的作者进行了测试，显示整数UNIX时间戳比DateTime更好。请注意，他使用的是MySQL。但我觉得，无论你使用什么数据库引擎，比较整数都比比较日期稍微快一些，因此int索引比DateTime索引更好。假设T1为比较两个日期所需的时间，T2为比较两个整数所需的时间。根据索引字段搜索大约需要O(log(rows))的时间，因为索引基于某些平衡树——不同的DB引擎可能不同，但无论如何，Log(rows)都是一个常见的估计值。（如果您不使用基于位掩码或r-tree的索引）。因此，如果您经常执行查询，则差异为(T2-T1)*Log（rows）可能会起到作用。

- user2104560

---

谢谢。我曾经考虑过那个选项，但不知道如何处理。我相信你是对的，整数总是更快的。 - Jaylen

更好吗？我怀疑 Unix 时间戳在所有情况下都更好。是的，存储整数通常比存储字符串更快，但是 MySQL 所公开的所有 DateTime 函数呢？自己实现这些函数将会对性能或功能产生负面影响。 - Greg

我在一款传统的 Web 应用中工作，开发人员坚持使用Unix时间戳来处理数据库中的所有内容。由于这个原因，在处理日期方面已经让我头痛数年了，所以我花费了很多时间将尽可能多的内容迁移到 datetime 或 timestamp SQL 字段上。我相信 Unix 时间戳在某些情况下可能很有用，但一般情况下最好避免使用它们。 - BadHorsie

是的，MySQL使用5字节表示法（分离年、月等）存储日期，有点奇怪。 - Leonardo Herrera

TIMESTAMP、DATETIME、INT 和 BIGINT 之间的性能差异不值得担心。另一方面，要小心夏令时（daylight saving stupidity）及其额外或重复的小时。 - undefined

Answer 3

我知道这个问题早就被问过了，但我刚刚找到了解决方案。 我给一个日期时间列添加了索引。 在排序日期时间后调用最后的600条记录时，查询时间从1.6秒缩短到了0.0028秒。我觉得这是一次胜利。

ALTER TABLE `database`.`table` 
ADD INDEX `name_of_index`   (`datetime_field_from_table`);

- vr_driver