故事

會(huì)議室里，小貓撓著頭，心里暗暗叫苦著“哎，這代碼都擼完了呀，改起來成本也太大了?！?/p>

原來就在剛才，組長找到了小貓，說代碼review過程中發(fā)現(xiàn)有些數(shù)據(jù)表模型設(shè)計(jì)得不合理，要求小貓改掉。小貓大概是設(shè)計(jì)了一個(gè)配置表，為了省事兒，小貓直接把相關(guān)的配置設(shè)計(jì)成了text類型的存儲(chǔ)形式。

關(guān)于這種業(yè)務(wù)場(chǎng)景下使用text文本類型存儲(chǔ)，組長指出了以下缺點(diǎn)：

• 在內(nèi)存中處理Text字段時(shí)，由于需要處理大量數(shù)據(jù)，可能會(huì)導(dǎo)致內(nèi)存使用過度，影響數(shù)據(jù)庫性能。
• Text字段無法創(chuàng)建索引，這會(huì)導(dǎo)致數(shù)據(jù)庫在執(zhí)行查詢時(shí)無法利用索引來加速搜索。雖然可以通過全文索引來改善搜索性能，但是卻會(huì)有諸多限制，例如只能用于InnoDB引擎，并且索引只能建立在不超過1000字節(jié)的前綴上。
• 目前剛設(shè)計(jì)的時(shí)候就用text類型，后期隨著數(shù)據(jù)量的增長以及業(yè)務(wù)需求的變化，可能就意味著要將text類型繼續(xù)擴(kuò)大變成，LongText或MediumText類型，這樣的轉(zhuǎn)換既費(fèi)時(shí)又可能需要額外的存儲(chǔ)空間。

“組長說的也有道理，但是為什么現(xiàn)在才指出來，當(dāng)時(shí)方案模型評(píng)審的時(shí)候咋提呢，哎，醉了醉了，現(xiàn)在業(yè)務(wù)邏輯都按照現(xiàn)有方案開發(fā)完了，才提出來.....”

“改？要重寫邏輯，不改？萬一今后真的出現(xiàn)上面組長說的這些問題，不得被噴死......”

彷徨過后，小貓終于下定了決心，改了吧，長痛不如短痛...反正如果不改的話，受罪的還是自己。于是加班是少不了了......

數(shù)據(jù)庫設(shè)計(jì)

在需求評(píng)審?fù)戤呏?，一般就是我們的技術(shù)方案的設(shè)計(jì)，在技術(shù)方案設(shè)計(jì)過程中，數(shù)據(jù)庫模型設(shè)計(jì)是一個(gè)非常重要的環(huán)節(jié)。數(shù)據(jù)庫模型的設(shè)計(jì)往往會(huì)影響后續(xù)業(yè)務(wù)邏輯的拓展或者直接影響著研發(fā)實(shí)際寫代碼的工作量。甚至?xí)绊憥状邪l(fā)的維護(hù)成本。因此物理數(shù)據(jù)庫的設(shè)計(jì)應(yīng)該是一個(gè)非常謹(jǐn)慎以及嚴(yán)苛的過程，我們需要步步為營。

那么我們又當(dāng)如何去進(jìn)行數(shù)據(jù)庫的設(shè)計(jì)呢？接下來，老貓從我們?nèi)粘ｉ_發(fā)中用到比較多的mysql數(shù)據(jù)庫的設(shè)計(jì)規(guī)范說起。如下。

概要

表設(shè)計(jì)

關(guān)于表設(shè)計(jì)，咱們從以下幾個(gè)方面來看。

1、 表的命名：表命名非常重要，我們要盡量去做到見名知意。

2、 根據(jù)實(shí)際場(chǎng)景確定引擎，咱們一般業(yè)務(wù)都會(huì)涉及到事務(wù)、行級(jí)鎖等功能，所以日常開發(fā)中包括設(shè)計(jì)中咱們還是以InnoDB引擎為準(zhǔn)。

表設(shè)計(jì)-命名規(guī)范

