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

一、選擇優化的數據類型

MySQL支持的數據類型非常多，選擇正確的數據類型對于獲得高性能至關重要。不管 存儲哪種類型的數據，下面幾個簡單的原則都有助于做出更好的選擇。

• 更小的通常更好

  更小的數據類型通常更快，因為它們占用更少的磁盤、內存和CPU緩存，并且處理時需要的CPU周期也更少。

• 簡單就好

  簡單數據類型的操作通常需要更少的CPU周期。

• 盡量避免NULL

  如果査詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使 得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在 MySQL里也需要特殊處理。

在為列選擇數據類型時：

第一步需要確定合適的大類型：數字、字符串、時間等；

第二步是選擇具體類型。很多MySQL的數據類型可以存儲相同類型的數據，只是存儲 的長度和范圍不一樣、允許的精度不同，或者需要的物理空間不同（相同大類型的不同子類型數據有時也有一些特殊的行為和屬性）。

1.1、整數類型

有兩種類型的數字：整數和實數。

整數類型：

• TINYINT
  1字節、【-128，127】、【0，255】
• SMALLINT
  2字節、【-32768，32767】、【0，65535】
• MEDIUMINT
  3字節、【-2147483648，2147483647】、【0，4294967295】
• INT
  4字節、【-2147483648，2147483647】、【0，4294967295】
• BIGINT
  8字節、【-263，263-1】、【0，264-1】

整數類型有可選的UNSIGNED屬性，表示不允許為負數，大致可以使得正數的上限提高一倍。

有符號和無符號具有相同的存儲空間和性能，根據實際情況選擇合適的類型。

Tips:整數計算一般使用 64位的BIGINT整數，即使在32位環境也是如此，不同的數據可以決定的只有MySQL是怎么在內存和磁盤中保存數據的。

例如INT(11),對大多數應用這是沒有意義的：它不會限制值的合法范圍，只是規定了MySQL的一些交互工具(例如MySQL命令行客戶端) 用來顯示字符的個數。對于存儲和計算來說，INT(1)和INT(20)是相同的

2.2、實數類型

實數是帶有小數部分的數字。

• FLOAT
  單精度，4字節
• DOUBLE
  雙精度，8字節
• DECIMAL
  存儲精確的小數

FLOAT和DOUBLE使用標準的浮點運算進行近似運算，如果需要知道浮點運算是 怎么計算的，則需要研究所使用的平臺的浮點數的具體實現。

DECIMAL支持精確計算，但CPU不支持對DECIMAL的直接計算，MySQL自己實現了DECIMAL的高精度計算，所以DECIMAL在性能上要弱一些。

Tips:DECIMAL需要額外的空間和計算消耗，當數據量比較大時，可以考慮使用BITINT來代替，將存儲的數據根據小數的位數乘以相應的倍數即可。這樣可以解決浮點類型計算不準確，DECIMAL計算開銷太大的問題。

2.3、字符串類型

VARCHAR

VARCHAR用于存儲可變長的字符，比定長更節省空間，越短的字符占用空間越少。
但有一種例外：當表使用“ROW_FORMAT=FIXED”創建時，每一行都會使用定長存儲，浪費空間。

VARCHAR需要使用1或2個額外字節記錄字符串的長度：如果列的最大長度小于或 等于255字節，則只使用1個字節表示，否則使用2個字節。假設采用latinl字符集， 一個VARCHAR(IO)的列需要11個字節的存儲空間。VARCHAR(1000)的列則需要1002 個字節，因為需要2個字節存儲長度信息

VARCHAR節省了存儲空間，對性能也有幫助。但是由于行是變長的，所以UPDATE時可能比原來更長，這就需要MySQL為其額外再分配存儲空間，導致UPDATE時開銷比定長類型要大。

CHAR

CHAR類型是定長的，MySQL會根據定義的長度去分配存儲空間，所以不會有VARCHAR進行UPDATE時的額外開銷。
CHAR類型存儲時，會自動去除末尾的空格，這一點需要注意。

CHAR適合存儲短的，長度固定的字符，例如MD5值，UUID等…

由于UPDATE時沒有額外的開銷，對于經常變更的數據，CHAR的性能也比VARCHAR更好。

BLOB和TEXT類型

BLOB和TEXT都是為了存儲很大的數據而設計的字符串類型，分別采用二進制和字符的方式進行存儲。

它們分別屬于不同的數據類型家族：

字符類型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
二進制類型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

與其他類型不同，MySQL會將BLOB和TEXT當做單獨的對象處理。

