引言:在以前,我总是习惯用 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 还能支持自动更新成当前最新时间,何乐而不为呢?