本文摘錄總結自《高性能MySQL》(第三版)戳玫，將以每章一篇文章的方式帶大家讀這本數(shù)據(jù)庫經(jīng)典之作丈屹∑剂模總結精華钞翔，幫大家快速抓住重點信息芬失，節(jié)省寶貴時間暂吉。

這章概念性東西比較多揖赴，可能有點枯燥诡延。但講了很多底層原理鉴象，堅持讀下來還是會有一些收獲的忙菠。

100 多位經(jīng)驗豐富的開發(fā)者參與，在 Github 上獲得了近 1000 個 star 的全棧全平臺開源項目想了解下嗎纺弊？
項目地址：https://github.com/cachecats/coderiver

一牛欢、選擇優(yōu)化的數(shù)據(jù)類型

MySQL 支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型至關重要淆游。下面的幾個簡單原則有助于做出更好的選擇傍睹。

• 更小的通常更好

  一般情況下，應該盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型犹菱。更小的數(shù)據(jù)類型通常更快拾稳，因為它們占用更少的磁盤、內(nèi)存和CPU緩存腊脱，并且處理時需要的CPU周期也更少访得。

  但是要確保沒有低估需要存儲的值的范圍，因為在schema中的多個地方增加數(shù)據(jù)類型的范圍是一個非常耗時和痛苦的操作陕凹。如果無法確定哪個數(shù)據(jù)類型是最好的悍抑，就選擇你認為不會超過范圍的最小類型。

• 簡單就好

  簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期杜耙。例如搜骡，整型比字符操作代價更低，因為字符集和校對規(guī)則（排序規(guī)則）使字符比較比整型比較更復雜泥技。這里有兩個例子：一個是應該使用MySQL內(nèi)建的類型(2)而不是字符串來存儲日期和時間浆兰，另外一個是應該用整型存儲IP地址。稍后我們將專門討論這個話題珊豹。

• 盡量避免NULL

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

  如果查詢中包含可為NULL的列店茶，對MySQL來說更難優(yōu)化蜕便，因為可為NULL的列使得索引、索引統(tǒng)計和值比較都更復雜贩幻〗蜗伲可為NULL的列會使用更多的存儲空間两嘴，在MySQL里也需要特殊處理。如果計劃在列上建索引族壳，就應該盡量避免設計成可為NULL的列憔辫。

1.1 整數(shù)類型

有兩種類型的數(shù)字：整數(shù)（whole number）和實數(shù)（real number）。如果存儲整數(shù)仿荆，可以使用這幾種整數(shù)類型：TINYINT贰您，SMALLINT，MEDIUMINT拢操，INT锦亦，BIGINT。分別使用8令境，16杠园，24，32舔庶，64位存儲空間抛蚁。

整數(shù)類型有可選的UNSIGNED屬性，表示不允許負值栖茉，這大致可以使正數(shù)的上限提高一倍篮绿。例如TINYINT UNSIGNED可以存儲的范圍是0～255，而TINYINT的存儲范圍是?128～127吕漂。

有符號和無符號類型使用相同的存儲空間，并具有相同的性能尘应，因此可以根據(jù)實際情況選擇合適的類型惶凝。

MySQL可以為整數(shù)類型指定寬度，例如INT（11）犬钢，對大多數(shù)應用這是沒有意義的：它不會限制值的合法范圍苍鲜，只是規(guī)定了MySQL的一些交互工具（例如MySQL命令行客戶端）用來顯示字符的個數(shù)。對于存儲和計算來說玷犹，INT（1） 和 INT（20）是相同的混滔。

1.2 實數(shù)類型

實數(shù)是帶有小數(shù)部分的數(shù)字。然而歹颓，它們不只是為了存儲小數(shù)部分坯屿；也可以使用DECIMAL存儲比BIGINT還大的整數(shù)。MySQL既支持精確類型巍扛，也支持不精確類型领跛。

FLOAT 和 DOUBLE類型支持使用標準的浮點運算進行近似計算。DECIMAL類型用于存儲精確的小數(shù)撤奸。

CPU不支持對DECIMAL的直接計算吠昭，5.0以及更高版本中喊括，MySQL服務器自身實現(xiàn)了DECIMAL的高精度計算。相對而言矢棚，CPU直接支持原生浮點計算郑什，所以浮點運算明顯更快。

定義列的時候建議只指定數(shù)據(jù)類型蒲肋，不指定精度蹦误。

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

1.3 字符串類型

VARCHAR

VARCHAR類型用于存儲可變長字符串偶洋，是最常見的字符串數(shù)據(jù)類型。它比定長類型更節(jié)省空間距糖，因為它僅使用必要的空間玄窝。

