MySQL Integer与DateTime索引 [英] MySQL Integer vs DateTime index
问题描述
首先让我说我已经查看了许多类似的问题，但所有这些问题都涉及时间戳和 DateTime 没有索引的字段类型。至少这是我的理解。
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.
众所周知，DateTime有一些优势。把它们搁置一分钟，并假设表的引擎是 InnoDB ，其中 10万条记录，哪个查询执行速度更快当条件基于：
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:
- 带索引的日期时间
- int索引
换句话说，最好将日期和时间存储为 DateTime 或 int 中的UNIX时间戳？请记住，不需要使用任何内置的MySQL函数。
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.
更新
使用MySQL 5.1.41（64位）和1000万条记录进行测试，初始测试表明显着的速度差异有利于 int 。使用了两个表， tbl_dt ，其中 DateTime 和 tbl_int ， int 列。几个结果：
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)
我会按照 shantanuo 的建议在一个表格中发布另一个包含这两个字段的更新。
I'll post another update with both fields in one table as suggested by shantanuo.
更新＃2
众多服务器崩溃后的最终结果:) Int类型明显更快，无论运行什么查询，速度差异或多或少与上面的结果相同。
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.
奇怪观察到的事情是执行时间差不多相同当两个两种字段类型存储在同一个表中时。似乎MySQL足够聪明，可以在存储在DateTime和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.
推荐答案
我的直觉是说整数是总是更快。但是，情况似乎并非如此
My instinct would be to say that ints are always faster. However, this seems not to be the case
编辑添加：我意识到你正在使用InnoDB，而不是MyISAM，但在InnoDB案例中我没有发现任何与此相矛盾的内容。此外，同一作者进行了InnoDB测试
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
这篇关于MySQL Integer与DateTime索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
