高效能MySql進化論(一):資料類型的最佳化_上

在資料庫的效能調優的過程中會涉及到很多的知識，包括字段的屬性設定是否合適，索引的建立是否恰當，表結構涉及是否合理，資料庫/作業系統 的設定是否正確…..其中每個topic可能都是一個領域。

 

在我看來，在資料庫效能提升關鍵技術中，對欄位的最佳化難度相對較低且對效能的影響也非常的大。由於MySQL支援的資料類型比較多，且每個類型都有其獨特的特性，但是有時候在選擇一個具體的資料類型時，往往都是隨意的選擇一個能用的類型，而不會考慮到這個類型是否是最優的。在特定的類型描述之前，先來看看一些針對資料類型選擇的主要原則：

a)      盡量選擇佔用空間小的類型
因為小的類型無論是在磁碟，還是在記憶體中佔用的空間都是小的,在進行查詢或排序是臨時表要求的空間也會相對較少。在資料量比較小的時候可能感覺不到，但是當資料量比較大時，這個原則的重要性可能就會顯現。

 

例如,有一張「商品資訊」表，記錄為2000萬條，這張表有個「剩餘商品數量」(COUNT)的字段，一般而言SMALLINT (len:16  range:0-65535)已經足夠表達這個字段，可是如果你在設計的過程中用了BIGINT(len:64 range:0-18446744073709551615)來表達，雖然說程序可能正確的運行，但是這一個字段將會額外的增加大概95M的磁碟儲存空間(64-16)/8*20,000,000 Bytes)，另外在做資料選擇和排序時僅僅這一個欄位就會增加你95M的記憶體消耗，基於以上行為的影響，資料庫的Performance必然會被影響的

這裡說的盡量小的前提是確保你將要選擇的類型可以滿足日後業務發展的需求，因為在數據量比較大的時候做表結構的更新是個非常緩慢而且麻煩的事情。

 

b)    盡量選擇簡單/恰當的類型

在對錶進行選擇以及排序的時候，對於簡單的類型往往只需要消耗較少的CPU時鐘週期。例如，對於MySql server而言，整數類型值的Compare往往會比字串類型值的Compare簡單且快，所以當你需要對特定的表進行排序時應該盡量選擇整數類型作為排序的依據

 

c)       盡量將欄位設為NOTNULL
一般情況下，如果你沒有顯示的製定一個欄位為NULL，那麼這個欄位將會被資料庫系統認為是NULLABLE, 系統的這種預設行為將會導致下列三個問題
(1) Mysql伺服器本身的查詢最佳化功能將會受影響
(2) Mysql針對null值的欄位需要額外的儲存空間以及處理
(3) 如果一個null值是索引的一部分，那麼索引的效果也會收到影響

 由於這個原則對於資料庫效能提升的作用不是很大，所以對於已經存在的DB schema，其存在NULLABLE字段或者是索引為NULLABLE的，也不用專門的去修改它，但是對於新設計的DB或索引需要盡量遵守這個原則。

 

介紹完了資料類型選擇的原則後，接下來將會介紹Mysql中常見的資料類型以及在效能最佳化方面需要注意的地方。

·        整數
在Mysql 的整數家族成員中主要包括TINYINT(8bit), SMALLINT(16bit),  MEDIUMINT(24bit), INT(32bit), or BIGINT(64bit)。

對於有符號整數而言這些類型的儲存範圍為(-2(n-1) ,2(n-1)-1)，對於無符號數而言表達的範圍是(0,2n-1) ，對於資料庫而言有符號數和無符號數佔用相同的儲存空間，所以在選擇類型的時候可以只考慮數的區間，而不用考慮是signed還是unsigned

 Mysql允許你在定義整數類型時指定他的寬度，例如INT(10)。 INT(10) 對於Client/CMD Line的輸出是有差別的，但在Mysql Server看來實際的儲存空間/運算消耗/數字範圍 INT(10)與INT(32)沒有任何的差別。

