后端程序員必備：15個MySQL表設(shè)計的經(jīng)驗準(zhǔn)則

作者：撿田螺的小男孩 2024-10-07 11:11:26

我們設(shè)計表的時候，一般都需要加通用時間的字段，如create_time、modified_time等等。那對于時間的類型，我們該如何選擇呢？

前言

大家好，我是田螺。

昨天星球粉絲，咨詢了一個并發(fā)的問題~ 我提供了一個樂觀鎖兜底的方案，然后發(fā)現(xiàn)他們的表，都沒有加version字段的,我想到，這不是表設(shè)計通用字段嘛。因此，本文跟大家聊聊，設(shè)計表的時候，有哪些經(jīng)驗準(zhǔn)則。

1. 設(shè)計表時，盡量都有這幾個通用字段

表必備一般來說，或具備這幾個字段：

id：主鍵，一個表必須得有主鍵，必須
create_time：創(chuàng)建時間，必須
modifed_time: 修改時間，必須，更新記錄時，就更新它。
version : 數(shù)據(jù)記錄的版本號，一般用于樂觀鎖，非必須
modifier :修改人，非必須
creator ：創(chuàng)建人，非必須

2. 每個字段都要有注釋，尤其涉及枚舉這些時

我們在設(shè)計表的時候，每個字段，都要寫上注釋哈，尤其涉及到一個枚舉字段的時候，更要把每個枚舉值寫出來，后面如果有變更，也要維護到這里來~

反例：

CREATE TABLE order_tab (  
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE, 
    user_id BIGINT NOT NULL,  
    total_amount DECIMAL(10, 2) NOT NULL, 
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',   
    payment_status VARCHAR(20) DEFAULT 'not_paid',  
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)  
);

正例：

CREATE TABLE order_tab (  
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '訂單項的唯一標(biāo)識符，自增主鍵',  
    order_id BIGINT UNIQUE COMMENT '訂單的唯一標(biāo)識符，在整個系統(tǒng)中唯一',  
    user_id BIGINT NOT NULL COMMENT '用戶的唯一標(biāo)識符，關(guān)聯(lián)到用戶表',  
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '訂單的總金額，精確到小數(shù)點后兩位',  
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '訂單的狀態(tài)，例如：PENDING（待處理）、COMPLETED（已完成）等',  
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT '訂單的支付狀態(tài)，如：not_paid（未支付）、paid（已支付）等',  
    version INT DEFAULT 0 COMMENT '樂觀鎖版本號，用于并發(fā)控制',  
    created_time DATETIME COMMENT '訂單的創(chuàng)建時間',  
    updated_time DATETIME COMMENT '訂單的最后一次更新時間',  
    creator VARCHAR(255) COMMENT '訂單的創(chuàng)建者，通常記錄創(chuàng)建訂單的用戶或系統(tǒng)的用戶名',  
    modifier VARCHAR(255) COMMENT '訂單的修改者，通常記錄最后修改訂單的用戶或系統(tǒng)的用戶名'  
);

3. 命名規(guī)范

數(shù)據(jù)庫表名、字段名、索引名等都需要命名規(guī)范，可讀性高(一般要求用英文)，讓別人一看命名，就知道這個字段表示什么意思。

比如一個表的賬號字段，反例如下：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number

表名、字段名必須使用小寫字母或者數(shù)字，禁止使用數(shù)字開頭，禁止使用拼音，并且一般不使用英文縮寫。
主鍵索引名為pk_字段名；唯一索引名為uk_字段名；普通索引名則為idx_字段名。

4. 選擇合適的字段類型

設(shè)計表時，我們需要選擇合適的字段類型，比如：

盡可能選擇存儲空間小的字段類型，就好像數(shù)字類型的，從tinyint、smallint、int、bigint從左往右開始選擇
小數(shù)類型如金額，則選擇 decimal，禁止使用 float 和 double。
如果存儲的字符串長度幾乎相等，使用 char 定長字符串類型。
varchar是可變長字符串，不預(yù)先分配存儲空間，長度不要超過5000。
如果存儲的值太大，建議字段類型修改為text，同時抽出單獨一張表，用主鍵與之對應(yīng)。
同一表中，所有varchar字段的長度加起來，不能大于65535. 如果有這樣的需求，請使用TEXT/LONGTEXT 類型。

5. 主鍵設(shè)計要合理

主鍵設(shè)計的話，最好不要與業(yè)務(wù)邏輯有所關(guān)聯(lián)。有些業(yè)務(wù)上的字段，比如身份證，雖然是唯一的，一些開發(fā)者喜歡用它來做主鍵，但是不是很建議哈。主鍵最好是毫無意義的一串獨立不重復(fù)的數(shù)字，比如UUID，又或者Auto_increment自增的主鍵，或者是雪花算法生成的主鍵等等;

