21個MySQL表設計的經驗準則

語言: CN / TW / HK

前言

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

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

  • 公眾號:撿田螺的小男孩

1.命名規範

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

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

acc_no,1_acc_no,zhanghao 正例: account_no,account_number

  • 表名、欄位名必須使用小寫字母或者數字,禁止使用數字開頭,禁止使用拼音,並且一般不使用英文縮寫。
  • 主鍵索引名為pk_欄位名;唯一索引名為uk_欄位名;普通索引名則為idx_欄位名

2.選擇合適的欄位型別

設計表時,我們需要選擇合適的欄位型別,比如: - 儘可能選擇儲存空間小的欄位型別,就好像數字型別的,從tinyint、smallint、int、bigint從左往右開始選擇 - 小數型別如金額,則選擇 decimal,禁止使用 floatdouble。 - 如果儲存的字串長度幾乎相等,使用 char 定長字串型別。 - varchar是可變長字串,不預先分配儲存空間,長度不要超過5000。 - 如果儲存的值太大,建議欄位型別修改為text,同時抽出單獨一張表,用主鍵與之對應。 - 同一表中,所有varchar欄位的長度加起來,不能大於65535. 如果有這樣的需求,請使用TEXT/LONGTEXT型別。

3. 主鍵設計要合理

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

4. 選擇合適的欄位長度

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

其實在mysql中,varcharchar型別表示字元長度,而其他型別表示的長度都表示位元組長度。比如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: 修改時間,必須,更新記錄時,需要更新它 - 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中,然後,在業務表儲存對應mongodbid即可。

這種設計思想類似於,我們表字段儲存圖片時,為什麼不是儲存圖片內容,而是直接儲存圖片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的計劃

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