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 userWHERE 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 blogON 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 對子查詢有了優化，可以放心使用。到此這篇關于MySQL 那些常見的錯誤設計規范的文章就介紹到這了,更多相關MySQL 錯誤設計規范內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
網公網安備