blob資料類型_MySQL中的資料類型和schema優化

最近在學習MySQL優化方面的知識。本文就資料類型和schema方面的優化進行介紹。

1. 選擇優化的資料類型

MySQL支援的資料類型有很多，而如何選擇出正确的資料類型，對于性能是至關重要的。以下幾個原則能夠幫助确定資料類型：

1. 更小的通常更好

   應盡可能使用可以正确存儲資料的最小資料類型，夠用就好。這樣将占用更少的磁盤、記憶體和緩存，而在處理時也會耗時更少。

2. 簡單就好

   當兩種資料類型都能勝任一個字段的存儲工作時，選擇簡單的那一方，往往是最好的選擇。例如整型和字元串，由于整型的操作代價要小于字元，是以當在兩者之間選擇時，選擇整型通常能夠獲得更好的性能。

3. 盡量避免NULL

   當列可為NULL時，對于MySQL來說，在索引和值比較等方面需要做更多的工作，雖然對性能的影響不是很大，但也應盡量避免設計為可為NULL。

除了以上原則，在選擇資料類型時，需遵循的步驟：首先确定合适的大類型，例如資料、字元串、時間等；然後再選擇具體的類型。下面将讨論大類型下的一些具體類型，首先是數字，有兩種類型：整數和實數。

1.1 整數類型

整數類型和所占用的空間如下：

整數類型	空間大小(bit)
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

整數類型所能存儲的範圍和空間大小有關：-2^(N-1)至2^(N-1)-1，其中N為空間大小的位數。

整數類型具有UNSIGNED的可選屬性，當聲明時，表示不允許負數，則存儲範圍變為：0至2^(N)-1，擴大了一倍。

在MySQL中，還可以為整數類型指定寬度，例如INT(1)，但這樣的意義并不大，并不會限制值的合法範圍，仍能存儲-2^31至2^31-1的值，所影響的是與MySQL的互動工具顯示字元的個數。

1.2 實數類型

實數類型的對比如下：

實數類型	空間大小(Byte)	取值範圍	計算精度
FLOAT	4	負數：-3.4E+38～-1.17E-38；非負數：0、1.17E-38~3.4E+38	近似計算
DOUBLE	8	負數：-1.79E+308～-2.22E-308；非負數：0、2.22E-308~1.79E+308	近似計算
DECIMAL	與精度有關	同DOUBLE	精确計算

從上面可以看出，FLOAT和DOUBLE都有固定的空間大小，但同時由于是使用标準的浮點運算，是以隻能近似計算。而DECIMAL則可以實作精确計算，與此同時占用的空間會相較更大，所耗費的計算開銷也更多。

DECIMAL所占空間大小與指定的精度有關，例如DECIMAL(M,D)：

• M為整個數字的最大長度，取值範圍為[1, 65]，預設值為10；
• D為小數點後的長度，取值範圍為[0, 30]，且D <= M，預設值為0。

MySQL在存儲DECIMAL類型時會作為二進制字元串存儲，每4個位元組存9個數字，當不足9位時，數字的占用空間如下：

數字個數	占用空間(Byte)
1、2	1
3、4	2
5、6	3
7、8	4

小數點前後将分别存儲，同時小數點也要占1個位元組。下面舉兩個計算的例子：

1. DECIMAL(18, 9)：整數部分長度為9，占用4個位元組。小數部分長度為9，占用4個位元組。同時加上小數點1個位元組，則總共占用9個位元組。
2. DECIMAL(20, 9)：整數部分長度為14，占用7(4+3)個位元組。小數部分長度為9，占用4個位元組。同時加上小數點1個位元組，則總共占用12個位元組。

可以看出DECIMAL的空間占用還是很大的，是以隻有當需要對小數進行精确計算時，才需要使用DECIMAL。除此之外，我們還可以使用BIGINT代替DECIMAL，例如需要保證小數點後5位的計算，可以将值乘上10的5次方後作為BIGINT存儲，這樣能同時避免浮點存儲計算不精确和DECIMAL精确計算代價高的問題。

1.3 字元串類型

最常用的字元串類型當屬VARCHAR和CHAR。VARCHAR作為可變長字元串，會使用1或2個額外位元組記錄字元串的長度，當最大長度未超過255時，隻需1個位元組記錄長度，超過255，則需2個位元組。VARCHAR的适用場景：

1. 最大長度比平均長度大很多；
2. 列的更新少，避免碎片；
3. 使用複雜的字元集，如UTF-8，每個字元能使用不同的位元組存儲。

CHAR則為定長字元串，根據定義的字元串長度配置設定足夠的空間，适用場景：

1. 長度短；
2. 長度相近，例如MD5；
3. 經常更新。

除了VARCHAR和CHAR，針對存儲大字元串，可以使用BLOB和TEXT類型。BLOB和TEXT的差別在于，BLOB是以二進制方式存儲，而TEXT是以字元方式存儲。這也導緻，BLOB類型的資料沒有字元集的概念，無法按字元排序，而TEXT類型則有字元集的概念，可以按字元排序。兩者的使用場景，也由存儲格式決定了，當存儲二進制資料時，例如圖檔，應使用BLOB，而存儲文本時，例如文章，則應使用TEXT類型。

1.4 日期和時間類型

MySQL中所能存儲的最小時間粒度為秒，常用的日期類型有DATETIME和TIMESTAMP。

類型	存儲内容	空間大小(Byte)	時區概念
DATETIME	格式為YYYYMMDDHHMMSS的整數	8	無
TIMESTAMP	從1970年1月1日零點以來的秒數	4	有

