MySQL中的數(shù)據(jù)類型和schema優(yōu)化是什么

小編給大家分享一下MySQL中的數(shù)據(jù)類型和schema優(yōu)化是什么，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！

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

MySQL支持的數(shù)據(jù)類型有很多，而如何選擇出正確的數(shù)據(jù)類型，對于性能是至關(guān)重要的。以下幾個原則能夠幫助確定數(shù)據(jù)類型：

1. 更小的通常更好

   應(yīng)盡可能使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型，夠用就好。這樣將占用更少的磁盤、內(nèi)存和緩存，而在處理時也會耗時更少。

2. 簡單就好

   當(dāng)兩種數(shù)據(jù)類型都能勝任一個字段的存儲工作時，選擇簡單的那一方，往往是最好的選擇。例如整型和字符串，由于整型的操作代價要小于字符，所以當(dāng)在兩者之間選擇時，選擇整型通常能夠獲得更好的性能。

3. 盡量避免NULL

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

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

1.1 整數(shù)類型

整數(shù)類型和所占用的空間如下：

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

整數(shù)類型具有UNSIGNED的可選屬性，當(dāng)聲明時，表示不允許負(fù)數(shù)，則存儲范圍變?yōu)椋?至2^(N)-1，擴大了一倍。

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

1.2 實數(shù)類型

實數(shù)類型的對比如下：

從上面可以看出，F(xiàn)LOAT和DOUBLE都有固定的空間大小，但同時由于是使用標(biāo)準(zhǔn)的浮點運算，所以只能近似計算。而DECIMAL則可以實現(xiàn)精確計算，與此同時占用的空間會相較更大，所耗費的計算開銷也更多。

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

• M為整個數(shù)字的最大長度，取值范圍為[1, 65]，默認(rèn)值為10；
• D為小數(shù)點后的長度，取值范圍為[0, 30]，且D <= M，默認(rèn)值為0。

MySQL在存儲DECIMAL類型時會作為二進制字符串存儲，每4個字節(jié)存9個數(shù)字，當(dāng)不足9位時，數(shù)字的占用空間如下：

小數(shù)點前后將分別存儲，同時小數(shù)點也要占1個字節(jié)。下面舉兩個計算的例子：

1. DECIMAL(18, 9)：整數(shù)部分長度為9，占用4個字節(jié)。小數(shù)部分長度為9，占用4個字節(jié)。同時加上小數(shù)點1個字節(jié)，則總共占用9個字節(jié)。
2. DECIMAL(20, 9)：整數(shù)部分長度為14，占用7（4+3）個字節(jié)。小數(shù)部分長度為9，占用4個字節(jié)。同時加上小數(shù)點1個字節(jié)，則總共占用12個字節(jié)。

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

1.3 字符串類型

最常用的字符串類型當(dāng)屬VARCHAR和CHAR。VARCHAR作為可變長字符串，會使用1或2個額外字節(jié)記錄字符串的長度，當(dāng)最大長度未超過255時，只需1個字節(jié)記錄長度，超過255，則需2個字節(jié)。VARCHAR的適用場景：

1. 最大長度比平均長度大很多；
2. 列的更新少，避免碎片；
3. 使用復(fù)雜的字符集，如UTF-8，每個字符能使用不同的字節(jié)存儲。

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

1. 長度短；
2. 長度相近，例如MD5；
3. 經(jīng)常更新。

除了VARCHAR和CHAR，針對存儲大字符串，可以使用BLOB和TEXT類型。BLOB和TEXT的區(qū)別在于，BLOB是以二進制方式存儲，而TEXT是以字符方式存儲。這也導(dǎo)致，BLOB類型的數(shù)據(jù)沒有字符集的概念，無法按字符排序，而TEXT類型則有字符集的概念，可以按字符排序。兩者的使用場景，也由存儲格式?jīng)Q定了，當(dāng)存儲二進制數(shù)據(jù)時，例如圖片，應(yīng)使用BLOB，而存儲文本時，例如文章，則應(yīng)使用TEXT類型。

1.4 日期和時間類型

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

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

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

如果我們想存儲的日期和時間精確到秒之后，怎么辦？由于MySQL并未提供，所以我們可以使用BIGINT存儲微妙級別的時間戳，或者使用DOUBLE存儲秒之后的小數(shù)部分。

1.5 選擇標(biāo)識符

通常來說整數(shù)是標(biāo)識符的最好選擇，主要是因為其簡單，計算快，且可使用AUTO_INCREMENT。

