MySQL如何優化Schema與數據類型性能

本文主要給大家介紹MySQL如何優化Schema與數據類型性能，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL如何優化Schema與數據類型性能吧。

良好的邏輯設計和物理設計是高性能的基石，應該根據系統將要執行的查詢語句來設計schema。

反范式的設計可以加快某些類型的查詢，單同時可能使另一類型的查詢變慢，比如添加計數表和匯總表是一種很好的優化查詢的方式，但這些表的維護成本可能會很高。

1.選擇優化的數據類型

更小的通常更好。

應該盡量使用可以正確存儲數據的最小類型，更小的數據類型通常更快，因為他們占用更少的磁盤，內存和CPU緩存，并且處理時需要的CPU周期更少。

簡單就好

更簡單的數據類型的操作通常需要更少的CPU周期。例如，整型數字比字符操作代價更低，因為字符集和校對規則（排序規則）使字符比較相對整型數字比較更復雜。比如，應使用INTERGER存儲IP地址（inet_aton）

盡量避免NULL

通常情況下，最好指定列為NOT NULL。如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引，索引統計和值比較非常復雜，可為NULL的列會使用更多的存儲空間，當可謂NULL的列被索引時，每個索引記錄需要一個額外的字節。但是把可為NULL的列改成NOT NULL帶來的性能提升比較小，但如果計劃在列上創建索引，就應該避免設計成可為NULL的列。

1.1整數類型

整型類型有可選的UNSIGNED屬性，表示不允許負值，可以使原本正數的上線提高一倍。有符號和無符號類型使用相同的存儲空間，并具有相同的性能。整型之間相互計算，是以64位的BIGINT作為中間類型進行計算的。

1.2實數類型

實數是帶有小數部分的數字，可以使用DECIMAL存儲比BIGINT還大的整數。

DECIMAL類型用于存儲精確的小數，支持精確計算。例如，DECIMAL(18，9)小數點兩邊將各存儲9個數字，一共使用9個字節，其中小數點前面的數字使用。DECIMAL最多允許65個數字。

浮點類型在存儲同樣范圍的值時，通常比DECIMAL占用更少的空間，內部計算時采用DOUBLE作為計算類型。

因為需要額外的空間和計算開銷，盡量只在對小鼠進行精確計算時才使用DECIMAL，在數據量比較大的時候，可以考慮使用BIGINT代替DECIMAL，講需要存儲的貨幣單位根據小數的位數乘以相應的倍數即可。

1.3字符串類型

varchar

varchar類型用于存儲可變長字符串，比定長更節省空間，varchar需要使用1個或2個額外字節記錄字符串的長度，如果列的最大長度小于或等于255個字節，則只是用1個字節表示，否則使用2個字節。varchar節省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，如果在UPDATE時增加了該邊長列的實際存儲長度，這就導致需要額外的工作，如果一個行占用的空間增長，并且在頁內沒有更多的存儲空間可以存儲，在這種情況下，InnoDB需要分裂頁來使行可以放進頁內。

varchar使用場合：1.字符串列的最大長度比平均長度大很多，列的更新很少

2.使用了UTF-8這種復合的字符集（每個字符都使用不同的字節數存儲）

MySQL在存儲和檢索時會保留varchar尾部的空格。InnoDB可以把過長的VARCHAR存儲為BLOB。

char

定長字符串，MySQL在存儲時會去除char尾部的空格。會造成“A  ”與“A”產生唯一性沖突。數據如何存儲取決于存儲引擎，填充和截取空格的行為是在MySQL服務層進行的。

更長的列會消耗更高的內存，MySQL通常會分配固定大小的內存來保存內部值，尤其是使用內存臨時表進行排序或操作總是會特別糟糕。

blob

采用二進制的方式存儲，沒有排序規則和字符集。包含tinyblob,blob,mediumblob,longblob

text

采用字符串的方式存儲，有排序規則和字符集，包含tinytext,text,mediumtext,longtext。

與其他類型不同，MySQL把每個BLOB值和TEXT值當作一個獨立的對象處理，存儲引擎在存儲時通常會做特殊處理，當BLOB和TEXT值太大時，InnoDB會使用專門的“外部”存儲區域來存儲，在原本的行中使用指針指向外部的存儲區域。同事這兩種數據格式最多只能建立前綴索引。

