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

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

MySQL支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要。不管 存儲哪種類型的數(shù)據(jù)，下面幾個簡單的原則都有助于做出更好的選擇。

• 更小的通常更好

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

• 簡單就好

  簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。

• 盡量避免NULL

  如果査詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因為可為NULL的列使 得索引、索引統(tǒng)計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在 MySQL里也需要特殊處理。

在為列選擇數(shù)據(jù)類型時：

第一步需要確定合適的大類型：數(shù)字、字符串、時間等；

第二步是選擇具體類型。很多MySQL的數(shù)據(jù)類型可以存儲相同類型的數(shù)據(jù)，只是存儲 的長度和范圍不一樣、允許的精度不同，或者需要的物理空間不同（相同大類型的不同子類型數(shù)據(jù)有時也有一些特殊的行為和屬性）。

1.1、整數(shù)類型

有兩種類型的數(shù)字：整數(shù)和實數(shù)。

整數(shù)類型：

• TINYINT
  1字節(jié)、【-128，127】、【0，255】
• SMALLINT
  2字節(jié)、【-32768，32767】、【0，65535】
• MEDIUMINT
  3字節(jié)、【-2147483648，2147483647】、【0，4294967295】
• INT
  4字節(jié)、【-2147483648，2147483647】、【0，4294967295】
• BIGINT
  8字節(jié)、【-263，263-1】、【0，264-1】

整數(shù)類型有可選的UNSIGNED屬性，表示不允許為負數(shù)，大致可以使得正數(shù)的上限提高一倍。

有符號和無符號具有相同的存儲空間和性能，根據(jù)實際情況選擇合適的類型。

Tips:整數(shù)計算一般使用 64位的BIGINT整數(shù)，即使在32位環(huán)境也是如此，不同的數(shù)據(jù)可以決定的只有MySQL是怎么在內(nèi)存和磁盤中保存數(shù)據(jù)的。

例如INT(11),對大多數(shù)應(yīng)用這是沒有意義的：它不會限制值的合法范圍，只是規(guī)定了MySQL的一些交互工具(例如MySQL命令行客戶端) 用來顯示字符的個數(shù)。對于存儲和計算來說，INT(1)和INT(20)是相同的

2.2、實數(shù)類型

實數(shù)是帶有小數(shù)部分的數(shù)字。

• FLOAT
  單精度，4字節(jié)
• DOUBLE
  雙精度，8字節(jié)
• DECIMAL
  存儲精確的小數(shù)

FLOAT和DOUBLE使用標準的浮點運算進行近似運算，如果需要知道浮點運算是 怎么計算的，則需要研究所使用的平臺的浮點數(shù)的具體實現(xiàn)。

DECIMAL支持精確計算，但CPU不支持對DECIMAL的直接計算，MySQL自己實現(xiàn)了DECIMAL的高精度計算，所以DECIMAL在性能上要弱一些。

Tips:DECIMAL需要額外的空間和計算消耗，當數(shù)據(jù)量比較大時，可以考慮使用BITINT來代替，將存儲的數(shù)據(jù)根據(jù)小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)即可。這樣可以解決浮點類型計算不準確，DECIMAL計算開銷太大的問題。

2.3、字符串類型

VARCHAR

VARCHAR用于存儲可變長的字符，比定長更節(jié)省空間，越短的字符占用空間越少。
但有一種例外：當表使用“ROW_FORMAT=FIXED”創(chuàng)建時，每一行都會使用定長存儲，浪費空間。

VARCHAR需要使用1或2個額外字節(jié)記錄字符串的長度：如果列的最大長度小于或 等于255字節(jié)，則只使用1個字節(jié)表示，否則使用2個字節(jié)。假設(shè)采用latinl字符集， 一個VARCHAR(IO)的列需要11個字節(jié)的存儲空間。VARCHAR(1000)的列則需要1002 個字節(jié)，因為需要2個字節(jié)存儲長度信息

