MySQL Schema設計（二）精打細算，油鹽不斷

茹志鵑在《妯娌》中說，“再看紅英自己，那是連半個鐘頭的工都不肯耽誤的，也從沒見她吃過一口零食，一看就知道是個會精打細算、會過日子的人。”曾有人調侃，已婚身份最是適合DBA，畢竟，不當家不知柴米貴，年底的資源容量訂購，那一分錢都是心頭肉啊，會吃的吃千頓，不會吃的吃一頓。而且，故障診斷以及效能調優時，OS層的APP直接拖垮DB的案例也是家珍如數啊。所以，思前顧後，吃穿常有。謂之，DBA以儉德辟難。

活在大資料時代下，勤儉節約更是DBA的傳統美德。謹慎選擇資料類型很重要，對類型當持有斤斤計較的心思，理由如下：
● 儲存（記憶體、磁碟）、從而節省I/O（檢索相同資料情況下）

● 計算、進而減負CPU負載

㈠ 3種資料類型
1. INT（M） 到底有多M？

M 預設是11，最大有效顯示寬度是255。無論M多大，INT一定是4 bytes。M僅表示顯示寬度，與儲存大小或類型包含的值的範圍無關。離了zerofill這個屬性，M是毫無意義的，硬說有呢、大概也是為了顯示字元的個數、人性化點。對於儲存和計算而言，INT(11)和INT(255)是相同的。

