MYSQL?數(shù)據(jù)庫時間字段?INT,TIMESTAMP,DATETIME?性能效率的比較介紹
更新時間：2022年09月16日 15:38:23 作者：周三不加班
這篇文章主要介紹了MYSQL數(shù)據(jù)庫時間字段INT,TIMESTAMP,DATETIME性能效率的比較介紹，文章通過圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下
前言：
在數(shù)據(jù)庫設(shè)計的時候，我們經(jīng)常會需要設(shè)計時間字段，在 MYSQL 中，時間字段可以使用 int、timestamp、datetime 三種類型來存儲，那么這三種類型哪一種用來存儲時間性能比較高，效率好呢 ？
就這個問題，來一個實踐出真知吧。
一、準(zhǔn)備工作
1.1 建表
CREATE TABLE IF NOT EXISTS `datetime_test` ( `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1, `d_int` int(11) NOT NULL DEFAULT '0', `d_timestamp` timestamp NULL DEFAULT NULL, `d_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;
1.2 插入100萬條測試數(shù)據(jù)
//插入d_intvalue=1到100萬之間的數(shù)據(jù) insert into datetime_test(d_int,d_timestamp,d_datetime) values(d_intvalue,FROM_UNIXTIME(d_intvalue),FROM_UNIXTIME(d_intvalue));
取中間的 20 萬條做查詢測試：
SELECT FROM_UNIXTIME(400000), FROM_UNIXTIME(600000) 1970-01-05 23:06:40, 1970-01-08 06:40:00
二、MyISAM引擎
2.1 MyISAM 引擎無索引下的 dint/dtimestamp/d_datetime
2.1.1 int 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
//SQL_NO_CACHE意思是說查詢時不適用緩存
SELECT SQL_NO_CACHE count(id) FROM `datetime_test`
WHERE d_int >400000 AND d_int<600000
查詢花費(fèi) 0.0780 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test`
WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40')
AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查詢花費(fèi) 0.0780 秒效率不錯
2.1.2 timestamp 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00' 查詢花費(fèi) 0.4368 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000 查詢花費(fèi) 0.0780 秒
對于 timestamp 類型，使用UNIX_TIMESTAMP內(nèi)置函數(shù)查詢效率很高，幾乎和int相當(dāng)；直接和日期比較效率低。
2.1.3 datetime 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00' 查詢花費(fèi) 0.1370 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000 查詢花費(fèi) 0.7498 秒
對于 datetime 類型，使用 UNIX_TIMESTAMP 內(nèi)置函數(shù)查詢效率很低，不建議；直接和日期比較，效率還行。
2.2 MyISAM 引擎有索引下的 dint/dtimestamp/d_datetime
2.2.1 int 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test`
WHERE d_int >400000
AND d_int<600000
查詢花費(fèi) 0.3900 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test`
WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40')
AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查詢花費(fèi) 0.3824 秒對于 int 類型，有索引的效率反而低了，筆者估計是由于設(shè)計的表結(jié)構(gòu)問題，多了索引，反倒多了一個索引查找。
2.2.2 timestamp 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00' 查詢花費(fèi) 0.5696 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000 查詢花費(fèi) 0.0780 秒
對于 timestamp 類型，有沒有索引貌似區(qū)別不大。
2.2.3 datetime 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00' 查詢花費(fèi) 0.4508 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000 查詢花費(fèi) 0.7614 秒
對于 datetime 類型，有索引反而效率低了。
三、InnoDB引擎
3.1 InnoDB 引擎無索引下的 dint/dtimestamp/d_datetime
3.1.1 int 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2`
WHERE d_int >400000
AND d_int<600000
查詢花費(fèi) 0.3198 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40')
AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查詢花費(fèi) 0.3092 秒InnoDB 引擎的查詢效率明細(xì)比 MyISAM 引擎的低，低 3 倍+。
3.1.2 timestamp 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00' 查詢花費(fèi) 0.7092 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000 查詢花費(fèi) 0.3160 秒
對于 timestamp 類型，使用 UNIX_TIMESTAMP 內(nèi)置函數(shù)查詢效率同樣高出直接和日期比較。
3.1.3 datetime 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00' 查詢花費(fèi) 0.3834 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000 查詢花費(fèi) 0.9794 秒
對于 datetime 類型，直接和日期比較，效率高于 UNIX_TIMESTAMP 內(nèi)置函數(shù)查詢。
3.2 InnoDB 引擎無索引下的 dint/dtimestamp/d_datetime
3.2.1 int 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2`
WHERE d_int >400000
AND d_int<600000
查詢花費(fèi) 0.0522 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2`
WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40')
AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
查詢花費(fèi) 0.0624 秒InnoDB引 擎有了索引之后，性能較 MyISAM 有大幅提高。
3.2.2 timestamp 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00' 查詢花費(fèi) 0.1776 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000 查詢花費(fèi) 0.2944 秒
對于 timestamp 類型，有了索引，反倒不建議使用 MYSQL 內(nèi)置函數(shù)UNIX_TIMESTAMP 查詢了。
3.2.3 datetime 類型是否調(diào)用 UNIX_TIMESTAMP 優(yōu)化對比
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00' 查詢花費(fèi) 0.0820 秒 SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000 查詢花費(fèi) 0.9994 秒
對于 datetime 類型，同樣有了索引，反倒不建議使用 MYSQL 內(nèi)置函數(shù)UNIX_TIMESTAMP 查詢了。
四、總結(jié)
- 對于 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直接時間比較。
到此這篇關(guān)于MYSQL 數(shù)據(jù)庫時間字段 INT,TIMESTAMP,DATETIME 性能效率的比較介紹的文章就介紹到這了,更多相關(guān)MYSQL 時間字段 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在本篇文章里小編給大家整理的是一篇關(guān)于MySQL給新建用戶并賦予權(quán)限最簡單的方法，需要的朋友們參考下。2019-11-11
Mysql 錯誤問題匯總，我們在開發(fā)過程中經(jīng)常看到mysql的一些錯誤，這里整理下，方便需要的朋友2012-07-07
本文主要介紹了MySQL中符號@的作用，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-06-06
emoji表情在我們?nèi)粘ｉ_發(fā)中經(jīng)常會遇到，但是最近在mysql中插入emoji表情遇到了問題，通過查找相關(guān)的資料終于解決了，所以將這次解決的過程分享出來，這篇文章主要就給大家介紹了MySQL插入emoji表情失敗問題的解決方法，需要的朋友可以參考。2017-05-05
這篇文章主要介紹了MySQL中如何給一個字段遞增賦值問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-07-07
Xtrabackup 是percona公司的開源項目，用以實現(xiàn)類似innodb官方的熱備份工具InnoDB Hot Backup的功能，能夠非常快速地備份與恢復(fù)MySQL數(shù)據(jù)庫。今天我們就來詳細(xì)討論下Xtrabackup的使用方法2016-11-11
這篇文章主要為大家詳細(xì)介紹了mac系統(tǒng)下mysql 8.0.11 安裝指南，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
本文主要介紹了mysql主從復(fù)制的實現(xiàn)步驟，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-10-10
本文給大家分享ubuntu下apt-get安裝和徹底卸載mysql的方法，文中提到了安裝mysq命令查看mysql是否運(yùn)行的命令寫法，具體內(nèi)容詳情大家通過本文學(xué)習(xí)吧2017-08-08
這篇文章主要介紹了mysql中怎樣使用合適的字段和字段長度問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11