VARCHAR需要使用1或2個額外字節(jié)記錄字符串的長度：如果列的最大長度小于或等于255字節(jié)，則只使用1個字節(jié)表示悍引，否則使用2個字節(jié)恩脂。

VARCHAR節(jié)省了存儲空間，所以對性能也有幫助趣斤。但是俩块，由于行是變長的，在UPDATE時可能使行變得比原來更長浓领，這就導致需要做額外的工作玉凯。

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

• 字符串列的最大長度比平均長度大很多；
• 列的更新很少联贩，所以碎片不是問題漫仆；
• 使用了像UTF-8這樣復雜的字符集，每個字符都使用不同的字節(jié)數(shù)進行存儲泪幌。

最好的策略是只分配真正需要的空間盲厌，不要太慷慨，因為更長的列會消耗更多的內(nèi)存祸泪。

CHAR

CHAR類型是定長的：MySQL總是根據(jù)定義的字符串長度分配足夠的空間吗浩。當存儲CHAR值時，MySQL會刪除所有的末尾空格浴滴。

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

對于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好炕倘，因為定長的CHAR類型不容易產(chǎn)生碎片钧大。對于非常短的列，CHAR 比 VARCHAR 在存儲空間上更有效率罩旋，因為 VARCHAR 還需要一個記錄長度的額外字節(jié)啊央。

BLOB 和 TEXT 類型

BLOB 和 TEXT都是為存儲很大的數(shù)據(jù)而設計的字符串數(shù)據(jù)類型，分別采用二進制和字符方式存儲涨醋。

實際上瓜饥，它們分別屬于兩組不同的數(shù)據(jù)類型家族：字符類型是TINYTEXT，SMALLTEXT浴骂，TEXT乓土，MEDIUMTEXT，LONGTEXT溯警；對應的二進制類型是TINYBLOB趣苏，SMALLBLOB，BLOB梯轻，MEDIUMBLOB食磕，LONGBLOB 。 BLOB 是 SMALLBLOB的同義詞喳挑，TEXT 是 SMALLTEXT的同義詞彬伦。

MySQL對BLOB 和 TEXT列進行排序與其他類型是不同的：它只對每個列的最前max_sort_length 字節(jié)而不是整個字符串做排序。如果只需要排序前面一小部分字符蟀悦，則可以減小max_sort_length的配置媚朦，或者使用ORDER BY SUSTRING（column，length ） 日戈。

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

有時候可以使用枚舉列代替常用的字符串類型。枚舉列可以把一些不重復的字符串存儲成一個預定義的集合孙乖。MySQL在存儲枚舉時非常緊湊浙炼，會根據(jù)列表值的數(shù)量壓縮到一個或者兩個字節(jié)中。MySQL在內(nèi)部會將每個值在列表中的位置保存為整數(shù)唯袄，并且在表的.frm文件中保存“數(shù)字-字符串”映射關系的“查找表”弯屈。

枚舉最不好的地方是，字符串列表是固定的恋拷，添加或刪除字符串必須使用ALTER TABLE资厉。除非能接受只在列表末尾添加元素，否則使用枚舉不是個好主意蔬顾。

1.4 日期和時間類型

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

MySQL 提供兩種相似的日期類型，DATETIME 和 TIMESTAMP娩践。對于很多應用程序活翩，它們都能工作，但是在某些場景翻伺，一個比另一個工作得好材泄。

DATETIME

這個類型能保存大范圍的值，從1001年到9999年吨岭，精度為秒拉宗。它把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關未妹。使用8個字節(jié)的存儲空間簿废。 默認情況下，MySQL以一種可排序的络它、無歧義的格式顯示DATETIME值族檬，例如“2008-01-16 22:37:08”。這是ANSI標準定義的日期和時間表示方法化戳。

TIMESTAMP

就像它的名字一樣单料，TIMETAMP類型保存了從1970年1月1日午夜（格林尼治標準時間）以來的秒數(shù)，它和UNIX時間戳相同点楼。TIMESTAMP只使用4個字節(jié)的存儲空間扫尖，因此它的范圍比DATETIME小得多：只能表示從1970年到2038年。

TIMESTAMP顯示的值也依賴于時區(qū)掠廓。MySQL服務器换怖、操作系統(tǒng)，以及客戶端連接都有時區(qū)設置蟀瞧。

有必要強調(diào)一下這個區(qū)別：如果在多個時區(qū)存儲或訪問數(shù)據(jù)沉颂，TIMESTAMP 和 DATETIME的行為將很不一樣。前者提供的值與時區(qū)有關系悦污，后者則保留文本表示的日期和時間铸屉。

TIMESTAMP 的特殊屬性：

在插入數(shù)據(jù)時如果沒有指定值，會自動填充為當前時間切端。

