資料庫非主屬性是什麼

作者 | 草捏子

頭圖 |  CSDN 下載自東方IC

本文就資料型別和schema方面的最佳化進行介紹。

選擇最佳化的資料型別

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

更小的通常更好

應儘可能使用可以正確儲存資料的最小資料型別，夠用就好。這樣將佔用更少的磁碟、記憶體和快取，而在處理時也會耗時更少。

簡單就好

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

儘量避免NULL

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

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

一、整數型別

整數型別和所佔用的空間如下：

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

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

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

二、實數型別

實數型別的對比如下：

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

DECIMAL所佔空間大小與指定的精度有關，例如DECIMAL（M，D）：

M為整個數字的最大長度，取值範圍為［1， 65］，預設值為10；

D為小數點後的長度，取值範圍為［0， 30］，且D

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

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

DECIMAL（18， 9）：整數部分長度為9，佔用4個位元組。小數部分長度為9，佔用4個位元組。同時加上小數點1個位元組，則總共佔用9個位元組。

DECIMAL（20， 9）：整數部分長度為14，佔用7（4+3）個位元組。小數部分長度為9，佔用4個位元組。同時加上小數點1個位元組，則總共佔用12個位元組。

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

三、字串型別

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

最大長度比平均長度大很多；

列的更新少，避免碎片；

使用複雜的字符集，如UTF-8，每個字元能使用不同的位元組儲存。

CHAR則為定長字串，根據定義的字串長度分配足夠的空間，適用場景：

長度短；

長度相近，例如MD5；

經常更新。

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

四、日期和時間型別

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

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

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

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

五、選擇識別符號

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

正規化和反正規化

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

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

更新操作更快；

修改更少的資料；

表更小，更好地放記憶體中，執行操作更快；

更少需要 DISTINCT 或 GROUP BY。

但也由於資料分散存在各張表中，查詢時需要對錶進行關聯。而反正規化的優點則是不用進行關聯，將資料冗餘儲存。

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

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

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

第二境界：隨著遇到問題解決問題，漸漸瞭解到正規化的真正好處，從而能快速設計出低冗餘、高效率的資料庫。

第三境界：再經過N年的鍛鍊，是一定會發覺正規化的侷限性的。此時再去打破正規化，設計更合理的反正規化部分。

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

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

快取表和彙總表

除了上述說到的反正規化，在表中儲存冗餘資料，我們還可以建立一張完全獨立的彙總表或快取表，來滿足檢索的需要。

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

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

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

加快ALTER TABLE操作的速度

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

在一臺不提供服務的機器上執行 ALTER TABLE 操作，然後再與提供服務的主庫進行切換；

“影子複製”，建立一張與原表無關的新表，在資料遷移完成後，透過重新命名操作進行切換。

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

參考

《高效能MySQL》

MySQL DECIMAL 資料型別（https：//my。oschina。net/u/559356/blog/3057960）