MySQL-11.資料庫的設計規範_ZenDei技術網路在線

C-11.資料庫的設計規範 1.為什麼需要資料庫設計我們在設計數據表的時候，要考慮很多問題。比如：用戶需要什麼數據？需要在數據表中保存那些數據？如何保證數據表中數據的正確性，當插入，刪除，更新的時候該進行怎樣的約束檢查？如何降低數據表的數據冗餘度，保證數據表不會因為用戶量的增長而迅速擴張？ ...

C-11.資料庫的設計規範

1.為什麼需要資料庫設計

我們在設計數據表的時候，要考慮很多問題。比如：

用戶需要什麼數據？需要在數據表中保存那些數據？
如何保證數據表中數據的正確性，當插入，刪除，更新的時候該進行怎樣的約束檢查？
如何降低數據表的數據冗餘度，保證數據表不會因為用戶量的增長而迅速擴張？
如何讓負責資料庫維護的人員更方便地使用資料庫?
使用資料庫的應用場景也各不相同，可以說針對不同的情況，設計出來的數據表可能千差萬別。

現實情況中，面臨的場景:

當資料庫運行了一段時間之後，我們才發現數據表設計的有問題。重新調整數據表的結構，就需要做數據遷移，還有可能影響程式的業務邏輯，以及網站正常的訪問。

如果是糟糕的資料庫設計可能會造成以下問題:

數據冗餘、信息重覆，存儲空間浪費
數據更新、插入、刪除的異常
無法正確表示信息
丟失有效信息
程式性能差

良好的資料庫設計則有以下優點:

節省數據的存儲空間
能夠保證數據的完整性
方便進行資料庫應用系統的開發

總之，開始設置資料庫的時候，我們就需要重視數據表的設計。為了建立冗餘較小、結構合理的資料庫，設計資料庫時必須遵循一定的規則。

2.範式

2.1 範式簡介

在關係型資料庫中，關於數據表設計的基本原則、規則就稱為範式。可以理解為，一張數據表的設計結構需要滿足的某種設計標準的級別。要想設計一個結構合理的關係型資料庫，必須滿足一定的範式。

範式的英文名稱是Normal Form，簡稱NF。它是英國人E.F.Codd在上個世紀70年代提出關係資料庫模型後總結出來的。範式是關係資料庫理論的基礎，也是我們在設計資料庫結構過程中所要遵循的規則和指導方法。

2.2 範式都包括那些

目前關係型資料庫有六種常見範式，按照範式級別，從低到高分別是：第一範式（1NF)、第二範式(2NF)、第三範式(3NF)、巴斯-科德範式(BCNF)、第四範式(4NF)和第五範式(5NF，又稱完美範式)。

資料庫的範式設計越高階，冗餘度就越低，同時高階的範式一定符合低階範式的要求，滿足最低要求的範式是第一範式(1NF)。在第一範式的基礎上進一步滿足更多規範要求的稱為第二範式(2NF)，其餘範式以次類推。

一般來說，在關係型資料庫設計中，最高也就遵循到BCNF，普遍還是3NF。但也不絕對，有時候為了提高某些查詢性能，我們還需要破壞範式規則，也就是反規範化。

2.3 鍵和相關屬性的概念

範式的定義會使用到主鍵和候選鍵，資料庫中的鍵(Key)由一個或者多個屬性組成。數據表中常用的幾種鍵和屬性的定義:

超鍵: 能唯一標識元組的屬性集叫做超鍵。
候選鍵: 如果超鍵不包括多餘的屬性，那麼這個超鍵就是候選鍵。
主鍵: 用戶可以從候選鍵中選擇一個作為主鍵。
外鍵: 如果數據表R1中的某屬性集不是R1的主鍵，而是另一個數據表R2的主鍵，那麼這個屬性集就是數據表R1的外鍵。
主屬性: 包含在任一候選鍵中的屬性稱為主屬性。
非主屬性: 與主屬性相對，指的是不包含在任何一個候選鍵中的屬性。

通常，我們也將候選鍵稱之為“碼”，把主鍵也稱為“主碼”。因為鍵可能是由多個屬性組成的，針對單個屬性，我們還可以用主屬性和非主屬性來進行區分。

舉例

這裡有兩個表：

球員表(player) ：球員編號 | 姓名 | 身份證號 | 年齡 | 球隊編號

球隊表(team) ：球隊編號 | 主教練 | 球隊所在地

超鍵：對於球員表來說，超鍵就是包括球員編號或者身份證號的任意組合，比如（球員編號）（球員編號，姓名）（身份證號，年齡）等。
候選鍵：就是最小的超鍵，對於球員表來說，候選鍵就是（球員編號）或者（身份證號）。
主鍵：我們自己選定，也就是從候選鍵中選擇一個，比如（球員編號）。
外鍵：球員表中的球隊編號。
主屬性、非主屬性：在球員表中，主屬性是（球員編號）（身份證號），其他的屬性（姓名）（年齡）（球隊編號）都是非主屬性。

2.4 第一範式(1st NF)

第一範式主要是確保數據表中每個欄位的值必須具有原子性，也就是說數據表中每個欄位的值為不可再次拆分的最小數據單元。

我們在設計某個欄位的時候，對於欄位X來說，不能把欄位X拆分成欄位X-1和欄位X-2。事實上任何的DBMS都會滿足第一範式的要求，不會將欄位進行拆分。

舉例1

假設一家公司要存儲員工的姓名和聯繫方式。它創建一個如下表：

該表不符合 1NF ，因為規則說“表的每個屬性必須具有原子（單個）值”，lisi和zhaoliu員工的emp_mobile 值違反了該規則。為了使表符合 1NF ，我們應該有如下表數據：

舉例2

user 表的設計不符合第一範式

其中，user_info欄位為用戶信息，可以進一步拆分成更小粒度的欄位，不符合資料庫設計對第一範式的要求。將user_info拆分後如下：

