選擇優化的數據類型

MySQL支持的數據類型非常多，選擇正確的數據類型對于獲得高性能至關重要。不管存儲那種類型的數據，下面幾個原則都有助于做出更好的選擇。

更小的通常更好

一般情況下，應該盡量使用可以正確存儲數據的最小數據類型（例如只需要存0-200，tinyint unsigned更好）。更小的數據類型通常更快，因為它們占用更少的磁盤、內存和CPU緩存，并且處理時需要的CPU周期也更少。

簡單就好

簡單數據類型的操作通常需要更少的CPU周期。例如，整型比字符操作代價更低，因為字符集和校對規則（排序規則）是字符串比較比整型比較更復雜。這里有兩個例子：一個是應該用MySQL內建的類型（比如date,time,datetime）而不是字符串來存儲日期時間，另外一個是應該用整型來存儲IP地址。

盡量避免使用NULL

很多表都包含了可為NULL的列，即使應用程序不需要保存NULL也是如此，這是因為可為NULL是列的默認屬性。通常情況下最好指定列為NOT NULL，除非真的需要存儲NULL值。

如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理。當可為NULL的列被索引時，每個索引記錄需要一個額外的字節，在MyISAM中甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引。

通常把可為NULL的值改為NOT NULL帶來的性能提升比較小，所以（調優時）沒有必要首先在現有的schema中查找并修改掉這種情況，除非確定這會導致問題。但是，如果計劃在列上建立索引，就應該避免設計成可為NULL的列。

當然也有例外，例如值得一提的是，InnoDB使用單獨的位（bit）存儲NULL值，所以對于稀疏數據（大部分值為NULL，只有少數行為非NULL的值）有良好的空間效率。但這一點不適用于MyISAM。

整數類型

如果存儲整數，可以使用這幾種整數類型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分別使用8,16,24,32,64位存儲空間。它們的存儲范圍從 -2的(N-1)次方到 2的(N-1)次方-1，其中N為存儲空間的位數。

整數類型有可選的UNSIGNED屬性，表示不允許負值，這大致可以使正數的上限提高一倍，例如TINYINT UNSIGNED可以存儲的范圍是0-255，而TINYINT 的存儲范圍是-128~127。

MySQL可以為整數類型指定寬度，例如INT(11)，對大多數應用這是沒有意義的：他不會限制值得合法范圍，知識規定了MySQL的一些交互工具（例如MySQL命令行客戶端）用來顯示字符的個數。對于存儲來說，INT(1)和INT(20)是相同的。

實數類型

實數是帶有小數部分的數字。然而，它們不只是為了存儲小數部分，也可以使用DECIMAL存儲比BIGINT還大的整數。MySQL既支持精確類型，也支持不精確類型。

FLOAT 和 DOUBLE 類型支持使用標準的浮點運算進行近似計算。如果需要知道浮點運算時怎么計算的，則需要研究所使用的平臺的浮點數的具體實現。

DECIMAL 類型用于存儲精確的小數。但因為CPU不支持對DECIMAL的直接計算，所以在MySQL5.0及更高版本中，MySQL服務器自身實現了DECIMAL的高精度計算。相對而言，這比CPU直接支持原生浮點數運算要慢。

浮點和DECIMAL類型都可以指定精度。對于DECIMAL列，可以指定小數點前后所允許的最大位數。這會影響列的空間消耗。

浮點類型在存儲同樣范圍的值時，通常比DECIMAL使用更少的空間。FLOAT使用4個字節存儲。DOUBLE占用8個字節，相比FLOAT有更高的精度和更大的范圍。

因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用DECIMAL——例如存儲財務數據。但數據量比較大的時候，可以考慮使用BIGINT代替DECIMAL，將需要存儲的貨幣單位根據小數點的位數乘以相應的倍數即可。假設要存儲財務數據精確到萬分之一分，則可以把所有金額乘以100W，然后將結果存儲在BIGINT里，這樣可以同時避免浮點存儲計算不精確和DECIMAL精確計算代價高的問題。

字符串類型

下面的描述假設使用的存儲引擎是InnoDB/或者MyISAM。如果不是這兩種存儲引擎的，請參考所使用的存儲引擎的文檔。

