mysql教程

【MySQL】MySQL的資料類型優化

黄舟

Feb 25, 2017 am 10:19 AM

選擇優化的資料類型

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()函數在這兩種表示方法之間轉換。

選擇最佳化的資料類型

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

更小的通常更好

簡單就好

盡量避免使用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。如果不是这两种存储引擎的，请参考所使用的存储引擎的文档。