文章目錄

範式與反範式
- 範式
- - 第一範式
  - 第二範式
  - 第三範式
  - 第二範式 VS 第三範式
  - 設計符合 2NF 的表
  - 範式優缺點
- 反範式
範式 VS 反範式
MySQL 使用原則和設計規範
- 基本設置規則
- - 線上系統轉不區分大小寫
- 不建議使用的功能
規範命名
InnoDB 表的注意事項
備份表/臨時表等常見表的設計規範
欄位設計要求
- int(3) int(5) 區別
- 浮點數與定點數區別
- N 解釋
- Char 與 Varchar 類型

範式與反範式

範式

範式是關係資料庫理論的基礎，也是我們在設計資料庫結構過程中所要遵循的規則和指導方法。資料庫的設計範式是資料庫設計所需要滿足的規範。

目前關係資料庫有六種範式：第一範式（1NF）、第二範式（2NF）、第三範式（3NF）、巴斯-科德範式（BCNF）、第四範式（4NF）和第五範式（5NF，又稱完美範式）。

滿足最低要求的叫第一範式，簡稱 1NF。在第一範式基礎上進一步滿足一些要求的為第二範式，簡稱 2NF。其餘依此類推。各種範式呈遞次規範，越高的範式資料庫冗餘越小。通常所用到的只是前三個範式，即：第一範式（1NF），第二範式（2NF），第三範式（3NF）。

第一範式

第一範式無重複的列，表中的每一列都是拆分的基本數據項，即列不能夠再拆分成其他幾列，強調的是列的原子性.。

如果在實際場景中，一個聯繫人有家庭電話和公司電話，那麼以「姓名、性別、電話」為表頭的表結構就沒有達到 1NF。要符合 1NF 我們只需把電話列拆分，讓表頭變為姓名、性別、家庭電話、公司電話即可。

第二範式

第二範式屬性完全依賴於主鍵，首先要滿足它符合 1NF，另外還需要包含兩部分內容

表必須有一個主鍵；
沒有包含在主鍵中的列必須完全依賴於主鍵，而不能只依賴於主鍵的一部分。即要求實體的屬性完全依賴於主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性。

第三範式

第三範式屬性不傳遞依賴於其他非主屬性，首先需要滿足 2NF，另外非主鍵列必須直接依賴於主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況。

第二範式 VS 第三範式

第二範式：非主鍵列是否依賴主鍵（包括一列通過某一列間接依賴主鍵），要是有依賴關係就是第二範式；
第三範式：非主鍵列是否直接依賴主鍵，不能是那種通過傳遞關係的依賴。要是符合這種依賴關係就是第三範式。

通過對前三個範式的了解，我們知道 3NF 是 2NF 的子集，2NF 是 1NF 的子集。

設計符合 2NF 的表

以訂單信息表為例，講述如何設計一個符合 2NF 的表

首先，我們看原始的訂單信息表，如下圖所示

圖中，以訂單編號和商品編號作為聯合主鍵，商品名稱、單位、價格等信息不與主鍵相關，只與編號相關，違反了第二範式。應該對訂單信息表進行拆分，商品信息單獨一張表，訂單項目一張表，如下所示，拆分分成 3 張表。

包含客戶信息的訂單信息表
包含商品詳情的商品信息表
包含訂單詳情的訂單詳情表

範式優缺點

【優點】

避免數據冗餘，減少維護數據完整性的麻煩；
減少資料庫的空間；
數據變更速度快

【缺點】

按照範式的規範設計的表，等級越高的範式設計出來的表數量越多。
獲取數據時，表關聯過多，性能較差。

表的數量越多，查詢所需要的時間越多。也就是說所用的範式越高，對數據操作的性能越低。

反範式

範式是普適的規則，滿足大多數的業務場景的需求。對於一些特殊的業務場景，範式設計的表，無法滿足性能的需求。此時，就需要根據業務場景，在範式的基礎之上進行靈活設計，也就是反範式設計。

反範式設計主要從三方面考慮：

業務場景
響應時間
欄位冗餘

反範式設計就是用空間來換取時間，提高業務場景的響應時間，減少多表關聯。主要的優點如下

允許適當的數據冗餘，業務場景中需要的數據幾乎都可以在一張表上顯示，避免關聯
可以設計有效的索引
範式 VS 反範式

範式化模型：

數據沒有冗餘，更新容易
當表的數量比較多，查詢數據需要多表關聯時，會導致查詢性能低下

反範式化模型：

冗餘將帶來很好的讀取性能，因為不需要 join 很多表
雖然需要維護冗餘數據，但是對磁碟空間的消耗是可以接受的

MySQL 使用原則和設計規範

聊完範式，接下來我們看看 MySQL 使用中的一些使用原則和設計規範。

MySQL 雖然具有很多特性並提供了很多功能，但是有些特性會嚴重影響它的性能，比如，在資料庫里進行計算，寫大事務、大 SQL、存儲大欄位等。

