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

长名06發表於2024-06-05
MySql資料庫

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. 歷史快照、歷史資料的需要

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

反正規化最佳化也常用在資料倉儲的設計中，因為資料倉儲通常儲存歷史資料，對增刪改的實時性要求不強，對歷史資料的分析需求強。這時適當允許資料的冗餘度，更方便進行資料分析。

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

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

4.BCNF(巴斯正規化)

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

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

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

1.案例

我們分析如下表的正規化情況：

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

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

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

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

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

2. 是否符合三正規化

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

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

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

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

3. 存在的問題

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

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

你能看到，即便資料表符合 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 關於庫

  1. 【強制】庫的名稱必須控制在32個字元以內，只能使用英文字母、數字和下劃線，建議以英文字母開頭。

  2. 【強制】庫名中英文一律小寫，不同單詞采用下劃線分割。須見名知意。

  3. 【強制】庫的名稱格式：業務系統名稱_子系統名。

  4. 【強制】庫名禁止使用關鍵字（如type,order等）。

  5. 【強制】建立資料庫時必須顯式指定字符集，並且字符集只能是utf8或者utf8mb4。建立資料庫SQL舉例：CREATE DATABASE crm_fundDEFAULT CHARACTER SET 'utf8';

  6. 【建議】對於程式連線資料庫賬號，遵循 許可權最小原則使用資料庫賬號只能在一個DB下使用，不準跨庫。程式使用的賬號原則上不準有drop許可權。

  7. 【建議】臨時庫以tmp_為字首，並以日期為字尾；備份庫以bak_為字首，並以日期為字尾。

10.2 關於表，列

  1. 【強制】表和列的名稱必須控制在32個字元以內，表名只能使用英文字母、數字和下劃線，建議以英文字母開頭 。

  2. 【強制】表名、列名一律小寫，不同單詞采用下劃線分割。須見名知意。

  3. 【強制】表名要求有模組名強相關，同一模組的表名儘量使用統一字首。比如：crm_fund_item

  4. 【強制】建立表時必須顯式指定字符集為utf8或utf8mb4。

  5. 【強制】表名、列名禁止使用關鍵字（如type,order等）。

  6. 【強制】建立表時必須顯式指定表儲存引擎型別。如無特殊需求，一律為InnoDB。

  7. 【強制】建表必須有comment。

  8. 【強制】欄位命名應儘可能使用表達實際含義的英文單詞或縮寫。如：公司 ID，不要使用corporation_id, 而用corp_id 即可。

  9. 【強制】布林值型別的欄位命名為is_描述。如member表上表示是否為enabled的會員的欄位命名為 is_enabled。

  10. 【強制】禁止在資料庫中儲存圖片、檔案等大的二進位制資料通常檔案很大，短時間內造成資料量快速增長，資料庫進行資料庫讀取時，通常會進行大量的隨機IO操作，檔案很大時，IO操作很耗時。通常儲存於檔案伺服器，資料庫只儲存檔案地址資訊。

  11. 【建議】建表時關於主鍵：表必須有主鍵(1)強制要求主鍵為id，型別為int或bigint，且為auto_increment 建議使用unsigned無符號型。 (2)標識表裡每一行主體的欄位不要設為主鍵，建議設為其他欄位如user_id，order_id等，並建立unique key索引。因為如果設為主鍵且主鍵值為隨機插入，則會導致innodb內部頁分裂和大量隨機I/O，效能下降。

  12. 【建議】核心表（如使用者表）必須有行資料的建立時間欄位（create_time）和最後更新時間欄位（update_time），便於查問題。

  13. 【建議】表中所有欄位儘量都是NOT NULL屬性，業務可以根據需要定義 DEFAULT值 。 因為使用NULL值會存在每一行都會佔用額外儲存空間、資料遷移容易出錯、聚合函式計算結果偏差等問題。

  14. 【建議】所有儲存相同資料的列名和列型別必須一致（一般作為關聯列，如果查詢時關聯列型別不一致會自動進行資料型別隱式轉換，會造成列上的索引失效，導致查詢效率降低）。

  15. 【建議】中間表（或臨時表）用於保留中間結果集，名稱以tmp_開頭。備份表用於備份或抓取源錶快照，名稱以bak_開頭。中間表和備份表定期清理。

  16. 【示範】一個較為規範的建表語句：

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='網站使用者基本資訊'
  1. 【建議】建立表時，可以使用視覺化工具。這樣可以確保表、欄位相關的約定都能設定上。實際上，我們通常很少自己寫DDL語句，可以使用一些視覺化工具來建立和運算元據庫和資料表。視覺化工具除了方便，還能直接幫我們將資料庫的結構定義轉化成 SQL 語言，方便資料庫和資料表結構d的匯出和匯入。

