有声小说下载_21個(gè)MySQL表設計的經(jīng)驗準則| 成都西華升騰科技有限公司

MySQL知識大全

21個(gè)MySQL表設計的經(jīng)驗準則

### 前言
作為后端開(kāi)發(fā)，我們經(jīng)常需要設計數據庫表。整理了21個(gè)設計MySQL表的經(jīng)驗準則，分享給大家，大家看完一定會(huì )有幫助的。

### 1.命名規范
數據庫表名、字段名、索引名等都需要命名規范，可讀性高(一般要求用英文)，讓別人一看命名，就知道這個(gè)字段表示什么意思。

比如一個(gè)表的賬號字段，反例如下：

acc_no,1_acc_no,zhanghao
正例：

account_no,account_number
表名、字段名必須使用小寫(xiě)字母或者數字，禁止使用數字開(kāi)頭，禁止使用拼音，并且一般不使用英文縮寫(xiě)。
主鍵索引名為pk_字段名；唯一索引名為uk_字段名；普通索引名則為idx_字段名。
### 2.選擇合適的字段類(lèi)型
設計表時(shí)，我們需要選擇合適的字段類(lèi)型，比如：

- 盡可能選擇存儲空間小的字段類(lèi)型，就好像數字類(lèi)型的，從tinyint、smallint、int、bigint從左往右開(kāi)始選擇
- 小數類(lèi)型如金額，則選擇 decimal，禁止使用 float 和 double。
- 如果存儲的字符串長(cháng)度幾乎相等，使用 char 定長(cháng)字符串類(lèi)型。
- varchar是可變長(cháng)字符串，不預先分配存儲空間，長(cháng)度不要超過(guò)5000。
- 如果存儲的值太大，建議字段類(lèi)型修改為text，同時(shí)抽出單獨一張表，用主鍵與之對應。
- 同一表中，所有varchar字段的長(cháng)度加起來(lái)，不能大于65535. 如果有這樣的需求，請使用TEXT/LONGTEXT 類(lèi)型。

### 3. 主鍵設計要合理
主鍵設計的話(huà)，最好不要與業(yè)務(wù)邏輯有所關(guān)聯(lián)。有些業(yè)務(wù)上的字段，比如身份證，雖然是唯一的，一些開(kāi)發(fā)者喜歡用它來(lái)做主鍵，但是不是很建議哈。主鍵最好是毫無(wú)意義的一串獨立不重復的數字，比如UUID，又或者Auto_increment自增的主鍵，或者是雪花算法生成的主鍵等等;

圖片
### 4. 選擇合適的字段長(cháng)度
先問(wèn)大家一個(gè)問(wèn)題，大家知道數據庫字段長(cháng)度表示字符長(cháng)度還是字節長(cháng)度嘛？

> 其實(shí)在mysql中，varchar和char類(lèi)型表示字符長(cháng)度，而其他類(lèi)型表示的長(cháng)度都表示字節長(cháng)度。比如char(10)表示字符長(cháng)度是10，而bigint（4）表示顯示長(cháng)度是4個(gè)字節，但是因為bigint實(shí)際長(cháng)度是8個(gè)字節，所以bigint（4）的實(shí)際長(cháng)度就是8個(gè)字節。

我們在設計表的時(shí)候，需要充分考慮一個(gè)字段的長(cháng)度，比如一個(gè)用戶(hù)名字段（它的長(cháng)度5~20個(gè)字符），你覺(jué)得應該設置多長(cháng)呢？可以考慮設置為 username varchar（32）。字段長(cháng)度一般設置為2的冪哈（也就是2的n次方）?！?

5，優(yōu)先考慮邏輯刪除，而不是物理刪除
什么是物理刪除？什么是邏輯刪除？

物理刪除：把數據從硬盤(pán)中刪除，可釋放存儲空間
邏輯刪除：給數據添加一個(gè)字段，比如is_deleted，以標記該數據已經(jīng)邏輯刪除。
物理刪除就是執行delete語(yǔ)句，如刪除account_no =‘666’的賬戶(hù)信息SQL如下：

delete from account_info_tab whereaccount_no ='666';
邏輯刪除呢，就是這樣：

update account_info_tab set is_deleted = 1 where account_no ='666';
	
為什么推薦用邏輯刪除，不推薦物理刪除呢？

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

### 6. 每個(gè)表都需要添加這幾個(gè)通用字段如主鍵、create_time、modifed_time等
表必備一般來(lái)說(shuō)，或具備這幾個(gè)字段：