1、在給相關(guān)的表進(jìn)行命名的時(shí)候，表名建議還是以小寫英文字母和0-9數(shù)字組成（如果不涉及分表等業(yè)務(wù)場(chǎng)景，其實(shí)很少表名中會(huì)帶有數(shù)字）以及下劃線組成。雖然mysql在windows下表名不區(qū)分大小寫，但是在linux下是區(qū)分大小寫的，因此表名最好為小寫。

2、命名需要分類區(qū)分對(duì)待，當(dāng)然英文單詞的命名建議使用名詞而不是動(dòng)詞，另外的話詞義應(yīng)該要與業(yè)務(wù)、產(chǎn)品線想關(guān)聯(lián)。例如我們命名一些配置類表的時(shí)候習(xí)慣以config打頭，例如config_XXX。當(dāng)命名臨時(shí)表的時(shí)候一般tmp打頭，一般為tmp_XXX，當(dāng)備份表的時(shí)候那么就是bak_XXX。

3、 表命名咱們要用英文，而不是拼音或者是拼音和英文混合。記得大學(xué)剛出來的時(shí)候，那時(shí)候老貓也用拼音命名過一些表，例如設(shè)計(jì)圖書管理系統(tǒng)的時(shí)候居然用上了shu_jia（書架）?，F(xiàn)在想想好搓。雖然用到英文，上面提到用名詞，那咱們?cè)谟妹~的時(shí)候其實(shí)最好也是使用單數(shù)形式而非復(fù)數(shù)。例如員工表設(shè)計(jì)的時(shí)候，我們?cè)O(shè)計(jì)成employee而不是employees。

4、 上述還提及表名中包含數(shù)字，其實(shí)很多時(shí)候在我們分庫分表的時(shí)候會(huì)用到。這里提及幾個(gè)散表命名方式。首先是hash取模散表，咱們表名后綴使用16進(jìn)制數(shù)，下標(biāo)從0開始，或者咱們用md5進(jìn)行散表那么基友user_0,user_ff等等。當(dāng)然如果用到時(shí)間散表，咱們按照年月分表的時(shí)候，咱們會(huì)命名成user_202404等諸如此類。

表設(shè)計(jì)-設(shè)計(jì)規(guī)范

1、表設(shè)計(jì)的時(shí)候一般使用Innodb引擎。當(dāng)然Mysql存在兩種可選引擎還有一種是MyISAM。MyISAM速度快，但是不支持事務(wù)、外鍵以及行級(jí)鎖。反觀Innodb速度稍遜一籌，但是可以支持事務(wù)、外鍵（雖然微服務(wù)的場(chǎng)景下，外鍵很少用了）、行級(jí)鎖等高級(jí)功能。

2、必須定義主鍵。說到主鍵，咱一般都是Id自增主鍵。有的時(shí)候咱們可能也會(huì)用到uuid或者M(jìn)d5或者h(yuǎn)ash等字符串作為主鍵，但是這些列并不能保證數(shù)據(jù)的順序增長。這里還是要和大家聊聊這兩種主鍵的優(yōu)缺點(diǎn)。方便大家后續(xù)在做表設(shè)計(jì)的時(shí)候進(jìn)行取舍。老貓總結(jié)了一下，如下圖：

主鍵對(duì)比

3、表設(shè)計(jì)必須包含創(chuàng)建時(shí)間以及修改時(shí)間，用于記錄創(chuàng)建時(shí)間和修改時(shí)間。

4、表設(shè)計(jì)的時(shí)候不要使用外鍵，外鍵影響高并發(fā)下的性能，另外的目前我們的大型項(xiàng)目中會(huì)涉及到分庫分表，如果遇到外鍵的話，咱們的分庫分表將會(huì)難以實(shí)施。

