高效能MySQL (一)：Schema與資料型別優化

solocoder發表於2019-01-23
MySql資料型別優化

本文摘錄總結自《高效能MySQL》(第三版)，將以每章一篇文章的方式帶大家讀這本資料庫經典之作。總結精華，幫大家快速抓住重點資訊，節省寶貴時間。

這章概念性東西比較多，可能有點枯燥。但講了很多底層原理，堅持讀下來還是會有一些收穫的。

100 多位經驗豐富的開發者參與，在 Github 上獲得了近 1000 個 star 的全棧全平臺開源專案想了解下嗎？
專案地址：github.com/cachecats/c…

一、選擇優化的資料型別

MySQL 支援的資料型別非常多，選擇正確的資料型別至關重要。下面的幾個簡單原則有助於做出更好的選擇。

  • 更小的通常更好

    一般情況下，應該儘量使用可以正確儲存資料的最小資料型別。更小的資料型別通常更快，因為它們佔用更少的磁碟、記憶體和CPU快取，並且處理時需要的CPU週期也更少。

    但是要確保沒有低估需要儲存的值的範圍，因為在schema中的多個地方增加資料型別的範圍是一個非常耗時和痛苦的操作。如果無法確定哪個資料型別是最好的，就選擇你認為不會超過範圍的最小型別。

  • 簡單就好

    簡單資料型別的操作通常需要更少的CPU週期。例如，整型比字元操作代價更低，因為字符集和校對規則（排序規則）使字元比較比整型比較更復雜。這裡有兩個例子：一個是應該使用MySQL內建的型別(2)而不是字串來儲存日期和時間，另外一個是應該用整型儲存IP地址。稍後我們將專門討論這個話題。

  • 儘量避免NULL

    通常情況下最好指定列為NOT NULL，除非真的需要儲存NULL值。

    如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的儲存空間，在MySQL裡也需要特殊處理。如果計劃在列上建索引，就應該儘量避免設計成可為NULL的列。

1.1 整數型別

有兩種型別的數字：整數（whole number）和實數（real number）。如果儲存整數，可以使用這幾種整數型別：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分別使用8，16，24，32，64位儲存空間。

整數型別有可選的UNSIGNED屬性，表示不允許負值，這大致可以使正數的上限提高一倍。例如TINYINT UNSIGNED可以儲存的範圍是0～255，而TINYINT的儲存範圍是−128～127。

有符號和無符號型別使用相同的儲存空間，並具有相同的效能，因此可以根據實際情況選擇合適的型別。

MySQL可以為整數型別指定寬度，例如INT（11），對大多數應用這是沒有意義的：它不會限制值的合法範圍，只是規定了MySQL的一些互動工具（例如MySQL命令列客戶端）用來顯示字元的個數。對於儲存和計算來說，INT（1） 和 INT（20）是相同的。

1.2 實數型別

實數是帶有小數部分的數字。然而，它們不只是為了儲存小數部分；也可以使用DECIMAL儲存比BIGINT還大的整數。MySQL既支援精確型別，也支援不精確型別。

FLOAT 和 DOUBLE型別支援使用標準的浮點運算進行近似計算。DECIMAL型別用於儲存精確的小數。

CPU不支援對DECIMAL的直接計算，5.0以及更高版本中，MySQL伺服器自身實現了DECIMAL的高精度計算。相對而言，CPU直接支援原生浮點計算，所以浮點運算明顯更快。

定義列的時候建議只指定資料型別，不指定精度。

因為需要額外的空間和計算開銷，所以應該儘量只在對小數進行精確計算時才使用DECIMAL——例如儲存財務資料。但在資料量比較大的時候，可以考慮使用BIGINT代替DECIMAL，將需要儲存的貨幣單位根據小數的位數乘以相應的倍數即可。

1.3 字串型別

VARCHAR

VARCHAR型別用於儲存可變長字串，是最常見的字串資料型別。它比定長型別更節省空間，因為它僅使用必要的空間。

VARCHAR需要使用1或2個額外位元組記錄字串的長度：如果列的最大長度小於或等於255位元組，則只使用1個位元組表示，否則使用2個位元組。