- id：主鍵，一個(gè)表必須得有主鍵，必須
- create_time：創(chuàng  )建時(shí)間，必須
- modifed_time/update_time: 修改時(shí)間，必須，更新記錄時(shí)，需要更新它
- version : 數據記錄的版本號，用于樂(lè )觀(guān)鎖，非必須
- remark ：數據記錄備注，非必須
- modified_by :修改人，非必須
- creator ：創(chuàng  )建人，非必須

### 7. 一張表的字段不宜過(guò)多
我們建表的時(shí)候，要牢記，一張表的字段不宜過(guò)多哈，一般盡量不要超過(guò)20個(gè)字段哈。筆者記得上個(gè)公司，有伙伴設計開(kāi)戶(hù)表，加了五十多個(gè)字段。。。

如果一張表的字段過(guò)多，表中保存的數據可能就會(huì )很大，查詢(xún)效率就會(huì )很低。因此，一張表不要設計太多字段哈，如果業(yè)務(wù)需求，實(shí)在需要很多字段，可以把一張大的表，拆成多張小的表，它們的主鍵相同即可。

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

### 8. 盡可能使用not null定義字段
如果沒(méi)有特殊的理由， 一般都建議將字段定義為 NOT NULL 。

為什么呢？

- 首先， NOT NULL 可以防止出現空指針問(wèn)題。
- 其次，NULL值存儲也需要額外的空間的，它也會(huì )導致比較運算更為復雜，使優(yōu)化器難以?xún)?yōu)化SQL。NULL值有可能會(huì )導致索引失效
- 如果將字段默認設置成一個(gè)空字符串或常量值并沒(méi)有什么不同，且都不會(huì )影響到應用邏輯， 那就可以將這個(gè)字段設置為NOT NULL。

### 9. 設計表時(shí)，評估哪些字段需要加索引
首先，評估你的表數據量。如果你的表數據量只有一百幾十行，就沒(méi)有必要加索引。否則設計表的時(shí)候，如果有查詢(xún)條件的字段，一般就需要建立索引。但是索引也不能濫用：

- 索引也不要建得太多，一般單表索引個(gè)數不要超過(guò)5個(gè)。因為創(chuàng  )建過(guò)多的索引，會(huì )降低寫(xiě)得速度。
- 區分度不高的字段，不能加索引，如性別等
- 索引創(chuàng  )建完后，還是要注意避免索引失效的情況，如使用mysql的內置函數，會(huì )導致索引失效的
- 索引過(guò)多的話(huà)，可以通過(guò)聯(lián)合索引的話(huà)方式來(lái)優(yōu)化。然后的話(huà)，索引還有一些規則，如覆蓋索引，最左匹配原則等等。。
假設你新建一張用戶(hù)表，如下：

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;
對于這張表，很可能會(huì )有根據user_id或者name查詢(xún)用戶(hù)信息，并且，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，通過(guò)業(yè)務(wù)字段冗余來(lái)減少表關(guān)聯(lián)
什么是數據庫三范式（3NF），大家是否還有印象嗎？

- 第一范式：對屬性的原子性，要求屬性具有原子性，不可再分解；
- 第二范式：對記錄的唯一性，要求記錄有唯一標識，即實(shí)體的唯一性，即不存在部分依賴(lài)；
- 第三方式：對字段的冗余性，要求任何字段不能由其他字段派生出來(lái)，它要求字段沒(méi)有冗余，即不存在傳遞依賴(lài)；
我們設計表及其字段之間的關(guān)系, 應盡量滿(mǎn)足第三范式。但是有時(shí)候，可以適當冗余，來(lái)提高效率。比如以下這張表

商品名稱(chēng)	商品型號	單價(jià)	數量	總金額
手機	華為	8000	5	40000
以上這張存放商品信息的基本表?？偨痤~這個(gè)字段的存在，表明該表的設計不滿(mǎn)足第三范式，因為總金額可以由單價(jià)*數量得到，說(shuō)明總金額是冗余字段。但是，增加總金額這個(gè)冗余字段，可以提高查詢(xún)統計的速度，這就是以空間換時(shí)間的作法。

當然，這只是個(gè)小例子哈，大家開(kāi)發(fā)設計的時(shí)候，要結合具體業(yè)務(wù)分析哈。

### 11. 避免使用MySQL保留字
如果庫名、表名、字段名等屬性含有保留字時(shí)，SQL語(yǔ)句必須用反引號來(lái)引用屬性名稱(chēng)，這將使得SQL語(yǔ)句書(shū)寫(xiě)、SHELL腳本中變量的轉義等變得非常復雜。

因此，我們一般避免使用MySQL保留字，如select、interval、desc等等

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

