Mysql架構
- MySQL邏輯架構
  - 連接管理與安全性
  - 優化與執行
- 并發控制
  - 讀寫鎖
  - 鎖粒度
- 事務
  - 隔離級別
  - 死鎖
  - 事務日志
  - MySQL中的事務
- 多版本并發控制

Mysql架構

MySQL邏輯架構

如果能在頭腦中構建出一幅MySQL各組件之間如何協同作業的架構圖，就會有助于深入理解MySQL服務器，

最上層的服務并不是MySQL所獨有的，大多數基于網路的客戶端/服務器的工具或者服務都有類似的架構，比如連接處理、授權認證、安全等等，

第二層架構是MySQL比較有意思的部分，大多數MySQL的核心服務功能都在這一層，包括查詢決議、分析、優化、快取以及所有的內置函式（例如，日期、時間、數學和加密函式），所有跨存盤引擎的功能都在這一層實作：存盤程序、觸發器、視圖等，

第三層包含了存盤引擎，存盤引擎負責MySQL中資料的存盤和提取，和GNU/Linux下的各種檔案系統一樣，每個存盤引擎都有它的優勢和劣勢，服務器通過API與存盤引擎進行通信，這些介面屏蔽了不同存盤引擎之間的差異，使得這些差異對上層的查詢程序透明，存盤引擎API包含幾十個底層函式，用于執行諸如“開始一個事務”或者“根據主鍵提取一行記錄”等操作，但存盤引擎不會去決議SQL，不同存盤引擎之間也不會相互通信，而只是簡單地回應上層服務器的請求，

連接管理與安全性

每個客戶端連接都會在服務器行程中擁有一個執行緒，這個連接的查詢只會在這個單獨的執行緒中執行，該執行緒只能輪流在某個CPU核心或者CPU中運行，服務器會負責快取執行緒，因此不需要為每一個新建的連接創建或者銷毀執行緒.當客戶端（應用）連接到MySQL服務器時，服務器需要對其進行認證，認證基于用戶名、原始主機資訊和密碼，如果使用了安全套接字（SSL）的方式連接，還可以使用X.509證書認證，一旦客戶端連接成功，服務器會繼續驗證該客戶端是否具有執行某個特定查詢的權限

優化與執行

MySQL會決議查詢，并創建內部資料結構（決議樹），然后對其進行各種優化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等，用戶可以通過特殊的關鍵字提示（hint）優化器，影響它的決策程序，也可以請求優化器解釋（explain）優化程序的各個因素，使用戶可以知道服務器是如何進行優化決策的，并提供一個參考基準，便于用戶重構查詢和schema、修改相關配置，使應用盡可能高效運行，

優化器并不關心表使用的是什么存盤引擎，但存盤引擎對于優化查詢是有影響的，優化器會請求存盤引擎提供容量或某個具體操作的開銷資訊，以及表資料的統計資訊等，例如，某些存盤引擎的某種索引，可能對一些特定的查詢有優化，

對于SELECT陳述句，在決議查詢之前，服務器會先檢查查詢快取（Query Cache），如果能夠在其中找到對應的查詢，服務器就不必再執行查詢決議、優化和執行的整個程序，而是直接回傳查詢快取中的結果集，

并發控制

無論何時，只要有多個查詢需要在同一時刻修改資料，都會產生并發控制的問題，

本章的目的是討論MySQL在兩個層面的并發控制：服務器層與存盤引擎層，

讀寫鎖

在處理并發讀或者寫時，可以通過實作一個由兩種型別的鎖組成的鎖系統來解決問題，這兩種型別的鎖通常被稱為共享鎖（shared lock）和排他鎖（exclusive lock），也叫讀鎖（read lock）和寫鎖（write lock），

讀鎖是共享的，或者說是相互不阻塞的，多個客戶在同一時刻可以同時讀取同一個資源，而互不干擾，寫鎖則是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，這是出于安全策略的考慮，只有這樣，才能確保在給定的時間里，只有一個用戶能執行寫入，并防止其他用戶讀取正在寫入的同一資源，

