MySQL 那些常見的錯誤設計規範

2021 年 7 月 15 日
筆記
前端

依託於互聯網的發達，我們可以隨時隨地利用一些等車或坐地鐵的碎片時間學習以及了解資訊。同時發達的互聯網也方便人們能夠快速分享自己的知識，與相同愛好和需求的朋友們一起共同討論。

但是過於方便的分享也讓知識變得五花八門，很容易讓人接收到錯誤的信息。這些錯誤最多的都是因為技術發展迅速，而且沒有空閑時間去及時更新已經發佈的內容所導致。為了避免給後面學習的人造成誤解，我們今天來看一看 MySQL 設計規範中幾個常見的錯誤例子。

主鍵的設計

錯誤的設計規範：主鍵建議使用自增 ID 值，不要使用 UUID，MD5，HASH，字符串作為主鍵

這個設計規範在很多文章中都能看到，自增主鍵的優點有佔用空間小，有序，使用起來簡單等優點。

下面先來看看自增主鍵的缺點：

自增值由於在服務器端產生，需要有一把自增的 AI 鎖保護，若這時有大量的插入請求，就可能存在自增引起的性能瓶頸，所以存在並發性能問題；
自增值做主鍵，只能在當前實例中保證唯一，不能保證全局唯一，這就導致無法在分佈式架構中使用；
公開數據值，容易引發安全問題，如果我們的商品 ID 是自增主鍵的話，用戶可以通過修改 ID 值來獲取商品，嚴重的情況下可以知道我們數據庫中一共存了多少商品。
MGR（MySQL Group Replication）可能引起的性能問題；

因為自增值是在 MySQL 服務端產生的值，需要有一把自增的 AI 鎖保護，若這時有大量的插入請求，就可能存在自增引起的性能瓶頸。比如在 MySQL 數據庫中，參數 innodb_autoinc_lock_mode 用於控制自增鎖持有的時間。雖然，我們可以調整參數 innodb_autoinc_lock_mode 獲得自增的最大性能，但是由於其還存在其它問題。因此，在並發場景中，更推薦 UUID 做主鍵或業務自定義生成主鍵。

我們可以直接在 MySQ L使用 UUID() 函數來獲取 UUID 的值。

MySQL> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 23ebaa88-ce89-11eb-b431-0242ac110002 |
+--------------------------------------+
1 row in set (0.00 sec)

需要特別注意的是，在存儲時間時，UUID 是根據時間位逆序存儲， 也就是低時間低位存放在最前面，高時間位在最後，即 UUID 的前 4 個位元組會隨着時間的變化而不斷「隨機」變化，並非單調遞增。而非隨機值在插入時會產生離散 IO，從而產生性能瓶頸。這也是 UUID 對比自增值最大的弊端。

為了解決這個問題，MySQL 8.0 推出了函數 UUID_TO_BIN，它可以把 UUID 字符串：

通過參數將時間高位放在最前，解決了 UUID 插入時亂序問題；
去掉了無用的字符串”-“，精簡存儲空間；
將字符串其轉換為二進制值存儲，空間最終從之前的 36 個位元組縮短為了 16 位元組。

下面我們將之前的 UUID 字符串 23ebaa88-ce89-11eb-b431-0242ac110002 通過函數 UUID_TO_BIN 進行轉換，得到二進制值如下所示：

MySQL> SELECT UUID_TO_BIN('23ebaa88-ce89-11eb-b431-0242ac110002',TRUE) as UUID_BIN;
+------------------------------------+
| UUID_BIN                           |
+------------------------------------+
| 0x11EBCE8923EBAA88B4310242AC110002 |
+------------------------------------+
1 row in set (0.01 sec)

除此之外，MySQL 8.0 也提供了函數 BIN_TO_UUID，支持將二進制值反轉為 UUID 字符串。

雖然 MySQL 8.0 版本之前沒有函數 UUID_TO_BIN/BIN_TO_UUID，還是可以通過自定義函數的方式解決。應用層的話可以根據自己的編程語言編寫相應的函數。

當然，很多同學也擔心 UUID 的性能和存儲佔用的空間問題，這裡我也做了相關的插入性能測試，結果如下表所示：

可以看到，MySQL 8.0 提供的排序 UUID 性能最好，甚至比自增 ID 還要好。此外，由於 UUID_TO_BIN 轉換為的結果是16 位元組，僅比自增 ID 增加 8 個位元組，最後存儲佔用的空間也僅比自增大了 3G。

而且由於 UUID 能保證全局唯一，因此使用 UUID 的收益遠遠大於自增 ID。可能你已經習慣了用自增做主鍵，但是在並發場景下，更推薦 UUID 這樣的全局唯一值做主鍵。

