MySQL字段時間類型該如何選擇實現(xiàn)千萬數(shù)據(jù)下性能提升10%~30%
MySQL字段時間類型如何選擇
在MySQL中時間類型的選擇有很多，比如：date、time、year、datetime、timestamp...
在某些情況下還會使用整形int、bigint來存儲時間戳
根據(jù)節(jié)省空間的原則，當只需要存儲年份、日期、時間時，可以使用year、date、time
如果需要詳細的時間，可以選擇datetime、timestamp或者使用整形來存儲時間戳
以下是不同類型的格式、時間范圍、占用空間相關信息
datetime
datetime不僅可以存儲日期、時間，還可以存儲小數(shù)點后續(xù)的毫秒等 YYYY-MM-DD hh:mm:ss[.fraction]
比如datetime(3) 就可以保留三位小數(shù) 2023-04-22 20:47:32.000
當datetime不保留小數(shù)時使用5 Byte，需要保留小數(shù)時多加3 Byte，總共8 Byte （5.6.X之后）
datetime是最常用的時間類型，在存儲、讀取的性能和數(shù)據(jù)庫可視化方面都不錯，但它只能展示固定的時間，如果在不同時區(qū)，看到的時間依舊是固定的，不會隨著時間變化
timestamp 時間戳
MySQL中的timestamp能有效的解決時區(qū)問題
timestamp用于存儲時間戳，在進行存儲時會先將時間戳轉(zhuǎn)換為UTC
UTC是世界統(tǒng)一時間，比如我們的時區(qū)為東八區(qū)，則是在UTC的基礎上增加八小時
時間戳在進行存儲時，先根據(jù)當前時區(qū)轉(zhuǎn)換成UTC，再轉(zhuǎn)換成int類型進行存儲
時間戳在進行讀取時，先將int類型轉(zhuǎn)換為UTC，再轉(zhuǎn)換為當前時區(qū)
當前時區(qū)指的是MySQL服務端本地時區(qū)，默認為系統(tǒng)時區(qū)，可以進行配置
當前時區(qū)發(fā)生變化時，讀取時間戳會發(fā)生變化
比如我的服務端默認系統(tǒng)為東八區(qū)（+8:00），當我修改為（+11:00）
[mysqld] default_time_zone = +11:00
讀取時，所有的timestamp都增加3小時
如果MySQL時區(qū)設置為系統(tǒng)時區(qū)（time_zone = SYSTEM）時，進行時區(qū)轉(zhuǎn)換會調(diào)用系統(tǒng)函數(shù)，高并發(fā)下開銷會很大
@Resource
private JdbcTemplate jdbcTemplate;
@Test
/**
* 10個線程每次查詢10次 一次查500條
* timestamp:11,978ms
* datetime:9,057ms
*/
void getTimestamp() throws BrokenBarrierException, InterruptedException {
String timestampSql = "select SQL_NO_CACHE test_timestamp from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' order by test_timestamp limit 500;";
String dateTimeSql = "select SQL_NO_CACHE test_datetime from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' order by test_datetime limit 500;";
CountDownLatch countDownLatch = new CountDownLatch(10);
long start = System.currentTimeMillis();
forQuery(timestampSql, countDownLatch);
countDownLatch.await();
//timestamp:11,978ms
System.out.println(MessageFormat.format("timestamp:{0}ms", System.currentTimeMillis() - start));
CountDownLatch countDownLatch2 = new CountDownLatch(10);
start = System.currentTimeMillis();
forQuery(dateTimeSql, countDownLatch2);
countDownLatch2.await();
//datetime:9,057ms
System.out.println(MessageFormat.format("datetime:{0}ms", System.currentTimeMillis() - start));
}
private void forQuery(String timestampSql, CountDownLatch countDownLatch) {
for (int j = 1; j <= 10; j++) {
new Thread(() -> {
for (int i = 0; i < 10; i++) {
jdbcTemplate.queryForList(timestampSql);
}
countDownLatch.countDown();
}).start();
}
}timestamp 時間戳使用整形進行存儲，占用4Byte空間
timestamp范圍有限'1970-01-01 00:00:01.000000'UTC 到'2038-01-19 03:14:07.499999'UTC ，2038年XX后的時間需要其他解決方案進行處理
timestamp當時區(qū)發(fā)生改變時讀取數(shù)據(jù)會有變化，由于存儲、讀取都需要根據(jù)時區(qū)對數(shù)據(jù)進行轉(zhuǎn)換，因此性能也會有一定的開銷，同時由于時間有限，還需要提供超出時間后的解決方案
整形時間戳
上文說到timestamp存儲時間戳使用整形來存儲，只是存儲、讀取會將時間戳轉(zhuǎn)換為當前時區(qū)的時間
其實我們還可以通過整形自己進行存儲，比如使用int直接存儲時間戳
但由于int整形只有4B（時間范圍有限），在未來可能無法進行存儲時間，就需要其他方案解決
為了避免空間太小，可以直接使用bigint 8B進行存儲
使用整形存儲時間戳不需要轉(zhuǎn)換成時區(qū)，因此沒有轉(zhuǎn)換的性能開銷，但無法顯示時間、可讀性不好，可以由我們自由進行時區(qū)轉(zhuǎn)換適合國際化
千萬數(shù)據(jù)測試
為了比較datetime、timestamp、bigint的性能，我們需要先搭建環(huán)境
案例只測試innodb存儲引擎有索引的情況，想測試其他情況的同學，可以使用以下腳本函數(shù)自由測試
首先拿出一個快過期的云服務器，然后在服務器上啟動MySQL，待會用函數(shù)狠狠的把它的CPU跑滿
搭建環(huán)境
查看是否開啟函數(shù)創(chuàng)建
#開啟函數(shù)創(chuàng)建 set global log_bin_trust_function_creators=1; #ON表示已開啟 show variables like 'log_bin_trust%';
創(chuàng)建表，表中數(shù)據(jù)類型為bigint、datetime、timestamp進行測試
（先不要創(chuàng)建索引，因為生成的時間是隨機無序的，維護索引的開銷會很大，等數(shù)據(jù)跑完后續(xù)再生成索引）
CREATE TABLE `datetime_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `money` int(11) DEFAULT NULL, `test_datetime` datetime DEFAULT NULL, `test_timestamp` timestamp NULL DEFAULT NULL, `test_bigint` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
隨機生成字符串的函數(shù)
#分割符從;改為$$ delimiter $$ #函數(shù)名ran_string 需要一個參數(shù)int類型 返回類型varchar(255) create function ran_string(n int) returns varchar(255) begin #聲明變量chars_str默認'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; #聲明變量return_str默認'' declare return_str varchar(255) default ''; #聲明變量i默認0 declare i int default 0; #循環(huán)條件 i<n while i < n do set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i=i+1; end while; return return_str; end $$
隨機生成整形的函數(shù)
#生成隨機num的函數(shù) delimiter $$ create function rand_num() returns int(5) begin declare i int default 0; set i=floor(100+rand()*10); return i; end $$
編寫插入函數(shù)
其中使用UNIX_TIMESTAMP函數(shù)將時間轉(zhuǎn)化為時間戳存入bigint中
#插入 從參數(shù)start開始 插入max_num條數(shù)據(jù)（未使用startc）
delimiter $$
create procedure insert_datetime_test(in start int(10),in max_num int(10))
begin
declare i int default 0;
declare random datetime default '2022-01-01 00:00:00';
set autocommit = 0;
repeat
set i = i+1;
set random = DATE_ADD('2022-01-01 00:00:00', INTERVAL FLOOR(RAND() * 31536000) SECOND);
#SQL 語句
insert into datetime_test(username,money,test_bigint,test_datetime,test_timestamp)
values (ran_string(8),rand_num(),UNIX_TIMESTAMP(random),random,random);
until i=max_num
end repeat;
commit;
end $$執(zhí)行
#執(zhí)行插入函數(shù) delimiter ; call insert_datetime_test(1,10000000);
我生成的是兩千萬條數(shù)據(jù)，想生成別的數(shù)量也可以設置call insert_datetime_test(1,10000000)
建索引
alter table datetime_test add index idx_datetime(test_datetime); alter table datetime_test add index idx_timestamp(test_timestamp); alter table datetime_test add index idx_bigint(test_bigint);
根據(jù)時間段查詢數(shù)據(jù)（需要回表）
與時間相關、最常見的功能就是根據(jù)時間段進行查詢數(shù)據(jù)，比如想查詢2022-10-10這一天的下單數(shù)據(jù)
為了模擬真實場景，這里將查詢列表設置為*，讓MySQL回表查詢其他數(shù)據(jù)
（回表：使用二級索引后，需要回表查詢聚簇【主鍵】索引獲取全部數(shù)據(jù)，可能導致隨機IO）
根據(jù)時間段查詢少量數(shù)據(jù)
select SQL_NO_CACHE * from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' order by test_datetime limit 20 > OK > 時間: 0.038s select SQL_NO_CACHE * from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' order by test_timestamp limit 20 > OK > 時間: 0.034s select SQL_NO_CACHE * from datetime_test where test_bigint >= 1665331200 and test_bigint <= 1665417600 order by test_bigint limit 20 > OK > 時間: 0.036s
由于數(shù)據(jù)量比較小，回表次數(shù)少、隨機IO少，會更傾向于使用索引
三種類型查詢時間差不多
根據(jù)時間段查詢大量數(shù)據(jù) （數(shù)據(jù)量5.5W）
一般也不會根據(jù)時間段一次性查這么多數(shù)據(jù)，主要是想看下性能
select SQL_NO_CACHE * from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' > OK > 時間: 37.084s select SQL_NO_CACHE * from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' > OK > 時間: 39.558s select SQL_NO_CACHE * from datetime_test where test_bigint >= 1665331200 and test_bigint <= 1665417600 > OK > 時間: 38.966s
主要的性能開銷是需要回表查數(shù)據(jù)，三種類型性能都差不多 datetime > bigint > timestamp
由于回表的開銷可能會影響我們的結果，因此還是要看不回表的案例
根據(jù)時間段查詢數(shù)據(jù)（不回表）
select SQL_NO_CACHE test_datetime from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' > OK > 時間: 8.478s select SQL_NO_CACHE test_timestamp from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' > OK > 時間: 9.063s select SQL_NO_CACHE test_bigint from datetime_test where test_bigint >= 1665331200 and test_bigint <= 1665417600 > OK > 時間: 5.773s
測試不用回表時，三種類型的性能差異還是比較顯著的，bigint > datetime > timestamp
但根據(jù)時間段不回表的查詢場景還是比較少的，除非用聯(lián)合索引，時間加上另一個需要的值
統(tǒng)計數(shù)量
根據(jù)時間統(tǒng)計數(shù)量的場景還是比較多的：統(tǒng)計某天、某月下單數(shù)量等...
統(tǒng)計部分數(shù)據(jù)
select SQL_NO_CACHE count(*) from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' > OK > 時間: 0.053s select SQL_NO_CACHE count(*) from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' > OK > 時間: 0.078s select SQL_NO_CACHE count(*) from datetime_test where test_bigint >= 1665331200 and test_bigint <= 1665417600 > OK > 時間: 0.049s
統(tǒng)計所有數(shù)據(jù)
select SQL_NO_CACHE count(*) from datetime_test > OK > 時間: 3.898s select SQL_NO_CACHE count(*) from datetime_test > OK > 時間: 4.152s select SQL_NO_CACHE count(*) from datetime_test > OK > 時間: 3.17s
統(tǒng)計數(shù)量count 可以直接使用二級索引，不需要回表
性能：bigint > datetime > timestamp
經(jīng)過不回表的測試bigint是性能最好的，與datetime相比性能提升在10%~30%之間
總結
當只需要存儲年份、日期、時間時，可以使用year、date、time，盡量使用少的空間
datetime性能不錯，方便可視化，固定時間，可以在不追求性能、方便可視化、不涉及時區(qū)的場景使用
timestamp性能較差，存儲時間戳，涉及時區(qū)轉(zhuǎn)換（如果是系統(tǒng)時區(qū)高并發(fā)下性能更差），有時間范圍限制，還需要為未來準備解決方案（感覺比較雞肋）
bigint性能最好，存儲時間戳，不方便可視化，由自己自由轉(zhuǎn)換時區(qū)，適合追求性能、國際化（時區(qū)轉(zhuǎn)換）、不注重DB可視化的場景，還不用考慮時間范圍，如果是短期不會超出2038年XX還可以使用空間更小的int整形
以上就是MySQL字段的時間類型該如何選擇？千萬數(shù)據(jù)下性能提升10%~30%的詳細內(nèi)容，更多關于MySQL字段時間類型選擇的資料請關注腳本之家其它相關文章！
相關文章
聯(lián)合索引也叫組合索引或多列索引,是指在MySQL中對一個表的多個列共同建立的索引,與單列索引不同,聯(lián)合索引是同時對多個列的值進行排序和存儲的索引結構,下面就來介紹一下如何使用2025-12-12
DataGrip連接Mysql并創(chuàng)建數(shù)據(jù)庫的方法實現(xiàn)
本文主要介紹了DataGrip連接Mysql并創(chuàng)建數(shù)據(jù)庫的方法實現(xiàn)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
想在Mysql命令行下導出數(shù)據(jù)庫，但就是每天提示不那個錯誤，后來才知道其實mysqldump不是mysql命令，因此不能在Mysql命令行下導出。2011-05-05
這篇文章主要介紹了mysql innodb 異常修復經(jīng)驗分享,需要的朋友可以參考下2017-04-04
本文主要介紹了MySQL不用like+%實現(xiàn)模糊查詢，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
這篇文章主要介紹了在linux命令下導出導入.sql文件的方法，具有很好的參考價值，給大家做個參考，跟隨小編過來看看吧2018-05-05
MySQL中利用索引對數(shù)據(jù)進行排序的基礎教程
這篇文章主要介紹了MySQL中利用索引對數(shù)據(jù)進行排序的基礎教程,需要的朋友可以參考下2015-11-11
MySQL存儲引擎 InnoDB與MyISAM的區(qū)別
InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優(yōu)劣，視具體應用而定。2014-03-03
MySQL是一種開源的關系型數(shù)據(jù)庫管理系統(tǒng),被廣泛應用于Web應用程序的后端數(shù)據(jù)存儲和管理,下面這篇文章主要介紹了mysql基礎教程之啟動與客戶端連接的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-10-10
MySQL 5.5 range分區(qū)增加刪除處理的方法示例
這篇文章主要給大家介紹了關于MySQL 5.5 range分區(qū)增加刪除處理的相關資料，文中通過示例代碼介紹的非常詳細，對大家具有一定的參考學習價值，需要的朋友們下面來一起看看吧。2017-06-06