2013年8月13日 星期二

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 個字還得了。)

    短小的索引值效率會更快,不只是因為需要更少的磁碟空間,也因為他們可以增加快取的擊中率,從而減少磁碟搜尋。
  • 在某些情況下,如果一個表被掃描的非常頻繁,把一個表切成兩個表會更有效。

沒有留言:

張貼留言