MySQL 高效能表設計規範

良好的邏輯設計和物理設計是高效能的基石， 應該根據系統將要執行的查詢語句來設計schema, 這往往需要權衡各種因素。

一、選擇優化的資料型別

MySQL支援的資料型別非常多， 選擇正確的資料型別對於獲得高效能至關重要。

更小的通常更好

更小的資料型別通常更快， 因為它們佔用更少的磁碟、 記憶體和CPU快取， 並且處理時需要的CPU週期也更少。

簡單就好

簡單資料型別的操作通常需要更少的CPU週期。 例如， 整型比字元操作代價更低， 因為字符集和校對規則（排序規則 ）使字元比較比整型比較更復雜。

儘量避免NULL

如果查詢中包含可為NULL 的列， 對MySQL來說更難優化， 因為可為NULL 的列使得索引、 索引統計和值比較都更復雜。 可為NULL的列會使用更多的儲存空間， 在MySQL裡也需要特殊處理。 當可為NULL的列被索引時， 每個索引記錄需要一個額外的位元組， 在MyISAM裡甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引。
當然也有例外， 例如InnoDB 使用單獨的位 (bit) 儲存NULL值， 所以對於稀疏資料有很好的空間效率。

1.整數型別

有兩種型別的數字：整數 (whole number) 和實數 (real number) 。 如果儲存整數， 可以使用這幾種整數型別：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分別使用8,16, 24, 32, 64位儲存空間。

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

有符號和無符號型別使用相同的儲存空間，並具有相同的效能 ， 因此可以根據實際情況選擇合適的型別。

你的選擇決定 MySQL 是怎麼在記憶體和磁碟中儲存資料的。 然而， 整數計算一般使用64 位的 BIGINT 整數， 即使在 32 位環境也是如此。（ 一些聚合函式是例外， 它們使用DECIMAL 或 DOUBLE 進行計算）。

MySQL 可以為整數型別指定寬度， 例如 INT(11), 對大多數應用這是沒有意義的：它不會限制值的合法範圍，只是規定了MySQL 的一些互動工具（例如 MySQL 命令列客戶端）用來顯示字元的個數。 對於儲存和計算來說， INT(1) 和 INT(20) 是相同的。

2.實數型別

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

FLOAT和DOUBLE型別支援使用標準的浮點運算進行近似計算。
DECIMAL型別用於儲存精確的小數。
浮點和DECIMAL型別都可以指定精度。 對於DECIMAL列， 可以指定小數點前後所允許的最大位數。這會影響列的空間消耗。

有多種方法可以指定浮點列所需要的精度， 這會使得MySQL選擇不同的資料型別，或者在儲存時對值進行取捨。 這些精度定義是非標準的，所以我們建議只指定資料型別，不指定精度。

浮點型別在儲存同樣範圍的值時， 通常比DECIMAL使用更少的空間。FLOAT使用4個位元組儲存。DOUBLE佔用8個位元組，相比FLOAT有更高的精度和更大的範圍。和整數型別一樣， 能選擇的只是儲存型別； MySQL使用DOUBLE作為內部浮點計算的型別。

因為需要額外的空間和計算開銷，所以應該儘量只在對小數進行精確計算時才使用DECIMAL。但在資料最比較大的時候， 可以考慮使用BIGINT代替DECIMAL, 將需要儲存的貨幣單位根據小數的位數乘以相應的倍數即可。

3.字串型別

VARCHAR

用於儲存可變⻓字串，長度支援到65535
需要使用1或2個額外位元組記錄字串的長度
適合：字串的最大⻓度比平均⻓度⼤很多；更新很少

CHAR

定⻓，⻓度範圍是1~255
適合：儲存很短的字串，或者所有值接近同一個長度；經常變更

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)儲存’hello’的空間開銷是一樣的。 那麼使用更短的列有什麼優勢嗎？

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

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

4.BLOB和TEXT型別

BLOB和 TEXT都是為儲存很大的資料而設計的字串資料型別， 分別採用 二進位制和字元方式儲存 。

