Mysql - 如何決定用 datetime、timestamp、int 哪種型別儲存時間戳？

背景

• 資料表都很可能會有一兩個欄位需要儲存日期時間資料，那應該用什麼 Mysql 型別來儲存呢？
• 前面講過 datetime、timestamp、int 的方式來儲存日期時間

如何儲存 10位、13位的 unix 時間戳？

date、datetime、timestamp 的區別

本篇文章會對 datetime、timestamp、int 進行比較，然後對一些典型的查詢進行基準測試，來決定什麼情況下使用哪種資料型別

 

整體對比表

加粗是缺點

Feature	datetime	timestamp	Int (儲存 Unix time)
本地時間表示	Yes	Yes	No，如果要表示為本地時間需要藉助轉換函式，比如FROM_UNIXTIME()
儲存小數秒	Yes，高達 6 位精度	Yes，高達 6 位精度	No
有效範圍	'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999	'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'	如果是無符號， '1970-01-01 00:00:01.000000; 理論上可到 '2106-2-07 06:28:15'
自動初始化 (MySQL 5.6.5+)	Yes	Yes	No
可讀性好	Yes	Yes	No, 必須轉換才能知道具體時間點
儲存時間值會轉換為 UTC 時間	No	Yes	No
可以改成其他了誒性	Yes, 如果結果值在有效時間範圍內	Yes	Yes, 如果結果值在有效時間範圍內並使用了轉換函式
儲存要求 (MySQL 5.6.4+)	5 bytes （加上最多 3 個位元組的小數秒，如果使用）	4 bytes（加上最多 3 個位元組的小數秒，如果使用）	4 bytes (no fractional seconds allowed)

 

接下來對 int、timestamp、datetime 的效能進行基準測試

• 這裡直接展示結果，不展示過程了（因為只需要關注結果即可）
• 感興趣可以看：https://vertabelo.com/blog/
• 這裡會使用 sysbench、mysqlslap 兩個效能測試工具

 

測試一：選擇日期範圍內的值

下列查詢均是從 1,497,421 個可用資料中返回 75,706 行

datetime

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < '2016-02-01 00:00:00.0';

Response time (ms)	Sysbench	mysqlslap
Min	152	296
Max	1261	3203
Average	362	809

 

timestamp

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < '2016-02-01 00:00:00.0'; 

Response time (ms)	Sysbench	mysqlslap
Min	214	359
Max	1389	3313
Average	431	1004

 

int（使用 FROM_UNIXTIME 轉換函式）

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0'
        AND FROM_UNIXTIME(m.measured_on) < '2016-02-01 00:00:00.0';

Response time (ms)	Sysbench	mysqlslap
Min	2472	7968
Max	6554	10312
Average	4107	8527

 

int

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    m.measured_on > 1451617200
        AND m.measured_on < 1454295600;

Response time (ms)	Sysbench	mysqlslap
Min	88	171
Max	275	2157
Average	165	514

 

結論

• 兩個基準測試工具都表明 datime 比 timestamp 和 int（使用轉換函式） 快
• 但是 datetime 並不比直接用 int 數字快

Avg response time (ms)	Sysbench	相對於 datetime 的速度	mysqlslap	相對於 datetime 的速度
datetime	362	-	809	-
timestamp	431	19% slower	1004	24% slower
int（使用轉換函式）	4107	1134% slower	8527	1054% slower
int	165	55% faster	514	36% faster

 

測試二：選擇星期一的資料

下列查詢均是從 1,497,421 個可用資料中返回 221,850 行

datetime

SELECT SQL_NO_CACHE measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	1874	4343
Max	6168	7797
Average	3127	6103

 

timestamp 

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	2688	5953
Max	6666	13531
Average	3653	8412

 

int（使用 FROM_UNIXTIME 轉換函式） 

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	2051	5844
Max	7007	10469
Average	3486	8088

 

結論

• 兩個基準測試工具都表明 datime 比 timestamp 和 int（使用轉換函式） 快
• 但在這個測試中，int（使用轉換函式）比 timestamp 更快

Avg response time (ms)	Sysbench	相對於 datetime 的速度	mysqlslap	相對於 datetime 的速度
Datetime	3127	-	6103	-
Timestamp	3653	17% slower	8412	38% slower
INT	3486	11% slower	8088	32% slower

 

測試三：統計星期一的資料量

下列查詢均是從 1,497,421 個可用資料中返回 1 行

datetime

SELECT SQL_NO_CACHE 
    COUNT(measured_on)
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	1720	4063
Max	4594	7812
Average	2797	5540

 

timestamp 

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	1907	4578
Max	5437	10235
Average	3408	7102

 

int（使用 FROM_UNIXTIME 轉換函式） 

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Response time (ms)	Sysbench	mysqlslap
Min	2108	5609
Max	4764	9735
Average	3307	7416

 

結論 

• 兩個基準測試工具都表明 datime 比 timestamp 和 int（使用轉換函式） 快
• 但在這個測試中，int（使用轉換函式）比 timestamp 更快

 

Avg response time (ms)	Sysbench	相對於 datetime 的速度	mysqlslap	相對於 datetime 的速度
Datetime	2797	-	5540	-
Timestamp	3408	22% slower	7102	28% slower
INT	3307	18% slower	7416	33% slower

 

最終結論

使用 datetime 應該是絕大多數場景下的最佳選擇，因為

• 它更快
• 它可讀性更好，無需轉換
• 沒有時區切換的問題
• 它僅比 timestamp 多使用 1 個位元組，但儲存的時間範圍卻非常大

 

做抉擇

• 如果只是想儲存簡單的 unix 時間戳，那麼使用 int 是最佳選擇，因為它非常快，和使用普通數字一樣
• 而如果要根據時區進行儲存日期時間，那麼就應該使用 timestamp
• 否則絕大多數情況下推薦使用  datetime