VARCHAR和CHAR

VARCHAR:它比定長類型更節省空間，因為它僅使用必要的空間。VARCHAR節省了空間，所以對性能也有幫助。但是由于行是變長的，在UPDATE時可能使行變得比原來更長，這就導致需要做額外的工作。

下面的情況使用VARCHAR是合適的：字符串最大長度比平均長度大很多；列的更新少，所以碎片不是問題；使用了像UTF-8這樣復雜的字符集，每個字符使用不同的字節數。

在5.0或更高的版本中，MySQL在存儲和檢索時會保留末尾空格。InnoDB則更靈活，它可以把長的VARCHAR存儲為BLOB

CHAR: 定長，當存儲CHAR值時，MySQL會刪除所有的末尾空格。定長的CHAR類型不容易產生碎片，對于非常短的列，CHAR比VARCHAR在存儲空間上也更有效率，VACHAR還有一個或兩個記錄長度的額外字節。CHAR適合存儲很短的字符串，或者所有值都接近同一個長度。例如：CHAR非常適合存儲密碼的MD5值，因為這是一個定長的值。CHAR會根據需要采用空格填充以方便比較。

與CHAR和VARCHAR類似的類型還有BINARY和VARBINARY，它們存儲的是二進制字符串。二進制字符串中存儲的是字節碼而不是字符。

二進制比較的優勢并不僅僅體現在大小寫敏感上。MySQL比較BINARY字符串是，每次按一個字節，并且根據該字節的數值進行比較。因此，二進制比字符比較簡單的多，所以也就更快。

BLOB 和 TEXT類型

BLOB和TEXT類型：BLOB和TEXT都是為了存儲很大的數據而設計的字符串數據類型，分別采用二進制和字符方式存儲。當BLOB和TEXT值太大時，InnoDB會使用專門的”外部”存儲區域來進行存儲。原表字段存儲指針指向外部存儲區域。

MySQL對BLOB和TEXT列進行排序與其他類型是不同的：它只對列最前max_sort_length 字節而不是整個字符串做排序。如果只需要排序前面一小部分字符，則可以減小max_sort_length 的配置，或者使用ORDER BY SUSTRING(column, length)。

MySQL不能將BLOB和TEXT列全部長度的字符串進行索引，也不能使用這些索引消除排序。

使用枚舉（ENUM）代替字符串類型

可以使用枚舉（ENUM）代替字符串類型。很多時候建議使用枚舉列代替常用的字符串類型。

（1）枚舉列可以把一些不重復的字符串存儲成一個預定義的集合。
（2）Mysql在存儲枚舉時非常緊湊，會根據列表值的數量壓縮到一到兩個字節中。 ?
（3）Mysql在內部會將每個值在列表中的位置保存為整數，并且在表的.frm文件中保存“數字-字符串”映射關系的“查找表”。

注意：有一個令人吃驚的地方是，枚舉字段是按照內部存儲的整數而不是定義的字符串進行排序的。

注意：枚舉最不好的地方是：字符串列表是固定的，添加或者刪除字符串必須使用ALTER TABLE，因此對于一系列未來可能會改變的字符串，使用枚舉并不是一個好主意，除非接受只能在列表末尾添加元素。

注意：由于Mysql把每個枚舉值保存為整數，并且必須進行查找才能轉換為字符串，所以枚舉列有一些開銷。

日期時間類型

數據類型及用法詳見： http://www.php.cn/

Mysql有很多類型可以保存日期和時間值，比如YEAR和DATE。

Mysql能存儲的最小時間粒度為秒（MariaDB支持微秒級別的事件類型）。但是Mysql也可以使用微秒級別的粒度進行臨時運算。

大部分時間類型都沒有替代品，因此沒有什么是最佳選擇的問題。

接下來唯一的問題是保存日期和時間的時候需要做什么。

DATETIME

（1）這個類型能保存大范圍的值，從1001年到9999年，精度為秒。（2）DATETIME把時間和日期封裝到格式為YYYYMMDDHHMMSS的整數中，與時區無關。（3）DATETIME使用8個字節的存儲空間。

