1 mysql邏輯架構 mysql邏輯架構圖： Mysql伺服器、存儲引擎 是兩個獨立的組件，彼此通過api交互 第一層：連接處理、授權認證、安全管理 第二層：核心服務功能 查詢解析、分析、優化、緩存以及所有的內置函數(日期、時間、數學、加密函數等） 跨存儲引擎的功能：存儲過程、觸發器、視圖等。 第 ...
目錄
1 mysql邏輯架構
mysql邏輯架構圖：
Mysql伺服器、存儲引擎 是兩個獨立的組件，彼此通過api交互
- 第一層：連接處理、授權認證、安全管理
- 第二層：核心服務功能
- 查詢解析、分析、優化、緩存以及所有的內置函數(日期、時間、數學、加密函數等）
- 跨存儲引擎的功能：存儲過程、觸發器、視圖等。
- 第三層：存儲引擎，負責MySQL中數據的存儲和提取。
- 伺服器通過API與存儲引擎進行通信。
- 存儲引擎不會去解析SQL，不同存儲引擎之間也不會相互通信，僅響應上層伺服器的請求。
1.1 連接管理與安全性
- 伺服器為每個客戶端單獨開闢一個線程（或線程池（少量線程）以應對大量連接），處理來自該客戶端的所有連接。
- 認證方式：
- 用戶名+密碼+主機信息(ip 埠)
- 安全套接字SSL （後續章節詳解 todo）
- 連接許可權校驗
1.2 優化與執行
優化器工作內容：
- 先解析查詢，並創建解析樹，再優化，如：重寫查詢、決定表的讀取順序、選擇合適的索引等。使用【優化器解釋explain】來查看其優化內容
- 優化器並不關心表使用什麼存儲引擎，但存儲引擎對於優化查詢有影響：優化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數據的統計信息等。
- 對於SELECT語句，在解析查詢之前，伺服器會先檢查查詢緩存，如果能夠在其中找到對應的查詢，伺服器就不必再執行查詢解析、優化和執行的整個過程，而是直接返回查詢緩存中的結果集。
2 併發控制
Mysql有兩個層面的併發控制：伺服器層和存儲引擎層
2.1 鎖粒度
- 每個存儲引擎都可以實現自己的鎖策略和鎖粒度。
- 兩種基本鎖粒度：表鎖、行級鎖
2.2 表鎖
- Mysql最基本的鎖策略，也是開銷最小的策略。它會鎖定整張表。對錶進行寫操作前，需先獲得寫鎖。讀寫互斥，讀讀不互斥
- 寫鎖比讀鎖有更高的優先順序，一個寫鎖請求可能會被插入到讀鎖隊列的前面(反之讀鎖則不能插入到寫鎖的前面)。
- 儘管存儲引擎可以管理自己的鎖，MySQL伺服器還是會使用各種有效的表鎖來實現不同
的目的。例如，Mysql伺服器會為諸如ALTER TABLE之類的語句使用表鎖，而忽略存儲引擎的鎖機制。
2.3 行級鎖
- 只在存儲引擎層實現，而MySQL伺服器層沒有實現。
- 併發性好，但鎖開銷大
3 事務
3.1 資料庫事務四特性
事務是指一組邏輯操作，它們要麼一起成功，要麼一起失敗。
ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）
- 原子性：事務是一個不可分割的最小單元，事務中的操作要麼都發生，要麼都不發生。
- 一致性：如果事務執行之前資料庫是一個完整的狀態，那麼事務結束後（無論事務是否執行成功）資料庫仍然是一個完整的狀態。(DB中所有的數據都符合DB的約束規範）
- 隔離性：多個用戶併發訪問資料庫時，一個用戶的事務不能被其他用戶的事務所干擾，多個併發事務之間數據要相互隔離。
- 持久性：事務一旦被提交，它對資料庫的影響是永久性的
3.2 隔離級別
SQL標準定義了四種隔離級別：
| 隔離級別 | 說明 | 問題 |
|---|---|---|
| 讀未提交（Read Uncommitted） | 事務A可讀取事務B未提交的數據 | 引發 臟讀 |
| 讀已提交（Read Committed） | 事務A只能讀取其它已提交事務的數據； | 引發 不可重覆讀 |
| 可重覆讀（Repeatable Read） | 保證同一事務中多次讀取同樣的記錄的結果是一致的；Mysql預設事務隔離級別 | 解決了臟讀；但引發 幻讀 |
| 可串列化（Serializable） | 強制事務串列執行，在讀的每一行數據行上加鎖 | 大量的超時和鎖競爭 |
-
臟讀：一個事務讀取了另外一個未提交事務數據
-
不可重覆讀取：在當前事務中，讀取了另一事務提交的更新或者刪除的數據。異常情形：同一事務先後執行相同的select語句時可能看到不一樣的結果
-
幻讀：當事務A在讀取某個範圍內的記錄時,事務B又在該範圍內插入了新的記錄，當事務A再次讀取該範圍的記錄時，會產生幻行（讀取到新插入的記錄）；多版本控制(MVVC)解決幻讀
總結：
3.3 死鎖
資料庫系統實現了多種鎖檢測和死鎖超時機制：
- 方式一：當檢測到死鎖的迴圈依賴，立即返回一個錯誤。
- 方式二：當查詢的時間達到鎖等待超時的設定值後，放棄鎖請求
- InnoDB處理死鎖的方法：將持有最少行級排他鎖的事務進行回滾
3.4 事務日誌
事務日誌的目的是提交事務效率。
- 首先資料庫的數據可以被緩存到記憶體
- 修改表數據時，會先修改記憶體中的數據，再把該修改行為以追加的方式記錄到事務日誌
- 事務日誌保存到磁碟後，後臺線程稍後把修改的數據刷新到磁碟
- 它是一種預寫日誌策略（write ahead logger），修改數據需要寫兩次磁碟
3.5 MySql中的事務
一、自動提交
預設採用自動提交模式（auto commit）
- 自動提交模式：如果不是顯式地開啟一個事務，那麼每一條sql指令都會當做一個事務來執行。
- 非自動提交模式：所有sql指令都在一個事務中，直到顯式地執行COMMIT提交或者ROLLBACK回滾，該事務結束，同時又開始了新的事務。
二、在事務中混合使用存儲引擎
MySQL伺服器層不管理事務，事務是由下層的存儲引擎實現的。所以在同一個事務中,使用多種存儲引擎是不可靠的。
三、隱式和顯式鎖定
todo
4 多版本併發控制MVCC
目標：減少不必要的鎖操作
實現原理：
一、兩個前提：
- 每個事務開始之前，會以遞增方式生成一個系統版本號。以下簡稱：VNum
- 在每一行記錄增加兩個隱藏列：一個保存行的創建時間、一個保存行的刪除時間。這兩個時間概念用系統版本號來代替
二、對於不同的sql指令，InnoDB執行不同的操作：
INSERT： 為新插入的每一行保存VNum作為行版本號。
DELETE：為刪除的每一行保存VNum作為行刪除標識。
UPDATE：為插入一行新記錄，保存VNum作為行版本號，同時保存VNum到原來的行作為行刪除標識。
SELECT：
InnoDB會根據以下兩個條件檢查每行記錄:
- 只查找版本早於當前事務版本的數據行(也就是，行的系統版本號小於或等於事務的系統版本號)，這樣可以確保事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。
- 行的刪除版本要麼未定義，要麼大於當前事務版本號。這可以確保事務讀取到的行，在事務開始之前未被刪除。
只有符合上述兩個條件的記錄，才能返回作為查詢結果。
溫馨提示：資料庫的刪除、更新，跟淺義上的理解不一樣：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事務模型和鎖定