在實際的資料庫系統中，每時每刻都在發生鎖定，當某個用戶在修改某一部分資料時，MySQL會通過鎖定防止其他用戶讀取同一資料，大多數時候，MySQL鎖的內部管理都是透明的，

鎖粒度

一種提高共享資源并發性的方式就是讓鎖定物件更有選擇性，盡量只鎖定需要修改的部分資料，而不是所有的資源，更理想的方式是，只對會修改的資料片進行精確的鎖定，任何時候，在給定的資源上，鎖定的資料量越少，則系統的并發程度越高，只要相互之間不發生沖突即可，

問題是加鎖也需要消耗資源，鎖的各種操作，包括獲得鎖、檢查鎖是否已經解除、釋放鎖等，都會增加系統的開銷，如果系統花費大量的時間來管理鎖，而不是存取資料，那么系統的性能可能會因此受到影響，

所謂的鎖策略，就是在鎖的開銷和資料的安全性之間尋求平衡，這種平衡當然也會影響到性能，大多數商業資料庫系統沒有提供更多的選擇，一般都是在表上施加行級鎖（row-level lock），并以各種復雜的方式來實作，以便在鎖比較多的情況下盡可能地提供更好的性能，

而MySQL則提供了多種選擇，每種MySQL存盤引擎都可以實作自己的鎖策略和鎖粒度，在存盤引擎的設計中，鎖管理是個非常重要的決定，將鎖粒度固定在某個級別，可以為某些特定的應用場景提供更好的性能，但同時卻會失去對另外一些應用場景的良好支持，好在MySQL支持多個存盤引擎的架構，所以不需要單一的通用解決方案，下面將介紹兩種最重要的鎖策略，

表鎖（table lock）

表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略，它會鎖定整張表，一個用戶在對表進行寫操作（插入、洗掉、更新等）前，需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作，只有沒有寫鎖時，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的，

在特定的場景中，表鎖也可能有良好的性能，例如，READ LOCAL表鎖支持某些型別的并發寫操作，另外，寫鎖也比讀鎖有更高的優先級，因此一個寫鎖請求可能會被插入到讀鎖佇列的前面（寫鎖可以插入到鎖佇列中讀鎖的前面，反之讀鎖則不能插入到寫鎖的前面），

盡管存盤引擎可以管理自己的鎖，MySQL本身還是會使用各種有效的表鎖來實作不同的目的，例如，服務器會為諸如ALTER TABLE之類的陳述句使用表鎖，而忽略存盤引擎的鎖機制，

行級鎖（row lock）

行級鎖可以最大程度地支持并發處理（同時也帶來了最大的鎖開銷），行級鎖只在存盤引擎層實作，而MySQL服務器層沒有實作，服務器層完全不了解存盤引擎中的鎖實作，

事務

事務就是一組原子性的SQL查詢，或者說一個獨立的作業單元，如果資料庫引擎能夠成功地對資料庫應用該組查詢的全部陳述句，那么就執行該組查詢，如果其中有任何一條陳述句因為崩潰或其他原因無法執行，那么所有的陳述句都不會執行，也就是說，事務內的陳述句，要么全部執行成功，要么全部執行失敗，

可以用START TRANSACTION陳述句開始一個事務，然后要么使用COMMIT提交事務將修改的資料持久保留，要么使用ROLLBACK撤銷所有的修改，

除非系統通過嚴格的ACID測驗，否則空談事務的概念是不夠的，ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）和持久性（durability），一個運行良好的事務處理系統，必須具備這些標準特征，

原子性（atomicity）

一個事務必須被視為一個不可分割的最小作業單元，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性，

一致性（consistency）

資料庫總是從一個一致性的狀態轉換到另外一個一致性的狀態，在前面的例子中，一致性確保了，即使在執行第三、四條陳述句之間時系統崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會保存到資料庫中，

隔離性（isolation)

通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的，

持久性（durability）

