MySQL 整数与日期时间索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4594229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Integer vs DateTime index
提问by David Kurid?a
Let me start by saying I have looked at many similar questions asked, but all of them relate to Timestampand DateTimefield type without indexing. At least that is my understanding.
让我先说我已经看过很多类似的问题问开始，但它们都涉及到Timestamp，并DateTime没有索引字段类型。至少这是我的理解。
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 InnoDBwith 10+ million records, which query would perform faster when criteria is based on:
众所周知，DateTime 有一定的优势。将它们放在一边一分钟，并假设表的引擎是InnoDBwith 10+ million records，当条件基于以下条件时，哪个查询执行得更快：
- DateTime with index
- int with index
- 带索引的日期时间
- 带索引的整数
In other words, it is better to store date and time as DateTimeor UNIX timestamp in int? Keep in mind there is no need for any built-in MySQL functions to be used.
换句话说，最好将日期和时间存储为?DateTime或 UNIX 时间戳int？请记住，不需要使用任何内置的 MySQL 函数。
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_dtwith DateTimeand tbl_intwith intcolumn. Few results:
使用 MySQL 5.1.41（64 位）和 1000 万条记录进行测试，初始测试显示速度差异显着，有利于int. 两个表中使用，tbl_dt与DateTime和tbl_int带int柱。结果很少：
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.
我将按照shantanuo 的建议发布另一个更新，其中包含一个表中的两个字段。
Update #2
更新 #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.
无数服务器崩溃后的最终结果:) Int 类型明显更快，无论运行什么查询，速度差异与上述结果大致相同。
"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.
观察到的“奇怪”事情是，当两个字段类型存储在同一个表中时，执行时间或多或少相同。似乎 MySQL 足够聪明，可以确定存储在 DateTime 和 int 中的值何时相同。尚未找到有关该主题的任何文档，因此只是观察。
采纳答案by coolgeek
My instinct would be to say that ints are always faster. However, this seems not to be the case
我的直觉是说整数总是更快。然而，情况似乎并非如此
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
编辑添加：我意识到您使用的是 InnoDB，而不是 MyISAM，但在 InnoDB 案例中我没有发现任何与此矛盾的内容。另外，同一个作者做了一个 InnoDB 测试
回答by Bexol
I see that in the test mentioned in the above answer, the author basically proves it that when the UNIX timeis calculated in advance, INTwins.
回答by Yuseferi
it depends on your application, as you can see in an awesome comparison and benchmark of DATETIME , TIMESTAMP and INT type in Mysql server in MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.you can see in some situation INT has better perfomance than other and in some cases DATETIME has better performance. and It completely depends on your application
这取决于您的应用程序，正如您在MySQL 日期格式的Mysql 服务器中 DATETIME、TIMESTAMP 和 INT 类型的出色比较和基准测试中所看到的 ：您应该使用什么数据类型？我们比较日期时间、时间戳和 INT。您可以看到在某些情况下 INT 比其他情况具有更好的性能，在某些情况下 DATETIME 具有更好的性能。而这完全取决于你的应用
