2 MySQL 資料庫設計的“奧祕”
【主題】邏輯設計：資料型別與 Schema
所謂“萬丈高樓平地起”，一個穩固的建築離不開紮實的基礎。同樣，良好的的「邏輯設計」和「物理設計」是資料庫高效能的基石。
本文的主要內容總結了一張圖，你可以點選檢視。
我們需要根據系統需要執行的 SQL 語句設計 schema，這往往權衡各種因素。
[idea]瞧！權衡和取捨不僅僅在系統設計、方案設計時處處體現其價值，在資料庫表設計也同樣如此。
舉個例子，反正規化設計可以加快某些型別查詢，但可能會使另一些型別查詢變慢。如增加彙總表可以優化查詢，但這些表維護成本可能很高。
首先，讓我們一起來看下 MySQL 的資料型別，這是我們設計資料表前需要事先掌握的。
如何選擇優化的資料型別？
MySQL 支援的資料型別很多，為了做出更好的選擇，我們來看幾個簡單的原則：
1.更小的通常更好
儘量使用可以正確儲存資料的最小資料型別。
優點：
- 佔用更少的磁碟、記憶體和 CPU 快取
- 處理時需要 CPU 週期更少
缺點：
- 但是要確保沒有低估儲存值範圍
2.簡單就好
簡單資料型別需要更少的 CPU 週期。
比如整型比字串代價低（字串有字符集和校對規則）。
3.儘量避免 NULL
NULL 列對 MySQL 更難優化，因為為 NULL 的列使得索引、索引統計和值比較都更復雜；並且佔用更多儲存空間。
todo 為什麼佔用更多儲存空間？
推薦步驟
步驟1：確定合適的大型別
如數字、字串、時間等。
步驟2：選擇具體型別
同型別下只是儲存的長度、範圍、允許的精度、需要的物理空間（磁碟、記憶體）不同。
資料型別概覽
1.整數型別
有兩種型別的數字：「整數」和「實數」。
整數有很多種，如：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別使用 8，16，24，32，64 位儲存空間。
-
有可選的 UNSIGNED 屬性，表示不允許負值。
-
還可以指定寬度
例如 INT(11)，它不會限制值的合法範圍，只是規定了 MySQL 的一些客戶端顯示字元的個數。
INT(1) 和 INT(11) 是相同的。
2.實數型別
即帶有小數部分的數字。MySQL 既支援精確型別，也支援不精確型別。
- 可以指定需要的精度
FLOAT 和 DOUBLE 型別使用浮點運算進行近似計算。
DECIMAL 型別用於儲存精確的小數。使用場景：儘量只有對小數需要精確計算時才使用，因為需要額外的儲存空間和計算開銷。
資料量比較大時，可以使用 BIGINT 代替 DECIMAL，對應單位乘以相應倍數即可。
3.字串型別
VACHAR 和 CHAR 型別
VACHAR
-
比定長型別更節省空間，因為它只使用必要的空間（如字串越短使用空間越少）。
-
需要使用 1(<=255) 或 2(>255) 個額外位元組記錄字串長度。
-
由於變長，Update 可能使行變得比原來更長，容易產生碎片
如 InnoDB 需要頁分裂使得行可以放到頁內。
適合 VACHAR 的場景：
- 最大長度比平均長度大得多
- 列的更新很少
- 使用了像 UTF-8 這樣的複雜字符集
CHAR
- 定長，會根據需要填充空格
適合 CHAR 的場景：
- 儲存很短的字串或所有值接近同一個長度
- 經常變更資料，不易產生碎片（因為定長不會頁分裂）
建議：最好的策略是隻分配真正需要的空間。
todo Vachar(5) 和 Vachar(200) 又什麼區別？空間開銷一樣吧？
BLOB 和 TEXT 型別
適合場景：儲存很大的資料，前者使用二進位制，後者使用字元儲存。
特殊之處：MySQL 把 BLOB 和 TEXT 值當作獨立物件處理，值太大時會使用專門的“外部”儲存區域儲存，在行記憶體儲指標指向外部實際值。
[idea]這個設計在程式開發中也可以使用，有時一些大屬性佔用儲存較多但使用頻率較低，可以單獨儲存在其他地方，通過指標進行引用，從而使得該物件更小。
不能將 BLOB 和 TEXT 列全部長度索引，不能用這些索引消除排序。
如果 Explain 執行計劃的 Extra 列包含“Using temporary”，說明這個查詢使用了「隱式臨時表」。
使用列舉（ENUM）代替字串型別
優點：儲存列舉時非常緊湊，節約空間
MySQL 內部將每個值在列表中位置儲存為整數，在表的 .frm 檔案中儲存“數字-字串”對映關係的“查詢表”。
所以，使用數字作為列舉常量，這種雙重性容易導致混亂。建議儘量避免這麼做。
例如 ENUM('1','2',3'')
⚠️列舉欄位是按照內部儲存整數而不是定義的字串數字排序。
缺點：
- 字串列表是固定的，如果未來要改變列舉值則不適合
- 列舉值轉為整數值儲存有開銷
建議通用設計實踐：在“查詢表”時採用整數主鍵而避免採用字串的值進行關聯，因為效能更好。