21個MySQL表設計的經驗準則

語言: CN / TW / HK

時間 2022-09-25 09:26:27 撿田螺的小男孩

主題: MySQL

前言

大家好，我是 撿田螺的小男孩 。

作為後端開發，我們經常需要設計資料庫表。整理了21個設計MySQL表的經驗準則，分享給大家，大家看完一定會有幫助的。

1.命名規範

資料庫表名、欄位名、索引名等都需要命名規範，可讀性高(一般要求用英文)，讓別人一看命名，就知道這個欄位表示什麼意思。

比如一個表的賬號欄位， 反例如下 ：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number

表名、欄位名必須使用小寫字母或者數字，禁止使用數字開頭，禁止使用拼音，並且一般不使用英文縮寫。

pk_欄位名
uk_欄位名
idx_欄位名

2.選擇合適的欄位型別

設計表時，我們需要選擇合適的欄位型別，比如：

儘可能選擇儲存空間小的欄位型別，就好像數字型別的，從 tinyint、smallint、int、bigint 從左往右開始選擇
```
decimal
float
double
```
如果儲存的字串長度幾乎相等，使用 char 定長字串型別。
varchar 是可變長字串，不預先分配儲存空間，長度不要超過 5000 。
如果儲存的值太大，建議欄位型別修改為 text ，同時抽出單獨一張表，用主鍵與之對應。
```
varchar
65535
TEXT/LONGTEXT 
```

3. 主鍵設計要合理

主鍵設計的話，最好不要與業務邏輯有所關聯。有些業務上的欄位，比如身份證，雖然是唯一的，一些開發者喜歡用它來做主鍵，但是不是很建議哈。主鍵最好是毫無意義的一串獨立不重複的數字，比如 UUID ，又或者 Auto_increment 自增的主鍵，或者是雪花演算法生成的主鍵等等;

4. 選擇合適的欄位長度

先問大家一個問題，大家知道資料庫欄位長度表示 字元長度 還是 位元組長度 嘛？

其實在mysql中， varchar 和 char 型別表示字元長度，而其他型別表示的長度都表示位元組長度。比如 char(10) 表示字元長度是10，而 bigint（4） 表示顯示長度是 4 個位元組，但是因為bigint實際長度是 8 個位元組，所以bigint（4）的實際長度就是8個位元組。

我們在設計表的時候，需要充分考慮一個欄位的長度，比如一個使用者名稱欄位（它的長度5~20個字元），你覺得應該設定多長呢？可以考慮設定為 username varchar（32） 。欄位長度一般設定為2的冪哈（也就是 2的n 次方）。’;

5，優先考慮邏輯刪除，而不是物理刪除

什麼是物理刪除？什麼是邏輯刪除？

物理刪除：把資料從硬碟中刪除，可釋放儲存空間
邏輯刪除：給資料新增一個欄位，比如 is_deleted ，以標記該資料已經邏輯刪除。

物理刪除就是執行 delete 語句，如刪除 account_no =‘666’ 的賬戶資訊SQL如下：

delete from account_info_tab whereaccount_no ='666';

邏輯刪除呢，就是這樣：

update account_info_tab set is_deleted = 1 where account_no ='666';

為什麼推薦用邏輯刪除，不推薦物理刪除呢？

為什麼不推薦使用物理刪除，因為恢復資料很困難
物理刪除會使自增主鍵不再連續
核心業務表的資料不建議做物理刪除，只適合做狀態變更。

6. 每個表都需要新增這幾個通用欄位如主鍵、create_time、modifed_time等

表必備一般來說，或具備這幾個欄位：

id：主鍵，一個表必須得有主鍵，必須
create_time：建立時間，必須
modifed_time/update_time: 修改時間，必須，更新記錄時，需要更新它
version : 資料記錄的版本號，用於樂觀鎖，非必須
remark ：資料記錄備註，非必須
modified_by :修改人，非必須
creator ：建立人，非必須

