理解 MySQL（1）：架構和概念

寫在前面：最早接觸的MySQL是在2006年前，那時候MySQL還是4.x版本，很多功能都不支援，比如，儲存過程，檢視，觸發器，更別說分散式事務等複雜特性了。但從5.0(2005年10月)開始，MySQL漸漸步入企業級資料庫的行列了；複製、叢集、分割槽、分散式事務，這些企業級的特性，使得現在的MySQL，完全可以應用於企業級應用環境(很多網際網路公司都用其作為資料庫伺服器，儘管節約成本是一個因素，但是沒有強大功能作後盾，則是不可想象的)。

雖然，MySQL還有很多不足，比如，複製、分割槽的支援都十分有限、查詢優化仍需要改進，但是MySQL已經是一個足夠好的DBMS了，更何況它是opensource的。這段時間沒有事，出於好奇，略微的研究了一下MySQL，積累了一些資料，欲總結出來。這些資料打算分為兩部分，上部主要討論MySQL的優化，其中主要參考了《MySQL Manual》和《High Performance MySQL》，如果有時間，以後在下部分析一下MySQL的原始碼。如果你是MySQL高手，希望你不吝賜教；如果你是新手，希望對你有用。

第一章、MySQL架構與概念

1、MySQL的邏輯架構

最上面不是MySQL特有的，所有基於網路的C/S的網路應用程式都應該包括連線處理、認證、安全管理等。

中間層是MySQL的核心，包括查詢解析、分析、優化和快取等。同時它還提供跨儲存引擎的功能，包括儲存過程、觸發器和檢視等。

最下面是儲存引擎，它負責存取資料。伺服器通過storage engine API可以和各種儲存引擎進行互動。

1.1、查詢優化和執行(Optimization and Execution)

MySQL將使用者的查詢語句進行解析，並建立一個內部的資料結構——分析樹，然後進行各種優化，例如重寫查詢、選擇讀取表的順序，以及使用哪個索引等。查詢優化器不關心一個表所使用的儲存引擎，但是儲存引擎會影響伺服器如何優化查詢。優化器通過儲存引擎獲取一些引數、某個操作的執行代價、以及統計資訊等。在解析查詢之前，伺服器會先訪問查詢快取(query cache)——它儲存SELECT語句以及相應的查詢結果集。如果某個查詢結果已經位於快取中，伺服器就不會再對查詢進行解析、優化、以及執行。它僅僅將快取中的結果返回給使用者即可，這將大大提高系統的效能。

1.2、併發控制

MySQL提供兩個級別的併發控制：伺服器級(the server level)和儲存引擎級(the storage engine level)。加鎖是實現併發控制的基本方法，MySQL中鎖的粒度：

• 表級鎖：MySQL獨立於儲存引擎提供表鎖，例如，對於ALTER TABLE語句，伺服器提供表鎖(table-level lock)。
• 行級鎖：InnoDB和Falcon儲存引擎提供行級鎖，此外，BDB支援頁級鎖。InnoDB的併發控制機制，下節詳細討論。

另外，值得一提的是，MySQL的一些儲存引擎（如InnoDB、BDB）除了使用封鎖機制外，還同時結合MVCC機制，即多版本兩階段封鎖協議(Multiversion two-phrase locking protocal)，來實現事務的併發控制，從而使得只讀事務不用等待鎖，提高了事務的併發性。

注：併發控制是DBMS的核心技術之一(實際上，對於OS也一樣)，它對系統效能有著至關重要的影響，以後再詳細討論。

1.3、事務處理

MySQL中，InnoDB和BDB都支援事務處理。這裡主要討論InnoDB的事務處理(關於BDB的事務處理，也十分複雜，以前曾較為詳細看過其原始碼，以後有機會再討論)。

1.3.1、事務的ACID特性

事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性(Jim Gray在《事務處理：概念與技術》中對事務進行了詳盡的討論)。

(1)原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。

(2)一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；事務結束時，所有的內部資料結構（如B樹索引或雙向連結串列）也都必須是正確的。

(3)隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。

(4)永續性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

1.3.2、事務處理帶來的相關問題

由於事務的併發執行，帶來以下一些著名的問題：

(1)更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題－－最後的更新覆蓋了由其他事務所做的更新。

(2)髒讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“髒”資料，並據此做進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象地叫做”髒讀”。

(3)不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些資料後的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重複讀”。

(4)幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”。

1.3.3、事務的隔離性

