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

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

主鍵的設(shè)計(jì)

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

這個(gè)設(shè)計(jì)規(guī)范在很多文章中都能看到，自增主鍵的優(yōu)點(diǎn)有占用空間小，有序，使用起來(lái)簡(jiǎn)單等優(yōu)點(diǎn)。

下面先來(lái)看看自增主鍵的缺點(diǎn)：

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

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

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

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

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

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

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

下面我們將之前的 UUID 字符串 23ebaa88-ce89-11eb-b431-0242ac110002 通過(guò)函數(shù) UUID_TO_BIN 進(jìn)行轉(zhuǎn)換，得到二進(jì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，支持將二進(jìn)制值反轉(zhuǎn)為 UUID 字符串。

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

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

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

而且由于 UUID 能保證全局唯一，因此使用 UUID 的收益遠(yuǎn)遠(yuǎn)大于自增 ID。可能你已經(jīng)習(xí)慣了用自增做主鍵，但是在并發(fā)場(chǎng)景下，更推薦 UUID 這樣的全局唯一值做主鍵。

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

金融字段的設(shè)計(jì)

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

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

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

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

枚舉字段的使用

錯(cuò)誤的設(shè)計(jì)規(guī)范：避免使用 ENUM 類型

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

這樣設(shè)計(jì)的問(wèn)題也比較明顯：

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

這種固定選項(xiàng)值的字段，推薦使用 ENUM 枚舉字符串類型，外加 SQL_MODE 的嚴(yán)格模式

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

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

索引個(gè)數(shù)限制

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

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

子查詢的使用

錯(cuò)誤的設(shè)計(jì)規(guī)范：避免使用子查詢

其實(shí)這個(gè)規(guī)范對(duì)老版本的 MySQL 來(lái)說(shuō)是對(duì)的，因?yàn)橹鞍姹镜?MySQL 數(shù)據(jù)庫(kù)對(duì)子查詢優(yōu)化有限，所以很多 OLTP 業(yè)務(wù)場(chǎng)合下，我們都要求在線業(yè)務(wù)盡可能不用子查詢。

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

子查詢相比 JOIN 更易于人類理解，比如我們現(xiàn)在想查看2020年沒(méi)有發(fā)過(guò)文章的同學(xué)的數(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"
)

可以看到，子查詢的邏輯非常清晰：通過(guò) 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í)行計(jì)劃，發(fā)現(xiàn)都是一樣的

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

總結(jié)

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

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

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

最新評(píng)論