[MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME-阿里云开发者社区

开发者社区> 数据库> 正文

[MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME

简介:
引言:在以前,我总是习惯用 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 还能支持自动更新成当前最新时间,何乐而不为呢?
作/译者:叶金荣(Email: email.gif),来源:http://imysql.cn,转载请注明作/译者和出处,并且不能用于商业用途,违者必究。
引言:在以前,我总是习惯用 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 还能支持自动更新成当前最新时间,何乐而不为呢?


本文转自叶金荣51CTO博客,原文链接:http://blog.51cto.com/imysql/308818,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章