MySQL 整数与日期时间索引

首先让我说我看过许多类似的问题，但所有这些都与 Timestamp 和 DateTime 字段类型相关，没有索引.至少这是我的理解.

众所周知，DateTime 有一定的优势.把它们放在一边，假设表的引擎是 InnoDB 和 10+ 百万条记录，当条件基于以下时，查询会执行得更快:

<块引用>

1. 带索引的日期时间
2. int 与索引

换句话说，将日期和时间存储为 DateTime 或 int 中的 UNIX 时间戳更好?请记住，不需要使用任何内置的 MySQL 函数.

更新

使用 MySQL 5.1.41(64 位)和 1000 万条记录进行测试，初始测试显示出明显的速度差异，有利于 int.使用了两个表，带有 DateTime 的 tbl_dt 和带有 int 列的 tbl_int.结果很少:

SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt`;+-----------+|COUNT(*) |+-----------+|10000000 |+-----------+1 排(2 分 10.27 秒)SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int`;+-----------+|计数(*) |+-----------+|10000000 |+-----------+1 排(25.02 秒)SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt` WHERE `created` BETWEEN '2009-01-30' AND '2009-12-30';+-----------+|COUNT(*) |+-----------+|835663 |+-----------+1 排(8.41 秒)SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int` WHERE `created` BETWEEN 1233270000 AND 1262127600;+-----------+|COUNT(*) |+-----------+|835663 |+-----------+1 排(1.56 秒)

我将按照 shantanuo 的建议发布另一个更新，将两个字段放在一个表中.

更新 #2

多次服务器崩溃后的最终结果:) Int 类型明显更快，无论运行什么查询，速度差异与上述结果大致相同.

观察到的奇怪"事情是，当两个字段类型存储在同一个表中时，执行时间或多或少相同.似乎 MySQL 足够聪明，可以确定存储在 DateTime 和 int 中的值何时相同.尚未找到有关该主题的任何文档，因此只是观察.

解决方案

我的直觉是整数总是更快.然而，情况似乎并非如此

http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/

编辑添加:我意识到您使用的是 InnoDB，而不是 MyISAM，但我在 InnoDB 案例中没有发现任何与此矛盾的内容.另外，同一个作者做了一个 InnoDB 测试

http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-innodb/

Let me start by saying I have looked at many similar questions asked, but all of them relate to Timestamp and DateTime field type without indexing. At least that is my understanding.

As we all know, there are certain advantages when it comes to DateTime. Putting them aside for a minute, and assuming table's engine is InnoDB with 10+ million records, which query would perform faster when criteria is based on:

1. DateTime with index
2. int with index

In other words, it is better to store date and time as DateTime or UNIX timestamp in int? Keep in mind there is no need for any built-in MySQL functions to be used.

Update

Tested with MySQL 5.1.41 (64bit) and 10 million records, initial testing showed significant speed difference in favour of int. Two tables were used, tbl_dt with DateTime and tbl_int with int column. Few results:

SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt`;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 10.27 sec)

SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int`;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (25.02 sec)

SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt` WHERE `created` BETWEEN '2009-01-30' AND '2009-12-30';
+----------+
| COUNT(*) |
+----------+
|   835663 |
+----------+
1 row in set (8.41 sec)

SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int` WHERE `created` BETWEEN 1233270000 AND 1262127600;
+----------+
| COUNT(*) |
+----------+
|   835663 |
+----------+
1 row in set (1.56 sec)

I'll post another update with both fields in one table as suggested by shantanuo.

Update #2

Final results after numerous server crashes :) Int type is significantly faster, no matter what query was run, the speed difference was more or less the same as results above.

"Strange" thing observed was execution time was more or less the same when two both field types are stored in the same table. It seems MySQL is smart enough to figure out when the values are the same when stored in both DateTime and int. Haven't found any documentation on the subject, therefore is just an observation.

解决方案

My instinct would be to say that ints are always faster. However, this seems not to be the case

http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/

Edited to add: I realize that you're using InnoDB, rather than MyISAM, but I haven't found anything to contradict this in the InnoDB case. Also, the same author did an InnoDB test

http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-innodb/