21個(gè)MySQL表設(shè)計(jì)的經(jīng)驗(yàn)準(zhǔn)則

前言

作為后端開(kāi)發(fā)，我們經(jīng)常需要設(shè)計(jì)數(shù)據(jù)庫(kù)表。整理了21個(gè)設(shè)計(jì)MySQL表的經(jīng)驗(yàn)準(zhǔn)則，分享給大家，大家看完一定會(huì)有幫助的。

1.命名規(guī)范

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

比如一個(gè)表的賬號(hào)字段，反例如下：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number

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

2.選擇合適的字段類型

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

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

3. 主鍵設(shè)計(jì)要合理

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

4. 選擇合適的字段長(zhǎng)度

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

其實(shí)在mysql中，varchar和char類型表示字符長(zhǎng)度，而其他類型表示的長(zhǎng)度都表示字節(jié)長(zhǎng)度。比如char(10)表示字符長(zhǎng)度是10，而bigint（4）表示顯示長(zhǎng)度是4個(gè)字節(jié)，但是因?yàn)閎igint實(shí)際長(zhǎng)度是8個(gè)字節(jié)，所以bigint（4）的實(shí)際長(zhǎng)度就是8個(gè)字節(jié)。

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

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

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

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

物理刪除就是執(zhí)行delete語(yǔ)句，如刪除account_no =‘666’的賬戶信息SQL如下：

deletefromaccount_info_tabwhereaccount_no='666';

邏輯刪除呢，就是這樣：

updateaccount_info_tabsetis_deleted=1whereaccount_no='666';

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

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

6. 每個(gè)表都需要添加這幾個(gè)通用字段如主鍵、create_time、modifed_time等

表必備一般來(lái)說(shuō)，或具備這幾個(gè)字段：

• id：主鍵，一個(gè)表必須得有主鍵，必須
• create_time：創(chuàng)建時(shí)間，必須
• modifed_time/update_time: 修改時(shí)間，必須，更新記錄時(shí)，需要更新它
• version : 數(shù)據(jù)記錄的版本號(hào)，用于樂(lè)觀鎖，非必須
• remark ：數(shù)據(jù)記錄備注，非必須
• modified_by :修改人，非必須
• creator ：創(chuàng)建人，非必須

7. 一張表的字段不宜過(guò)多

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

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

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

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

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

為什么呢？

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

9. 設(shè)計(jì)表時(shí)，評(píng)估哪些字段需要加索引

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

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

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

CREATETABLEuser_info_tab(
`id`int(11)NOTNULLAUTO_INCREMENT,
`user_id`int(11)NOTNULL,
`age`int(11)DEFAULTNULL,
`name`varchar(255)NOTNULL,
`create_time`datetimeNOTNULL,
`modifed_time`datetimeNOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

對(duì)于這張表，很可能會(huì)有根據(jù)user_id或者name查詢用戶信息，并且，user_id是唯一的。因此，你是可以給user_id加上唯一索引，name加上普通索引。

CREATETABLEuser_info_tab(
`id`int(11)NOTNULLAUTO_INCREMENT,
`user_id`int(11)NOTNULL,
`age`int(11)DEFAULTNULL,
`name`varchar(255)NOTNULL,
`create_time`datetimeNOTNULL,
`modifed_time`datetimeNOTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE,
UNIQUEKEYun_user_id(user_id)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

10. 不需要嚴(yán)格遵守 3NF，通過(guò)業(yè)務(wù)字段冗余來(lái)減少表關(guān)聯(lián)

什么是數(shù)據(jù)庫(kù)三范式（3NF），大家是否還有印象嗎？

• 第一范式：對(duì)屬性的原子性，要求屬性具有原子性，不可再分解；
• 第二范式：對(duì)記錄的唯一性，要求記錄有唯一標(biāo)識(shí)，即實(shí)體的唯一性，即不存在部分依賴；
• 第三方式：對(duì)字段的冗余性，要求任何字段不能由其他字段派生出來(lái)，它要求字段沒(méi)有冗余，即不存在傳遞依賴；

我們?cè)O(shè)計(jì)表及其字段之間的關(guān)系, 應(yīng)盡量滿足第三范式。但是有時(shí)候，可以適當(dāng)冗余，來(lái)提高效率。比如以下這張表

以上這張存放商品信息的基本表。總金額這個(gè)字段的存在，表明該表的設(shè)計(jì)不滿足第三范式，因?yàn)?code style="font-size:14px;padding:2px 4px;margin-right:2px;margin-left:2px;background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;color:rgb(239,112,96);">總金額可以由單價(jià)*數(shù)量得到，說(shuō)明總金額是冗余字段。但是，增加總金額這個(gè)冗余字段，可以提高查詢統(tǒng)計(jì)的速度，這就是以空間換時(shí)間的作法。

當(dāng)然，這只是個(gè)小例子哈，大家開(kāi)發(fā)設(shè)計(jì)的時(shí)候，要結(jié)合具體業(yè)務(wù)分析哈。

