表設計的 18 條軍規

前言

對於後端開發同學來說，訪問數據庫，是代碼中必不可少的一個環節。

系統中收集到用戶的核心數據，爲了安全性，我們一般會存儲到數據庫，比如：mysql，oracle 等。

後端開發的日常工作，需要不斷的建庫和建表，來滿足業務需求。

通常情況下，建庫的頻率比建表要低很多，所以，我們這篇文章主要討論建表相關的內容。

如果我們在建表的時候不注意細節，等後面系統上線之後，表的維護成本變得非常高，而且很容易踩坑。

今天就跟大家一起聊聊，數據庫建表的 18 個小技巧。

文章中介紹的很多細節，我在工作中踩過坑，並且實踐過的，非常有借鑑意義，希望對你會有所幫助。

1. 名字

建表的時候，給表、字段和索引起個好名字，真的太重要了。

1.1 見名知意

名字就像表、字段和索引的一張臉，可以給人留下第一印象。

好的名字，言簡意賅，見名知意，讓人心情愉悅，能夠提高溝通和維護成本。

壞的名字，模擬兩可，不知所云。而且顯得雜亂無章，看得讓人抓狂。

反例：

用戶名稱字段定義成：yong_hu_ming、用戶_name、name、user_name_123456789

你看了可能會一臉懵逼，這是什麼騷操作？

正例：

用戶名稱字段定義成：user_name

溫馨提醒一下，名字也不宜過長，儘量控制在30個字符以內。

1.2 大小寫

名字儘量都用小寫字母，因爲從視覺上，小寫字母更容易讓人讀懂。

反例：

字段名：PRODUCT_NAME、PRODUCT_name

全部大寫，看起來有點不太直觀。而一部分大寫，一部分小寫，讓人看着更不爽。

正例：

字段名：product_name

名字還是使用全小寫字母，看着更舒服。

1.3 分隔符

很多時候，名字爲了讓人好理解，有可能會包含多個單詞。

那麼，多個單詞間的分隔符該用什麼呢？

反例：

字段名：productname、productName、product name、product@name

單詞間沒有分隔，或者單詞間用駝峯標識，或者單詞間用空格分隔，或者單詞間用 @分隔，這幾種方式都不太建議。

正例：

字段名：product_name

強烈建議大家在單詞間用_分隔。

1.4 表名

對於表名，在言簡意賅，見名知意的基礎之上，建議帶上業務前綴。

如果是訂單相關的業務表，可以在表名前面加個前綴：order_。

例如：order_pay、order_pay_detail 等。

如果是商品相關的業務表，可以在表名前面加個前綴：product_。

例如：product_spu，product_sku 等。

這樣做的好處是爲了方便歸類，把相同業務的表，可以非常快速的聚集到一起。

另外，還有有個好處是，如果哪天有非訂單的業務，比如：金融業務，也需要建一個名字叫做 pay 的表，可以取名：finance_pay，就能非常輕鬆的區分。

這樣就不會出現同名表的情況。

1.5 字段名稱

字段名稱是開發人員發揮空間最大，但也最容易發生混亂的地方。

比如有些表，使用 flag 表示狀態，另外的表用 status 表示狀態。

可以統一一下，使用 status 表示狀態。

如果一個表使用了另一個表的主鍵，可以在另一張表的名後面，加_id或_sys_no，例如：

在 product_sku 表中有個字段，是 product_spu 表的主鍵，這時候可以取名：product_spu_id 或 product_spu_sys_no。

還有創建時間，可以統一成：create_time，修改時間統一成：update_time。

刪除狀態固定爲：delete_status。

其實還有很多公共字段，在不同的表之間，可以使用全局統一的命名規則，定義成相同的名稱，以便於大家好理解。

1.6 索引名

在數據庫中，索引有很多種，包括：主鍵、普通索引、唯一索引、聯合索引等。

每張表的主鍵只有一個，一般使用：id或者sys_no命名。

普通索引和聯合索引，其實是一類。在建立該類索引時，可以加ix_前綴，比如：ix_product_status。