5、慎用觸發(fā)器和存儲(chǔ)過程。當(dāng)然現(xiàn)在咱們應(yīng)該很少會(huì)用到了，老貓只有當(dāng)年在學(xué)習(xí)的時(shí)候用到了存儲(chǔ)過程，后面實(shí)際工作的時(shí)候好像就再也沒有接觸過了。觸發(fā)器和存儲(chǔ)過程雖然可以減少開發(fā)量，另外封裝性也好，比較安全并且不存在SQL注入問題。但是其本身可移植性是非常差的，另外的話占用服務(wù)器的資源也比較多，一旦發(fā)生錯(cuò)誤，咱們排查問題也比較困難?；ヂ?lián)網(wǎng)領(lǐng)域，我們現(xiàn)在更愿意把業(yè)務(wù)邏輯放到代碼側(cè)，變更會(huì)容易一些。

6、不要在建表的時(shí)候進(jìn)行預(yù)留字段，預(yù)留字段命名很難做到見名知意，另外的話及時(shí)今后用到，在數(shù)據(jù)量大的情況下，如果類型不滿足需求，我們?nèi)プ兏愋偷臅r(shí)候會(huì)導(dǎo)致鎖表。

7、單條記錄的大小不要超過8kb。那么這又是為什么呢？首先，咱們從索引角度來看，innodb的頁塊大小默認(rèn)為16kb，由于innodb采用聚簇索引（B+樹結(jié)構(gòu)）存放數(shù)據(jù)，每個(gè)頁塊中至少有兩行數(shù)據(jù)，否則就失去了B+樹的意義（如果每個(gè)頁中只有一條數(shù)據(jù)，整個(gè)樹就成了一條雙向鏈表）。由于每個(gè)頁塊中至少有兩行數(shù)據(jù)，可以得出一行數(shù)據(jù)的大小限制為8kb。其次，從硬盤扇區(qū)大小的角度來看，單條記錄的大小一般不應(yīng)該超過硬盤的扇區(qū)大小，目前硬盤的扇區(qū)大小多為4kb（只有少數(shù)是16kb），如果單條記錄過大的話，查找的時(shí)候就會(huì)跨越多個(gè)扇區(qū)，增加尋道時(shí)間，可能導(dǎo)致性能下降。

8、單表在設(shè)計(jì)過程中，咱們最好不要超過50個(gè)int字段、20個(gè)char字段、2個(gè)text字段，另外的話單表列數(shù)也要盡量少于50，單表數(shù)量咱們也要盡量控制在500w一下，2Gb以內(nèi)。如果過大的情況下，修改表結(jié)構(gòu)、備份、恢復(fù)就有影響，所以當(dāng)出現(xiàn)太大表的時(shí)候，咱們還是盡量要去分庫分表。

字段設(shè)計(jì)

字段設(shè)計(jì)主要涵蓋兩個(gè)方面，一個(gè)是字段的命名，另外一個(gè)是字段的數(shù)據(jù)類型。咱們接下來詳細(xì)看一下。

字段設(shè)計(jì)-命名規(guī)范

1、和表設(shè)計(jì)的時(shí)候一樣，咱們?cè)谧侄蚊倪^程中也盡量不要使用拼音。

2、在設(shè)計(jì)字段的時(shí)候，咱們要避免數(shù)據(jù)庫關(guān)鍵字，比如name、time、datetime、desc等等。如果真要用到name的時(shí)候，咱們最好加上其他元素以及下劃線進(jìn)行組成，例如user_name、biz_name等等。

3、字段表示枚舉、狀態(tài)類型表示是或者否的時(shí)候，咱們最好用is打頭，例如is_member，類型用unsigned tinyint(1-是 0-否) default 0。

字段設(shè)計(jì)-設(shè)計(jì)規(guī)范

1、當(dāng)我們預(yù)知當(dāng)前字段比較重要，或者之后查詢的時(shí)候用到比較多的時(shí)候，我們肯定要加上索引，那么這種字段，咱們?cè)谶M(jìn)行設(shè)計(jì)的時(shí)候就必須定義成Not null，并且設(shè)置default值。例如name為非空的，那么我們的定義可能是name not null default '' comment "命名"。