6.選擇合適的字段長度

先問大家一個問題，大家知道數(shù)據(jù)庫字段長度表示字符長度還是字節(jié)長度嘛？

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

我們在設(shè)計表的時候，需要充分考慮一個字段的長度，比如一個用戶名字段（它的長度5~20個字符），你覺得應(yīng)該設(shè)置多長呢？可以考慮設(shè)置為 username varchar（32）。字段長度一般設(shè)置為2的冪哈（也就是2的n次方）。

7. 優(yōu)先考慮邏輯刪除，而不是物理刪除

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

物理刪除：把數(shù)據(jù)從硬盤中刪除，可釋放存儲空間
邏輯刪除：給數(shù)據(jù)添加一個字段，比如is_deleted，以標(biāo)記該數(shù)據(jù)已經(jīng)邏輯刪除。

物理刪除就是執(zhí)行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';

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

為什么不推薦使用物理刪除，因為恢復(fù)數(shù)據(jù)很困難
物理刪除會使自增主鍵不再連續(xù)
核心業(yè)務(wù)表的數(shù)據(jù)不建議做物理刪除，只適合做狀態(tài)變更。

8. 一張表的字段不宜過多

我們建表的時候，要牢記，一張表的字段不宜過多哈，一般盡量不要超過20個字段哈。筆者記得上個公司，有伙伴設(shè)計開戶表，加了五十多個字段。。。

如果一張表的字段過多，表中保存的數(shù)據(jù)可能就會很大，查詢效率就會很低。因此，一張表不要設(shè)計太多字段哈，如果業(yè)務(wù)需求，實在需要很多字段，可以把一張大的表，拆成多張小的表，它們的主鍵相同即可。

當(dāng)表的字段數(shù)非常多時，可以將表分成兩張表，一張作為條件查詢表，一張作為詳細(xì)內(nèi)容表 (主要是為了性能考慮)。

9. 盡可能使用not null定義字段

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

為什么呢？

首先， NOT NULL 可以防止出現(xiàn)空指針問題。
其次，NULL值存儲也需要額外的空間的，它也會導(dǎo)致比較運算更為復(fù)雜，使優(yōu)化器難以優(yōu)化SQL。
NULL值有可能會導(dǎo)致索引失效
如果將字段默認(rèn)設(shè)置成一個空字符串或常量值并沒有什么不同，且都不會影響到應(yīng)用邏輯，那就可以將這個字段設(shè)置為NOT NULL。

10. 設(shè)計表時，評估哪些字段需要加索引

首先，評估你的表數(shù)據(jù)量。如果你的表數(shù)據(jù)量只有一百幾十行，就沒有必要加索引。否則設(shè)計表的時候，如果有查詢條件的字段，一般就需要建立索引。但是索引也不能濫用：

索引也不要建得太多，一般單表索引個數(shù)不要超過5個。因為創(chuàng)建過多的索引，會降低寫得速度。
區(qū)分度不高的字段，不能加索引，如性別等
索引創(chuàng)建完后，還是要注意避免索引失效的情況，如使用mysql的內(nèi)置函數(shù)，會導(dǎo)致索引失效的
索引過多的話，可以通過聯(lián)合索引的話方式來優(yōu)化。然后的話，索引還有一些規(guī)則，如覆蓋索引，最左匹配原則等等。。

假設(shè)你新建一張用戶表，如下：

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;

對于這張表，很可能會有根據(jù)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;

11. 避免使用MySQL保留字

如果庫名、表名、字段名等屬性含有保留字時，SQL語句必須用反引號來引用屬性名稱，這將使得SQL語句書寫、SHELL腳本中變量的轉(zhuǎn)義等變得非常復(fù)雜。

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

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

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

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

我們來復(fù)習(xí)一下這MySQL這三種存儲引擎的對比區(qū)別吧：

13. 選擇合適統(tǒng)一的字符集

數(shù)據(jù)庫庫、表、開發(fā)程序等都需要統(tǒng)一字符集，通常中英文環(huán)境用utf8。

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

utf8：支持中英文混合場景，國際通過，3個字節(jié)長度
utf8mb4: 完全兼容utf8，4個字節(jié)長度，一般存儲emoji表情需要用到它。
GBK ：支持中文，但是不支持國際通用字符集，2個字節(jié)長度
latin1：MySQL默認(rèn)字符集，1個字節(jié)長度

14. 時間的類型選擇