唯一索引，可以加ux_前綴，比如：ux_product_code。

2. 字段類型

在設計表時，我們在選擇字段類型時，可發揮空間很大。

時間格式的數據有：date、datetime 和 timestamp 等等可以選擇。

字符類型的數據有：varchar、char、text 等可以選擇。

數字類型的數據有：int、bigint、smallint、tinyint 等可以選擇。

說實話，選擇很多，有時候是一件好事，也可能是一件壞事。

如何選擇一個合適的字段類型，變成了我們不得不面對的問題。

如果字段類型選大了，比如：原本只有 1-10 之間的 10 個數字，結果選了bigint，它佔8個字節。

其實，1-10 之間的 10 個數字，每個數字1個字節就能保存，選擇tinyint更爲合適。

這樣會白白浪費 7 個字節的空間。

如果字段類型擇小了，比如：一個 18 位的 id 字段，選擇了int類型，最終數據會保存失敗。

所以選擇一個合適的字段類型，還是非常重要的一件事情。

以下原則可以參考一下：

1. 儘可能選擇佔用存儲空間小的字段類型，在滿足正常業務需求的情況下，從小到大，往上選。

2. 如果字符串長度固定，或者差別不大，可以選擇 char 類型。如果字符串長度差別較大，可以選擇 varchar 類型。

3. 是否字段，可以選擇 bit 類型。

4. 枚舉字段，可以選擇 tinyint 類型。

5. 主鍵字段，可以選擇 bigint 類型。

6. 金額字段，可以選擇 decimal 類型。

7. 時間字段，可以選擇 timestamp 或 datetime 類型。

8. 字段長度

前面我們已經定義好了字段名稱，選擇了合適的字段類型，接下來，需要重點關注的是字段長度了。

比如：varchar(20)，biginit(20) 等。

那麼問題來了，varchar代表的是字節長度，還是字符長度呢？

答：在 mysql 中除了varchar和char是代表字符長度之外，其餘的類型都是代表字節長度。

biginit(n) 這個n表示什麼意思呢？

假如我們定義的字段類型和長度是：bigint(4)，bigint 實際長度是8個字節。

現在有個數據 a=1，a 顯示 4 個字節，所以在不滿 4 個字節時前面填充 0（前提是該字段設置了 zerofill 屬性），比如：0001。

當滿了 4 個字節時，比如現在數據是 a=123456，它會按照實際的長度顯示，比如：123456。

但需要注意的是，有些 mysql 客戶端即使滿了 4 個字節，也可能只顯示 4 個字節的內容，比如會顯示成：1234。

所以 bigint(4)，這裏的 4 表示顯示的長度爲 4 個字節，實際長度還是佔 8 個字節。

4. 字段個數

我們在建表的時候，一定要對字段個數做一些限制。

我之前見過有人創建的表，有幾十個，甚至上百個字段，表中保存的數據非常大，查詢效率很低。

如果真有這種情況，可以將一張大表拆成多張小表，這幾張表的主鍵相同。

建議每表的字段個數，不要超過20個。

5. 主鍵

在創建表時，一定要創建主鍵。

因爲主鍵自帶了主鍵索引，相比於其他索引，主鍵索引的查詢效率最高，因爲它不需要回表。

此外，主鍵還是天然的唯一索引，可以根據它來判重。

在單個數據庫中，主鍵可以通過AUTO_INCREMENT，設置成自動增長的。