7. 一張表的欄位不宜過多

我們建表的時候，要牢記，一張表的欄位不宜過多哈，一般儘量不要超過20個欄位哈。筆者記得上個公司，有夥伴設計開戶表，加了五十多個欄位。。。

如果一張表的欄位過多，表中儲存的資料可能就會很大，查詢效率就會很低。因此，一張表不要設計太多欄位哈，如果業務需求，實在需要很多欄位，可以把一張大的表，拆成多張小的表，它們的主鍵相同即可。

當表的欄位數非常多時，可以將表分成兩張表，一張作為條件查詢表，一張作為詳細內容表 (主要是為了效能考慮)。

8. 儘可能使用not null定義欄位

如果沒有特殊的理由，一般都建議將欄位定義為 NOT NULL 。

為什麼呢？

首先， NOT NULL 可以防止出現空指標問題。
其次， NULL 值儲存也需要額外的空間的，它也會導致比較運算更為複雜，使優化器難以優化SQL。
NULL 值有可能會導致索引失效
如果將欄位預設設定成一個空字串或常量值並沒有什麼不同，且都不會影響到應用邏輯，那就可以將這個欄位設定為 NOT NULL 。

9. 設計表時，評估哪些欄位需要加索引

首先，評估你的表資料量。如果你的表資料量只有一百幾十行，就沒有必要加索引。否則設計表的時候，如果有查詢條件的欄位，一般就需要建立索引。但是索引也不能濫用：

索引也不要建得太多，一般單表索引個數不要超過 5 個。因為建立過多的索引，會降低寫得速度。
區分度不高的欄位，不能加索引，如性別等
索引建立完後，還是要注意避免索引失效的情況，如使用mysql的內建函式，會導致索引失效的
索引過多的話，可以通過聯合索引的話方式來優化。然後的話，索引還有一些規則，如覆蓋索引，最左匹配原則等等。。

假設你新建一張使用者表，如下：

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

對於這張表，很可能會有根據 user_id 或者 name 查詢使用者資訊，並且， user_id 是唯一的。因此，你是可以給 user_id 加上唯一索引， name 加上普通索引。

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10. 不需要嚴格遵守 3NF，通過業務欄位冗餘來減少表關聯

什麼是資料庫三正規化（ 3NF ），大家是否還有印象嗎？

第一正規化：對屬性的原子性，要求屬性具有原子性，不可再分解；
第二正規化：對記錄的唯一性，要求記錄有唯一標識，即實體的唯一性，即不存在部分依賴；
第三方式：對欄位的冗餘性，要求任何欄位不能由其他欄位派生出來，它要求欄位沒有冗餘，即不存在傳遞依賴；

我們設計表及其欄位之間的關係, 應儘量滿足第三正規化。但是有時候，可以適當冗餘，來提高效率。比如以下這張表

商品名稱	商品型號	單價	數量	總金額
手機	華為	8000	5	40000

以上這張存放商品資訊的基本表。 總金額 這個欄位的存在，表明該表的設計不滿足第三正規化，因為 總金額 可以由 單價*數量 得到，說明 總金額 是冗餘欄位。但是，增加 總金額 這個冗餘欄位，可以提高查詢統計的速度，這就是以空間換時間的作法。

當然，這只是個小例子哈，大家開發設計的時候，要結合具體業務分析哈。

11. 避免使用MySQL保留字

如果庫名、表名、欄位名等屬性含有保留字時， SQL 語句必須用反引號來引用屬性名稱，這將使得SQL語句書寫、SHELL指令碼中變數的轉義等變得非常複雜。

因此，我們一般避免使用 MySQL 保留字，如 select、interval、desc 等等

12. 不搞外來鍵關聯，一般都在程式碼維護

什麼是外來鍵呢？

外來鍵，也叫 FOREIGN KEY ，它是用於將兩個表連線在一起的鍵。 FOREIGN KEY 是一個表中的一個欄位（或欄位集合），它引用另一個表中的 PRIMARY KEY 。它是用來保證資料的一致性和完整性的。