一旦事務提交，則其所做的修改就會永久保存到資料庫中，此時即使系統崩潰，修改的資料也不會丟失，持久性是個有點模糊的概念，因為實際上持久性也分很多不同的級別，有些持久性策略能夠提供非常強的安全保障，而有些則未必，而且不可能有能做到100％的持久性保證的策略.

就像鎖粒度的升級會增加系統開銷一樣，這種事務處理程序中額外的安全性，也會需要資料庫系統做更多的額外作業，一個實作了ACID的資料庫，相比沒有實作ACID的資料庫，通常會需要更強的CPU處理能力、更大的記憶體和更多的磁盤空間，正如本章不斷重復的，這也正是MySQL的存盤引擎架構可以發揮優勢的地方，用戶可以根據業務是否需要事務處理，來選擇合適的存盤引擎，對于一些不需要事務的查詢類應用，選擇一個非事務型的存盤引擎，可以獲得更高的性能，即使存盤引擎不支持事務，也可以通過LOCK TABLES陳述句為應用提供一定程度的保護，這些選擇用戶都可以自主決定，

隔離級別

隔離性其實比想象的要復雜，在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些是不可見的，較低級別的隔離通常可以執行更高的并發，系統的開銷也更低，

下面簡單地介紹一下四種隔離級別，

READ UNCOMMITTED（未提交讀）:
在READ UNCOMMITTED級別，事務中的修改，即使沒有提交，對其他事務也都是可見的，事務可以讀取未提交的資料，這也被稱為臟讀（Dirty Read），這個級別會導致很多問題，從性能上來說，READ UNCOMMITTED不會比其他的級別好太多，但卻缺乏其他級別的很多好處，除非真的有非常必要的理由，在實際應用中一般很少使用，

READ COMMITTED（提交讀）:
大多數資料庫系統的默認隔離級別都是READ COMMITTED（但MySQL不是），READ COMMITTED滿足前面提到的隔離性的簡單定義：一個事務開始時，只能“看見”已經提交的事務所做的修改，換句話說，一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的，這個級別有時候也叫做不可重復讀（nonrepeatable read），因為兩次執行同樣的查詢，可能會得到不一樣的結果，

REPEATABLE READ（可重復讀）:
REPEATABLE READ解決了臟讀的問題，該級別保證了在同一個事務中多次讀取同樣記錄的結果是一致的，但是理論上，可重復讀隔離級別還是無法解決另外一個幻讀（Phantom Read）的問題，所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產生幻行（Phantom Row），InnoDB和XtraDB存盤引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control）解決了幻讀的問題，

可重復讀是MySQL的默認事務隔離級別，

SERIALIZABLE（可串行化）:
SERIALIZABLE是最高的隔離級別，它通過強制事務串行執行，避免了前面說的幻讀的問題，簡單來說，SERIALIZABLE會在讀取的每一行資料上都加鎖，所以可能導致大量的超時和鎖爭用的問題，實際應用中也很少用到這個隔離級別，只有在非常需要確保資料的一致性而且可以接受沒有并發的情況下，才考慮采用該級別，

死鎖

死鎖是指兩個或者多個事務在同一資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性回圈的現象，當多個事務試圖以不同的順序鎖定資源時，就可能會產生死鎖，多個事務同時鎖定同一個資源時，也會產生死鎖，例如，設想下面兩個事務同時處理StockPrice表：
事務1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

事務2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果湊巧，兩個事務都執行了第一條UPDATE陳述句，更新了一行資料，同時也鎖定了該行資料，接著每個事務都嘗試去執行第二條UPDATE陳述句，卻發現該行已經被對方鎖定，然后兩個事務都等待對方釋放鎖，同時又持有對方需要的鎖，則陷入死回圈，除非有外部因素介入才可能解除死鎖，

為了解決這種問題，資料庫系統實作了各種死鎖檢測和死鎖超時機制，越復雜的系統，比如InnoDB存盤引擎，越能檢測到死鎖的回圈依賴，并立即回傳一個錯誤，這種解決方式很有效，否則死鎖會導致出現非常慢的查詢，還有一種解決方式，就是當查詢的時間達到鎖等待超時的設定后放棄鎖請求，這種方式通常來說不太好，InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾（這是相對比較簡單的死鎖回滾演算法），

