Mysql是我們開發(fā)中存儲(chǔ)數(shù)據(jù)的常用工具之一，好的數(shù)據(jù)表設(shè)計(jì)不僅讓業(yè)務(wù)更加清晰，而且也會(huì)讓后面繼續(xù)維護(hù)這套業(yè)務(wù)的人更易上手，今天我們從表的字段、索引等方面來聊聊一些好的Mysql表設(shè)計(jì)經(jīng)驗(yàn)。

1、表、字段、索引的命名規(guī)范

    數(shù)據(jù)庫表名、字段名、索引名等都需要規(guī)范的命名，命名盡量使用英文并要可讀性高，采用駝峰或者下劃線分割的方式，讓人見名知意。例如訂單的id可以命名為order_id，用戶的id可命名為user_id，這樣我們見到字段就可以知道其含義。

    一般表名、字段名使用小寫字母，不使用數(shù)字開頭、不使用拼音。對(duì)于索引的命名來講，我們遵循主鍵索引名（pk_字段名）；唯一索引名（uk_字段名）；普通索引名（idx_字段名）。

2、字段

2.1 選擇合適的字段類型

    設(shè)計(jì)字段的時(shí)候往往要考慮到自己的實(shí)際業(yè)務(wù)場景，然后選擇合適的字段類型。

（1）如某個(gè)字段的數(shù)據(jù)長度不會(huì)超過10個(gè)字符，則可以使用CHAR類型，如果字段的長度是定長（如身份證號(hào)、門牌號(hào)等）也可以CHAR類型，如果某個(gè)字段的長度不確定可以使用VARCHAR類型，如果字段長度可能很長，建議使用text類型。

（2）對(duì)于需要精確數(shù)值計(jì)算的字段(如貨幣和百分比)，應(yīng)該選擇帶有精度和小數(shù)位數(shù)的字段類型(如DECIMAL)。

（3）Mysql表示時(shí)間類型的字段主要如下的幾種：

    date：表示的日期值，格式y(tǒng)yyy-mm-dd，范圍1000-01-01到9999-12-31

    datetime：表示的日期時(shí)間值，它與時(shí)區(qū)無關(guān)，格式y(tǒng)yyy-mm-dd hh:mm:ss，范圍1000-01-0100:00:00到9999-12-31 23:59:59

    time：表示的時(shí)間值，格式hh:mm:ss，范圍-838:59:59到838:59:59

    timestamp： 表示的時(shí)間戳值，它跟時(shí)區(qū)有關(guān)，格式為yyyymmdd hhmmss，范圍1970-01-01 00:00:01到2038-01-19 03:14:07

    year：年份值，格式為yyyy。范圍1901到2155

    在阿里開發(fā)規(guī)范中推薦使用datetime類型來保存日期和時(shí)間，這是因?yàn)榇鎯?chǔ)范圍更大，且跟時(shí)區(qū)無關(guān)。

2.2 選擇適合字段長度

    首先我們要明確一點(diǎn)。在Mysql中的VARCHAR和CHAR類型表示字符長度，而其他類型表示的長度都表示字節(jié)長度。如CHAR(10)表示字符長度是10；bigint(8)表示長度是8個(gè)字節(jié)長度。

    我們需要根據(jù)實(shí)際的業(yè)務(wù)場景選擇合適的長度可以節(jié)省存儲(chǔ)空間，提升存儲(chǔ)、查詢的效率。

2.3 設(shè)計(jì)合理的主鍵

    主鍵的設(shè)計(jì)在數(shù)據(jù)庫中非常重要，它用于唯一標(biāo)識(shí)表中的每一行數(shù)據(jù)，并且在數(shù)據(jù)操作和查詢中起到關(guān)鍵作用。通常主鍵使用自增的id，這樣可以保持主鍵的連續(xù)性。在分布式的環(huán)境下，我們無法使用自增主鍵，一般推薦使用id生成器生成唯一的主鍵（常見的有雪花算法）。

2.4 添加一些通用的字段

    在阿里的表設(shè)計(jì)規(guī)范中也提到了設(shè)計(jì)數(shù)據(jù)表的時(shí)候添加一些通用的字段，如id、創(chuàng)建人字段、創(chuàng)建時(shí)間字段、修改人字段、修改時(shí)間字段等等，通過這些通用字段可以幫助我們了解這個(gè)數(shù)據(jù)誰創(chuàng)建的、最后的是誰在什么時(shí)間做了修改等信息。

2.5 表字段的數(shù)量不宜過多

    我們設(shè)計(jì)數(shù)據(jù)表的時(shí)候，表的字段盡量不超過20個(gè)。如果超出的話考慮做拆分，拆分為基本表和詳情表。這個(gè)拆分可以提高查詢效率，優(yōu)化了磁盤的存儲(chǔ)空間（表的字段數(shù)越多，每一行數(shù)據(jù)占用的存儲(chǔ)空間也就越大。這樣可能會(huì)導(dǎo)致磁盤空間的浪費(fèi)），同時(shí)方便后續(xù)的維護(hù)工作。