但在分佈式數據庫中，特別是做了分庫分表的業務庫中，主鍵最好由外部算法 (比如：雪花算法）生成，它能夠保證生成的 id 是全局唯一的。

除此之外，主鍵建議保存跟業務無關的值，減少業務耦合性，方便今後的擴展。

不過我也見過，有些一對一的表關係，比如：用戶表和用戶擴展表，在保存數據時是一對一的關係。

這樣，用戶擴展表的主鍵，可以直接保存用戶表的主鍵。

6. 存儲引擎

在mysql8以前的版本，默認的存儲引擎是myslam，而mysql8以後的版本，默認的存儲引擎變成了innodb。

之前我們還在創建表時，還一直糾結要選哪種存儲引擎？

myslam的索引和數據分開存儲，而有利於查詢，但它不支持事務和外鍵等功能。

而innodb雖說查詢性能，稍微弱一點，但它支持事務和外鍵等，功能更強大一些。

以前的建議是：讀多寫少的表，用 myslam 存儲引擎。而寫多讀多的表，用 innodb。

但雖說 mysql 對 innodb 存儲引擎性能的不斷優化，現在 myslam 和 innodb 查詢性能相差已經越來越小。

所以，建議我們在使用mysql8以後的版本時，直接使用默認的innodb存儲引擎即可，無需額外修改存儲引擎。

7. NOT NULL

在創建字段時，需要選擇該字段是否允許爲NULL。

我們在定義字段時，應該儘可能明確該字段NOT NULL。

爲什麼呢？

我們主要以 innodb 存儲引擎爲例，myslam 存儲引擎沒啥好說的。

主要有以下原因：

1. 在 innodb 中，需要額外的空間存儲 null 值，需要佔用更多的空間。

2. null 值可能會導致索引失效。

3. null 值只能用is null或者is not null判斷，用=號判斷永遠返回 false。

因此，建議我們在定義字段時，能定義成 NOT NULL，就定義成 NOT NULL。

但如果某個字段直接定義成 NOT NULL，萬一有些地方忘了給該字段寫值，就會insert不了數據。

這也算合理的情況。

但有一種情況是，系統有新功能上線，新增了字段。上線時一般會先執行 sql 腳本，再部署代碼。

由於老代碼中，不會給新字段賦值，則 insert 數據時，也會報錯。

由此，非常有必要給 NOT NULL 的字段設置默認值，特別是後面新增的字段。

例如：

alter table product_sku add column  brand_id int(10) not null default 0;

8. 外鍵

在 mysql 中，是存在外鍵的。

外鍵存在的主要作用是：保證數據的一致性和完整性。

例如：

create table class (
  id int(10) primary key auto_increment,
  cname varchar(15)
);

有個班級表 class。

然後有個 student 表：

create table student(
  id int(10) primary key auto_increment,
  name varchar(15) not null,
  gender varchar(10) not null,
  cid int,
  foreign key(cid) references class(id)
);

其中 student 表中的 cid 字段，保存的 class 表的 id，這時通過foreign key增加了一個外鍵。

這時，如果你直接通過 student 表的 id 刪除數據，會報異常：

a foreign key constraint fails

必須要先刪除 class 表對於的 cid 那條數據，再刪除 student 表的數據纔行，這樣能夠保證數據的一致性和完整性。

順便說一句：只有存儲引擎是 innodb 時，才能使用外鍵。

如果只有兩張表的關聯還好，但如果有十幾張表都建了外鍵關聯，每刪除一次主表，都需要同步刪除十幾張子表，很顯然性能會非常差。

因此，互聯網系統中，一般建議不使用外鍵。因爲這類系統更多的是爲了性能考慮，寧可犧牲一點數據一致性和完整性。

除了外鍵之外，存儲過程和觸發器也不太建議使用，他們都會影響性能。

9. 索引

在建表時，除了指定主鍵索引之外，還需要創建一些普通索引。

例如：

create table product_sku(
  id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null
);

在創建商品表時，使用 spu_id（商品組表）和 brand_id（品牌表）的 id。

像這類保存其他表 id 的情況，可以增加普通索引：

create table product_sku (
  id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

後面查表的時候，效率更高。

但索引字段也不能建的太多，可能會影響保存數據的效率，因爲索引需要額外的存儲空間。

建議單表的索引個數不要超過：5個。

如果在建表時，發現索引個數超過 5 個了，可以刪除部分普通索引，改成聯合索引。

順便說一句：在創建聯合索引的時候，需要使用注意最左匹配原則，不然，建的聯合索引效率可能不高。

對於數據重複率非常高的字段，比如：狀態，不建議單獨創建普通索引。因爲即使加了索引，如果 mysql 發現全表掃描效率更高，可能會導致索引失效。

10. 時間字段

時間字段的類型，我們可以選擇的範圍還是比較多的，目前 mysql 支持：date、datetime、timestamp、varchar 等。

varchar類型可能是爲了跟接口保持一致，接口中的時間類型是 String。

但如果哪天我們要通過時間範圍查詢數據，效率會非常低，因爲這種情況沒法走索引。

date類型主要是爲了保存日期，比如：2020-08-20，不適合保存日期和時間，比如：2020-08-20 12:12:20。

而datetime和timestamp類型更適合我們保存日期和時間。

但它們有略微區別。

• timestamp：用 4 個字節來保存數據，它的取值範圍爲1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外，它還跟時區有關。

• datetime：用 8 個字節來保存數據，它的取值範圍爲1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟時區無關。

優先推薦使用datetime類型保存日期和時間，可以保存的時間範圍更大一些。

溫馨提醒一下，在給時間字段設置默認值是，建議不要設置成：0000-00-00 00:00:00，不然查詢表時可能會因爲轉換不了，而直接報錯。

11. 金額字段

mysql 中有多個字段可以表示浮點數：float、double、decimal 等。

而float和double可能會丟失精度，因此推薦大家使用decimal類型保存金額。

一般我們是這樣定義浮點數的：decimal(m,n)。

其中n是指小數的長度，而m是指整數加小數的總長度。

假如我們定義的金額類型是這樣的：decimal(10,2)，則表示整數長度是 8 位，並且保留 2 位小數。

12. json 字段

我們在設計表結構時，經常會遇到某個字段保存的數據值不固定的需求。

舉個例子，比如：做異步 excel 導出功能時，需要在異步任務表中加一個字段，保存用戶通過前端頁面選擇的查詢條件，每個用戶的查詢條件可能都不一樣。

這種業務場景，使用傳統的數據庫字段，不太好實現。

這時候就可以使用 MySQL 的 json 字段類型了，可以保存 json 格式的結構化數據。

保存和查詢數據都是非常方便的。

MySQL 還支持按字段名稱或者字段值，查詢 json 中的數據。

13. 唯一索引

唯一索引在我們實際工作中，使用頻率相當高。

你可以給單個字段，加唯一索引，比如：組織機構 code。

也可以給多個字段，加一個聯合的唯一索引，比如：分類編號、單位、規格等。

單個的唯一索引還好，但如果是聯合的唯一索引，字段值出現 null 時，則唯一性約束可能會失效。

創建唯一索引時，相關字段一定不能包含 null 值，否則唯一性會失效。

14. 字符集

mysql 中支持的字符集有很多，常用的有：latin1、utf-8、utf8mb4、GBK 等。

這 4 種字符集情況如下：

latin1容易出現亂碼問題，在實際項目中使用比較少。

而GBK支持中文，但不支持國際通用字符，在實際項目中使用也不多。

從目前來看，mysql 的字符集使用最多的還是：utf-8和utf8mb4。

其中utf-8佔用 3 個字節，比utf8mb4的 4 個字節，佔用更小的存儲空間。

但 utf-8 有個問題：即無法存儲 emoji 表情，因爲 emoji 表情一般需要 4 個字節。

由此，使用 utf-8 字符集，保存 emoji 表情時，數據庫會直接報錯。

所以，建議在建表時字符集設置成：utf8mb4，會省去很多不必要的麻煩。

15. 排序規則

不知道，你關注過沒，在 mysql 中創建表時，有個COLLATE參數可以設置。

例如：

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

它是用來設置排序規則的。

字符排序規則跟字符集有關，比如：字符集如果是utf8mb4，則字符排序規則也是以：utf8mb4_開頭的，常用的有：utf8mb4_general_ci、utf8mb4_bin等。

其中 utf8mb4_general_ci 排序規則，對字母的大小寫不敏感。說得更直白一點，就是不區分大小寫。

而 utf8mb4_bin 排序規則，對字符大小寫敏感，也就是區分大小寫。

說實話，這一點還是非常重要的。

假如 order 表中現在有一條記錄，name 的值是大寫的 YOYO，但我們用小寫的 yoyo 去查，例如：

select * from order where name='yoyo';

如果字符排序規則是 utf8mb4_general_ci，則可以查出大寫的 YOYO 的那條數據。

如果字符排序規則是 utf8mb4_bin，則查不出來。

由此，字符排序規則一定要根據實際的業務場景選擇，否則容易出現問題。

16. 大字段

我們在創建表時，對一些特殊字段，要額外關注，比如：大字段，即佔用較多存儲空間的字段。

比如：用戶的評論，這就屬於一個大字段，但這個字段可長可短。

但一般會對評論的總長度做限制，比如：最多允許輸入 500 個字符。

如果直接定義成text類型，可能會浪費存儲空間，所以建議將這類字段定義成varchar類型的存儲效率更高。

當然，我還見過更大的字段，即該字段直接保存合同數據。

一個合同可能會佔幾Mb。

在 mysql 中保存這種數據，從系統設計的角度來說，本身就不太合理。

像合同這種非常大的數據，可以保存到mongodb中，然後在 mysql 的業務表中，保存 mongodb 表的 id。

17. 冗餘字段

我們在設計表的時候，爲了性能考慮，提升查詢速度，有時可以冗餘一些字段。

舉個例子，比如：訂單表中一般會有 userId 字段，用來記錄用戶的唯一標識。

但很多訂單的查詢頁面，或者訂單的明細頁面，除了需要顯示訂單信息之外，還需要顯示用戶 ID 和用戶名稱。

如果訂單表和用戶表的數據量不多，我們可以直接用 userId，將這兩張表 join 起來，查詢出用戶名稱。

但如果訂單表和用戶表的數據量都非常多，這樣 join 是比較消耗查詢性能的。

這時候我們可以通過冗餘字段的方案，來解決性能問題。

我們可以在訂單表中，可以再加一個 userName 字段，在系統創建訂單時，將 userId 和 userName 同時寫值。

當然訂單表中歷史數據的 userName 是空的，可以刷一下歷史數據。

這樣調整之後，後面只需要查詢訂單表，即可查詢出我們所需要的數據。

不過冗餘字段的方案，有利也有弊。

對查詢性能有利。

但需要額外的存儲空間，還可能會有數據不一致的情況，比如用戶名稱修改了。

我們在實際業務場景中，需要綜合評估，冗餘字段方案不適用於所有業務場景。

18. 註釋

我們在做表設計的時候，一定要把表和相關字段的註釋加好。

例如下面這樣的：

CREATE TABLE `sys_dept` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(30) NOT NULL COMMENT '名稱',
  `pid` bigint NOT NULL COMMENT '上級部門',
  `valid_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '有效狀態 1:有效 0:無效',
  `create_user_id` bigint NOT NULL COMMENT '創建人ID',
  `create_user_name` varchar(30) NOT NULL COMMENT '創建人名稱',
  `create_time` datetime(3) DEFAULT NULL COMMENT '創建日期',
  `update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
  `update_user_name` varchar(30)  DEFAULT NULL COMMENT '修改人名稱',
  `update_time` datetime(3) DEFAULT NULL COMMENT '修改時間',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '是否刪除 1：已刪除 0：未刪除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='部門';

表和字段的註釋，都列舉的非常詳細。

特別是有些狀態類型的字段，比如：valid_status 字段，該字段表示有效狀態， 1: 有效 0: 無效。

讓人可以一目瞭然，表和字段是幹什麼用的，字段的值可能有哪些。

最怕的情況是，你在表中創建了很多 status 字段，每個字段都有 1、2、3、4、5、6、7、8、9 等多個值。

沒有寫什麼註釋。

誰都不知道 1 代表什麼含義，2 代表什麼含義，3 代表什麼含義。

可能剛開始你還記得。

但系統上線使用一年半載之後，可能連你自己也忘記了這些 status 字段，每個值的具體含義了，埋下了一個巨坑。

由此，我們在做表設計時，一定要寫好相關的註釋，並且經常需要更新這些註釋。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/gVXX1P9mU4aO0SONqaq88Q