MySQL 高效能表設計規範

標籤：大致   pad   innodb   個數   字串   證明   情境   int   佔用   

良好的邏輯設計和實體設計是高效能的基石， 應該根據系統將要執行的查詢語句來設計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 、 緩衝表，以及其他技巧。

表適當增加冗餘欄位，如效能優先，但會增加複雜度。可避免表關聯查詢。

 簡單熟悉資料庫範式

 

第一範式(1NF)：欄位值具有原子性,不能再分(所有關係型資料庫系統都滿足第一範式);

例如：姓名欄位,其中姓和名是一個整體,如果區分姓和名那麼必須設立兩個獨立欄位;

 

 

第二範式(2NF)：一個表必須有主鍵,即每行資料都能被唯一的區分;

備忘：必須先滿足第一範式;

 

 

第三範式(3NF)：一個表中不能包涵其他相關表中非關鍵字段的資訊,即資料表不能有冗餘欄位;

備忘：必須先滿足第二範式;

 

2.表欄位少而精

 

• I/O高效

• 欄位分開維護簡單

• 單表1G體積 500W行評估

• 單行不超過200Byte

• 單表不超過50個INT欄位

• 單表不超過20個CHAR(10)欄位

• 建議單表欄位數控制在20個以內

• 拆分TEXT/BLOB，TEXT類型處理效能遠低於VARCHAR，強制產生硬碟暫存資料表浪費更多空間。

 

<轉： http://mp.weixin.qq.com/s/ES2qdpD_Gzx-5mDIjHUpAA >

MySQL 高效能表設計規範