TIMESTAMP顯示的值将依賴于時區，意味在不同時區查詢到的值将不一樣。除了以上列出的不同，TIMESTAMP還具有一個特殊屬性，在插入和更新時，如果沒有指定第一個TIMESTAMP列的值，将會設定這個列的值為目前時間。

我們在開發過程中，應盡量使用TIMESTAMP，主要是因為其空間大小僅需DATETIME的一半，空間效率更高。

如果我們想存儲的日期和時間精确到秒之後，怎麼辦？由于MySQL并未提供，是以我們可以使用BIGINT存儲微妙級别的時間戳，或者使用DOUBLE存儲秒之後的小數部分。

1.5 選擇辨別符

通常來說整數是辨別符的最好選擇，主要是因為其簡單，計算快，且可使用AUTO_INCREMENT。

2. 範式和反範式

簡單來說，範式就是一張資料表的表結構所符合的某種設計标準的級别。第一範式，屬性不可分割，現在的RDBMS系統建成的表都是符合第一範式的。而第二範式，則是消除非主屬性對碼(可以了解為主鍵)的部分依賴。第三範式消除非主屬性對碼的傳遞依賴。具體的介紹，可以讀讀知乎上的這個回答(https://www.zhihu.com/question/24696366/answer/29189700)

嚴格範式化的資料庫中，每個事實資料會出現且隻出現一次，不會出現資料備援，這樣所能帶能帶來的好處有：

1. 更新操作更快；
2. 修改更少的資料；
3. 表更小，更好地放記憶體中，執行操作更快；
4. 更少需要DISTINCT或GROUP BY。

但也由于資料分散存在各張表中，查詢時需要對表進行關聯。而反範式的優點則是不用進行關聯，将資料備援存儲。

在實際應用中，不會出現完全的範式化或完全的反範式化，時常需要混用範式和反範式，使用部分範式化的schema，往往是最好的選擇。關于資料庫設計，在網上看到這樣一段話，大家可以感受下。

資料庫設計應該分為三個境界：

第一境界：剛入門資料庫設計，範式的重要性還未深刻了解。這時候出現的反範式設計，一般會出問題。

第二境界：随着遇到問題解決問題，漸漸了解到範式的真正好處，進而能快速設計出低備援、高效率的資料庫。

第三境界：再經過N年的鍛煉，是一定會發覺範式的局限性的。此時再去打破範式，設計更合理的反範式部分。

範式就像武俠裡面的招數，初學者妄想不按招數來，隻能死的很難堪。畢竟招數都是高手總結歸納的精華。而随着武功提高，招數熟練之後，必然是發現招數的局限性，要麼忘掉招數，要麼自創招數。

隻要努力，加上多熬幾年，總能達到第二個境界，總會覺得範式是經典。此時能不過分依賴範式，快速突破範式局限性的人，自然是高手。

4. 緩存表和彙總表

除了上述說到的反範式，在表中存儲備援資料，我們還可以建立一張完全獨立的彙總表或緩存表，來滿足檢索的需要。

緩存表，指的是存儲可以從schema其他表中擷取資料的表，也就是邏輯上備援的資料。而彙總表，則指的是存儲使用GROUP BY等語句聚合資料，計算出的不備援的資料。

緩存表，可用于優化搜尋和檢索查詢語句，這裡可以使用的技巧有對緩存表使用不同的存儲引擎，例如主表使用InnoDB，而緩存表則可使用MyISAM，獲得更小的索引占用空間。甚至可以将緩存表放到專門的搜尋系統中，例如Lucene。

彙總表，則是為了避免實時計算統計值所帶來的高昂代價，代價來自兩方面，一是需要掃描表中的大部分資料，二是建立特定的索引，會對UPDATE操作有影響。例如，查詢微信過去24小時的朋友圈數量，則可固定每1小時掃描全表，統計後寫一條記錄到彙總表，當查詢時，隻需查詢彙總表上最新的24條記錄，而不必每次查詢時都去掃描全表進行統計。

在使用緩存表和彙總表時，必須決定是實時維護資料還是定期重建，這取決于我們的需求。定期重建相比實時維護，能節省更多的資源，表的碎片更少。而在重建時，我們仍需保證資料在操作時可用，需要通過“影子表”來實作。在真實表後建立一張影子表，當填充好資料後，通過原子的重命名操作來切換影子表和原表。

5. 加快ALTER TABLE操作的速度

當MySQL在執行ALTER TABLE操作時，往往是建立一張表，然後把資料從舊表查出并插入到新表中，再删除舊表，如果表很大，這樣需要花費很長時間，且會導緻MySQL的服務中斷。為了避免服務中斷，通常可以使用兩種技巧：

1. 在一台不提供服務的機器上執行ALTER TABLE操作，然後再與提供服務的主庫進行切換；
2. “影子拷貝”，建立一張與原表無關的新表，在資料遷移完成後，通過重命名操作進行切換。

但也不是所有的ALTER TABLE操作會引起表重建，例如在修改字段的預設值時，使用MODIFY COLUMN會進行表重建，而使用ALTER COLUMN則不會進行表重建，操作速度很快。這是因為ALTER COLUMN在修改預設值時，會直接修改了存在表的.frm檔案(存儲字段的預設值)，而并未重建表。

參考

1. 《高性能MySQL》
2. MySQL DECIMAL 資料類型(https://my.oschina.net/u/559356/blog/3057960)