與其他型別不同， MySQL把每個BLOB和TEXT值當作一個獨立的物件處理。 儲存引擎在儲存時通常會做特殊處理。 當BLOB和TEXT值太大時，InnoDB會使用專門的 “外部“儲存區域來進行儲存， 此時每個值在行內需要1 – 4個位元組儲存 儲存區域儲存實際的值。

BLOB 和 TEXT 之間僅有的不同是 BLOB 型別儲存的是二進位制資料， 沒有排序規則或字符集， 而 TEXT型別有字符集和排序規則

5.日期和時間型別

大部分時間型別都沒有替代品， 因此沒有什麼是最佳選擇的問題。 唯一的問題是儲存日期和時間的時候需要做什麼。 MySQL提供兩種相似的日期型別： DATE TIME和 TIMESTAMP。

但是目前我們更建議儲存時間戳的方式，因此該處不再對 DATE TIME和 TIMESTAMP做過多說明。

5.其他型別

5.1選擇識別符號

在可以滿足值的範圍的需求， 井且預留未來增長空間的前提下， 應該選擇最小的資料型別。

整數型別

整數通常是標識列最好的選擇， 因為它們很快並且可以使用AUTO_INCREMENT。

ENUM和SET型別

對於標識列來說，EMUM和SET型別通常是一個糟糕的選擇， 儘管對某些只包含固定狀態或者型別的靜態 ”定義表” 來說可能是沒有問題的。ENUM和SET列適合儲存固定資訊， 例如有序的狀態、 產品型別、 人的性別。

字串型別

如果可能， 應該避免使用字串型別作為標識列， 因為它們很消耗空間， 並且通常比數字型別慢。

對於完全 “隨機” 的字串也需要多加註意， 例如 MDS() 、 SHAl() 或者 UUID() 產生的字串。 這些函式生成的新值會任意分佈在很大的空間內， 這會導致 INSERT 以及一些SELECT語句變得很慢。如果儲存 UUID 值， 則應該移除 “-“符號。

5.2特殊型別資料

某些型別的資料井不直接與內建型別一致。 低千秒級精度的時間戳就是一個例子，另一個例子是以個1Pv4地址，人們經常使用VARCHAR(15)列來儲存IP地址，然而， 它們實際上是32位無符號整數， 不是字串。用小數點將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應該用無符號整數儲存IP地址。MySQL提供INET_ATON()和INET_NTOA()函式在這兩種表示方法之間轉換。

二、表結構設計

1.正規化和反正規化

對於任何給定的資料通常都有很多種表示方法， 從完全的正規化化到完全的反正規化化， 以及兩者的折中。 在正規化化的資料庫中， 每個事實資料會出現並且只出現一次。 相反， 在反正規化化的資料庫中， 資訊是冗餘的， 可能會儲存在多個地方。

正規化的優點和缺點

為效能提升考慮時，經常會被建議對 schema 進行正規化化設計，尤其是寫密集的場景。

• 正規化化的更新操作通常比反正規化化要快。
• 當資料較好地正規化化時，就只有很少或者沒有重複資料，所以只需要修改更少的資料。
• 正規化化的表通常更小，可以更好地放在記憶體裡，所以執行操作會更快。
• 很少有多餘的資料意味著檢索列表資料時更少需要 DISTINCT 或者 GROUP BY語句。

反正規化的優點和缺點

不需要關聯表，則對大部分查詢最差的情況——即使表沒有使用索引——是全表掃描。 當資料比記憶體大時這可能比關聯要快得多，因為這樣避免了隨機I/0。

單獨的表也能使用更有效的索引策略。

混用正規化化和反正規化化

在實際應用中經常需要混用，可能使用部分正規化化的 schema 、 快取表，以及其他技巧。
表適當增加冗餘欄位，如效能優先，但會增加複雜度。可避免表關聯查詢。

簡單熟悉資料庫正規化

2.表欄位少⽽精

• I/O高效
• 欄位分開維護簡單
• 單表1G體積 500W⾏行評估
• 單⾏行不超過200Byte
• 單表不超過50個INT欄位
• 單表不超過20個CHAR(10)欄位
• 建議單表欄位數控制在20個以內
• 拆分TEXT/BLOB，TEXT型別處理效能遠低於VARCHAR，強制生成硬碟臨時表浪費更多空間。

參考資料：

高效能mysql第三版