鎖的行為和順序是和存盤引擎相關的，以同樣的順序執行陳述句，有些存盤引擎會產生死鎖，有些則不會，死鎖的產生有雙重原因：有些是因為真正的資料沖突，這種情況通常很難避免，但有些則完全是由于存盤引擎的實作方式導致的，

死鎖發生以后，只有部分或者完全回滾其中一個事務，才能打破死鎖，對于事務型的系統，這是無法避免的，所以應用程式在設計時必須考慮如何處理死鎖，大多數情況下只需要重新執行因死鎖回滾的事務即可，

事務日志

事務日志可以幫助提高事務的效率，使用事務日志，存盤引擎在修改表的資料時只需要修改其記憶體拷貝，再把該修改行為記錄到持久在硬碟上的事務日志中，而不用每次都將修改的資料本身持久到磁盤，事務日志采用的是追加的方式，因此寫日志的操作是磁盤上一小塊區域內的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務日志的方式相對來說要快得多，事務日志持久以后，記憶體中被修改的資料在后臺可以慢慢地刷回到磁盤，目前大多數存盤引擎都是這樣實作的，我們通常稱之為預寫式日志（Write-Ahead Logging），修改資料需要寫兩次磁盤，

如果資料的修改已經記錄到事務日志并持久化，但資料本身還沒有寫回磁盤，此時系統崩潰，存盤引擎在重啟時能夠自動恢復這部分修改的資料，具體的恢復方式則視存盤引擎而定，

MySQL中的事務

MySQL提供了兩種事務型的存盤引擎：InnoDB和NDB Cluster，另外還有一些第三方存盤引擎也支持事務，比較知名的包括XtraDB和PBXT

自動提交（AUTOCOMMIT）:
MySQL默認采用自動提交（AUTOCOMMIT）模式，也就是說，如果不是顯式地開始一個事務，則每個查詢都被當作一個事務執行提交操作，在當前連接中，可以通過設定AUTOCOMMIT變數來啟用或者禁用自動提交模式：

show variables like 'AUTOCOMMIT';

1或者ON表示啟用，0或者OFF表示禁用，當AUTOCOMMIT=0時，所有的查詢都是在一個事務中，直到顯式地執行COMMIT提交或者ROLLBACK回滾，該事務結束，同時又開始了另一個新事務，修改AUTOCOMMIT對非事務型的表，比如MyISAM或者記憶體表，不會有任何影響，對這類表來說，沒有COMMIT或者ROLLBACK的概念，也可以說是相當于一直處于AUTOCOMMIT啟用的模式，

另外還有一些命令，在執行之前會強制執行COMMIT提交當前的活動事務，典型的例子，在資料定義語言（DDL）中，如果是會導致大量資料改變的操作，比如ALTER TABLE，就是如此，另外還有LOCK TABLES等其他陳述句也會導致同樣的結果，如果有需要，請檢查對應版本的官方檔案來確認所有可能導致自動提交的陳述句串列，

MySQL可以通過執行SET TRANSACTION ISOLATION LEVEL命令來設定隔離級別，新的隔離級別會在下一個事務開始的時候生效，可以在組態檔中設定整個資料庫的隔離級別，也可以只改變當前會話的隔離級別：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能夠識別所有的4個ANSI隔離級別，InnoDB引擎也支持所有的隔離級別，

在事務中混合使用存盤引擎:MySQL服務器層不管理事務，事務是由下層的存盤引擎實作的，所以在同一個事務中，使用多種存盤引擎是不可靠的，如果在事務中混合使用了事務型和非事務型的表（例如InnoDB和MyISAM表），在正常提交的情況下不會有什么問題，但如果該事務需要回滾，非事務型的表上的變更就無法撤銷，這會導致資料庫處于不一致的狀態，這種情況很難修復，事務的最終結果將無法確定，所以，為每張表選擇合適的存盤引擎非常重要，