> 外鍵，也叫FOREIGN KEY，它是用于將兩個(gè)表連接在一起的鍵。FOREIGN KEY是一個(gè)表中的一個(gè)字段（或字段集合），它引用另一個(gè)表中的PRIMARY KEY。它是用來(lái)保證數據的一致性和完整性的。

阿里的Java規范也有這么一條：

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

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

> - 使用外鍵存在性能問(wèn)題、并發(fā)死鎖問(wèn)題、使用起來(lái)不方便等等。每次做DELETE或者UPDATE都必須考慮外鍵約束，會(huì )導致開(kāi)發(fā)的時(shí)候很難受,測試數據造數據也不方便。
- 還有一個(gè)場(chǎng)景不能使用外鍵，就是分庫分表。

### 13. 一般都選擇INNODB存儲引擎
建表是需要選擇存儲引擎的，我們一般都選擇INNODB存儲引擎，除非讀寫(xiě)比率小于1%, 才考慮使用MyISAM 。

有些小伙伴可能會(huì )有疑惑，不是還有MEMORY等其他存儲引擎嗎？什么時(shí)候使用它呢？其實(shí)其他存儲引擎一般除了都建議在DBA的指導下使用。

我們來(lái)復習一下這MySQL這三種存儲引擎的對比區別吧：

| 特性  | INNODB  | MyISAM  |MEMORY   |
| ------------ | ------------ | ------------ | ------------ |
| 事務(wù)安全  |支持   |無(wú)   | 無(wú)  |
| 存儲限制  | 64TB  | 無(wú)  |  無(wú) |
| 空間使用  | 高  | 低  | 低  |
|內存使用   | 高  | 低  | 高  |
| 插入數據速度  |低   |高   | 高  |
|是否支持外鍵   |支持   |無(wú)  | 無(wú)  |

### 14. 選擇合適統一的字符集。

數據庫庫、表、開(kāi)發(fā)程序等都需要統一字符集，通常中英文環(huán)境用utf8。

MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。

- utf8：支持中英文混合場(chǎng)景，國際通過(guò)，3個(gè)字節長(cháng)度
- utf8mb4:   完全兼容utf8，4個(gè)字節長(cháng)度，一般存儲emoji表情需要用到它。
- GBK ：支持中文，但是不支持國際通用字符集，2個(gè)字節長(cháng)度
- latin1：MySQL默認字符集，1個(gè)字節長(cháng)度

### 15. 如果你的數據庫字段是枚舉類(lèi)型的，需要在comment注釋清楚
如果你設計的數據庫字段是枚舉類(lèi)型的話(huà)，就需要在comment后面注釋清楚每個(gè)枚舉的意思，以便于維護

正例如下：

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

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授權態(tài)'
并且，如果你的枚舉類(lèi)型在未來(lái)的版本有增加修改的話(huà)，也需要同時(shí)維護到comment后面。

### 16.時(shí)間的類(lèi)型選擇
我們設計表的時(shí)候，一般都需要加通用時(shí)間的字段，如create_time、modified_time等等。那對于時(shí)間的類(lèi)型，我們該如何選擇呢？

對于MySQL來(lái)說(shuō)，主要有date、datetime、time、timestamp 和 year。

