問題

今天我們來探討一個(gè)有意思的問題，先說場景：

• 這是一個(gè)做在線文檔產(chǎn)品的業(yè)務(wù)，需要給用戶展示文檔的編輯記錄，現(xiàn)在我們叫它【智能文檔】。
• 智能文檔會不定期給文檔數(shù)據(jù)打一個(gè)快照，保存起來。用戶可以在歷史記錄中查閱快照。
• 快照之間會展示具體的變更記錄，比如“用戶A 復(fù)制了一段文字”，“用戶B刪除了一個(gè)圖片”。
• 快照本身是動態(tài)生成和回收的，即距離現(xiàn)在越遠(yuǎn)的快照，留下來的越少（更稀疏的快照意味著相鄰快照之間的變更記錄會更多，本來是一天一個(gè)快照，展示這一天內(nèi)的變更記錄即可，后來變成了一周一個(gè)快照，于是需要展示這一周內(nèi)的變更記錄）

那如何實(shí)現(xiàn)查找兩個(gè)快照之間的【變更記錄】有哪些呢？

快照 和 變更記錄 預(yù)期是兩張表。首先我們不能將【變更記錄】通過 id 掛在某個(gè)【快照】上，因?yàn)槲覀兊目煺帐遣粩啾换厥盏?，這樣的話當(dāng)你回收快照時(shí)，也需要連帶著更新大量的【變更記錄】，出現(xiàn)寫擴(kuò)散。

另一個(gè)想法是，能否通過時(shí)間戳進(jìn)行比較？比如快照 A 的創(chuàng)建時(shí)間戳是 12345，快照 B 的創(chuàng)建時(shí)間戳是 23456。那么我只要【變更記錄】這張表也有一個(gè)時(shí)間戳字段，寫一個(gè) SQL 查到兩個(gè)快照時(shí)間戳之間的變更記錄是不是就可以了？

寫出來 SQL 類似這樣：

select * from change_record where create_time > 12345 and create_time < 23456;

那么，問題來了，這個(gè) create_time，雖然這里我們直接拿時(shí)間戳比較，但真的是性能最好的么？建表的時(shí)候，我應(yīng)該用 datetime, timestamp 還是 int ?

今天我們就來看看到底有什么區(qū)別。

MySQL 支持的數(shù)據(jù)類型

任何一篇博客，教程都比不上官方文檔，大家選型有疑慮時(shí)還是建議先來看看 MySQL Data Types 。

Integer

我們先來看 integer 有什么類型。

SQL 標(biāo)準(zhǔn)中對于整數(shù)，提出了兩種類型：INTEGER(INT) 以及 SMALLINT。在此之外，MySQL 還額外提供了 TINYINT, MEDIUMINT, BIGINT 三種類型。

所以一共是五種:

可以看到，INT 其實(shí)和我們通常用的 int32 是一樣的，本質(zhì)是 2 的 31 次方 - 1，大概21億4千7百萬。（正整數(shù)以二進(jìn)制存儲。負(fù)整數(shù)以補(bǔ)碼存儲。一個(gè)Int類型數(shù)據(jù)占據(jù)空間4字節(jié)。每個(gè)字節(jié)8位，共32位。因此最大存儲2的31次方（從2的0次方開始）。但32位的第一位是符號位。所以2的31次方減1.簡單說Int類型占據(jù)4字節(jié)，所以是這個(gè)取值范圍。）

這里 BIGINT 就等價(jià)于 int64。

Datetime

datetime 其實(shí)是一個(gè)統(tǒng)稱，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 類型沒有具體的時(shí)間點(diǎn)，只能精確到【日期】，即 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 則同時(shí)支持【日期】和【時(shí)間】，格式為 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 同樣也支持【日期】和【時(shí)間】，但由于帶上了時(shí)間戳的語義，就不如 DATETIME 支持的范圍那么寬了。UTC 時(shí)間，從'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'

因?yàn)榇饲暗南到y(tǒng)設(shè)計(jì)都是基于 32 位實(shí)現(xiàn)的，我們上面提到過，最多無非是 2 的 31次方 - 1，每個(gè)數(shù)代表一秒的話，最多表示 68 年。所以 Unix 選取了 1970年1月1日作為UNIX TIME的紀(jì)元時(shí)間(開始時(shí)間)。

這里我們主要還是關(guān)心 DATETIME 以及 TIMESTAMP，二者除了整秒之外，還可以支持小數(shù)點(diǎn)后的部分，最多到 microseconds （6位）精度。格式為 'YYYY-MM-DD hh:mm:ss[.fraction]'，比如 '2038-01-19 03:14:07.999999' （事實(shí)上這也是 TIMESTAMP 能支持的最大值）。

