MySQL时间设计 int timestamp datatime 查询效率性能比较

在数据库设计的时候,我们经常会需要设计时间字段,在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万条测试数据:

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.0780SELECT 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.4368SELECT 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.1370SELECT 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.3900SELECT 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.5696SELECT 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.4508SELECT 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.3198SELECT 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.7092SELECT 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.3834SELECT 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.0522SELECT 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.1776SELECT 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.0820SELECT 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直接时间比较。

posted @ 2019-07-10 23:50  iFindU_San  阅读(5323)  评论(0编辑  收藏  举报