VARCHAR節(jié)省了存儲空間，對性能也有幫助。但是由于行是變長的，所以UPDATE時可能比原來更長，這就需要MySQL為其額外再分配存儲空間，導致UPDATE時開銷比定長類型要大。

CHAR

CHAR類型是定長的，MySQL會根據(jù)定義的長度去分配存儲空間，所以不會有VARCHAR進行UPDATE時的額外開銷。
CHAR類型存儲時，會自動去除末尾的空格，這一點需要注意。

CHAR適合存儲短的，長度固定的字符，例如MD5值，UUID等…

由于UPDATE時沒有額外的開銷，對于經(jīng)常變更的數(shù)據(jù)，CHAR的性能也比VARCHAR更好。

BLOB和TEXT類型

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

它們分別屬于不同的數(shù)據(jù)類型家族：

字符類型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
二進制類型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

與其他類型不同，MySQL會將BLOB和TEXT當做單獨的對象處理。

當值太大時，MySQL會使用專門的存儲區(qū)域來存儲數(shù)據(jù)，行內(nèi)使用1~4字節(jié)來存儲一個指針，指向?qū)?yīng)的大文本字符。

BLOB和TEXT的不同之處在于：由于BLOB是二進制，所以沒有字符集和排序規(guī)則，但是TEXT有。

即使TEXT有排序規(guī)則，MySQL對其進行排序時，也不會對整個文本進行排序，只會對前max_sort_length字節(jié)進行排序，可以通過修改max_sort_length進行配置。

MySQL不能將BLOB和TEXT全部長度的字符進行索引。

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

枚舉可以把一些不重復的字符串存儲成一個預定義的集合，MySQL在存儲枚舉時非常緊湊，會根據(jù)列表值壓縮到1到2個字節(jié)中。

MySQL在內(nèi)部會將列中的枚舉值保存為整數(shù)，在.frm文件中保存一個“數(shù)字->字符串”的映射關(guān)系，通過數(shù)字快速的查找到具體的枚舉值。

枚舉字段排序時，并不會按照給定的字符串排序，而是根據(jù)內(nèi)部的整數(shù)排序，所以建議列舉枚舉時按照預想的順序給出。

日期和時間類型

MySQL提供了多種類型來保存時間和日期，例如：YEAR、DATE、DATETIME。
MySQL能存儲的最小時間粒度為秒（有的第三方存儲引擎支持微秒）。

MySQL提供了兩種相似的事件類型：DATETIME和TIMESTAMP。

DATETIME

用來保存大范圍的時間，從1001年到9999年，精度為秒。
它把時間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關(guān)，使用8個字節(jié)來存儲。

TIMESTAMP

保存了從1970年1月1日凌晨以來的秒數(shù)，和UNIX時間戳相同。
使用4個字節(jié)來保存，比DATETIME節(jié)省空間，具有更高的性能。
但是范圍比DATETIME要小得多，只能存儲1970年到2038年。

TIMESTAMP顯示的值依賴于時區(qū)，MySQL服務(wù)器，操作系統(tǒng)以及客戶端的連接都有時區(qū)的設(shè)置。

除了特殊行為之外，應(yīng)該盡量使用TIMESTAMP，它比DATETIME空間效率要高。

如果需要存儲比秒更小粒度的時間，MySQL目前沒有提供合適的數(shù)據(jù)類型，可以考慮使用BIGINT來存儲微秒級別的時間戳。

2.4、位數(shù)據(jù)類型

可以使用BIT列存儲一個或多個true/false值，BIT(1)包含單個位的字段，最多可包含64個位。

MySQL將BIT當做字符串類型，而不是數(shù)字類型。
當查詢BIT(1)時，結(jié)果是一個包含二進制0或1的字符串，而不是ASCII碼中的“0”或“1”。

BIT列進行比較時，MySQL會將位字符串轉(zhuǎn)換為十進制數(shù)字進行比較。
例如：‘111’ = 7。

對于大部分應(yīng)用，最好慎用BIT類型。

