MySQL 資料庫設計的「奧秘」
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”)
⚠️枚舉欄位是按照內部存儲整數而不是定義的字元串數字排序。
缺點：
- 字元串列表是固定的，如果未來要改變枚舉值則不適合
- 枚舉值轉為整數值保存有開銷
建議通用設計實踐：在「查找表」時採用整數主鍵而避免採用字元串的值進行關聯，因為性能更好。