11. 避免使用MySQL保留字

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

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

12. 不搞外鍵關(guān)聯(lián)，一般都在代碼維護(hù)

什么是外鍵呢？

外鍵，也叫FOREIGN KEY，它是用于將兩個(gè)表連接在一起的鍵。FOREIGN KEY是一個(gè)表中的一個(gè)字段（或字段集合），它引用另一個(gè)表中的PRIMARY KEY。它是用來(lái)保證數(shù)據(jù)的一致性和完整性的。

阿里的Java規(guī)范也有這么一條：

【強(qiáng)制】不得使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

我們?yōu)槭裁床煌扑]使用外鍵呢？

• 使用外鍵存在性能問(wèn)題、并發(fā)死鎖問(wèn)題、使用起來(lái)不方便等等。每次做DELETE或者UPDATE都必須考慮外鍵約束，會(huì)導(dǎo)致開(kāi)發(fā)的時(shí)候很難受,測(cè)試數(shù)據(jù)造數(shù)據(jù)也不方便。
• 還有一個(gè)場(chǎng)景不能使用外鍵，就是分庫(kù)分表。

13. 一般都選擇INNODB存儲(chǔ)引擎

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

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

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

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

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

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

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

15. 如果你的數(shù)據(jù)庫(kù)字段是枚舉類型的，需要在comment注釋清楚

如果你設(shè)計(jì)的數(shù)據(jù)庫(kù)字段是枚舉類型的話，就需要在comment后面注釋清楚每個(gè)枚舉的意思，以便于維護(hù)

正例如下：

`session_status`varchar(2)COLLATEutf8_binNOTNULLCOMMENT'session授權(quán)態(tài)00：在線-授權(quán)態(tài)有效01：下線-授權(quán)態(tài)失效02：下線-主動(dòng)退出03：下線-在別處被登錄'

反例：

`session_status`varchar(2)COLLATEutf8_binNOTNULLCOMMENT'session授權(quán)態(tài)'

并且，如果你的枚舉類型在未來(lái)的版本有增加修改的話，也需要同時(shí)維護(hù)到comment后面。

16.時(shí)間的類型選擇

我們?cè)O(shè)計(jì)表的時(shí)候，一般都需要加通用時(shí)間的字段，如create_time、modified_time等等。那對(duì)于時(shí)間的類型，我們?cè)撊绾芜x擇呢？

對(duì)于MySQL來(lái)說(shuō)，主要有date、datetime、time、timestamp 和 year。

