1. 范式的概念

1.1 范式分類

目前關(guān)系型數(shù)據(jù)庫有六種常見范式，按照范式級別，從低到高分別是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又稱完美范式）。

數(shù)據(jù)庫的范式設(shè)計越高階，冗余度就越低，同時高階的范式一定符合低階范式的要求，滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎(chǔ)上進一步滿足更多規(guī)范要求的稱為第二范式（2NF），其余范式以次類推。

一般來說，在關(guān)系型數(shù)據(jù)庫設(shè)計中，最高也就遵循到BCNF，普遍還是3NF。但也不絕對，有時候為了提高某些查詢性能，我們還需要破壞范式規(guī)則，也就是反規(guī)范化。

1.2 鍵和相關(guān)屬性的概念

• 超鍵：能唯一標(biāo)識元組的屬性集叫做超鍵（主鍵、主鍵+任意字段、任何組合能唯一的能標(biāo)識這一行的就是超鍵）。
• 候選鍵：如果超鍵不包括多余的屬性，那么這個超鍵就是候選鍵（最小的超鍵，能唯一標(biāo)識一行數(shù)據(jù)的字段）。
• 主鍵：用戶可以從候選鍵中選擇一個作為主鍵。
• 外鍵：如果數(shù)據(jù)表R1中的某屬性集不是R1的主鍵，而是另一個數(shù)據(jù)表R2的主鍵，那么這個屬性集就是數(shù)據(jù)表R1的外鍵。
• 主屬性：包含在任一候選鍵中的屬性稱為主屬性（候選鍵涉及到的屬性）。
• 非主屬性：與主屬性相對，指的是不包含在任何一個候選鍵中的屬性（候選鍵不涉及到的屬性）。

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

2. 第一范式（1st NF）

2.1 概念

第一范式主要是確保數(shù)據(jù)表中每個字段的值必須具有原于性，也就是說數(shù)據(jù)表中每個字段的值為不可再次拆分的最小數(shù)據(jù)單元。

我們在設(shè)計某個字段的時候，對于字段X來說，不能把字段X拆分成字段X-1和字段X-2。事實上，任何的DBMS都會滿足第一范式的要求，不會將字段進行拆分。

2.2 舉例

2.1 總結(jié)

1NF告訴我們字段屬性需要是原子性的，數(shù)據(jù)不可再分

3. 第二范式（2nd NF）

3.1 概念

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

3.2 舉例

非完全依賴候選鍵產(chǎn)生的問題：

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

改進后：

3.3 總結(jié)

第二范式（2NF）要求實體的屬性完全依賴主關(guān)鍵字。如果存在不完全依賴，那么這個屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來形成一個新的實體，新實體與元實體之間是一對多的關(guān)系。

4. 第三范式(3rd NF)

4.1 概念

第三范式是在第二范式的基礎(chǔ)上，確保數(shù)據(jù)表中的每一個非主鍵字段都和主鍵字段直接相關(guān)，也就是說，要求數(shù)據(jù)表中的所有非主鍵字段不能依賴于其他非主鍵字段。（即，不能存存非主屬性A依賴于非主屬性B，非主屬性B依賴于主鍵C的情況，即存在“A→B→C”的決定關(guān)系）通俗地講，該規(guī)則的意思是所有非主鍵屬性之間不能有依賴關(guān)系，必須相互獨立。

這里的主鍵可以拓展為候選鍵。

4.2 舉例

4.3 總結(jié)

符合3NF后的數(shù)據(jù)模型通俗地講，2NF和3NF通常以這句話概括：“每個非鍵屬性依賴于鍵，依賴于整個鍵，并且除了鍵別無他物。

5. 范式的優(yōu)缺點

5.1 優(yōu)點