VARCHAR節省了儲存空間，所以對效能也有幫助。但是，由於行是變長的，在UPDATE時可能使行變得比原來更長，這就導致需要做額外的工作。

下面這些情況下使用VARCHAR是合適的：

  • 字串列的最大長度比平均長度大很多；
  • 列的更新很少，所以碎片不是問題；
  • 使用了像UTF-8這樣複雜的字符集，每個字元都使用不同的位元組數進行儲存。

最好的策略是隻分配真正需要的空間，不要太慷慨，因為更長的列會消耗更多的記憶體。

CHAR

CHAR型別是定長的：MySQL總是根據定義的字串長度分配足夠的空間。當儲存CHAR值時，MySQL會刪除所有的末尾空格。

CHAR適合儲存很短的字串，或者所有值都接近同一個長度。例如，CHAR非常適合儲存密碼的MD5值，因為這是一個定長的值。

對於經常變更的資料，CHAR也比VARCHAR更好，因為定長的CHAR型別不容易產生碎片。對於非常短的列，CHAR 比 VARCHAR 在儲存空間上更有效率，因為 VARCHAR 還需要一個記錄長度的額外位元組。

BLOB 和 TEXT 型別

BLOB 和 TEXT都是為儲存很大的資料而設計的字串資料型別，分別採用二進位制和字元方式儲存。

實際上，它們分別屬於兩組不同的資料型別家族：字元型別是TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；對應的二進位制型別是TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB 。 BLOB 是 SMALLBLOB的同義詞，TEXT 是 SMALLTEXT的同義詞。

MySQL對BLOB 和 TEXT列進行排序與其他型別是不同的：它只對每個列的最前max_sort_length 位元組而不是整個字串做排序。如果只需要排序前面一小部分字元，則可以減小max_sort_length的配置，或者使用ORDER BY SUSTRING（column，length ） 。

使用列舉（ENUM）代替字串型別

有時候可以使用列舉列代替常用的字串型別。列舉列可以把一些不重複的字串儲存成一個預定義的集合。MySQL在儲存列舉時非常緊湊，會根據列表值的數量壓縮到一個或者兩個位元組中。MySQL在內部會將每個值在列表中的位置儲存為整數，並且在表的.frm檔案中儲存“數字-字串”對映關係的“查詢表”。

列舉最不好的地方是，字串列表是固定的，新增或刪除字串必須使用ALTER TABLE。除非能接受只在列表末尾新增元素，否則使用列舉不是個好主意。

1.4 日期和時間型別

MySQL可以使用許多型別來儲存日期和時間值，例如YEAR 和 DATE。MySQL能儲存的最小時間粒度為秒（MariaDB支援微秒級別的時間型別）。但是MySQL也可以使用微秒級的粒度進行臨時運算，我們會展示怎麼繞開這種儲存限制。

MySQL 提供兩種相似的日期型別，DATETIME 和 TIMESTAMP。對於很多應用程式，它們都能工作，但是在某些場景，一個比另一個工作得好。

DATETIME

這個型別能儲存大範圍的值，從1001年到9999年，精度為秒。它把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數中，與時區無關。使用8個位元組的儲存空間。 預設情況下，MySQL以一種可排序的、無歧義的格式顯示DATETIME值，例如“2008-01-16 22:37:08”。這是ANSI標準定義的日期和時間表示方法。

TIMESTAMP

就像它的名字一樣，TIMETAMP型別儲存了從1970年1月1日午夜（格林尼治標準時間）以來的秒數，它和UNIX時間戳相同。TIMESTAMP只使用4個位元組的儲存空間，因此它的範圍比DATETIME小得多：只能表示從1970年到2038年。

TIMESTAMP顯示的值也依賴於時區。MySQL伺服器、作業系統，以及客戶端連線都有時區設定。

有必要強調一下這個區別：如果在多個時區儲存或訪問資料，TIMESTAMP 和 DATETIME的行為將很不一樣。前者提供的值與時區有關係，後者則保留文字表示的日期和時間。