• date ：表示的日期值, 格式yyyy-mm-dd,范圍1000-01-01 到 9999-12-31，3字節(jié)
• time ：表示的時(shí)間值，格式 hhss，范圍-83859 到 83859，3字節(jié)
• datetime：表示的日期時(shí)間值，格式yyyy-mm-dd hhss，范圍1000-01-01 0000到9999-12-31 2359```,8字節(jié)，跟時(shí)區(qū)無(wú)關(guān)
• timestamp：表示的時(shí)間戳值，格式為yyyymmddhhmmss，范圍1970-01-01 0001到2038-01-19 0307，4字節(jié)，跟時(shí)區(qū)有關(guān)
• year：年份值，格式為yyyy。范圍1901到2155，1字節(jié)

推薦優(yōu)先使用datetime類型來(lái)保存日期和時(shí)間，因?yàn)榇鎯?chǔ)范圍更大，且跟時(shí)區(qū)無(wú)關(guān)。

17. 不建議使用Stored procedure (包括存儲(chǔ)過(guò)程，觸發(fā)器) 。

什么是存儲(chǔ)過(guò)程

已預(yù)編譯為一個(gè)可執(zhí)行過(guò)程的一個(gè)或多個(gè)SQL語(yǔ)句。

什么是觸發(fā)器

觸發(fā)器，指一段代碼，當(dāng)觸發(fā)某個(gè)事件時(shí)，自動(dòng)執(zhí)行這些代碼。使用場(chǎng)景：

• 可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。
• 實(shí)時(shí)監(jiān)控某張表中的某個(gè)字段的更改而需要做出相應(yīng)的處理。
• 例如可以生成某些業(yè)務(wù)的編號(hào)。
• 注意不要濫用，否則會(huì)造成數(shù)據(jù)庫(kù)及應(yīng)用程序的維護(hù)困難。

對(duì)于MYSQL來(lái)說(shuō)，存儲(chǔ)過(guò)程、觸發(fā)器等還不是很成熟， 并沒(méi)有完善的出錯(cuò)記錄處理，不建議使用。

18. 1:N 關(guān)系的設(shè)計(jì)

日常開(kāi)發(fā)中，1對(duì)多的關(guān)系應(yīng)該是非常常見(jiàn)的。比如一個(gè)班級(jí)有多個(gè)學(xué)生，一個(gè)部門有多個(gè)員工等等。這種的建表原則就是：在從表（N的這一方）創(chuàng)建一個(gè)字段，以字段作為外鍵指向主表（1的這一方）的主鍵。示意圖如下:

學(xué)生表是多（N）的一方，會(huì)有個(gè)字段class_id保存班級(jí)表的主鍵。當(dāng)然，一班不加外鍵約束哈，只是單純保存這個(gè)關(guān)系而已。

有時(shí)候兩張表存在N:N關(guān)系時(shí)，我們應(yīng)該消除這種關(guān)系。通過(guò)增加第三張表，把N:N修改為兩個(gè) 1:N。比如圖書和讀者，是一個(gè)典型的多對(duì)多的關(guān)系。一本書可以被多個(gè)讀者借，一個(gè)讀者又可以借多本書。我們就可以設(shè)計(jì)一個(gè)借書表，包含圖書表的主鍵，以及讀者的主鍵，以及借還標(biāo)記等字段。

19. 大字段

設(shè)計(jì)表的時(shí)候，我們尤其需要關(guān)注一些大字段，即占用較多存儲(chǔ)空間的字段。比如用來(lái)記錄用戶評(píng)論的字段，又或者記錄博客內(nèi)容的字段，又或者保存合同數(shù)據(jù)的字段。如果直接把表字段設(shè)計(jì)成text類型的話，就會(huì)浪費(fèi)存儲(chǔ)空間，查詢效率也不好。

在MySQl中，這種方式保存的設(shè)計(jì)方案，其實(shí)是不太合理的。這種非常大的數(shù)據(jù)，可以保存到mongodb中，然后，在業(yè)務(wù)表保存對(duì)應(yīng)mongodb的id即可。

這種設(shè)計(jì)思想類似于，我們表字段保存圖片時(shí)，為什么不是保存圖片內(nèi)容，而是直接保存圖片url即可。

20. 考慮是否需要分庫(kù)分表

什么是分庫(kù)分表呢？

• 分庫(kù)：就是一個(gè)數(shù)據(jù)庫(kù)分成多個(gè)數(shù)據(jù)庫(kù)，部署到不同機(jī)器。

• 分表：就是一個(gè)數(shù)據(jù)庫(kù)表分成多個(gè)表。

我們?cè)谠O(shè)計(jì)表的時(shí)候，其實(shí)可以提前估算一下，是否需要做分庫(kù)分表。比如一些用戶信息，未來(lái)可能數(shù)據(jù)量到達(dá)百萬(wàn)設(shè)置千萬(wàn)的話，就可以提前考慮分庫(kù)分表。

為什么需要分庫(kù)分表: 數(shù)據(jù)量太大的話，SQL的查詢就會(huì)變慢。如果一個(gè)查詢SQL沒(méi)命中索引，千百萬(wàn)數(shù)據(jù)量級(jí)別的表可能會(huì)拖垮整個(gè)數(shù)據(jù)庫(kù)。即使SQL命中了索引，如果表的數(shù)據(jù)量超過(guò)一千萬(wàn)的話，查詢也是會(huì)明顯變慢的。這是因?yàn)樗饕话闶荁+樹(shù)結(jié)構(gòu)，數(shù)據(jù)千萬(wàn)級(jí)別的話，B+樹(shù)的高度會(huì)增高，查詢就變慢啦。

分庫(kù)分表主要有水平拆分、垂直拆分的說(shuō)法，拆分策略有range范圍、hash取模。而分庫(kù)分表主要有這些問(wèn)題：

• 事務(wù)問(wèn)題
• 跨庫(kù)關(guān)聯(lián)
• 排序問(wèn)題
• 分頁(yè)問(wèn)題
• 分布式ID

21. sqL 編寫的一些優(yōu)化經(jīng)驗(yàn)

最后的話，跟大家聊來(lái)一些寫SQL的經(jīng)驗(yàn)吧：

• 查詢SQL盡量不要使用select *，而是select具體字段
• 如果知道查詢結(jié)果只有一條或者只要最大/最小一條記錄，建議用limit 1
• 應(yīng)盡量避免在where子句中使用or來(lái)連接條件
• 注意優(yōu)化limit深分頁(yè)問(wèn)題
• 使用where條件限定要查詢的數(shù)據(jù)，避免返回多余的行
• 盡量避免在索引列上使用mysql的內(nèi)置函數(shù)
• 應(yīng)盡量避免在 where子句中對(duì)字段進(jìn)行表達(dá)式操作
• 應(yīng)盡量避免在where 子句中使用!=或<>操作符
• 使用聯(lián)合索引時(shí)，注意索引列的順序，一般遵循最左匹配原則。
• 對(duì)查詢進(jìn)行優(yōu)化，應(yīng)考慮在where 及 order by涉及的列上建立索引
• 如果插入數(shù)據(jù)過(guò)多，考慮批量插入
• 在適當(dāng)?shù)臅r(shí)候，使用覆蓋索引
• 使用explain 分析你SQL的計(jì)劃