在數(shù)據(jù)庫設(shè)計的時候，我們經(jīng)常會需要設(shè)計時間字段，在MYSQL中，時間字段可以使用int、timestamp、datetime三種類型來存儲，那么這三種類型哪一種用來存儲時間性能比較高，效率好呢？飄易就這個問題，來一個實踐出真知吧。
MYSQL版本號：5.5.19
建立表：
CREATE TABLE IF NOT EXISTS `datetime_test` ( `id` int(11) NOT NULL, `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;ALTER TABLE `datetime_test` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;
插入100萬條測試數(shù)據(jù)：
- <?php header( 'Content-Type: text/html; charset=UTF-8' );
- set_time_limit(300); //最大執(zhí)行時間這里設(shè)置300秒
- //連接數(shù)據(jù)庫
- $pdo = new PDO("mysql:host=localhost;dbname=test","root","123");
- for ($i = 1; $i <= 1000000; $i++) {
- $d_int=$i;
- $pdo->exec("insert into datetime_test(d_int,d_timestamp,d_datetime)
- values($d_int,FROM_UNIXTIME($d_int),FROM_UNIXTIME($d_int))");
- }
取中間的20萬條做查詢測試：
SELECT FROM_UNIXTIME(400000), FROM_UNIXTIME(600000)1970-01-05 23:06:40, 1970-01-08 06:40:00
第一種情況，MyISAM引擎， d_int/d_timestamp/d_datetime這三個字段都沒有索引
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 秒效率不錯。
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內(nèi)置函數(shù)查詢效率很高，幾乎和int相當(dāng)；直接和日期比較效率低。
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內(nèi)置函數(shù)查詢效率很低，不建議；直接和日期比較，效率還行。
第二種情況，MyISAM引擎， d_int/d_timestamp/d_datetime這三個字段都有索引
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類型，有索引的效率反而低了，飄易的猜測是由于設(shè)計的表結(jié)構(gò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'查詢花費 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類型，有沒有索引貌似區(qū)別不大。
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引擎， d_int/d_timestamp/d_datetime這三個字段都沒有索引
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引擎的查詢效率明細(xì)比MyISAM引擎的低，低3倍+。
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內(nèi)置函數(shù)查詢效率同樣高出直接和日期比較。
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內(nèi)置函數(shù)查詢。
第四種情況，InnoDB引擎， d_int/d_timestamp/d_datetime這三個字段都有索引
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有大幅提高。
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內(nèi)置函數(shù)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內(nèi)置函數(shù)UNIX_TIMESTAMP查詢了。
【總結(jié)】：
對于MyISAM引擎，不建立索引的情況下（推薦），效率從高到低：int > UNIX_TIMESTAMP(timestamp) > datetime（直接和時間比較）>timestamp（直接和時間比較）>UNIX_TIMESTAMP(datetime) 。
對于MyISAM引擎，建立索引的情況下，效率從高到低： UNIX_TIMESTAMP(timestamp) > int > datetime（直接和時間比較）>timestamp（直接和時間比較）>UNIX_TIMESTAMP(datetime) 。
對于InnoDB引擎，沒有索引的情況下(不建議)，效率從高到低：int > UNIX_TIMESTAMP(timestamp) > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIX_TIMESTAMP(datetime)。
對于InnoDB引擎，建立索引的情況下，效率從高到低：int > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。
一句話，對于MyISAM引擎，采用 UNIX_TIMESTAMP(timestamp) 比較；對于InnoDB引擎，建立索引，采用 int 或 datetime直接時間比較。