想要發揮 MySQL 的最佳性能，需要遵循 3 個基本使用原則

首先是需要讓 MySQL 回歸存儲的基本職能：MySQL 資料庫只用於數據的存儲，不進行數據的複雜計算，不承載業務邏輯，確保存儲和計算分離
其次是查詢數據時，儘量單表查詢，減少跨庫查詢和多表關聯
還有就是要杜絕大事務、大 SQL、大批量、大欄位等一系列性能殺手。

大事務，運行步驟較多，涉及的表和欄位較多，容易造成資源的爭搶，甚至形成死鎖。一旦事務回滾，會導致資源占用時間過長

大 SQL，複雜的 SQL 意味著過多的表的關聯，MySQL 資料庫處理關聯超過 3 張表以上的 SQL 時，占用資源多，性能低下
大批量，意味著多條 SQL 一次性執行完成，必須確保進行充分的測試，並且在業務低峰時段或者非業務時段執行
大欄位，blob、text 等大欄位，儘量少用。必須要用時，儘量與主業務表分離，減少對這類欄位的檢索和更新

基本設置規則

必須指定默認存儲引擎為 InnoDB，並且禁用 MyISAM 存儲引擎，隨著 MySQL 8.0 版本的發布，所有的數據字典表都已經轉換成了 InnoDB，MyISAM 存儲引擎已成為了歷史。
默認字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含個別特殊字符，新版本的 UTF8mb4 包含所有字符，官方強烈建議使用此字符集。
關閉區分大小寫功能。設置 lower_case_tables_name=1，即可關閉區分大小寫功能，即大寫字母 T 和小寫字母 t 一樣

線上系統轉不區分大小寫

如何讓系統中區分大小寫的庫錶轉換為不區分大小寫的庫表呢？因為要修改底層數據，還是比較麻煩的，操作步驟如下。

MySQL dump 導出資料庫。
修改參數 lower_case_tables_name=1。
導入備份數據時，必須停止資料庫，停止業務，影響非常大。
開啟 per-table 表空間，開啟後，每張業務表會單獨創建一個獨立於系統表空間的表空間，便於空間的回收，數據的遷移

不建議使用的功能

存儲過程、觸發器、視圖、event。為了存儲計算分離，這類功能儘量在程序中實現。這些功能非常不完整，調試、排錯、監控都非常困難，相關數據字典也不完善，存在潛在的風險。一般在生產資料庫中，禁止使用。
lob、text、enum、set。這些欄位類型，在 MySQL 資料庫的檢索性能不高，很難使用索引進行優化。如果必須使用這些功能，一般採取特殊的結構設計，或者與程序結合使用其他的欄位類型替代。比如：set 可以使用整型（0，1，2，3）、注釋功能和程序的檢查功能集合替代。

規範命名

命名規範如下，命名時的字符取值範圍為：az，09 和 _（下畫線）。

所有表名小寫，不允許駝峰式命名；
允許使用 -（橫線）和（空格）；如下圖所示，當使用 -（橫線），後台默認會轉化成 @002d；
不允許使用其他特殊字符作為名稱，減少潛在風險。

資料庫庫名的命名規則必須遵循「見名知意」的原則，即庫名規則為「資料庫類型代碼 + 項目簡稱 + 識別代碼 + 序號」。

表名的命名規則分為：

單表僅使用 a~z、_；
分表名稱為「表名_編號」；
業務表名代表用途、內容：子系統簡稱_業務含義_後綴。

常見業務表類型有：

臨時表，tmp；
備份表，bak；
字典表，dic；
日誌表，log。

欄位名精確，遵循「見名知意」的原則，格式：名稱_後綴。

避免普遍簡單、有歧義的名稱。
用戶表中，用戶名的欄位為 UserName 比 Name 更好。
布爾型的欄位，以助動詞（has/is）開頭。

用戶是否有留言 hasmessage，用戶是否通過檢查 ischecked 等。

常見後綴如下：

流水號/無意義主鍵，後綴為 id，比如 task_id；
時間，後綴為 time，insert_time。

程序帳號與資料庫名稱保持一致。如果所有的程序帳號都是 root@『%』，密碼也一樣，很容易錯連到其他的資料庫，造成誤操作。

索引命名格式，主要為了區分哪些對象是索引：

前綴_表名（或縮寫）_欄位名（或縮寫）；
主鍵必須使用前綴「pk_」；
UNIQUE 約束必須使用前綴「uk_」；
普通索引必須使用前綴「idx_」。

資料庫規範庫表欄位的命名，能夠提高資料庫的易讀性，為資料庫表設計打下基礎。下面我們具體看看錶設計的一些規則。

顯式指定需要的屬性；

創建表時顯示指定字符集、存儲引擎、注釋信息等。

不同系統之間，統一規範；