2.5、選擇標識符

為標識列選擇合適的數(shù)據(jù)類型十分重要。

一般來說標識列很可能用來在不同的表之間進行比較，甚至作為外鍵來使用。
合適的數(shù)據(jù)類型可以提升系統(tǒng)的整體性能，減少數(shù)據(jù)比較的系統(tǒng)開銷。

一旦選定了類型，一定要確保關(guān)聯(lián)表中也是相同的數(shù)據(jù)類型，混用不同的數(shù)據(jù)類型會帶來很多麻煩。
例如：將字符串與整形做比較，會導致嚴重的性能問題。

一般來說，在沒有特殊要求的情況下，整型 通常是標識列最好的選擇，因為它很快，而且可以自動遞增。

如果可以的話，應(yīng)該盡量避免使用字符串當做標識列，它很消耗空間，而且比整型慢。

很多人喜歡用隨機的字符串來作為標識列，例如：UUID。
由于生成的字符沒有規(guī)律，會導致INSERT和SELECT語句變得很慢：

• 插入的值會隨機的寫入到索引的不同位置，使得INSERT更慢。這會導致頁分裂，磁盤隨機訪問，以及對于聚簇存儲引擎產(chǎn)生聚簇索引碎片。
• SELECT語句變慢，因為邏輯上相鄰的數(shù)據(jù)會分布在磁盤的不同地方。
• 隨機值導致緩存對所有類型的查詢語句效果都很差，因為會使得緩存賴以工作的訪問“局部性原理”失效。緩存無法命中，加載到內(nèi)存中也是徒勞。

Tips：如果需要使用UUID當做標識列，那么應(yīng)該移除“-”這種沒有意義的字符。
最好的解決方案是：用UNHEX()將UUID轉(zhuǎn)換為16位的二進制數(shù)據(jù)，沒有字符集，沒有排序，而且占用更少的磁盤空間，可以很好的提升性能。

2.6、特殊類型數(shù)據(jù)

有些類型的數(shù)據(jù)并不直接與MySQL的內(nèi)置類型一致，微秒型的時間戳就是個例子。

還有例如：IPv4地址，應(yīng)該使用無符號的整數(shù)來保存，而非字符串。
MySQL內(nèi)置的函數(shù)INET_ATON和INET_NTOA可以很好的轉(zhuǎn)換。

二、Schema設(shè)計中的陷阱

雖然有一些普遍的好或壞的設(shè)計原則，但也有一些問題是由MySQL的實現(xiàn)機制導致的， 這意味著有可能犯一些只在MySQL下發(fā)生的特定錯誤。

了解討論設(shè)計MySQL的 schema的問題。這也許會幫助我們避免這些錯誤，并且選擇在MySQL特定實現(xiàn)下工作得更好的替代方案。

• 太多的列
  MySQL存儲引擎工作時，需要在服務(wù)器層和存儲引擎層之間做行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層之間將緩沖內(nèi)容解碼成各個列。從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的操作代價是非常高的。
  如果單張表的列太多，就應(yīng)該要考慮做表的拆分。

• 太多的關(guān)聯(lián)
  MySQL限制了每個關(guān)聯(lián)最多只能61張表，一個粗略的經(jīng)驗法則：如果希望査詢執(zhí)行得快速且并發(fā)性好,單個查詢最好在12張表以內(nèi)做關(guān)聯(lián)。

• 全能的枚舉

  注意防止過度使用枚舉(ENUM)，在MySQL 5.0以及更早的版本中ALTER TABLE是一 種阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也會一樣需 要ALTER TABLE 。

• Not Invent Here 的 NULL

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

  但是遵循這個原則也不要走極端。當確實需要表示未知值時也不要害怕使用NULL在一些場景中，使用NULL可能會比某個神奇常數(shù)更好。

三、范式和反范式

對于任何給定的數(shù)據(jù)通常都有很多種表示方法，從完全的范式化到完全的反范式化，以及兩者的折中。

