MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较

最新推荐文章于 2022-08-31 21:44:25 发布
最新推荐文章于 2022-08-31 21:44:25 发布
阅读量2.1w 收藏 20
点赞数 5
正 文：

   在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。


MYSQL版本号：5.5.19


建立表：

CREATE TABLE IF NOT EXISTS `datetime_test` (
  `id` int(11) NOT NULL,
  `d_int` int(11) NOT NULL DEFAULT '0',
  `d_timestamp` timestamp NULL DEFAULT NULL,
  `d_datetime` datetime DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;
ALTER TABLE `datetime_test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

插入100万条测试数据：

<?php header( 'Content-Type: text/html; charset=UTF-8' );
set_time_limit(300); //最大执行时间这里设置300秒

//连接数据库
$pdo = new PDO("mysql:host=localhost;dbname=test","root","123"); 

for ($i = 1; $i <= 1000000; $i++) { 
	$d_int=$i;
	$pdo->exec("insert into datetime_test(d_int,d_timestamp,d_datetime) 
		values($d_int,FROM_UNIXTIME($d_int),FROM_UNIXTIME($d_int))");
}

取中间的20万条做查询测试：

SELECT FROM_UNIXTIME(400000), FROM_UNIXTIME(600000)
1970-01-05 23:06:40, 1970-01-08 06:40:00


第一种情况，MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
查询花费 0.0780 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查询花费 0.0780 秒

效率不错。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
查询花费 0.4368 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
查询花费 0.0780 秒

对于timestamp类型，使用UNIX_TIMESTAMP内置函数查询效率很高，几乎和int相当；直接和日期比较效率低。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
查询花费 0.1370 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
查询花费 0.7498 秒

对于datetime类型，使用UNIX_TIMESTAMP内置函数查询效率很低，不建议；直接和日期比较，效率还行。


第二种情况，MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都有索引


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
查询花费 0.3900 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查询花费 0.3824 秒

对于int类型，有索引的效率反而低了，飘易的猜测是由于设计的表结构问题，多了索引，反倒多了一个索引查找。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
查询花费 0.5696 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
查询花费 0.0780 秒

对于timestamp类型，有没有索引貌似区别不大。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
查询花费 0.4508 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
查询花费 0.7614 秒

对于datetime类型，有索引反而效率低了。


第三种情况，InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
查询花费 0.3198 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查询花费 0.3092 秒

InnoDB引擎的查询效率明细比MyISAM引擎的低，低3倍+。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
查询花费 0.7092 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
查询花费 0.3160 秒

对于timestamp类型，使用UNIX_TIMESTAMP内置函数查询效率同样高出直接和日期比较。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
查询花费 0.3834 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
查询花费 0.9794 秒

对于datetime类型，直接和日期比较，效率高于UNIX_TIMESTAMP内置函数查询。


第四种情况，InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都有索引


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
查询花费 0.0522 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查询花费 0.0624 秒

InnoDB引擎有了索引之后，性能较MyISAM有大幅提高。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
查询花费 0.1776 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
查询花费 0.2944 秒

对于timestamp类型，有了索引，反倒不建议使用MYSQL内置函数UNIX_TIMESTAMP查询了。


SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
查询花费 0.0820 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
查询花费 0.9994 秒

对于datetime类型，同样有了索引，反倒不建议使用MYSQL内置函数UNIX_TIMESTAMP查询了。


【总结】：

对于MyISAM引擎，不建立索引的情况下（推荐），效率从高到低：int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。


对于MyISAM引擎，建立索引的情况下，效率从高到低： UNIX_TIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。


对于InnoDB引擎，没有索引的情况下(不建议)，效率从高到低：int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(datetime)。


对于InnoDB引擎，建立索引的情况下，效率从高到低：int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。


一句话，对于MyISAM引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；对于InnoDB引擎，建立索引，采用 int 或 datetime直接时间比较。

忞于醇
关注
  • 5
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
如何选择合适的MySQL日期时间类型来存储你的时间
12-14
构建数据库写程序避免不了使用日期和时间，对于数据库来说，有多种日期时间字段可供选择，如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp。 不仅新手，包括一些有经验的程序员还是比较迷茫，究竟我该用哪种类型来存储日期时间呢？ 那我们就一步一步来分析他们的特点，这样我们根据自己的需求选择合适的字段类型来存储 (优点和缺点是比较出来的  , 跟父母从小喜欢拿邻居小孩子跟自己比一样的) datetime 和 timestamp datetime 更像日历上面的时间和你手表的时间的结合，就是指具体某个时间。 timestamp 更适合来记录时间，
MySQL数据库：表的字段类型.pptx
05-29
数据库和表的创建与管理 表的字段类型 课程目标 掌握 —— 不同数据类型的选择。 理解 —— MySQL支持的数据类型； 表的字段类型 MySQL支持的类型 MySQL支持多种类型，大致可以分为三类： 数值类型 日期和时间类型 字符串(字符)类型。 数值类型 表的字段类型 MySQL支持所有标准SQL数值数据类型。 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。 作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。 表的字段类型 数值类型 表的字段类型 日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使
Mysql数据库设计.pdf
01-08
Mysql数据库设计 数据库设计 MYSQL数据库设计 串转义序列 \0 NUL(ASCLL 0) \' \" \b 退格 \n 新⾏ \r 回车 \t 制表符 \\ 反斜杠 数值列类型 TINYINT 1字节 ⾮常⼩的整数 有符号值：-128⾄127 ⽆符号值：0⾄255 SMALLINT 2字节 较⼩的整数 有符号值：-32768 MEDIUMINT 3字节 中等⼤⼩的整数 有符号值： INT 4字节 标准整数 有符号值： BIGINT 8字节 ⼤整数 有符号值： FLOAT 4字节 单精度浮点数 有符号值： DOUBLE 8字节 双精度浮点数 有符号值： DECIMAL M字节 ⼀个串的浮点数 有符号值： CHAR VARCHAR TINYBLOB BLOB MEDIUMBLOB LONGBLOB TINYTEXT TEXT MEDIUMTEXT LONGTEXT ENUM SET ⽇期与时间类型格式 DATE "YYYY-MM-DD" TIME "hh:mm:ss" DATETIME "YYYY-MM-DD hh:mm:ss" TIMESTAMP "YYYYMMDDhhmmss" YEAR "YYYY" 数值列类型的取值范围 数值列的列属性： AUTO_INCREMENT 创建⼀个唯⼀标志符号或许顺序值 create table a ( I int auto_increment not null primary key); UNSIGNED 禁⽌此属性存在负值 create table test(num tingint , num2 tingint unsigned); NULL and NOT NULL 缺省为NULL create talbe test (num int, num2 int default 1, num3 int default null) 运算符 = 等于 <>或者！= 不等于 <=> NULL安全的等于(NULL-safe) < ⼩于 > ⼤于 <= ⼩于等于 >= ⼤于等于 BETWEEN 存在与指定范围 IN 存在于指点集合 IS NULL 为NULL IS NOT NULL 不为NULL LIKE 通配符匹配 REGEXP或者RLIKE 正则表达式匹配 %（百分号） a%b表⽰以a开头b结尾的任意长度的字符串 _（下横线） a_b表⽰以a开头b结尾的长度为3的任意字符， PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT DEFAULT default_value PRIMARY KEY 与UNIQUE 数据库操作： show database; show tables; use dataname; create database 数据库名; create table test( id int primary key auto_increment, title varchar(16) ); describe 表名; desc 表名; drop talbe tablename; alter talbe 表名 action add 列名 add primary key alter 列名 set default drop 列名 drop primary key drop index index_name rename as 新表名 insert into 表名(id,name,...) valuse(1,liqingbo); update 表名 set 列名=数据值 where ⼦句; delet from 表名 where ⼦句; select group by having order by limit desc asc count sum avg max min resource mysql_connect(host,root,password); mysql_close(); bool mysql_select_db(tablename[,],连接返回变量); resource mysql_query(SQL语句) resource mysql_db_query(tablename[,]); array mysql_fetch_row(); array mysql_fetch_array(); MYSQL_ASSOC: MYSQL_NUM: MYSQL_BOTH: mysql_num_fields(); 取得结果集中字段的数⽬ mysql_num_rows(); 取得结果集中⾏的数⽬ mysql_result(); mysql_free_result(); mysql_close(); mysql_pconnect; mysql_create_db; m
解析MySQL中存储时间日期类型的选择问题
12-16
一般应用中，我们用timestamp,datetime,int类型来存储时间格式： int（对应javaBean中的Integer或int） 1. 占用4个字节 2. 建立索引之后，查询速度快 3. 条件范围搜索可以使用使用between 4. 不能使用mysql提供的时间函数 结论：适合需要进行大量时间范围查询的数据表 datetime（javaBean中用Date类型） 1. 占用8个字节 2. 允许为空值，可以自定义值，系统不会自动修改其值。 3. 实际格式储存（Just stores what you have stored and retrieves the sam
144790个去重/未去重的按连续/不连续ID排序的单词、注音、释义、例句表（MySQL，可直接加载导入数据库，注音单独成字段）
05-29
这个压缩文件包含了2个.sql文件（还有1个sql命令参考），由MySQL Workbench生成，包含了144790个单词的释义、注音（少部分单词无注音）与例句，均来自必应在线词典。MySQL表的字段如下所示： Field Type Null Key Default Extra id int unsigned NO PRI NULL auto_increment word varchar(50) NO NULL pronunciation varchar(100) YES NULL meaning varchar(500) NO NULL EXAMPLE_SENTENCES varchar(2500) YES NULL create_time datetime YES CURRENT_TIMESTAMP DEFAULT_GENERATED update_time datetime YES CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
mysql 时间 >=_MySQL 日期与时间的处理
weixin_35693911的博客
01-27 673
1.查询当前日期时间：函数有now(),localtime(),current_timestamp(),sysdate()。mysql> select now(),localtime(),current_timestamp(),sysdate();+---------------------+---------------------+---------------------+------...
MySQL 中的日期时间
彳亍
12-19 519
1. now返回当前的日期和时间。MariaDB [test]&amp;amp;gt; select now(); +---------------------+ | now() | +---------------------+ | 2016-11-20 21:46:56 | +---------------------+2. curdate返回当前的日期。curdate()是current
Mysql日期和时间函数大全
放飞我心
01-08 972
原文 对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描述见7.3.6 日期和时间类型。  这里是一个使用日期函数的例子。下面的查询选择了所有记录，其date_col的值是在最后30天以内：  mysql> SELECT something FROM table  WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) select DAY
mysql的日期存储字段比较int,datetime,timestamp区别
weixin_30268071的博客
10-23 306
1、首先是我们分析datetime长度是8个字节，INT的长度是4个字节，存储空间上比datatime少。 2、int存储索引的空间也比datetime少，排序效率高，查询速度比较快。 3、方便计算，unix时间戳有点就是方便计算。 总结了一下timestamp和datetime的区别： 1、timestamp有范围限制1970~2037 2、timestamp支持defaultc...
MySQL中的时间类型
热门推荐
Valineliu
08-31 1万+
时间是一类重要的数据，MySQL中有多种关于时间的类型可以选择。这篇文章主要介绍MySQL中的时间类型，主要参考MySQL文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html。
MySQL中timestamp、datetime和int类型的区别与优劣
riemann_的博客
09-10 2152
一、三者的区别 1、int 占用4个字节 建立索引之后，查询速度快 条件范围搜索可以使用使用between 不能使用mysql提供的时间函数 结论：适合需要进行大量时间范围查询的数据表 2、datetime 占用8个字节 允许为空值，可以自定义值，系统不会自动修改其值 实际格式储存（Just stores what you have stored and retrieves the same...
mysql字段类型与查询效率_简单了解mysql存储字段类型查询效率
weixin_28782823的博客
02-08 867
检索性能从快到慢的是(此处是听人说的)：第一：tinyint，smallint，mediumint，int，bigint第二：char，varchar第三：NULL解释(转载)：整数类型1、TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，分别用8,16,24,32,64存2、整数都有UNSIGNED可选属性 (拿tinyint字段来举例，unsigned后，字段的取值范...
MySQL 尽量避免使用 TIMESTAMP！！
Java技术栈，分享最主流的Java技术
10-27 392
点击关注公众号，Java干货及时送达MySQL 中常见的时间类型有三种DATE, DATETIME和 TIMESTAMP，其中DATE类型用于表示日期，但是不会包含时间，格式为YYYY-M...
mysql date 索引性能_【转】MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
weixin_33851300的博客
02-17 938
对于MyISAM引擎，不建立索引的情况下(推荐)，效率从高到低：int > UNIX_TIMESTAMP(timestamp) > datetime(直接和时间比较)>timestamp(直接和时间比较)>UNIX_TIMESTAMP(datetime) 。对于MyISAM引擎，建立索引的情况下，效率从高到低： UNIX_TIMESTAMP(timestamp) > ...
JAVA获取时间戳，哪个更快<转>
yongjian_luo的专栏
10-21 838
目前获取毫秒值大概有下面三种方法   Java代码   //方法 一   System.currentTimeMillis();    //方法 二   Calendar.getInstance().getTimeInMillis();   //方法 三   new Date().getTime();    最近做监控系统，发现代码中有前两种方法，
mysql按时间查询的优化_Mysql根据时间查询日期的优化技巧
weixin_29006187的博客
02-28 2751
Mysql根据时间查询日期的优化技巧例如查询昨日新注册用户，写法有如下两种：EXPLAINselect * from chess_user u where DATE_FORMAT(u.register_time,'%Y-%m-%d')='2018-01-25';EXPLAINselect * from chess_user u where u.register_time BETWEEN '2018...
【mySQL】MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
bandaoyu的note
04-12 1220
目录 MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较 mysql的日期存储字段比较int,datetime,timestamp区别 MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较 文章：https://www.jianshu.com/p/27c807ed6974 文章2：https://blog.csdn.net/ad...
mysql大于小于能用between_MySQL中大于小于，IN，OR，BETWEEN性能比较
weixin_33930436的博客
01-19 1993
在MySQL中经常会有朋友问关于大于小于，IN，OR，BETWEEN，哪个性能更好？我想有很多朋友会用它们但确实不知道大于小于，IN，OR，BETWEEN性能哪个更好吧，下面总结一下关于大于小于，IN，OR，BETWEEN性能。这里的查询条件有四种：>=和<=；between；or；in这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。1 准备环境mysql&...
datetime 索引_date类型和varchar类型在查询索引效率上比较
weixin_39964528的博客
11-29 1945
突然有开发人员问我，使用 varchar 来存储日期，索引查询效率上的区别。oracle 中date 类型固定使用7个字节mysql 中 date 类型使用3个字节，datetime使用8个字节(DATETIME的打包效率更高，对非分数部分需要5个而不是8个字节，并且所有三个部分都有一个小数部分，需要0到3个字节，具体取决于存储值的小数秒精度)可以看出使用日期格式存储，比直接使用字符串存储使用的字...
mysql数据库字段
最新发布
07-28
4. 日期和时间类型（Date and Time Types）：例如DATE、TIME、DATETIME、TIMESTAMP等，用于存储日期和时间数据。 5. 布尔类型（Boolean Type）：例如BOOL或BOOLEAN，用于存储逻辑值（真或假）。 6. 枚举类型...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值