SQL2標準定義了四個隔離級別。定義語句如下：

這與Jim Gray所提出的隔離級別有點差異。其中READ UNCOMMITTED即Jim的10（瀏覽）；READ COMMITTED即20，遊標穩定性；REPEATABLE READ為2.99990隔離(沒有幻像保護)；SERIALIZABLE隔離級別為30，完全隔離。SQL2標準預設為完全隔離(30)。各個級別存在問題如下：

隔離級	髒讀	不可重複讀	幻象讀
讀未提交(Read uncommitted)	可能	可能	可能
讀提交(Read committed)	不可能	可能	可能
可重複讀(Repeatable read)	不可能	不可能	可能
可序列化(Serializable)	不可能	不可能	不可能

各個具體資料庫並不一定完全實現了上述4個隔離級別，例如，Oracle只提供READ COMMITTED和Serializable兩個標準隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支援上述ISO/ANSI SQL92定義的4個隔離級別外，還支援一個叫做“快照”的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。MySQL 支援全部4個隔離級別，其預設級別為Repeatable read，但在具體實現時，有一些特點，比如在一些隔離級別下是採用MVCC一致性讀。國產資料庫DM也支援所有級別，其預設級別為READ COMMITTED。

1.3.4、InnoDB的鎖模型

InnoDB的行級鎖有兩種型別：

(1)共享鎖(shared lock，S)：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。

(2)排它鎖(exclusive lock，X)：允許獲得排它鎖的事務更新資料，阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖。

此外，InnoDB支援多粒度加鎖(multiple granularity locking)，從而允許對記錄和表同時加鎖。為此，InnoDB引入意向鎖(intention locks)，意向鎖是針對表的：

(1)意向共享鎖（IS）：事務打算給資料行加行共享鎖，事務在給一個資料行加共享鎖前必須先取得該表的IS鎖。

(2)意向排他鎖（IX）：事務打算給資料行加行排他鎖，事務在給一個資料行加排他鎖前必須先取得該表的IX鎖。

例如，SELECT … LOCK IN SHARE MODE加IS鎖，SELECT … FOR UPDATE加IX鎖，意向鎖的規則如下：

(1)事務在對錶T中的記錄獲取S鎖前，先要獲取表T的IS鎖或者更強的鎖；

(2)事務在獲取表T中記錄的X鎖前，先要獲取表T的IX鎖。

InnoDB的鎖相容性矩陣：

如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。意向鎖只會阻塞其它事務對錶的請求，例如，LOCK TABLES …WRITE，意向鎖的主要目的是表明該事務將要或者正在對錶中的記錄加鎖。使用封鎖機制來進行併發控制，一個比較重要的問題就是死鎖。

來看一個死鎖的例子：

例1-1

1.3.5、一致性非阻塞讀

一致性讀是MySQL的重要特點之一，InnoDB通過MVCC機制表示資料庫某一時刻的查詢快照，查詢可以看該時刻之前提交的事務所做的改變，但是不能看到該時刻之後或者未提交事務所做的改變。但是，查詢可以看到同一事務中之前語句所做的改變，例如：

例1-2

如果事務的隔離級別為REPEATABLE READ（預設），同一個事務中的所有一致性讀都是讀的事務的第一次讀操作建立的快照。你可以提交當前事務，然後在新的查詢中即可看到最新的快照，如上所示。

如果事務的隔離級別為READ COMMITTED，一致性讀只是對事務內部的讀操作和它自己的快照而言的，結果如下：

例1-3

注意，session 2發生了不可重複讀。

當InnoDB在READ COMMITTED 和REPEATABLE READ隔離級別下處理SELECT語句時，一致性讀是預設的模式。一致性讀不會對錶加任何鎖，所以，其它連線可以同時改變表。

假設事務處於REPEATABLE READ級別，當你正在進行一致性讀時，InnoDB根據查詢看到的資料給你一個時間點。如果其它的事務在該時間點之後刪除一行，且提交事務，你不會看到行已經被刪除，插入和更新操作一樣。但是，InnoDB與其它DBMS的不同是，在REPEATABLE READ隔離級別下並不會造成幻像。

一致性讀不與DROP TABLE 或者 ALTER TABLE一起工作。

