首页 > 数据库

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

来自：网络
时间：2022-12-27
阅读：
免费资源网 - https://freexyz.cn/
目录

前言：

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

就这个问题，来一个实践出真知吧。

一、准备工作

1.1 建表

CREATE TABLE IF NOT EXISTS `datetime_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1,
  `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;

1.2 插入100万条测试数据

//插入d_intvalue=1到100万之间的数据
insert into datetime_test(d_int,d_timestamp,d_datetime) 
values(d_intvalue,FROM_UNIXTIME(d_intvalue),FROM_UNIXTIME(d_intvalue));

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

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

二、MyISAM引擎

2.1 MyISAM 引擎无索引下的 dint/dtimestamp/d_datetime

2.1.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比

//SQL_NO_CACHE意思是说查询时不适用缓存
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 秒

效率不错

2.1.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比

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相当；直接和日期比较效率低。

2.1.3 datetime 类型是否调用 UNIX_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.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 内置函数查询效率很低，不建议；直接和日期比较，效率还行。

2.2 MyISAM 引擎有索引下的 dint/dtimestamp/d_datetime

2.2.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比

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 类型，有索引的效率反而低了，笔者估计是由于设计的表结构问题，多了索引，反倒多了一个索引查找。

2.2.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比

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 类型，有没有索引貌似区别不大。

2.2.3 datetime 类型是否调用 UNIX_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引擎

3.1 InnoDB 引擎无索引下的 dint/dtimestamp/d_datetime

3.1.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比

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 倍+。

3.1.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比

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 内置函数查询效率同样高出直接和日期比较。

3.1.3 datetime 类型是否调用 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 内置函数查询。

3.2 InnoDB 引擎无索引下的 dint/dtimestamp/d_datetime

3.2.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比

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 有大幅提高。

3.2.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比

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 查询了。

3.2.3 datetime 类型是否调用 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 > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较）> timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime) 。
  • 对于 MyISAM 引擎，建立索引的情况下，效率从高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIXTIMESTAMP(datetime) 。
  • 对于 InnoDB 引擎，没有索引的情况下(不建议)，效率从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime)。
  • 对于 InnoDB 引擎，建立索引的情况下，效率从高到低：int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。
  • 一句话，对于 MyISAM 引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；对于InnoDB 引擎，建立索引，采用 int 或 datetime直接时间比较。
免费资源网 - https://freexyz.cn/
最新文章 相关文章

四种数据库随机获取10条数据的方法

SQL Server:SELECT TOP 10 * FROM T_USER ORDER BY NEWID()ORACLE:SELECT * FROM (SELECT * FROM T_USER ORDER BY DBMS_RANDOM.RANDOM()) WHERE RONUM <= 10MySQL:SELECT *

2024-10-21 20:47:50

Mysql分页查询limit语句的性能分析

