iT邦幫忙

7

資料庫日期資料使用日期型態與字元型態存放的比較

有網友提出這方面的問題,大家討論的蠻熱烈的.也有許多觀點.
請參考:
http://ithelp.ithome.com.tw/question/10148171?tag=hp.all

至於哪種好?我想這沒有絕對的答案.以下就做一些測試,但也只是MySQL的一個版本而已,並不是很全面.
之前有發表過測試資料的產生,恰好可以用來做這次的測試之用.

http://ithelp.ithome.com.tw/question/10147387

已經產生了100萬筆資料,裡面存放日期型態的資料,
範圍從2014-01-01到2014-12-31.

接著建立以CHAR型態來存放日期資料的Table.

CREATE TABLE hito_char(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sdate CHAR(10) NOT NULL);

將hito_date的資料轉入到hito_char

INSERT INTO hito_char(id, sdate)
SELECT id
     , sdate
  FROM hito_date;

-- 查詢本月份資料筆數,做兩種型態的比較.

SELECT COUNT(1) AS cnt
   FROM hito_date
  WHERE sdate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    AND sdate <= LAST_DAY(CURDATE());

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.31 sec)


SELECT COUNT(1) AS cnt
   FROM hito_char
  WHERE sdate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    AND sdate <= LAST_DAY(CURDATE());

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.41 sec)

-- 增加索引

ALTER TABLE hito_date
  ADD INDEX (sdate);

ALTER TABLE hito_char
  ADD INDEX (sdate);

SELECT COUNT(1) AS cnt
   FROM hito_date
  WHERE sdate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    AND sdate <= LAST_DAY(CURDATE());

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.03 sec)


SELECT COUNT(1) AS cnt
   FROM hito_char
  WHERE sdate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    AND sdate <= LAST_DAY(CURDATE());

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.35 sec)

-- 因為用日期函數產生的本月初與本月底,與字元型態比較時,需要做轉換,故
-- 字元型態會較慢.
-- 改用手動設定日期範圍

SELECT COUNT(1) AS cnt
   FROM hito_char
  WHERE sdate >= '2014-04-01'
    AND sdate <= '2014-04-31';

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.05 sec)

SELECT COUNT(1) AS cnt
   FROM hito_date
  WHERE sdate >= '2014-04-01'
    AND sdate <= '2014-04-31';

+-------+
| cnt   |
+-------+
| 82880 |
+-------+
1 row in set (0.04 sec)

-- 還是日期型態較快.

-- 接著做磁碟空間比較

SELECT table_name
     , data_length / 1024 AS 'data(K)'
     , index_length / 1024 AS 'index(K)'  
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name IN ('hito_date', 'hito_char');

+------------+------------+------------+
| table_name | data(K)    | index(K)   |
+------------+------------+------------+
| hito_char  | 35392.0000 | 21056.0000 |
| hito_date  | 27184.0000 | 12816.0000 |
+------------+------------+------------+

-- 日期型態較節省空間.index的空間差異更明顯.

-- 輸入 0001-01-01 與 9999-12-31 測試範圍

INSERT INTO hito_char(sdate) VALUES
('0001-01-01'), ('9999-12-31');

INSERT INTO hito_date(sdate) VALUES
('0001-01-01'), ('9999-12-31');

SELECT MIN(sdate)
     , MAX(sdate)
  FROM hito_char;

+------------+------------+
| MIN(sdate) | MAX(sdate) |
+------------+------------+
| 0001-01-01 | 9999-12-31 |
+------------+------------+

SELECT MIN(sdate)
     , MAX(sdate)
  FROM hito_date;

+------------+------------+
| MIN(sdate) | MAX(sdate) |
+------------+------------+
| 0001-01-01 | 9999-12-31 |
+------------+------------+

-- 以上測試在 MySQL 5.6.17 進行

SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.17    |
+-----------+

2 則留言

0
海綿寶寶
iT邦超人 1 級 ‧ 2014-04-04 23:16:54

0
一級屠豬士
iT邦高手 1 級 ‧ 2014-04-07 12:46:25

wiselou貼的,只看到這樣:

我要留言

立即登入留言