數(shù)據(jù)庫建表的15個小技巧

前言

對于后端開發(fā)同學來說，訪問數(shù)據(jù)庫，是代碼中必不可少的一個環(huán)節(jié)。

系統(tǒng)中收集到用戶的核心數(shù)據(jù)，為了安全性，我們一般會存儲到數(shù)據(jù)庫，比如：mysql，oracle等。

后端開發(fā)的日常工作，需要不斷的建庫和建表，來滿足業(yè)務需求。

通常情況下，建庫的頻率比建表要低很多，所以，我們這篇文章主要討論建表相關的內容。

如果我們在建表的時候不注意細節(jié)，等后面系統(tǒng)上線之后，表的維護成本變得非常高，而且很容易踩坑。

今天就跟大家一起聊聊，數(shù)據(jù)庫建表的15個小技巧，希望對你會有所幫助。

1.名字

建表的時候，給表、字段和索引起個好名字，真的太重要了。

1.1 見名知意

名字就像表、字段和索引的一張臉，可以給人留下第一印象。

好的名字，言簡意賅，見名知意，讓人心情愉悅，能夠提高溝通和維護成本。

壞的名字，模擬兩可，不知所云。而且顯得雜亂無章，看得讓人抓狂。

反例：

用戶名稱字段定義成：yong_hu_ming、用戶_name、name、user_name_123456789

你看了可能會一臉懵逼，這是什么騷操作？

正例：

用戶名稱字段定義成：user_name

溫馨提醒一下，名字也不宜過長，盡量控制在30個字符以內。

1.2 大小寫

名字盡量都用小寫字母，因為從視覺上，小寫字母更容易讓人讀懂。

反例：

字段名：PRODUCT_NAME、PRODUCT_name

全部大寫，看起來有點不太直觀。而一部分大寫，一部分小寫，讓人看著更不爽。

正例：

字段名：product_name

名字還是使用全小寫字母，看著更舒服。

1.3 分隔符

很多時候，名字為了讓人好理解，有可能會包含多個單詞。

那么，多個單詞間的分隔符該用什么呢？

反例：

字段名：productname、productName、product name、product@name

單詞間沒有分隔，或者單詞間用駝峰標識，或者單詞間用空格分隔，或者單詞間用@分隔，這幾種方式都不太建議。

正例：

字段名：product_name

強烈建議大家在單詞間用_分隔。

1.4 表名

對于表名，在言簡意賅，見名知意的基礎之上，建議帶上業(yè)務前綴。

如果是訂單相關的業(yè)務表，可以在表名前面加個前綴：order_。

例如：order_pay、order_pay_detail等。

如果是商品相關的業(yè)務表，可以在表名前面加個前綴：product_。

例如：product_spu，product_sku等。

這樣做的好處是為了方便歸類，把相同業(yè)務的表，可以非?？焖俚木奂揭黄?。

另外，還有有個好處是，如果哪天有非訂單的業(yè)務，比如：金融業(yè)務，也需要建一個名字叫做pay的表，可以取名：finance_pay，就能非常輕松的區(qū)分。

這樣就不會出現(xiàn)同名表的情況。

1.5 字段名稱

字段名稱是開發(fā)人員發(fā)揮空間最大，但也最容易發(fā)生混亂的地方。

比如有些表，使用flag表示狀態(tài)，另外的表用status表示狀態(tài)。

可以統(tǒng)一一下，使用status表示狀態(tài)。

如果一個表使用了另一個表的主鍵，可以在另一張表的名后面，加_id或_sys_no，例如：

在product_sku表中有個字段，是product_spu表的主鍵，這時候可以取名：product_spu_id或product_spu_sys_no。

還有創(chuàng)建時間，可以統(tǒng)一成：create_time，修改時間統(tǒng)一成：update_time。

刪除狀態(tài)固定為：delete_status。