設(shè)計關(guān)系型數(shù)據(jù)庫時，需要遵從不同的規(guī)范，設(shè)計合理的關(guān)系型數(shù)據(jù)庫，不同的規(guī)范被稱為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫冗余約小。在關(guān)系型數(shù)據(jù)庫中有六中范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），BCNF，第四范式（4NF），第五范式(5NF)。一般數(shù)據(jù)庫設(shè)計到第三范式就行了

這里簡單介紹一下三大范式：

• 第一范式
  確保數(shù)據(jù)表中每列（字段）的原子性。
  如果數(shù)據(jù)表中每個字段都是不可再分的最小數(shù)據(jù)單元，則滿足第一范式。
• 第二范式
  在第一范式的基礎(chǔ)上更進一步，目標是確保表中的每列都和主鍵相關(guān)。
  如果一個關(guān)系滿足第一范式，并且除了主鍵之外的其他列，都依賴于該主鍵，則滿足第二范式。
• 第三范式
  在第二范式的基礎(chǔ)上更進一步，目標是確保表中的列都和主鍵直接相關(guān)，而不是間接相關(guān)。通過第三張表（中間表）來建立用戶表和角色表之間的關(guān)系，同時又符合范式化的原則，就可以稱為第三范式。
• 反范式化
  反范式化指的是通過增加冗余或重復的數(shù)據(jù)來提高數(shù)據(jù)庫的讀性能。

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

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

• 范式化更新操作通常比反范式化要快。
• 當數(shù)據(jù)較好的范式化時，就只有很少或者沒有重復數(shù)據(jù)，所以，只需要修改更少的數(shù)據(jù)。
• 范式化的表通常更小，可以更好地放在內(nèi)存里，所以執(zhí)行操作會更快。
• 很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)更少需要distinct或者group by 語句。

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

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

反范式的優(yōu)點：

• 可以很好地避免關(guān)聯(lián)。
• 如果不需要關(guān)聯(lián)表，對大部分查詢最差情況，即沒有使用索引，全表掃描。當數(shù)據(jù)幣內(nèi)存大時這可能比關(guān)聯(lián)要快很多， 這樣避免了隨機I/O。

在真實環(huán)境中很少會極端地使用范式化或者反范式化的schema。而是可能使用部分范式化的schema、緩存表、以及其它技巧。最常見的反范式化數(shù)據(jù)的方法是復制或者緩存，在不同的表中存儲相同的特定的列。

3.3、混用范式化和反范式化

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

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

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

四、加快ALTER TABLE操作的速度

MySQL 對于大表的ALTER操作是非常慢的，因為 MySQL 對于ALTER操作的的方法是創(chuàng)建一個新結(jié)構(gòu)的表，然后將舊結(jié)構(gòu)表中的數(shù)據(jù)復制過去，最后將舊表刪除。如此操作對于海量數(shù)據(jù)的表來說花費的時間是非常長的。

一般而言，大部分ALTER TABLE操作將導致MySQL服務(wù)中斷。我們會展示一些在DDL 操作時有用的技巧，但這是針對一些特殊的場景而言的。對常見的場景，能使用的技巧 只有兩種：

一種是先在一臺不提供服務(wù)的機器上執(zhí)行ALTER TABLE操作，然后和提供服 務(wù)的主庫進行切換；

另外一種技巧是影子拷貝，影子拷貝的技巧是用要求的表結(jié)構(gòu)創(chuàng)建一張和源表無關(guān)的新表，然后通過重命名和刪表操作交換兩張表。

4.1、只修改.frm文件

如果愿意冒一些風險，可以讓MySQL做一些其他類型的修改而不用重 建表。

下面這些操作是有可能不需要重建表的：

• 移除（不是增加）一個列的AUTO_INCREMENT屬性。
• 增加、移除，或更改ENUM和SET常量。如果移除的是已經(jīng)有行數(shù)據(jù)用到其值的常量， 查詢將會返回一個空字串值。