當值太大時，MySQL會使用專門的存儲區域來存儲數據，行內使用1~4字節來存儲一個指針，指向對應的大文本字符。

BLOB和TEXT的不同之處在于：由于BLOB是二進制，所以沒有字符集和排序規則，但是TEXT有。

即使TEXT有排序規則，MySQL對其進行排序時，也不會對整個文本進行排序，只會對前max_sort_length字節進行排序，可以通過修改max_sort_length進行配置。

MySQL不能將BLOB和TEXT全部長度的字符進行索引。

使用枚舉（enum）代替字符串類型

枚舉可以把一些不重復的字符串存儲成一個預定義的集合，MySQL在存儲枚舉時非常緊湊，會根據列表值壓縮到1到2個字節中。

MySQL在內部會將列中的枚舉值保存為整數，在.frm文件中保存一個“數字->字符串”的映射關系，通過數字快速的查找到具體的枚舉值。

枚舉字段排序時，并不會按照給定的字符串排序，而是根據內部的整數排序，所以建議列舉枚舉時按照預想的順序給出。

日期和時間類型

MySQL提供了多種類型來保存時間和日期，例如：YEAR、DATE、DATETIME。
MySQL能存儲的最小時間粒度為秒（有的第三方存儲引擎支持微秒）。

MySQL提供了兩種相似的事件類型：DATETIME和TIMESTAMP。

DATETIME

用來保存大范圍的時間，從1001年到9999年，精度為秒。
它把時間封裝到格式為YYYYMMDDHHMMSS的整數中，與時區無關，使用8個字節來存儲。

TIMESTAMP

保存了從1970年1月1日凌晨以來的秒數，和UNIX時間戳相同。
使用4個字節來保存，比DATETIME節省空間，具有更高的性能。
但是范圍比DATETIME要小得多，只能存儲1970年到2038年。

TIMESTAMP顯示的值依賴于時區，MySQL服務器，操作系統以及客戶端的連接都有時區的設置。

除了特殊行為之外，應該盡量使用TIMESTAMP，它比DATETIME空間效率要高。

如果需要存儲比秒更小粒度的時間，MySQL目前沒有提供合適的數據類型，可以考慮使用BIGINT來存儲微秒級別的時間戳。

2.4、位數據類型

可以使用BIT列存儲一個或多個true/false值，BIT(1)包含單個位的字段，最多可包含64個位。

MySQL將BIT當做字符串類型，而不是數字類型。
當查詢BIT(1)時，結果是一個包含二進制0或1的字符串，而不是ASCII碼中的“0”或“1”。

BIT列進行比較時，MySQL會將位字符串轉換為十進制數字進行比較。
例如：‘111’ = 7。

對于大部分應用，最好慎用BIT類型。

2.5、選擇標識符

為標識列選擇合適的數據類型十分重要。

一般來說標識列很可能用來在不同的表之間進行比較，甚至作為外鍵來使用。
合適的數據類型可以提升系統的整體性能，減少數據比較的系統開銷。

一旦選定了類型，一定要確保關聯表中也是相同的數據類型，混用不同的數據類型會帶來很多麻煩。
例如：將字符串與整形做比較，會導致嚴重的性能問題。

一般來說，在沒有特殊要求的情況下，整型 通常是標識列最好的選擇，因為它很快，而且可以自動遞增。

如果可以的話，應該盡量避免使用字符串當做標識列，它很消耗空間，而且比整型慢。

很多人喜歡用隨機的字符串來作為標識列，例如：UUID。
由于生成的字符沒有規律，會導致INSERT和SELECT語句變得很慢：

• 插入的值會隨機的寫入到索引的不同位置，使得INSERT更慢。這會導致頁分裂，磁盤隨機訪問，以及對于聚簇存儲引擎產生聚簇索引碎片。
• SELECT語句變慢，因為邏輯上相鄰的數據會分布在磁盤的不同地方。
• 隨機值導致緩存對所有類型的查詢語句效果都很差，因為會使得緩存賴以工作的訪問“局部性原理”失效。緩存無法命中，加載到內存中也是徒勞。

Tips：如果需要使用UUID當做標識列，那么應該移除“-”這種沒有意義的字符。
最好的解決方案是：用UNHEX()將UUID轉換為16位的二進制數據，沒有字符集，沒有排序，而且占用更少的磁盤空間，可以很好的提升性能。

2.6、特殊類型數據

有些類型的數據并不直接與MySQL的內置類型一致，微秒型的時間戳就是個例子。

還有例如：IPv4地址，應該使用無符號的整數來保存，而非字符串。
MySQL內置的函數INET_ATON和INET_NTOA可以很好的轉換。