mysql> create table t (id int(2));Query OK, 0 rows affected (0.08 sec)mysql> insert into t select 10086;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.01 sec)mysql> alter table t change column id id int(16);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.00 sec)mysql> alter table t change column id id int(16) zerofill;Query OK, 1 row affected (0.19 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t;+------------------+| id |+------------------+| 0000000000010086 |+------------------+1 row in set (0.00 sec)mysql> alter table t change column id id int(5) zerofill;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+-------+| id |+-------+| 10086 |+-------+1 row in set (0.00 sec)mysql> alter table t change column id id int(6) zerofill;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t;+--------+| id |+--------+| 010086 |+--------+1 row in set (0.00 sec)

2 計算VARCHAR（N）N的最大值

今有道面試題：若一張表中只有一個欄位VARCHAR(N)類型，utf8編碼，則N最大值為多少?

我們不急著計算，先來看幾個注意事項：

● 最大行長度是65535，不過NDB引擎除外。這個限制了列的數目，比如char(255) utf8，那麼列的數目最多有65535/(255*3)=85，列的數目可以從這裡得到依據

● 字元集問題

   latin1：佔用一個位元組

   gbk：每個字元最多佔用2個位元組

   utf8：每個字元最多佔用3個位元組

● 長度列表

    需要額外地在長度列表上存放實際的字元長度：小於255為1個位元組，大於255則要2個位元組

● 1byte/row開銷

    在字元集選用latin1情況下，依據限制3，應該有65533長度可用，然而：

mysql> create table max_len_varchar(col varchar(65533) charset latin1);ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table max_len_varchar(col varchar(65532) charset latin1);Query OK, 0 rows affected (0.16 sec)

     所以，MySQL中，實際儲存應該是從第2個位元組開始

至此，我們便可以從容得出開頭的答案：(65535-1-2)/3。有始有終，再以一道面試題結束本小節：

create table t (col1 int(11), col2 char(50), col3 varchar(N)) charset=utf8;這裡的N最大值？有興趣的朋友可自行算下。

3 timestamp那些事

先看個MySQL datetime的bug提提神：

mysql> create table t (start_time datetime,stop_time datetime);Query OK, 0 rows affected (0.12 sec)mysql> insert into t (start_time, stop_time) values ("2014-01-19 21:46:18", "2014-01-20 00:21:31");Query OK, 1 row affected (0.02 sec)mysql> select start_time, stop_time, stop_time - start_time from t;+---------------------+---------------------+------------------------+| start_time | stop_time | stop_time - start_time |+---------------------+---------------------+------------------------+| 2014-01-19 21:46:18 | 2014-01-20 00:21:31 | 787513 |+---------------------+---------------------+------------------------+1 row in set (0.00 sec)

因為datetime類型不支援直接計算，時間轉化為了數字來相減了才得到此結果的。除了這個bug之外，通常也應該儘可能使用timestamp，畢竟從儲存上看，timestamp 僅占 4 個位元組，比datetime(8位元組)和date(8位元組)的空間效率都要高。而且，有的人習慣用 INT UNSIGNED 來儲存一個轉換成Unix時間戳記的時間值，但這不會帶來任何收益，MySQL提供的from_unixtime()把Unix時間戳記轉換成日期，unix_timestamp()把日期轉換成Unix時間戳記，所以我們沒有必要堅持這個習慣，因為timestamp實際上是4個位元組的INT值，都用系統預設的時區，相同的字串值會得到不同的時間戳記，反而更加不好處理。

timestamp的行為規則比較複雜，並且不同版本的MySQL會有變動，那麼有時候"經驗主義"便會讓人踢到鐵板，所以我們應該驗證資料庫的行為是你需要的，比較好的做法是，修改完timestamp列後用show create table命令檢查輸出，以下是同一個DDL語句在不同版本的timestamp展現

create table t (col timestamp);5.1表現為：`col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP5.5 層現是： `col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP5.6 則為： `col` timestamp NULL DEFAULT NULL

可見，timestamp在5.6版本的變化是翻天覆地的。

隨著經濟全球化日益激烈，跨時區倒資料已是家常便飯。建立資料和schema的邏輯備份最常見的選擇還是mysqldump，但當我們開啟dump檔案頭會發現" /*!40103 SET TIME_ZONE='+00:00' */; "這麼一行。而我們的用戶端預設時區是：

mysql> select @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM |+-------------+1 row in set (0.00 sec)

這個SYSTEM表示MySQL取作業系統的預設時區，因此是東8區。可timestamp顯示的值卻是依賴於時區，MySQL伺服器、OS以及用戶端串連都有時區設定。以下做個簡單測試：

mysql> drop table if exists t;mysql> create table t (col timestamp);mysql> insert into t select now();mysql> select * from t;+---------------------+| col |+---------------------+| 2014-01-25 10:42:44 |+---------------------+1 row in set (0.00 sec)

$ mysqldump -uroot -poracle testdb t --where='col="2014-01-25 10:42:44"' | grep INSERT

返回空，導不出資料？下面給出2種解決方案

方法一  加上參數 --tz-utc

$ mysqldump -uroot -p testdb t --tz-utc=0 --where='col="2014-01-25 10:42:44"' | grep INSERT

INSERT INTO `t` VALUES ('2014-01-25 10:42:44');

方法二  用轉換函式處理

mysql> select unix_timestamp(col) from t;+---------------------+| unix_timestamp(col) |+---------------------+| 1390617764 |+---------------------+

$ mysqldump -uroot -p testdb t --where='col=from_unixtime(1390617764)' | grep INSERT

INSERT INTO `t` VALUES ('2014-01-25 02:42:44');

㈡ 資料類型轉換

基本原則：

⑴ 所有Where條件的欄位上不使用函數做類型轉換，這主要是為了避免索引列被汙染，這裡給出 2 條建議：

① 不要在欄位前增加函數

如：
     to_char(start_time,'yyyy.mm.dd') between '2013.06.06' and '2013.06.10'
     和
     start_time between to_date('2013.06.06','yyyy.mm.dd') and to_date('2013.06.10','yyyy.mm.dd')
任何時候都應該是第二種！！！

② 不要把欄位嵌入到運算式中
          
  如：
      start_time + 7 < sysdate;
      和
      start_time < sysdate - 7

⑵ 隱式轉換

舉個例子吧、假設我在字元列上建立個索引、然後：
         
         select * from t  where index_column = 8;
         
         這條語句實際上會被等價於：
         
         select * from t where to_number(index_column) = 8;
         
一定要儘可能避免隱轉，自己跟自己比就行了，"別人家的孩子"就讓他們自己搗鼓去吧

⑶ 在表串連Query中，如果串連條件兩端的資料類型不一致，必須保證將驅動表的串連條件資料類型轉換為與被動表一致的資料類型。

㈢ 資料類型最佳化

下面總結我認為最佳化資料類型的幾條通用原則為：

1、資料類型更小通常更好，資料類型越簡單越好
2、數值操作比字元操作快，小類型的處理速度比大類型快
3、不同資料表中固定長度類型和可變長度類型的處理效率是不同的
4、盡量避免使用NULL，要是有必要用NULL，那也可考慮使用0來進行代替

5、多使用enum，set

6、IP用int存：inet_aton()、inet_ntoa()

7、使用decimal而不是float & double

8、MyISAM多使用char、InnoDB多使用varchar

By 迦葉

2014-1-25

Good Luck！