《高性能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必然是會被影響的。

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

在企業項目中展現：

mysql中的tinyint：從 0 到 255 的整型資料。存儲大小為 1 位元組。

mysql中的Smallint： 存儲大小為 2 位元組。

mysql中的int：存儲大小為 4 位元組。

我看了下我們現有的系統，表數量有400張。

表中的“類型”字段，不會少吧，比如訂單有訂單類型、取消原因類型等等10來個類型字段，（這些類型，最多有10多個值，再加也多不到哪去），其他的雖然少，但是一個表最少有1個吧，算這400多個表“類型”字段一共500個吧，我們來算一下資料空間的差别(假設資料都是500萬條)：

使用tinyint:5000000*1*500/1024/1024 = 2384MB

使用int:5000000*4*500/1024/1024 = 9537MB

光資料存儲，相差就是幾個G,還有資料庫日志呢，絕對不會比資料少（現在生産環境資料已經差不多到達1000多萬了）

字段大，對于資料庫查詢、資料傳輸等方面都會有很大影響的，是以不要小看 int和tinyint類型的選擇！我們系統很多都是開發者自己建立的資料庫表，int在和程式中友善使用，直接用Int了，根本不會考慮效率問題，這是需要重視的。

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

在對表進行選擇以及排序的時候，對于簡單的類型往往隻需要消耗較少的CPU時鐘周期。例如，對于mysql server而言，整數類型值的比較往往會比字元串類型值的Compare簡單且快，是以當你需要對特定的表進行排序時應該盡量選擇整數類型作為排序的依據。字元集和比較規則使得字元排序與比較更複雜。

打個比方：全世界的人都知道 1~10這10個整數怎麼比較和排序。因為整數可以直接比較。但是你知道α β γ δ ε ζ η θ ι κ λ μ ν ξ ο π ρ σ τ υ φχ ψ ω 怎麼排序嗎？ 我們不得不借助字元校對集進行排序。字元校對集

c) 盡量将字段設定為NOTNULL

一般情況下，如果你沒有顯示的制定一個字段為NULL，那麼這個字段将會被資料庫系統認為是NULL, 系統的這種預設行為将會導緻以下三個問題

(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》（第三版MySQL5.6以上版本）

· 小數

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

注意： 指定浮點數的精度特性已經被移除，請避免使用。–《高性能MySQL》（第三版MySQL5.6以上版本） 如Float(4,2) 。

規範：對小數的計算請使用DECIMAL進行精确計算，避免使用浮點數。—《阿裡巴巴JAVA技術手冊–建表公約》

· 字元串

不管對于哪門語言而言，字元串都是一個比較重要且複雜的類型，這個規律對于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<=4.1 截取空格

以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 無關

·BLOB/TEXT

在實際的應用程式中往往需要存儲兩種體積較大的資料，一種是較大的Binary資料，e.g. 一張10M的圖檔，另外一種是 較大的文本 e.g.一篇幾萬字的文章。在Oracle中有BOLB和CLOB來應對這兩種資料，而在MySQL中對應的是BLOB以及TEXT.

鑒于這兩種資料類型的特殊性，在MySQL中對BLOB以及TEXT的存儲和操作做了特殊的處理:

1) BLOB/TEXT 的值往往是作為對象來處理，這些對象有自己的ID，以及獨立的存儲空間

2) BLOB/TEXT的值被用來排序的時候，隻有前N個位元組會被使用，N 對應的是資料庫中的一個常量值 (max_sort_length), 如果你想指定更多的位元組被用來排序，那麼你可以增加max_sort_length的值或者是使用ORDER BY SUBSTRING(column, length)函數來處理

3) 當BLOB/TEXT 被用作索引或者排序的時候，不能使用整個字段的值.

在萬不得已的情況下要避免把BOLB/TEXT用作索引或是排序

因為MySQL 的Memory 引擎不支援BLOB 和TEXT 類型，是以，如果查詢的過程中涉及到BLOB /TEXT,則需要使用MyISAM 磁盤臨時表，即使隻有幾行資料也是如此（在最新的Percona Server 的Memory 引擎支援BLOB 和TEXT 類型）。