2、如果字段涉及小數(shù)存儲(chǔ)的時(shí)候，我們的字段類型最好使用bigdecimal類型，而不是float或者是double，float以及double都會(huì)存在精度丟失的問題。當(dāng)然有較真的小伙伴也會(huì)說bigdecimal也是有范圍的，那么如果超過范圍的話，應(yīng)該怎么辦？那么這個(gè)時(shí)候，其實(shí)我們可以將其分開進(jìn)行存儲(chǔ)，整數(shù)和小數(shù)拆開。

3、避免使用text或者blob類型存儲(chǔ)大圖片文件等信息，這種信息建議直接存儲(chǔ)到文件系統(tǒng)，數(shù)據(jù)庫里面可以直接存儲(chǔ)對(duì)應(yīng)的文件系統(tǒng)鏈接即可。

4、字符串類型的，咱們一般使用varchar類型，如果說存儲(chǔ)的字符串差不多都是等長的，那么我們可以將字段設(shè)計(jì)成char定長字符串類型。另外的，varhcar類型在進(jìn)行設(shè)計(jì)的時(shí)候咱們要避免設(shè)計(jì)過長，因?yàn)関archar類型在存儲(chǔ)層面是根據(jù)實(shí)際長度存儲(chǔ)的，但是內(nèi)存分配卻是根據(jù)指定長度進(jìn)行的。所以如果字段設(shè)計(jì)不合理會(huì)導(dǎo)致內(nèi)存不合理占用。

5、進(jìn)行時(shí)間設(shè)計(jì)的時(shí)候，如果確定只要年月日，那么咱們就將字段設(shè)計(jì)成date類型。如果說要用到時(shí)間戳的話，那么我們要用到datetime以及timestamp。但是我們要注意這兩者的區(qū)別。關(guān)于這兩者的區(qū)別，老貓?jiān)俅瞬蛔稣归_，大家有興趣的可以自己查一下。

6、當(dāng)多個(gè)表中都關(guān)聯(lián)一個(gè)字段的時(shí)候，咱們應(yīng)該要保證這兩個(gè)字段的類型一致，以免在寫代碼的時(shí)候帶來不必要的轉(zhuǎn)換麻煩。例如tenant_id這個(gè)字段在A表中我們?cè)O(shè)計(jì)成int類型在另外一個(gè)地方又設(shè)計(jì)成了bigint，那么我們對(duì)應(yīng)的代碼中可能一個(gè)就是int類型另外一個(gè)地方就是Long類型。這樣在實(shí)際編碼的過程中就要去轉(zhuǎn)換。

索引設(shè)計(jì)

聊到索引相信大家都不陌生，索引一般以索引文件的形式存儲(chǔ)在磁盤上。我們一般所說的索引指的就是B+樹結(jié)構(gòu)組織的索引。

接下來咱們簡單聊一下不同層面的索引的劃分，然后再來聊索引相關(guān)的設(shè)計(jì)規(guī)范。

索引的分類

根據(jù)存儲(chǔ)類型劃分

聚集索引：在數(shù)據(jù)庫表中物理順序和主鍵順序一致，即數(shù)據(jù)行按照主鍵的順序存儲(chǔ)。只要找到第一個(gè)索引值記錄，其余的連續(xù)記錄在物理層存儲(chǔ)層面一樣是連續(xù)存放。為了使得表記錄和索引的排列順序一致，插入記錄會(huì)重新排序，因此修改數(shù)據(jù)比較慢。

聚集索引

非聚集索引：表記錄和索引的排列順序不一定一致，非聚集索引的葉子層并不和實(shí)際數(shù)據(jù)頁相重疊，而是采用葉子層包含一個(gè)指向表記錄的指針。非聚集索引層次多，不會(huì)造成數(shù)據(jù)重排。

關(guān)于數(shù)據(jù)庫的索引的詳細(xì)介紹，老貓?jiān)诖瞬蛔稣归_。后續(xù)會(huì)有專門的文章和大家分享。

根據(jù)邏輯劃分

這塊大家日常應(yīng)用的過程中應(yīng)該還是比較常見的。咱們可以分成以下幾種類型。

