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