資料庫基礎知識詳解三：MVCC、正規化以及表連線方式

投三分的金閃閃發表於2022-04-09
資料庫MVC

寫在文章前：本系列文章用於博主自己歸納複習一些基礎知識，同時也分享給可能需要的人，因為水平有限，肯定存在諸多不足以及技術性錯誤，請大佬們及時指正。

8.MVCC

多版本併發控制（Multi-Version Concurrency Control, MVCC），MVCC在資料表中每行記錄後面都儲存有兩個隱藏的列，用來儲存更新資訊的事務號：DB_TRX_ID和一個回滾指標：DB_ROLL_PTR（指向該行資料上一次修改前的資料，儲存在undo log中）。

系統版本號：每開始一個新的事務，系統版本號就會自動遞增）。（更新包括增刪改）

更新事務號：更新一個資料行時的事務版本號（事務版本號：事務開始時的系統版本號。）

各種操作具體實現：

  • 插入操作時，記錄建立版本號。
  • 刪除操作時，記錄刪除版本號。
  • 更新操作時，先記錄刪除版本號，再新增一行記錄建立版本號。
  • 查詢操作時，要符合以下條件才能被查詢出來：刪除的版本號未定義或大於當前事務版本號（刪除操作是在當前事務啟動之後做的）。建立的版本號小於或等於當前事務版本號（建立操作是事務完成或者在事務啟動之前完成）

通過版本號減少了鎖的爭用，提高了系統效能。可以實現提交讀和可重複讀兩種隔離級別，未提交讀級別無需使用MVCC。

快照讀：使用MVCC讀取的是快照中的資料，這樣可以減少加鎖帶來的開銷。

當前讀：讀取的是最新的資料，需要加鎖。

問題：MVCC不是有類似生成快照的機制嗎，為什麼不能解決幻讀？

我們設計一個實際案例：現在假設事務A的版本號為200：

select * from user,
-- 其他操作
update user set level=1 where age>0,
select * from user

在事務A執行第一次select的語句時，假設查詢出了三個使用者。然後在事務A執行中間的其他操作時，事務B插入了一條新的使用者資料，因為事務B的版本號為300，所以假設此時事務A查詢，因為該行資料建立的版本號大於自己的版本號，所以不會被查詢出。

但是由於此時事務A剛好執行了下一條更新語句，而且恰好新插入的那行資料滿足更新條件，它的更新版本號被修改為事務A的版本號，這導致事務A的第二次查詢操作會查詢出這條別的事務新插入的資料，這就造成了幻讀的問題。

MySQL是使用MVCC+Next Key Lock來解決幻讀問題的，關於Next-Key Lock可以看博主資料庫基礎知識一的介紹。

9.資料庫的正規化

講解資料庫的正規化之前，補充一下資料庫中的基本概念：

  • 主鍵：關係型資料庫中的一條記錄中有若干個屬性，若其中某一個屬性組(注意是組)能唯一標識一條記錄，該屬性組就可以成為一個主鍵（一張表只有一個，不允許重複，不允許為空）。
  • 外來鍵：外來鍵用於與另一張表的關聯。是能確定另一張表記錄的欄位，用於保持資料的一致性。成績表中的學號不是成績表的主鍵，但它和學生表中的學號相對應，並且學生表中的學號是學生表的主鍵，則稱成績表中的學號是學生表的外來鍵（一張表可以有多個，可以有重複的，可以是空值）。
  • 元組：可以理解為資料表的某一行屬性：可以理解為資料表的某一列，屬性名就是列的欄位。
  • 候選碼：某一屬性組能唯一標識一個元組而其子集不能，則稱該屬性組為候選碼。若有多個候選碼，選擇其中一個為主碼。
  • 主屬性：候選碼包含的屬性（一個或多個）。
  • 非主屬性：顧名思義，就是候選碼不包括的屬性。