二、Schema設計中的陷阱

雖然有一些普遍的好或壞的設計原則，但也有一些問題是由MySQL的實現機制導致的， 這意味著有可能犯一些只在MySQL下發生的特定錯誤。

了解討論設計MySQL的 schema的問題。這也許會幫助我們避免這些錯誤，并且選擇在MySQL特定實現下工作得更好的替代方案。

• 太多的列
  MySQL存儲引擎工作時，需要在服務器層和存儲引擎層之間做行緩沖格式拷貝數據，然后在服務器層之間將緩沖內容解碼成各個列。從行緩沖中將編碼過的列轉換成行數據結構的操作代價是非常高的。
  如果單張表的列太多，就應該要考慮做表的拆分。

• 太多的關聯
  MySQL限制了每個關聯最多只能61張表，一個粗略的經驗法則：如果希望査詢執行得快速且并發性好,單個查詢最好在12張表以內做關聯。

• 全能的枚舉

  注意防止過度使用枚舉(ENUM)，在MySQL 5.0以及更早的版本中ALTER TABLE是一 種阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也會一樣需 要ALTER TABLE 。

• Not Invent Here 的 NULL

  我們之前寫了避免使用NULL的好處，并且建議盡可能地考慮替代方案。即使需要存 儲一個事實上的“空值”到表中時，也不一定非得使用NULLO也許可以使用0、某個特殊值，或者空字符串作為代替。

  但是遵循這個原則也不要走極端。當確實需要表示未知值時也不要害怕使用NULL在一些場景中，使用NULL可能會比某個神奇常數更好。

三、范式和反范式

對于任何給定的數據通常都有很多種表示方法，從完全的范式化到完全的反范式化，以及兩者的折中。

設計關系型數據庫時，需要遵從不同的規范，設計合理的關系型數據庫，不同的規范被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余約小。在關系型數據庫中有六中范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），BCNF，第四范式（4NF），第五范式(5NF)。一般數據庫設計到第三范式就行了

這里簡單介紹一下三大范式：

• 第一范式
  確保數據表中每列（字段）的原子性。
  如果數據表中每個字段都是不可再分的最小數據單元，則滿足第一范式。
• 第二范式
  在第一范式的基礎上更進一步，目標是確保表中的每列都和主鍵相關。
  如果一個關系滿足第一范式，并且除了主鍵之外的其他列，都依賴于該主鍵，則滿足第二范式。
• 第三范式
  在第二范式的基礎上更進一步，目標是確保表中的列都和主鍵直接相關，而不是間接相關。通過第三張表（中間表）來建立用戶表和角色表之間的關系，同時又符合范式化的原則，就可以稱為第三范式。
• 反范式化
  反范式化指的是通過增加冗余或重復的數據來提高數據庫的讀性能。

3.1、范式的優點和缺點

當為性能問題而尋求幫助時，經常會被建議對schema進行范式化設計，尤其是寫密集 的場景。這通常是個好建議。因為下面這些原因，范式化通常能夠帶來好處：

• 范式化更新操作通常比反范式化要快。
• 當數據較好的范式化時，就只有很少或者沒有重復數據，所以，只需要修改更少的數據。
• 范式化的表通常更小，可以更好地放在內存里，所以執行操作會更快。
• 很少有多余的數據意味著檢索列表數據更少需要distinct或者group by 語句。

范式化設計的schema的缺點是通常需要關聯。稍微復雜一些的査詢語句在符合范式的 schema上都可能需要至少一次關聯，也許更多。這不但代價昂貴，也可能使一些索引策 略無效。例如，范式化可能將列存放在不同的表中，而這些列如果在一個表中本可以屬 于同一個索引。

3.2、反范式的優點和缺點

反范式的優點：

• 可以很好地避免關聯。
• 如果不需要關聯表，對大部分查詢最差情況，即沒有使用索引，全表掃描。當數據幣內存大時這可能比關聯要快很多， 這樣避免了隨機I/O。

在真實環境中很少會極端地使用范式化或者反范式化的schema。而是可能使用部分范式化的schema、緩存表、以及其它技巧。最常見的反范式化數據的方法是復制或者緩存，在不同的表中存儲相同的特定的列。

3.3、混用范式化和反范式化

范式化和反范式化的schema各有優劣，怎么選擇最佳的設計？

事實是，完全的范式化和完全的反范式化schema都是實驗室里才有的東西：在真實 世界中很少會這么極端地使用。在實際應用中經常需要混用，可能使用部分范式化的 schema、緩存表，以及其他技巧。

