MYSQL数据库时间字段到底应该用什么类型？是INT,TIMESTAMP还是DATETIME？ 且看三者性能效率比较_用户订单表的订单时间是否设置int类型

一、前言

每次新做项目设计数据库的时候，项目组里的几个人总会为了时间字段的设计争论个不停。

在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？

接下来我们就来看一下具体的比较吧!

先立个flag：int存时间戳绝对是无敌的！

二、准备

使用的MYSQL版本号：5.5.19

1. 建表：

-- mysql
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=1 DEFAULT CHARSET=utf8;
 
ALTER TABLE `datetime_test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

2. 插入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++) { 
    $pdo->exec("insert into datetime_test(d_int,d_timestamp,d_datetime) 
    values($i,FROM_UNIXTIME($i),FROM_UNIXTIME($i))");
}

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

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

 

三、 开始比较

1. MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引

1.1 d_int（int类型）

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 秒

小结: int类型的效率不错。

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

1.3 d_datetime(datetime类型)

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. MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都有索引

2.1 d_int（int类型）

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 d_timestamp(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.3 d_datetime(datetime类型)

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类型，有索引反而效率低了。

 

3. InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引

3.1 d_int（int类型）

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.2 d_timestamp(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.3 d_datetime(datetime类型)

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内置函数查询。

 

4. InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都有索引

4.1 d_int（int类型）

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

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

4.3 d_datetime(datetime类型)

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

 

四、总结

经过整理后得出：

1. 对于MyISAM引擎，不建立索引的情况下（推荐），效率从高到低：

int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。

2. 对于MyISAM引擎，建立索引的情况下，效率从高到低： 

UNIX_TIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。

3. 对于InnoDB引擎，没有索引的情况下(不建议)，效率从高到低：

int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(datetime)。

4. 对于InnoDB引擎，建立索引的情况下，效率从高到低：

int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。

 

总结来说，

对于MyISAM引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；

对于InnoDB引擎，建立索引，采用 int 或 datetime直接时间比较。