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

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

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

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

2、字段

2.1 選擇合適的字段類型

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

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

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

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

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

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

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

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

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

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

2.2 選擇適合字段長度

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

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

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

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

2.4 添加一些通用的字段

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

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

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

2.6 字段添加注釋

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

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

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

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

    一般都推薦將字段定義為NOT NULL，因為一方面NOT NULL可以有效的防止代碼中出現(xiàn)空指針問題，另一方面由于NULL值存儲也需要額外的空間的，同時NULL值也會導(dǎo)致比較運算更為復(fù)雜，使優(yōu)化器難以優(yōu)化sql。

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

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

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

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

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

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

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

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

3、索引

3.1 合理設(shè)置索引

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

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

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

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

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

3.2 盡可能少使用外鍵

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

總結(jié)：

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

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

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