引言：在以前，我总是习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，认为这样做从索引，比较等角度来讲，都会比较高效。现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。
1. 准备
创建一个测试表：
SQL代码
- 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`)
- );
然后创建一个存储过程填充数据：
SQL代码
- 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 这个时间点。然后运行存储过程，大概填充几十万条记录后，中止执行，因为上面的存储过程是个死循环，所以需要人工中止。
来看看到底有多少条记录了，以及索引情况：
SQL代码
- 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 只检索一条记录
SQL代码
- 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 范围检索
SQL代码
- 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 还能支持自动更新成当前最新时间，何乐而不为呢？
－－END－－
依稀记得，timestamp类型好象是从MYSQL4.1以后才支持的吧，很多时间为了兼容都采用了无符号的INT类型，效率相差也并不是很大，如果是直接在高版本上操作，还是和作者学习一下吧。黑黑
#1