MYSQL 資料庫時間欄位 INT,TIMESTAMP,DATETIME 效能效率的比較介紹

2022-09-16 22:04:43

一、準備工作

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萬條測試資料

//插入d_intvalue=1到100萬之間的資料
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 型別是否呼叫 UNIX_TIMESTAMP 優化對比

//SQL_NO_CACHE意思是說查詢時不適用快取
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE d_int >400000 AND d_int<600000

查詢花費 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')

查詢花費 0.0780 秒

效率不錯

2.1.2 timestamp 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'

查詢花費 0.4368 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE UNIX_TIMESTAMP(d_timestamp)>400000 
AND UNIX_TIMESTAMP(d_timestamp)<600000

查詢花費 0.0780 秒

對於 timestamp 型別，使用UNIX_TIMESTAMP內建函數查詢效率很高，幾乎和int相當；直接和日期比較效率低。

2.1.3 datetime 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.1370 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE UNIX_TIMESTAMP(d_datetime)>400000 
AND UNIX_TIMESTAMP(d_datetime)<600000
查詢花費 0.7498 秒

對於 datetime 型別，使用 UNIX_TIMESTAMP 內建函數查詢效率很低，不建議；直接和日期比較，效率還行。

2.2 MyISAM 引擎有索引下的 dint/dtimestamp/d_datetime

2.2.1 int 型別是否呼叫 UNIX_TIMESTAMP 優化對比

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE d_int >400000
AND d_int<600000
查詢花費 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')
查詢花費 0.3824 秒

對於 int 型別，有索引的效率反而低了，筆者估計是由於設計的表結構問題，多了索引，反倒多了一個索引查詢。

2.2.2 timestamp 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.5696 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE UNIX_TIMESTAMP(d_timestamp)>400000 
AND UNIX_TIMESTAMP(d_timestamp)<600000
查詢花費 0.0780 秒

對於 timestamp 型別，有沒有索引貌似區別不大。

2.2.3 datetime 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.4508 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test` 
WHERE UNIX_TIMESTAMP(d_datetime)>400000 
AND UNIX_TIMESTAMP(d_datetime)<600000
查詢花費 0.7614 秒

對於 datetime 型別，有索引反而效率低了。

三、InnoDB引擎

3.1 InnoDB 引擎無索引下的 dint/dtimestamp/d_datetime

3.1.1 int 型別是否呼叫 UNIX_TIMESTAMP 優化對比

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE d_int >400000 
AND d_int<600000
查詢花費 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')
查詢花費 0.3092 秒

InnoDB 引擎的查詢效率明細比 MyISAM 引擎的低，低 3 倍+。

3.1.2 timestamp 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.7092 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE UNIX_TIMESTAMP(d_timestamp)>400000 
AND UNIX_TIMESTAMP(d_timestamp)<600000
查詢花費 0.3160 秒

對於 timestamp 型別，使用 UNIX_TIMESTAMP 內建函數查詢效率同樣高出直接和日期比較。

3.1.3 datetime 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.3834 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE UNIX_TIMESTAMP(d_datetime)>400000 
AND UNIX_TIMESTAMP(d_datetime)<600000
查詢花費 0.9794 秒

對於 datetime 型別，直接和日期比較，效率高於 UNIX_TIMESTAMP 內建函數查詢。

3.2 InnoDB 引擎無索引下的 dint/dtimestamp/d_datetime

3.2.1 int 型別是否呼叫 UNIX_TIMESTAMP 優化對比

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE d_int >400000 
AND d_int<600000
查詢花費 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')
查詢花費 0.0624 秒

InnoDB引擎有了索引之後，效能較 MyISAM 有大幅提高。

3.2.2 timestamp 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.1776 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE UNIX_TIMESTAMP(d_timestamp)>400000 
AND UNIX_TIMESTAMP(d_timestamp)<600000
查詢花費 0.2944 秒

對於 timestamp 型別，有了索引，反倒不建議使用 MYSQL 內建函數UNIX_TIMESTAMP 查詢了。

3.2.3 datetime 型別是否呼叫 UNIX_TIMESTAMP 優化對比

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'
查詢花費 0.0820 秒
SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` 
WHERE UNIX_TIMESTAMP(d_datetime)>400000 
AND UNIX_TIMESTAMP(d_datetime)<600000
查詢花費 0.9994 秒

對於 datetime 型別，同樣有了索引，反倒不建議使用 MYSQL 內建函數UNIX_TIMESTAMP 查詢了。

四、總結

對於 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直接時間比較。

到此這篇關於MYSQL 資料庫時間欄位 INT,TIMESTAMP,DATETIME 效能效率的比較介紹的文章就介紹到這了,更多相關MYSQL 時間欄位內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！