MySQL存储时间类型选择的问题讲解

MySQL中存储时间通常会用datetime类型,但现在很多系统也用int存储unix时间戳,它们有什么区别?本人总结如下:

int

(1)4个字节存储,INT的长度是4个字节,存储空间上比datatime少,int索引存储空间也相对较小,排序和查询效率相对较高一点点

(2)可读性极差,无法直观的看到数据

TIMESTAMP

(1)4个字节储存

(2)值以UTC格式保存

(3)时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区。

(4)TIMESTAMP值不能早于1970或晚于2037

datetime

(1)8个字节储存

(2)与时区无关

(3)以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

随着Mysql性能越来越来高,个人觉得关于时间的存储方式,具体怎么存储看个人习惯和项目需求吧

分享两篇关于int vs timestamp vs datetime性能测试的文章

Myisam:MySQL DATETIME vs TIMESTAMP vs INT 测试仪

CREATE TABLE `test_datetime` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime` FIELDTYPE NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

机型配置

  • kip-locking
  • key_buffer = 128M
  • max_allowed_packet = 1M
  • table_cache = 512
  • sort_buffer_size = 2M
  • read_buffer_size = 2M
  • read_rnd_buffer_size = 8M
  • myisam_sort_buffer_size = 8M
  • thread_cache_size = 8
  • query_cache_type = 0
  • query_cache_size = 0
  • thread_concurrency = 4

测试

DATETIME   14111 14010        14369     130000000
TIMESTAMP  13888        13887        14122     90000000
INT        13270        12970        13496     90000000

执行mysql

mysql> select * from test_datetime into outfile ‘/tmp/test_datetime.sql';
Query OK, 10000000 rows affected (6.19 sec)

mysql> select * from test_timestamp into outfile ‘/tmp/test_timestamp.sql';
Query OK, 10000000 rows affected (8.75 sec)

mysql> select * from test_int into outfile ‘/tmp/test_int.sql';
Query OK, 10000000 rows affected (4.29 sec)

alter table test_datetime rename test_int;
alter table test_int add column datetimeint INT NOT NULL;
update test_int set datetimeint = UNIX_TIMESTAMP(datetime);
alter table test_int drop column datetime;
alter table test_int change column datetimeint datetime int not null;
select * from test_int into outfile ‘/tmp/test_int2.sql';
drop table test_int;

So now I have exactly the same timestamps from the DATETIME test, and it will be possible to reuse the originals for TIMESTAMP tests as well.

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql' into table test_datetime;
Query OK, 10000000 rows affected (41.52 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql' into table test_timestamp;
Query OK, 10000000 rows affected, 44 warnings (48.32 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 44

mysql> load data infile ‘/export/home/ntavares/test_int2.sql' into table test_int;
Query OK, 10000000 rows affected (37.73 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

As expected, since INT is simply stored as is while the others have to be recalculated. Notice how TIMESTAMP still performs worse, even though uses half of DATETIME storage size.

Let's check the performance of full table scan:

mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime < ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (3.93 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_timestamp WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime < ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (9.87 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > UNIX_TIMESTAMP('1970-01-01 01:30:00′) AND datetime < UNIX_TIMESTAMP('1970-01-01 01:35:00′);
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (15.12 sec)

Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to be without those recalculations: find the equivalents of those UNIX_TIMESTAMP() values, and use them instead:

mysql> select UNIX_TIMESTAMP('1970-01-01 01:30:00′) AS lower, UNIX_TIMESTAMP('1970-01-01 01:35:00′) AS bigger;
+——-+——–+
| lower | bigger |
+——-+——–+
| 1800 |  2100 |
+——-+——–+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime < 2100;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (1.94 sec)

Innodb:MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。如果你想了解更多相关内容请查看下面相关链接

(0)

相关推荐

  • 怎样正确创建MySQL索引的方法详解

    索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本.MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,所以大数据量建立索引是非常有必要的.MySQL提供了Explain,用于显示SQL执行的详细信息,可以进行索引的优化. 什么是索引? MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构.我们可以简单理解为:快速查找排好序的一种数据结构.Mysql索引主要有两种结构:B+Tree索引和Hash索引.我们平

  • MySQL中decimal类型用法的简单介绍

    MySQL中支持浮点数的类型有FLOAT.DOUBLE和DECIMAL类型,DECIMAL 类型不同于FLOAT和DOUBLE,DECIMAL 实际是以串存放的.DECIMAL 可能的最大取值范围与DOUBLE 一样,但是其有效的取值范围由M 和D 的值决定.如果改变M 而固定D,则其取值范围将随M 的变大而变大. 对于精度比较高的东西,比如money,建议使用decimal类型,不要考虑float,double, 因为他们容易产生误差,numeric和decimal同义,numeric将自动转

  • Mysql中SQL语句不使用索引的情况

    MySQL查询不使用索引汇总 众所周知,增加索引是提高查询速度的有效途径,但是很多时候,即使增加了索引,查询仍然不使用索引,这种情况严重影响性能,这里就简单总结几条MySQL不使用索引的情况 如果MySQL估计使用索引比全表扫描更慢,则不使用索引.例如,如果列key均匀分布在1和100之间,下面的查询使用索引就不是很好:select * from table_name where key>1 and key<90; 如果使用MEMORY/HEAP表,并且where条件中不使用"=&q

  • Mysql将一个表中的某一列数据复制到另一个表中某一列里的方法

    mysql复制表中的一列到另一个表中 有时候,我们需要复制某个字段一整列的数据到另外一个新的字段中,这很简单,SQL可以这么写: UPDATE tb_1 SET content_target = content_source; 大概写法如下: Update {your_table} set {source_field} = {object_field} WHERE cause 有Navicat等工具更好,可以直接选中一列数据,拷贝粘贴到你需要的列中.如果是同一个表那没什么问题,如果是新表,请保持

  • 通过MySQL慢查询优化MySQL性能的方法讲解

    随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo

  • MySQL使用命令创建、删除、查询索引的介绍

    MySQL数据库表可以创建.查看.重建和删除索引,索引可以提供查询速度.索引根据分类,分为普通索引和唯一索引:有新建索引.修改索引和删除.但是索引不是到处都可以创建,需要根据具体的条件.下面利用实例说明索引创建到销毁的过程,操作如下: 熟悉使用MySQL命令可以方便灵活地执行各种数据库操作:本文主要是对如何使用命令操作MySQL索引,包括创建索引.重建索引.查询索引.删除索引的操作.以下所列示例中的 `table_name` 表示数据表名,`index_name` 表示索引名,column li

  • Mysql主从数据库(Master/Slave)同步配置与常见错误

    随着访问量的增加,对于一些比较耗时的数据库读取操作,一般采用将写入与读取操作分开来缓解数据库的压力,数据库引擎一般采用Master/Slave架构.实现mysql服务器的主从配置,可以实现读写分离,另外在主数据库崩溃后可以从备用数据库中恢复数据以不至于网站中断访问.下面简单说下mysql主从服务器配置的过程. 首先需要在同一个局域网内的两台机器(当然也可以用一台机器虚拟两台机器出来),都安装上mysql服务. 主机A: 192.168.1.100 从机B: 192.168.1.101 可以有多台

  • Mysql Explain命令的使用与分析

    mysql explain命令用于显示mysql如何使用索引来处理select语句以及连接表.通过该命令可以分析出查询语句或是表结构的性能瓶颈,从而写出性能更好的SQL语句,通过 expalin 命令我们可以得到: 1. 表的读取顺序 2. 表的读取操作的操作类型 3. 哪些索引可以使用 4. 哪些索引被实际使用 5. 表之间的引用 6. 每张表有多少行被优化器查询 explain 的使用方法,在select语句前加上explain就可以了, 如: explain select * form c

  • Mysql中replace与replace into的用法讲解

    Mysql replace与replace into都是经常会用到的功能:replace其实是做了一次update操作,而不是先delete再insert:而replace into其实与insert into很相像,但对于replace into,假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除. replace是mysql 里面处理字符串比较常用的函数,可以替换字符串中的内容.类似的处理字符串的还有trim截取

  • 如何提高MySQL Limit查询性能的方法详解

    在MySQL数据库操作中,我们在做一些查询的时候总希望能避免数据库引擎做全表扫描,因为全表扫描时间长,而且其中大部分扫描对客户端而言是没有意义的.其实我们可以使用Limit关键字来避免全表扫描的情况,从而提高效率. 有个几千万条记录的表 on MySQL 5.0.x,现在要读出其中几十万万条左右的记录.常用方法,依次循环: select * from mytable where index_col = xxx limit offset, limit; 经验:如果没有blob/text字段,单行记

随机推荐