TIMESTAMP

（1）TIMESTAMP類型保存了從1970年1月1日午夜以來的秒數，它和UNIX時間戳相同。（2）TIMESTAMP只使用4個字節的存儲空間，因此它的范圍比DATETIME小得多。（3）TIMESTAMP顯示的值依賴時區。

DATETIME和TIMESTAMP的對比:

（1）默認情況下，如果插入時沒有指定第一個TIMESTAMP列的值，Mysql則設置這個列的值為當前時間。（這是DATETIME沒有的特性）（2）在插入一行記錄時，Mysql默認也會更新第一個TIMESTAMP列的值。（3）TIMESTAMP列默認為NOT NULL，這與其他的數據類型不一樣。

總結

（1）除了特殊行為之外，通常也應該盡可能使用TIMESTAMP，因為它比DATETIME空間效率更高。（2）一般來講不建議把UNIX時間戳保存為整數值，這不會帶來任何收益，用整數保存時間戳格式通常不方便處理。（3）如果需呀存儲比秒更小粒度的日期和時間值，可以使用BIGINT類型存儲微秒級別的時間戳，或者使用DOUBLE存儲秒之后的小數部分，也可以用MariaDB替代Mysql。

位數據類型

MySQL有少數幾種存儲類型使用緊湊的位存儲數據。所有這些位類型，不管底層存儲格式和處理方式如何，從技術上來說都是字符串類型的。

BIT

可以使用BIT列在一列中存儲一個或多個true/false值。BIT(1)定義了一個包含單個位的字段，BIT(2)存儲2個位，依次類推。BIT列的最大長度是64位。

如果想在一個bit的存儲空間中存儲一個true/false值，另一個方法是創建一個可以為空的CHAR(0)列。該列可以保存空值(NULL)或者長度為零的字符串(空字符串)。

SET

如果需要保存很多true/false 值，可以考慮合并這些列到一個SET 數據類型，它在MySQL 內部是以一系列打包的位的集合來表示的。這樣就有效地利用了存儲空間，并且MySQL 有像FIND_IN_SET（）和FIELD（）這樣的函數，方便地在查詢中使用。它的主要缺點是改變列的定義的代價較高：需要ALTER TABLE,這對大表來說是非常昂貴的操作。一般來說，也無法在SET 列上通過索引查找。

一種替代SET 的方式是使用一個整數包裝一系列的位。例如，可以把8 個位包裝到一個TINYINT 中，并且按位操作來使用。可以在應用中為每個位定義名稱常量來簡化這個工作。

比起SET,這種辦法主要的好處在于可以不使用ALTER TABLE 改變字段代表的”枚舉”值，缺點是查詢語句更難寫，并且更難理解（當第5 個bit 位被設置時是什么意思？）。一些人非常適應這種方式，也有一些人不適應，所以是否采用這種技術取決于個人的偏好。

選擇標識符（identifier）

為identifier（標識列）選擇合適的數據類型非常重要。

一般來講更有可能用標識列與其他值進行比較，或者通過標識列尋找其他列。

當選擇標識列的類型時，不僅僅需要考慮存儲類型，還需要考慮Mysql對這種類型怎么執行計算和比較。

一旦選定了一種類型，要確保在所有關聯表中都使用同樣的類型。

在可以滿足值的范圍需求，并且預留未來增長空間的前提下，應該選擇最小的數據類型。

整數通常是標識列最好的選擇，因為它們很快而且可以使用AUTO_INCREMENT。
ENUM和SET是最糟糕的選擇了；
如果可能也盡可能避免使用字符串作為標識列，因為它們很消耗空間并且通常比數字類慢。

特殊類型數據

某些類型的數據并不直接與內置類型一致。低于秒級精度的時間戳就是一個例子。

另一個例子是人們通常使用VARCHAR(15)來存儲IP地址。然而，它們實際是32位無符號整數，不是字符串。用小數點將字段分割成四段是為了閱讀方便。所以應該用無符號整數存儲IP地址。MySQL提供INET_ATON()和INET_NTOA()函數在這兩種表示方法之間轉換。