mysql 時間戳 bigint_MySQL時間類型datetime、bigint及timestamp的查詢效率

問題：數據庫中可以用 datetime、bigint、timestamp 來表示時間，那么選擇什么類型來存儲時間比較合適呢？

前期數據準備

通過程序往數據庫插入 50w 數據

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

其中 time_long、time_timestamp、time_date 為同一時間的不同存儲格式

實體類 users

/**

* @author hetiantian

* @date 2018/10/21

* */

@Builder

@Data

public class Users{

/**

* 自增唯一id

* */

private Long id;

/**

* date類型的時間

* */

private Date timeDate;

/**

* timestamp類型的時間

* */

private Timestamp timeTimestamp;

/**

* long類型的時間

* */

private long timeLong;

}

dao 層接口

/**

* @author hetiantian

* @date 2018/10/21

* */

@Mapper

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);

}

測試類往數據庫插入數據

public class UsersMapperTest extends BaseTest{

@Resource

private UsersMapper usersMapper;

@Test

public void test(){

for (int i = 0; i < 500000; i++) {

long time = System.currentTimeMillis();

usersMapper.saveUsers(Users.builder().timeDate(new Date(time)).timeLong(time).timeTimestamp(new Timestamp(time)).build());

}

}

}

生成數據代碼方至 github：http://github.com/TiantianUpup/sql-test/ 如果不想用代碼生成，而是想通過 sql 文件倒入數據，附 sql 文件網盤地址：http://pan.baidu.com/s/1Qp9x6z8CN6puGfg-eNghig

sql 查詢速率測試

通過 datetime 類型查詢：

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

耗時：0.171

通過 timestamp 類型查詢

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

耗時：0.351

通過 bigint 類型查詢

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

耗時：0.130s

結論 在 InnoDB 存儲引擎下，通過時間范圍查找，性能 bigint > datetime > timestamp

sql 分組速率測試

使用 bigint 進行分組會每條數據進行一個分組，如果將 bigint 做一個轉化在去分組就沒有比較的意義了，轉化也是需要時間的

通過 datetime 類型分組：

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

耗時：0.176s

通過 timestamp 類型分組：

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

耗時：0.173s

結論 在 InnoDB 存儲引擎下，通過時間分組，性能 timestamp > datetime，但是相差不大

sql 排序速率測試

通過 datetime 類型排序：

select * from users order by time_date

耗時：1.038s

通過 timestamp 類型排序

select * from users order by time_timestamp

耗時：0.933s

通過 bigint 類型排序

select * from users order by time_long

耗時：0.775s

結論 在 InnoDB 存儲引擎下，通過時間排序，性能 bigint > timestamp > datetime

小結

如果需要對時間字段進行操作 (如通過時間范圍查找或者排序等)，推薦使用 bigint，如果時間字段不需要進行任何操作，推薦使用 timestamp，使用 4 個字節保存比較節省空間，但是只能記錄到 2038 年記錄的時間有限。