《MySQL 進階篇》十六：資料庫的設計規範

ACatSmiling發表於2024-09-22
MySql資料庫

Author: ACatSmiling

Since: 2024-09-21

為什麼需要資料庫設計

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

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

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

  • 資料冗餘、資訊重複，儲存空間浪費。
  • 資料更新、插入、刪除的異常。
  • 無法正確表示資訊。
  • 丟失有效資訊。
  • 程式效能差。

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

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

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

正規化

正規化簡介

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

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

正規化都包括哪些

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

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

image-20231105101706772

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

鍵和相關屬性的概念

正規化的定義會使用到主鍵和候選鍵，資料庫中的鍵（Key）由一個或者多個屬性組成。資料表中常用的幾種鍵和屬性的定義：

  • 超鍵：能唯一標識元組的屬性集叫做超鍵。（屬性集，可以理解為一個或多個欄位的組合）
  • 候選鍵：如果超鍵不包括多餘的屬性，那麼這個超鍵就是候選鍵。使用者可以從候選鍵中選擇一個作為主鍵。
  • 外來鍵∶如果資料表 R1 中的某屬性集不是 R1 的主鍵，而是另一個資料表 R2 的主鍵，那麼這個屬性集就是資料表 R1 的外來鍵。
  • 主屬性：包含在任一候選鍵中的屬性稱為主屬性。
  • 非主屬性：與主屬性相對，指的是不包含在任何一個候選鍵中的屬性。

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

舉例來說，假設有兩個表：

  • 球員表（player）：球員編號 | 姓名 | 身份證號 | 年齡 | 球隊編號

  • 球隊表（team）：球隊編號 | 主教練 | 球隊所在地

那麼：

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

第一正規化

第一正規化主要是確保資料表中每個欄位的值必須具有原子性，也就是說資料表中每個欄位的值為不可再次拆分的最小資料單位。

我們在設計某個欄位的時候，對於欄位 x 來說，不能把欄位 x 拆分成欄位 x_1 和欄位 x_2。事實上，任何的 DBMS 都會滿足第一正規化的要求，不會將欄位進行拆分。

示例一：

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

    image-20231105111915247

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

    image-20231105112027619

示例二：

  • user 表的設計不符合第一正規化：

    image-20231105112149639

  • 其中，user_info 欄位為使用者資訊，可以進一步拆分成更小粒度的欄位，不符合資料庫設計對第一正規化的要求。將 user_info 拆分後如下：

    image-20231105112426133

示例三：

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

    image-20231105112551568

    image-20231105112627064

第二正規化

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

另外第二正規化只能完全函式依賴，不能部分函式依賴。

示例一：

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

示例二：

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

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

  • 但是這個資料表不滿足第二正規化，因為資料表中的欄位之間還存在著如下的對應關係：

    # 姓名和年齡部分依賴球員編號
(球員編號) → (姓名，年齡)

# 比賽時間, 比賽場地部分依賴(球員編號, 比賽編號)
(比賽編號) → (比賽時間, 比賽場地)

對於非主屬性來說，並非完全依賴候選鍵。這樣會產生怎樣的問題呢？（為什麼要滿足 2NF）

  • 資料冗餘： 如果一個球員可以參加 m 場比賽，那麼球員的姓名和年齡就重複了 m - 1 次。一個比賽也可能會有 n 個球員參加，比賽的時間和地點就重複了 n - 1 次。

  • 插入異常： 如果我們想要新增一場新的比賽，但是這時還沒有確定參加的球員都有誰，那麼就沒法插入。

  • 刪除異常： 如果我要刪除某個球員編號，如果沒有單獨儲存比賽表的話，就會同時把比賽資訊刪除掉。

  • 更新異常： 如果我們調整了某場比賽的時間，那麼資料表中所有這場比賽的時間都需要進行調整，否則就會出現一場比賽時間不同的情況。

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

表名 屬性（欄位）
球員 player 表 球員編號、姓名和年齡等屬性
比賽 game 表 比賽編號、比賽時間和比賽場地等屬性
球員比賽關係 player_game 表 球員編號、比賽編號和得分等屬性

這樣的話，每張資料表都符合第二正規化，也就避免了異常情況的發生。

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

示例三：

  • 定義一個名為 Orders 的關係，表示訂單和訂單行的資訊：

    image-20231105135908487

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

  • 修改為 Orders 表和 OrderDetails 表如下，此時符合第二正規化。

    image-20231105142331128

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

第三正規化

第三正規化是在第二正規化的基礎上，確保資料表中的每一個非主鍵欄位都和主鍵欄位直接相關，也就是說，要求資料表中的所有非主鍵欄位不能依賴於其他非主鍵欄位。即，不能存在非主屬性 A 依賴於非主屬性 B，非主屬性 B 依賴於主鍵 C 的情況，即存在 "A ---> B ---> C" 的決定關係。通俗地講，該規則的意思是所有非主鍵屬性之間不能有傳遞依賴關係，必須相互獨立。（這裡的主鍵可以擴充為候選鍵）

