高效能MySQL筆記：第1章 MySQL架構

標籤：

MySQL 最重要、最與眾不同的特性是他的儲存引擎架構，這種架構的設計將查詢處理（Query Precessing）及其系統任務（Server Task）和資料的儲存/提取相分離。  1.1 MySQL 邏輯架構  基礎服務層 第一層構架 ：包含串連處理、授權認證、安全等基礎服務功能；  核心服務層 第二層構架 ：包含查詢解析、分析、最佳化（包括重寫查詢、決定表的讀取順序、選擇合適的索引等）、緩衝以及內建函數，所有跨儲存引擎的功能也在這一層實現：預存程序、觸發器、視圖等；  儲存引擎層 第三層構架 ：響應上層伺服器請求，負責資料的儲存和提取；   1.2 並發控制  讀寫鎖 MySQL通過由兩種類型的鎖組成的鎖系統來解決並發控制問題。 這兩類鎖被稱為共用鎖定（shared lock）和獨佔鎖定（exclusive lock）；  鎖粒度  MySQL的鎖粒度包括表鎖和行級鎖。鎖策略就是在鎖的開銷和資料的安全性（並發處理的支援性）之間尋求平衡，這種平衡當然也會影響到效能；  表鎖（table lock）  鎖定整張表（MyISAM型表 或 進行 ALTER TABLE 操作）。  行級鎖（row lock）  行級鎖可最大程度地支援並發處理（同時也帶來了最大的鎖開銷），行級鎖只在儲存引擎層實現，而MySQL伺服器層沒有實現，伺服器層完全不瞭解儲存引擎中的鎖實現。  1.3 事務 事務就是一組原子性的SQL查詢，或者說是一個獨立的工作單元。 事務內的語句，要麼全部執行成功，要不全部執行失敗。  ACID 特性  原子性（atomicity） 一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗復原，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性。  一致性（consistency） 資料庫總是從一個一致性的狀態轉換到另外一個一致性的狀態，事務在提交前，在事務內所做的任何修改都不會儲存到資料庫中。  隔離性（isolation） 一個事務所做的修改在最終提交前，對其他事務時不可見的。  持久性（durability） 事務一旦提交，則其所做的任何修改都會永久儲存在資料庫中。  隔離等級（ TRANSACTION ISOLATION LEVEL ） 在SQL標準中定義了四種隔離等級，每一種層級都規定了一個事務中所做的修改，哪些在事務內和事務間可見的，哪些是不可見的。較低層級的隔離通常可以執行更高的並發，系統的開銷也更低。  未提交讀（READ UNCOMMITTED）  事務中的修改，即使沒有提交，對其他事務也都是可見的； 存在髒讀（Dirty Read）問題；  提交讀（READ COMMITTED）  一個事務從開始直到提交之前，所做的任務修改對其他事務都是不可見的；  可重複讀 （REPEATABLE READ）  MySQL預設的隔離等級； 該層級保證了在同一個事物中多次讀取同樣記錄的結果是一致的； 但這會可能會出現幻讀（Phantom Read）問題，幻讀是指當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍插入入了新的紀錄，當之前的事務再次讀取該範圍記錄時，會產生幻行（Phantom Row）； InnoDB通過多版本並發控制（MVCC，Multiversion Concurrency Control）來解決幻讀問題；  可序列化（SERIALIZABLE）  最高的隔離等級，它通過強制事務串列執行，避免出現幻讀問題； SERIALIZABLE 會在讀取的每一行資料上加鎖（即：所有無格式 SELECT 語句被 隱式轉換成 SELECT ... LOCK IN SHARE MODE），可能會導致逾時和鎖爭用的問題；  四種隔離等級的比較 

