1.命名規(guī)范

數(shù)據(jù)庫表名做鹰、字段名徒仓、索引名等都需要命名規(guī)范，可讀性高(一般要求用英文)誊垢，讓別人一看命名掉弛，就知道這個字段表示什么意思症见。

比如一個表的賬號字段，反例如下：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number

• 表名殃饿、字段名必須使用小寫字母或者數(shù)字谋作，禁止使用數(shù)字開頭，禁止使用拼音乎芳，并且一般不使用英文縮寫遵蚜。
• 主鍵索引名為pk_字段名；唯一索引名為uk_字段名奈惑；普通索引名則為idx_字段名吭净。

2.選擇合適的字段類型

設(shè)計表時，我們需要選擇合適的字段類型肴甸，比如：

• 盡可能選擇存儲空間小的字段類型寂殉，就好像數(shù)字類型的，從tinyint原在、smallint友扰、int、bigint從左往右開始選擇
• 小數(shù)類型如金額庶柿，則選擇 decimal村怪，禁止使用 float 和 double。
• 如果存儲的字符串長度幾乎相等浮庐，使用 char 定長字符串類型甚负。
• varchar是可變長字符串，不預(yù)先分配存儲空間审残，長度不要超過5000梭域。
• 如果存儲的值太大，建議字段類型修改為text维苔，同時抽出單獨一張表碰辅，用主鍵與之對應(yīng)懂昂。
• 同一表中介时，所有varchar字段的長度加起來，不能大于65535. 如果有這樣的需求凌彬，請使用TEXT/LONGTEXT 類型沸柔。

3. 主鍵設(shè)計要合理

主鍵設(shè)計的話，最好不要與業(yè)務(wù)邏輯有所關(guān)聯(lián)铲敛。有些業(yè)務(wù)上的字段褐澎，比如身份證，雖然是唯一的伐蒋，一些開發(fā)者喜歡用它來做主鍵工三，但是不是很建議哈迁酸。主鍵最好是毫無意義的一串獨立不重復(fù)的數(shù)字，比如UUID俭正，又或者Auto_increment自增的主鍵奸鬓，或者是雪花算法生成的主鍵等等;

4. 選擇合適的字段長度

先問大家一個問題，大家知道數(shù)據(jù)庫字段長度表示字符長度還是字節(jié)長度嘛掸读？

其實在mysql中串远，varchar和char類型表示字符長度，而其他類型表示的長度都表示字節(jié)長度儿惫。比如char(10)表示字符長度是10澡罚，而bigint（4）表示顯示長度是4個字節(jié)，但是因為bigint實際長度是8個字節(jié)肾请，所以bigint（4）的實際長度就是8個字節(jié)留搔。

我們在設(shè)計表的時候，需要充分考慮一個字段的長度筐喳，比如一個用戶名字段（它的長度5~20個字符）催式，你覺得應(yīng)該設(shè)置多長呢？可以考慮設(shè)置為 username varchar（32）避归。字段長度一般設(shè)置為2的冪哈（也就是2的n次方）荣月。’;

5梳毙，優(yōu)先考慮邏輯刪除哺窄，而不是物理刪除

什么是物理刪除？什么是邏輯刪除账锹？

• 物理刪除：把數(shù)據(jù)從硬盤中刪除萌业，可釋放存儲空間
• 邏輯刪除：給數(shù)據(jù)添加一個字段，比如is_deleted奸柬，以標記該數(shù)據(jù)已經(jīng)邏輯刪除生年。

物理刪除就是執(zhí)行delete語句，如刪除account_no =‘666’的賬戶信息SQL如下：

delete from account_info_tab whereaccount_no ='666';

邏輯刪除呢廓奕，就是這樣：

update account_info_tab set is_deleted = 1 where account_no ='666';

為什么推薦用邏輯刪除抱婉，不推薦物理刪除呢？

• 為什么不推薦使用物理刪除桌粉，因為恢復(fù)數(shù)據(jù)很困難
• 物理刪除會使自增主鍵不再連續(xù)
• 核心業(yè)務(wù)表 的數(shù)據(jù)不建議做物理刪除蒸绩，只適合做狀態(tài)變更。

6. 每個表都需要添加這幾個通用字段如主鍵铃肯、create_time患亿、modifed_time等

表必備一般來說，或具備這幾個字段：

• id：主鍵押逼，一個表必須得有主鍵步藕，必須
• create_time：創(chuàng)建時間惦界，必須
• modifed_time/update_time: 修改時間，必須咙冗，更新記錄時表锻，需要更新它
• version : 數(shù)據(jù)記錄的版本號，用于樂觀鎖乞娄，非必須
• remark ：數(shù)據(jù)記錄備注瞬逊，非必須
• modified_by :修改人，非必須
• creator ：創(chuàng)建人仪或，非必須