Memory引擎頻繁的通路磁盤臨時表會産生嚴重的性能開銷，最好的解決方案是盡量避免使用BLOB 和TEXT 類型。如果實在無法避免，有一個技巧是在所有用到BLOB 字段的地方都使用SUBSTRING(column, length) 将列值轉換為字元串（在ORDER BY 子句中也适用），這樣就可以使用記憶體臨時表了。但是要確定截取的子字元串足夠短，不會使臨時表的大小超過max_heap_table_size 或tmp_table_size，超過以後MySQL 會将記憶體臨時表轉換為MyISAM 磁盤臨時表。

最壞情況下的長度配置設定對于排序的時候也是一樣的，是以這一招對于記憶體中建立大臨時表和檔案排序，以及在磁盤上建立大臨時表和檔案排序這兩種情況都很有幫助。例如，假設有一個1 000 萬行的表，占用幾個GB 的磁盤空間。其中有一個utf8字元集的VARCHAR(1000) 列。每個字元最多使用3 個位元組，最壞情況下需要3 000位元組的空間。如果在ORDER BY 中用到這個列，并且查詢掃描整個表，為了排序就需要超過30GB 的臨時表

· DATETIME/TIMESTAMP

在MySQL中包含兩種時間格式 DATETIME，TIMESTAMP， 通常在使用的過程中這兩種類型差別不是很大，但是在細節上還是存在差别DATETIME TIMESTAMP占用空間8Bytes

4Bytes可表示區間(年)1001-9999 1970-2038是否與時區有關否是存儲内容日期和時間封裝到格式為YYYYMMDDHHMMSS 的整數中

儲存了從1970 年1 月1 日午夜（格林尼治标準時間）以來的秒數，它和UNIX 時間戳相同

顯示方式是否與時區有關

否（ANSI 标準定義的日期和時間表示方法）

是

特殊屬性

(1)TIMESTAMP 列預設為NOT NULL,預設值為目前時間

因為TMESSTAMP會占用更小的存儲空間，是以可以使用它作為預設的時間格式

· ENUM

這種類型的字段主要是通過枚舉的方式來儲存列的值，因為在使用的過程中會涉及到枚舉位置與實際值的轉換，是以對于整體的性能可能會有一定的影響，而且枚舉的值是存儲在.frm(資料表結構定義檔案)中，是以當建立完ENUM的列後，如果你想對EMUM的内容進行更新，也就相當于做了表結構的更新。

下面是個簡單建立ENUM列的例子：

mysql> CREATE TABLEenum_test(

-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL

-> );

mysql> INSERT INTOenum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);

·BIT

如果需要讓你設計一個表示布爾值的字段要求占用的空間最少，你會如何去設計？用INT,還是用CHAR(1)?相比INT以及CHAR(1)而言BIT(1)或許是個更好的選擇，因為它占用的空間隻是一個BIT。它可以通過BIT(N)的方式來表達多個BIT的值，這種方式最大支援到BIT(64)。

在MySQL5.0之前的版本中，BIT被認為是和TINYINT等同的，在新的版本中被作為兩種完全不同的類型來對待。

當你把一個BIT字段從資料庫中檢索出來顯示在控制台上時，值會被顯示成ASCII編碼，當字段的值出在一個數字運算的上下文時，它會被當成是BIT的十進制的值，下面的一個例子可以很清楚的說明這兩種情況

mysql>CREATE TABLE bittest(a bit(8));

mysql> INSERT INTObittest VALUES(b’00111001’);

mysql> SELECT a, a+ 0 FROM bittest;

+——+——-+

| a | a + 0 |

+——+——-+

| 9 | 57 |

+——+——-+

上面的這個例子或許會讓你感到困惑，很有可能讓你不再想使用這種機制來存儲單個的位，作為一種替代方案可以把相關字段設定成CHAR(0)，NULL用來表示False,””(Empty String)表示True