跳至主要内容

數據庫設計原則

ACID​

  • 原子性
    • 事務是最小單元，不可分割，當前事務的操作要麼全部成功，要麼全部失敗，由 undo log 來日誌保證
  • 一致性
    • 要求所有 DML 語句操作的時候，必須保證同時成功或同時失敗
  • 隔離性
    • 事務 A 和 事務 B 之間具有隔離，併發執行時互補影響
  • 持久性
    • 事務的保證，事務終結的標誌【內存中的數據持久化至硬碟中】，由 redo 日誌來保證。

關鍵字：

  • 開啟事務 Start transaction
  • 事務結束 End transaction
  • 提交事務 Commit transaction
  • 回滾事務 Rollback transaction

和事務相關的 SQL 語句【TCL】：

  • commit【提交】
  • rollback【回滾】

開啟事務的標誌/結束事務的標誌

  • 開啟的標誌：任何一條 DML 語句執行，標誌事務的開始。
  • 結束的標誌：提交或回滾
    • 提交，成功的結束，將所有 DML 語句操作歷史記錄和底層硬碟文件中的數據做一次同步。
    • 回滾，失敗的結束，將所有 DML 語句操作歷史記錄，全部清空。

重點： 事務進行過程中，未結束前，DML 語句是不會更改底層數據庫文件的數據。只是將操作記錄記錄保存在內存中，事務結束後，並且是事務成功，才會修改底層文件中的數據。

三范式​

  • 第一范式：要有主鍵，要求每一字段原子性不可再分。
  • 第二范式：要求所有非主鍵字段完全依賴主鍵，不能產生部份依賴。
  • 所有非主鍵字段和主鍵字段之間不能產生傳遞依賴。

經典設計

  1. 一對一：
    1. 第一種方案：分兩張表儲存，共享主鍵。
    2. 第二種方案：分兩張表儲存，外鍵唯一。
  2. 一對多：分兩張表儲存，在多的一方添加外鍵，外鍵字段引用的一方的主鍵字段。
  3. 多對多：分三張表儲存，學生表儲存學社訊息，課程表儲存課程訊息，學生選課表中儲存學生和課程的關係訊息。

實際開發是怎樣？

盡量遵守三范式，但還是根據實際情況進行取捨，有時候拿冗余換速度，最終滿足客戶目的。

ESR​

事務​

  • 一個“最小”的“不可分割”的工作單元。
  • 通常一個事務對應一個完整業務。【銀行轉帳】
  • 一個完整事務需要批量 DML 語句（insert/update/delete）共同聯合完成。
  • 事務只和 DML 語句有關。
  • 批量 DML 語句數量具體有多少和業務邏輯有關。

例如銀行轉帳業務 t_act 帳戶表

actnobalance
act-100600000
act-200200000

執行轉帳業務（100000）：

# 以下兩條 DML 語句要求必須同時成功或同時失敗，最小單元不可分割
# 當第一條 DML 語句成功，並不能將底層數據庫第一個帳戶的數據修改，只是將操作記錄下來在內存中完成
# 第二條 DML 語句成功之後，和底層數據庫文件的數據完成同步
# 若第二條 DML 語句失敗，清空所有相關的歷史操作記錄
update t_act set balance = 500000 where actno = "act-100";
update t_act set balance = 300000 where actno = "act-200";

事務隔離級別​

InnoDB引擎中定義四種級別提供使用，級別越高隔離性越好，但性能越低，隔離級別由各種鎖和MVCC機制實現。

  • 事務A和事務B之間具有一定隔離性
  • 4個隔離級別：
    • read uncommitted 讀未提交:
      • 事務A和事務B，事務A "未提交" 數據，事務B可以讀取，導致 "髒數據"。
    • read committed 讀已提交：
      • 事務A和事務B，事務A "提交" 數據，事務B才能讀取，導致 "不可重複讀"。Oracle 預設隔離級別
    • repeatable read 可重複讀
      • 事務A和事務B，事務A "提交" 數據，事務B不能讀取，達到 "可重複讀，避免髒讀"，導致 "幻象讀"（讀取緩存，事務結束緩存更新產生新的數據）。MYSQL 預設隔離級別
    • serializable 串行化：
      • 事務A和事務B，事務A在操作數據庫表中的數據時，事務B只能排隊等待，不再併發，此級別很少使用，吞吐量低用戶體驗差，達到 "避免幻象讀"，每次讀取都是數據庫的真實數據。
  • 事務隔離級別，決定事務之間可見的級別。
  • 客戶端併發訪問同一張表時，可能出現一致性問題：
    • 髒讀取（Dirty Read）：一個事務開始讀取某數據，但是另一個事務已經更新了此數據但還未提交，產生髒讀取。
    • 不可重複讀（Non-repeatable Read）：同一個事務中，同一個讀操作對同一個數據的前後兩次讀取產生不同結果，這就是不可重複讀。
    • 幻想讀（Phantom Read）：同一個事務中，以前沒有的數據，由於其他事務提交而出現的數據。快照讀。

隔離級別與一致性​

隔離級別髒讀取不可重複讀幻想讀
讀未提交可能可能可能
讀已提交不可能可能可能
可重複讀不可能不可能對InnoDB不可能
串行化不可能不可能不可能