MySQL 高性能表設(shè)計規(guī)范

相關(guān)文章：

MySQL高性能表設(shè)計規(guī)范:http://www.reibang.com/p/f797bbe11d76
MySQL EXPLAIN詳解:http://www.reibang.com/p/ea3fc71fdc45
MySQL 鎖機制 常用知識點:http://www.reibang.com/p/0d5b7cd592f9

image.png

良好的邏輯設(shè)計和物理設(shè)計是高性能的基石寞肖，應該根據(jù)系統(tǒng)將要執(zhí)行的查詢語句來設(shè)計schema, 這往往需要權(quán)衡各種因素抚芦。

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

MySQL支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要蝌借。

更小的通常更好

更小的數(shù)據(jù)類型通常更快卿啡，因為它們占用更少的磁盤吟吝、內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更少颈娜。

簡單就好

簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期剑逃。例如，整型比字符操作代價更低官辽，因為字符集和校對規(guī)則（排序規(guī)則）使字符比較比整型比較更復雜蛹磺。

盡量避免NULL

如果查詢中包含可為NULL 的列，對MySQL來說更難優(yōu)化同仆，因為可為NULL 的列使得索引萤捆、索引統(tǒng)計和值比較都更復雜。可為NULL的列會使用更多的存儲空間乓梨，在MySQL里也需要特殊處理鳖轰。當可為NULL的列被索引時清酥，每個索引記錄需要一個額外的字節(jié)扶镀，在MyISAM里甚至還可能導致固定大小的索引（例如只有一個整數(shù)列的索引）變成可變大小的索引。
當然也有例外焰轻，例如InnoDB 使用單獨的位 (bit) 存儲NULL值臭觉，所以對于稀疏數(shù)據(jù)有很好的空間效率。

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 是怎么在內(nèi)存和磁盤中保存數(shù)據(jù)的。然而阔蛉，整數(shù)計算一般使用64 位的 BIGINT 整數(shù)弃舒，即使在 32 位環(huán)境也是如此。（一些聚合函數(shù)是例外状原，它們使用DECIMAL 或 DOUBLE 進行計算）聋呢。

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

2.實數(shù)類型

實數(shù)是帶有小數(shù)部分的數(shù)字喂窟。然而测暗，它們不只是為了存儲小數(shù)部分，也可以使用DECIMAL 存儲比 BIGINT 還大的整數(shù)磨澡。

FLOAT和DOUBLE類型支持使用標準的浮點運算進行近似計算碗啄。
DECIMAL類型用于存儲精確的小數(shù)。
浮點和DECIMAL類型都可以指定精度稳摄。對于DECIMAL列稚字，可以指定小數(shù)點前后所允許的最大位數(shù)。這會影響列的空間消耗厦酬。

有多種方法可以指定浮點列所需要的精度胆描，這會使得MySQL選擇不同的數(shù)據(jù)類型，或者在存儲時對值進行取舍仗阅。這些精度定義是非標準的昌讲，所以我們建議只指定數(shù)據(jù)類型，不指定精度减噪。

浮點類型在存儲同樣范圍的值時短绸，通常比DECIMAL使用更少的空間。FLOAT使用4個字節(jié)存儲筹裕。DOUBLE占用8個字節(jié)醋闭，相比FLOAT有更高的精度和更大的范圍。和整數(shù)類型一樣朝卒，能選擇的只是存儲類型证逻； MySQL使用DOUBLE作為內(nèi)部浮點計算的類型。

因為需要額外的空間和計算開銷抗斤，所以應該盡量只在對小數(shù)進行精確計算時才使用DECIMAL囚企。但在數(shù)據(jù)最比較大的時候，可以考慮使用BIGINT代替DECIMAL, 將需要存儲的貨幣單位根據(jù)小數(shù)的位數(shù)乘以相應的倍數(shù)即可豪治。

3.字符串類型

VARCHAR

用于存儲可變?字符串洞拨，長度支持到65535
需要使用1或2個額外字節(jié)記錄字符串的長度
適合：字符串的最大?度比平均?度?很多；更新很少

CHAR

定?负拟，?度范圍是1~255
適合：存儲很短的字符串烦衣，或者所有值接近同一個長度；經(jīng)常變更

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)存儲'hello'的空間開銷是一樣的。那么使用更短的列有什么優(yōu)勢嗎花吟？

事實證明有很大的優(yōu)勢秸歧。更長的列會消耗更多的內(nèi)存，因為MySQL通常會分配固定大小的內(nèi)存塊來保存內(nèi)部值衅澈。尤其是使用內(nèi)存臨時表進行排序或操作時會特別糟糕键菱。在利用磁盤臨時表進行排序時也同樣糟糕。

所以最好的策略是只分配真正需要的空間今布。

4.BLOB和TEXT類型

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

與其他類型不同部默， MySQL把每個BLOB和TEXT值當作一個獨立的對象處理侵蒙。存儲引擎在存儲時通常會做特殊處理。當BLOB和TEXT值太大時傅蹂，InnoDB會使用專門的 “外部“存儲區(qū)域來進行存儲纷闺，此時每個值在行內(nèi)需要1 - 4個字節(jié)存儲存儲區(qū)域存儲實際的值。

BLOB 和 TEXT 之間僅有的不同是 BLOB 類型存儲的是二進制數(shù)據(jù)份蝴，沒有排序規(guī)則或字符集犁功，而 TEXT類型有字符集和排序規(guī)則

