主頁 > 知識庫 > MySQL存儲時間類型選擇的問題講解

MySQL存儲時間類型選擇的問題講解

熱門標簽:電銷機器人各個細節(jié)介紹 昆明電信400電話辦理 電銷機器人 行業(yè) 俄國地圖標注app 溫州瑞安400電話怎么申請 電話機器人市場趨勢 南昌高頻外呼系統(tǒng)哪家公司做的好 百度地圖標注后不顯示 淄博400電話申請

MySQL中存儲時間通常會用datetime類型,但現(xiàn)在很多系統(tǒng)也用int存儲unix時間戳,它們有什么區(qū)別?本人總結如下:

int

(1)4個字節(jié)存儲,INT的長度是4個字節(jié),存儲空間上比datatime少,int索引存儲空間也相對較小,排序和查詢效率相對較高一點點

(2)可讀性極差,無法直觀的看到數(shù)據(jù)

TIMESTAMP

(1)4個字節(jié)儲存

(2)值以UTC格式保存

(3)時區(qū)轉化 ,存儲時對當前的時區(qū)進行轉換,檢索時再轉換回當前的時區(qū)。

(4)TIMESTAMP值不能早于1970或晚于2037

datetime

(1)8個字節(jié)儲存

(2)與時區(qū)無關

(3)以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

隨著Mysql性能越來越來高,個人覺得關于時間的存儲方式,具體怎么存儲看個人習慣和項目需求吧

分享兩篇關于int vs timestamp vs datetime性能測試的文章

Myisam:MySQL DATETIME vs TIMESTAMP vs INT 測試儀

CREATE TABLE `test_datetime` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime` FIELDTYPE NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

機型配置

  • kip-locking
  • key_buffer = 128M
  • max_allowed_packet = 1M
  • table_cache = 512
  • sort_buffer_size = 2M
  • read_buffer_size = 2M
  • read_rnd_buffer_size = 8M
  • myisam_sort_buffer_size = 8M
  • thread_cache_size = 8
  • query_cache_type = 0
  • query_cache_size = 0
  • thread_concurrency = 4

測試

DATETIME   14111 14010        14369     130000000
TIMESTAMP  13888        13887        14122     90000000
INT        13270        12970        13496     90000000

執(zhí)行mysql

mysql> select * from test_datetime into outfile ‘/tmp/test_datetime.sql';
Query OK, 10000000 rows affected (6.19 sec)

mysql> select * from test_timestamp into outfile ‘/tmp/test_timestamp.sql';
Query OK, 10000000 rows affected (8.75 sec)

mysql> select * from test_int into outfile ‘/tmp/test_int.sql';
Query OK, 10000000 rows affected (4.29 sec)

alter table test_datetime rename test_int;
alter table test_int add column datetimeint INT NOT NULL;
update test_int set datetimeint = UNIX_TIMESTAMP(datetime);
alter table test_int drop column datetime;
alter table test_int change column datetimeint datetime int not null;
select * from test_int into outfile ‘/tmp/test_int2.sql';
drop table test_int;

So now I have exactly the same timestamps from the DATETIME test, and it will be possible to reuse the originals for TIMESTAMP tests as well.

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql' into table test_datetime;
Query OK, 10000000 rows affected (41.52 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql' into table test_timest
Query OK, 10000000 rows affected, 44 warnings (48.32 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 44

mysql> load data infile ‘/export/home/ntavares/test_int2.sql' into table test_int;
Query OK, 10000000 rows affected (37.73 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

As expected, since INT is simply stored as is while the others have to be recalculated. Notice how TIMESTAMP still performs worse, even though uses half of DATETIME storage size.

Let's check the performance of full table scan:

mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime  ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (3.93 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_timestamp WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime  ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (9.87 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > UNIX_TIMESTAMP('1970-01-01 01:30:00′) AND datetime  UNIX_TIMESTAMP('1970-01-01 01:35:00′);
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (15.12 sec)

Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to be without those recalculations: find the equivalents of those UNIX_TIMESTAMP() values, and use them instead:

mysql> select UNIX_TIMESTAMP('1970-01-01 01:30:00′) AS lower, UNIX_TIMESTAMP('1970-01-01 01:35:00′) AS bigger;
+——-+——–+
| lower | bigger |
+——-+——–+
| 1800 |  2100 |
+——-+——–+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime  2100;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (1.94 sec)

Innodb:MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

總結

以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對腳本之家的支持。如果你想了解更多相關內(nèi)容請查看下面相關鏈接

您可能感興趣的文章:
  • MySQL 時間類型的選擇
  • 如何選擇合適的MySQL日期時間類型來存儲你的時間
  • 關于mysql 的時間類型選擇
  • 解析MySql與Java的時間類型
  • MySQL日期數(shù)據(jù)類型、時間類型使用總結
  • MySQL時間類型和模式詳情

標簽:吐魯番 安徽 洛陽 拉薩 葫蘆島 甘南 嘉峪關 ???/a>

巨人網(wǎng)絡通訊聲明:本文標題《MySQL存儲時間類型選擇的問題講解》,本文關鍵詞  MySQL,存儲,時間,類型,選擇,;如發(fā)現(xiàn)本文內(nèi)容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《MySQL存儲時間類型選擇的問題講解》相關的同類信息!
  • 本頁收集關于MySQL存儲時間類型選擇的問題講解的相關信息資訊供網(wǎng)民參考!
  • 推薦文章