其實還有很多公共字段，在不同的表之間，可以使用全局統(tǒng)一的命名規(guī)則，定義成相同的名稱，以便于大家好理解。

1.6 索引名

在數(shù)據(jù)庫中，索引有很多種，包括：主鍵、普通索引、唯一索引、聯(lián)合索引等。

每張表的主鍵只有一個，一般使用：id或者sys_no命名。

普通索引和聯(lián)合索引，其實是一類。在建立該類索引時，可以加ix_前綴，比如：ix_product_status。

唯一索引，可以加ux_前綴，比如：ux_product_code。

2.字段類型

在設計表時，我們在選擇字段類型時，可發(fā)揮空間很大。

時間格式的數(shù)據(jù)有：date、datetime和timestamp等等可以選擇。

字符類型的數(shù)據(jù)有：varchar、char、text等可以選擇。

數(shù)字類型的數(shù)據(jù)有：int、bigint、smallint、tinyint等可以選擇。

說實話，選擇很多，有時候是一件好事，也可能是一件壞事。

如何選擇一個合適的字段類型，變成了我們不得不面對的問題。

如果字段類型選大了，比如：原本只有1-10之間的10個數(shù)字，結果選了bigint，它占8個字節(jié)。

其實，1-10之間的10個數(shù)字，每個數(shù)字1個字節(jié)就能保存，選擇tinyint更為合適。

這樣會白白浪費7個字節(jié)的空間。

如果字段類型擇小了，比如：一個18位的id字段，選擇了int類型，最終數(shù)據(jù)會保存失敗。

所以選擇一個合適的字段類型，還是非常重要的一件事情。

以下原則可以參考一下：

盡可能選擇占用存儲空間小的字段類型，在滿足正常業(yè)務需求的情況下，從小到大，往上選。

如果字符串長度固定，或者差別不大，可以選擇char類型。如果字符串長度差別較大，可以選擇varchar類型。

是否字段，可以選擇bit類型。

枚舉字段，可以選擇tinyint類型。

主鍵字段，可以選擇bigint類型。

金額字段，可以選擇decimal類型。

時間字段，可以選擇timestamp或datetime類型。

3.字段長度

前面我們已經定義好了字段名稱，選擇了合適的字段類型，接下來，需要重點關注的是字段長度了。

比如：varchar(20)，biginit(20)等。

那么問題來了，varchar代表的是字節(jié)長度，還是字符長度呢？

答：在mysql中除了varchar和char是代表字符長度之外，其余的類型都是代表字節(jié)長度。

biginit(n) 這個n表示什么意思呢？

假如我們定義的字段類型和長度是：bigint(4)，bigint實際長度是8個字節(jié)。

現(xiàn)在有個數(shù)據(jù)a=1，a顯示4個字節(jié)，所以在不滿4個字節(jié)時前面填充0（前提是該字段設置了zerofill屬性），比如：0001。

當滿了4個字節(jié)時，比如現(xiàn)在數(shù)據(jù)是a=123456，它會按照實際的長度顯示，比如：123456。

但需要注意的是，有些mysql客戶端即使?jié)M了4個字節(jié)，也可能只顯示4個字節(jié)的內容，比如會顯示成：1234。

所以bigint(4)，這里的4表示顯示的長度為4個字節(jié)，實際長度還是占8個字節(jié)。

4.字段個數(shù)

我們在建表的時候，一定要對字段個數(shù)做一些限制。

我之前見過有人創(chuàng)建的表，有幾十個，甚至上百個字段，表中保存的數(shù)據(jù)非常大，查詢效率很低。

如果真有這種情況，可以將一張大表拆成多張小表，這幾張表的主鍵相同。

建議每表的字段個數(shù)，不要超過20個。

5. 主鍵

在創(chuàng)建表時，一定要創(chuàng)建主鍵。

因為主鍵自帶了主鍵索引，相比于其他索引，主鍵索引的查詢效率最高，因為它不需要回表。

