本文共 2839 字，大约阅读时间需要 9 分钟。

数据库时间类型存储与查询性能比较

在实际应用开发中，选择合适的时间类型存储是数据库性能优化的重要环节。本文通过对不同时间类型存储和查询操作的性能测试，得出了一些实用的存储和查询建议。

数据库时间类型存储选择

数据库中常用的时间类型包括datetime、timestamp和bigint。具体选择哪种类型存储时间字段，需要根据实际需求来决定。以下是各类型的特点及适用场景：

数据库前期准备

在进行性能测试之前，需要准备一张包含50万条数据的测试数据集。以下是测试数据集的结构和内容：

CREATE TABLE `users` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `time_date` datetime NOT NULL,    `time_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    `time_long` bigint(20) NOT NULL,    PRIMARY KEY (`id`),    KEY `time_long` (`time_long`),    KEY `time_timestamp` (`time_timestamp`),    KEY `time_date` (`time_date`)) ENGINE=InnoDB AUTO_INCREMENT=500003 DEFAULT CHARSET=latin1

2. 实体类定义：

public class Users {    private Long id;    private Date timeDate;    private Timestamp timeTimestamp;    private long timeLong;}

3. DAO层接口：

public interface UsersMapper {    @Insert("insert into users(time_date, time_timestamp, time_long) value(#{timeDate}, #{timeTimestamp}, #{timeLong})")    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")    int saveUsers(Users users);}

4. 测试数据生成：

public class UsersMapperTest extends BaseTest {    @Resource    private UsersMapper usersMapper;    @Test    public void test() {        long time = System.currentTimeMillis();        for (int i = 0; i < 500000; i++) {            usersMapper.saveUsers(Users.builder()                .timeDate(new Date(time))                .timeLong(time)                .timeTimestamp(new Timestamp(time))                .build());        }    }}

SQL查询速率测试

通过对不同时间类型的查询性能进行测试，我们可以看出：

5. datetime类型查询：

select count(*) from users where time_date >="2018-10-21 23:32:44" and time_date <="2018-10-21 23:41:22"

耗时：0.171s

2. timestamp类型查询：

select count(*) from users where time_timestamp >= "2018-10-21 23:32:44" and time_timestamp <= "2018-10-21 23:41:22"

耗时：0.351s

3. bigint类型查询：

select count(*) from users where time_long >= 1540135964091 and time_long <= 1540136482372

耗时：0.130s

结论：在InnoDB存储引擎下，通过时间范围查找，bigint类型的查询性能优于datetime和timestamp类型。

SQL分组速率测试

对于分组操作，bigint的性能表现不如datetime和timestamp：

4. datetime类型分组：

select time_date, count(*) from users group by time_date

耗时：0.176s

2. timestamp类型分组：

select time_timestamp, count(*) from users group by time_timestamp

耗时：0.173s

结论：在InnoDB存储引擎下，通过时间分组，timestamp类型的性能略优于datetime类型，但两者相差不大。

SQL排序速率测试

对于排序操作，bigint类型的性能最优：

3. datetime类型排序：

select * from users order by time_date

耗时：1.038s

2. timestamp类型排序：

select * from users order by time_timestamp

耗时：0.933s

3. bigint类型排序：

select * from users order by time_long

耗时：0.775s

结论：在InnoDB存储引擎下，通过时间排序，bigint类型的性能优于timestamp类型，timestamp类型又优于datetime类型。

小结

在实际应用中，数据库中时间字段的存储类型选择需要根据具体需求来决定。如果需要对时间字段进行范围查询或排序操作，建议使用bigint类型存储。如果对时间字段不需要进行任何操作，建议使用timestamp类型，因为它既节省了存储空间（4个字节），又能满足大部分时间记录需求。