示例一：

  • 部門資訊表：每個部門有部門編號（dept_id）、部門名稱、部門簡介等資訊。
  • 員工資訊表：每個員工有員工編號、姓名、部門編號。列出部門編號後就不能再將部門名稱、部門簡介等與部門有關的資訊再加入員工資訊表中。
  • 如果不存在部門資訊表，則根據第三正規化（3NF）也應該構建它，否則就會有大量的資料冗餘。

示例二：

  • 如下表，商品類別名稱依賴於商品類別編號，不符合第三正規化：

    image-20231105143003915

  • 將其修改為商品表和商品類別表，商品表 goods 透過商品類別 id 欄位（category_id）與商品類別表 goods_category 進行關聯。

    image-20231105143223182

示例三：

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

    image-20231105192956621

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

  • 如果要達到 3NF 的要求，需要把資料表拆成下面這樣：

    表名 屬性（欄位）
    球隊表 球員編號、姓名和球隊名稱
    球員表 球隊名稱、球隊主教練

示例四：

  • 修改第二正規化中的示例三。

  • 此時的 Orders 關係包含 orderid、orderdate、customerid 和 companyname 屬性，主鍵定義為 orderid。customerid 和 companyname 均依賴於主鍵 orderid。例如，你需要透過 orderid 主鍵來查詢代表訂單中客戶的 customerid，同樣，你需要透過 orderid 主鍵查詢訂單中客戶的公司名稱（companyname）。然而， customerid 和 companyname 也是互相依靠的。為滿足第三正規化，可以改寫如下：

    image-20231105193501255

符合3NF後的資料模型通俗地講，2NF 和 3NF 通常以這句話概括："每個非鍵屬性依賴於鍵，依賴於整個鍵，並且除了鍵別無他物"。

小結

關於資料表的設計，有三個正規化要遵循：

  • 第一正規化（1NF），確保每列保持原子性。資料庫的每一列都是不可分割的原子資料項，不可再分的最小資料單元，而不能是集合、陣列、記錄等非原子資料項。
  • 第二正規化（2NF），確保每列都和主鍵完全依賴。尤其在複合主鍵的情況向下，非主鍵部分不應該依賴於部分主鍵。
  • 第三正規化（3NF），確保每列都和主鍵直接相關，而不是間接相關。

正規化的優點：資料的標準化有助於消除資料庫中的資料冗餘，第三正規化通常被認為在效能、擴充性和資料完整性方面達到了最好的平衡。

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

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

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

反正規化化

概述

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

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

規範化 VS 效能：

  1. 為滿足某種商業目標，資料庫效能比規範化資料庫更重要。
  2. 在資料規範化的同時，要綜合考慮資料庫的效能。
  3. 透過在給定的表中新增額外的欄位，以大量減少需要從中搜尋資訊所需的時間。
  4. 透過在給定的表中插入計算列，以方便查詢。

應用舉例

示例一：

  • 員工的資訊儲存在 employees 表中，部門資訊儲存在 departments 表中，透過 employees 表中的 department_id 欄位與 departments 表建立關聯關係。如果要查詢一個員工所在部門的名稱，可以使用下面的 SQL。如果經常需要進行這個操作，連線查詢就會浪費很多時間。可以在 employees 表中增加一個冗餘欄位 department_name，這樣就不用每次都進行連線操作了。

    SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

示例二：

  • 反正規化化的 goods商品資訊表 設計如下：

    image-20231105195225812

示例三：

  • 假設有 2 個表，分別是商品流水錶（trans ）和商品資訊表（goodsinfo）。商品流水錶裡有 400 萬條流水記錄，商品資訊表裡有 2000 條商品記錄。

    • 商品流水錶：

      image-20231105195442786

    • 商品資訊表：

      image-20231105195506227

  • 上面這兩個表，都是符合第三正規化要求的。但是，在專案的實施過程中，對流水的查詢頻率很高，而且為了獲取商品名稱，基本都會用到與商品資訊表的連線查詢。為了減少連線，可以直接把商品名稱欄位加到流水錶裡面。這樣一來，就可以直接從流水錶中獲取商品名稱欄位了。雖然增加了冗餘欄位，但是避免了關聯查詢，提升了查詢的效率。新的商品流水錶如下所示：

    image-20231105200705246

示例四：

  • 課程評論表 class_comment，對應的欄位名稱及含義如下：

    image-20231105200825357

  • 學生表 student，對應的欄位名稱及含義如下：

    image-20231105200859601

  • 在實際應用中，我們在顯示課程評論的時候，通常會顯示這個學生的暱稱，而不是學生 ID，因此，當我們想要查詢某個課程的前 1000 條評論時，需要關聯 class_comment 和 student這兩張表來進行查詢。

實驗資料：模擬兩張百萬量級的資料表