除此之外，二者也都支持 自動初始化（Automatic Initialization)。這里要用到的兩個(gè)大殺器：

• DEFAULT CURRENT_TIMESTAMP
• ON UPDATE CURRENT_TIMESTAMP

二者可以同時(shí)出現(xiàn)，也可以單獨(dú)出現(xiàn)，分幾種情況：

• 同時(shí)出現(xiàn)

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

此時(shí) ts 和 dt 的默認(rèn)值就是當(dāng)前時(shí)間，當(dāng)這一行其他值發(fā)生變化時(shí)，也會自動把這兩個(gè)屬性更新為當(dāng)前時(shí)間。

• 只有 DEFAULT

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

此時(shí)只有初始化的時(shí)候才會寫入當(dāng)前時(shí)間，隨后更新時(shí)不會變動。（當(dāng)然，我們也可以把 CURRENT_TIMESTAMP 換成一個(gè)常數(shù)，比如 0，語法上是支持的，只不過那樣就不是當(dāng)前時(shí)間了）

• 只有 ON UPDATE

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
  );

此時(shí)沒有指定默認(rèn)值，但發(fā)生更新時(shí)會改為當(dāng)前時(shí)間，這時(shí)的默認(rèn)值就是 type dependent，依賴類型了。 TIMESTAMP 的默認(rèn)值為 0，如果定義了 NULL 則默認(rèn)值為 NULL。

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, 
  -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
  );

這次我們換成了 DATETIME，二者正好相反，不指定 DEFAULT 的話，默認(rèn)值為 NULL，但如果我們聲明了 NOT NULL，則默認(rèn)值變成 0。

• 我們可以使用 show variables like '%explicit_defaults_for_timestamp%'; 來查看是否禁用了自動初始化和更新。

• 雖然在MySQL中可以對時(shí)間戳字段賦值或更新，但建議僅在必要的情況下對時(shí)間戳列進(jìn)行顯式插入和更新。

TIMESTAMP

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 個(gè)字節(jié)存儲（2的31次方-1，還記得么），能支持的時(shí)間范圍比 DATETIME 要小一倍，但它的特點(diǎn)在于，當(dāng)我們寫入時(shí)，MySQL會根據(jù)當(dāng)前 server 所在的時(shí)區(qū)進(jìn)行轉(zhuǎn)換，將值變成 UTC 時(shí)區(qū)的時(shí)間，再存儲。同樣的，在查詢的時(shí)候，MySQL 也會幫助我們轉(zhuǎn)成當(dāng)前時(shí)區(qū)再展示。這是 DATETIME 不具備的。

這樣的跨時(shí)區(qū)支持，在一些業(yè)務(wù)場景下是很有用的。畢竟存儲時(shí)間這件事情本身是很敏感的。海外用戶一開始請求到了新加坡機(jī)房，落了一個(gè)時(shí)間。隨后跑到歐洲玩耍，在法國重新訪問，發(fā)現(xiàn)跟本地時(shí)間完全對不上，這就有問題了。

所以 TIMESTAMP 的思路就是，大家都以 UTC 時(shí)間為準(zhǔn)，這是個(gè)基線，不管你是哪個(gè)時(shí)區(qū)的，我都要轉(zhuǎn)成統(tǒng)一的時(shí)間，查詢的時(shí)候給你轉(zhuǎn)回去就是了。

我們可以用 show variables like '%time_zone%'; 來查看當(dāng)前庫的時(shí)區(qū)：

需要注意，當(dāng)MySQL參數(shù)time_zone=system時(shí)，查詢timestamp字段會調(diào)用系統(tǒng)時(shí)區(qū)做時(shí)區(qū)轉(zhuǎn)換，而由于系統(tǒng)時(shí)區(qū)存在全局鎖問題，在多并發(fā)大數(shù)據(jù)量訪問時(shí)會導(dǎo)致線程上下文頻繁切換，CPU使用率暴漲，系統(tǒng)響應(yīng)變慢設(shè)置假死。

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"; 來設(shè)置時(shí)區(qū)。每個(gè)連接可以使用不同的時(shí)區(qū)

可以實(shí)驗(yàn)一下，在一個(gè)時(shí)區(qū)寫入 TIMESTAMP 數(shù)據(jù)，切換時(shí)區(qū)后讀出來，顯示的時(shí)間是不一樣的，而 DATETIME 則是完全一致的。demo