1. 主鍵索引：特殊的唯一索引，不允許有空值。
2. 聯(lián)合索引：多個(gè)字段上建立的索引，用來提升復(fù)合查詢的效率。
3. 普通索引：屬于基本索引，沒有其他限制。
4. 唯一索引：和普通索引相似，但是值必須唯一，可以用空值，常用來做冪等。

索引命名規(guī)范

咱們?cè)诮o索引命名的時(shí)候需要均用英文小寫字母進(jìn)行命名。

主鍵索引：一般命名用pk_字段名稱（默認(rèn)一般都是id索引，在創(chuàng)建表的時(shí)候一般就已經(jīng)指定完成了）

普通索引：咱們命名的時(shí)候一般用idx_表名_字段名稱或者idx_字段名稱。

唯一索引：一般用uk_表名_字段名稱或者uk_字段名稱。

設(shè)計(jì)規(guī)范

1、不是所有的數(shù)據(jù)庫字段都適合加索引的。我們?cè)诮⑺饕臅r(shí)候需要評(píng)估字段的區(qū)分度。應(yīng)該盡量避免將索引建立在區(qū)分度低的字段上。舉個(gè)例子，例如性別：男女。還有日常業(yè)務(wù)中用到的狀態(tài)值、或者status-是否標(biāo)記等等。

2、應(yīng)當(dāng)避免在頻繁更新的字段上建立索引。因?yàn)槊看巫兏紩?huì)導(dǎo)致B+樹發(fā)生變更，頻繁的變更會(huì)導(dǎo)致數(shù)據(jù)庫的性能大大降低。

3、我們需要控制一張表中索引的數(shù)量，索引數(shù)量并不是越多越好，單表建議控制在5個(gè)以內(nèi)，當(dāng)然這個(gè)也要結(jié)合表字段的總數(shù)來定并非絕對(duì)。索引創(chuàng)建過多會(huì)增加CPU以及IO的開銷。雖然索引可以提高查詢效率，但是同樣會(huì)降低插入以及更新的效率。

4、創(chuàng)建聯(lián)合索引的時(shí)候盡量避免冗余。例如（a,b,c）聯(lián)合索引即相當(dāng)于(a)、（a,b）、(a、b、c)。另外這里其實(shí)要提到索引的最左匹配原則。當(dāng)查詢的時(shí)候?yàn)?a)或者（a,b）或者（a,b,c）的時(shí)候才能走到索引。如果查詢是(a,c)那么其實(shí)只能走到(a)索引，這個(gè)時(shí)候其實(shí)需要注意(a)的時(shí)候返回的數(shù)據(jù)量，如果過多的話，其實(shí)語句設(shè)計(jì)就是不合理的。如果查詢是（b,c）則不能走索引。（面試官也比較喜歡問這類問題）

5、能使用唯一索引的場(chǎng)景，我們應(yīng)該盡量去使用唯一索引。

6、如果一個(gè)字段的類型是varchar并且此時(shí)我們需要去建立相關(guān)的索引，我們此時(shí)必須要指定相關(guān)索引的長度，因?yàn)樵谇拔闹形覀円蔡岬搅藇archar類型存儲(chǔ)的字符串長度往往是不固定的，如果是固定長度的咱們一般用char。我們完全沒有必要對(duì)全字段建立索引，我們只要根據(jù)字段文本的區(qū)分度來建立索引即可。如下建立索引語句：

ALTER TABLE users ADD INDEX idx_email (email(10));

總結(jié)

當(dāng)我們接到產(chǎn)品提的相關(guān)需求之后，我們就會(huì)開始進(jìn)行相關(guān)的技術(shù)分析和設(shè)計(jì)，其中在設(shè)計(jì)階段就會(huì)涉及基本的業(yè)務(wù)模型的設(shè)計(jì)。最終就是進(jìn)行數(shù)據(jù)模型的設(shè)計(jì)。此時(shí)就會(huì)遇到上述的一些數(shù)據(jù)庫設(shè)計(jì)的問題。

通過上述一些注意點(diǎn)，相信很多小伙伴應(yīng)該知道數(shù)據(jù)表設(shè)計(jì)階段的一些注意點(diǎn)了。