• 1 mysql邏輯架構
  • 1.1 連接管理與安全性
  • 1.2 優化與執行
• 2 并發控制
  • 2.1 鎖粒度
  • 2.2 表鎖
  • 2.3 行級鎖
• 3 事務
  • 3.1 資料庫事務四特性
  • 3.2 隔離級別
  • 3.3 死鎖
  • 3.4 事務日志
  • 3.5 MySql中的事務
• 4 多版本并發控制MVCC
• 5 MySql常用存盤引擎
  • 5.1 InnoDB
  • 5.2 MyISAM
• 6 InnoDB如何使用MVCC解決幻讀

1 mysql邏輯架構

mysql邏輯架構圖：

Mysql服務器、存盤引擎 是兩個獨立的組件，彼此通過api互動

1. 第一層：連接處理、授權認證、安全管理
2. 第二層：核心服務功能
   1. 查詢決議、分析、優化、快取以及所有的內置函式(日期、時間、數學、加密函式等）
   2. 跨存盤引擎的功能：存盤程序、觸發器、視圖等，
3. 第三層：存盤引擎，負責MySQL中資料的存盤和提取，
   1. 服務器通過API與存盤引擎進行通信，
   2. 存盤引擎不會去決議SQL，不同存盤引擎之間也不會相互通信，僅回應上層服務器的請求，

1.1 連接管理與安全性

1. 服務器為每個客戶端單獨開辟一個執行緒（或執行緒池（少量執行緒）以應對大量連接），處理來自該客戶端的所有連接，
2. 認證方式：
   1. 用戶名+密碼+主機資訊(ip 埠)
   2. 安全套接字SSL （后續章節詳解 todo）
3. 連接權限校驗

1.2 優化與執行

優化器作業內容：

1. 先決議查詢，并創建決議樹，再優化，如：重寫查詢、決定表的讀取順序、選擇合適的索引等，使用【優化器解釋explain】來查看其優化內容
2. 優化器并不關心表使用什么存盤引擎，但存盤引擎對于優化查詢有影響：優化器會請求存盤引擎提供容量或某個具體操作的開銷資訊，以及表資料的統計資訊等，
3. 對于SELECT陳述句，在決議查詢之前，服務器會先檢查查詢快取，如果能夠在其中找到對應的查詢，服務器就不必再執行查詢決議、優化和執行的整個程序，而是直接回傳查詢快取中的結果集，

2 并發控制

Mysql有兩個層面的并發控制：服務器層和存盤引擎層

2.1 鎖粒度

1. 每個存盤引擎都可以實作自己的鎖策略和鎖粒度，
2. 兩種基本鎖粒度：表鎖、行級鎖

2.2 表鎖

1. Mysql最基本的鎖策略，也是開銷最小的策略，它會鎖定整張表，對表進行寫操作前，需先獲得寫鎖，讀寫互斥，讀讀不互斥
2. 寫鎖比讀鎖有更高的優先級，一個寫鎖請求可能會被插入到讀鎖佇列的前面(反之讀鎖則不能插入到寫鎖的前面)，
3. 盡管存盤引擎可以管理自己的鎖，MySQL服務器還是會使用各種有效的表鎖來實作不同
   的目的，例如，Mysql服務器會為諸如ALTER TABLE之類的陳述句使用表鎖，而忽略存盤引擎的鎖機制，

2.3 行級鎖

1. 只在存盤引擎層實作，而MySQL服務器層沒有實作，
2. 并發性好，但鎖開銷大

3 事務

3.1 資料庫事務四特性

事務是指一組邏輯操作，它們要么一起成功，要么一起失敗，

ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）

1. 原子性：事務是一個不可分割的最小單元，事務中的操作要么都發生，要么都不發生，
2. 一致性：如果事務執行之前資料庫是一個完整的狀態，那么事務結束后（無論事務是否執行成功）資料庫仍然是一個完整的狀態，(DB中所有的資料都符合DB的約束規范）
3. 隔離性：多個用戶并發訪問資料庫時，一個用戶的事務不能被其他用戶的事務所干擾，多個并發事務之間資料要相互隔離，
4. 持久性：事務一旦被提交，它對資料庫的影響是永久性的

3.2 隔離級別

SQL標準定義了四種隔離級別：

• 臟讀：一個事務讀取了另外一個未提交事務資料

• 不可重復讀取：在當前事務中，讀取了另一事務提交的更新或者洗掉的資料，例外情形：同一事務先后執行相同的select陳述句時可能看到不一樣的結果

• 幻讀：當事務A在讀取某個范圍內的記錄時,事務B又在該范圍內插入了新的記錄，當事務A再次讀取該范圍的記錄時，會產生幻行（讀取到新插入的記錄）；多版本控制(MVVC)解決幻讀

總結：

3.3 死鎖

資料庫系統實作了多種鎖檢測和死鎖超時機制：

1. 方式一：當檢測到死鎖的回圈依賴，立即回傳一個錯誤，
2. 方式二：當查詢的時間達到鎖等待超時的設定值后，放棄鎖請求
3. InnoDB處理死鎖的方法：將持有最少行級排他鎖的事務進行回滾

3.4 事務日志

事務日志的目的是提交事務效率，

1. 首先資料庫的資料可以被快取到記憶體
2. 修改表資料時，會先修改記憶體中的資料，再把該修改行為以追加的方式記錄到事務日志
3. 事務日志保存到磁盤后，后臺執行緒稍后把修改的資料重繪到磁盤
4. 它是一種預寫日志策略（write ahead logger），修改資料需要寫兩次磁盤

3.5 MySql中的事務

一、自動提交

默認采用自動提交模式（auto commit）

1. 自動提交模式：如果不是顯式地開啟一個事務，那么每一條sql指令都會當做一個事務來執行，
2. 非自動提交模式：所有sql指令都在一個事務中，直到顯式地執行COMMIT提交或者ROLLBACK回滾，該事務結束，同時又開始了新的事務，

二、在事務中混合使用存盤引擎

MySQL服務器層不管理事務，事務是由下層的存盤引擎實作的，所以在同一個事務中,使用多種存盤引擎是不可靠的，

三、隱式和顯式鎖定
todo

4 多版本并發控制MVCC

目標：減少不必要的鎖操作

實作原理：

一、兩個前提：

1. 每個事務開始之前，會以遞增方式生成一個系統版本號，以下簡稱：VNum
2. 在每一行記錄增加兩個隱藏列：一個保存行的創建時間、一個保存行的洗掉時間，這兩個時間概念用系統版本號來代替

二、對于不同的sql指令，InnoDB執行不同的操作：

INSERT： 為新插入的每一行保存VNum作為行版本號，
DELETE：為洗掉的每一行保存VNum作為行洗掉標識，
UPDATE：為插入一行新記錄，保存VNum作為行版本號，同時保存VNum到原來的行作為行洗掉標識，
SELECT：
InnoDB會根據以下兩個條件檢查每行記錄:

1. 只查找版本早于當前事務版本的資料行(也就是，行的系統版本號小于或等于事務的系統版本號)，這樣可以確保事務讀取的行，要么是在事務開始前已經存在的，要么是事務自身插入或者修改過的，
2. 行的洗掉版本要么未定義，要么大于當前事務版本號，這可以確保事務讀取到的行，在事務開始之前未被洗掉，

只有符合上述兩個條件的記錄，才能回傳作為查詢結果，

溫馨提示：資料庫的洗掉、更新，跟淺義上的理解不一樣：1、delete并非把資料從磁盤洗掉；update它是先插入后洗掉，在后續學習筆記會對此進行更深入講解，

5 MySql常用存盤引擎

5.1 InnoDB

• InnoDB采用MVCC來支持高并發，實作了四個標準的隔離級別，默認級別：REPEATABLE READ (可重復讀)
• 通過間隙鎖(next-key locking)策略防止幻讀的出現 （后續章節詳解 todo)
• InnoDB的表是基于聚族索引建立 （后續章節詳解 todo)
• 存盤格式是平臺獨立，可以將資料和索引檔案跨平臺復制，
• 其它優化：可預測性預讀、自適應hash索引、插入緩沖區等 （后續章節詳解 todo)
• 支持真正熱備份 （后續章節詳解 todo)

5.2 MyISAM

不支持：事務、行級鎖、奔潰后安全恢復
適用于：表比較小、讀多寫少的場景

特性：

一、加鎖與并發
對整張表加鎖，而不是針對行，讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排他鎖，在表有讀取查詢的同時，也可以往表中插入新的記錄(并發插人)
二、奔潰修復
崩潰后無法安全恢復
三、索引特性
支持全文索引：對于MyISAM表，即使是BL0B和TEXT等長欄位，也可以基于其前500個字符創建索引

6 InnoDB如何使用MVCC解決幻讀

后續章節詳解 todo
可參照：InnoDB事務模型和鎖定

標籤：其他

上一篇：國貿股份 x 袋鼠云：推進全鏈業務深度數字化，為產業綜合服務插上數字化翅膀

下一篇：網易數帆資料治理體系、工具、流程、制度與管理概述