时间的存储，比较TIMESTAMP类型、INT和DATETIME

2012-8-8 11:19| 发布者: cryinglily| 查看: 545| 评论: 0|原作者: luinstein

摘要: 在以前，我总是习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，认为这样做从索引，比较等角度来讲，都会比较高效。现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。 ...

在以前，我总是习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，认为这样做从索引，比较等角度来讲，都会比较高效。现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。

1. 准备

创建一个测试表：

mysql> CREATE TABLE `t` (
`d1` int(10) unsigned NOT NULL default '0',
`d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
`d3` datetime NOT NULL,
KEY `d2` (`d2`),
KEY `d1` (`d1`),
KEY `d3` (`d3`)
);

然后创建一个存储过程填充数据：

mysql> DELIMITER //
CREATE PROCEDURE INS_T()
BEGIN
SET @i=1;
WHILE 0<1
DO
SET @i=@i+1;
INSERT INTO i VALUES (1199116800+@i, FROM_UNIXTIME(1199116800+@i), FROM_UNIXTIME(1199116800+@i));
END WHILE;
END;//
DELIMITER ;

时间戳 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 |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

小结：从上面的2次对比中可以看到，对 d1 或 d2 字段检索时的索引长度都是 4，因为 TIMESTAMP 实际上是 4字节的 INT 值。因此，实际应用中，基本上完全可以采用 TIMESTAMP 来代替另外2种类型了，并且 TIMESTAMP 还能支持自动更新成当前最新时间，何乐而不为呢？

from:http://imysql.cn/2008_07_17_timestamp_vs_int_and_datetime

鲜花

握手

雷人

路过

鸡蛋

收藏分享邀请

		自动登录	找回密码
密码			立即注册

时间的存储，比较TIMESTAMP类型、INT和DATETIME

相关分类