2. 范式和反范式

簡單來說，范式就是一張數(shù)據(jù)表的表結(jié)構(gòu)所符合的某種設(shè)計標(biāo)準(zhǔn)的級別。第一范式，屬性不可分割，現(xiàn)在的RDBMS系統(tǒng)建成的表都是符合第一范式的。而第二范式，則是消除非主屬性對碼（可以理解為主鍵）的部分依賴。第三范式消除非主屬性對碼的傳遞依賴。具體的介紹，可以讀讀知乎上的這個回答（https://www.zhihu.com/question/24696366/answer/29189700）

嚴(yán)格范式化的數(shù)據(jù)庫中，每個事實數(shù)據(jù)會出現(xiàn)且只出現(xiàn)一次，不會出現(xiàn)數(shù)據(jù)冗余，這樣所能帶能帶來的好處有：

1. 更新操作更快；
2. 修改更少的數(shù)據(jù)；
3. 表更小，更好地放內(nèi)存中，執(zhí)行操作更快；
4. 更少需要DISTINCT或GROUP BY。

但也由于數(shù)據(jù)分散存在各張表中，查詢時需要對表進行關(guān)聯(lián)。而反范式的優(yōu)點則是不用進行關(guān)聯(lián)，將數(shù)據(jù)冗余存儲。

在實際應(yīng)用中，不會出現(xiàn)完全的范式化或完全的反范式化，時常需要混用范式和反范式，使用部分范式化的schema，往往是最好的選擇。關(guān)于數(shù)據(jù)庫設(shè)計，在網(wǎng)上看到這樣一段話，大家可以感受下。

數(shù)據(jù)庫設(shè)計應(yīng)該分為三個境界：

第一境界：剛?cè)腴T數(shù)據(jù)庫設(shè)計，范式的重要性還未深刻理解。這時候出現(xiàn)的反范式設(shè)計，一般會出問題。

第二境界：隨著遇到問題解決問題，漸漸了解到范式的真正好處，從而能快速設(shè)計出低冗余、高效率的數(shù)據(jù)庫。

第三境界：再經(jīng)過N年的鍛煉，是一定會發(fā)覺范式的局限性的。此時再去打破范式，設(shè)計更合理的反范式部分。

范式就像武俠里面的招數(shù)，初學(xué)者妄想不按招數(shù)來，只能死的很難堪。畢竟招數(shù)都是高手總結(jié)歸納的精華。而隨著武功提高，招數(shù)熟練之后，必然是發(fā)現(xiàn)招數(shù)的局限性，要么忘掉招數(shù)，要么自創(chuàng)招數(shù)。

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

4. 緩存表和匯總表

除了上述說到的反范式，在表中存儲冗余數(shù)據(jù)，我們還可以創(chuàng)建一張完全獨立的匯總表或緩存表，來滿足檢索的需要。

緩存表，指的是存儲可以從schema其他表中獲取數(shù)據(jù)的表，也就是邏輯上冗余的數(shù)據(jù)。而匯總表，則指的是存儲使用GROUP BY等語句聚合數(shù)據(jù)，計算出的不冗余的數(shù)據(jù)。

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

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

在使用緩存表和匯總表時，必須決定是實時維護數(shù)據(jù)還是定期重建，這取決于我們的需求。定期重建相比實時維護，能節(jié)省更多的資源，表的碎片更少。而在重建時，我們?nèi)孕璞ＷC數(shù)據(jù)在操作時可用，需要通過“影子表”來實現(xiàn)。在真實表后創(chuàng)建一張影子表，當(dāng)填充好數(shù)據(jù)后，通過原子的重命名操作來切換影子表和原表。

5. 加快ALTER TABLE操作的速度

當(dāng)MySQL在執(zhí)行ALTER TABLE操作時，往往是新建一張表，然后把數(shù)據(jù)從舊表查出并插入到新表中，再刪除舊表，如果表很大，這樣需要花費很長時間，且會導(dǎo)致MySQL的服務(wù)中斷。為了避免服務(wù)中斷，通?？梢允褂?strong>兩種技巧：

1. 在一臺不提供服務(wù)的機器上執(zhí)行ALTER TABLE操作，然后再與提供服務(wù)的主庫進行切換；
2. “影子拷貝”，建立一張與原表無關(guān)的新表，在數(shù)據(jù)遷移完成后，通過重命名操作進行切換。

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

看完了這篇文章，相信你對MySQL中的數(shù)據(jù)類型和schema優(yōu)化是什么有了一定的了解，想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！