隔離等級	髒讀可能性	不可重複可能性	幻讀可能性	加鎖讀
READ UNCOMMITTED	YES	YES	YES	NO
READ COMMITTED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

  死結 死結是指兩個或者多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈的現象。當多個事務試圖以不同的順序鎖定資源時，就可能會產生死結。鎖哥事務同時鎖定同一個資源時，也會產生死結。 InnoDB 處理死結的方法是將持有最少行級獨佔鎖定的事務進行復原。  交易記錄 交易記錄可以協助提高事務的效率。使用交易記錄，儲存引擎在修改表的資料時只需要修改其記憶體拷貝（buffer poor，即讀取到記憶體中的資料區塊），再把該修改行為記錄到持久（非同步重新整理）在硬碟上交易記錄（redo log記錄檔）中，而不用每次都將修改的資料本身持久到磁碟。 交易記錄採用的是追加的方式，因此寫日誌的操作是磁碟上一小塊地區內的順序I/O，而不像隨機I/O需要在磁碟的多個地方移動磁頭，所以採用交易記錄的方式相對來說要快得多。 交易記錄持久之後，記憶體中被修改的資料在後台可以慢慢地刷回到磁碟。目前大多數儲存引擎都是這樣實現的，我們通常稱之為預寫式日誌（Write-Ahead Logging），修改資料需要寫兩次磁碟。 如果資料的修改已經記錄到交易記錄並持久化，但資料本身還沒有寫回磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動回復這部分修改的資料。  MySQL 中的事務 MySQL 預設採用自動認可（autocommit）模式。如果不是顯式地開始一個事務（begin or start transaction），則每個查詢都被當作一個事務執行提交操作，在當前串連（session，全域則為global）中，可以通過設定autocommit變數來啟動或禁用自動認可模式。 show variables like ‘%autocommit%‘; set autocommit = 0; 非事務型表，沒有commit或者rollback的概念，對其記錄的變更無法撤銷。 alter table 等資料定義語言 (Data Definition Language)（DDL）或lock tables 在執行之前會強制執行 commit 提交當前的活動事務。 MySQL 通過執行 set transaction isolation level repeatable read 命令來設定隔離等級。  隱式和顯式鎖定 InnoDB 採用的是兩階段鎖定協議（two-phase locking protocol）。在事務執行過程中，隨時都可執行鎖定。鎖只有在執行 commit 或 rollback 時才會釋放，悲情所有的鎖是在同一時刻被釋放。InnoDB會根據隔離等級在需要時自動加鎖。此為隱式鎖定；InnoDB 也支援通過特定語句進行顯式鎖定：select ... lock in share mode (shared lock)select ... for update (exclusive lock)另外 MySQL在伺服器層也支援lock tables 和 unlock tables 語句，這和儲存引擎無關。   1.4 多版本並發控制（MVCC）  實現方式  MySQL 的事務型儲存引擎實現的不是簡單的行級鎖。基於提升並發效能的考慮，加入了（MVCC，Multiversion Concurrency Control，多版本並發控制）的邏輯。 MVCC 可被理解行級鎖的變種，在很多情況下避免了加鎖操作帶來的開銷。 MVCC是通過儲存資料在某個時間的快照來實現的。不管需要執行多長時間，每個事務看到的資料都是一致的。 InnoDB 的MVCC 是通過每行記錄後面儲存兩個隱藏的列來實現的。這兩個列，一個儲存了行的建立時間，一個儲存行的到期時間（或刪除時間）。當然儲存的並不是實際的時間值，而是系統版本號碼（sestem version number），每開始一個新的事務，系統版本號碼都會自動遞增。事務開始時刻的系統版本號碼作為事務的的版本號碼，用來和查詢到的每行記錄的版本號碼進行比較。  select  InnoDB 會根據以下兩個條件檢查每行記錄： 

• InnoDB 只尋找版本遭遇當前事務版本的資料行（也就是，行的系統版本號碼小於或等於事務的系統版本號碼），這樣可以確保事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。

 

• 行的刪除版本要麼未定義，要麼大於當前事務版本號碼。這可以確保事務讀取到的行，在事務開始之前未被刪除。

  insert InnoDB 為新插入的每一行儲存當前系統版本號碼作為行版本號碼。  delete  InnoDB 為刪除的每一行儲存當前系統版本號碼作為行刪除標識。  update InnoDB 為插入一行記錄，儲存當前系統版本號碼作為行版本號碼，同時儲存當前系統版本號碼到原來的行作為行刪除標識。  1.5 MySQL 的儲存引擎   InnoDB InnoDB 的資料存放區在資料表空間（tablspace）中。資料表空間是由 InnoDB 管理的一個黑盒子，由一系列的資料檔案組成。 InnoDB 採用 MVCC 來支援高並發，並且實現了四個標準的隔離等級。其預設層級是 REPEATABLE  READ（可重複讀），並且通過間隙鎖（next-key locking）策略防止幻讀的出現。間隙鎖使得 InnoDB 不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。 InnoDB 表是基於聚簇索引表建立的。聚簇索引對主鍵查詢有很高的效能。 InnoDB 表的二級索引（secondary index，非主鍵索引）中必須包含主鍵列。 InnoDB 支援熱備份：MySQL Exterprise Backup XtraBackup。  轉換表的引擎 ALTER TABLE table_name ENGINE = INNODB;      

高效能MySQL筆記：第1章 MySQL架構