基本的技術(shù)是為想要的表結(jié)構(gòu)創(chuàng)建一個新的**.frm文件，然后用它替換掉已經(jīng)存在的那張 表的.frm**文件，像下面這樣：

創(chuàng)建一張有相同結(jié)構(gòu)的空表，并進行所需要的修改（例如增加ENUM常量）。

執(zhí)行FLUSH TABLES WITH READ L0CKo這將會關(guān)閉所有正在使用的表，并且禁止任何表被打開。

交換**.frm**文件.

執(zhí)行UNLOCK TABLES來釋放第2步的讀鎖。

4.2、快速創(chuàng)建MylSAM索引

為了高效地載入數(shù)據(jù)到MylSAM表中，有一個常用的技巧是先禁用索引、載入數(shù)據(jù)，然后重新啟用索引，這個技巧能夠發(fā)揮作用，是因為構(gòu)建索引的工作被延遲到數(shù)據(jù)完全載入以后，這個時候 已經(jīng)可以通過排序來構(gòu)建索引了。這樣做會快很多，并且使得索引樹注”的碎片更少、更緊湊。

不幸的是，這個辦法對唯一索引無效，因為DISABLE KEYS只對非唯一索引有效。 MylSAM會在內(nèi)存中構(gòu)造唯一索引，并且為載入的每一行檢査唯一性。一旦索引的大小<33 超過了有效內(nèi)存大小，載入操作就會變得越來越慢。

下面是操作步驟：

用需要的表結(jié)構(gòu)創(chuàng)建一張表，但是不包括索引。

注：如果使用的是LOAD DATA FILE,并且要載入的表是空的，MylSAM也可以通過排序來構(gòu)造索引。

載入數(shù)據(jù)到表中以構(gòu)建.M陽 文件。

按照需要的結(jié)構(gòu)創(chuàng)建另外一張空表，這次要包含索引。這會創(chuàng)建需要的斤％和.心以 文件。

獲取讀鎖并刷新表。

重命名第二張表的為“和文件，讓MySQL認為是第一張表的文件。

釋放讀鎖。

使用REPAIR TABLE來重建表的索引。該操作會通過排序來構(gòu)建所有索引，包括唯一 索引。

五、總結(jié)

良好的schema設(shè)計原則是普遍適用的，但MySQL有它自己的實現(xiàn)細節(jié)要注意。概括來 說，盡可能保持任何東西小而簡單總是好的。MySQL喜歡簡單，需要使用數(shù)據(jù)庫的人 應(yīng)該也同樣會喜歡簡單的原則：

• 盡量避免過度設(shè)計，例如會導致極其復雜査詢的schema設(shè)計，或者有很多列的表設(shè) 計（很多的意思是介于有點多和非常多之間）。
• 使用小而簡單的合適數(shù)據(jù)類型，除非真實數(shù)據(jù)模型中有確切的需要，否則應(yīng)該盡可 能地避免使用NULL值。
• 盡量使用相同的數(shù)據(jù)類型存儲相似或相關(guān)的值，尤其是要在關(guān)聯(lián)條件中使用的列。
• 注意可變長字符串，其在臨時表和排序時可能導致悲觀的按最大長度分配內(nèi)存。
• 盡量使用整型定義標識列。
• 避免使用MySQL已經(jīng)遺棄的特性，例如指定浮點數(shù)的精度，或者整數(shù)的顯示寬度。
• 小心使用ENUM和SET。雖然它們用起來很方便，但是不要濫用，否則有時候會變成 陷阱。最好避免使用BITO

范式是好的，但是反范式（大多數(shù)情況下意味著重復數(shù)據(jù)）有時也是必需的，并且能帶 來好處。

參考：

《高性能 MySQL 第三版》

Schema與數(shù)據(jù)類型優(yōu)化

MySQL三大范式和反范式

[MySQL中范式與反范式的優(yōu)缺點](

總結(jié)

以上是生活随笔為你收集整理的高性能MySQL(2)——Schema与数据类型的优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。