在nodb_locks_unsafe_for_binlog變數被設定或者事務的隔離級別不是SERIALIZABLE的情況下，InnoDB對於沒有指定FOR UPDATE 或 LOCK IN SHARE MODE的INSERT INTO … SELECT, UPDATE … (SELECT), 和CREATE TABLE … SELECT語句使用一致性讀，在這種情況下，查詢語句不會對錶中的元組加鎖。否則，InnoDB將使用鎖。

1.3.6、SELECT … FOR UPDATE和SELECT … LOCK IN SHARE MODE的加鎖讀(locking read)

在一些場合，一致性讀並不是很方便，此時，可以用加鎖讀。InnoDB支援兩種加鎖讀：

(1) SELECT … LOCK IN SHARE MODE：對讀取的元組加S鎖。

(2) SELECT … FOR UPDATE：在掃描索引記錄的過程中，會阻塞其它連線的SELECT …LOCK IN SHARE MODE和一定事務隔離級別下的讀操作。

InnoDB使用兩階段封鎖協議，事務直到提交或回滾時才會釋放所有的鎖，這都是系統自動執行的。此外，MySQL支援LOCK TABLES和UNLOCK TABLES，但這些都是在伺服器層實現的，而不是在儲存引擎。它們有用處，但是不能取代儲存引擎完成事務處理，如果你需要事務功能，請使用事務型儲存引擎。

來考慮locking read的應用，假設你要在表child插入一個新的元組，並保證child中的記錄在表parent有一條父記錄。如果你用一致性讀來讀parent表，確實可以將要插入的child row的parent row，但是可以安全的插入嗎？不，因為在你讀parent表時，其它連線可能已經刪除該記錄。（一致性讀是針對事務內而言的，對於資料庫的狀態，它應該叫做“不一致性讀”）

此時，就可以使用SELECT LOCK IN SHARE MODE，它會對讀取的元組加S鎖，從而防止其它連線刪除或更新元組。另外，如果你想在查詢的同時，進行更新操作，可以使用SELECT … FOR UPDATE，它讀取最新的資料，然後對讀到的元組加X鎖。此時，使用SELECT … LOCK IN SHARE MODE不是一個好主意，因為此時如果有兩個事務進行這樣的操作，就會造成死鎖。

注：SELECT … FOR UPDATE僅在自動提交關閉(即手動提交)時才會對元組加鎖，而在自動提交時，符合條件的元組不會被加鎖。

1.3.7、記錄鎖(record lok)、間隙鎖(gap lock)和後碼鎖(next-key lock)

InnoDB有以下幾種行級鎖：

(1)記錄鎖：對索引記錄(index records)加鎖，InnoDB行級鎖是通過給索引的索引項加鎖來實現的，而不是對記錄例項本身加鎖。如果表沒有定義索引，InnoDB建立一個隱藏的聚簇索引，然後用它來實現記錄加鎖（關於索引與加鎖之間的關係的詳細介紹請看下一章）。

(2)間隙鎖：對索引記錄之間的區間，或者第一個索引記錄之前的區間和最後一個索引之後的區間加鎖。

(3)後碼鎖：對索引記錄加記錄鎖，且對索引記錄之前的區間加鎖。

預設情況下，InnoDB的事務工作在REPEATABLE READ的隔離級別，而且系統變數innodb_locks_unsafe_for_binlog為關閉狀態。此時，InnoDB使用next-key鎖進行查詢和索引掃描，從而達到防止“幻像”的目的。

Next-key鎖是記錄鎖和間隙的結合體。當InnoDB查詢或掃描表的索引時，對它遇到的索引記錄加S鎖或者X鎖，所以，行級鎖(row-level lock)實際上就是索引記錄鎖(index-record lock)；此外，它還對索引記錄之前的區間加鎖。也就是說，next-key鎖是索引記錄鎖，外加索引記錄之前的區間的間隙鎖。如果一個連線對索引中的記錄R持有S或X鎖，其它的連線不能按照索引的順序在R之前的區間插入一個索引記錄。

