正文
用TIMESTAMP类型取代INT和DATETIME
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
时间在我们开发中应用非常普遍，大部分开发中我们将用Mysql的datetime格式来存储，但是对于经常用时间来排序或者查询的应用中，我们要将时间做成索引，这个就跟查询效率很有关系，但是很多程序员会用int来存时间戳，然后再进行处理
1、首先是我们分析datetime长度是8个字节，INT的长度是4个字节，存储空间上比datatime少。
2、int存储索引的空间也比datetime少，排序效率高，查询速度比较快。
3、方便计算，unix时间戳有点就是方便计算。
总结了一下timestamp和datetime的区别：
1、timestamp有范围限制1970 ~ 2037
2、timestamp支持default current_timestamp 来设置默认自动当前时间
3、timestamp支持on update current_timestamp 来设置更新时自动当前时间
4、timestamp时区相关，存储时以UTC时间保持，查询时转换为当前时区，
即如果在东8区的08:00:00分保存的数据，在东9区看到的是09:00:00
5、timestamp 4个字节存储（实际上就是int），datetime 8个字节
6、如果timestamp的值超出范围，mysql不会报错
7、如果是自动更新模式，手动修改数据导致timestamp字段更新
8、同时有两个timestamp字段默认值为current_timestamp会报错
9、如果mysql在maxdb模式下，timestamp等同于datetime，也就是说timestamp
受服务器模式影响
网上还有还一个TIMESTAMP 和INT比较的例子，timestamp效率比int不会差，而且timestamp有自动更新功能
现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。
mysql> CREATE TABLE `t` (
`d1` int(10) unsigned NOT NULL default '',
`d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
`d3` datetime NOT NULL,
KEY `d2` (`d2`),
KEY `d1` (`d1`),
KEY `d3` (`d3`)
);
时间戳
1199116800
表示
2008-01-01
这个时间点。然后运行存储过程，大概填充几十万条记录后，中止执行，因为上面的存储过程是个死循环，所以需要人工中止。
来看看到底有多少条记录了，以及索引情况：
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 924707 |
+----------+
mysql> analyze table t;
+--------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+-----------------------------+
| test.t | analyze | status | Table is already up to date |
+--------+---------+----------+-----------------------------+
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t | 1 | d2 | 1 | d2 | A | 924707 | NULL | NULL | | BTREE | |
| t | 1 | d1 | 1 | d1 | A | 924707 | NULL | NULL | | BTREE | |
| t | 1 | d3 | 1 | d3 | A | 924707 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2. 对比
2.1 只检索一条记录
mysql> explain select * from t where d1 = 1199579155;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d1 | d1 | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d2 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d2 | d2 | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d3 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d3 | d3 | 8 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
2.2 范围检索
mysql> explain select * from t where d1 >= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d1 | d1 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d2 | d2 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d3 | d3 | 8 | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d1 = 1199579155;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d1 | d1 | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d2 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d2 | d2 | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d3 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | d3 | d3 | 8 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
2.2 范围检索
mysql> explain select * from t where d1 >= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d1 | d1 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d2 | d2 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d3 | d3 | 8 | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d1 >= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d1 | d1 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d2 | d2 | 4 | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | range | d3 | d3 | 8 | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
小结：从上面的2次对比中可以看到，对 d1 或 d2 字段检索时的索引长度都是 4 ，因为 TIMESTAMP 实际上是 4字节 的 INT 值。因此，实际应用中，基本上完全可以采用 TIMESTAMP 来代替另外2种类型了，并且 TIMESTAMP 还能支持自动更新成当前最新时间，何乐而不为呢？