DATETIME

DATETIME 底層采用 8 個(gè)字節(jié)存儲，沒有跨時(shí)區(qū)的支持，結(jié)果直接展示。你存進(jìn)去的是什么時(shí)間，讀到的就是什么時(shí)間。不過我們?nèi)绻枰鐣r(shí)區(qū)，也不是沒有辦法，可以在讀出來 DATETIME 后轉(zhuǎn)為時(shí)間戳，從業(yè)務(wù)代碼層面來處理，想轉(zhuǎn)成什么時(shí)區(qū)都 OK。

這里不用擔(dān)心 2038 年的限制，雖然空間大了一倍，但通常情況下不會造成多大性能影響。

Integer

這里在討論完 DATETIME, TIMESTAMP 之后，我們回過頭來看看 Integer。

為什么我們能用一個(gè)整數(shù)來代表時(shí)間呢？這里本質(zhì)是我們給它賦予了【時(shí)間戳】的語義。

雖然整數(shù)的上下限更大（比如我們用 BIGINT，可以支持 2 的 63 次方 - 1 的數(shù)據(jù)），但是，但是，用法是關(guān)鍵。

如果你打算還用時(shí)間戳函數(shù)進(jìn)行生成和轉(zhuǎn)換，那就需要關(guān)注 2038 年這個(gè)限制，本質(zhì)上和 TIMESTAMP 是沒有區(qū)別的。

所以，通常我們認(rèn)為，用整型時(shí)間戳的形式，取值范圍也是 1970 年 1 月 1日起，到 2038 年截止，這個(gè)區(qū)間。用 BIGINT 的意義不大，只要它的語義還是時(shí)間戳，就需要遵循這個(gè)規(guī)范。

BETWEEN 查詢

回到我們一開始提到的案例，我們需要篩選出兩個(gè)時(shí)間點(diǎn)之間，有哪些【變更記錄】。

如果是整型，我們其實(shí)經(jīng)常使用 BETWEEN 來進(jìn)行查詢：

SELECT *
FROM contacts
WHERE contact_id BETWEEN 100 AND 200;

它和下面直接用運(yùn)算符的形式是等價(jià)的，注意 BETWEEN 是個(gè)閉區(qū)間：

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

下面兩個(gè)查詢也是等價(jià)的：

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

當(dāng)然，我們也可以用 now() 等函數(shù)作為輔助，注意 between 里面一定要先寫小的時(shí)間，and 后面寫更大的時(shí)間點(diǎn)。

性能差異

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.

其實(shí) DATETIME 和 TIMESTAMP 底層也是整型存儲（否則就不會按照 2 的31 次方，63 次方來支持了），算是一層封裝，提供了一系列時(shí)間函數(shù)使用。

DATETIME 底層存儲實(shí)現(xiàn)是 BigInt，索引存儲上和 BigInt 的處理是幾乎一模一樣的，所以 BigInt 支持的索引查詢，datetime也支持。

加上索引后的速度如何，推薦大家閱讀這一篇 benchmark MYSQL 數(shù)據(jù)庫時(shí)間字段 INT,TIMESTAMP,DATETIME 性能效率的比較介紹

這里引用一下結(jié)論：

• 對于 MyISAM 引擎，不建立索引的情況下（推薦），效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時(shí)間比較）> timestamp（直接和時(shí)間比較）> UNIXTIMESTAMP(datetime) 。
• 對于 MyISAM 引擎，建立索引的情況下，效率從高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和時(shí)間比較）>timestamp（直接和時(shí)間比較）>UNIXTIMESTAMP(datetime) 。
• 對于 InnoDB 引擎，沒有索引的情況下(不建議)，效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時(shí)間比較） > timestamp（直接和時(shí)間比較）> UNIXTIMESTAMP(datetime)。
• 對于 InnoDB 引擎，建立索引的情況下，效率從高到低：int > datetime（直接和時(shí)間比較） > timestamp（直接和時(shí)間比較）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。
• 一句話，對于 MyISAM 引擎，采用 UNIX_TIMESTAMP(timestamp) 比較；對于InnoDB 引擎，建立索引，采用 int 或 datetime直接時(shí)間比較。

大家可以嘗試一下，結(jié)合你的業(yè)務(wù)場景，跑一下 explain 看看。

到此這篇關(guān)于MySQL 時(shí)間類型用 datetime, timestamp 還是 integer 更好的文章就介紹到這了,更多相關(guān)MySQL 時(shí)間類型使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論