如何設計出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做過多說明。

6.其他型別

#6.1選擇標識符

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

• 整數型別

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

• ENUM和SET類型

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

• 字串型別

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

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

6.2特殊型別資料

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

二、表格結構設計

1.範式與反範式

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

範式的優點和缺點

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

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

反範式的優點和缺點

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

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

混用範式化和反範式化

在實際應用中經常需要混用，可能使用部分範式化的 schema 、 快取表，以及其他技巧。

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

簡單熟悉資料庫範式

#

<br># 第一範式(1NF)：字段值具有原子性,不能再分(所有關係型資料庫系統都滿足第一範式);<br> 例如：姓名欄位,其中姓和名是一個整體,如果區分姓和名那麼必須設立兩個獨立欄位;

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

第三範式(3NF)：一個表格中不能包涵其他相關表格中非關鍵字段的資訊,即資料表不能有沈餘欄位;
備註：必須先滿足第二範式;

2.表格欄位少精

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

以上是如何設計出MySQL高效能表的詳細內容。更多資訊請關注PHP中文網其他相關文章！