7. 一張表的字段不宜過多

我們建表的時候确镊，要牢記，一張表的字段不宜過多哈范删，一般盡量不要超過20個字段哈蕾域。筆者記得上個公司，有伙伴設(shè)計開戶表到旦，加了五十多個字段旨巷。。添忘。

如果一張表的字段過多采呐，表中保存的數(shù)據(jù)可能就會很大，查詢效率就會很低搁骑。因此斧吐，一張表不要設(shè)計太多字段哈，如果業(yè)務(wù)需求仲器，實在需要很多字段煤率，可以把一張大的表，拆成多張小的表乏冀，它們的主鍵相同即可蝶糯。

當表的字段數(shù)非常多時，可以將表分成兩張表辆沦，一張作為條件查詢表昼捍，一張作為詳細內(nèi)容表 (主要是為了性能考慮)。

8. 盡可能使用not null定義字段

如果沒有特殊的理由众辨， 一般都建議將字段定義為 NOT NULL 端三。

為什么呢舷礼？

• 首先鹃彻， NOT NULL 可以防止出現(xiàn)空指針問題。
• 其次妻献，NULL值存儲也需要額外的空間的蛛株，它也會導(dǎo)致比較運算更為復(fù)雜团赁，使優(yōu)化器難以優(yōu)化SQL。
• NULL值有可能會導(dǎo)致索引失效
• 如果將字段默認設(shè)置成一個空字符串或常量值并沒有什么不同谨履，且都不會影響到應(yīng)用邏輯欢摄， 那就可以將這個字段設(shè)置為NOT NULL。

9. 設(shè)計表時笋粟，評估哪些字段需要加索引

首先怀挠，評估你的表數(shù)據(jù)量。如果你的表數(shù)據(jù)量只有一百幾十行害捕，就沒有必要加索引绿淋。否則設(shè)計表的時候曙痘，如果有查詢條件的字段炫欺，一般就需要建立索引。但是索引也不能濫用：

• 索引也不要建得太多扁凛，一般單表索引個數(shù)不要超過5個盾沫。因為創(chuàng)建過多的索引裁赠，會降低寫得速度。
• 區(qū)分度不高的字段赴精，不能加索引佩捞，如性別等
• 索引創(chuàng)建完后，還是要注意避免索引失效的情況蕾哟，如使用mysql的內(nèi)置函數(shù)失尖，會導(dǎo)致索引失效的
• 索引過多的話，可以通過聯(lián)合索引的話方式來優(yōu)化渐苏。然后的話掀潮，索引還有一些規(guī)則，如覆蓋索引琼富，最左匹配原則等等仪吧。。

假設(shè)你新建一張用戶表鞠眉，如下：

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

對于這張表薯鼠，很可能會有根據(jù)user_id或者name查詢用戶信息，并且械蹋，user_id是唯一的出皇。因此，你是可以給user_id加上唯一索引哗戈，name加上普通索引郊艘。

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10. 不需要嚴格遵守 3NF，通過業(yè)務(wù)字段冗余來減少表關(guān)聯(lián)

什么是數(shù)據(jù)庫三范式（3NF），大家是否還有印象嗎纱注？

• 第一范式：對屬性的原子性畏浆，要求屬性具有原子性，不可再分解狞贱；
• 第二范式：對記錄的唯一性刻获，要求記錄有唯一標識，即實體的唯一性瞎嬉，即不存在部分依賴蝎毡；
• 第三方式：對字段的冗余性，要求任何字段不能由其他字段派生出來氧枣，它要求字段沒有冗余顶掉，即不存在傳遞依賴；

我們設(shè)計表及其字段之間的關(guān)系, 應(yīng)盡量滿足第三范式挑胸。但是有時候痒筒，可以適當冗余，來提高效率茬贵。比如以下這張表

以上這張存放商品信息的基本表簿透。總金額這個字段的存在，表明該表的設(shè)計不滿足第三范式解藻，因為總金額可以由單價*數(shù)量得到老充，說明總金額是冗余字段。但是螟左，增加總金額這個冗余字段啡浊，可以提高查詢統(tǒng)計的速度，這就是以空間換時間的作法胶背。

當然巷嚣，這只是個小例子哈，大家開發(fā)設(shè)計的時候钳吟，要結(jié)合具體業(yè)務(wù)分析哈廷粒。

11. 避免使用MySQL保留字

如果庫名、表名红且、字段名等屬性含有保留字時坝茎，SQL語句必須用反引號來引用屬性名稱，這將使得SQL語句書寫暇番、SHELL腳本中變量的轉(zhuǎn)義等變得非常復(fù)雜嗤放。

因此，我們一般避免使用MySQL保留字壁酬，如select次酌、interval恨课、desc等等

12. 不搞外鍵關(guān)聯(lián)，一般都在代碼維護

什么是外鍵呢和措？