數(shù)據(jù)的標(biāo)準(zhǔn)化有助于`消除數(shù)據(jù)庫中的數(shù)據(jù)冗余``，第三范式（3NF）通常被認(rèn)為在性能、擴展性和數(shù)據(jù)完整性方面達到了最好的平衡。

5.2 缺點

范式的使用，可能降低查詢的效率。因為范式等級越高，設(shè)計出來的數(shù)據(jù)表就越多、越精細(xì)，數(shù)據(jù)
的冗余度就越低，進行數(shù)據(jù)查詢的時候就可能需要關(guān)聯(lián)多張表，這不但代價昂貴，也可能使一些索引策略無效。

6. 反范式化

6.1 概念

有的時候不能簡單按照規(guī)范要求設(shè)計數(shù)據(jù)表，因為有的數(shù)據(jù)看似冗余，其實對業(yè)務(wù)來說十分重要。這個時候，我們就要遵循業(yè)務(wù)優(yōu)先的原則，首先滿足業(yè)務(wù)需求，再盡量減少冗余。

如果數(shù)據(jù)庫中的數(shù)據(jù)量比較大，系統(tǒng)的UV和PV訪問頻次比較高，則完全按照MySQL的三大范式設(shè)計數(shù)據(jù)表，讀數(shù)據(jù)時會產(chǎn)生大量的關(guān)聯(lián)查詢，在一定程度上會影響數(shù)據(jù)庫的讀性能。如果我們想對查詢效率進行優(yōu)化，反范式優(yōu)化也是一種優(yōu)化思路。此時，可以通過在數(shù)據(jù)表中增加冗余字段來提高數(shù)據(jù)庫的讀性能。

6.2 規(guī)范與性能平衡

1. 為滿足某種商業(yè)目標(biāo)，數(shù)據(jù)庫性能比規(guī)范化數(shù)據(jù)庫更重要
2. 在數(shù)據(jù)規(guī)范化的同時，要綜合考慮數(shù)據(jù)庫的性能
3. 通過在給定的表中添加額外的字段，以大量減少需要從中搜索信息所需的時間
4. 通過在給定的表中插入計算列，以方便查詢

6.3 舉例

6.4 代碼演示

#07-數(shù)據(jù)表的設(shè)計規(guī)范

#反范式化的舉例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#學(xué)生表
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
);

###創(chuàng)建向?qū)W生表中添加數(shù)據(jù)的存儲過程
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 ;

#調(diào)用存儲過程，學(xué)生id從10001開始，添加1000000數(shù)據(jù)
CALL batch_insert_student(10000,1000000);

####創(chuàng)建向課程評論表中添加數(shù)據(jù)的存儲過程
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 ;

#添加數(shù)據(jù)的存儲過程的調(diào)用，一共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;


#####進行反范式化的設(shè)計######
#表的復(fù)制
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主鍵，保證class_comment1 與class_comment的結(jié)構(gòu)相同
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;

6.5 反范式的問題

• 存儲空間變大了
• 一個表中字段做了修改，另一個表中冗余的字段也需要做同步修改，否則數(shù)據(jù)不一致
• 若采用存儲過程來支持?jǐn)?shù)據(jù)的更新、刪除等額外操作，如果更新頻繁，會非常消耗系統(tǒng)資源
• 在數(shù)據(jù)量小的情況下，反范式不能體現(xiàn)性能的優(yōu)勢，可能還會讓數(shù)據(jù)庫的設(shè)計更加復(fù)雜

6.6 反范式的適用場景

當(dāng)冗余信息有價值或者能大幅度提高查詢效率的時候，我們才會采取反范式的優(yōu)化。

6.6.1 增加冗余的建議

1. 不需要經(jīng)常進行修改；
2. 查詢的時候不可或缺。

6.6.2 歷史快照、歷史數(shù)據(jù)的需要

在現(xiàn)實生活中，我們經(jīng)常需要一些冗余信息，比如訂單中的收貨人信息，包括姓名、電話和地址等。每次發(fā)生的訂單收貨信息都屬于歷史快照，需要進行保存，用戶修改自己的信息時，這時保存這些冗余信息可以起到歷史證明的作用。

反范式優(yōu)化也常用在數(shù)據(jù)倉庫的設(shè)計中，因為數(shù)據(jù)倉庫通常存儲歷史數(shù)據(jù)，對增刪改的實時性要求不強，對歷史數(shù)據(jù)的分析需求強。這時適當(dāng)允許數(shù)據(jù)的冗余度，更方便進行數(shù)據(jù)分析。

簡單總結(jié)下數(shù)據(jù)倉庫和數(shù)據(jù)庫在使用上的區(qū)別：

1. 數(shù)據(jù)庫設(shè)計的目的在于捕獲數(shù)據(jù)，而數(shù)據(jù)倉庫設(shè)計的目的在于分析數(shù)據(jù)；
2. 數(shù)據(jù)庫對數(shù)據(jù)的增刪改實時性要求強，需要存儲在線的用戶數(shù)據(jù)，而數(shù)據(jù)倉庫存儲的一般是歷史數(shù)據(jù)；
3. 數(shù)據(jù)庫設(shè)計需要盡量避免冗余，但為了提高查詢效率也允許一定的冗余度，而數(shù)據(jù)倉庫在設(shè)計上更偏向采用反范式設(shè)計。

7. BCNF（巴斯范式）

7.1 概念

人們在3NF的基礎(chǔ)上進行了改進，提出了巴斯范式（BCNF），也叫做巴斯-科德范式（Boyce-Codd NormalForm）。BCNF被認(rèn)為沒有新的設(shè)計規(guī)范加入，只是對第三范式中設(shè)計規(guī)范要求更強，使得數(shù)據(jù)庫冗余度更小。所以，稱為是修正的第三范式，或擴充的第三范式，BCNF不被稱為第四范式。

若一個關(guān)系達到了第三范式，并且它只有一個候選鍵，或者它的每個候選鍵都是單屬性，則該關(guān)系自然達到BC范式。

一般來說，一個數(shù)據(jù)庫設(shè)計符合3NF或BCNF就可以了。

7.2 舉例

文章來源地址http://www.zghlxwxcb.cn/news/detail-447044.html

到了這里，關(guān)于數(shù)據(jù)庫的設(shè)計規(guī)范：第一范式、第二范式、第三范式、巴斯范式的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！