高性能的MySQL（4）資料類型的優化

一、基本原則

1、更小的通常更好

更小的資料類型通常更快，因為占用更少的磁盤、記憶體和CPU緩存，并且處理時需要的CPU周期也更少。

但是要確定沒有低估需要存儲的值的範圍，因為在schema中的多個地方增加資料類型的範圍是個非常耗時的操作。

2、簡單就好

例如，整數比字元串操作代價更低，應該用内建類型而不是字元串來存儲時間和日期，用整型存儲IP。

3、盡量避免NULL

可為NULL的列使用更多的存儲空間，需要特殊的處理。特别是可為NULL的列被索引時，每個索引需要額外的位元組，在Myisam引擎裡甚至還可能導緻固定大小的索引，是以計劃建立索引的列，要避免使用NULL。

二、資料類型

1、整數類型

TINYINT、SMALLINT、MEDIUINT、INT、BIGINT，分别使用8、16、24、32、64位存儲空間。範圍從-2（N-1）次方到2（N-1）次方-1。

指定範圍，隻是一種顯示，對存儲和計算來說INT(1)和INT(20)是一樣的。

2、實數類型

FLOAT和DOUBLE支援使用标準的浮點運算進行近似計算。

DECIMAL類型用于存儲精确的小數

浮點類型在存儲同樣的範圍的值是，通常比DECIMAL使用的空間更少。因為額外的空間和計算開銷是以應該隻在對小數進行精确計算時才使用，也可以考慮使用BIGINT代替DECIMAL，将小數的位數乘以相應的倍數即可。

3、字元串類型

VARCHAR類型用于存儲可變長字元串，但是需要1-2個額外位元組記錄字元串的長度。由于行是變長的，在UPDATE時不同的引擎需要不同的額外處理工作。同時存儲和檢索時會保留末尾空格。

CHAR類型是定長的。存儲時會删除末尾的空格。

對于字元串最大長度比平均長度大很多，列的更新很少，适合VARCHAR。對于經常更新的資料，CHAR更好，因為不容易産生碎片。

4、BLOB和TEXT類型

都是為存儲大的資料而設計的字元串類型，分别采用二進制和字元方式存儲，BLOB沒有排序規則和字元集。

MySQL對BLOB和TEXT列進行排序和其他類型是不同的，它隻針對每個列的最前max_sort_length位元組排序，或者使用order by substring(column,length).

如果查詢使用了BLOB和TEXT列并且需要使用隐式臨時表，将不得不用到MYIASM磁盤臨時表，這是很大的系統開銷。如果無法避免，有一個技巧是在所有使用到BLOB字段的地方使用substring(column,length)将列值轉換為字元串，這樣就可以使用記憶體臨時表了。但是要確定截取的字元串足夠短，不會使臨時表的大小超過max_heap_table_size和tmp_table_size，如果超過還是會使用磁盤臨時表的。

如果explain的Extra列包含“Using temporary”,則說明用到了隐式臨時表

5、ENUM類型

把一些不重複字元串存儲成一個預定義的集合，非常節省空間。mysql在内部會将每一個值在清單中的位置儲存為整數，并且在表的.frm檔案中儲存“數字-字元串”的映射關系的查找表。隻有在進行查找時才會轉化為字元串。

<a href="http://blog.51cto.com/attachment/201310/155358802.png" target="_blank"></a>

是以要盡量避免使用數字作為枚舉值來存儲。

另外一個要注意的是枚舉字段是按照内部存儲的整數來排序的，而不是字元串。

<a href="http://blog.51cto.com/attachment/201310/155645946.png" target="_blank"></a>

是以盡量按照需要的順序來定義枚舉列，也可以使用field()函數指定排序，但會導緻無法利用索引消除排序。

<a href="http://blog.51cto.com/attachment/201310/160416557.png" target="_blank"></a>

枚舉類型有另外一個好處。根據show table status指令結果看data_length列的值，可以讓表的大小縮小1/3，同樣，轉換後主鍵也隻有原來的一半了。

6、日期和時間類型

DATETIME：儲存從1001年到9999年，精度為秒，把日期和時間封裝到整數中，與時區無關，使用8個位元組存儲空間。

TIMESTAMP：儲存從1970到2038年的時間戳，可以使用UNIX_TIMESTAMP()和FROM_UNIXTIME()來互相轉換。

除了特殊行為之外，通常也應該盡量使用TIMESTAMP，因為空間效率更高。

7、BIT類型

存儲一個或多個true/false值，最大64位。

mysql把bit當作字元串類型而不是數字類型。當檢索bit(1)時，結果是包含二進制0或1的字元串，而不是ASCII碼的0或1，然而，在數字場景中檢索時，結果是位字元串轉換成的數字。

<a href="http://blog.51cto.com/attachment/201310/164222614.png" target="_blank"></a>

是以不建議使用BIT類型。

8、SET類型

儲存多個true/false值，修改列定義代價較高，需要alter table,而且無法使用索引。

可以使用整數列來進行按位操作，但是邏輯上的查詢語句就會很難些，因人而異選擇吧。

使用set

<a href="http://blog.51cto.com/attachment/201310/165952976.png" target="_blank"></a>

使用tinyint

<a href="http://blog.51cto.com/attachment/201310/170502841.png" target="_blank"></a>

辨別列或者可能做外鍵的列，最好使用整型類型。

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1308826，如需轉載請自行聯系原作者