前言
作為後端開發，我們經常需要設計數據庫表。整理了21個設計MySQL表的經驗準則，分享給大家，大家看完一定會有幫助的。
1.命名規範
數據庫表名、字段名、索引名等都需要命名規範，可讀性高(一般要求用英文)，讓別人一看命名，就知道這個字段表示什麼意思。
account_no,account_number
表名、字段名必須使用小寫字母或者數字，禁止使用數字開頭，禁止使用拼音，並且一般不使用英文縮寫。
主鍵索引名為pk_字段名；唯一索引名為uk_字段名；普通索引名則為idx_字段名。
2.選擇合適的字段類型
儘可能選擇存儲空間小的字段類型，就好像數字類型的，從tinyint、smallint、int、bigint從左往右開始選擇
小數類型如金額，則選擇 decimal，禁止使用 float 和 double。
如果存儲的字符串長度幾乎相等，使用 char 定長字符串類型。
varchar是可變長字符串，不預先分配存儲空間，長度不要超過5000。
如果存儲的值太大，建議字段類型修改為text，同時抽出單獨一張表，用主鍵與之對應。
同一表中，所有varchar字段的長度加起來，不能大於65535. 如果有這樣的需求，請使用TEXT/LONGTEXT 類型。
3. 主鍵設計要合理
主鍵設計的話，最好不要與業務邏輯有所關聯。有些業務上的字段，比如身份證，雖然是唯一的，一些開發者喜歡用它來做主鍵，但是不是很建議哈。主鍵最好是毫無意義的一串獨立不重複的數字，比如UUID，又或者Auto_increment自增的主鍵，或者是雪花算法生成的主鍵等等;
4. 選擇合適的字段長度
先問大家一個問題，大家知道數據庫字段長度表示字符長度還是字節長度嘛？
其實在mysql中，varchar和char類型表示字符長度，而其他類型表示的長度都表示字節長度。比如char(10)表示字符長度是10，而bigint（4）表示顯示長度是4個字節，但是因為bigint實際長度是8個字節，所以bigint（4）的實際長度就是8個字節。
我們在設計表的時候，需要充分考慮一個字段的長度，比如一個用戶名字段（它的長度5~20個字符），你覺得應該設置多長呢？可以考慮設置為 username varchar（32）。字段長度一般設置為2的冪哈（也就是2的n次方）。』;
5，優先考慮邏輯刪除，而不是物理刪除
邏輯刪除：給數據添加一個字段，比如is_deleted，以標記該數據已經邏輯刪除。
物理刪除就是執行delete語句，如刪除account_no =『666』的賬戶信息SQL如下：
deletefromaccount_info_tabwhereaccount_no='666';
updateaccount_info_tabsetis_deleted=1whereaccount_no='666';
核心業務表 的數據不建議做物理刪除，只適合做狀態變更。
6. 每個表都需要添加這幾個通用字段如主鍵、create_time、modifed_time等
modifed_time/update_time: 修改時間，必須，更新記錄時，需要更新它
version : 數據記錄的版本號，用於樂觀鎖，非必須
7. 一張表的字段不宜過多
我們建表的時候，要牢記，一張表的字段不宜過多哈，一般儘量不要超過20個字段哈。筆者記得上個公司，有夥伴設計開戶表，加了五十多個字段。。。
如果一張表的字段過多，表中保存的數據可能就會很大，查詢效率就會很低。因此，一張表不要設計太多字段哈，如果業務需求，實在需要很多字段，可以把一張大的表，拆成多張小的表，它們的主鍵相同即可。
當表的字段數非常多時，可以將表分成兩張表，一張作為條件查詢表，一張作為詳細內容表 (主要是為了性能考慮)。
8. 儘可能使用not null定義字段
如果沒有特殊的理由， 一般都建議將字段定義為 NOT NULL 。
首先， NOT NULL 可以防止出現空指針問題。
其次，NULL值存儲也需要額外的空間的，它也會導致比較運算更為複雜，使優化器難以優化SQL。
如果將字段默認設置成一個空字符串或常量值並沒有什麼不同，且都不會影響到應用邏輯， 那就可以將這個字段設置為NOT NULL。
9. 設計表時，評估哪些字段需要加索引
首先，評估你的表數據量。如果你的表數據量只有一百幾十行，就沒有必要加索引。否則設計表的時候，如果有查詢條件的字段，一般就需要建立索引。但是索引也不能濫用：
索引也不要建得太多，一般單表索引個數不要超過5個。因為創建過多的索引，會降低寫得速度。
索引創建完後，還是要注意避免索引失效的情況，如使用mysql的內置函數，會導致索引失效的
索引過多的話，可以通過聯合索引的話方式來優化。然後的話，索引還有一些規則，如覆蓋索引，最左匹配原則等等。。
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;
對於這張表，很可能會有根據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. 不需要嚴格遵守 3NF，通過業務字段冗餘來減少表關聯
什麼是數據庫三範式（3NF），大家是否還有印象嗎？
第一範式：對屬性的原子性，要求屬性具有原子性，不可再分解；
第二範式：對記錄的唯一性，要求記錄有唯一標識，即實體的唯一性，即不存在部分依賴；
第三方式：對字段的冗餘性，要求任何字段不能由其他字段派生出來，它要求字段沒有冗餘，即不存在傳遞依賴；
我們設計表及其字段之間的關係, 應儘量滿足第三範式。但是有時候，可以適當冗餘，來提高效率。比如以下這張表
以上這張存放商品信息的基本表。總金額這個字段的存在，表明該表的設計不滿足第三範式，因為總金額可以由單價*數量得到，說明總金額是冗餘字段。但是，增加總金額這個冗餘字段，可以提高查詢統計的速度，這就是以空間換時間的作法。
當然，這只是個小例子哈，大家開發設計的時候，要結合具體業務分析哈。
11. 避免使用MySQL保留字
如果庫名、表名、字段名等屬性含有保留字時，SQL語句必須用反引號來引用屬性名稱，這將使得SQL語句書寫、SHELL腳本中變量的轉義等變得非常複雜。
因此，我們一般避免使用MySQL保留字，如select、interval、desc等等
12. 不搞外鍵關聯，一般都在代碼維護
外鍵，也叫FOREIGN KEY，它是用於將兩個表連接在一起的鍵。FOREIGN KEY是一個表中的一個字段（或字段集合），它引用另一個表中的PRIMARY KEY。它是用來保證數據的一致性和完整性的。
【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。
使用外鍵存在性能問題、並發死鎖問題、使用起來不方便等等。每次做DELETE或者UPDATE都必須考慮外鍵約束，會導致開發的時候很難受,測試數據造數據也不方便。
13. 一般都選擇INNODB存儲引擎
建表是需要選擇存儲引擎的，我們一般都選擇INNODB存儲引擎，除非讀寫比率小於1%, 才考慮使用MyISAM 。
有些小夥伴可能會有疑惑，不是還有MEMORY等其他存儲引擎嗎？什麼時候使用它呢？其實其他存儲引擎一般除了都建議在DBA的指導下使用。
我們來複習一下這MySQL這三種存儲引擎的對比區別吧：
14. 選擇合適統一的字符集。
數據庫庫、表、開發程序等都需要統一字符集，通常中英文環境用utf8。
MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。
utf8：支持中英文混合場景，國際通過，3個字節長度
utf8mb4: 完全兼容utf8，4個字節長度，一般存儲emoji表情需要用到它。
GBK ：支持中文，但是不支持國際通用字符集，2個字節長度
15. 如果你的數據庫字段是枚舉類型的，需要在comment注釋清楚
如果你設計的數據庫字段是枚舉類型的話，就需要在comment後面注釋清楚每個枚舉的意思，以便於維護
`session_status`varchar(2)COLLATEutf8_binNOTNULLCOMMENT'session授權態00：在線-授權態有效01：下線-授權態失效02：下線-主動退出03：下線-在別處被登錄'
`session_status`varchar(2)COLLATEutf8_binNOTNULLCOMMENT'session授權態'
並且，如果你的枚舉類型在未來的版本有增加修改的話，也需要同時維護到comment後面。
16.時間的類型選擇
我們設計表的時候，一般都需要加通用時間的字段，如create_time、modified_time等等。那對於時間的類型，我們該如何選擇呢？
對於MySQL來說，主要有date、datetime、time、timestamp 和 year。
date ：表示的日期值, 格式yyyy-mm-dd,範圍1000-01-01 到 9999-12-31，3字節
time ：表示的時間值，格式 hh:mm:ss，範圍-838:59:59 到 838:59:59，3字節
datetime：表示的日期時間值，格式yyyy-mm-dd hh:mm:ss，範圍1000-01-01 00:00:00到9999-12-31 23:59:59```,8字節，跟時區無關
timestamp：表示的時間戳值，格式為yyyymmddhhmmss，範圍1970-01-01 00:00:01到2038-01-19 03:14:07，4字節，跟時區有關
year：年份值，格式為yyyy。範圍1901到2155，1字節
推薦優先使用datetime類型來保存日期和時間，因為存儲範圍更大，且跟時區無關。
17. 不建議使用Stored procedure (包括存儲過程，觸發器) 。
觸發器，指一段代碼，當觸發某個事件時，自動執行這些代碼。使用場景：
實時監控某張表中的某個字段的更改而需要做出相應的處理。
注意不要濫用，否則會造成數據庫及應用程序的維護困難。
對於MYSQL來說，存儲過程、觸發器等還不是很成熟， 並沒有完善的出錯記錄處理，不建議使用。
18. 1:N 關係的設計
日常開發中，1對多的關係應該是非常常見的。比如一個班級有多個學生，一個部門有多個員工等等。這種的建表原則就是：在從表（N的這一方）創建一個字段，以字段作為外鍵指向主表（1的這一方）的主鍵。示意圖如下:
學生表是多（N）的一方，會有個字段class_id保存班級表的主鍵。當然，一班不加外鍵約束哈，只是單純保存這個關係而已。
有時候兩張表存在N:N關係時，我們應該消除這種關係。通過增加第三張表，把N:N修改為兩個 1:N。比如圖書和讀者，是一個典型的多對多的關係。一本書可以被多個讀者借，一個讀者又可以借多本書。我們就可以設計一個借書表，包含圖書表的主鍵，以及讀者的主鍵，以及借還標記等字段。
19. 大字段
設計表的時候，我們尤其需要關注一些大字段，即占用較多存儲空間的字段。比如用來記錄用戶評論的字段，又或者記錄博客內容的字段，又或者保存合同數據的字段。如果直接把表字段設計成text類型的話，就會浪費存儲空間，查詢效率也不好。
在MySQl中，這種方式保存的設計方案，其實是不太合理的。這種非常大的數據，可以保存到mongodb中，然後，在業務表保存對應mongodb的id即可。
這種設計思想類似於，我們表字段保存圖片時，為什麼不是保存圖片內容，而是直接保存圖片url即可。
20. 考慮是否需要分庫分表
分庫：就是一個數據庫分成多個數據庫，部署到不同機器。
我們在設計表的時候，其實可以提前估算一下，是否需要做分庫分表。比如一些用戶信息，未來可能數據量到達百萬設置千萬的話，就可以提前考慮分庫分表。
為什麼需要分庫分表: 數據量太大的話，SQL的查詢就會變慢。如果一個查詢SQL沒命中索引，千百萬數據量級別的表可能會拖垮整個數據庫。即使SQL命中了索引，如果表的數據量超過一千萬的話，查詢也是會明顯變慢的。這是因為索引一般是B+樹結構，數據千萬級別的話，B+樹的高度會增高，查詢就變慢啦。
分庫分表主要有水平拆分、垂直拆分的說法，拆分策略有range範圍、hash取模。而分庫分表主要有這些問題：
大家可以看下之前我這篇文章哈：我們為什麼要分庫分表？
21. sqL 編寫的一些優化經驗
查詢SQL儘量不要使用select *，而是select具體字段
如果知道查詢結果只有一條或者只要最大/最小一條記錄，建議用limit 1
使用where條件限定要查詢的數據，避免返回多餘的行
應儘量避免在 where子句中對字段進行表達式操作
應儘量避免在where 子句中使用!=或<>操作符
使用聯合索引時，注意索引列的順序，一般遵循最左匹配原則。
對查詢進行優化，應考慮在where 及 order by涉及的列上建立索引
