寫在前面：最早接觸的MySQL是在三年前，那時候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中鎖的粒度：
(1)    表級鎖：MySQL獨立于存儲引擎提供表鎖，例如，對于ALTER TABLE語句，服務器提供表鎖(table-level lock)。
(2)    行級鎖：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標準定義了四個隔離級別。定義語句如下：
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED |
READ COMMITTED  |
REPEATABLE READ  |
SERIALIZABLE ]
這與Jim Gray所提出的隔離級別有點差異。其中READ UNCOMMITTED即Jim的10（瀏覽）；READ COMMITTED即20，游標穩定性；REPEATABLE READ為2.99990隔離(沒有幻像保護)；SERIALIZABLE隔離級別為30，完全隔離。SQL2標準默認為完全隔離(30)。各個級別存在問題如下：

各個具體數據庫并不一定完全實現了上述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