共計 4275 個字符，預計需要花費 11 分鐘才能閱讀完成。
這篇文章將為大家詳細講解有關 MySQL 中怎么優化 Schema，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
1. 選擇優化的數據類型
MySQL 支持的數據類型有很多，而如何選擇出正確的數據類型，對于性能是至關重要的。以下幾個原則能夠幫助確定數據類型：
更小的通常更好
應盡可能使用可以正確存儲數據的最小數據類型，夠用就好。這樣將占用更少的磁盤、內存和緩存，而在處理時也會耗時更少。
簡單就好
當兩種數據類型都能勝任一個字段的存儲工作時，選擇簡單的那一方，往往是最好的選擇。例如整型和字符串，由于整型的操作代價要小于字符，所以當在兩者之間選擇時，選擇整型通常能夠獲得更好的性能。
盡量避免 NULL
當列可為 NULL 時，對于 MySQL 來說，在索引和值比較等方面需要做更多的工作，雖然對性能的影響不是很大，但也應盡量避免設計為可為 NULL。
除了以上原則，在選擇數據類型時，需遵循的步驟：首先確定合適的大類型，例如數據、字符串、時間等; 然后再選擇具體的類型。下面將討論大類型下的一些具體類型，首先是數字，有兩種類型：整數和實數。
1.1 整數類型
整數類型和所占用的空間如下：
整數類型空間大小（bit）TINYINT8SMALLINT16MEDIUMINT24INT32BIGINT64
整數類型所能存儲的范圍和空間大小有關：-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）取值范圍計算精度 FLOAT4 負數：-3.4E+38～-1.17E-38；非負數：0、1.17E-38~3.4E+38 近似計算 DOUBLE8 負數：-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、213、425、637、84
小數點前后將分別存儲，同時小數點也要占 1 個字節。下面舉兩個計算的例子：
鴻蒙官方戰略合作共建——HarmonyOS 技術社區
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 精確計算代價高的問題。
1.3 字符串類型
最常用的字符串類型當屬 VARCHAR 和 CHAR。VARCHAR 作為可變長字符串，會使用 1 或 2 個額外字節記錄字符串的長度，當最大長度未超過 255 時，只需 1 個字節記錄長度，超過 255，則需 2 個字節。VARCHAR 的適用場景：
鴻蒙官方戰略合作共建——HarmonyOS 技術社區
最大長度比平均長度大很多;
列的更新少，避免碎片;
使用復雜的字符集，如 UTF-8，每個字符能使用不同的字節存儲。
CHAR 則為定長字符串，根據定義的字符串長度分配足夠的空間，適用場景：
鴻蒙官方戰略合作共建——HarmonyOS 技術社區
長度短;
長度相近，例如 MD5;
經常更新。
除了 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)
嚴格范式化的數據庫中，每個事實數據會出現且只出現一次，不會出現數據冗余，這樣所能帶能帶來的好處有：
鴻蒙官方戰略合作共建——HarmonyOS 技術社區
更新操作更快;
修改更少的數據;
表更小，更好地放內存中，執行操作更快;
更少需要 DISTINCT 或 GROUP BY。
但也由于數據分散存在各張表中，查詢時需要對表進行關聯。而反范式的優點則是不用進行關聯，將數據冗余存儲。
在實際應用中，不會出現完全的范式化或完全的反范式化，時常需要混用范式和反范式，使用部分范式化的 schema，往往是最好的選擇。關于數據庫設計，在網上看到這樣一段話，大家可以感受下。
數據庫設計應該分為三個境界：
第一境界：剛入門數據庫設計，范式的重要性還未深刻理解。這時候出現的反范式設計，一般會出問題。
第二境界：隨著遇到問題解決問題，漸漸了解到范式的真正好處，從而能快速設計出低冗余、高效率的數據庫。
第三境界：再經過 N 年的鍛煉，是一定會發覺范式的局限性的。此時再去打破范式，設計更合理的反范式部分。
范式就像武俠里面的招數，初學者妄想不按招數來，只能死的很難堪。畢竟招數都是高手總結歸納的精華。而隨著武功提高，招數熟練之后，必然是發現招數的局限性，要么忘掉招數，要么自創招數。
只要努力，加上多熬幾年，總能達到第二個境界，總會覺得范式是經典。此時能不過分依賴范式，快速突破范式局限性的人，自然是高手。
4. 緩存表和匯總表
除了上述說到的反范式，在表中存儲冗余數據，我們還可以創建一張完全獨立的匯總表或緩存表，來滿足檢索的需要。
緩存表，指的是存儲可以從 schema 其他表中獲取數據的表，也就是邏輯上冗余的數據。而匯總表，則指的是存儲使用 GROUP BY 等語句聚合數據，計算出的不冗余的數據。
緩存表，可用于優化搜索和檢索查詢語句，這里可以使用的技巧有對緩存表使用不同的存儲引擎，例如主表使用 InnoDB，而緩存表則可使用 MyISAM，獲得更小的索引占用空間。甚至可以將緩存表放到專門的搜索系統中，例如 Lucene。
匯總表，則是為了避免實時計算統計值所帶來的高昂代價，代價來自兩方面，一是需要掃描表中的大部分數據，二是建立特定的索引，會對 UPDATE 操作有影響。例如，查詢微信過去 24 小時的朋友圈數量，則可固定每 1 小時掃描全表，統計后寫一條記錄到匯總表，當查詢時，只需查詢匯總表上最新的 24 條記錄，而不必每次查詢時都去掃描全表進行統計。
在使用緩存表和匯總表時，必須決定是實時維護數據還是定期重建，這取決于我們的需求。定期重建相比實時維護，能節省更多的資源，表的碎片更少。而在重建時，我們仍需保證數據在操作時可用，需要通過“影子表”來實現。在真實表后創建一張影子表，當填充好數據后，通過原子的重命名操作來切換影子表和原表。
5. 加快 ALTER TABLE 操作的速度
當 MySQL 在執行 ALTER TABLE 操作時，往往是新建一張表，然后把數據從舊表查出并插入到新表中，再刪除舊表，如果表很大，這樣需要花費很長時間，且會導致 MySQL 的服務中斷。為了避免服務中斷，通常可以使用兩種技巧：
在一臺不提供服務的機器上執行 ALTER TABLE 操作，然后再與提供服務的主庫進行切換;
“影子拷貝”，建立一張與原表無關的新表，在數據遷移完成后，通過重命名操作進行切換。
但也不是所有的 ALTER TABLE 操作會引起表重建，例如在修改字段的默認值時，使用 MODIFY COLUMN 會進行表重建，而使用 ALTER COLUMN 則不會進行表重建，操作速度很快。這是因為 ALTER COLUMN 在修改默認值時，會直接修改了存在表的.frm 文件(存儲字段的默認值)，而并未重建表。
關于 MySQL 中怎么優化 Schema 就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
