手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜Qcloud , 注册 | 登陆

用TIMESTAMP类型取代INT和DATETIME[转]

首页 > DataBase >

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

 

1. 准备

创建一个测试表:

SQL代码
  1. mysql> CREATE TABLE `t` (  
  2. `d1` int(10) unsigned NOT NULL default '0',  
  3. `d2` timestamp NOT NULL default CURRENT_TIMESTAMP,  
  4. `d3` datetime NOT NULL,  
  5. KEY `d2` (`d2`),  
  6. KEY `d1` (`d1`),  
  7. KEY `d3` (`d3`)  
  8. );  

 

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

SQL代码
  1. mysql> DELIMITER //  
  2. CREATE PROCEDURE INS_T()  
  3. BEGIN  
  4. SET @i=1;  
  5. WHILE 0<1  
  6. DO  
  7. SET @i=@i+1;  
  8. INSERT INTO i VALUES (1199116800+@i, FROM_UNIXTIME(1199116800+@i), FROM_UNIXTIME(1199116800+@i));  
  9. END WHILE;  
  10. END;//  
  11. DELIMITER ;  

 

时间戳 1199116800 表示 2008-01-01 这个时间点。然后运行存储过程,大概填充几十万条记录后,中止执行,因为上面的存储过程是个死循环,所以需要人工中止。
来看看到底有多少条记录了,以及索引情况:

SQL代码
  1. mysql> select count(*) from t;  
  2. +----------+  
  3. count(*) |  
  4. +----------+  
  5. |   924707 |  
  6. +----------+  
  7. mysql> analyze table t;  
  8. +--------+---------+----------+-----------------------------+  
  9. Table  | Op      | Msg_type | Msg_text                    |  
  10. +--------+---------+----------+-----------------------------+  
  11. | test.t | analyze | status   | Table is already up to date |  
  12. +--------+---------+----------+-----------------------------+  
  13. mysql> show index from t;  
  14. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
  15. Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
  16. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
  17. | t     |          1 | d2       |            1 | d2          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |  
  18. | t     |          1 | d1       |            1 | d1          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |  
  19. | t     |          1 | d3       |            1 | d3          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |  
  20. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
  21.   
  22.    

 

2. 对比

2.1 只检索一条记录

SQL代码
  1. mysql> explain select * from t where d1 = 1199579155;  
  2. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |  
  4. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  5. |  1 | SIMPLE      | t     | ref  | d1            | d1   | 4       | const |    1 |       |  
  6. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  7. mysql> explain select * from t where d2 = '2008-01-06 08:25:55';  
  8. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  9. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |  
  10. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  11. |  1 | SIMPLE      | t     | ref  | d2            | d2   | 4       | const |    1 |       |  
  12. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  13. mysql> explain select * from t where d3 = '2008-01-06 08:25:55';  
  14. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  15. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |  
  16. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  17. |  1 | SIMPLE      | t     | ref  | d3            | d3   | 8       | const |    1 |       |  
  18. +----+-------------+-------+------+---------------+------+---------+-------+------+-------+  
  19.   
  20.    

 

2.2 范围检索

SQL代码
  1. mysql> explain select * from t where d1 >= 1199894400;  
  2. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  3. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |  
  4. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  5. |  1 | SIMPLE      | t     | range | d1            | d1   | 4       | NULL | 121961 | Using where |  
  6. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  7. mysql> explain select * from t where d2 >= from_unixtime(1199894400);  
  8. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  9. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |  
  10. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  11. |  1 | SIMPLE      | t     | range | d2            | d2   | 4       | NULL | 121961 | Using where |  
  12. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  13. mysql> explain select * from t where d3 >= from_unixtime(1199894400);  
  14. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  15. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |  
  16. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  17. |  1 | SIMPLE      | t     | range | d3            | d3   | 8       | NULL | 120625 | Using where |  
  18. +----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+  
  19.   
  20.    

 

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

 

--END--

依稀记得,timestamp类型好象是从MYSQL4.1以后才支持的吧,很多时间为了兼容都采用了无符号的INT类型,效率相差也并不是很大,如果是直接在高版本上操作,还是和作者学习一下吧。黑黑




本站采用创作共享版权协议, 要求署名、非商业和保持一致. 本站欢迎任何非商业应用的转载, 但须注明出自"膘叔", 保留原始链接, 此外还必须标注原文标题和链接.

Tags: mysql, 优化

« 上一篇 | 下一篇 »

只显示10条记录相关文章

1条记录访客评论

再次来膘叔这学习了

Post by Cherry on 2009, October 28, 2:28 PM 引用此文发表评论 #1


发表评论

评论内容 (必填):