阿里的 Java 規範也有這麼一條：

【強制】 不得使用外來鍵與級聯 ，一切外來鍵概念必須在應用層解決。

我們為什麼不推薦使用 外來鍵 呢？

使用外來鍵存在效能問題、併發死鎖問題、使用起來不方便等等。每次做 DELETE 或者 UPDATE 都必須考慮外來鍵約束，會導致開發的時候很難受,測試資料造資料也不方便。
還有一個場景不能使用外來鍵，就是分庫分表。

13. 一般都選擇INNODB儲存引擎

建表是需要選擇 儲存引擎 的，我們一般都選擇 INNODB 儲存引擎，除非讀寫比率小於 1% , 才考慮使用 MyISAM 。

有些小夥伴可能會有疑惑，不是還有 MEMORY 等其他儲存引擎嗎？什麼時候使用它呢？其實其他儲存引擎一般除了都建議在 DBA 的指導下使用。

我們來複習一下這 MySQL 這三種儲存引擎的對比區別吧：

特性	INNODB	MyISAM	MEMORY
事務安全	支援	無	無
儲存限制	64TB	有	有
空間使用	高	低	低
記憶體使用	高	低	高
插入資料速度	低	高	高
是否支援外來鍵	支援	無	無

14. 選擇合適統一的字符集。

資料庫庫、表、開發程式等都需要統一字符集，通常中英文環境用 utf8 。

MySQL支援的字符集有 utf8、utf8mb4、GBK、latin1 等。

utf8：支援中英文混合場景，國際通過，3個位元組長度
utf8mb4: 完全相容utf8，4個位元組長度，一般儲存 emoji表情 需要用到它。
GBK ：支援中文，但是不支援國際通用字符集，2個位元組長度
latin1：MySQL預設字符集，1個位元組長度

15. 如果你的資料庫欄位是列舉型別的，需要在comment註釋清楚

如果你設計的資料庫欄位是列舉型別的話，就需要在 comment 後面註釋清楚每個列舉的意思，以便於維護

正例如下：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授權態 00：線上-授權態有效 01：下線-授權態失效 02：下線-主動退出 03：下線-在別處被登入'

反例：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授權態'

並且，如果你的列舉型別在未來的版本有增加修改的話，也需要同時維護到 comment 後面。

16.時間的型別選擇

我們設計表的時候，一般都需要加通用時間的欄位，如 create_time、modified_time 等等。那對於時間的型別，我們該如何選擇呢？

對於MySQL來說，主要有 date、datetime、time、timestamp 和 year 。

