mysql 时间字段性能比较

最新推荐文章于 2024-08-06 04:26:59 发布
最新推荐文章于 2024-08-06 04:26:59 发布
阅读量1w 收藏 3
点赞数
分类专栏： mysql

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

正 文：

在数据库设计的时候，我们经常会需要设计时间字段，在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直接时间比较。
洪武
关注
专栏目录
比较详细的MySQL字段类型说明
12-15
在设计数据库表结构时，选择合适的字段类型至关重要，因为它直接影响到数据的存储效率、查询性能以及数据的正确性。理解这些基本的字段类型及其特性，可以帮助开发者更好地设计数据库，满足各种业务需求。
MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
adsadadaddadasda的博客
12-29 2万+
正 文：    在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。 MYSQL版本号：5.5.19 建立表： CREATE TABLE IF N
mysql 时间 >=_MySQL 日期与时间的处理
weixin_35693911的博客
01-27 748
1.查询当前日期时间：函数有now(),localtime(),current_timestamp(),sysdate()。mysql> select now(),localtime(),current_timestamp(),sysdate();+---------------------+---------------------+---------------------+------...
mysql建表字段是时间
最新发布
weixin_36901065的博客
08-06 54
我整理的一些关于【MySQL,SQL,数据库设计】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://edu.51cto.com/surl=QDW3g3深入理解MySQL中的时间字段 在数据库设计中，时间字段是一项极其重要的组成部分，特别是在涉及记录事件、记录创建时间或更新数据的情况下。在MySQL中，...
MYSQL性能调优07_MySQL数据类型选择、数值类型、日期和时间、字符串
所得皆惊喜
08-11 585
文章目录①. MySQL数据类型选择②. 数值类型②. 日期和时间③. 字符串 ①. MySQL数据类型选择 ①. 在MySQL中,选择正确的数据类型,对于性能至关重要。一般应该遵循下面两步: 确定合适的大类型:数字、字符串、时间、二进制； 确定具体的类型:有无符号、取值范围、变长定长等。 ②. 在MySQL数据类型设置方面,尽量用更小的数据类型,因为它们通常有更好的性能,花费更少的硬件资源。并且,尽量把字段定义为NOT NULL,避免使用NULL。 ②. 数值类型 ①. 如果整形数据没有负
MySQL 中的日期时间
彳亍
12-19 570
1. now返回当前的日期和时间。MariaDB [test]&amp;amp;gt; select now(); +---------------------+ | now() | +---------------------+ | 2016-11-20 21:46:56 | +---------------------+2. curdate返回当前的日期。curdate()是current
mysql比较时间
sun13212715744的博客
10-17 3741
在MySQL中，您可以使用不同的方法来比较时间。通常，时间比较是在查询中使用的常见操作，用于筛选或排序具有时间戳或时间数据类型的记录。DATE()函数可用于提取日期部分，从而使日期比较更容易。例如，如果您希望比较两个日期是否相等，可以使用DATE()函数将日期部分提取出来，然后进行比较。BETWEEN运算符用于检查值是否在指定范围内。您可以使用它来比较两个时间点之间的记录。= 或 <>：不等于。
MySQL如何为字段添加默认时间浅析
09-09
在MySQL数据库中，为字段添加默认时间是一种常见的需求，特别是在记录数据创建和更新时间时。以下将详细解析如何实现这一功能。 首先，了解MySQL中的日期和时间类型是至关重要的。MySQL提供了五种不同的日期和时间...
MySQL查询性能优化
01-19
MySQL 查询性能优化 MySQL 查询性能优化是数据库管理和开发中非常重要的一方面。为了实现高性能，需要从多方面考虑，包括库表结构的设计、索引的建立、查询的设计等。只有合理的设计库表结构和索引，才能确保查询的...
MySQL中使用SQL语句进行日期比较
SrySmalltalk的博客
09-17 4317
日期比较在数据库中是一项常见的操作，它允许我们根据日期的先后顺序来查询和筛选数据。需要注意的是，日期比较时，日期值应该以字符串的形式出现，并遵循’YYYY-MM-DD’的格式。另外，如果在表中存储的是日期和时间类型的数据，比较运算符也可以用于比较日期和时间的组合。上述SQL语句将返回所有日期在’2023-01-01’和’2023-12-31’之间（包括这两个日期）的记录。上述SQL语句将返回所有日期晚于或等于’2023-01-01’的记录。上述SQL语句将返回所有日期早于’2023-01-01’的记录。
Mysql日期和时间函数大全
放飞我心
01-08 1051
原文 对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描述见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 344
1、首先是我们分析datetime长度是8个字节，INT的长度是4个字节，存储空间上比datatime少。 2、int存储索引的空间也比datetime少，排序效率高，查询速度比较快。 3、方便计算，unix时间戳有点就是方便计算。 总结了一下timestamp和datetime的区别： 1、timestamp有范围限制1970~2037 2、timestamp支持defaultc...
MySQL中的时间类型
热门推荐
Valineliu
08-31 2万+
时间是一类重要的数据，MySQL中有多种关于时间的类型可以选择。这篇文章主要介绍MySQL中的时间类型，主要参考MySQL文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html。
mysql date 索引性能_【转】MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
weixin_33851300的博客
02-17 1009
对于MyISAM引擎，不建立索引的情况下(推荐)，效率从高到低：int > UNIX_TIMESTAMP(timestamp) > datetime(直接和时间比较)>timestamp(直接和时间比较)>UNIX_TIMESTAMP(datetime) 。对于MyISAM引擎，建立索引的情况下，效率从高到低： UNIX_TIMESTAMP(timestamp) > ...
MySQL中timestamp、datetime和int类型的区别与优劣
riemann_的博客
09-10 2502
一、三者的区别 1、int 占用4个字节 建立索引之后，查询速度快 条件范围搜索可以使用使用between 不能使用mysql提供的时间函数 结论：适合需要进行大量时间范围查询的数据表 2、datetime 占用8个字节 允许为空值，可以自定义值，系统不会自动修改其值 实际格式储存（Just stores what you have stored and retrieves the same...
mysql按时间查询的优化_Mysql根据时间查询日期的优化技巧
weixin_29006187的博客
02-28 2880
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...
JAVA获取时间戳，哪个更快<转>
yongjian_luo的专栏
10-21 884
目前获取毫秒值大概有下面三种方法   Java代码   //方法 一   System.currentTimeMillis();    //方法 二   Calendar.getInstance().getTimeInMillis();   //方法 三   new Date().getTime();    最近做监控系统，发现代码中有前两种方法，
【mySQL】MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
bandaoyu的note
04-12 1364
目录 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字段的时间类型该如何选择？千万数据下性能提升10%~30%
关注菜菜的后端私房菜，获取更多技术干货~
10-16 1732
在MySQL中时间类型的选择有很多，比如：date、time、year、datetime、timestamp...在某些情况下还会使用整形int、bigint来存储时间戳根据节省空间的原则，当只需要存储年份、日期、时间时，可以使用year、date、time如果需要详细的时间，可以选择datetime、timestamp或者使用整形来存储时间戳以下是不同类型的格式、时间范围、占用空间相关信息类型格式范围空间（字节Byte）dateYYYY-MM-DD1000-01-01to。
MySQL VARCHAR日期字段分区实战
为了适应varchar类型的日期字段进行分区，我们可以使用转换函数将其转化为整型，以便进行比较。 创建分区的SQL语句如下： ```sql CREATE TABLE `t_test_trade` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值