假設索引包含以下值：10, 11,13和20，則索引的next-key鎖會覆蓋以下區間(“(”表示不包含，“[”表示包含)：

對於最後一個區間，next-key鎖將鎖住索引最大值以上的區間，上界虛記錄(“supremum” pseudo-record)的值比索引中的任何值都大，其實，上界不是一個真實的索引記錄，所以，next-lock將對索引的最大值之後的區間加鎖。

間隙鎖對查詢唯一索引中的唯一值是沒有必要的，例如，id列有唯一索引，則下面的查詢僅對id=100的元組加索引記錄鎖(index-record lock)，而不管其它連線是否在之前的區間插入元組。

如果id沒有索引，或者非唯一索引，則語句會鎖住之前的空間。

例1-4

上例中，產生了幻像問題。如果將唯一查詢變成範圍查詢，結果如下(接上例的索引)：

例1-5

可以看到，session 2 的next-key使得在i=4之前的區間和之後的插入都被阻塞。

另外，如果刪除索引i_index，則結果如下：

例1-6

另外，針對插入(INSERT)操作，只要多個事務不會在同一索引區間的同一個位置插入記錄，它們就不用互相等待，這種情況可以稱為插入意向間隙鎖(insertion intention gap lock)。例如，索引記錄的值為4和7，兩個獨立的事務分別插入5和6，僅管它們都持有4—7之間的間隙鎖，但是它們不會相互阻塞。這可以提高事務的併發性。

例1-7

間隙鎖是可以顯示關閉的，如果你將事務的隔離級別設為READ COMMITTED，或者開啟innodb_locks_unsafe_for_binlog系統變數，間隙鎖就會關閉。在這種情況下，查詢或掃描索引僅會進行外來鍵約束檢查和重複鍵值檢查。

此外，READ COMMITTED隔離級別和關閉nodb_locks_unsafe_for_binlog還有另外一個負作用：MySQL會釋放掉不匹配Where條件的記錄鎖。例如，對於UPDATE語句，InnoDB只能進行“半一致性(semi_consistent)讀”，所以，它會返回最新提交事務所做改變，從而產生不可重複讀和幻像問題。

1.3.8、使用next-key lock防止幻像問題

例1-4展示了一個幻像問題。使用next-key鎖的select語句可以解決幻像問題，但例1-4的之所以會產生總是在於唯一索引，使得select語句沒有使用gap lock，而只使用了index-record lock。

1.4、儲存引擎

外掛式儲存引擎是MySQL最重要特性之一，也是最不同於其它DBMS的地方。MySQL支援很多儲存引擎，以適用於不同的應用需求，常用的包括MyISAM、InnoDB、BDB、MEMORY、MERGE、NDB Cluster等。其中，BDB和NDB Cluster提供事務支援。

MySQL預設的儲存引擎為MyISAM，當然，建立表的時候可以指定其它的儲存引擎，你可以在同一個資料庫中對不同的表使用不同的儲存引擎(這是非常強大而獨特的特性)。可以通過SHOW TABLE STATUS命令查詢表所使用的儲存引擎，例如，檢視mysql資料庫的user表：

• Name：表的名稱；
• Engine：表使用的儲存引擎；
• Row_format：記錄的格式。MyISAM支援三種不同的儲存格式：靜態(固定長度)表(預設格式)、動態表及壓縮表。靜態表的欄位都是固定長度的，例如CHAR和INTEGER；動態表的欄位可以是變長的，例如，VARCHAR或者BLOB。
• Rows：表中記錄的數量。
• Avg_row_length：記錄的平均長度(位元組數)；
• Data_length：表中資料的全部位元組數；
• Max_data_length：表中資料最大的位元組數；
• Index_length：索引消耗的磁碟空間；
• Data_free：對於MyISAM表，表示已經分配但還沒有使用的空間；該空間包含以前刪除的記錄留下的空間，可以被INSERT操作重用。
• Auto_increment：下一個自增的值。
• Check_time：上次使用CHECK TABLE或myisamchk檢查表的時間。

1.4.1、MyISAM

1.4.1.1、儲存

MySQL的預設儲存引擎，效能與功能的折中，包括全文索引(full-text index)、資料壓縮，支援空間(GIS)資料，但是，不支援事務和行級鎖。一般來說，MyISAM更適用於大量查詢操作。如果你有大量的插入、刪除操作，你應該選擇InnoDB。
每個表包含3個檔案：

(1).frm：表定義檔案，對於其它儲存引擎也一樣。
(2).MYD檔案：資料檔案。
(3).MYI檔案：索引檔案。

可以在建立表時通過DATA DIRECTORY和INDEX DIRECTORY為資料檔案和索引檔案指定路徑，它們可以位於不同目錄。另外，MyISAM的儲存格式是跨平臺的，你可以將資料檔案和索引檔案從Intel平臺拷貝到PPC或者SPARC平臺。

5.0中，MyISAM的變長記錄表預設處理256TB資料，使用6位元組的指標來指向資料記錄；而之前的版本使用預設的4位元組指標，所以只能處理4GB資料。所有的版本都可以將指標增加到8位元組指標，如果你想改變MyISAM表的指標的大小，可以通過設定MAX_ROWS和AVG_ROW_LENGTH來實現：

上面的例子中，MySQL將至少可以儲存32GB的資料。可以檢視一下表的資訊：

可以看到，Create_options列出了建立時的選項，而且該表的最大的資料量為91GB。你可以用ALTER TABLE來改變指標的大小，但是那會導致表和索引的重建，這會花費很長的時間。

1.4.1.2、MyISAM的特性

(1)鎖與併發性：MyISAM只有表級鎖，不支援行級鎖。所以不適合於大量的寫操作，但是它支援併發插入(concurrent inserts)，這是一個非常重要且有用的特性。

(2)自動修復：MySQL支援自動檢查和修復MyISAM表。

(3)手動修復：你可以使用CHECK TABLE檢查表的狀態，並用REPAIR TABLE修復表。

(4)索引：你可以為BLOB和TEXT的前500個字元建立索引。而且，MyISAM還支援全文索引，但僅限於CHAR、VARCHAR、和TEXT列。

(5)延遲鍵寫(Delayed key writes)：如果建立MyISAM表時指定DELAY_KEY_WRITE，MySQL在查詢結束時，不會將改變的索引資料寫入磁碟，而將修改儲存在key buffer中。只有要改變快取或者關閉表時，才會把索引資料刷入磁碟。

1.4.2、InnoDB

InnoDB是一個高效能的事務儲存引擎，此外，BDB也支援事務處理(關於BDB，以前曾較為詳細的閱讀過其原始碼，以後有時間再討論)，它有以下一些特點：

1.4.2.1、表空間

InnoDB儲存表和索引有兩種方式：

(1)共享表空間儲存：這種方式下，表的定義位於.frm檔案中，資料和索引儲存在innodb_data_home_dir和innodb_data_file_path指定的表空間中。

(2)多表空間儲存：表的定義仍位於.frm檔案，但是，每個InnoDB表和它的索引在它自己的檔案(.idb)中，每個表有它自己的表空間。

對那些想把特定表格移到分離物理磁碟的使用者，或者那些希望快速恢復單個表的備份而無須打斷其餘InnoDB表的使用的使用者，使用多表空間會是有益的。你可以往my.cnf的[mysqld]節新增下面行來允許多表空間：

重啟伺服器之後，InnoDB儲存每個新建立的表到表格所屬於的資料庫目錄下它自己的檔案tbl_name.ibd裡。這類似於MyISAM儲存引擎所做的，但MyISAM 把表分成資料檔案tbl_name.MYD和索引檔案tbl_name.MYI。對於InnoDB，資料和所以被一起存到.ibd檔案。tbl_name.frm檔案照舊依然被建立。

如果你從my.cnf檔案刪除innodb_file_per_table行，並重啟伺服器，InnoDB在共享的表空間檔案裡再次建立表。

innodb_file_per_table隻影響表的建立。如果你用這個選項啟動伺服器，新表被用.ibd檔案來建立，但是你仍舊能訪問在共享表空間裡的表。如果你刪掉這個選項，新表在共享表空間內建立，但你仍舊可以訪問任何用多表空間建立的表。

InnoDB總是需要共享表空間，.ibd檔案對InnoDB不足以去執行，共享表空間包含熟悉的ibdata檔案，InnoDB把內部資料詞典和undo日誌放在這個檔案中。

1.4.2.2、外來鍵約束

MySQL中，支援外來鍵的儲存引擎只有InnoDB，在建立外來鍵時，要求被參照表必須有對應的索引，參照表在建立外來鍵時也會自動建立對應的索引。

1.4.2.3、MVCC與後碼鎖(next-key locking)

InnoDB將MVCC機制與next-key lock結合起來，實現事務的各個隔離級別，這是非常用意思的。在nodb_locks_unsafe_for_binlog變數被設定或者事務的隔離級別不是SERIALIZABLE的情況下，InnoDB對於沒有指定FOR UPDATE 或 LOCK IN SHARE MODE的INSERT INTO … SELECT, UPDATE … (SELECT), 和CREATE TABLE … SELECT語句使用一致性讀(參照前面)，在這種情況下，查詢語句不會對錶中的元組加鎖。否則，InnoDB將使用鎖。

 

主要參考：

《MySQL Manual》

《High Performance MySQL》