最常見的反范式化數據的方法是復制或者緩存，在不同的表中存儲相同的特定列。在 MySQL 5.0和更新版本中，可以使用觸發器更新緩存值，這使得實現這樣的方案變得更 簡單。

四、加快ALTER TABLE操作的速度

MySQL 對于大表的ALTER操作是非常慢的，因為 MySQL 對于ALTER操作的的方法是創建一個新結構的表，然后將舊結構表中的數據復制過去，最后將舊表刪除。如此操作對于海量數據的表來說花費的時間是非常長的。

一般而言，大部分ALTER TABLE操作將導致MySQL服務中斷。我們會展示一些在DDL 操作時有用的技巧，但這是針對一些特殊的場景而言的。對常見的場景，能使用的技巧 只有兩種：

一種是先在一臺不提供服務的機器上執行ALTER TABLE操作，然后和提供服 務的主庫進行切換；

另外一種技巧是影子拷貝，影子拷貝的技巧是用要求的表結構創建一張和源表無關的新表，然后通過重命名和刪表操作交換兩張表。

4.1、只修改.frm文件

如果愿意冒一些風險，可以讓MySQL做一些其他類型的修改而不用重 建表。

下面這些操作是有可能不需要重建表的：

• 移除（不是增加）一個列的AUTO_INCREMENT屬性。
• 增加、移除，或更改ENUM和SET常量。如果移除的是已經有行數據用到其值的常量， 查詢將會返回一個空字串值。

基本的技術是為想要的表結構創建一個新的**.frm文件，然后用它替換掉已經存在的那張 表的.frm**文件，像下面這樣：

1. 創建一張有相同結構的空表，并進行所需要的修改（例如增加ENUM常量）。
2. 執行FLUSH TABLES WITH READ L0CKo這將會關閉所有正在使用的表，并且禁止任何表被打開。
3. 交換**.frm**文件.
4. 執行UNLOCK TABLES來釋放第2步的讀鎖。

4.2、快速創建MylSAM索引

為了高效地載入數據到MylSAM表中，有一個常用的技巧是先禁用索引、載入數據，然后重新啟用索引，這個技巧能夠發揮作用，是因為構建索引的工作被延遲到數據完全載入以后，這個時候 已經可以通過排序來構建索引了。這樣做會快很多，并且使得索引樹注”的碎片更少、更緊湊。

不幸的是，這個辦法對唯一索引無效，因為DISABLE KEYS只對非唯一索引有效。 MylSAM會在內存中構造唯一索引，并且為載入的每一行檢査唯一性。一旦索引的大小<33 超過了有效內存大小，載入操作就會變得越來越慢。

下面是操作步驟：

1. 用需要的表結構創建一張表，但是不包括索引。

   注：如果使用的是LOAD DATA FILE,并且要載入的表是空的，MylSAM也可以通過排序來構造索引。

2. 載入數據到表中以構建.M陽 文件。

3. 按照需要的結構創建另外一張空表，這次要包含索引。這會創建需要的斤％和.心以 文件。

4. 獲取讀鎖并刷新表。

5. 重命名第二張表的為“和文件，讓MySQL認為是第一張表的文件。

6. 釋放讀鎖。

7. 使用REPAIR TABLE來重建表的索引。該操作會通過排序來構建所有索引，包括唯一 索引。

五、總結

良好的schema設計原則是普遍適用的，但MySQL有它自己的實現細節要注意。概括來 說，盡可能保持任何東西小而簡單總是好的。MySQL喜歡簡單，需要使用數據庫的人 應該也同樣會喜歡簡單的原則：

• 盡量避免過度設計，例如會導致極其復雜査詢的schema設計，或者有很多列的表設 計（很多的意思是介于有點多和非常多之間）。
• 使用小而簡單的合適數據類型，除非真實數據模型中有確切的需要，否則應該盡可 能地避免使用NULL值。
• 盡量使用相同的數據類型存儲相似或相關的值，尤其是要在關聯條件中使用的列。
• 注意可變長字符串，其在臨時表和排序時可能導致悲觀的按最大長度分配內存。
• 盡量使用整型定義標識列。
• 避免使用MySQL已經遺棄的特性，例如指定浮點數的精度，或者整數的顯示寬度。
• 小心使用ENUM和SET。雖然它們用起來很方便，但是不要濫用，否則有時候會變成 陷阱。最好避免使用BITO

范式是好的，但是反范式（大多數情況下意味著重復數據）有時也是必需的，并且能帶 來好處。

參考：

《高性能 MySQL 第三版》

Schema與數據類型優化

MySQL三大范式和反范式

[MySQL中范式與反范式的優缺點](