此外，主鍵還是天然的唯一索引，可以根據(jù)它來判重。

在單個數(shù)據(jù)庫中，主鍵可以通過AUTO_INCREMENT，設置成自動增長的。

但在分布式數(shù)據(jù)庫中，特別是做了分庫分表的業(yè)務庫中，主鍵最好由外部算法(比如：雪花算法）生成，它能夠保證生成的id是全局唯一的。

除此之外，主鍵建議保存跟業(yè)務無關的值，減少業(yè)務耦合性，方便今后的擴展。

不過我也見過，有些一對一的表關系，比如：用戶表和用戶擴展表，在保存數(shù)據(jù)時是一對一的關系。

這樣，用戶擴展表的主鍵，可以直接保存用戶表的主鍵。

6.存儲引擎

在mysql5.1以前的版本，默認的存儲引擎是myslam，而mysql5.1以后的版本，默認的存儲引擎變成了innodb。

之前我們還在創(chuàng)建表時，還一直糾結要選哪種存儲引擎？

myslam的索引和數(shù)據(jù)分開存儲，而有利于查詢，但它不支持事務和外鍵等功能。

而innodb雖說查詢性能，稍微弱一點，但它支持事務和外鍵等，功能更強大一些。

以前的建議是：讀多寫少的表，用myslam存儲引擎。而寫多讀多的表，用innodb。

但雖說mysql對innodb存儲引擎性能的不斷優(yōu)化，現(xiàn)在myslam和innodb查詢性能相差已經越來越小。

所以，建議我們在使用mysql8以后的版本時，直接使用默認的innodb存儲引擎即可，無需額外修改存儲引擎。

7. NOT NULL

在創(chuàng)建字段時，需要選擇該字段是否允許為NULL。

我們在定義字段時，應該盡可能明確該字段NOT NULL。

為什么呢？

我們主要以innodb存儲引擎為例，myslam存儲引擎沒啥好說的。

主要有以下原因：

在innodb中，需要額外的空間存儲null值，需要占用更多的空間。

null值可能會導致索引失效。

null值只能用is null或者is not null判斷，用=號判斷永遠返回false。

因此，建議我們在定義字段時，能定義成NOT NULL，就定義成NOT NULL。

但如果某個字段直接定義成NOT NULL，萬一有些地方忘了給該字段寫值，就會insert不了數(shù)據(jù)。

這也算合理的情況。

但有一種情況是，系統(tǒng)有新功能上線，新增了字段。上線時一般會先執(zhí)行sql腳本，再部署代碼。

由于老代碼中，不會給新字段賦值，則insert數(shù)據(jù)時，也會報錯。

由此，非常有必要給NOT NULL的字段設置默認值，特別是后面新增的字段。

例如：

altertableproduct_skuaddcolumnbrand_idint(10)notnulldefault0;

8.外鍵

在mysql中，是存在外鍵的。

外鍵存在的主要作用是：保證數(shù)據(jù)的一致性和完整性。

例如：

createtableclass(
idint(10)primarykeyauto_increment,
cnamevarchar(15)
);

有個班級表class。

然后有個student表：

createtablestudent(
idint(10)primarykeyauto_increment,
namevarchar(15)notnull,
gendervarchar(10)notnull,
cidint,
foreignkey(cid)referencesclass(id)
);

其中student表中的cid字段，保存的class表的id，這時通過foreign key增加了一個外鍵。

這時，如果你直接通過student表的id刪除數(shù)據(jù)，會報異常：

aforeignkeyconstraintfails

必須要先刪除class表對于的cid那條數(shù)據(jù)，再刪除student表的數(shù)據(jù)才行，這樣能夠保證數(shù)據(jù)的一致性和完整性。

順便說一句：只有存儲引擎是innodb時，才能使用外鍵。

如果只有兩張表的關聯(lián)還好，但如果有十幾張表都建了外鍵關聯(lián)，每刪除一次主表，都需要同步刪除十幾張子表，很顯然性能會非常差。

因此，互聯(lián)網系統(tǒng)中，一般建議不使用外鍵。因為這類系統(tǒng)更多的是為了性能考慮，寧可犧牲一點數(shù)據(jù)一致性和完整性。

除了外鍵之外，存儲過程和觸發(fā)器也不太建議使用，他們都會影響性能。

9. 索引

在建表時，除了指定主鍵索引之外，還需要創(chuàng)建一些普通索引。

例如：

createtableproduct_sku(
idint(10)primarykeyauto_increment,
spu_idint(10)notnull,
brand_idint(10)notnull,
namevarchar(15)notnull
);

在創(chuàng)建商品表時，使用spu_id（商品組表）和brand_id（品牌表）的id。

像這類保存其他表id的情況，可以增加普通索引：

createtableproduct_sku(
idint(10)primarykeyauto_increment,
spu_idint(10)notnull,
brand_idint(10)notnull,
namevarchar(15)notnull,
KEY`ix_spu_id`(`spu_id`)USINGBTREE,
KEY`ix_brand_id`(`brand_id`)USINGBTREE
);

后面查表的時候，效率更高。

但索引字段也不能建的太多，可能會影響保存數(shù)據(jù)的效率，因為索引需要額外的存儲空間。

建議單表的索引個數(shù)不要超過：5個。

如果在建表時，發(fā)現(xiàn)索引個數(shù)超過5個了，可以刪除部分普通索引，改成聯(lián)合索引。

順便說一句：在創(chuàng)建聯(lián)合索引的時候，需要使用注意最左匹配原則，不然，建的聯(lián)合索引效率可能不高。

對于數(shù)據(jù)重復率非常高的字段，比如：狀態(tài)，不建議單獨創(chuàng)建普通索引。因為即使加了索引，如果mysql發(fā)現(xiàn)全表掃描效率更高，可能會導致索引失效。

如果你對索引失效問題比較感興趣，可以看看我的另一篇文章《聊聊索引失效的10種場景，太坑了》，里面有非常詳細的介紹。

10.時間字段

時間字段的類型，我們可以選擇的范圍還是比較多的，目前mysql支持：date、datetime、timestamp、varchar等。

varchar類型可能是為了跟接口保持一致，接口中的時間類型是String。

但如果哪天我們要通過時間范圍查詢數(shù)據(jù)，效率會非常低，因為這種情況沒法走索引。

date類型主要是為了保存日期，比如：2020-08-20，不適合保存日期和時間，比如：2020-08-20 1220。

而datetime和timestamp類型更適合我們保存日期和時間。

但它們有略微區(qū)別。

timestamp：用4個字節(jié)來保存數(shù)據(jù)，它的取值范圍為1970-01-01 0001 UTC ~ 2038-01-19 0307。此外，它還跟時區(qū)有關。

datetime：用8個字節(jié)來保存數(shù)據(jù)，它的取值范圍為1000-01-01 0000 ~ 9999-12-31 2359。它跟時區(qū)無關。

優(yōu)先推薦使用datetime類型保存日期和時間，可以保存的時間范圍更大一些。

溫馨提醒一下，在給時間字段設置默認值是，建議不要設置成：0000-00-00 0000，不然查詢表時可能會因為轉換不了，而直接報錯。

11.金額字段

mysql中有多個字段可以表示浮點數(shù)：float、double、decimal等。

而float和double可能會丟失精度，因此推薦大家使用decimal類型保存金額。

一般我們是這樣定義浮點數(shù)的：decimal(m,n)。

其中n是指小數(shù)的長度，而m是指整數(shù)加小數(shù)的總長度。

假如我們定義的金額類型是這樣的：decimal(10,2)，則表示整數(shù)長度是8位，并且保留2位小數(shù)。

12.唯一索引

唯一索引在我們實際工作中，使用頻率相當高。

你可以給單個字段，加唯一索引，比如：組織機構code。

也可以給多個字段，加一個聯(lián)合的唯一索引，比如：分類編號、單位、規(guī)格等。

單個的唯一索引還好，但如果是聯(lián)合的唯一索引，字段值出現(xiàn)null時，則唯一性約束可能會失效。

關于唯一索引失效的問題，感興趣的小伙伴可以看看我的另一篇文章《明明加了唯一索引，為什么還是產生重復數(shù)據(jù)？》。

創(chuàng)建唯一索引時，相關字段一定不能包含null值，否則唯一性會失效。

13.字符集

mysql中支持的字符集有很多，常用的有：latin1、utf-8、utf8mb4、GBK等。

這4種字符集情況如下：

latin1容易出現(xiàn)亂碼問題，在實際項目中使用比較少。

而GBK支持中文，但不支持國際通用字符，在實際項目中使用也不多。

從目前來看，mysql的字符集使用最多的還是：utf-8和utf8mb4。

其中utf-8占用3個字節(jié)，比utf8mb4的4個字節(jié)，占用更小的存儲空間。

但utf-8有個問題：即無法存儲emoji表情，因為emoji表情一般需要4個字節(jié)。

由此，使用utf-8字符集，保存emoji表情時，數(shù)據(jù)庫會直接報錯。

所以，建議在建表時字符集設置成：utf8mb4，會省去很多不必要的麻煩。

14. 排序規(guī)則

不知道，你關注過沒，在mysql中創(chuàng)建表時，有個COLLATE參數(shù)可以設置。

例如：

CREATETABLE`order`(
`id`bigintNOTNULLAUTO_INCREMENT,
`code`varchar(20)COLLATEutf8mb4_binNOTNULL,
`name`varchar(30)COLLATEutf8mb4_binNOTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`un_code`(`code`),
KEY`un_code_name`(`code`,`name`)USINGBTREE,
KEY`idx_name`(`name`)
)ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_bin

它是用來設置排序規(guī)則的。

字符排序規(guī)則跟字符集有關，比如：字符集如果是utf8mb4，則字符排序規(guī)則也是以：utf8mb4_開頭的，常用的有：utf8mb4_general_ci、utf8mb4_bin等。

其中utf8mb4_general_ci排序規(guī)則，對字母的大小寫不敏感。說得更直白一點，就是不區(qū)分大小寫。

而utf8mb4_bin排序規(guī)則，對字符大小寫敏感，也就是區(qū)分大小寫。

說實話，這一點還是非常重要的。

假如order表中現(xiàn)在有一條記錄，name的值是大寫的YOYO，但我們用小寫的yoyo去查，例如：

select*fromorderwherename='yoyo';

如果字符排序規(guī)則是utf8mb4_general_ci，則可以查出大寫的YOYO的那條數(shù)據(jù)。

如果字符排序規(guī)則是utf8mb4_bin，則查不出來。

由此，字符排序規(guī)則一定要根據(jù)實際的業(yè)務場景選擇，否則容易出現(xiàn)問題。

15.大字段

我們在創(chuàng)建表時，對一些特殊字段，要額外關注，比如：大字段，即占用較多存儲空間的字段。

比如：用戶的評論，這就屬于一個大字段，但這個字段可長可短。

但一般會對評論的總長度做限制，比如：最多允許輸入500個字符。

如果直接定義成text類型，可能會浪費存儲空間，所以建議將這類字段定義成varchar類型的存儲效率更高。

當然，我還見過更大的字段，即該字段直接保存合同數(shù)據(jù)。

一個合同可能會占幾Mb。

在mysql中保存這種數(shù)據(jù)，從系統(tǒng)設計的角度來說，本身就不太合理。

像合同這種非常大的數(shù)據(jù)，可以保存到mongodb中，然后在mysql的業(yè)務表中，保存mongodb表的id。