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

撈起月亮的漁民發表於2022-09-07
MySql

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

但是過於方便的分享也讓知識變得五花八門，很容易讓人接收到錯誤的資訊。這些錯誤最多的都是因為技術發展迅速，而且沒有空閒時間去及時更新已經發布的內容所導致。為了避免給後面學習的人造成誤解，我們今天來看一看 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 對子查詢有了優化，可以放心使用。

相關文章