淺談MySQL表結構設計

本文首發於個人微信公眾號《andyqian》,期待您的關注！

前言

有一小陣子沒有更新技術文章了，今天我們繼續MySQL系列，今天要說的是MySQL表結構設計。在我的工作經歷當中，就踩過很多這方面的坑，在之前的文章《MySQL表設計踩過的坑！》中，也談到了一些坑，但總有一種，只是指出了問題，卻沒有給出解決方案的感覺，今天我們就來講講MySQL表如何設計才能避免踩坑。

選擇合適的存儲引擎

在表結構設計中，首先我們就要選擇一個合適的存儲引擎，我們在之前的文章《談談MySQL存儲引擎》就簡述了每個存儲引擎的區別，在選擇存儲引擎時，我們應該根據自己的業務場景來選型存儲引擎，如：需要OLTP(在線事務處理)的應用，就應該選擇InnoDB存儲引擎，如果僅僅用作存儲數據，允許數據不一致，數據倉庫等場景時，可用MyISAM存儲引擎，如果你不知道如何選擇，那麼使用InnoDB存儲引擎就對了。

選擇合適的字符集

這裡之前也說過一次了，主要是為了保存emoji表情，例如: 微信昵稱，就有很多帶有emoji表情的，這裡我們使用utf8mb4字符集，千萬不要使用blob類型來存儲。

主/外鍵類型

眾所周知，主鍵的設定是非常重要的，在主鍵的選擇上，應該滿足以下幾個條件:

1. 唯一性 (必要條件)
2. 非空性
3. 有序性
4. 可讀性
5. 可擴展性

主鍵的唯一性和非空性，就不多說了，其中有序性就有不少好處。例如: 查詢時，為有序IO，就可提高查詢效率，存儲的順序也是有序的，往遠了看，分庫分表也是有好處的。因此，我建議使8位元組無符號的bigint(20)作為主鍵的數據類型

create table t_base_user(nid bigint(20) unsigned not null primary key auto_increment;n....n)n

這裡有幾個注意點:

1. 主外鍵的數據類型一定要一致！
2. 每個表中的主鍵命名保持一致！

(我就吃過這種細節上的虧)。

無符號與有符號的區別:

以下為各個int類型的範圍:

問題一: 為什麼不使用varchar類型作主鍵

答: varchar類型會對性能有影響，其中varchar也不是有序的，可讀性也不好，這裡不建議使用。

PS: 主鍵其實是一個比較大的話題，為什麼這麼說呢，其實上面的方案也是有缺點的，適用於主從架構，對於多主呢？此時的主鍵是不是就會有重複的情況產生了，這也就是為什麼一些大廠有自己的ID生成中間件的原因了，這裡不做深入討論，我們下次單獨寫一篇文章來說。

外鍵約束

外鍵約束用來保證數據完整性的，這一點我很贊同，但是我不建議在資料庫表中加外鍵約束，因為在數據表中添加外鍵約束，會影響性能，例如: 每一次修改數據時，都要在另外的一張表中執行查詢。我的建議是：在應用層，也就是代碼層面，來維持外鍵關係。

選擇合適的類型

這裡指的是數據列的數據類型，在選擇合適的數據類型時，我們應滿足以下條件:

1. 盡量選擇小，簡單的數據類型。
2. 保持可讀性。
3. 盡量避免Null

我們儘可能選擇小的數據類型，這樣會有很多好處，比如服務端處理效率，傳輸等都會快些。這裡給出幾個常見的數據類型設計：

• 狀態類型用 tinyint，例如 性別等。
• 時間日期使用datetime,timesamp類型，我比較喜歡使用datetime類型，(可讀性高些)。
• 盡量不要使用text和blob數據類型，特別是blob，

設計邏輯刪除

這個欄位主要用作業務上的邏輯刪除，在現在數據為金的時代，數據不建議做物理刪除。原因有以下幾點:

• 可恢復，物理刪除一旦刪除，即不可恢復。
• 邏輯刪除數據可用數據分析等。

例如:

deleted tinyint not null default 1;n

創建時間&修改時間

這些主要是用作業務上的欄位，每個表中都應該有，在查詢，以及問題查找定位時有諸多好處，例如： 查詢最近三個月的登錄用戶，這時就可用創建時間來查詢等等，

如：

created_time datetime not null default now();nnupdated_time datetime not null default now() comment n

添加合適的索引

索引說過很多次了，這裡就不再詳細說了，可參考之前的文章《寫會MySQL索引》，這裡需要強調的是：我建議在創建時間上都加上索引。

添加註釋

添加註釋，這是非常重要的，其中包括表注釋，欄位注釋。主要是為了後期表結構的維護，我相信你對著數據表中那麼多欄位，如果沒有注釋的話，你是很難一下子明白是什麼意思的，即使你是該表結構的設計者，十天半個月過去後，你還記得嗎？

簡單實例:

create table t_base_user(n id bigint(20) UNSIGNED not null primary key auto_increment comment "主鍵",n name varchar(50) character set utf8mb4 comment "",n created_time datetime not null default now() comment "創建時間",n updated_time datetime not null default now() comment "修改時間",n deleted tinyint not null default 1 comment "邏輯刪除 0正常數據 1刪除數據"n)engine=innodb charset=utf8 comment "用戶表";nn//添加索引nnalter table t_base_user idx_created_time(created_time);n

以上表結構僅作演示，實際表結構設計時，會更複雜些，但思路是一樣的。

小結:

下面精簡一下:

• 選擇合適的存儲引擎,一般使用InnoDB。
• 選擇合適的字符集，無emoji使用utf8，有emoji使用utf8mb4。
• 主鍵使用bigint(20)，主外鍵類型一致。
• 選擇合適的類型。
• 添加邏輯刪除，創建，修改時間。
• 添加表，欄位注釋
• 添加索引

以上是表結構設計時的一些注意事項，可以說是我在MySQL表結構設計的一點點積累吧，在這裡拋磚引玉了，有不同意見的小夥伴，都可在留言區留言，我們旨在設計好一個不給自己挖坑的表結構。

相關閱讀:

談談MySQL顯示類型轉換

十個實用MySQL命令

說說 MySQL JSON 數據類型

MySQL事務隔離級別