MySQL 那些常見的錯誤設計規(guī)范,你都知道嗎

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

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

主鍵的設計

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

這個設計規(guī)范在很多文章中都能看到，自增主鍵的優(yōu)點有占用空間小，有序，使用起來簡單等優(yōu)點。

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

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

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

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

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

需要特別注意的是，在存儲時間時，UUID 是根據(jù)時間位逆序存儲， 也就是低時間低位存放在最前面，高時間位在最后，即 UUID 的前 4 個字節(jié)會隨著時間的變化而不斷“隨機”變化，并非單調(diào)遞增。而非隨機值在插入時會產(chǎn)生離散 IO，從而產(chǎn)生性能瓶頸。這也是 UUID 對比自增值最大的弊端。

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

• 通過參數(shù)將時間高位放在最前，解決了 UUID 插入時亂序問題；
• 去掉了無用的字符串"-"，精簡存儲空間；
• 將字符串其轉(zhuǎn)換為二進制值存儲，空間最終從之前的 36 個字節(jié)縮短為了 16 字節(jié)。

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

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 也提供了函數(shù) BIN_TO_UUID，支持將二進制值反轉(zhuǎn)為 UUID 字符串。

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

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

可以看到，MySQL 8.0 提供的排序 UUID 性能最好，甚至比自增 ID 還要好。此外，由于 UUID_TO_BIN 轉(zhuǎn)換為的結(jié)果是16 字節(jié)，僅比自增 ID 增加 8 個字節(jié)，最后存儲占用的空間也僅比自增大了 3G。

而且由于 UUID 能保證全局唯一，因此使用 UUID 的收益遠遠大于自增 ID?？赡苣阋呀?jīng)習慣了用自增做主鍵，但是在并發(fā)場景下，更推薦 UUID 這樣的全局唯一值做主鍵。

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

金融字段的設計

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

但是在海量互聯(lián)網(wǎng)業(yè)務的設計標準中，并不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉(zhuǎn)化為整型類型。 也就是說，金融類型更推薦使用用分單位存儲，而不是用元單位存儲。如1元在數(shù)據(jù)庫中用整型類型 100 存儲。

下面是 bigint 類型的優(yōu)點：

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

枚舉字段的使用

錯誤的設計規(guī)范：避免使用 ENUM 類型

在以前開發(fā)項目中，遇到用戶性別，商品是否上架，評論是否隱藏等字段的時候，都是簡單的將字段設計為 tinyint，然后在字段里備注 0 為什么狀態(tài)，1 為什么狀態(tài)。

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

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

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

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

而且我們一般在定義枚舉值的時候使用"Y","N"等單個字符，并不會占用很多空間。但是如果選項值不固定的情況，隨著業(yè)務發(fā)展可能會增加，才不推薦使用枚舉字段。

索引個數(shù)限制

錯誤的設計規(guī)范：限制每張表上的索引數(shù)量，一張表的索引不能超過 5 個

MySQL 單表的索引沒有個數(shù)限制，業(yè)務查詢有具體需要，創(chuàng)建即可，不要迷信個數(shù)限制

子查詢的使用

錯誤的設計規(guī)范：避免使用子查詢

其實這個規(guī)范對老版本的 MySQL 來說是對的，因為之前版本的 MySQL 數(shù)據(jù)庫對子查詢優(yōu)化有限，所以很多 OLTP 業(yè)務場合下，我們都要求在線業(yè)務盡可能不用子查詢。

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

子查詢相比 JOIN 更易于人類理解，比如我們現(xiàn)在想查看2020年沒有發(fā)過文章的同學的數(shù)量

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;

可以發(fā)現(xiàn)，雖然 LEFT JOIN 也能完成上述需求，但不容易理解。

我們使用 explain查看兩條 sql 的執(zhí)行計劃，發(fā)現(xiàn)都是一樣的

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

總結(jié)

閱讀完前面的內(nèi)容相信大家對 MySQL 已經(jīng)有了新的認知，這些常見的錯誤可以總結(jié)為以下幾點：

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

到此這篇關于MySQL 那些常見的錯誤設計規(guī)范的文章就介紹到這了,更多相關MySQL 錯誤設計規(guī)范內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！