外鍵，也叫FOREIGN KEY蜕煌，它是用于將兩個表連接在一起的鍵派阱。FOREIGN KEY是一個表中的一個字段（或字段集合），它引用另一個表中的PRIMARY KEY斜纪。它是用來保證數(shù)據(jù)的一致性和完整性的贫母。

阿里的Java規(guī)范也有這么一條：

【強制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決盒刚。

我們?yōu)槭裁床煌扑]使用外鍵呢腺劣？

• 使用外鍵存在性能問題、并發(fā)死鎖問題因块、使用起來不方便等等橘原。每次做DELETE或者UPDATE都必須考慮外鍵約束，會導(dǎo)致開發(fā)的時候很難受,測試數(shù)據(jù)造數(shù)據(jù)也不方便涡上。
• 還有一個場景不能使用外鍵趾断，就是分庫分表。

13. 一般都選擇INNODB存儲引擎

建表是需要選擇存儲引擎的吩愧，我們一般都選擇INNODB存儲引擎芋酌，除非讀寫比率小于1%, 才考慮使用MyISAM 。

有些小伙伴可能會有疑惑雁佳，不是還有MEMORY等其他存儲引擎嗎脐帝？什么時候使用它呢？其實其他存儲引擎一般除了都建議在DBA的指導(dǎo)下使用糖权。

我們來復(fù)習一下這MySQL這三種存儲引擎的對比區(qū)別吧：

14. 選擇合適統(tǒng)一的字符集堵腹。

數(shù)據(jù)庫庫、表星澳、開發(fā)程序等都需要統(tǒng)一字符集秸滴，通常中英文環(huán)境用utf8。

MySQL支持的字符集有utf8募判、utf8mb4荡含、GBK、latin1等届垫。

• utf8：支持中英文混合場景释液，國際通過，3個字節(jié)長度
• utf8mb4: 完全兼容utf8装处，4個字節(jié)長度误债，一般存儲emoji表情需要用到它浸船。
• GBK ：支持中文，但是不支持國際通用字符集寝蹈，2個字節(jié)長度
• latin1：MySQL默認字符集李命，1個字節(jié)長度

15. 如果你的數(shù)據(jù)庫字段是枚舉類型的，需要在comment注釋清楚

如果你設(shè)計的數(shù)據(jù)庫字段是枚舉類型的話箫老，就需要在comment后面注釋清楚每個枚舉的意思封字，以便于維護

正例如下：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授權(quán)態(tài) 00：在線-授權(quán)態(tài)有效 01：下線-授權(quán)態(tài)失效 02：下線-主動退出 03：下線-在別處被登錄'

反例：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授權(quán)態(tài)'

并且，如果你的枚舉類型在未來的版本有增加修改的話耍鬓，也需要同時維護到comment后面阔籽。

16.時間的類型選擇

我們設(shè)計表的時候，一般都需要加通用時間的字段牲蜀，如create_time笆制、modified_time等等。那對于時間的類型涣达，我們該如何選擇呢在辆？

對于MySQL來說，主要有date度苔、datetime开缎、time、timestamp 和 year林螃。