為了更好地進行 SQL 最佳化實驗，我們需要給學生表和課程評論表隨機模擬出百萬量級的資料。我們可以透過儲存過程來實現模擬資料。

  • 建立表：

    mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE atguigudb2;
Query OK, 1 row affected (0.01 sec)

mysql> USE atguigudb2;
Database changed

# 學生表
mysql> CREATE TABLE student(
    -> stu_id INT PRIMARY KEY AUTO_INCREMENT,
    -> stu_name VARCHAR(25),
    -> create_time DATETIME
    -> );
Query OK, 0 rows affected (0.03 sec)

# 課程評論表
mysql> CREATE TABLE class_comment(
    -> comment_id INT PRIMARY KEY AUTO_INCREMENT,
    -> class_id INT,
    -> comment_text VARCHAR(35),
    -> comment_time DATETIME,
    -> stu_id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

  • 建立儲存過程：

    # 建立向學生表中新增資料的儲存過程
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> DELIMITER ;

# 建立向課程評論表中新增資料的儲存過程
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> DELIMITER ;

  • 呼叫儲存過程：

    # 呼叫儲存過程，學生 id 從 10001 開始，新增 1000000 條資料
mysql> CALL batch_insert_student(10000, 1000000);
Query OK, 0 rows affected (31.58 sec)

# 新增資料的過程的呼叫，一個 1000000 條資料
mysql> CALL batch_insert_class_comments(10000, 1000000);
Query OK, 0 rows affected (34.81 sec)

mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*) FROM class_comment;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

  • 測試：

    mysql> 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;
+----------------------+---------------------+------------+
| comment_text         | comment_time        | stu_name   |
+----------------------+---------------------+------------+
| ea4d03873f7f3a26e1ba | 2018-12-14 03:26:26 | stu_209036 |
| 1fe6697da6c77d637497 | 2018-12-14 03:26:22 | stu_740513 |
| 5fafc1b2b297f23159cd | 2018-12-14 03:26:21 | stu_168595 |
| badca0f8d453eb0b67bf | 2018-12-10 15:29:16 | stu_288793 |
| 219f577d16b8005ca971 | 2018-12-10 15:28:48 | stu_259916 |
| dcb3e24911b5bcaa47a8 | 2018-12-10 15:28:11 | stu_815722 |
+----------------------+---------------------+------------+
10000 rows in set (0.04 sec)
    • 執行時長為 0.04 秒，對於網站的響應來說，這已經很慢了，使用者體驗會非常差。如果我們想要提升查詢的效率，可以允許適當的資料冗餘，也就是在商品評論表中增加使用者暱稱欄位，在 class_comment 資料表的基礎上增加 stu_name 欄位，就得到了 class_comment2 資料表。

  • 反正規化最佳化實驗對比：

    # 進行反正規化化設計