當然了，UUID雖好，但是在分佈式場景下，主鍵還需要加入一些額外的信息，這樣才能保證後續二級索引的查詢效率，推薦根據業務自定義生成主鍵。但是在並發量和數據量沒那麼大的情況下，還是推薦使用自增 UUID 的。大家更不要以為 UUID 不能當主鍵了。

金融字段的設計

錯誤的設計規範：同財務相關的金額類數據必須使用 decimal 類型 由於 float 和 double 都是非精準的浮點數類型，而 decimal 是精準的浮點數類型。所以一般在設計用戶餘額，商品價格等金融類字段一般都是使用 decimal 類型，可以精確到分。

但是在海量互聯網業務的設計標準中，並不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉化為整型類型。 也就是說，金融類型更推薦使用用分單位存儲，而不是用元單位存儲。如1元在數據庫中用整型類型 100 存儲。

下面是 bigint 類型的優點：

decimal 是通過二進制實現的一種編碼方式，計算效率不如 bigint
使用 bigint 的話，字段是定長字段，存儲高效，而 decimal 根據定義的寬度決定，在數據設計中，定長存儲性能更好
使用 bigint 存儲分為單位的金額，也可以存儲千兆級別的金額，完全夠用

枚舉字段的使用

錯誤的設計規範：避免使用 ENUM 類型

在以前開發項目中，遇到用戶性別，商品是否上架，評論是否隱藏等字段的時候，都是簡單的將字段設計為 tinyint，然後在字段里備註 0 為什麼狀態，1 為什麼狀態。

這樣設計的問題也比較明顯：

表達不清：這個表可能是其他同事設計的，你印象不是特別深的話，每次都需要去看字段注釋，甚至有時候在編碼的時候需要去數據庫確認字段含義
臟數據：雖然在應用層可以通過代碼限制插入的數值，但是還是可以通過sql和可視化工具修改值

這種固定選項值的字段，推薦使用 ENUM 枚舉字符串類型，外加 SQL_MODE 的嚴格模式

在MySQL 8.0.16 以後的版本，可以直接使用check約束機制，不需要使用enum枚舉字段類型

而且我們一般在定義枚舉值的時候使用”Y”,”N”等單個字符，並不會佔用很多空間。但是如果選項值不固定的情況，隨着業務發展可能會增加，才不推薦使用枚舉字段。

索引個數限制

錯誤的設計規範：限制每張表上的索引數量，一張表的索引不能超過 5 個

MySQL 單表的索引沒有個數限制，業務查詢有具體需要，創建即可，不要迷信個數限制

子查詢的使用

錯誤的設計規範：避免使用子查詢

其實這個規範對老版本的 MySQL 來說是對的，因為之前版本的 MySQL 數據庫對子查詢優化有限，所以很多 OLTP 業務場合下，我們都要求在線業務儘可能不用子查詢。

然而，MySQL 8.0 版本中，子查詢的優化得到大幅提升，所以在新版本的MySQL中可以放心的使用子查詢。

子查詢相比 JOIN 更易於人類理解，比如我們現在想查看2020年沒有發過文章的同學的數量

SELECT COUNT(*)
FROM user
WHERE id not in (
    SELECT user_id
    from blog
    where publish_time >= "2020-01-01" AND  publish_time <= "2020-12-31"
)

可以看到，子查詢的邏輯非常清晰：通過 not IN 查詢文章表的用戶有哪些。

如果用 left join 寫

SELECT count(*)
FROM user LEFT JOIN blog
ON user.id = blog.user_id and blog.publish_time >= "2020-01-01" and blog.publish_time <= "2020-12-31"
where blog.user_id is NULL;

可以發現，雖然 LEFT JOIN 也能完成上述需求，但不容易理解。

我們使用 explain查看兩條 sql 的執行計劃，發現都是一樣的

通過上圖可以很明顯看到，不論是子查詢還是 LEFT JOIN，最終都被轉換成了left hash Join，所以上述兩條 SQL 的執行時間是一樣的。即，在 MySQL 8.0 中，優化器會自動地將 IN 子查詢優化，優化為最佳的 JOIN 執行計劃，這樣一來，會顯著的提升性能。

總結

閱讀完前面的內容相信大家對 MySQL 已經有了新的認知，這些常見的錯誤可以總結為以下幾點：

UUID 也可以當主鍵，自增 UUID 比自增主鍵性能更好，多佔用的空間也可忽略不計
金融字段除了 decimal，也可以試試 bigint，存儲分為單位的數據
對於固定選項值的字段，MySQL8 以前推薦使用枚舉字段，MySQL8 以後使用check函數約束，不要使用 0，1，2 表示
一張表的索引個數並沒有限制不能超過5個，可以根據業務情況添加和刪除
MySQL8 對子查詢有了優化，可以放心使用。

Tags: 前端