一、limit用法在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。SELECT * FROM table LIMIT [off

2024-10-21 20:44:48

sql文件导入[ERR] 2006 - MySQL server has gone away

目录 问题 原因 解决办法 如何设置和查看使用Navicat Premium 12导入mysql的sql文件时，导入失败，出现如下错误：2006 - MySQL server has gone away。mysql出现ERROR : (2006, &

2024-10-21 20:43:22

mysql8.0 .ibd文件恢复表结构的实现

今天早上启动了 phpstudy 软件中的 MySQL 8.0 数据库时，发现它启动后约过了 3 秒就自行关闭了。接着又自动启动然后再次关闭，似乎陷入了一个“开-闭-开”的循环。这导致我无

2024-10-18 23:15:41

MySQL实现按分秒统计数据量方式


目录



1. 每秒交易量


2. 每分钟交易量


3. 每5分钟交易量


4. 每10分钟交易量


5. 每30分钟交易量


结合 WHERE

2024-10-18 23:15:27

Redis的五种基本类型和业务场景和使用方式

目录 Redis是什么？ Redis的特点 依赖 配置 redis的配置类（用于格式转换，处理乱码） String（字符串） 特点 业务场景 代码使用案例 List（列表） 特点 业务场景 代码使用案例 S

2024-10-18 23:15:18

Oracle中BLOB、CLOB的读取和写入方式

目录 Oracle中BLOB、CLOB的读取和写入 基于SQL的方式实现CLOB、BLOB的插入与读取 1. 插入大类型数据 2. 读取大类型数据并转换为字符串 基于Java方式实现CLOB和B

2024-10-18 23:15:09

dbeaver如何导出mysql数据库

目录 dbeaver导出mysql数据库 右键表-tools-Dump database 按默认设置 总结 dbeaver导出mysql数据库右键表-tools-Dump database按默认设置只需修改输出文件夹我这里把

2024-10-18 23:15:01

DBeaver如何实现导入excel中的大量数据

目录 DBeaver如何导入excel中的大量数据 步骤一：将Excel文件转换为CSV格式 步骤二：在DBeaver中创建数据库表（如果尚未创建） 步骤三：导入CSV文件到数据库表 注意事项 总结

2024-10-18 23:14:43

Oracle归档日志爆满问题的处理方法

目录 一、先清理归档日志使得数据库能够正常连接 二、排查问题 三、处理问题 四、清理后效果最近客户单位的oracle数据库出了问题，经常出现无法连接,报错提示 ORA-00257: arc

2024-10-18 23:14:30

Mysql中常用函数之分组,连接查询功能实现

目录 函数 单行函数 字符函数 数学函数 日期函数 流程控制函数 分组函数 分组查询 按单个字段分组 在分组前进行条件筛选 在分组之后进行条件筛选 按多字段分

2024-10-18 23:14:18

SQL Server计算两个时间相差的示例代码

目录 1、计算两个时间字符串之间的差值 2、字符串转换成日期/时间类型 3、计算两个日期和时间之间的差值在 SQL Server 中，计算两个时间字符串之间的差值 首先将这些字符串转

2024-10-18 23:14:10

Oracle DECODE 丢失时间精度的原因与解决方案

目录 问题描述 原因分析 解决方案在Oracle数据库中，DECODE 函数是一个非常实用的条件处理函数，通常用于替代简单的 CASE WHEN 语句。它根据给定的值列表进行匹配，如果匹配成功

2024-10-18 23:14:02

使用 SQL 快速删除数百万行数据的实践记录

目录 描述 实践描述删除表大批量数据，这是一个比较少的事件。 但在实际的业务开发中或者数据测试也会遇到这种情况。比如定期从日志大表中删除几百万的数据记录；删除表数据的

2024-10-18 23:13:52

MySQL处理大量DELETE操作的多种方法

目录 前言 简介 概述 DELETE 操作的基本概念 常用的 DELETE 方法 核心源码解读 简单 DELETE 语句 批量 DELETE 示例 案例分析 案例1：使用简单 DELETE 删除用户数据

2024-10-18 23:13:43

MySQL删除表数据、清空表命令详解(truncate、drop、delete区别)

目录 一、MySQL清空表数据三种方法 1.1 清空表数据：truncate 1.2 删除表：drop 1.3 删除/清空表数据：delete 二、使用原则 三、truncate、drop、delete区别 1.操作类型

2024-10-14 19:50:11

mysql如何将数据库中的所有表结构和数据导入到另一个库

目录 一、问题描述 二、解决方案 1.使用mysqldump命令备份数据库 2.创建目标数据库 3.导入数据 到目标数据库 三、补充知识 3.1 mysqldump命令介绍 3.2 常用备份命

2024-10-14 19:50:03

MySQL深分页,limit 100000,10优化方式

目录 一、limit深分页为什么会变慢 二、优化方案 2.1 通过子查询优化（覆盖索引） 回顾B+树结构 覆盖索引 把条件转移到主键索引树 2.2 INNER JOIN 延迟关联

2024-10-14 19:49:55

MySQL中的TRUNCATE()函数用法

目录 MySQL的TRUNCATE()函数 规则如下 函数示例 当 X 为正数时 当 X 为负数时 总结 MySQL的TRUNCATE()函数TRUNCATE(X,D) 是MySQL自带的一个系统函数。其中，X是数值，D

2024-10-14 19:49:37

查看本地MYSQL数据库IP地址的三种方法

目录 方法一：在电脑设置中查看电脑iP地址 方法二：通过运行程序查看电脑IP地址 方法三：通过浏览器查看电脑IP地址 拓展：在自己电脑建立了MYSQL数据库，但是想在其它的电脑进行连接，

2024-10-14 19:49:29

Redis中pipeline(管道)的实现示例

目录 概念 Pipeline 底层原理分析 Redis单个命令执行基本步骤 RTT 时间 Redis Pipeline Pipeline实际应用场景 数据导入导出 数据处理 批量操作 其他应用场

2024-10-14 19:49:13

Postgresql开启SQL执行语句收集过程

修改配置文件1.打开Postgresql配置文件，如：C:\Program Files\PostgreSQL\14\data\postgresql.conf
2.修改如下，如果字段被#注释，则去掉#log_directory = &#39;log&#39;
log_file

2024-10-14 19:49:02

MySQL分区表的使用

目录 创建分区 删除分区 分区性能 总结说明：分区表，顾名思义，就是一张表根据规则，划分多个区，通过分区，实现一种“逻辑隔离”，这在Saas系统中是非常常见的。本文介绍如何

2024-10-14 19:48:52

KubeSphere部署mysql的详细步骤

目录 1.创建mysql基础配置 2.创建pvc挂载 3.创建工作负载 4.创建服务（创建服务后才能进行外部访问） 5.测试mysql是否能正常访问演示示例使用的是3.4.1，各版本有名字差异 功能是

2024-10-14 19:48:36

mySQL 8.0.33安装指南(推荐)

目录 安装步骤 1. 解压安装包 2. 移动解压后的文件夹 3. 创建MySQL用户 4. 设置权限 5. 配置MySQL 6. 设置环境变量 7. 初始化数据库 8. 设置系统服务 9. 设置roo

2024-10-14 19:48:11

重置MySQL 8.0 Root密码的简便方法小结

目录 解决方案 1. 检查是否输入正确的密码 2. 使用“跳过权限表”的方式重置 root 密码 2.1 停止 MySQL 服务 2.2 以跳过权限表模式启动 MySQL 2.3 重

2024-10-14 19:48:03

Redis实现接口防抖的示例代码

目录 实现 演示说明：实际开发中，我们在前端页面上点击了一个按钮，访问了一个接口，这时因为网络波动或者其他原因，页面上没有反应，用户可能会在短时间内再次点击一次或者用户以为没

2024-10-14 19:47:53

Solusi MySQL 错误无法启动

发生的错误好吧，当我在 laragon 应用程序中从mysql 5.1.72升级到mysql 8.0.32时，发生了这个错误。当我在 laragon 应用程序中 start all 时，出现的错误是这样的。laragon 中的

2024-09-17 21:32:55

掌握数据库操作：索引、视图、备份和恢复

介绍在本实验中，我们将学习和练习索引、视图、备份和恢复。这些概念对于数据库管理员来说非常重要。学习目标 创建索引 创建视图 备份与恢复准备开始之前，我们需要准备好环境

2024-09-17 21:32:18

怎么导入mysql数据库文件 mysql数据库怎么导入sql文件

如何将 SQL 文件导入 MySQL 数据库导入 MySQL 数据库文件的方法：使用 MySQL 客户端 打开 MySQL 客户端并连接到目标数据库。 使用 SOURCE 命令，后跟要导入的 SQL 文件路径：SOUR

2024-09-17 21:32:00

热点内容

PHP连接MongoDB数据库报错“No suitable servers found (`serverSelectionTryOnce` se
2018-08-19
解决Failed to set session cookie. Maybe you are using HTTP instead of HTTPS to a
2020-03-22
解决Job for mysqld.service failed because the control process exited with error
2020-03-22
Navicat for MySQL 15注册激活详细教程
2020-12-17
解决MySQL8 #1227 – Access denied; you need (at least one of) the SYSTEM_USER pr
2020-03-22
mysql workbench怎么设置为中文？
2020-10-15
Failed to open file ''****.sql'', error: 2的解决方案
2021-01-28
解决 SQLSTATE[HY000] [2054] The server requested authentication method unknown t
2020-03-22
MySQL8.0.23安装超详细教程
2021-01-21
mysql数据库mysql: [ERROR] unknown option '--skip-grant-tables'
2020-10-28
返回顶部
顶部