MySQL 最佳化 - 讓資料最小化 (官方文件翻譯)

原文：8.8.1. Make Your Data as Small as Possible

(不負責任翻譯)
(嚴禁轉貼內容，只能引用連結)

設計資料表時，以使用最小空間為原則。這樣可以大幅的減少磁碟的讀取量從而帶來巨大的效益。在 SQL 語句的執行過程中，較小的資料表會要求較少的記憶體空間。較小的主鍵也可以讓執行速度更快。

MySQL 提供很多種儲存引擎。選擇適當的種類可以為你帶來更好的效益。

下面列出幾個方法，可以提高效率，以及減少儲存空間

• 盡可能使用最小的欄位型態。例如 MEDIUMINT 比 INT 還要好，可以減少 25% 的空間。
• 盡可能宣告欄位是 NOT NULL。這會任何事加快，而且可以讓每個欄位額外節省 1 個bit。
• MyISM 類型的資料表，使用固定長度的型態可能會浪費空間。在使用可變動長度的欄位型態時，例如 VARCHAR ，可以使用下列指令來固定長度 ROW_FORMAT=FIXED 。(疑問：把 VARCHAR 固定長度，那佔用的空間不是一樣大嗎？ 另外這篇 MySQL數據庫中CHAR與VARCHAR之爭 好像講的蠻有道理，其實也沒看懂。目前知道的差別是， CHAR 的字串會自動以空格填補，VARCHAR 則不會。
• InnoDB 型態的資料表使用一種壓縮格式來儲存資料。在更早版本的 InnoDB ，系統保存一些冗餘資料，例如欄位數量、每個欄位的長度等。從 MySQL 5.0.3，系統預設使用 ROW_FORMAT=COMPACT 指令來創建資料表。(大概言下之意是不保存那些冗餘資料) 如果要降級到較舊版本，可以使用 ROW_FORMAT=REDUNDANT 。
  
  使用壓縮的方式，每列儲存空間可以減少約 20% ，但是會增加 CPU 負擔。如果主機效率原本的瓶頸在於磁碟讀取，那這種方式可能會加快效率。但如果瓶頸在於 CPU ，則反而會降低效率。(補充：目前主流的 CPU 四核、八核，相對於使用上來講應該都非常強大。硬碟雖有 SSD 但因為較貴及壽命問題，多數仍使用 Sata 硬碟。所以應該是可以用壓縮格式，從硬碟讀少一點資料，然後讓 CPU 去做解壓縮的運算。)
  
  使用壓縮格式也會變更 CHAR 型態保存 UTF-8 資料的方式。使用 ROW_FORMAT=REDUNDANT , 一個 UTF-8 CHAR(N) 會佔用 3 x N bytes。一個 UTF-8 編碼的字元，最大長度是 3 個 bytes。很多語言可以只使用 1 個位元的 UTF-8 編碼。所以固定長度可能會導致空間的浪費。使用 ROW_FORMAT=COMPACT ， 系統會分配一個可變動長度，範圍從 N 到 3 x N 個 bytes。方式是去掉尾部多餘的空間。所以通常最小儲存空間是 N bytes。
• 主索引值要盡可能的小。

• 只創建必要的索引。
• 很有可能一個字串在開頭幾個字就會不一樣。如果是這樣的話，創建索引時使用開頭這幾個字就好，不用整個欄位值都拿來做索引值(原文提供參考文章 13.1.8. CREATE INDEX Syntax) (直接把參考文章的重點語法寫下來：CREATE INDEX part_of_name ON customer (name(10)); 資料表是customer，欄位名稱 name，創建一個索引，只要取 name 的前 10 個字。這在英文姓名可能有用，中文姓名就沒用了。 3 個字同名同姓就一堆了，只取 2 個字還得了。)。
  
  短小的索引值效率會更快，不只是因為需要更少的磁碟空間，也因為他們可以增加快取的擊中率，從而減少磁碟搜尋。
• 在某些情況下，如果一個表被掃描的非常頻繁，把一個表切成兩個表會更有效。