TIMESTAMP 的特殊屬性：

在插入資料時如果沒有指定值，會自動填充為當前時間。

TIMESTAMP 預設為 NOT NULL。

通常應該儘量使用 TIMESTAMP，因為它比 DATETIME 空間效率更高。

如果需要儲存比秒更小粒度的日期和時間值，可以使用BIGINT型別儲存微秒級別的時間截，或者使用DOUBLE儲存秒之後的小數部分。這兩種方式都可以，或者也可以使用MariaDB替代MySQL。

1.5 選擇識別符號（identifier）

為標識列（identifier column）選擇合適的資料型別非常重要。

標識列也可能在另外的表中作為外來鍵使用，所以為標識列選擇資料型別時，應該選擇跟關聯表中的對應列一樣的型別。混用不同型別可能導致效能問題，即使沒有效能影響，在比較操作時隱式的型別轉換也可能導致很難發現的錯誤。

在可以滿足值的範圍的需求，並且預留未來增長空間的前提下，應該選擇最小的資料型別。下面是一些小技巧：

  • 整數型別

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

  • ENUM 和 SET 型別

    對於標識列來說 ENUM 和 SET 型別通常是比較糟糕的選擇，應儘量避免用這種型別。

  • 字串型別

    字串型別很消耗空間，且通常比數字型別慢，所以也應避免使用字串作為標識列。

    對於完全“隨機”的字串也需要多加註意，例如MD5()、SHA1()或者UUID()產生的字串。這些函式生成的新值會任意分佈在很大的空間內，這會導致INSERT以及一些SELECT語句變得很慢。

    如果儲存UUID值，則應該移除“-”符號；或者更好的做法是，用UNHEX()函式轉換UUID值為16位元組的數字，並且儲存在一個BINARY（16）列中。檢索時可以通過HEX()函式來格式化為十六進位制格式。

1.6 特殊資料型別

某些型別的資料並不直接與內建型別一致。這裡有兩個例子：

  1. 低於秒級精度的時間戳

    前面也介紹了，建議使用 BIGINT 型別儲存時間戳。

  2. IPv4 地址

    人們經常使用VARCHAR（15）列來儲存IP地址。然而，它們實際上是32位無符號整數，不是字串。用小數點將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應該用無符號整數儲存IP地址。MySQL提供INET_ATON() 和 INET_NTOA()函式在這兩種表示方法之間轉換。

二、MySQL schema設計中的陷阱

雖然有一些普遍的好或壞的設計原則，但也有一些問題是由MySQL的實現機制導致的，這意味著有可能犯一些只在MySQL下發生的特定錯誤。本節我們討論設計MySQL的schema的問題。這也許會幫助你避免這些錯誤，並且選擇在MySQL特定實現下工作得更好的替代方案。

太多的列

MySQL的儲存引擎API工作時需要在伺服器層和儲存引擎層之間通過行緩衝格式拷貝資料，然後在伺服器層將緩衝內容解碼成各個列。從行緩衝中將編碼過的列轉換成行資料結構的操作代價是非常高的。MyISAM的定長行結構實際上與伺服器層的行結構正好匹配，所以不需要轉換。然而，MyISAM的變長行結構和InnoDB的行結構則總是需要轉換。轉換的代價依賴於列的數量。當我們研究一個CPU佔用非常高的案例時，發現客戶使用了非常寬的表（數千個欄位），然而只有一小部分列會實際用到，這時轉換的代價就非常高。如果計劃使用數千個欄位，必須意識到伺服器的效能執行特徵會有一些不同。

太多的關聯

所謂的“實體-屬性-值”（EAV）設計模式是一個常見的糟糕設計模式，尤其是在MySQL下不能靠譜地工作。MySQL限制了每個關聯操作最多隻能有61張表，但是EAV資料庫需要許多自關聯。我們見過不少EAV資料庫最後超過了這個限制。事實上在許多關聯少於61張表的情況下，解析和優化查詢的代價也會成為MySQL的問題。一個粗略的經驗法則，如果希望查詢執行得快速且併發性好，單個查詢最好在12個表以內做關聯。