隱式和顯式鎖定:

InnoDB采用的是兩階段鎖定協議（two-phase locking protocol），在事務執行程序中，隨時都可以執行鎖定，鎖只有在執行COMMIT或者ROLLBACK的時候才會釋放，并且所有的鎖是在同一時刻被釋放，前面描述的鎖定都是隱式鎖定，InnoDB會根據隔離級別在需要的時候自動加鎖，
另外，InnoDB也支持通過特定的陳述句進行顯式鎖定，這些陳述句不屬于SQL規范:

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABLES陳述句，這是在服務器層實作的，和存盤引擎無關，它們有自己的用途，但并不能替代事務處理，如果應用需要用到事務，還是應該選擇事務型存盤引擎，

經常可以發現，應用已經將表從MyISAM轉換到InnoDB，但還是顯式地使用LOCK TABLES陳述句，這不但沒有必要，還會嚴重影響性能，實際上InnoDB的行級鎖作業得更好，

多版本并發控制

MySQL的大多數事務型存盤引擎實作的都不是簡單的行級鎖，基于提升并發性能的考慮，它們一般都同時實作了多版本并發控制（MVCC），不僅是MySQL，包括Oracle、PostgreSQL等其他資料庫系統也都實作了MVCC，但各自的實作機制不盡相同，因為MVCC沒有一個統一的實作標準，

可以認為MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低，雖然實作機制有所不同，但大都實作了非阻塞的讀操作，寫操作也只鎖定必要的行，

MVCC的實作，是通過保存資料在某個時間點的快照來實作的，也就是說，不管需要執行多長時間，每個事務看到的資料都是一致的，根據事務開始的時間不同，每個事務對同一張表，同一時刻看到的資料可能是不一樣的，如果之前沒有這方面的概念，這句話聽起來就有點迷惑，熟悉了以后會發現，這句話其實還是很容易理解的，

前面說到不同存盤引擎的MVCC實作是不同的，典型的有樂觀（optimistic）并發控制和悲觀（pessimistic）并發控制，下面我們通過InnoDB的簡化版行為來說明MVCC是如何作業的，

InnoDB的MVCC，是通過在每行記錄后面保存兩個隱藏的列來實作的，這兩個列，一個保存了行的創建時間，一個保存行的過期時間（或洗掉時間），當然存盤的并不是實際的時間值，而是系統版本號（system version number），每開始一個新的事務，系統版本號都會自動遞增，事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較，下面看一下在REPEATABLE READ隔離級別下，MVCC具體是如何操作的，

SELECT:

InnoDB會根據以下兩個條件檢查每行記錄：

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

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

INSERT:

InnoDB為新插入的每一行保存當前系統版本號作為行版本號，

DELETE:

InnoDB為洗掉的每一行保存當前系統版本號作為行洗掉標識，

UPDATE:

InnoDB為插入一行新記錄，保存當前系統版本號作為行版本號，同時保存當前系統版本號到原來的行作為行洗掉標識，

保存這兩個額外系統版本號，使大多數讀操作都可以不用加鎖，這樣設計使得讀資料操作很簡單，性能很好，并且也能保證只會讀取到符合標準的行，不足之處是每行記錄都需要額外的存盤空間，需要做更多的行檢查作業，以及一些額外的維護作業，MVCC只在REPEATABLE READ和READ COMMITTED兩個隔離級別下作業，其他兩個隔離級別都和MVCC不兼容，因為READ UNCOMMITTED總是讀取最新的資料行，而不是符合當前事務版本的資料行，而SERIALIZABLE則會對所有讀取的行都加鎖，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66589.html

標籤：MySQL

上一篇：求助 ! 如下是SQLSERVER語法轉換成ORACLE怎么寫?：判斷表中是否有資料，再決定是否關聯此表的寫法

下一篇：謝謝，行轉列，如下是SQLSERVER語法轉換成ORACLE怎么寫