ENUM

枚舉不推薦使用（想了解可以參考原書）

1.4日期和時間類型

DATETIME和TIMESTAMP

現在推薦使用DATETIME，范圍更大，與時區無關，占用8個字節

1.5位數據類型

InnoDB為每個BIT列使用一個足夠存儲的最小整數類型來存放，使用BIT類型并不能節省太多的存儲空間，MySQL把BIT當作字符串類型，當檢索BIT(1)的值時，結果是一個包含二進制0或者1的字符串。

2.MySQL 模式設計的陷阱

2.1 太多的列

MySQL的存儲引擎API在工作的時需要在云服務器層和存儲引擎層通過行緩沖格式拷貝數據，然后在云服務器層將行緩沖內容解碼成各個列。從行緩沖中將編碼過的列轉換成行結構的操作代價非常的高，轉換的代價依賴于列的數量。

2.2太多的關聯

一個粗略的經驗法則，如果希望查詢執行的快且并發性好，單個查詢最好在12個表內做關聯

2.3NULL值

需要存儲一個事實上的“空值”到列表中時，可以使用0，某個特殊值，或者空字符串代替。MySQL會在索引中存儲NULL值，而Oracle則不會。

3.范式和反范式

在范式化的數據庫中，每個事實數據只會出現一次，

反范式化的數據庫中，信息是冗余的，可能會存儲在多個地方。

3.1范式化的優點和缺點

優點：

范式化的更新操作更快，只需要更改較少的數據。

范式化的表更小，可以更好的放在內存里，執行操作會更快。

沒有多余的數據，可以減少distinct或GROUP BY的操作。

缺點：

通常需要關聯，關聯代價昂貴，也可能使一些索引策略無效。

3.2 反范式的優點和缺點

優點:

所有的數據都在一張表中，可以避免關聯。

不關聯的時候即使全表掃描，也是順序IO。

缺點：

冗余的多余數據，更新更慢

表大，放到內存中，占用大，容易擠出熱數據

4.更快的讀，更慢的寫

為了提升讀查詢的速度，經常會建一些額外索引，增加冗余列，甚至是創建緩存表和匯總表，這些方法會增加寫查詢的負擔。

寫操作變慢并不是讀操作變得更快所付出的唯一代價，還可能同時增加了讀操作和寫操作的并發難度。

5.加快ALTER TABLE操作的速度

ALTER TABLE操作對特大表來說，是個大問題。

MySQL執行大部分修改表結構的步驟：

1.用新結構創建一個空表

2.從舊表中查出所有數據插入新表

3.刪除舊表

一般而言，大部分ALTER TABLE操作將導致MySQL服務對該表的訪問中斷。

對于常見的場景，常見的技巧有兩種：

1.現在一臺不提供服務的機器上執行ALTER TABLE操作，然后切換

2.影子拷貝，即和原來的步驟一樣，但是通過觸發器的方式更新新表舊表數據，然后重命名

所有的MODIFY COLUMN操作，都會導致表重建。

5.1 只修改frm（表結構）文件

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

移除一個列的AUTO_INCREMENT屬性

增加，移除，或更改ENUM和SET常量

步驟（本操作是火中取栗）：

1.創建一張有相同結構的空表，進行所需要的修改

2.執行FLUSH TABLES WITH READ LOCK。關閉所有正在使用的表，并且禁止表被打開

3.交換frm文件

4.執行UNLOCK TABLES來釋放第二步的讀鎖。

6.總結

1.避免設計過度復雜的數據庫模式

2.使用小而簡單的合適數據類型，盡可能避免使用NULL值

3.盡量使用相同的數據類型存儲相似或者相關的值。

4.可變長字符串在臨時表和排序時有可能悲觀的按照最大長度分配內存。

5.盡量使用自增整數列定義主鍵

6.避免使用MySQL不再推薦的特性

7.謹慎對待BIT，ENUM,SET

看完以上關于MySQL如何優化Schema與數據類型性能，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。