# 表的複製
mysql> CREATE TABLE class_comment1 AS SELECT * FROM class_comment;
Query OK, 1000000 rows affected (5.70 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

# 新增主鍵，保證 class_comment1 與 class_comment 的結構相同
mysql> ALTER TABLE class_comment1 ADD PRIMARY KEY(comment_id);
Query OK, 0 rows affected (8.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM class_comment1;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| class_comment1 |          0 | PRIMARY  |            1 | comment_id  | A         |      996244 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

# 向課程評論表中增加 stu_name 欄位
mysql> ALTER TABLE class_comment1 ADD stu_name VARCHAR(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 給新新增的欄位賦值
mysql> UPDATE class_comment1 c SET stu_name = (SELECT stu_name FROM student s WHERE c.stu_id = s.stu_id);
Query OK, 989941 rows affected (32.28 sec)
Rows matched: 1000000  Changed: 989941  Warnings: 0

  • 如果我們想要查詢課程 ID 為 10001 的前 10000 條評論，需要寫成下面這樣：

    mysql> SELECT comment_text, comment_time, stu_name FROM class_comment1 WHERE class_id = 10001 ORDER BY class_id DESC LIMIT 10000;
+----------------------+---------------------+------------+
| comment_text         | comment_time        | stu_name   |
+----------------------+---------------------+------------+
| 98ac57e47f969ccb3d5e | 2018-01-01 00:00:58 | stu_657565 |
| 9417961cb63119c96cbc | 2018-01-01 00:01:10 | stu_236175 |
| cb5b9be56b3ce6de9f0f | 2018-01-01 00:01:30 | stu_659727 |
| 339b5f86158833d800c0 | 2018-01-04 11:59:26 | stu_411945 |
| 2e069c3facbd62cf2051 | 2018-01-04 11:59:37 | stu_759542 |
| 979fd006b1ea975b5c4d | 2018-01-04 12:00:28 | stu_160871 |
+----------------------+---------------------+------------+
10000 rows in set (0.00 sec)
    • 最佳化之後只需要掃描一次聚集索引即可，執行時間為 0.00 秒，查詢時間比之前少很多。 你能看到，在資料量大的情況下，查詢效率會有顯著的提升。

反正規化的新問題

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

反正規化的適用場景

當冗餘資訊有價值或者能大幅度提高查詢效率的時候，我們才會採取反正規化的最佳化。

  1. 增加冗餘欄位的建議。

    • 這個冗餘欄位不需要經常進行修改。
    • 這個冗餘欄位查詢的時候不可或缺（因為經常要用，所以才增加該冗餘欄位）。

  2. 歷史快照、歷史資料的需要。

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

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

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

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

注意：反正規化反的是第二正規化或第三正規化，第一正規化是一定要遵守的。

巴斯正規化

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

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

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

案例

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

image-20231105231508896

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

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

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

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

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

是否符合三正規化

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

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

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

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

存在的問題

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

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

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

問題解決

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

如果在關係 R 中，U 為主鍵，A 屬性是主鍵的一個屬性，若存在 A ---> Y，Y 為主屬性（也就是非候選碼中的屬性），則該關係不屬於 BCNF。

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

  • 倉庫表 ：（倉庫名，管理員）
  • 庫存表 ：（倉庫名，物品名，數量）

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

再舉例如下：

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

image-20231106082639953

這個表的設計滿足三正規化，但是這裡存在另一個依賴關係，"專業" 依賴於 "導師"，也就是說每個導師只做一個專業方面的導師，只要知道了是哪個導師，自然就知道是哪個專業的了。

所以這個表的部分主鍵 Major 依賴於非主鍵屬性 Advisor，那麼我們可以進行以下的調整，拆分成 2 個表：

學生導師表：

image-20231106090057819

導師表：

image-20231106090132192

第四正規化

多值依賴的概念：

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

示例一：

職工表（職工編號，職工孩子姓名，職工選修課程）。

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

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

示例二：

比如建立課程、教師、教材的模型。我們規定，每門課程有對應的一組教師，每門課程也有對應的一組教材，一門課程使用的教材和教師沒有關係。我們建立的關係表如下：課程 ID，教師 ID，教材 ID，這三列作為聯合主鍵。

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

image-20231106090818655

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

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

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

image-20231106104448526

image-20231106104509220

第五正規化、域鍵正規化

除了第四正規化外，還有更高階的第五正規化（又稱完美正規化）和域鍵正規化（DKNF）。

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

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

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

實戰案例

需求：商超進貨系統中的進貨單表進行剖析。

進貨單表：

image-20231106124225094

這個表中的欄位很多，表裡的資料量也很驚人。大量重複導致表變得龐大，效率極低。如何改造？

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

迭代 1 次：考慮 1NF

第一正規化要求：所有的欄位都是基本資料欄位，不可進一步拆分。這裡需要確認，所有的列中，每個欄位只包含一種資料。

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

image-20231106184832839

迭代 2 次：考慮 2NF

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

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

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

首先，進貨單明細表裡面的 "goodsname (名稱)"，"specification (規格)"，"unit (單位)" 這些資訊是商品的屬性，只依賴於 "barcode (條碼)"，不完全依賴主鍵，可以拆分出去。把這 3 個欄位加上它們所依賴的欄位 "barcode (條碼)"，拆分形成一個新的資料表 "商品資訊表"。這樣一來，原來的資料表就被拆分成了兩個表。

  • 商品資訊表：

    image-20231106190656366

  • 進貨單表：

    image-20231106190744501

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

  • 進貨單頭表：

    image-20231106191110728

  • 進貨單明細表：

    image-20231106191217636

  • 商品資訊表：

    image-20231106190656366

現在來分析一下拆分後的 3 個表，保證這 3 個表都滿足第二正規化的要求。

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

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

  • 進貨單明細表：

    image-20231106191535891

  • 商品資訊表：

    image-20231106191617188

拆分後的 3 個資料表，就全部滿足了第二正規化的要求。

迭代 3 次：考慮 3NF

進貨單頭表還有資料冗餘的可能。因為 "supplername" 依賴 "supplierid"，那麼，這個時候，就可以按照第三正規化的原則進行拆分了。進一步拆分一下進貨單頭表，把它拆解成供貨商表和進貨單頭表。

  • 供貨商表：

    image-20231106194914167

  • 進貨單頭表：

    image-20231106194949527

這 2 個表都滿足第三正規化的要求了。

反正規化化：業務優先的原則

在進貨單明細表中，"quantity * importprice = importvalue"，"importprice"、"quantity" 和 "importvalue"，可以透過任意兩個計算出第三個來，這就存在冗餘欄位。如果嚴格按照第三正規化的要求，應該進行進一步最佳化。最佳化的辦法是刪除其中一個欄位，只保留另外 2 個，這樣就沒有冗餘資料了。

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

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

對於 "quantity * importprice =importvalue"，看起來 "importvalue" 似乎是冗餘欄位，但並不會導致資料不一致，可是，如果把這個欄位取消，是會影響業務的。

因為有的時候，供貨商會經常進行一些促銷活動，按金額促銷，那他們拿來的進貨單隻有金額，沒有價格。而 "importprice" 反而是透過 "importvalue / quantity" 計算出來的，經過四捨五入，會產生較大的誤差。這樣日積月累，最終會導致查詢結果出現較大偏差，影響系統的可靠性。

舉例：進貨金額（importvalue）是 25.5 元，數量（quantity）是 34，那麼進貨價格（importprice）就等於 25.5 / 34 = 0.74 元，但是如果用這個計算出來的進貨價格（importprice）來計算進貨金額，那麼，進貨金額（importvalue）就等於 0.74 x 34 = 25.16元，其中相差了 25.5 - 25.16 = 0.34 元。

所以，本著業務優先的原則，在不影響系統可靠性的前提下，可適當增加資料冗餘，保留 "importvalue"，"importprice" 和 "quantity"。

因此，最終我們可以把進貨單表拆分成下面的 4 個表：

  • 進貨單明細表：

    image-20231106201017268

  • 商品資訊表：

    image-20231106201037491

  • 供貨商表：

    image-20231106201059846

  • 進貨單頭表：

    image-20231106201118266

這樣一來，我們就避免了冗餘，而且還能夠滿足業務的需求，這樣的資料表設計，才是合格的設計。

ER 模型

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

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

ER 模型包括哪些要素

ER 模型中有三個要素，分別是實體、屬性和關係。

  • 實體，可以看做是資料物件，往往對應於現實生活中的真實存在的個體。在 ER 模型中，用矩形來表示。實體分為兩類，分別是強實體和弱實體。強實體是指不依賴於其他實體的實體；弱實體是指對另一個實體有很強的依賴關係的實體。
  • 屬性， 則是指實體的特性。比如超市的地址、聯絡電話、員工數等。在 ER 模型中用橢圓形來表示。
  • 關係， 則是指實體之間的聯絡。比如超市把商品賣給顧客，就是一種超市與顧客之間的聯絡。在 ER 模型中用菱形來表示。

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

關係型別

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

  • 一對一：指實體之間的關係是一一對應的，比如個人與身份證資訊之間的關係就是一對一的關係。一個人只能有一個身份證資訊，一個身份證資訊也只屬於一個人。
  • 一對多∶指一邊的實體透過關係，可以對應多個另外一邊的實體。相反，另外一邊的實體透過這個關係，則只能對應唯一的一邊的實體。比如說，新建一個班級表，而每個班級都有多個學生，每個學生則對應一個班級，班級對學生就是一對多的關係。
  • 多對多：指關係兩邊的實體都可以透過關係對應多個對方的實體。比如在進貨模組中，供貨商與超市之間的關係就是多對多的關係，一個供貨商可以給多個超市供貨，一個超市也可以從多個供貨商那裡採購商品。再比如一個選課表，有許多科目，每個科目有很多學生選，而每個學生又可以選擇多個科目，這就是多對多的關係。

建模分析

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

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

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

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

image-20231106201758591

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

  1. 使用者可以在電商平臺新增多個地址。
  2. 使用者只能擁有一個購物車。
  3. 使用者可以生成多個訂單。
  4. 使用者可以發表多條評論。
  5. 一件商品可以有多條評論。
  6. 每一個商品分類包含多種商品。
  7. 一個訂單可以包含多個商品，一個商品可以在多個訂單裡。
  8. 訂單中又包含多個訂單詳情，因為一個訂單中可能包含不同種類的商品。

ER 模型的細化

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

因此，我們需要進一步去設計一下這個 ER 模型的各個區域性，也就是細化下電商的具體業務流程，然後把它們綜合到一起，形成一個完整的 ER 模型。這樣可以理清資料庫的設計思路。

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

  1. 地址實體：包括使用者編號、省、市、地區、收件人、聯絡電話、是否是預設地址。
  2. 使用者實體：包括使用者編號、使用者名稱稱、暱稱、使用者密碼、手機號、郵箱、頭像、使用者級別。
  3. 購物車實體：包括購物車編號、使用者編號、商品編號、商品數量、圖片檔案 url。
  4. 訂單實體：包括訂單編號、收貨人、收件人電話、總金額、使用者編號、付款方式、送貨地址、下單時間。
  5. 訂單詳情實體：包括訂單詳情編號、訂單編號、商品名稱、商品編號、商品數量。
  6. 商品實體：包括商品編號、價格、商品名稱、分類編號、是否銷售，規格、顏色。
  7. 評論實體：包括評論 id、評論內容、評論時間、使用者編號、商品編號。
  8. 商品分類實體：包括類別編號、類別名稱、父類別編號。

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

image-20231106202151621

ER 模型圖轉換成資料表

透過繪製 ER 模型已經理清了業務邏輯，現在就要進行非常重要的一步了：把繪製好的 ER 模型，轉換成具體的資料表。下面介紹下轉換的原則：

  1. 一個實體通常轉換成一個資料表；
  2. 一個多對多的關係，通常也轉換成一個 資料表；
  3. 一個 1 對 1，或者 1 對多的關係，往往透過表的外來鍵來表達，而不是設計一個新的資料表；
  4. 屬性轉換成表的欄位。

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

1、一個實體通常轉換成一個資料表

先來看一下強實體轉換成資料表：

  • 使用者實體轉換成使用者表（user_info）的程式碼如下所示：

    CREATE TABLE `user_info`(
  `id` bigint(20)NOT NULL AUTO_INCREMENT COMMENT '編號',
  `user_name` varchar(200)DEFAULT NULL COMMENT '使用者名稱稱',
  `nick_name` varchar (200)DEFAULT NULL COMMENT '使用者暱稱',
  `passwd` varchar (200)DEFAULT NULL COMMENT '使用者密碼',
  `phone_num` varchar (200) DEFAULT NULL COMMENT '手機號',
  `email` varchar(200) DEFAULT NULL COMMENT '郵箱',
  `head_img` varchar ( 200)DEFAULT NULL COMMENT'頭像',
  `user_level` varchar(200) DEFAULT NULL COMMENT '使用者級別',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='使用者表';

  • 商品分類實體轉換成商品分類表（base_category），由於商品分類可以有一級分類和二級分類，比如一級分類有家居、手機等等分類，二級分類可以根據手機的一級分類分為手機配件，運營商等，這裡我們把商品分類實體規劃為兩張表，分別是一級分類表和二級分類表，之所以這麼規劃是因為一級分類和二級分類都是有限的，儲存為兩張表業務結構更加清晰。

    # 一級分類表
  CREATE TABLE`base_category1`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '編號',
  `name` varchar (10) NOT-NULL COMMENT '分類名稱',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='一級分類表';

# 二級分類表
CREATE TABLE `base_category2`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '編號',
  `name` varchar (208) NOT NULL COMMENT '二級分類名稱',
  `category1_id` bigint(20) DEFAULT NULL COMMENT '一級分類編號',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='二級分類表';

  • 那麼如果規劃為—張表呢，表結構如下所示：

    CREATE TABLE `base_category`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '編號',
  `name` varchar (200)NOT NULL COMMENT '分類名不',
  `category_parent_id` bigint(20) DEFAULT NULL COMMENT '父分類編號',
  PRIMARY KEY ( id  ) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT= '分類表';
    • 如果這樣分類的話，那麼查詢一級分類時候，就需要判斷父分類編號是否為空，但是如果插入二級分類的時候也是空，就容易造成業務資料混亂。而且查詢二級分類的時候 IS NOT NULL 條件是無法使用到索引的。同時，這樣的設計也不符合第二正規化（因為父分類編號並不依賴分類編號 ID，因為父分類編號可以有很多資料為 NULL)，所以需要進行表的拆分。因此無論是業務需求還是資料庫表的規範來看都應該拆分為兩張表。

下面再把弱實體轉換成資料表：

  • 地址實體轉換成地址表（user_address），如下所示：

    CREATE TABLE `user_address`(
`id` bigint(20)NOT NULL AUTO_INCREMENT COMMENT '編號',
'province' varchar (500)DEFAULT NULL COMMENT'省',
`city` varchar (500) DEFAULT NULL COMMENT '市',
`user_address` varchar (500) DEFAULT NULL COMMENT '具體地址',
`user_id bipint(20)` DEFAULT NULL COMMENT '使用者id',
`consignee` varchar( 40) DEFAULT NULL COMMENT '收件人',
`phone_num ` varchar(40) DEFAULT NULL COMMENT ‘聯絡方式',
`is_default` varchar( 1) DEFAULT NULL COMMENT '是否是預設',
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='使用者地址表';

  • 訂單實體轉換成訂單表（order_info)，如下所示，實際業務中訂單的資訊會非常多，我們這裡做了簡化。

    CREATE TABLE `order_info`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '編號',
  `consignee` varchar (100) DEFAULT NULL COMMENT '收貨人',
  `consignee_tel` varchar(20) DEFAULT NULL COMMENT'收件人電話',
  `total_amount` decimal( 10,2)DEFAULT NULL COMMENT '總金額',
  `user_id` bigint(20) DEFAULT NULL COMMENT'使用者id',
  `payment_way` varchar(20)DEFAULT NULL COMMENT'付款方式',
  `delivery_address` varchar( 1000) DEFAULT NULL COMMENT'送貨地址',
  `create_time` datetime DEFAULT NULL COMMENT'下單時間',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=1 DEFAULT CHARSET=utf8 ROW_FORNAT=DYNAMIC COMMENT= '訂單表';

  • 訂單詳情實體轉換成訂單詳情表（order_detail)，如下所示。（用於體現多對多關係的，見下節）：

    # 訂單詳情表
CREATE TABLE `order_detail`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單詳情編號',
  `order_id` bigint(20) DEFAULT NULL COMMENT '訂單編號',
  `sku_id` bigint(20)DEFAULT NULL COMMENT 'sku_id',
  `sku_name` varchar(200) DEFAULT NULL COMMENT 'sku名稱',
  `sku_num` varchar(200) DEFAULT NULL COMMENT '購買個數',
  `create_time` datetime DEFAULT NULL COMMENT'操作時間',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='訂單明細表';

  • 購物車實體轉換成購物車表（cart_info），如下所示：

    CREATE TABLE `cart_info`(
  `cart_id` bigint(20)NOT NULL AUTO_INCREMENT COMMENT'編號',
  `user_id` varchar(200) DEFAULT NULL COMMENT'使用者id',
  `sku_id` bigint(20)DEFAULT NULL COMMENT 'skuid' ,
  `sku_num` int( 11)DEFAULT NULL COMMENT '數量',
  `img_url` varchar ( 500) DEFAULT NULL COMMENT '圖片檔案',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='購物車表';

  • 評論實體轉換成評論表（members），如下所示：

    CREATE TABLE `sku_comments`(
  `comment_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT'評論編號',
  `user_id` bigin)t (20) DEFAULT NULL COMMENT'使用者編號',
  `sku_id` decimal( 10,0) DEFAULT NULI COMMENT '商品編號',
  `comment` varchar(2000)DEFAULT NULL COMMENT '評論內容',
  `create_time` datetime DEFAULT NULL COMMENT '評論時間',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=45 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMNENT='商品評論表';

  • 商品實體轉換成商品表（members），如下所示：

    CREATE TABLE `sku_info`(
  `sku_id` bigint(20) NOT NULL AUTO_INCREMENT COPMENT'商品編號(itemID)',
   `price` decimal(10,0) DEFAULT NULL COMMENT'價格',
  `sku_name` varchar(200) DEFAULT NULL COMMENT 'sku名稱',
  `sku_desc` varchar(2000) DEFAULT NULL COMMENT'商品規格描述',
  `category3_id` bigint(20) DEFAULT NULL COMMENT'三級分類id(冗餘)',
  `color` varchar (2000) DEFAULT NULL COMMENT '顏色',
  `is_sale` tinyint(3) NOT NULL DEFAULT '0' CONMMENT'是否銷售(1:是0:否)',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=45 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT= '商品表';

2、一個多對多的關係轉換成一個資料表

這個 ER 模型中的多對多的關係有 1 個，即商品和訂單之間的關係，同品類的商品可以出現在不同的訂單中，不同的訂單也可以包含同一型別的商品，所以它們之間的關係是多對多。針對這種情況需要設計一個獨立的表來表示，這種表一般稱為中間表。

我們可以設計一個獨立的訂單詳情表，來代表商品和訂單之間的包含關係。這個表關聯到 2 個實體，分別是訂單、商品。所以，表中必須要包括這 2 個實體轉換成的表的主鍵。除此之外，我們還要包括該關係自有的屬性：商品數量，商品下單價格以及商品名稱。

# 訂單詳情表
CREATE TABLE `order_detail`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單詳情編號',
`order_id` bigint(20)DEFAULT NULL COMMENT '訂單編號',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id ',
`sku_name` varchar(200) DEFAULT NULL COMMENT 'sku名稱',
`sku_num` varchar(200)DEFAULT NULL COMMENT '購買個數',
`create_time` datetime DEFAULT NULL COMMENT '操作時間',
PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='訂單明細表';

公司的訂單相關表主要有：order、order_item、sku、spu…，其中 order_detail 相當於 order_item。

3、透過外來鍵來表達1對多的關係

在上面的表的設計中，我們可以用外來鍵來表達一對多的關係。比如在商品評論表 sku_comments中，我們分別把 user_id、sku_id 定義成外來鍵，以使用下面的語句設定外來鍵。

CONSTRAINT fk_comment_user FOREIGN KEY (user_id) REFERENCES user_info (id)

CONSTRAINT fk_comment_sku FOREIGN KEY (sku_id) REFERENCES sku_info (sku_id)

外來鍵約束主要是在資料庫層面上保證資料的一致性，但是因為插入和更新資料需要檢查外來鍵，理論上效能會有所下降，對效能是負面的影響。

實際的專案，不建議使用外來鍵。一方面是降低開發的複雜度（有外來鍵的話主從表類的操作必須先操作主表），另外是有外來鍵在處理資料的時候非常麻煩。在電商平臺，由於併發業務量比較大，所以一般不設定外來鍵，以免影響資料庫效能。

在應用層面做資料的一致性檢查，本來就是一個正常的功能需求。如學生選課的場景，課程肯定不是輸入的，而是透過下拉或查詢等方式從系統中進行選取，就能夠保證是合法的課程 ID，因此就不需要靠資料庫的外來鍵來檢查了。

4、把屬性轉換成表的欄位

在剛剛的設計中，我們也完成了把屬性都轉換成了表的欄位，比如把商品屬性轉換成了商品資訊表中的欄位。

CREATE TABLE `sku_info`(
  `sku_id` bigint(20) NOT NULL AUTO_INCREMENT COPMENT'商品編號(itemID)',
   `price` decimal(10,0) DEFAULT NULL COMMENT'價格',
  `sku_name` varchar(200) DEFAULT NULL COMMENT 'sku名稱',
  `sku_desc` varchar(2000) DEFAULT NULL COMMENT'商品規格描述',
  `category3_id` bigint(20) DEFAULT NULL COMMENT'三級分類id(冗餘)',
  `color` varchar (2000) DEFAULT NULL COMMENT '顏色',
  `is_sale` tinyint(3) NOT NULL DEFAULT '0' CONMMENT'是否銷售(1:是0:否)',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCRENENT=45 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT= '商品表';

到這裡，我們透過建立電商專案業務流程的 ER 模型，再把 ER 模型轉換成具體的資料表的過程，完成利用 ER 模型設計電商專案資料庫的工作。

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

image-20231106205251437

資料表的設計原則

綜合以上內容，總結出資料表設計的一般原則：三少一多。

  1. 資料表的個數越少越好。
    • RDBMS 的核心在於對實體和聯絡的定義，也就是 E-R 圖（Entity Relationship Diagram），資料表越少，證明實體和聯絡設計得越簡潔，既方便理解又方便操作。
  2. 資料表中的欄位個數越少越好。
    • 欄位個數越多，資料冗餘的可能性越大。設定欄位個數少的前提是各個欄位相互獨立，而不是某個欄位的取值可以由其他欄位計算出來。當然欄位個數少是相對的，通常會在資料冗餘和檢索效率中進行平衡。
  3. 資料表中聯合主鍵的欄位個數越少越好。
    • 設定主鍵是為了確定唯一性，當一個欄位無法確定唯一性的時候，就需要採用聯合主鍵的方式（也就是用多個欄位來定義一個主健）。聯合主鍵中的欄位越多，佔用的索列空間越大，不僅會加大理解難度，還會增加執行時間和索引空間，因此聯合主鍵的欄位個數越少越好。
  4. 使用主鍵和外來鍵越多越好。
    • 資料庫的設計實際上就是定義各種表，以及各種欄位之間的關係。這些關係越多，證明這些實體之間的冗餘度越低，利用度越高。這樣做的好處在於不僅保證了資料表之間的獨立性，還能提升相互之間的關聯使用率。
    • 這裡的外來鍵指業務上實現外來鍵，也就是邏輯外來鍵。不一定使用外來鍵約束實現。

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

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

資料庫物件編寫建議

前面講了資料庫的設計規範，下面給出的這些規範適用於大多數公司，按照下面的規範來使用資料庫，這樣資料庫可以發揮出更高的效能。

關於庫

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

關於表、列

  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. 【強制】禁止在資料庫中儲存圖片、檔案等大的二進位制資料。通常檔案很大，短時間內造成資料量快速增長，資料庫進行資料庫讀取時，通常會進行大量的隨機 I/O 操作，檔案很大時，I/O 操作很耗時。通常儲存於檔案伺服器（如 FastDFS），資料庫只儲存檔案地址資訊。

  11. 【建議】建表時關於主鍵：表必須有主鍵。

    • 強制要求主鍵為 id，型別為 int 或 bigint，且為 auto_increment，建議使用 unsigned 無符號型。
    • 標識表裡每一行主體的欄位不要設為主鍵，建議設為其他欄位如 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='網站使用者基本資訊';

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

關於索引

  1. 【強制】InnoDB 表必須主鍵為 id int/bigint auto_increment，且主鍵值禁止被更新。
  2. 【強制】InnoDB 和 MyISAM 儲存引擎表，索引型別必須為 BTREE（此處是官方寫法，代表的其實就是 B+Tree）。
  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) 為冗餘索引，需要刪除。

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 操作必須合併為一次。對於超過 100 W 行的大表進行 ALTER TABLE，必須經過 DBA 稽核，並在業務低峰期執行，多個 ALTER 需整合在一起。 因為 ALTER TABLE 會產生表鎖，期間阻塞對於該表的所有寫入，對於業務可能會產生極大影響。
  10. 【建議】批次運算元據時，需要控制事務處理間隔時間，進行必要的 sleep。
  11. 【建議】事務裡包含 SQL 不超過 5 個。因為過長的事務會導致鎖資料較久，MySQL 內部快取、連線消耗過多等問題。
  12. 【建議】事務裡更新語句儘量基於主鍵或 UNIQUE KEY，如 UPDATE… WHERE id = XX，否則會產生間隙鎖，內部擴大鎖定範圍，導致系統效能下降，產生死鎖。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md

相關文章