在優(yōu)化索引時(shí)，思考了一個(gè)問題，DATE, DATETIME, TIMESTAMP，還有INT存儲(chǔ)的時(shí)間，在索引中哪個(gè)效率更高一些？
索引存儲(chǔ)的，如果單純的測(cè)試，而不去了解底層存儲(chǔ)的方式和類型就不能斷言哪個(gè)類型的效率更好一些。
DATE: （YYYY-MM-DD） 范圍 '1000-01-01' to '9999-12-31' .DATETIME: （YYYY-MM-DD HH:MM:SS） 范圍 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 支持自動(dòng)更新TIMESTAMP: （YYYY-MM-DD HH:MM:SS）范圍 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 支持自動(dòng)更新-
INT： (整數(shù)) 范圍 '1970-01-01 00:00:01' to '2038-01-19 03:14:07'
（這里 DATETIME，TIMESTAMP都支持0-6位的時(shí)間精度，這里就不講了）
注意 TIMESTAMP 會(huì)先將用戶輸入轉(zhuǎn)換成UTC時(shí)間進(jìn)行存儲(chǔ)，查詢時(shí)也會(huì)將時(shí)間轉(zhuǎn)換成當(dāng)前UTC時(shí)間，所以當(dāng)數(shù)據(jù)庫服務(wù)器的時(shí)區(qū)跟查詢客戶端時(shí)區(qū)不一致時(shí)，會(huì)出現(xiàn)查詢不到的情況。
再看MYSQL對(duì)這個(gè)幾個(gè)類型的存儲(chǔ)情況。
| 類型 | before MySQL 5.6.4 | Storage as of MySQL 5.6.4 |
|---|---|---|
| DATE | 3 bytes, 小端字節(jié) | 3 bytes, 小端字節(jié) |
| DATE | 3 bytes, 小端字節(jié) | 3 bytes, 小端字節(jié) |
| TIMESTAMP | 4 bytes, 小端字節(jié) | 4 bytes + 毫秒存儲(chǔ), 大端字節(jié) |
| DATETIME | 8 bytes, 小端字節(jié) | 5 bytes + 毫秒存儲(chǔ), 大端字節(jié) |
| INT | 4 bytes | 4 bytes |
- DATE : 3個(gè)字節(jié)整型 YYYY×16×32 + MM×32 + DD
- TIMESTAMP : 4個(gè)字節(jié)整型，存儲(chǔ)UTC時(shí)間秒。
- DATETIME:
1.（5.6.4 之前）8個(gè)字節(jié)， 其中4個(gè)字節(jié)整型 YYYY×10000 + MM×100 + DD 和 4個(gè)字節(jié)整型HH×10000 + MM×100 + SS
2.（5.6.4 之后）
1 bit 符號(hào)位 (1= 整數(shù), 0=負(fù)數(shù))
17 bits year*13+month (year 0-9999, month 0-12)
5 bits day (0-31)
5 bits hour (0-23)
6 bits minute (0-59)
6 bits second (0-59)
---------------------------
40 bits = 5 bytes
- INT 4個(gè)字節(jié)整型，存儲(chǔ)時(shí)間秒
毫秒存儲(chǔ)位
| 精度 | 存儲(chǔ)空間 |
|---|---|
| 0 | bytes |
| 1，2 | 1 bytes |
| 3，4 | 2 bytes |
| 4，6 | 3 bytes |
對(duì)各個(gè)時(shí)間類型有個(gè)整體的了解以后我們分析一下。
其中DATE字段精度不夠不參與對(duì)比。
對(duì)比一下 DATETIME 、 TIMESTAMP、 INT
- 從存儲(chǔ)時(shí)間范圍看 DATETIME > TIMESTAMP = INT
- 從使用方便上看 帶有自動(dòng)更新 DATETIME , TIMESTAMP > INT
- 從時(shí)間精度看 DATETIME > TIMESTAMP > INT
- 從存儲(chǔ)空間上看,同一精度下DATETIME > TIMESTAMP = INT
- 從查詢速度上看，底層都是整型存儲(chǔ)，所以 DATETIME = TIMESTAMP = INT
不考慮存儲(chǔ)空間優(yōu)化的情況下，DATETIME是最優(yōu)的時(shí)間存儲(chǔ)類型
考慮存儲(chǔ)空間的情況下，TIMESTAMP是最優(yōu)的時(shí)間存儲(chǔ)類型
參考文檔
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html