- date ：表示的日期值, 格式yyyy-mm-dd,范圍1000-01-01 到 9999-12-31，3字節
- time ：表示的時(shí)間值，格式 hh:mm:ss，范圍-838:59:59 到 838:59:59，3字節
- datetime：表示的日期時(shí)間值，格式yyyy-mm-dd hh:mm:ss，范圍1000-01-01 00:00:00到9999-12-31 23:59:59```,8字節，跟時(shí)區無(wú)關(guān)
- timestamp：表示的時(shí)間戳值，格式為yyyymmddhhmmss，范圍1970-01-01 00:00:01到2038-01-19 03:14:07，4字節，跟時(shí)區有關(guān)
- year：年份值，格式為yyyy。范圍1901到2155，1字節
推薦優(yōu)先使用datetime類(lèi)型來(lái)保存日期和時(shí)間，因為存儲范圍更大，且跟時(shí)區無(wú)關(guān)。

### 17. 不建議使用Stored procedure (包括存儲過(guò)程，觸發(fā)器) 。
什么是存儲過(guò)程

已預編譯為一個(gè)可執行過(guò)程的一個(gè)或多個(gè)SQL語(yǔ)句。

#### 什么是觸發(fā)器

觸發(fā)器，指一段代碼，當觸發(fā)某個(gè)事件時(shí)，自動(dòng)執行這些代碼。使用場(chǎng)景：

- 可以通過(guò)數據庫中的相關(guān)表實(shí)現級聯(lián)更改。
- 實(shí)時(shí)監控某張表中的某個(gè)字段的更改而需要做出相應的處理。
- 例如可以生成某些業(yè)務(wù)的編號。
- 注意不要濫用，否則會(huì )造成數據庫及應用程序的維護困難。
對于MYSQL來(lái)說(shuō)，存儲過(guò)程、觸發(fā)器等還不是很成熟， 并沒(méi)有完善的出錯記錄處理，不建議使用。

### 18. 1:N 關(guān)系的設計
日常開(kāi)發(fā)中，1對多的關(guān)系應該是非常常見(jiàn)的。比如一個(gè)班級有多個(gè)學(xué)生，一個(gè)部門(mén)有多個(gè)員工等等。這種的建表原則就是：在從表（N的這一方）創(chuàng  )建一個(gè)字段，以字段作為外鍵指向主表（1的這一方）的主鍵。示意圖如下:

![](/upload/picture/2022-09-30/upload_3227207511cfc190af00e42b88d068ae.png)

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

有時(shí)候兩張表存在N:N關(guān)系時(shí)，我們應該消除這種關(guān)系。通過(guò)增加第三張表，把N:N修改為兩個(gè) 1:N。比如圖書(shū)和讀者，是一個(gè)典型的多對多的關(guān)系。一本書(shū)可以被多個(gè)讀者借，一個(gè)讀者又可以借多本書(shū)。我們就可以設計一個(gè)借書(shū)表，包含圖書(shū)表的主鍵，以及讀者的主鍵，以及借還標記等字段。

### 19. 大字段
設計表的時(shí)候，我們尤其需要關(guān)注一些大字段，即占用較多存儲空間的字段。比如用來(lái)記錄用戶(hù)評論的字段，又或者記錄博客內容的字段，又或者保存合同數據的字段。如果直接把表字段設計成text類(lèi)型的話(huà)，就會(huì )浪費存儲空間，查詢(xún)效率也不好。

在MySQl中，這種方式保存的設計方案，其實(shí)是不太合理的。這種非常大的數據，可以保存到mongodb中，然后，在業(yè)務(wù)表保存對應mongodb的id即可。

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

### 20. 考慮是否需要分庫分表
#### 什么是分庫分表呢？

- 分庫：就是一個(gè)數據庫分成多個(gè)數據庫，部署到不同機器。
![](/upload/picture/2022-09-30/upload_43ce55e50468bf24ec507d2f74ed6c74.png)
-  分表：就是一個(gè)數據庫表分成多個(gè)表。分表：就是一個(gè)數據庫表分成多個(gè)表。
![](/upload/picture/2022-09-30/upload_3bd6d4415da158eba31cfdda600ec958.png)

我們在設計表的時(shí)候，其實(shí)可以提前估算一下，是否需要做分庫分表。比如一些用戶(hù)信息，未來(lái)可能數據量到達百萬(wàn)設置千萬(wàn)的話(huà)，就可以提前考慮分庫分表。

> 為什么需要分庫分表: 數據量太大的話(huà)，SQL的查詢(xún)就會(huì )變慢。如果一個(gè)查詢(xún)SQL沒(méi)命中索引，千百萬(wàn)數據量級別的表可能會(huì )拖垮整個(gè)數據庫。即使SQL命中了索引，如果表的數據量超過(guò)一千萬(wàn)的話(huà)，查詢(xún)也是會(huì )明顯變慢的。這是因為索引一般是B+樹(shù)結構，數據千萬(wàn)級別的話(huà)，B+樹(shù)的高度會(huì )增高，查詢(xún)就變慢啦。

分庫分表主要有水平拆分、垂直拆分的說(shuō)法，拆分策略有range范圍、hash取模。而分庫分表主要有這些問(wèn)題：

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

大家可以看下之前我這篇文章哈：[我們?yōu)槭裁匆謳旆直?？](http://www.555ad.cn/p/453.html "我們?yōu)槭裁匆謳旆直恚?)

### 21. sqL 編寫(xiě)的一些優(yōu)化經(jīng)驗
最后的話(huà)，跟大家聊來(lái)一些寫(xiě)SQL的經(jīng)驗吧：

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

98国产在线视频福利_欧亚v视频日韩一区二区_国产在线精彩视频_亚洲伊人久久综合影院_亚洲欧美高清激情精品一区_少妇熟女天堂网av_午夜福利日本专区_中美女子成人毛片_大荫蒂毛茸茸视频国产_小14萝裸体自慰洗澡大尺度

購物車(chē)

0 ITEMS ON YOUR CART

MySQL知識大全

21個(gè)MySQL表設計的經(jīng)驗準則

網(wǎng)站導航