2.6 字段添加注釋

    設(shè)計(jì)數(shù)據(jù)表的字段的時(shí)候我們也要些字段的注釋，這樣自己也包括其他的人在看到這個(gè)字段的時(shí)候知道這個(gè)字段代表的含義，例如type字段

type tinyint not null default 0 comment '類型 1-上傳文件 2-下載文件'

    通過添加注釋我們在后續(xù)維護(hù)的時(shí)候無需去看業(yè)務(wù)代碼就知道字段type所代表的含義。

2.7 盡可能設(shè)置字段不為空

    一般都推薦將字段定義為NOT NULL，因?yàn)橐环矫?span style="-webkit-tap-highlight-color: transparent;padding: 0px;outline: 0px;max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;font-size: var(--articleFontsize);letter-spacing: 0.034em">NOT NULL可以有效的防止代碼中出現(xiàn)空指針問題，另一方面由于NULL值存儲(chǔ)也需要額外的空間的，同時(shí)NULL值也會(huì)導(dǎo)致比較運(yùn)算更為復(fù)雜，使優(yōu)化器難以優(yōu)化sql。

    如果在實(shí)際業(yè)務(wù)中將字段默認(rèn)設(shè)置成一個(gè)空字符串或常量值并沒有什么影響，那可以將這個(gè)字段設(shè)置為NOT NULL。

2.8 字段的刪除優(yōu)先考慮邏輯刪除

    常見的刪除有兩種方式，一種是邏輯刪除（數(shù)據(jù)表中添加一個(gè)字段is_deleted，用來標(biāo)記該數(shù)據(jù)已經(jīng)邏輯刪除）；一種是物理刪除（把數(shù)據(jù)從硬盤中刪除，可釋放存儲(chǔ)空間）。

    由于使用物理刪除一方面數(shù)據(jù)恢復(fù)困難，另一方面物理刪除也會(huì)導(dǎo)致索引樹重構(gòu)，所以推薦使用邏輯刪除。

2.9 合理的添加表的冗余字段

    添加表的冗余字段是違反了Mysql的三大范式設(shè)計(jì)要求，但是在實(shí)際的業(yè)務(wù)中我們增加了冗余字段可以減少表的關(guān)聯(lián)提升了性能。如在設(shè)計(jì)訂單的表的時(shí)候，我們在訂單表中的添加用戶下單的商品的冗余字段，這樣設(shè)計(jì)的目的就在查詢的訂單的時(shí)候就不用再做一次查詢來獲取下單商品的信息。

2.10 核心業(yè)務(wù)表添加擴(kuò)展字段

    我們一些關(guān)鍵的表設(shè)計(jì)的時(shí)候建議加上擴(kuò)展字段（extra），因?yàn)殡S著業(yè)務(wù)的發(fā)展，我們需要存儲(chǔ)更多業(yè)務(wù)信息，有些業(yè)務(wù)信息只做展示和代碼中查詢使用（如下單商品的規(guī)格信息），這些字段可以考慮放在擴(kuò)展字段中保存。

3、索引

3.1 合理設(shè)置索引

    在設(shè)計(jì)表時(shí)，我么需要充分考慮哪些字段需要加索引，可以參考如以下幾個(gè)原則：

（1）高頻的查詢條件：如果在查詢中使用了某個(gè)字段作為查詢條件，那么這個(gè)字段考慮建立索引。如在訂單表中將訂單的id設(shè)置為索引。

（2）區(qū)分度高的字段設(shè)置索引：如果一個(gè)字段的取值范圍非常小，典型的是性別字典，它只有男女兩種可能，那么這個(gè)字段就不適合建立索引，因?yàn)槠鋮^(qū)分度低。

（3）不要建立過多的索引：每個(gè)表所建立的索引數(shù)量應(yīng)該控制在一個(gè)合理的范圍內(nèi)，一般不要超過5個(gè)。因?yàn)檫^多的索引會(huì)導(dǎo)致寫入速度變慢，并占用更多的存儲(chǔ)空間。

（4）善于使用聯(lián)合索引：在某些情況下可以通過聯(lián)合索引的方式來優(yōu)化查詢速度，減少所需的索引數(shù)量。

3.2 盡可能少使用外鍵

    外鍵可以保證數(shù)據(jù)的一致性和完整性，但是它也會(huì)帶來一些問題，如性能問題、限制數(shù)據(jù)庫的擴(kuò)展性和靈活性、增加了維護(hù)成本等等問題，所以在一些情況下我們可以通過代碼維護(hù)數(shù)據(jù)的一致性和完整性來替代外鍵。

總結(jié)：

（1）設(shè)計(jì)表的時(shí)候，對(duì)于表名、字段名、索引名都要規(guī)范命名

（2）設(shè)計(jì)數(shù)據(jù)表字段不宜過多，字段要選擇合適的類型和長度，字段盡可能設(shè)置不為空，字段的注釋也清晰，字段在一些業(yè)務(wù)場景中可以設(shè)計(jì)冗余字段。

（3）合理設(shè)置索引、盡量少使用外鍵。