寫在前面：最早接觸的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

 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鎖會覆蓋以下區間(“(”表示不包含，“[”表示包含)：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
對于最后一個區間，next-key鎖將鎖住索引最大值以上的區間，上界虛記錄(“supremum” pseudo-record)的值比索引中的任何值都大，其實，上界不是一個真實的索引記錄，所以，next-lock將對索引的最大值之后的區間加鎖。

間隙鎖對查詢唯一索引中的唯一值是沒有必要的，例如，id列有唯一索引，則下面的查詢僅對id=100的元組加索引記錄鎖(index-record lock)，而不管其它連接是否在之前的區間插入元組。
SELECT * FROM child WHERE id = 100;
如果id沒有索引，或者非唯一索引，則語句會鎖住之前的空間。

例1-4

Session 1

Session 2

mysql> create unique index i_index on t(i);

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t;

+------+

| i    |

+------+

|    4 |

|   10 |

+------+

2 rows in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select i from t where i =10 lock in share mode;

+------+

| i    |

+------+

|   10 |

+------+

1 row in set (0.00 sec)

mysql> insert into t(i) values(9);

Query OK, 1 row affected (0.03 sec)

mysql> select * from t;

+------+

| i    |

+------+

|    4 |

|    9 |

|   10 |

+------+

3 rows in set (0.00 sec)

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

例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來實現：
CREATE TABLE mytable (
   a    INTEGER  NOT NULL PRIMARY KEY,
   b    CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
上面的例子中，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]節添加下面行來允許多表空間：
[mysqld]
innodb_file_per_table
重啟服務器之后，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將使用鎖。