·        小數
在Mysql中小數家族的資料型態主要包括FLOAT(4Bytes),DOUBLE(8Bytes),從這兩種類型的儲存空間可以看出小數的存取比整數需要更多的空間，所以需要消耗更多的空間，所以除非必須，否則應該盡量避免使用小數的類型

創建小數類型的字段時，你可以使用FLOAT(10,3)的方式來指定小數的精度，>=Mysql 5.0的版本中最大的精度支援到小數點後65位。

由於資料庫採用Binary Array String的方式來儲存小數點後面的數字，所以你要求的精確度越高，儲存空間/運算的CPU時鐘可能消耗的也就越高。

 雖然使用小數可能會消耗更多的儲存空間以及CPU資源，而且對於早期的Mysql版本還會出現當兩個小數參與計算時精度丟失的情況，但是在很多情況下它又是必須的，例如在金融領域中關於金額的儲存。在許多情況下為了減少儲存的開銷以及保證精度的準確性，往往會把小數擴大至整數儲存在資料庫中，而在Application中再進行小數的轉換以及計算，例如某個使用者的帳戶餘額還剩下999.35元，那麼在資料中儲存的金額為99935分,銀行的處理程序拿到99935分後會先轉換成999.35元，然後再進行對應的處理

 

·     而言，字串都是一個比較重要且複雜的類型，這個規律對於MYSQL同樣適用

在MYSQL中主要包括VARCHAR以及CHAR兩種字串類型，對於這兩種字串類型在磁碟以及記憶體中儲存方式是由Storage engine決定的，且不同的storage engine可能會有不同的儲存方式。一般情況下對於一種storage engine 而言，在磁碟以及記憶體中的儲存方式也是不同的，當資料在磁碟與記憶體之間轉移時，storage engine將會負責將資料轉換

VARCHAR
首先需要指出的是Mysql是用variable  length的方式來儲存VARCHAR，相對於fixed length，這種方式對儲存空間採取的策略是“用多少，要多少”，是一種比較節省空間的儲存方案，在沒有特殊需求的情況下可以作為預設的類型

VARCHAR之所以可以實現定長，是因為每個VARCHAR值都會附加一個長度為1-2byte 的長度指示器，例如當需要儲存「I Love Java」時，底層的儲存內容為「11I Love Java」,其中11(1 Byte)代表長度。當需要儲存內容的長度為1000時長度指示器就需要兩個位元組。因為2bytes的最大值是216，所以當儲存的字串超過這個長度時，會出現不可預料的異常，這時就需要使用CLOB來儲存這個超長的字串。

在MYSQL的不同版本中，針對VARCHAR字段的結尾空格處理也有所不同
Version>=5.0 保留結尾的空格
Version
以MYSQL  5.6 為例：
       使用VARCHAR(5) 和VARCHAR(200) 儲存'hello'的空間開銷是一樣的。那麼使用更短的列有什麼優勢嗎？

事實證明有很大的優勢。更大的列會消耗更多的內存，因為MySQL 通常會分配固定大小的內存區塊來保存內部值。尤其是使用記憶體臨時表進行排序或操作時會特別糟糕。在利用磁碟臨時表進行排序時也同樣糟糕。

所以最好的策略是只分配真正需要的空間。

CHAR
CHAR類型與VARCHAR類型最大的區別在於它是定長的。同時相較於VARCHAR它主要有以下特點

 1)在所有的MYSQL版本中，末尾的空格都會被截取

2)對於一些短的且是長度基本相同的字段是個不錯的選擇例如MD5 ,ID Number
3)對於經常需要變更的字段，CHAR類型會更有效率
4)對於一些超短的字段，也非常的節約空間。例如你保存“Y”或“N”，用CHAR只需要一個字節，而用VARCHAR 的話需要兩個字節（1byte length+1 byte value）

對於定長的CHAR，Mysql server會根據其定義的長度採用補空格的方式來分配足夠大的儲存空間。有一點要注意的是VARCHAR/CHAR在進行「補空格」以及「去結尾空格」的操作是由Mysql server來實現的，與Storage engine 無關

以上就是 高效能MySql進化論(一):資料型態的優化_上的內容，更多相關內容請關注PHP中文網（www.php.cn）！