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

最新推荐文章于 2025-06-11 08:21:58 发布
转载 最新推荐文章于 2025-06-11 08:21:58 发布 · 1.3k 阅读 · 0

本文通过实验对比了MySQL中不同日期类型（int、timestamp、datetime）在MyISAM与InnoDB存储引擎下，有无索引时的查询性能。结果显示，在特定条件下，使用int类型或带有索引的datetime类型能获得较好的查询效率。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

转载自：http://www.piaoyi.org/database/MYSQL-INT-TIMESTAMP-DATETIME.html

建立表：

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中的DATETIME与TIMESTAMP选择指南
九天的专栏
02-27 1032
在MySQL中，选择合适的日期时间类型对于确保数据的准确性和性能至关重要。DATETIME和TIMESTAMP是两种常用的日期时间类型，它们在存储范围、时区处理和性能等方面存在差异。了解这些差异有助于选择最适合的应用场景。
Mysql-时间的存储 datetime还是时间戳timestamp？
S_ZaiJiangHu的博客
07-20 5329
c、然后mysql数据库接收到这个时间字符串2020-02-23080000后，由于数据库时区配置是东9区，它会认为这个时间是东9区的，它会以东9区解析这个时间字符串，这时数据库保存的时间是东9区的2020-02-23080000，也就是东8区的2020-02-23070000，保存的时间就偏差了1个小时。从上表可以看到，DATETIME默认占用5个字节，而TIMESTAMP默认占用4个字节，如果需要更高精度的存储（秒后的小数点个数，比如毫秒）那么需要额外的存储空间。......
MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
热门推荐
adsadadaddadasda的博客
12-29 2万+
正 文：    在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。 MYSQL版本号：5.5.19 建立表： CREATE TABLE IF N
MySQL 中的日期时间
彳亍
12-19 630
1. now返回当前的日期和时间。MariaDB [test]&amp;amp;gt; select now(); +---------------------+ | now() | +---------------------+ | 2016-11-20 21:46:56 | +---------------------+2. curdate返回当前的日期。curdate()是current
mysql怎么比较在两个时间之间
weixin_40260916的博客
07-19 1735
MySQL中比较两个时间之间的方法 在数据库操作中，我们经常需要比较两个时间点之间的差异。MySQL作为关系型数据库管理系统，提供了丰富的时间比较功能。本文将详细介绍如何在MySQL中比较两个时间点，并提供代码示例。 1. 基本的时间比较 在MySQL中，我们可以使用比较运算符（如>、<、=等）来比较两个时间点...
Mysql 时间类型比较
目标检测专栏持续更新中，改进YOLO系列通用，适用v5、v7、v8、所有博客均是团队原创博客，所有文章禁止转载，违者必究。
04-07 1万+
SELECT * FROM charging_rules WHERE startTime <= ‘14:00:00’ AND endTime >= ‘18:00:00’ SELECT * FROM charging_rules WHERE startTime <= ‘2019-03-21’ AND endTime >= ‘2019-03-21’ SELECT * FROM charging_rules WHERE startTime <= ‘2019-03-21 14.
MySQL时间戳用BIGINT和TIMESTAMP类型的比较
Mr_lee_1的博客
08-21 1485
存储效率TIMESTAMP占用空间较小（4 字节），但仅适用于 1970 到 2038 年的时间范围。BIGINT占用空间较大（8 字节），但可以存储更大的范围和更高精度的时间。查询效率：两者在索引上都表现良好，但TIMESTAMP自动处理时区，适合时间范围查询。BIGINT适合存储和查询不需要时区处理的时间戳。选择哪种类型取决于你的应用需求。如果需要时区支持并且时间范围在 2038 年之前，TIMESTAMP是更好的选择。如果需要处理更高精度或更大范围的时间戳，BIGINT更合适。
MySQL中应使用datetime还是timestamp数据类型
最新发布
qq_29715355的博客
06-11 526
在MySQL数据库开发中，经常需要存储日期和时间信息。datetime和timestamp是两种常用的数据类型，用于存储包含日期和时间的数据。但在实际应用中，需要根据具体的业务场景来选择合适的数据类型，以确保数据的准确性和系统的性能。
MySQL - DATETIME与TIMESTAMP时间精度问题
⚡
12-05 2979
datetime与timestamp时间精度问题 默认时间精度与最大时间精度 更改数据库中所有指定字段的类型的存储过程（用于修正时间精度） 一：默认时间精度与最大时间精度 -- 创建数据库 CREATE DATABASE mydb_1; -- 查看创建数据库建表语句（默认编码UTF8） SHOW CREATE DATABASE mydb_1; -- 创建表 -- 测试datetime的精度 CREATE TABLE test( -- 默认精度为0 -- Maximum is 6. datet
MySQL中时间类型 – datetime、bigint、timestamp的查询效率比较
bishe_teacher的博客
06-29 1194
MySQL中时间类型 – datetime、bigint、timestamp的查询效率比较 数据库中可以用 datetime、bigint、timestamp 来表示时间，那么选择什么类型来存储时间比较合适呢？ 前期数据准备 通过程序往数据库插入 50w 数据 数据表： CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, time_date datetime NOT NULL, time_timestamp timestamp NOT NULL
mysql时间比较
05-15
mysql时间比较，两个事件类型的字段比较大小，以及时间加减计算，例子
mysql按时间查询的优化_Mysql根据时间查询日期的优化技巧
weixin_29006187的博客
02-28 3037
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...
DATETIME vs TIMESTAMP
dreamship
04-13 130
DATETIME vs TIMESTAMP vs INT，怎么选？ 在做表结构设计时，对日期字段的存储，开发人员通常会有 3 种选择：DATETIME、TIMESTAMP、INT。 INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。 当然，有些同学会认为 INT 比 TIMESTAMP 性能更好。但是，由于当前每个 CPU 每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，
mysql大于小于能用between_MySQL中大于小于，IN，OR，BETWEEN性能比较
weixin_33930436的博客
01-19 2442
在MySQL中经常会有朋友问关于大于小于，IN，OR，BETWEEN，哪个性能更好？我想有很多朋友会用它们但确实不知道大于小于，IN，OR，BETWEEN性能哪个更好吧，下面总结一下关于大于小于，IN，OR，BETWEEN性能。这里的查询条件有四种：>=和<=；between；or；in这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。1 准备环境mysql&...
记录总结，MySQL中的时间字段的几种数据类型比较
08-15 719
序言 以前脑海里就一念头总以为啥时间都用int存储，最近在项目开发时，频繁见到datetime类型，对于MySQL数据库中的有关时间的字段该选用何种类型，引发了一些争论。所以做了一些简单的研究，看了一些blog和官方文档。最后做出一个自己的总结。 类型比较 int INT类型一般用于存储Unix时间戳。使用一个简单的 INT 列保存 Unix 时间是最普通的方法。使用 INT，你可以确保你要保存的...
mysql 日期时间
qq_20610631的博客
01-26 1063
搜索最新的时间 select * from po order by fdate desc   今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 Select * FROM 表名 Where TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) &lt;= 1 7天前 如果是7天后就是DATE_...
mysql时间等于现在_MySQL中的日期时间等于或大于今天
weixin_42464967的博客
01-19 1万+
10 个答案:答案 0 :(得分：280)SELECT * FROM users WHERE created >= CURDATE();但我认为你的意思是created < today答案 1 :(得分：69)答案 2 :(得分：29)SELECT * FROM users WHERE created >= NOW();如果列是日期时间类型。答案 3 :(得分：13)如果'cre...
mysql中的timestamp类型时间比较：unix_timestamp函数
weixin_33834628的博客
10-31 439
在mysql中，某字段的类型设置为了timestamp，那么我们现在希望取出指定时间段的记录，该如何做呢？ 在php中有time()和strtotime()来进行日期和时间戳的格式化，而在mysql中也有类似的函数，它们就是unix_timestamp()和from_unixtime()函数 举例说明，参考如下sql语句： ...
mysql数据库timestamp和datetime
02-26
### MySQL Timestamp 和 Datetime 类型的区别及用法 #### 数据存储方式 `TIMESTAMP` 和 `DATETIME` 都用于表示日期时间，但在内部存储上有所不同。`TIMESTAMP` 存储的是 Unix 时间戳，即从 1970 年 1月 1日 UTC 到当前时刻所经过的秒数；而 `DATETIME` 是按照 "YYYY-MM-DD HH:MM:SS" 的格式来保存具体的年份、月份、日子以及小时分钟秒钟等信息[^1]。 #### 范围大小 - **Timestamp**: 支持的有效范围是从 '1970-01-01 00:00:01' UTC 至 '2038-01-19 03:14:07' UTC。 - **Datetime**: 可以表达更广泛的时间区间，默认情况下支持从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'[ ^1 ]. #### 自动更新特性 当表结构定义时指定了属性 `ON UPDATE CURRENT_TIMESTAMP`, 对于 `TIMESTAMP` 字段来说，在记录被更改的时候会自动设置为最新的系统时间；但是这个行为对于 `DATETIME` 不适用，除非显式赋值否则不会改变原有数据中的时间戳[ ^1 ]. #### 占用空间 由于 `TIMESTAMP` 使用较少字节（通常4个字节），相比之下 `DATETIME` 所需的空间更多一些（通常是8个字节）。这使得前者更加节省磁盘资源但也限定了其能表示的时间跨度较小[ ^1]. ```sql -- 创建包含两种类型的测试表格 CREATE TABLE test_time ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 默认创建时间和更新时间为现在 dt DATETIME ); ``` #### 时区处理 另一个重要区别在于如何对待不同的服务器或客户端所在的地理位置所带来的影响。每当读取或者写入带有 `TIMESTAMP` 型别的列的数据时都会考虑当前会话设定好的时区参数，并将其转换成协调世界时 (UTC)，而在检索这些数值出来显示给用户之前再转回本地时间。然而，`DATETIME` 总是以固定的格式呈现而不受任何时差的影响[ ^1 ].
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值