不同表之間的相同欄位或者關聯欄位，欄位類型/命名要保持一致；庫表字符集和前端程序、中間件必須保持一致的 UTF8mb4。

InnoDB 表的注意事項

主鍵列，UNSIGNED 整數，使用 auto_increment；禁止手動更新 auto_increment，可以刪除。
必須添加 comment 注釋。
必須顯示指定的 engine。
表必備三欄位：id、 xxx_create、 xxx_modified。

id 為主鍵，類型為 unsigned bigint 等數字類型；
xxx_create、xxx_modified 的類型均為 datetime 類型，分別記錄該條數據的創建時間、修改時間。

備份表/臨時表等常見表的設計規範

備份表，表名必須添加 bak 和日期，主要用於系統版本上線時，存儲原始數據，上線完成後，必須及時刪除。
臨時表，用於存儲中間業務數據，定期優化，及時降低表碎片。
日誌類表，首先考慮不入庫，保存成文件，其次如果入庫，明確其生命周期，保留業務需求的數據，定期清理。
大欄位表，把主鍵欄位和大欄位，單獨拆分成表，並且保持與主表主鍵同步，儘量減少大欄位的檢索和更新。
大表，根據業務需求，從垂直和水平兩個維度進行拆分

垂直拆分：

按列關聯度

水平拆分：

按照時間、地域、範圍等；
冷熱數據（歷史數據歸檔）

欄位設計要求

根據業務場景需求，選擇合適的類型，最短的長度；確保欄位的寬度足夠用，但也不要過寬。所有欄位必須為 NOT NULL，空值則指定 default 值，空值難以優化，查詢效率低。比如：人的年齡用 unsigned tinyint（範圍 0~255，人的壽命不會超過 255 歲）；海龜就必須是 smallint，但如果是太陽的年齡，就必須是 int；如果是所有恆星的年齡都加起來，那麼就必須使用 bigint。
表欄位數少而精，儘量不加冗餘列。
單實例表個數必須控制在 2000 個以內。
單表分表個數必須控制在 1024 個以內。
單表欄位數上限控制在 20~50 個

【禁用 ENUM、SET 類型】

兼容性不好，性能差。

解決方案：使用 TINYINT，在 COMMENT 信息中標明被枚舉的含義。is_disable TINYINT UNSIGNED DEFAULT 『0』 COMMENT '0:啟用 1:禁用 2:異常』。

【禁用列為 NULL 】

MySQL 難以優化 NULL 列；
NULL 列加索引，需要額外空間；
含 NULL 複合索引無效。

解決方案：在列上添加 NOT NULL DEFAULT 預設值

【禁止 VARBINARY、BLOB 存儲圖片、文件等】

禁止在資料庫中存儲大文件，例如照片，可以將大文件存儲在對象存儲系統中，資料庫中存儲路徑。

不建議使用 TEXT/BLOB：

處理性能差；
行長度變長；
全表掃描代價大。

解決方案：拆分成單獨的表

存儲字節越小，占用空間越小。儘量選擇合適的整型，如下圖所示。

主鍵列，無負數，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估欄位數字取值會超過 42 億，使用 BIGINT 類型。
短數據使用 TINYINT 或 SMALLINT，比如：人類年齡，城市代碼。
使用 UNSIGNED 存儲非負數值，擴大正數的範圍。

int(3) int(5) 區別

正常顯示沒有區別。
3 和 5 僅是最小顯示寬度而已。
有 zerofill 等擴展屬性時則顯示有區別。

浮點數與定點數區別

浮點數與定點數區別，如下圖所示。

浮點數：float、double（或 real）
定點數：decimal（或 numberic）

從上圖中可以觀察到：

浮點數存在誤差問題；
儘量避免進行浮點數比較；
對貨幣等對精度敏感的數據，應該使用定點數

N 解釋

字符集都為 UTF8mb4，中文存儲占三個字節，而數據或字母，則只占一個字節。

下面看一下字符類型中 N 的解釋。

CHAR(N) 和 VARCHAR(N) 的長度 N，不是字節數，是字符數。
username 列可以存多少個漢字，占用多少個字節
username 最多能存儲 40 個字符，占用 120 個字節

Char 與 Varchar 類型

存儲字符串長度相同的全部使用 Char 類型；字符長度不相同的使用 Varchar 類型，不預先分配存儲空間，長度不要超過 255。

Char 和 Varchar 占用空間的對比，如下圖所示。

Varchar 值存儲為 1 字節或 2 字節長度前綴加數據

如果值不超過 255 個字節，則列使用一個字節長度

如果值可能需要超過 255 個字節，則列使用兩個字節長度

為什麼超過 255 個字節時，必須使用兩個字節長度。

2的8次方=256，1 個字節是 8 位；
2的16次方=65535，2 個字節是 16 位。

MySQL - 高效的設計MySQL庫表