全能的列舉

注意防止過度使用列舉（ENUM）。下面是我們見過的一個例子：

CREATE TABLE ... (       
	country enum('','0','1','2',...,'31')
複製程式碼

這種模式的schema設計非常凌亂。這麼使用列舉值型別也許在任何支援列舉型別的資料庫都是一個有問題的設計方案，這裡應該用整數作為外來鍵關聯到字典表或者查詢表來查詢具體值。但是在MySQL中，當需要在列舉列表中增加一個新的國家時就要做一次ALTER TABLE操作。在MySQL 5.0以及更早的版本中ALTER TABLE是一種阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也會一樣需要ALTER TABLE。

變相的列舉

列舉（ENUM）列允許在列中儲存一組定義值中的單個值，集合（SET）列則允許在列中儲存一組定義值中的一個或多個值。有時候這可能比較容易導致混亂。這是一個例子：

CREATE TABLE ... (       
	is_default set ('Y','N') NOT NULL default 'N' 
複製程式碼

如果這裡真和假兩種情況不會同時出現，那麼毫無疑問應該使用列舉列代替集合列。

非此發明（Not Invent Here）的NULL

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

但是遵循這個原則也不要走極端。當確實需要表示未知值時也不要害怕使用NULL。在一些場景中，使用NULL可能會比某個神奇常數更好。從特定型別的值域中選擇一個不可能的值，例如用−1代表一個未知的整數，可能導致程式碼複雜很多，並容易引入bug，還可能會讓事情變得一團糟。處理NULL確實不容易，但有時候會比它的替代方案更好。

三、正規化和反正規化

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

3.1 正規化的優點和缺點

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

  • 正規化化的更新操作通常比反正規化化要快。

  • 當資料較好地正規化化時，就只有很少或者沒有重複資料，所以只需要修改更少的資料。

  • 正規化化的表通常更小，可以更好地放在記憶體裡，所以執行操作會更快。

  • 很少有多餘的資料意味著檢索列表資料時更少需要DISTINCT或者GROUP BY語句。

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

3.2 反正規化的優點和缺點

反正規化化的schema因為所有資料都在一張表中，可以很好地避免關聯。 如果不需要關聯表，則對大部分查詢最差的情況——即使表沒有使用索引——是全表掃描。當資料比記憶體大時這可能比關聯要快得多，因為這樣避免了隨機 I/O 。

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

3.3 混用正規化化和反正規化化

正規化化和反正規化化的schema各有優劣，怎麼選擇最佳的設計？

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

最常見的反正規化化資料的方法是複製或者快取，在不同的表中儲存相同的特定列。在MySQL 5.0和更新版本中，可以使用觸發器更新快取值，這使得實現這樣的方案變得更簡單。

好啦，本章的內容就到這裡啦，我們下期見~

全棧全平臺開源專案 CodeRiver

CodeRiver 是一個免費的專案協作平臺，願景是打通 IT 產業上下游，無論你是產品經理、設計師、程式設計師或是測試，還是其他行業人員，只要有好的創意、想法，都可以來 CodeRiver 免費釋出專案，召集志同道合的隊友一起將夢想變為現實！

CodeRiver 本身還是一個大型開源專案，致力於打造全棧全平臺企業級精品開源專案。涵蓋了 React、Vue、Angular、小程式、ReactNative、Android、Flutter、Java、Node 等幾乎所有主流技術棧，主打程式碼質量。

目前已經有近 100 名優秀開發者參與，github 上的 star 數量將近 1000 個。每個技術棧都有多位經驗豐富的大佬坐鎮，更有兩位架構師指導專案架構。無論你想學什麼語言處於什麼技術水平，相信都能在這裡學有所獲。

通過 高質量原始碼 + 部落格 + 視訊，幫助每一位開發者快速成長。

專案地址：github.com/cachecats/c…

您的鼓勵是我們前行最大的動力，歡迎點贊，歡迎送小星星✨ ~

高效能MySQL (一)：Schema與資料型別優化

相關文章