TIMESTAMP 默認為 NOT NULL彻坛。

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

如果需要存儲比秒更小粒度的日期和時間值昌屉，可以使用BIGINT類型存儲微秒級別的時間截钙蒙，或者使用DOUBLE存儲秒之后的小數(shù)部分。這兩種方式都可以怠益，或者也可以使用MariaDB替代MySQL仪搔。

1.5 選擇標識符（identifier）

為標識列（identifier column）選擇合適的數(shù)據(jù)類型非常重要。

標識列也可能在另外的表中作為外鍵使用蜻牢，所以為標識列選擇數(shù)據(jù)類型時烤咧，應該選擇跟關聯(lián)表中的對應列一樣的類型∏来簦混用不同類型可能導致性能問題煮嫌，即使沒有性能影響，在比較操作時隱式的類型轉換也可能導致很難發(fā)現(xiàn)的錯誤抱虐。

在可以滿足值的范圍的需求昌阿，并且預留未來增長空間的前提下，應該選擇最小的數(shù)據(jù)類型恳邀。下面是一些小技巧：

• 整數(shù)類型

  整數(shù)通常是標識列最好的選擇懦冰，因為它們很快并且可以使用AUTO_INCREMENT 。

• ENUM 和 SET 類型

  對于標識列來說 ENUM 和 SET 類型通常是比較糟糕的選擇谣沸，應盡量避免用這種類型刷钢。

• 字符串類型

  字符串類型很消耗空間，且通常比數(shù)字類型慢乳附，所以也應避免使用字符串作為標識列内地。

  對于完全“隨機”的字符串也需要多加注意，例如MD5()赋除、SHA1()或者UUID()產(chǎn)生的字符串阱缓。這些函數(shù)生成的新值會任意分布在很大的空間內(nèi)，這會導致INSERT以及一些SELECT語句變得很慢举农。

  如果存儲UUID值荆针，則應該移除“-”符號；或者更好的做法是颁糟，用UNHEX()函數(shù)轉換UUID值為16字節(jié)的數(shù)字祭犯，并且存儲在一個BINARY（16）列中。檢索時可以通過HEX()函數(shù)來格式化為十六進制格式滚停。

1.6 特殊數(shù)據(jù)類型

某些類型的數(shù)據(jù)并不直接與內(nèi)置類型一致。這里有兩個例子：

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

   前面也介紹了粥惧，建議使用 BIGINT 類型存儲時間戳键畴。

2. IPv4 地址

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

二、MySQL schema設計中的陷阱

雖然有一些普遍的好或壞的設計原則嘀粱，但也有一些問題是由MySQL的實現(xiàn)機制導致的激挪，這意味著有可能犯一些只在MySQL下發(fā)生的特定錯誤。本節(jié)我們討論設計MySQL的schema的問題锋叨。這也許會幫助你避免這些錯誤垄分，并且選擇在MySQL特定實現(xiàn)下工作得更好的替代方案。

太多的列

MySQL的存儲引擎API工作時需要在服務器層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)娃磺，然后在服務器層將緩沖內(nèi)容解碼成各個列薄湿。從行緩沖中將編碼過的列轉換成行數(shù)據(jù)結構的操作代價是非常高的。MyISAM的定長行結構實際上與服務器層的行結構正好匹配偷卧，所以不需要轉換豺瘤。然而，MyISAM的變長行結構和InnoDB的行結構則總是需要轉換听诸。轉換的代價依賴于列的數(shù)量坐求。當我們研究一個CPU占用非常高的案例時，發(fā)現(xiàn)客戶使用了非常寬的表（數(shù)千個字段）蛇更，然而只有一小部分列會實際用到瞻赶，這時轉換的代價就非常高。如果計劃使用數(shù)千個字段派任，必須意識到服務器的性能運行特征會有一些不同砸逊。

太多的關聯(lián)

所謂的“實體-屬性-值”（EAV）設計模式是一個常見的糟糕設計模式，尤其是在MySQL下不能靠譜地工作掌逛。MySQL限制了每個關聯(lián)操作最多只能有61張表师逸，但是EAV數(shù)據(jù)庫需要許多自關聯(lián)。我們見過不少EAV數(shù)據(jù)庫最后超過了這個限制豆混。事實上在許多關聯(lián)少于61張表的情況下篓像，解析和優(yōu)化查詢的代價也會成為MySQL的問題。一個粗略的經(jīng)驗法則皿伺，如果希望查詢執(zhí)行得快速且并發(fā)性好员辩，單個查詢最好在12個表以內(nèi)做關聯(lián)。

全能的枚舉

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