正規化：

  • 第一正規化（1NF，Normal Form）：屬性不應該是可分的。舉例：如果將“電話”作為一個屬性（即資料表中的一列），是不符合1NF的，因為電話這個屬性可以分解為家庭電話和行動電話。如果將“行動電話”作為一個屬性，就符合1NF。

  • 第二正規化（2NF）：每個非主屬性完全依賴於主屬性集（候選鍵集）。B完全依賴於A，就是說A中的所有屬性唯一決定B，屬性少了就不能唯一決定，屬性多了則有冗餘（叫依賴不叫完全依賴）。

    舉例：（學號，課程名）這個主屬性集可以唯一決定成績，但是對於學生姓名這個屬性，（學號，課程名）這個屬性集就是冗餘的，所以學生姓名不完全依賴於（學號，課程名）這一屬性集。

    問題：那如何使其滿足2NF？

    可以通過分解來滿足 2NF：將（學號，課程名，成績）做成一張表；（學號，學生姓名）做成另一張表，避免大量的資料冗餘； 滿足1NF後，要求表中的所有列，都必須依賴於主鍵，而不能有任何一列與主鍵沒有關係，也就是說一個表只描述一件事情。

  • 第三正規化（3NF）：在 2NF 的基礎上，非主屬性不傳遞依賴於主屬性。

    傳遞依賴：如果C依賴於B，B依賴於A，那麼C傳遞依賴於A。3NF在2NF的基礎上，消除了非主屬性之間的依賴。

    比如一個表中，主屬性有（學號），非主屬性有（姓名，院系，院長名），可以看到院長名這個非主屬性依賴於院系，傳遞依賴於學號。要求：表中的每一列只與主鍵直接相關而不是間接相關，（表中的每一列只能依賴於主鍵）。

    使一個2NF變成3NF的方法同樣是分解，方法類似1NF變為2NF，這裡不再贅述。

不符合正規化會出現哪些異常？

  • 冗餘資料：某些同樣的資料多次出現（如學生姓名）。
  • 修改異常：修改了一個記錄中的資訊，另一個記錄中相同的資訊卻沒有修改。
  • 刪除異常：刪除一個資訊，那麼也會丟失其它資訊（刪除一個課程，丟失了一個學生的資訊）。
  • 插入異常：無法插入（插入一個還沒有課程資訊的學生）。

10.表連線方式

先建立兩張簡單的資料表以作後續的演示：
學生表 在這裡插入圖片描述
成績表 在這裡插入圖片描述

內連線（Inner Join）：僅將兩個表中滿足連線條件的行組合起來作為結果集

  • 自然連線：只考慮屬性相同的元組對。

示例：

select * from student natural join grade;

結果：

在這裡插入圖片描述

沒有給任何的條件，資料庫自動把兩張資料表各行有相同屬性的行（元組）連線在了一起。

  • 等值/連線：給定條件進行查詢。

示例：

select * from student,grade
where student.sno=grade.sno;

結果：
在這裡插入圖片描述

外連線（Outer Join）

  • 左連線：左邊表的所有資料都有顯示出來，右邊的表資料只顯示共同有的那部分（就比如說成績表和課程表連線，只顯示兩邊有學號相等的，如果某一邊的學號另一邊沒出現，那就不顯示），沒有對應的部分補NULL。

    示例：

    select * from student
left outer join grade
on student.sno=grade.sno;

    結果：

在這裡插入圖片描述

  • 右連線：和左連線相反。

    示例：

    select * from student
left outer join grade
on student.sno=grade.sno;

    結果：

在這裡插入圖片描述

  • 全外連線（Full Outer Join）：查詢出左表和右表所有資料，但是去除兩表的重複資料。

    示例：

    原本SQL語句只應該需要類似：

    select * from student
full outer join grade 
on student.sno=grade.sno;

    但因為MySQL不支援這樣的全外連線，所以我們使用UNION來達到全外連線的效果：

    select * from student
left join grade on student.sno=grade.sno
union
select * from student
right join grade on student.sno=grade.sno;

    結果：
    在這裡插入圖片描述

交叉連線（Cross Join）：返回兩表的笛卡爾積（對於所含資料分別為m、n的表，返回m*n的結果）。

示例：

select * from student,grade;

結果：

相關文章