舉例3

屬性的原子性是主觀的。例如，Employees關係中雇員姓名應當使用1個（fullname）、2個（firstname和lastname）還是3個（firstname、middlename和lastname）屬性表示呢？答案取決於應用程式。如果應用程式需要分別處理雇員的姓名部分（如：用於搜索目的），則有必要把它們分開。否則，不需要。

2.5 第二範式(2nd NF)

第二範式要求，在滿足第一範式的基礎上，還要滿足數據表裡的每一條數據記錄，都是可唯一標識的。而且所有非主鍵欄位，都必須完全依賴主鍵，不能只依賴主鍵的一部分。如果知道主鍵的所有屬性的值，就可以檢索到任何元組(行）的任何屬性的任何值。(要求中的主鍵，其實可以拓展替換為候選鍵)。

舉例1：

成績表（學號，課程號，成績）關係中，（學號，課程號）可以決定成績，但是學號不能決定成績，課程號也不能決定成績，所以“（學號，課程號）→成績”就是完全依賴關係。

舉例2：

比賽表 player_game，裡面包含球員編號、姓名、年齡、比賽編號、比賽時間和比賽場地等屬性，這裡候選鍵和主鍵都為（球員編號，比賽編號），我們可以通過候選鍵（或主鍵）來決定如下的關係：

(球員編號, 比賽編號) → (姓名, 年齡, 比賽時間, 比賽場地，得分)

但是這個數據表不滿足第二範式，因為數據表中的欄位之間還存在著如下的對應關係：

(球員編號) → (姓名，年齡)

(比賽編號) → (比賽時間, 比賽場地)

對於非主屬性來說，並非完全依賴候選鍵。這樣會產生怎樣的問題呢？

數據冗餘：如果一個球員可以參加 m 場比賽，那麼球員的姓名和年齡就重覆了 m-1 次。一個比賽也可能會有 n 個球員參加，比賽的時間和地點就重覆了 n-1 次。
插入異常：如果我們想要添加一場新的比賽，但是這時還沒有確定參加的球員都有誰，那麼就沒法插入。
刪除異常：如果我要刪除某個球員編號，如果沒有單獨保存比賽表的話，就會同時把比賽信息刪除掉。
更新異常：如果我們調整了某個比賽的時間，那麼數據表中所有這個比賽的時間都需要進行調整，否則就會出現一場比賽時間不同的情況。

為了避免出現上述的情況，我們可以把球員比賽表設計為下麵的三張表。

這樣的話，每張數據表都符合第二範式，也就避免了異常情況的發生。

1NF 告訴我們欄位屬性需要是原子性的，而 2NF 告訴我們一張表就是一個獨立的對象，一張表只表達一個意思。

舉例3：

定義了一個名為 Orders 的關係，表示訂單和訂單行的信息：

違反了第二範式，因為有非主鍵屬性僅依賴於候選鍵（或主鍵）的一部分。例如，可以僅通過orderid找到訂單的 orderdate，以及 customerid 和 companyname，而沒有必要再去使用productid。

修改：

Orders表和OrderDetails表如下，此時符合第二範式。

小結:第二範式(2NF）要求實體的屬性完全依賴主關鍵字。如果存在不完全依賴，那麼這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與元實體之間是一對多的關係。

2.6 第三範式(3rd NF)

第三範式是在第二範式的基礎上，確保數據表中的每一個非主鍵欄位都和主鍵欄位直接相關。也就是說，要求數據表中的所有非主鍵欄位不能依賴於其他非主鍵欄位。(即，不能存在非主屬性A依賴於非主屬性B，非主屬性B依賴於主鍵c的情況，即存在“A→B→C"”"的決定關係）通俗地講，該規則的意思是所有非主鍵屬性之間不能有依賴關係，必須相互獨立。

這裡的主鍵可以擴展為候選鍵。

舉例1：

部門信息表：每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。

員工信息表：每個員工有員工編號、姓名、部門編號。列出部門編號後就不能再將部門名稱、部門簡介等與部門有關的信息再加入員工信息表中。

如果不存在部門信息表，則根據第三範式（3NF）也應該構建它，否則就會有大量的數據冗餘。

舉例2：

商品類別名稱依賴於商品類別編號，不符合第三範式。

修改：

表1：符合第三範式的商品類別表的設計

表2：符合第三範式的商品表的設計

商品表goods通過商品類別id欄位（category_id）與商品類別表goods_category進行關聯。

舉例3

球員player表：球員編號、姓名、球隊名稱和球隊主教練。現在，我們把屬性之間的依賴關係畫出來，如下圖所示：

你能看到球員編號決定了球隊名稱，同時球隊名稱決定了球隊主教練，非主屬性球隊主教練就會傳遞依賴於球員編號，因此不符合 3NF 的要求。

如果要達到 3NF 的要求，需要把數據表拆成下麵這樣：

舉例4

修改第二範式中的舉例3。

此時的Orders關係包含 orderid、orderdate、customerid 和 companyname 屬性，主鍵定義為 orderid。customerid 和

companyname均依賴於主鍵——orderid。例如，你需要通過orderid主鍵來查找代表訂單中客戶的customerid，同樣，你需要通過

orderid 主鍵查找訂單中客戶的公司名稱（companyname）。然而， customerid和companyname也是互相依靠的。為滿足第三範式，

可以改寫如下：

符合3NF後的數據模型通俗地講，2NF和3NF通常以這句話概括：“每個非主鍵屬性依賴於主鍵，依賴於整個主鍵，並且除了主鍵別無他的依賴關係”。

2.7 小結

關於數據表的設計，有三個範式要遵循。

(1）第一範式（1NF)，確保每列保持原子性

資料庫的每一列都是不可分割的原子數據項，不可再分的最小數據單元，而不能是集合、數組、記錄等非原子數據項。

(2）第二範式（2NF)，確保每列都和主鍵完全依賴

尤其在複合主鍵的情況下，非主鍵部分不應該依賴於部分主鍵。

(3）第三範式(3NF）確保每列都和主鍵列直接相關，而不是間接相關

範式的優點：數據的標準化有助於消除資料庫中的數據冗餘，第三範式(3NF）通常被認為在性能擴展性和數據完整性方面達到了最好的平衡。

範式的缺點：範式的使用，可能降低查詢的效率。因為範式等級越高，設計出來的數據表就越多、越精細，數據的冗餘度就越低，進行數據查詢的時候就可能需要關聯多張表，這不但代價昂貴，也可能使一些索引策略無效。

範式只是提出了設計的標準，實際上設計數據表時，未必一定要符合這些標準。開發中，我們會出現為了性能和讀取效率違反範式化的原則，通過增加少量的冗餘或重覆的數據來提高資料庫的讀性能，減少關聯查詢，join表的次數，實現空間換取時間的目的。因此在實際的設計過程中要理論結合實際，靈活運用。

範式本身沒有優劣之分，只有適合場景不同。沒有完美的設計，只有合適的設計，我們在數據表的設計中，還需要根據需求將範式和反範式混合使用。

3.反範式

3.1 概述

有的時候不能簡單按照規範要求設計數據表，因為有的數據看似冗餘，其實對業務來說十分重要。這個時候，我們就要遵循業務優先的原則，首先滿足業務需求，再儘量減少冗餘。

如果資料庫中的數據量比較大，系統的UV和PV訪問頻次比較高，則完全按照MySQL的三大範式設計數據表，讀數據時會產生大量的關聯查詢，在一定程度上會影響資料庫的讀性能。如果我們想對查詢效率進行優化，反範式優化也是一種優化思路。此時，可以通過在數據表中增加冗餘欄位來提高資料庫的讀性能。

規範化vs性能

1.為了滿足某種商業目標，資料庫性能比規範化資料庫更重要。

2.在數據規範化的同時，要綜合考慮資料庫的性能。

3.通過在給定的表中添加額外的欄位，以大量減少需要從中搜索信息所需的時間。

4.通過在給定的表中插入計算列，以方便查詢。

3.2 應用舉例

舉例1

員工的信息存儲在employees表中，部門信息存儲在departments表中。通過employees表中的department_id欄位與departments表建立關聯關係。如果要查詢一個員工所在部門的名稱:

select employee_id,department_name 
from employees e join departments d
on e.department_id = d.department_id;

如果經常需要進行這個操作，連接查詢就會浪費很多時間。可以在employees表中增加一個冗餘欄位departmenl_name，這樣就不用每次都進行連接操作了。

舉例2

反範式的goods商品信息表設計如下

舉例3

我們有2個表，分別是商品流水錶（atguigu.trans）和商品信息表（atguigu.goodsinfo)。商品流水錶里有400萬條流水記錄，商品信息表裡有2000條商品記錄。

商品流水錶

商品信息表

兩個表是符合第三範式要求的。但是，在我們項目的實際業務中，對流水的查詢頻率很高，而且為了獲取商品名稱，基本都會用到與商品信息表的連接查詢。

為為減少連接,我們可以直接把商品名稱欄位加到流水錶裡面。這樣-來,我們就可以直接從流水錶中獲取商品名稱欄位了。雖然增加了冗餘欄位,但是避免了關聯查詢,提升了查詢的效率。

新的商品流水錶如下所示

舉例4

這裡宋紅康老師對於，反範式設計的表真的會對查詢有提升，做了個實際的案例演示，這裡貼出具體的sql，不在測試。因為從前面的筆記可以看出，多表聯查和單表查詢相比，就是會消耗更多的資源。

#07-數據表的設計規範

#反範式化的舉例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#學生表
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(25),
create_time DATETIME
);

#課程評論表
CREATE TABLE class_comment(
comment_id INT PRIMARY KEY AUTO_INCREMENT,
class_id INT,
comment_text VARCHAR(35),
comment_time DATETIME,
stu_id INT
);

###創建向學生表中添加數據的存儲過程
DELIMITER //

CREATE PROCEDURE batch_insert_student(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
INSERT INTO student(stu_id, stu_name, create_time)
VALUES((START+i), CONCAT('stu_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#調用存儲過程，學生id從10001開始，添加1000000數據
CALL batch_insert_student(10000,1000000);

####創建向課程評論表中添加數據的存儲過程
DELIMITER //

CREATE PROCEDURE batch_insert_class_comments(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE stu_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = SUBSTR(MD5(RAND()),1, 20);
SET stu_id = FLOOR(RAND()*1000000);
INSERT INTO class_comment(comment_id, class_id, comment_text, comment_time, stu_id)
VALUES((START+i), 10001, comment_text, date_temp, stu_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#添加數據的存儲過程的調用，一共1000000條記錄
CALL batch_insert_class_comments(10000,1000000);

#########
SELECT COUNT(*) FROM student;

SELECT COUNT(*) FROM class_comment;

###需求######
SELECT p.comment_text, p.comment_time, stu.stu_name 
FROM class_comment AS p LEFT JOIN student AS stu 
ON p.stu_id = stu.stu_id 
WHERE p.class_id = 10001 
ORDER BY p.comment_id DESC 
LIMIT 10000;


#####進行反範式化的設計######
#表的複製
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主鍵，保證class_comment1 與class_comment的結構相同
ALTER TABLE class_comment1
ADD PRIMARY KEY (comment_id);

SHOW INDEX FROM class_comment1;

#向課程評論表中增加stu_name欄位
ALTER TABLE class_comment1
ADD stu_name VARCHAR(25);

#給新添加的欄位賦值
UPDATE class_comment1 c
SET stu_name = (
SELECT stu_name
FROM student s
WHERE c.stu_id = s.stu_id
);

#查詢同樣的需求
SELECT comment_text, comment_time, stu_name 
FROM class_comment1 
WHERE class_id = 10001 
ORDER BY comment_id DESC 
LIMIT 10000;

3.3 反範式的新問題

反範式可以通過空間換時間，提升查詢的效率，但是反範式也會帶來一些新問題:

存儲空間變大了
一個表中欄位做了修改，另一個表中冗餘的欄位也需要做同步修改，否則數據不一致
若採用存儲過程來支持數據的更新、刪除等額外操作，如果更新頻繁，會非常消耗系統資源
在數據量小的情況下，反範式不能體現性能的優勢，可能還會讓資料庫的設計更加複雜

3.4 反範式的使用場景

當冗餘信息有價值或者能大幅度提高查詢效率的時候，我們才會採取反範式的優化。

1.增加冗餘欄位的建議

增加冗餘欄位一定要符合如下兩個條件。只有滿足這兩個條件，才可以考慮增加冗餘欄位。

1)這個冗餘欄位不需要經常進行修改。

2)這個冗餘欄位查詢的時候不可或缺。

2. 歷史快照、歷史數據的需要

在現實生活中，我們經常需要一些冗餘信息，比如訂單中的收貨人信息，包括姓名、電話和地址等。每次發生的訂單收貨信息都屬於歷史快照，需要進行保存，但用戶可以隨時修改自己的信息，這時保存這些冗餘信息是非常有必要的。

反範式優化也常用在數據倉庫的設計中，因為數據倉庫通常存儲歷史數據，對增刪改的實時性要求不強，對歷史數據的分析需求強。這時適當允許數據的冗餘度，更方便進行數據分析。

簡單總結數據倉庫和資料庫在使用上的區別：

資料庫設計的目的在於捕獲數據，而數據倉庫設計的目的在於分析數據;
資料庫對數據的增刪改實時性要求強，需要存儲線上的用戶數據，而數據倉庫存儲的一般是歷史數據;
資料庫設計需要儘量避免冗餘，但為了提高查詢效率也允許一定的冗餘度，而數據倉庫在設計上更偏向採用反範式設計。

4.BCNF(巴斯範式)

人們在3NF的基礎上進行了改進，提出了巴斯範式(BCNF)，也叫做巴斯-科德範式(Boyce-Codd NormalForm)。BCNF被認為沒有新的設計規範加入，只是對第三範式中設計規範要求更強，使得資料庫冗餘度更小。所以，稱為是修正的第三範式，或擴充的第三範式，BCNF不被稱為第四範式。

若一個關係達到了第三範式，並且它只有一個候選鍵，或者它的每個候選鍵都是單屬性，則該關係自然達到BC範式。

一般來說，一個資料庫設計符合3NF或BCNF就可以了。

1.案例

我們分析如下表的範式情況：

在這個表中，一個倉庫只有一個管理員，同時一個管理員也只管理一個倉庫。我們先來梳理下這些屬性之間的依賴關係。

倉庫名決定了管理員，管理員也決定了倉庫名，同時（倉庫名，物品名）的屬性集合可以決定數量這個屬性。這樣，我們就可以找到數據表的候選鍵。

候選鍵：是（管理員，物品名）和（倉庫名，物品名），然後我們從候選鍵中選擇一個作為主鍵，比如（倉庫名，物品名）。

主屬性：包含在任一候選鍵中的屬性，也就是倉庫名，管理員和物品名。

非主屬性：數量這個屬性。

2. 是否符合三範式

如何判斷一張表的範式呢？我們需要根據範式的等級，從低到高來進行判斷。

首先，數據表每個屬性都是原子性的，符合 1NF 的要求；

其次，數據表中非主屬性”數量“都與候選鍵全部依賴，（倉庫名，物品名）決定數量，（管理員，物品名）決定數量。因此，數據表符合 2NF 的要求；

最後，數據表中的非主屬性，不傳遞依賴於候選鍵。因此符合 3NF 的要求。

3. 存在的問題

既然數據表已經符合了 3NF 的要求，是不是就不存在問題了呢？我們來看下麵的情況：

增加一個倉庫，但是還沒有存放任何物品。根據數據表實體完整性的要求，主鍵不能有空值，因此會出現插入異常；
如果倉庫更換了管理員，我們就可能會修改數據表中的多條記錄；
如果倉庫里的商品都賣空了，那麼此時倉庫名稱和相應的管理員名稱也會隨之被刪除。

你能看到，即便數據表符合 3NF 的要求，同樣可能存在插入，更新和刪除數據的異常情況。

4. 問題解決

首先我們需要確認造成異常的原因：主屬性倉庫名對於候選鍵（管理員，物品名）是部分依賴的關係，這樣就有可能導致上面的異常情況。因此引入BCNF，它在 3NF 的基礎上消除了主屬性對候選鍵的部分依賴或者傳遞依賴關係。

如果在關係R中，U為主鍵，A屬性是主鍵的一個屬性，若存在A->Y，Y為主屬性，則該關係不屬於BCNF。

根據 BCNF 的要求，我們需要把倉庫管理關係 warehouse_keeper 表拆分成下麵這樣：

倉庫表：（倉庫名，管理員）

庫存表：（倉庫名，物品名，數量）

這樣就不存在主屬性對於候選鍵的部分依賴或傳遞依賴，上面數據表的設計就符合 BCNF。

再舉例：

有一個學生導師表，其中包含欄位：學生ID，專業，導師，專業GPA，這其中學生ID和專業是聯合主鍵。

這個表的設計滿足三範式，但是這裡存在另一個依賴關係，“專業”依賴於“導師”，也就是說每個導師只做一個專業方面的導師，只要知道了是哪個導師，我們自然就知道是哪個專業的了。所以這個表的部分主鍵Major依賴於非主鍵屬性Advisor，那麼我們可以進行以下的調整，拆分成2個表：

學生導師表：

導師表：

5.第四範式(瞭解)

註意實際中，很少使用到第四範式，一般都是第3範式就可以了，因為符合該第4範式及之後建立的表，在查詢時會join多張表。

多值依賴的概念:

多值依賴即屬性之間的一對多關係，記為K→→A。
函數依賴事實上是單值依賴，所以不能表達屬性值之間的一對多關係。
平凡的多值依賴︰全集U=K+A，一個K可以對應於多個A，即K→→A。此時整個表就是一組一對多關係。
非平凡的多值依賴∶全集U=K+A+B，一個K可以對應於多個A，也可以對應於多個B，A與B互相獨立，即K→一A，K→→B。整個表有多組一對多關係，且有:“一”部分是相同的屬性集合，“多"部分是互相獨立的屬性集合。

第四範式即在滿足巴斯-科德範式(BCNF）的基礎上，消除非平凡且非函數依賴的多值依賴(即把同一表內的多對多關係刪除)。

舉例1：

職工表(職工編號，職工孩子姓名，職工選修課程)。

在這個表中，同一個職工可能會有多個職工孩子姓名。同樣，同一個職工也可能會有多個職工選修課程，即這裡存在著多值事實，不符合第四範式。

如果要符合第四範式，只需要將上表分為兩個表，使它們只有一個多值事實，例如： 職工表一 (職工編號，職工孩子姓名)， 職工表二 (職工編號，職工選修課程)，兩個表都只有一個多值事實，所以符合第四範式。

舉例2：

比如我們建立課程、教師、教材的模型。我們規定，每門課程有對應的一組教師，每門課程也有對應的一組教材，一門課程使用的教材和教師沒有關係。我們建立的關係表如下：

課程ID，教師ID，教材ID；這三列作為聯合主鍵。

為了表述方便，我們用Name代替ID，這樣更容易看懂：

這個表除了主鍵，就沒有其他欄位了，所以肯定滿足BC範式，但是卻存在多值依賴導致的異常。

假如我們下學期想採用一本新的英版高數教材，但是還沒確定具體哪個老師來教，那麼我們就無法在這個表中維護Course高數和Book英版高數教材的的關係。

解決辦法是我們把這個多值依賴的表拆解成2個表，分別建立關係。這是我們拆分後的表:

以及

6.第五範式、域鍵範式

可以忽略，因為實際根本不會使用到該範式，所以在課程中也沒舉例

除了第四範式外，我們還有更高級的第五範式(又稱完美範式)和域鍵範式(DKNF)。

在滿足第四範式(4NF)的基礎上，消除不是由候選鍵所蘊含的連接依賴。如果關係模式R中的每一個連接依賴均由R的候選鍵所隱含，則稱此關係模式符合第五範式。

函數依賴是多值依賴的一種特殊的情況，而多值依賴實際上是連接依賴的一種特殊情況。但連接依賴不像函數依賴和多值依賴可以由語義直接導出，而是在關係連接運算時才反映出來。存在連接依賴的關係模式仍可能遇到數據冗餘及插入、修改、刪除異常等問題。

第五範式處理的是無損連接問題，這個範式基本沒有實際意義，因為無損連接很少出現，而且難以察覺。而域鍵範式試圖定義一個終極範式，該範式考慮所有的依賴和約束類型，但是實用價值也是最小的，只存在理論研究中。

7.實戰案例

商超進貨系統中的進貨單表進行剖析:

進貨單表:

這個表中的欄位很多，表裡的數據量也很驚人。大量重覆導致表變得龐大，效率極低。如何改造?

在實際工作場景中，這種由於數據表結構設計不合理，而導致的數據重覆的現象並不少見。往往是系統雖然能夠運行，承載能力卻很差，稍微有點流量，就會出現記憶體不足、CUP使用率飆升的情況，甚至會導致整個項目失敗。

7.1 迭代1次：考慮1NF

第一範式要求：所有的欄位都是基本數據欄位，不可進一步拆分。這裡需要確認，所有的列中，每個欄位只包含—種數據。

這張表裡，我們把“property”這一欄位，拆分成“specification(規格)"和“unit (單位)”，這2個欄位如下:

7.2 迭代2次：考慮2NF

第二範式要求，在滿足第一範式的基礎上，還要滿足數據表裡的每一條數據記錄，都是可唯一標識的。而且所有欄位，都必須完全依賴主鍵，不能只依賴主鍵的一部分。

第1步，就是要確定這個表的主鍵。通過觀察發現，欄位"listnumber(單號)"+"barcode(條碼)"可以唯一標識每一條記錄，可以作為主鍵。

第2步，確定好了主鍵以後，判斷哪些欄位完全依賴主鍵，哪些欄位只依賴於主鍵的一部分。把只依賴於主鍵一部分的欄位拆分出去，形成新的數據表。

首先，進貨單明細表裡面的“goodsname(名稱)""specification(規格)""unit(單位)"這些信息是商品的屬性，只依賴於“barcode(條碼)”，不完全依賴主鍵，可以拆分出去。我們把這3個欄位加上它們所依賴的欄位“barcode(條碼)”，拆分形成一個新的數據表“商品信息表”。

這樣一來，原來的數據表就被拆分成了兩個表。

商品信息表：

進貨單表

此外，欄位“supplierid(供應商編號)""suppliername(供應商名稱)""stock(倉庫)"只依賴於“listnumber(單號)”，不完全依賴於主鍵，所以，我們可以把“supplierid""suppliername""stock"這3個欄位拆出去，再加上它們依賴的欄位"listnumber(單號)”，就形成了一個新的表"進貨單頭表”。剩下的欄位，會組成新的表，我們叫它“進貨單明細表”。

原來的數據表就拆分成了3個表。

進貨單頭表：

進貨單明細表：

商品信息表：

現在，我們再來分析一下拆分後的3個表，保證這3個表都滿足第二範式的要求。

第3步，在"商品信息表"中，欄位""barcode”是有可能存在重覆的，比如，用戶門店可能有散裝稱重商品和自產商品，會存在條碼共用的情況。所以，所有的欄位都不能唯一標識表裡的記錄。這個時候，我們必須給這個表加上一個主鍵，比如說是自增欄位"itemnumber”。

現在，我們就可以把進貨單明細表裡面的欄位“barcode"都替換成欄位“ itemnumber”，這就得到了新的如下表。

進貨單明細表:

商品信息表：

7.3 迭代3次：考慮3NF

進貨單頭表，還有冗餘的可能。

供貨商表：

進貨單頭表：

這2個表多滿足第三範式的要求了。

7.4 反範式化：業務優先原則

對於進貨單明細表中，還存再一個隱式的依賴關係，importvalue欄位，依賴於quantity * importprice。如果嚴格遵守第三範式，應該予以優化，移除一部分欄位，這樣就沒有冗餘數據了。

可是，真的可以這樣做嗎?要回答這個問題，我們就要先瞭解下實際工作中的業務優先原則。

所謂的業務優先原則,就是指一切以業務需求為主，技術服務於業務。完全按照理論的設計不一定就是最優，還要根據實際情況來決定。這裡我們就來分析一下不同選擇的利與弊。

對於quantity * importprice = importvalue，看起來"importvalue”似乎是冗餘欄位，但並不會導致數據不一致。可是，如果我們把這個欄位取消，是會影響業務的。所以，加粗的四個表就是，最初的大表拆分後的表。

8.ER模式

資料庫設計是牽一發而動全身的。那有沒有什麼辦法提前看到資料庫的全貌呢?比如需要哪些數據表、數據表中應該有哪些欄位，數據表與數據表之間有什麼關係、通過什麼欄位進行連接，等等。這樣我們才能進行整體的梳理和設計。

其實，ER模型就是一個這樣的工具。ER模型也叫作實體關係模型，是用來描述現實生活中客觀存在的事物、事物的屬性，以及事物之間關係的一種數據模型。在開發基於資料庫的信息系統的設計階段，通常使用ER模型來描述信息需求和信息特性，幫助我們理清業務邏輯，從而設計出優秀的資料庫。

8.1 ER模型包括那些要素？

ER(entity-relationship)模型中有三個要素，分別是實體，屬性和關係。

實體，可以看做是數據對象，往往對應於現實生活中的真實存在的個體。在 ER 模型中，用矩形來表示。實體分為兩類，分別是強實體和弱實體。強實體是指不依賴於其他實體的實體；弱實體是指對另一個實體有很強的依賴關係的實體。

屬性，則是指實體的特性。比如超市的地址、聯繫電話、員工數等。在 ER 模型中用橢圓形來表示。

關係，則是指實體之間的聯繫。比如超市把商品賣給顧客，就是一種超市與顧客之間的聯繫。在 ER 模型中用菱形來表示。

註意：實體和屬性不容易區分。這裡提供一個原則：我們要從系統整體的角度出發去看，可以獨立存在的是實體，不可再分的是屬性。也就是說，屬性不能包含其他屬性。

8.2 關係的類型

在 ER 模型的 3 個要素中，關係又可以分為 3 種類型，分別是一對一、一對多、多對多。

一對一：指實體之間的關係是一一對應的，比如個人與身份證信息之間的關係就是一對一的關係。一個人只能有一個身份證信息，一個身份證信息也只屬於一個人。

一對多：指一邊的實體通過關係，可以對應多個另外一邊的實體。相反，另外一邊的實體通過這個關係，則只能對應唯一的一邊的實體。比如說，我們新建一個班級表，而每個班級都有多個學生，每個學生則對應一個班級，班級對學生就是一對多的關係。

多對多：指關係兩邊的實體都可以通過關係對應多個對方的實體。比如在進貨模塊中，供貨商與超市之間的關係就是多對多的關係，一個供貨商可以給多個超市供貨，一個超市也可以從多個供貨商那裡採購商品。再比如一個選課表，有許多科目，每個科目有很多學生選，而每個學生又可以選擇多個科目，這就是多對多的關係

8.3 建模分析

ER 模型看起來比較麻煩，但是對我們把控項目整體非常重要。如果你只是開發一個小應用，或許簡單設計幾個表夠用了，一旦要設計有一定規模的應用，在項目的初始階段，建立完整的 ER 模型就非常關鍵了。開發應用項目的實質，其實就是建模。

我們設計的案例是電商業務，由於電商業務太過龐大且複雜，所以我們做了業務簡化，比如針對SKU（Stock Keeping Unit，庫存量單位）和SPU（Standard Product Unit，標準化產品單元）的含義上，我們直接使用了SKU，並沒有提及SPU的概念。本次電商業務設計總共有8個實體，如下所示。

地址實體
用戶實體
購物車實體
評論實體
商品實體
商品分類實體
訂單實體
訂單詳情實體

其中，用戶和商品分類是強實體，因為它們不需要依賴其他任何實體。而其他屬於弱實體，因為它們雖然都可以獨立存在，但是它們都依賴用戶這個實體，因此都是弱實體。知道了這些要素，我們就可以給電商業務創建 ER 模型了，如圖：

在這個圖中，地址和用戶之間的添加關係，是一對多的關係，而商品和商品詳情示一對1的關係，商品和訂單是多對多的關係。這個 ER 模型，包括了 8個實體之間的 8種關係。

（1）用戶可以在電商平臺添加多個地址；

（2）用戶只能擁有一個購物車；

（3）用戶可以生成多個訂單；

（4）用戶可以發表多條評論；

（5）一件商品可以有多條評論；

（6）每一個商品分類包含多種商品；

（7）一個訂單可以包含多個商品，一個商品可以在多個訂單里。

（8）訂單中又包含多個訂單詳情，因為一個訂單中可能包含不同種類的商品

8.4 ER模型的細化

有了這個 ER 模型，我們就可以從整體上理解電商的業務了。剛剛的 ER 模型展示了電商業務的框架，但是只包括了訂單，地址，用戶，購物車，評論，商品，商品分類和訂單詳情這八個實體，以及它們之間的關係，還不能對應到具體的表，以及表與表之間的關聯。我們需要把屬性加上，用橢圓來表示，這樣我們得到的 ER 模型就更加完整了。

因此，我們需要進一步去設計一下這個 ER 模型的各個局部，也就是細化下電商的具體業務流程，然後把

它們綜合到一起，形成一個完整的 ER 模型。這樣可以幫助我們理清資料庫的設計思路。

接下來，我們再分析一下各個實體都有哪些屬性，如下所示。

（1）地址實體包括用戶編號、省、市、地區、收件人、聯繫電話、是否是預設地址。

（2）用戶實體包括用戶編號、用戶名稱、昵稱、用戶密碼、手機號、郵箱、頭像、用戶級別。

（3）購物車實體包括購物車編號、用戶編號、商品編號、商品數量、圖片文件url。

（4）訂單實體包括訂單編號、收貨人、收件人電話、總金額、用戶編號、付款方式、送貨地址、下單時間。

（5）訂單詳情實體包括訂單詳情編號、訂單編號、商品名稱、商品編號、商品數量。

（6）商品實體包括商品編號、價格、商品名稱、分類編號、是否銷售，規格、顏色。

（7）評論實體包括評論id、評論內容、評論時間、用戶編號、商品編號

（8）商品分類實體包括類別編號、類別名稱、父類別編號

這樣細分之後，我們就可以重新設計電商業務了，ER 模型如圖：

8.5 ER模型圖轉換成數據表

通過繪製 ER 模型，我們已經理清了業務邏輯，現在，我們就要進行非常重要的一步了：把繪製好的 ER模型，轉換成具體的數據表，下麵介紹下轉換的原則：

（1）一個實體通常轉換成一個數據表；

（2）一個多對多的關係 ，通常也轉換成一個數據表；

（3）一個 1對1，或者1對多的關係，往往通過表的外鍵來表達，而不是設計一個新的數據表；

（4）屬性轉換成表的欄位。

下麵結合前面的ER模型，具體講解一下怎麼運用這些轉換的原則，把 ER 模型轉換成具體的數據表，從而把抽象出來的數據模型，落實到具體的資料庫設計當中。

其實，任何一個基於資料庫的應用項目，都可以通過這種先建立 ER模型，再轉換成數據表的方式，完成資料庫的設計工作。創建 ER 模型不是目的，目的是把業務邏輯梳理清楚，設計出優秀的資料庫。我建議你不是為了建模而建模，要利用創建 ER 模型的過程來整理思路，這樣創建 ER 模型才有意義。

9.數據表的設計原則

綜合以上內容，總結出數據表設計的一般原則:"三少一多"

1.數據表的個數越少越好

RDBMS的核心在於對實體和聯繫的定義，也就是E-R圖(Entity Relationship Diagram)，數據表越少，證明實體和聯繫設計得越簡潔，既方便理解又方便操作。

2.數據表中的欄位個數越少越好

欄位個數越多，數據冗餘的可能性越大。設置欄位個數少的前提是各個欄位相互獨立，而不是某個欄位的取值可以由其他欄位計算出來。當然欄位個數身是相對的，我們通常會在數據冗餘和檢索效率中進行平衡。

3.數據表中聯合主鍵的欄位個數越少越好

設置主鍵是為了確定唯一性，當一個欄位無法確定唯一性的時候，就需要採用聯合主鍵的方式（也就是用多個欄位來定義一個主鍵)。聯合主鍵中的欄位越多，占用的索引空間越大，不僅會加大理解難度，還會增加運行時間和索引空間，因此聯合主鍵的欄位個數越少越好。

4.使用主鍵和外鍵越多越好

資料庫的設計實際上就是定義各種表，以及各種欄位之間的關係。這些關係越多，證明這些實體之間的冗餘度越低，利用度越高。這樣做的好處在於不僅保證了數據表之間的獨立性，還能提升相互之間的關聯使用率。

“三少一多"原則的核心就是簡單可復用。簡單指的是用更少的表、更少的欄位、更少的聯合主鍵欄位來完成數據表的設計。可復用則是通過主鍵、外鍵的使用來增強數據表之間的復用率。因為一個主鍵可以理解是一張表的代表。鍵設計得越多，證明它們之間的利用率越高。

註意：這個原則並不是絕對的，有時候我們需要犧牲數據的冗餘度來換取數據處理的效率。

10.資料庫對象編寫建議

10.1 關於庫

【強制】庫的名稱必須控制在32個字元以內，只能使用英文字母、數字和下劃線，建議以英文字母開頭。
【強制】庫名中英文一律小寫，不同單詞采用下劃線分割。須見名知意。
【強制】庫的名稱格式：業務系統名稱_子系統名。
【強制】庫名禁止使用關鍵字（如type,order等）。
【強制】創建資料庫時必須顯式指定字元集，並且字元集只能是utf8或者utf8mb4。創建資料庫SQL舉例：CREATE DATABASE crm_fundDEFAULT CHARACTER SET 'utf8';
【建議】對於程式連接資料庫賬號，遵循許可權最小原則使用資料庫賬號只能在一個DB下使用，不准跨庫。程式使用的賬號原則上不准有drop許可權。
【建議】臨時庫以tmp_為首碼，並以日期為尾碼；備份庫以bak_為首碼，並以日期為尾碼。

10.2 關於表，列

【強制】表和列的名稱必須控制在32個字元以內，表名只能使用英文字母、數字和下劃線，建議以英文字母開頭。
【強制】表名、列名一律小寫，不同單詞采用下劃線分割。須見名知意。
【強制】表名要求有模塊名強相關，同一模塊的表名儘量使用統一首碼。比如：crm_fund_item
【強制】創建表時必須顯式指定字元集為utf8或utf8mb4。
【強制】表名、列名禁止使用關鍵字（如type,order等）。
【強制】創建表時必須顯式指定表存儲引擎類型。如無特殊需求，一律為InnoDB。
【強制】建表必須有comment。
【強制】欄位命名應儘可能使用表達實際含義的英文單詞或縮寫。如：公司 ID，不要使用corporation_id, 而用corp_id 即可。
【強制】布爾值類型的欄位命名為is_描述。如member表上表示是否為enabled的會員的欄位命名為 is_enabled。
【強制】禁止在資料庫中存儲圖片、文件等大的二進位數據通常文件很大，短時間內造成數據量快速增長，資料庫進行資料庫讀取時，通常會進行大量的隨機IO操作，文件很大時，IO操作很耗時。通常存儲於文件伺服器，資料庫只存儲文件地址信息。
【建議】建表時關於主鍵：表必須有主鍵(1)強制要求主鍵為id，類型為int或bigint，且為auto_increment 建議使用unsigned無符號型。 (2)標識表裡每一行主體的欄位不要設為主鍵，建議設為其他欄位如user_id，order_id等，並建立unique key索引。因為如果設為主鍵且主鍵值為隨機插入，則會導致innodb內部頁分裂和大量隨機I/O，性能下降。
【建議】核心表（如用戶表）必須有行數據的創建時間欄位（create_time）和最後更新時間欄位（update_time），便於查問題。
【建議】表中所有欄位儘量都是NOT NULL屬性，業務可以根據需要定義 DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、數據遷移容易出錯、聚合函數計算結果偏差等問題。
【建議】所有存儲相同數據的列名和列類型必須一致（一般作為關聯列，如果查詢時關聯列類型不一致會自動進行數據類型隱式轉換，會造成列上的索引失效，導致查詢效率降低）。
【建議】中間表（或臨時表）用於保留中間結果集，名稱以tmp_開頭。備份表用於備份或抓取源表快照，名稱以bak_開頭。中間表和備份表定期清理。
【示範】一個較為規範的建表語句：

CREATE TABLE user_info (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
`user_id` bigint(11) NOT NULL COMMENT '用戶id',
`username` varchar(45) NOT NULL COMMENT '真實姓名',
`email` varchar(30) NOT NULL COMMENT '用戶郵箱',
`nickname` varchar(45) NOT NULL COMMENT '昵稱',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(4) DEFAULT '0' COMMENT '性別',
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句話介紹自己，最多50個漢字',
`user_resume` varchar(300) NOT NULL COMMENT '用戶提交的簡歷存放地址',
`user_register_ip` int NOT NULL COMMENT '用戶註冊時的源ip',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '修改時間',
`user_review_status` tinyint NOT NULL COMMENT '用戶資料審核狀態，1為通過，2為審核中，3為未通過，4為還未提交審核',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time_status`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網站用戶基本信息'

【建議】創建表時，可以使用可視化工具。這樣可以確保表、欄位相關的約定都能設置上。實際上，我們通常很少自己寫DDL語句，可以使用一些可視化工具來創建和操作資料庫和數據表。可視化工具除了方便，還能直接幫我們將資料庫的結構定義轉化成 SQL 語言，方便資料庫和數據表結構d的導出和導入。

10.3 關於索引

【強制】InnoDB表必須主鍵為id int/bigint auto_increment，且主鍵值禁止被更新。
【強制】InnoDB和MyISAM存儲引擎表，索引類型必須為BTREE。
【建議】主鍵的名稱以pk_開頭，唯一鍵以uni_ 或 uk_開頭，普通索引以idx_開頭，一律使用小寫格式，以欄位的名稱或縮寫作為尾碼。
【建議】多單片語成的columnname，取前幾個單詞首字母，加末單片語成column_name。如:sample 表 member_id 上的索引：idx_sample_mid。
【建議】單個表上的索引個數不能超過6個。
【建議】在建立索引時，多考慮建立聯合索引，並把區分度最高的欄位放在最前面。
【建議】在多表 JOIN 的SQL里，保證被驅動表的連接列上有索引，這樣JOIN 執行效率最高。
【建議】建表或加索引時，保證表裡互相不存在冗餘索引。比如：如果表裡已經存在key(a,b)，則key(a)為冗餘索引，需要刪除。

10.4 SQL編寫

【強制】程式端SELECT語句必須指定具體欄位名稱，禁止寫成 *。
【建議】程式端insert語句指定具體欄位名稱，不要寫成INSERT INTO t1 VALUES(…)。
【建議】除靜態表或小表（100行以內），DML語句必須有WHERE條件，且使用索引查找。
【建議】INSERT INTO…VALUES(XX),(XX),(XX).. 這裡XX的值不要超過5000個。值過多雖然上線很快，但會引起主從同步延遲。
【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，並且UNION子句個數限制在5個以內。
【建議】線上環境，多表 JOIN 不要超過5個表。
【建議】減少使用ORDER BY，和業務溝通能不排序就不排序，或將排序放到程式端去做。ORDER BY、GROUP BY、DISTINCT 這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。
【建議】包含了ORDER BY、GROUP BY、DISTINCT 這些查詢的語句，WHERE 條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。
【建議】對單表的多次alter操作必須合併為一次對於，超過100W行的大表進行alter table，必須經過DBA審核，併在業務低峰期執行，多個alter需整合在一起。因為alter table會產生表鎖，期間阻塞對於該表的所有寫入，對於業務可能會產生極大影響。
【建議】批量操作數據時，需要控制事務處理間隔時間，進行必要的sleep。
【建議】事務里包含SQL不超過5個。因為過長的事務會導致鎖數據較久，MySQL內部緩存、連接消耗過多等問題。
【建議】事務里更新語句儘量基於主鍵或UNIQUE KEY，如UPDATE… WHERE id=XX;否則會產生間隙鎖，內部擴大鎖定範圍，導致系統性能下降，產生死鎖。

11.PowerDesigner的使用

略

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。