CREATE TABLE ... (       
    country enum('','0','1','2',...,'31')

這種模式的schema設計非常凌亂奠滑。這么使用枚舉值類型也許在任何支持枚舉類型的數(shù)據(jù)庫都是一個有問題的設計方案丹皱，這里應該用整數(shù)作為外鍵關聯(lián)到字典表或者查找表來查找具體值。但是在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' 

如果這里真和假兩種情況不會同時出現(xiàn)，那么毫無疑問應該使用枚舉列代替集合列瓶珊。

非此發(fā)明（Not Invent Here）的NULL

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

但是遵循這個原則也不要走極端。當確實需要表示未知值時也不要害怕使用NULL南缓。在一些場景中胸遇，使用NULL可能會比某個神奇常數(shù)更好。從特定類型的值域中選擇一個不可能的值汉形，例如用?1代表一個未知的整數(shù)纸镊，可能導致代碼復雜很多，并容易引入bug概疆，還可能會讓事情變得一團糟逗威。處理NULL確實不容易，但有時候會比它的替代方案更好岔冀。

三凯旭、范式和反范式

對于任何給定的數(shù)據(jù)通常都有很多種表示方法，從完全的范式化到完全的反范式化使套，以及兩者的折中罐呼。在范式化的數(shù)據(jù)庫中，每個事實數(shù)據(jù)會出現(xiàn)并且只出現(xiàn)一次侦高。相反嫉柴，在反范式化的數(shù)據(jù)庫中，信息是冗余的奉呛，可能會存儲在多個地方差凹。

3.1 范式的優(yōu)點和缺點

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

• 范式化的更新操作通常比反范式化要快谊娇。

• 當數(shù)據(jù)較好地范式化時，就只有很少或者沒有重復數(shù)據(jù)罗晕，所以只需要修改更少的數(shù)據(jù)济欢。

• 范式化的表通常更小，可以更好地放在內(nèi)存里小渊，所以執(zhí)行操作會更快法褥。

• 很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時更少需要DISTINCT或者GROUP BY語句。

范式化設計的schema的缺點是通常需要關聯(lián)酬屉。稍微復雜一些的查詢語句在符合范式的schema上都可能需要至少一次關聯(lián)半等，也許更多。這不但代價昂貴呐萨，也可能使一些索引策略無效杀饵。例如，范式化可能將列存放在不同的表中谬擦，而這些列如果在一個表中本可以屬于同一個索引切距。

3.2 反范式的優(yōu)點和缺點

反范式化的schema因為所有數(shù)據(jù)都在一張表中，可以很好地避免關聯(lián)惨远。 如果不需要關聯(lián)表谜悟，則對大部分查詢最差的情況——即使表沒有使用索引——是全表掃描。當數(shù)據(jù)比內(nèi)存大時這可能比關聯(lián)要快得多北秽，因為這樣避免了隨機 I/O 葡幸。

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

3.3 混用范式化和反范式化

范式化和反范式化的schema各有優(yōu)劣羡儿，怎么選擇最佳的設計礼患？

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

最常見的反范式化數(shù)據(jù)的方法是復制或者緩存，在不同的表中存儲相同的特定列厨相。在MySQL 5.0和更新版本中领曼，可以使用觸發(fā)器更新緩存值鸥鹉，這使得實現(xiàn)這樣的方案變得更簡單。

好啦庶骄，本章的內(nèi)容就到這里啦毁渗，我們下期見~

全棧全平臺開源項目 CodeRiver

CodeRiver 是一個免費的項目協(xié)作平臺，愿景是打通 IT 產(chǎn)業(yè)上下游单刁，無論你是產(chǎn)品經(jīng)理灸异、設計師、程序員或是測試羔飞，還是其他行業(yè)人員肺樟，只要有好的創(chuàng)意、想法逻淌，都可以來 CodeRiver 免費發(fā)布項目么伯，召集志同道合的隊友一起將夢想變?yōu)楝F(xiàn)實！

CodeRiver 本身還是一個大型開源項目卡儒，致力于打造全棧全平臺企業(yè)級精品開源項目田柔。涵蓋了 React、Vue朋贬、Angular凯楔、小程序、ReactNative锦募、Android摆屯、Flutter、Java糠亩、Node 等幾乎所有主流技術棧虐骑，主打代碼質量。

目前已經(jīng)有近 100 名優(yōu)秀開發(fā)者參與赎线，github 上的 star 數(shù)量將近 1000 個廷没。每個技術棧都有多位經(jīng)驗豐富的大佬坐鎮(zhèn)，更有兩位架構師指導項目架構垂寥。無論你想學什么語言處于什么技術水平颠黎，相信都能在這里學有所獲。

通過 高質量源碼 + 博客 + 視頻滞项，幫助每一位開發(fā)者快速成長狭归。

項目地址：https://github.com/cachecats/coderiver

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