5.日期和時間類型

大部分時間類型都沒有替代品，因此沒有什么是最佳選擇的問題婚夫。唯一的問題是保存日期和時間的時候需要做什么浸卦。 MySQL提供兩種相似的日期類型： DATE TIME和 TIMESTAMP。

但是目前我們更建議存儲時間戳的方式请敦，因此該處不再對 DATE TIME和 TIMESTAMP做過多說明镐躲。

5.其他類型

5.1選擇標識符

在可以滿足值的范圍的需求，井且預留未來增長空間的前提下侍筛，應該選擇最小的數(shù)據(jù)類型。

整數(shù)類型

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

ENUM和SET類型

對于標識列來說，EMUM和SET類型通常是一個糟糕的選擇端礼，盡管對某些只包含固定狀態(tài)或者類型的靜態(tài) ”定義表” 來說可能是沒有問題的禽笑。ENUM和SET列適合存儲固定信息，例如有序的狀態(tài)蛤奥、產(chǎn)品類型佳镜、人的性別。

字符串類型

如果可能凡桥，應該避免使用字符串類型作為標識列蟀伸，因為它們很消耗空間，并且通常比數(shù)字類型慢。

對于完全 “隨機” 的字符串也需要多加注意啊掏，例如 MDS() 蠢络、 SHAl() 或者 UUID() 產(chǎn)生的字符串。這些函數(shù)生成的新值會任意分布在很大的空間內(nèi)迟蜜，這會導致 INSERT 以及一些SELECT語句變得很慢刹孔。如果存儲 UUID 值，則應該移除 "-"符號娜睛。

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

某些類型的數(shù)據(jù)井不直接與內(nèi)置類型一致髓霞。低千秒級精度的時間戳就是一個例子，另一個例子是以個1Pv4地址畦戒，人們經(jīng)常使用VARCHAR(15)列來存儲IP地址酸茴，然而，它們實際上是32位無符號整數(shù)兢交，不是字符串薪捍。用小數(shù)點將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應該用無符號整數(shù)存儲IP地址配喳。MySQL提供INET_ATON()和INET_NTOA()函數(shù)在這兩種表示方法之間轉(zhuǎn)換酪穿。

二、表結(jié)構(gòu)設(shè)計

1.范式和反范式

對于任何給定的數(shù)據(jù)通常都有很多種表示方法晴裹，從完全的范式化到完全的反范式化被济，以及兩者的折中。在范式化的數(shù)據(jù)庫中涧团，每個事實數(shù)據(jù)會出現(xiàn)并且只出現(xiàn)一次只磷。相反，在反范式化的數(shù)據(jù)庫中泌绣，信息是冗余的钮追，可能會存儲在多個地方。

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

為性能提升考慮時阿迈，經(jīng)常會被建議對 schema 進行范式化設(shè)計元媚，尤其是寫密集的場景。

范式化的更新操作通常比反范式化要快苗沧。
當數(shù)據(jù)較好地范式化時刊棕，就只有很少或者沒有重復數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)待逞。
范式化的表通常更小甥角，可以更好地放在內(nèi)存里，所以執(zhí)行操作會更快识樱。
很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時更少需要 DISTINCT 或者 GROUP BY語句嗤无。

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

不需要關(guān)聯(lián)表震束，則對大部分查詢最差的情況——即使表沒有使用索引——是全表掃描。當數(shù)據(jù)比內(nèi)存大時這可能比關(guān)聯(lián)要快得多翁巍，因為這樣避免了隨機I/0驴一。

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

混用范式化和反范式化

在實際應用中經(jīng)常需要混用灶壶，可能使用部分范式化的 schema 肝断、緩存表，以及其他技巧驰凛。
表適當增加冗余字段胸懈，如性能優(yōu)先，但會增加復雜度恰响∪で可避免表關(guān)聯(lián)查詢。

簡單熟悉數(shù)據(jù)庫范式

第一范式(1NF)：字段值具有原子性,不能再分(所有關(guān)系型數(shù)據(jù)庫系統(tǒng)都滿足第一范式);
            例如：姓名字段,其中姓和名是一個整體,如果區(qū)分姓和名那么必須設(shè)立兩個獨立字段;
         
第二范式(2NF)：一個表必須有主鍵,即每行數(shù)據(jù)都能被唯一的區(qū)分;
            備注：必須先滿足第一范式;
         
第三范式(3NF)：一個表中不能包涵其他相關(guān)表中非關(guān)鍵字段的信息,即數(shù)據(jù)表不能有沉余字段;
            備注：必須先滿足第二范式;

2.表字段少?精

I/O高效
字段分開維護簡單
單表1G體積 500W?行評估
單?行不超過200Byte
單表不超過50個INT字段
單表不超過20個CHAR(10)字段
建議單表字段數(shù)控制在20個以內(nèi)
拆分TEXT/BLOB胚宦，TEXT類型處理性能遠低于VARCHAR首有，強制生成硬盤臨時表浪費更多空間。

參考資料：

高性能mysql第三版

個人介紹：

高廣超 ：多年一線互聯(lián)網(wǎng)研發(fā)與架構(gòu)設(shè)計經(jīng)驗枢劝，擅長設(shè)計與落地高可用井联、高性能互聯(lián)網(wǎng)架構(gòu)。

本文首發(fā)在高廣超的簡書博客轉(zhuǎn)載請注明您旁！