從問題說起
今天我們來探討一個有意思的問題，先說場景：
這是一個做在線文檔產品的業務，需要給用戶展示文檔的編輯記錄，現在我們叫它【智能文檔】。 智能文檔會不定期給文檔數據打一個快照，保存起來。用戶可以在歷史記錄中查閱快照。 快照之間會展示具體的變更記錄，比如“用戶A 復制了一段文字”，“用戶B刪除了一個圖片”。 快照本身是動態生成和回收的，即距離現在越遠的快照，留下來得越少（更稀疏的快照意味著相鄰快照之間的變更記錄會更多，本來是一天一個快照，展示這一天內的變更記錄即可，后來變成了一周一個快照，于是需要展示這一周內的變更記錄）
那如何實現查找兩個快照之間的【變更記錄】有哪些呢？
快照 和 變更記錄 預期是兩張表。首先我們不能將【變更記錄】通過 id 掛在某個【快照】上，因為我們的快照是不斷被回收的，這樣的話當你回收快照時，也需要連帶著更新大量的【變更記錄】，出現寫擴散。
另一個想法是，能否通過時間戳進行比較？比如快照 A 的創建時間戳是 12345，快照 B 的創建時間戳是 23456。那么我只要【變更記錄】這張表也有一個時間戳字段，寫一個 SQL 查到兩個快照時間戳之間的變更記錄是不是就可以了？
寫出來 SQL 類似這樣：
select * from change_record where create_time > 12345 and create_time < 23456; 復制代碼
那么，問題來了，這個 create_time，雖然這里我們直接拿時間戳比較，但真的是性能最好的么？建表的時候，我應該用 datetime, TIMESTAMP 還是 int ?
今天我們就來看看到底有什么區別。
MySQL 支持的數據類型
任何一篇博客，教程都比不上官方文檔，大家選型有疑慮時還是建議先來看看 MySQL Data Types 。
integer
我們先來看 integer 有什么類型。
SQL 標準中對于整數，提出了兩種類型：INTEGER(INT) 以及 SMALLINT。在此之外，MySQL 還額外提供了 TINYINT, MEDIUMINT, BIGINT 三種類型。所以一共是五種:
可以看到，INT 其實和我們通常用的 int32 是一樣的，本質是 2 的 31 次方 - 1，大概21億4千7百萬。（正整數以二進制存儲。負整數以補碼存儲。一個Int類型數據占據空間4字節。每個字節8位，共32位。因此最大存儲2的31次方（從2的0次方開始）。但32位的第一位是符號位。所以2的31次方減1.簡單說Int類型占據4字節，所以是這個取值范圍。）
這里 BigInt 就等價于 int64。
Datetime
datetime 其實是一個統稱，MySQL 提供了 DATE, DATETIME, TIMESTAMP 三種類型。
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
DATE 類型沒有具體的時間點，只能精確到【日期】，即 YYYY-MM-DD，比如 1994-06-09。
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
DATETIME 則同時支持【日期】和【時間】，格式為 YYYY-MM-DD hh:mm:ss。如 1995-04-29 17:11:12。
The timestamp data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
TIMESTAMP 同樣也支持【日期】和【時間】，但由于帶上了時間戳的語義，就不如 DATETIME 支持的范圍那么寬了。UTC 時間，從'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'
因為此前的系統設計都是基于 32 位實現的，我們上面提到過，最多無非是 2 的 31次方 - 1，每個數代表一秒的話，最多表示 68 年。所以 Unix 選取了 1970年1月1日作為UNIX TIME的紀元時間(開始時間)。
這里我們主要還是關心 DATETIME 以及 TIMESTAMP，二者除了整秒之外，還可以支持小數點后的部分，最多到 microseconds （6位）精度。格式為 'YYYY-MM-DD hh:mm:ss[.fraction]'，比如 '2038-01-19 03:14:07.999999' （事實上這也是 TIMESTAMP 能支持的最大值）。
除此之外，二者也都支持 自動初始化（Automatic Initialization)。這里要用到的兩個大殺器：
- DEFAULT CURRENT_TIMESTAMP
- ON UPDATE CURRENT_TIMESTAMP
二者可以同時出現，也可以單獨出現，分幾種情況：
- 同時出現
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 復制代碼
此時 ts 和 dt 的默認值就是當前時間，當這一行其他值發生變化時，也會自動把這兩個屬性更新為當前時間。
- 只有 DEFAULT
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ); 復制代碼
此時只有初始化的時候才會寫入當前時間，隨后更新時不會變動。（當然，我們也可以把 CURRENT_TIMESTAMP 換成一個常數，比如 0，語法上是支持的，只不過那樣就不是當前時間了）
- 只有 ON UPDATE
CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL ); 復制代碼
此時沒有指定默認值，但發生更新時會改為當前時間，這時的默認值就是 type dependent，依賴類型了。 TIMESTAMP 的默認值為 0，如果定義了 NULL 則默認值為 NULL。
CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 ); 復制代碼
這次我們換成了 DATETIME，二者正好相反，不指定 DEFAULT 的話，默認值為 NULL，但如果我們聲明了 NOT NULL，則默認值變成 0。
- 我們可以使用 show variables like '%explicit_defaults_for_timestamp%'; 來查看是否禁用了自動初始化和更新。
- 雖然在MySQL中可以對時間戳字段賦值或更新，但建議僅在必要的情況下對時間戳列進行顯式插入和更新。
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
TIMESTAMP 底層采用 4 個字節存儲（2的31次方-1，還記得么），能支持的時間范圍比 DATETIME 要小一倍，但它的特點在于，當我們寫入時，MySQL會根據當前 server 所在的時區進行轉換，將值變成 UTC 時區的時間，再存儲。同樣的，在查詢的時候，MySQL 也會幫助我們轉成當前時區再展示。這是 DATETIME 不具備的。
這樣的跨時區支持，在一些業務場景下是很有用的。畢竟存儲時間這件事情本身是很敏感的。海外用戶一開始請求到了新加坡機房，落了一個時間。隨后跑到歐洲玩耍，在法國重新訪問，發現跟本地時間完全對不上，這就有問題了。
所以 TIMESTAMP 的思路就是，大家都以 UTC 時間為準，這是個基線，不管你是哪個時區的，我都要轉成統一的時間，查詢的時候給你轉回去就是了。
我們可以用 show variables like '%time_zone%'; 來查看當前庫的時區：
需要注意，當MySQL參數time_zone=system時，查詢timestamp字段會調用系統時區做時區轉換，而由于系統時區存在全局鎖問題，在多并發大數據量訪問時會導致線程上下文頻繁切換，CPU使用率暴漲，系統響應變慢設置假死。
The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.
使用 SET TIME_ZONE = 'america/new_york"; 來設置時區。每個連接可以使用不同的時區
可以實驗一下，在一個時區寫入 TIMESTAMP 數據，切換時區后讀出來，顯示的時間是不一樣的，而 DATETIME 則是完全一致的。demo
DATETIME
DATETIME 底層采用 8 個字節存儲，沒有跨時區的支持，結果直接展示。你存進去的是什么時間，讀到的就是什么時間。不過我們如果需要跨時區，也不是沒有辦法，可以在讀出來 DATETIME 后轉為時間戳，從業務代碼層面來處理，想轉成什么時區都 OK。
這里不用擔心 2038 年的限制，雖然空間大了一倍，但通常情況下不會造成多大性能影響。
Integer
這里在討論完 DATETIME, TIMESTAMP 之后，我們回過頭來看看 Integer。
為什么我們能用一個整數來代表時間呢？這里本質是我們給它賦予了【時間戳】的語義。
雖然整數的上下限更大（比如我們用 BIGINT，可以支持 2 的 63 次方 - 1 的數據），但是，但是，用法是關鍵。
如果你打算還用時間戳函數進行生成和轉換，那就需要關注 2038 年這個限制，本質上和 TIMESTAMP 是沒有區別的。
所以，通常我們認為，用整型時間戳的形式，取值范圍也是 1970 年 1 月 1日起，到 2038 年截止，這個區間。用 BIGINT 的意義不大，只要它的語義還是時間戳，就需要遵循這個規范。
BETWEEN 查詢
回到我們一開始提到的案例，我們需要篩選出兩個時間點之間，有哪些【變更記錄】。
如果是整型，我們其實經常使用 BETWEEN 來進行查詢：
SELECT * FROM contacts WHERE contact_id BETWEEN 100 AND 200; 復制代碼
它和下面直接用運算符的形式是等價的，注意 BETWEEN 是個閉區間：
SELECT * FROM contacts WHERE contact_id >= 100 AND contact_id <= 200; 復制代碼
同樣的，查詢 datetime 依然可以用 BETWEEN:
SELECT * FROM `objects` WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55') 復制代碼
下面兩個查詢也是等價的：
SELECT count(*) FROM `table` where created_at>='2011-03-17 06:42:10' and created_at<='2011-03-17 07:42:50'; 復制代碼 SELECT count(*) FROM `table` where created_at between '2011-03-17 06:42:10' and '2011-03-17 07:42:50'; 復制代碼
當然，我們也可以用 now() 等函數作為輔助，注意 between 里面一定要先寫小的時間，and 后面寫更大的時間點。
性能差異
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.
其實 DATETIME 和 TIMESTAMP 底層也是整型存儲（否則就不會按照 2 的31 次方，63 次方來支持了），算是一層封裝，提供了一系列時間函數使用。
DATETIME 底層存儲實現是 BigInt，索引存儲上和 BigInt 的處理是幾乎一模一樣的，所以 BigInt 支持的索引查詢，datetime也支持。
加上索引后的速度如何，推薦大家閱讀這一篇 benchmark 性能效率比較
這里引用一下結論：
對于 MyISAM 引擎，不建立索引的情況下（推薦），效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時間比較）> timestamp（直接和時間比較）> UNIXTIMESTAMP(datetime) 。 對于 MyISAM 引擎，建立索引的情況下，效率從高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和時間比較）>timestamp（直接和時間比較）>UNIXTIMESTAMP(datetime) 。 對于 InnoDB 引擎，沒有索引的情況下(不建議)，效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIXTIMESTAMP(datetime)。 對于 InnoDB 引擎，建立索引的情況下，效率從高到低：int > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。 一句話，對于 MyISAM 引擎，采用 UNIX_TIMESTAMP(timestamp) 比較；對于InnoDB 引擎，建立索引，采用 int 或 datetime直接時間比較。
大家可以嘗試一下，結合你的業務場景，跑一下 explain 看看。