• date ：表示的日期值, 格式yyyy-mm-dd,范圍1000-01-01 到 9999-12-31奕删，3字節(jié)
• time ：表示的時間值，格式 hh:mm:ss疗认，范圍-838:59:59 到 838:59:59完残，3字節(jié)
• datetime：表示的日期時間值，格式yyyy-mm-dd hh:mm:ss横漏，范圍1000-01-01 00:00:00到9999-12-31 23:59:59```,8字節(jié)谨设，跟時區(qū)無關(guān)
• timestamp：表示的時間戳值，格式為yyyymmddhhmmss缎浇，范圍1970-01-01 00:00:01到2038-01-19 03:14:07扎拣，4字節(jié)，跟時區(qū)有關(guān)
• year：年份值素跺，格式為yyyy二蓝。范圍1901到2155，1字節(jié)

推薦優(yōu)先使用datetime類型來保存日期和時間指厌，因為存儲范圍更大刊愚，且跟時區(qū)無關(guān)。

17. 不建議使用Stored procedure (包括存儲過程踩验，觸發(fā)器) 鸥诽。

什么是存儲過程

已預(yù)編譯為一個可執(zhí)行過程的一個或多個SQL語句商玫。

什么是觸發(fā)器

觸發(fā)器，指一段代碼牡借，當觸發(fā)某個事件時拳昌，自動執(zhí)行這些代碼。使用場景：

• 可以通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改钠龙。
• 實時監(jiān)控某張表中的某個字段的更改而需要做出相應(yīng)的處理炬藤。
• 例如可以生成某些業(yè)務(wù)的編號。
• 注意不要濫用俊鱼，否則會造成數(shù)據(jù)庫及應(yīng)用程序的維護困難刻像。

對于MYSQL來說畅买，存儲過程并闲、觸發(fā)器等還不是很成熟， 并沒有完善的出錯記錄處理谷羞，不建議使用帝火。

18. 1:N 關(guān)系的設(shè)計

日常開發(fā)中，1對多的關(guān)系應(yīng)該是非常常見的湃缎。比如一個班級有多個學(xué)生犀填，一個部門有多個員工等等。這種的建表原則就是：在從表（N的這一方）創(chuàng)建一個字段嗓违，以字段作為外鍵指向主表（1的這一方）的主鍵九巡。示意圖如下:

學(xué)生表是多（N）的一方，會有個字段class_id保存班級表的主鍵蹂季。當然冕广，一班不加外鍵約束哈，只是單純保存這個關(guān)系而已偿洁。

有時候兩張表存在N:N關(guān)系時撒汉，我們應(yīng)該消除這種關(guān)系。通過增加第三張表涕滋，把N:N修改為兩個 1:N睬辐。比如圖書和讀者，是一個典型的多對多的關(guān)系宾肺。一本書可以被多個讀者借溯饵，一個讀者又可以借多本書。我們就可以設(shè)計一個借書表锨用，包含圖書表的主鍵瓣喊，以及讀者的主鍵，以及借還標記等字段黔酥。

19. 大字段

設(shè)計表的時候藻三，我們尤其需要關(guān)注一些大字段洪橘，即占用較多存儲空間的字段。比如用來記錄用戶評論的字段棵帽，又或者記錄博客內(nèi)容的字段熄求，又或者保存合同數(shù)據(jù)的字段。如果直接把表字段設(shè)計成text類型的話逗概，就會浪費存儲空間弟晚，查詢效率也不好。

在MySQl中逾苫，這種方式保存的設(shè)計方案卿城，其實是不太合理的。這種非常大的數(shù)據(jù)铅搓，可以保存到mongodb中瑟押，然后，在業(yè)務(wù)表保存對應(yīng)mongodb的id即可星掰。

這種設(shè)計思想類似于多望，我們表字段保存圖片時，為什么不是保存圖片內(nèi)容氢烘，而是直接保存圖片url即可怀偷。

20. 考慮是否需要分庫分表

什么是分庫分表呢？

• 分庫：就是一個數(shù)據(jù)庫分成多個數(shù)據(jù)庫播玖，部署到不同機器椎工。

• 分表：就是一個數(shù)據(jù)庫表分成多個表。

我們在設(shè)計表的時候蜀踏，其實可以提前估算一下维蒙，是否需要做分庫分表。比如一些用戶信息脓斩，未來可能數(shù)據(jù)量到達百萬設(shè)置千萬的話木西，就可以提前考慮分庫分表。

為什么需要分庫分表: 數(shù)據(jù)量太大的話随静，SQL的查詢就會變慢八千。如果一個查詢SQL沒命中索引，千百萬數(shù)據(jù)量級別的表可能會拖垮整個數(shù)據(jù)庫燎猛。即使SQL命中了索引恋捆，如果表的數(shù)據(jù)量超過一千萬的話，查詢也是會明顯變慢的重绷。這是因為索引一般是B+樹結(jié)構(gòu)沸停，數(shù)據(jù)千萬級別的話，B+樹的高度會增高昭卓，查詢就變慢啦愤钾。

分庫分表主要有水平拆分瘟滨、垂直拆分的說法，拆分策略有range范圍能颁、hash取模杂瘸。而分庫分表主要有這些問題：

• 事務(wù)問題
• 跨庫關(guān)聯(lián)
• 排序問題
• 分頁問題
• 分布式ID

21. sqL 編寫的一些優(yōu)化經(jīng)驗

最后的話，跟大家聊來一些寫SQL的經(jīng)驗吧：

• 查詢SQL盡量不要使用select *伙菊，而是select具體字段
• 如果知道查詢結(jié)果只有一條或者只要最大/最小一條記錄败玉，建議用limit 1
• 應(yīng)盡量避免在where子句中使用or來連接條件
• 注意優(yōu)化limit深分頁問題
• 使用where條件限定要查詢的數(shù)據(jù)，避免返回多余的行
• 盡量避免在索引列上使用mysql的內(nèi)置函數(shù)
• 應(yīng)盡量避免在 where子句中對字段進行表達式操作
• 應(yīng)盡量避免在where 子句中使用!=或<>操作符
• 使用聯(lián)合索引時镜硕，注意索引列的順序运翼，一般遵循最左匹配原則。
• 對查詢進行優(yōu)化兴枯，應(yīng)考慮在where 及 order by涉及的列上建立索引
• 如果插入數(shù)據(jù)過多血淌，考慮批量插入
• 在適當?shù)臅r候，使用覆蓋索引
• 使用explain 分析你SQL的計劃