数据库中可以用datetime、bigint、timestamp来表示时间,那么选择什么类型来存储时间比较合适呢?
前期数据准备
通过程序往数据库插入50w数据
数据表:
CREATETABLE`users`(`id`int(11)NOTNULLAUTO_INCREMENT,`time_date`datetimeNOTNULL,`time_timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`time_long`bigint(20)NOTNULL,PRIMARYKEY(`id`),KEY`time_long`(`time_long`),KEY`time_timestamp`(`time_timestamp`),KEY`time_date`(`time_date`))ENGINE=InnoDBAUTO_INCREMENT=DEFAULTCHARSET=latin1
其中time_long、time_timestamp、time_date为同一时间的不同存储格式
实体类users
/***
authorhetiantian*date/10/21**/BuilderDatapublicclassUsers{/***自增唯一id**/privateLongid;/***date类型的时间**/privateDatetimeDate;/***timestamp类型的时间**/privateTimestamptimeTimestamp;/***long类型的时间**/privatelongtimeLong;}dao层接口
/***
authorhetiantian*date/10/21**/MapperpublicinterfaceUsersMapper{Insert("insertintousers(time_date,time_timestamp,time_long)value(#{timeDate},#{timeTimestamp},#{timeLong})")Options(useGeneratedKeys=true,keyProperty="id",keyColumn="id")intsaveUsers(Usersusers);}测试类往数据库插入数据
publicclassUsersMapperTestextendsBaseTest{
ResourceprivateUsersMapperusersMapper;Testpublicvoidtest(){for(inti=0;i;i++){longtime=System.currentTimeMillis();usersMapper.saveUsers(Users.builder().timeDate(newDate(time)).timeLong(time).timeTimestamp(newTimestamp(time)).build());}}}生成数据代码方至github:如果不想用代码生成,而是想通过sql文件导入数据,
SQL查询速率测试
通过datetime类型查询:
selectcount(*)fromuserswheretime_date="-10-:32:44"andtime_date="-10-:41:22"
耗时:0.
通过timestamp类型查询
selectcount(*)fromuserswheretime_timestamp="-10-:32:44"andtime_timestamp="-10-:41:22"
耗时:0.
通过bigint类型查询
selectcount(*)fromuserswheretime_long=1andtime_long=2
耗时:0.s
结论在InnoDB存储引擎下,通过时间范围查找,性能bigintdatetimetimestamp
SQL分组速率测试
使用bigint进行分组会每条数据进行一个分组,如果将bigint做一个转化在去分组就没有比较的意义了,转化也是需要时间的
通过datetime类型分组:
selecttime_date,count(*)fromusersgroupbytime_date
耗时:0.s
通过timestamp类型分组:
selecttime_timestamp,count(*)fromusersgroupbytime_timestamp
耗时:0.s
结论在InnoDB存储引擎下,通过时间分组,性能timestampdatetime,但是相差不大
SQL排序速率测试
通过datetime类型排序:
select*fromusersorderbytime_date
耗时:1.s
通过timestamp类型排序
select*fromusersorderbytime_timestamp
耗时:0.s
通过bigint类型排序
select*fromusersorderbytime_long
耗时:0.s
结论在InnoDB存储引擎下,通过时间排序,性能biginttimestampdatetime
小结
如果需要对时间字段进行操作(如通过时间范围查找或者排序等),推荐使用bigint,如果时间字段不需要进行任何操作,推荐使用timestamp,使用4个字节保存比较节省空间,但是只能记录到2年记录的时间有限