10.3 關於索引

  1. 【強制】InnoDB表必須主鍵為id int/bigint auto_increment，且主鍵值禁止被更新。

  2. 【強制】InnoDB和MyISAM儲存引擎表，索引型別必須為BTREE。

  3. 【建議】主鍵的名稱以pk_開頭，唯一鍵以uni_ 或 uk_開頭，普通索引以idx_開頭，一律使用小寫格式，以欄位的名稱或縮寫作為字尾。

  4. 【建議】多單片語成的columnname，取前幾個單詞首字母，加末單片語成column_name。如:sample 表 member_id 上的索引：idx_sample_mid。

  5. 【建議】單個表上的索引個數不能超過6個。

  6. 【建議】在建立索引時，多考慮建立聯合索引，並把區分度最高的欄位放在最前面。

  7. 【建議】在多表 JOIN 的SQL裡，保證被驅動表的連線列上有索引，這樣JOIN 執行效率最高。

  8. 【建議】建表或加索引時，保證表裡互相不存在冗餘索引。 比如：如果表裡已經存在key(a,b)，則key(a)為冗餘索引，需要刪除。

10.4 SQL編寫

  1. 【強制】程式端SELECT語句必須指定具體欄位名稱，禁止寫成 *。

  2. 【建議】程式端insert語句指定具體欄位名稱，不要寫成INSERT INTO t1 VALUES(…)。

  3. 【建議】除靜態表或小表（100行以內），DML語句必須有WHERE條件，且使用索引查詢。

  4. 【建議】INSERT INTO…VALUES(XX),(XX),(XX).. 這裡XX的值不要超過5000個。 值過多雖然上線很快，但會引起主從同步延遲。

  5. 【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，並且UNION子句個數限制在5個以內。

  6. 【建議】線上環境，多表 JOIN 不要超過5個表。

  7. 【建議】減少使用ORDER BY，和業務溝通能不排序就不排序，或將排序放到程式端去做。ORDER BY、GROUP BY、DISTINCT 這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。

  8. 【建議】包含了ORDER BY、GROUP BY、DISTINCT 這些查詢的語句，WHERE 條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。

  9. 【建議】對單表的多次alter操作必須合併為一次對於，超過100W行的大表進行alter table，必須經過DBA稽核，並在業務低峰期執行，多個alter需整合在一起。 因為alter table會產生表鎖，期間阻塞對於該表的所有寫入，對於業務可能會產生極大影響。

  10. 【建議】批次運算元據時，需要控制事務處理間隔時間，進行必要的sleep。

  11. 【建議】事務裡包含SQL不超過5個。因為過長的事務會導致鎖資料較久，MySQL內部快取、連線消耗過多等問題。

  12. 【建議】事務裡更新語句儘量基於主鍵或UNIQUE KEY，如UPDATE… WHERE id=XX;否則會產生間隙鎖，內部擴大鎖定範圍，導致系統效能下降，產生死鎖。

11.PowerDesigner的使用

略

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

相關文章