date ：表示的日期值, 格式 yyyy-mm-dd ,範圍 1000-01-01 到 9999-12-31 ，3位元組
time ：表示的時間值，格式 hh:mm:ss ，範圍 -838:59:59 到 838:59:59 ，3位元組
datetime：表示的日期時間值，格式 yyyy-mm-dd hh:mm:ss ，範圍 1000-01-01 00:00:00到 9999-12-31 23:59:59```,8位元組，跟時區無關
timestamp：表示的時間戳值，格式為 yyyymmddhhmmss ，範圍 1970-01-01 00:00:01到2038-01-19 03:14:07 ，4位元組，跟時區有關
year：年份值，格式為 yyyy 。範圍 1901到2155 ，1位元組

推薦優先使用 datetime 型別來儲存日期和時間，因為儲存範圍更大，且跟時區無關。

17. 不建議使用Stored procedure (包括儲存過程，觸發器) 。

什麼是儲存過程

已預編譯為一個可執行過程的一個或多個SQL語句。

什麼是觸發器

觸發器，指一段程式碼，當觸發某個事件時，自動執行這些程式碼。使用場景：

可以通過資料庫中的相關表實現級聯更改。
實時監控某張表中的某個欄位的更改而需要做出相應的處理。
例如可以生成某些業務的編號。
注意不要濫用，否則會造成資料庫及應用程式的維護困難。

對於MYSQL來說，儲存過程、觸發器等還不是很成熟，並沒有完善的出錯記錄處理，不建議使用。

18. 1:N 關係的設計

日常開發中， 1 對多的關係應該是非常常見的。比如一個班級有多個學生，一個部門有多個員工等等。這種的建表原則就是：在從表（ N 的這一方）建立一個欄位，以欄位作為外來鍵指向主表（ 1 的這一方）的主鍵。示意圖如下:

學生表是多（ N ）的一方，會有個欄位 class_id 儲存班級表的主鍵。當然，一班不加外來鍵約束哈，只是單純儲存這個關係而已。

有時候兩張表存在 N:N 關係時，我們應該消除這種關係。通過增加第三張表，把 N:N 修改為兩個 1:N 。比如圖書和讀者，是一個典型的多對多的關係。一本書可以被多個讀者借，一個讀者又可以借多本書。我們就可以設計一個借書表，包含圖書表的主鍵，以及讀者的主鍵，以及借還標記等欄位。

19. 大欄位

設計表的時候，我們尤其需要關注一些大欄位，即佔用較多儲存空間的欄位。比如用來記錄使用者評論的欄位，又或者記錄部落格內容的欄位，又或者儲存合同資料的欄位。如果直接把表字段設計成text型別的話，就會浪費儲存空間，查詢效率也不好。

在MySQl中，這種方式儲存的設計方案，其實是不太合理的。這種非常大的資料，可以儲存到 mongodb 中，然後，在業務表儲存對應 mongodb 的 id 即可。

這種設計思想類似於，我們表字段儲存圖片時，為什麼不是儲存圖片內容，而是直接儲存圖片url即可。

20. 考慮是否需要分庫分表

什麼是分庫分表呢？

分庫：就是一個數據庫分成多個數據庫，部署到不同機器。

分表：就是一個數據庫表分成多個表。

我們在設計表的時候，其實可以提前估算一下，是否需要做 分庫分表 。比如一些使用者資訊，未來可能資料量到達百萬設定千萬的話，就可以提前考慮分庫分表。

為什麼需要分庫分表: 資料量太大的話，SQL的查詢就會變慢。如果一個查詢SQL沒命中索引，千百萬資料量級別的表可能會拖垮整個資料庫。即使SQL命中了索引，如果表的資料量超過一千萬的話，查詢也是會明顯變慢的。這是因為索引一般是B+樹結構，資料千萬級別的話，B+樹的高度會增高，查詢就變慢啦。

分庫分表主要有水平拆分、垂直拆分的說法，拆分策略有 range範圍、hash取模 。而分庫分表主要有這些問題：

事務問題
跨庫關聯
排序問題
分頁問題
分散式ID

大家可以看下之前我這篇文章哈：我們為什麼要分庫分表？

21. sqL 編寫的一些優化經驗

最後的話，跟大家聊來一些寫SQL的經驗吧：

查詢SQL儘量不要使用 select * ，而是 select 具體欄位
如果知道查詢結果只有一條或者只要最大/最小一條記錄，建議用 limit 1
應儘量避免在 where 子句中使用 or 來連線條件
注意優化 limit 深分頁問題
使用 where 條件限定要查詢的資料，避免返回多餘的行
儘量避免在索引列上使用 mysql 的內建函式
應儘量避免在 where 子句中對欄位進行表示式操作
```
where 
!=
<>
```
使用聯合索引時，注意索引列的順序，一般遵循最左匹配原則。
對查詢進行優化，應考慮在 where 及 order by 涉及的列上建立索引
如果插入資料過多，考慮批量插入
在適當的時候，使用覆蓋索引
使用explain 分析你SQL的計劃

大家可以看下我之前這篇文章哈：

後端程式設計師必備：書寫高質量SQL的30條建議

你在看嗎

「其他文章」