MySQL 引擎 和 InnoDB并發控制 簡介

       MySQL存儲引擎采用了可插拔的結構，即使用者可以根據自己的需要來選擇不同的存儲引擎。

下表是MySQL不同的存儲引擎的不同的特性：

Feature	MyISAM	BDB	Memory	InnoDB	Archive	NDB
Storage limits	256TB	No	Yes	64TB	No	384EB[4]
Transactions	No	Yes	No	Yes	No	Yes
Locking granularity	Table	Page	Table	Row	Row	Row
MVCC (snapshot read)	No	No	No	Yes	Yes	No
Geospatial support	Yes	Yes[1]	No	Yes[1]	Yes[1]	Yes[1]
B-tree indexes	Yes	Yes	Yes	Yes	No	Yes
Hash indexes	No	No	Yes	No	No	Yes
Full-text search indexes	Yes	No	No	No	No	No
Clustered indexes	No	Yes	No	Yes	No	No
Data caches	No	Yes	N/A	Yes	No	Yes
Index caches	Yes	Yes	N/A	Yes	No	Yes
Compressed data	Yes	No	No	No	Yes	No
Encrypted data[2]	Yes	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	No	Yes
Replication support[3]	Yes	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	No	Yes	No	No
Backup / point-in-time recovery[3]	Yes	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes	Yes

幾個常用的存儲引擎：

（1）MyISAM：它主要用于大多數的Web、資料倉庫和其它應用中。可以通過資料庫配置檔案中的storage_engine選項來改變預設的存儲引擎。

（2）InnoDB：主要用于事務處理應用，并且支援事務的ACID特性和外鍵。

（3）BDB：支援COMMIT，ROLLBACK和其它事務特性。

在建立表或修改表的時候，都可以指定需要使用的存儲引擎：

       SQL>CREATE TABLE engineTest (id INT) ENGINE = MyISAM;

       SQL>ALTER TABLE engineTest ENGINE = ARCHIVE;

       雖然InnoDB和BDB都支援事務，但是相比而已InnoDB支援得更好。

       InnoDB通過多版本并發控制MVCC來支援事務的，允許COMMIT, ROLLBACK和svepoints。 而BDB支援事務，隻是允許COMMIT和ROLLBACK。

       由于我們主要研究MySQL的多版本并發控制機制，是以，後面主要是解析InnoDB存儲引擎的代碼。

       InnoDB的設計是為了在處理大資料量的時候得到最好的性能。InnoDB存儲引擎維護了一個它自己的緩沖區，用來存儲資料和索引。InnoDB将表和索引存儲在一個表空間中，這個表空間可能由不同的檔案組成。而MyISAM存儲引擎的表中每個表都存在一個獨立的檔案裡面。

       和達夢一樣InnoDB的每個表都對應了一個相應的聚簇索引，如果表上有主鍵的話，則聚簇索引使用主鍵作為索引鍵，如果沒有主鍵的話，則選擇第一個非空列的非唯一索引作為聚簇索引，如果都沒有的話，則使用rowid作為索引鍵。

事務模型：

       InnoDB事務模型是将傳統的兩階段封鎖協定同多版本資料庫特性相結合。它采用加行級鎖和查詢不加鎖。

鎖模型：

有兩種類型的鎖，共享鎖和排它鎖

（1）共享鎖S允許事務讀一條記錄

（2）排它鎖X允許事務更新或删除一條記錄

如果事務T1擁有行t上的共享鎖，那麼：

       如果其它事務T2請求t上的S鎖，那麼可以被立即授予。這樣T1和T2都擁有t上的S鎖。

       如果其它事務T2請求t上的X鎖，那麼不能被授予。

       如果事務T1擁有行t上的X鎖，那麼其它事務請求t上的任何鎖都不能被授予。 另外，InnoDB支援多種上鎖粒度，它允許同時加行鎖和表鎖。為了支援多粒度鎖，引入了一個新的鎖，意向鎖。意向鎖是加在表上的鎖。意向鎖就是表明某個事務之後要對這個表上的某個行加該類型的鎖。

       共享意向鎖IS，表明事務T将要在表T的某些行上加S鎖。

       排他意向鎖IX，表明事務T将要在表T的某些行上加X鎖

意向鎖協定是：

       在某個事務請求行上的S鎖之前，它必須先得到該行所在表的IS鎖或更強的鎖。

       在某個事務請求行上的X鎖之前，它必須先得到該行所在表的IX鎖。

下面是鎖的相容性矩陣：（相容為1,不相容為0）

X	IX	S	IS
X
IX	1	1
S	1	1
IS	1	1	1

一個鎖可以被授予被某個事務，如果事務請求的鎖和已經上的鎖相容。

隔離級别：

       InnoDB預設隔離級别是REPEATABLE READ。InnoDB支援SQL标準的四個隔離級别。

一緻性非上鎖讀：

       InnoDB使用多版本的方式來控制一緻性讀，也就是說，給某個查詢在該時刻的一個資料庫的快照。這個查詢可以看到這個時刻以前由其它事務送出的操作，而看不到之後做的改變或還未送出的改變。這個規則的唯一例外就是，事務可以看到本事務之前所做的還未送出的操作。

       這個規則導緻了下面的異常：如果你更新了某個表裡面的行，使用SELECT将可以看見最新更新的行和老版本的行。如果其它事務同時更新相同的表，那麼你就可能看到根本不可能在資料庫中存在的狀态。

       如果在某人的REPEATABLE READ隔離級别下的話，所有同一事物的所有一緻性讀都是讀的第一次查詢時建立的快照。如果想得到最新的快照的話，那麼需要送出目前的事務，然後再開始新的查詢。

       注意：DROP TABLE 和ALTER TABLE語句不使用一緻性讀。因為DROP TABLE的話，MYSQL不能使用已經删除了的表。而ALTER TABLE的時候，MYSQL是将原來的表複制一份，然後删除掉原來的表。

Next-Key Locking：避免幻象

       在行級鎖中，InnoDB使用一種稱為next-key locking的算法。當檢索表的一個索引的時候，它對遇到的索引記錄加S或X鎖。是以行級鎖實際上是索引記錄鎖。

       InnoDB在索引記錄上加鎖的時候也影響了索引記錄前的‘gap’。如果一個使用者擁有索引上某個記錄R的S或X鎖，另一個使用者不能馬上在記錄R前插入一個新的索引記錄。這樣就可以避免幻象的出現。

多版本的實作

       為了實作多版本，InnoDB必須在表空間中儲存行的舊版本資訊。這些資訊被儲存在復原段中。

       在内部，InnoDB為每個行增加了兩個域，一個6-byte的域來訓示最後插入或更新這個行的事務辨別符，删除标志也被認為是一個更新，因為它在送出前隻是在行上做了一個标記。另外一個7-byte的域被稱為復原指針(roll pointer)，復原指針指向一個由復原段寫入的undo日志記錄。如果一個行被更新了，undo日志記錄包含了重建這行更新前資訊的一些必要資料。

       InnoDB使用復原段的資訊來執行事務復原所必須的一些undo操作，而且也使用這些資訊來重建更新前的行資訊。

       復原段中的undo日志被分為插入日志和更新日志。插入日志僅在事務復原的時候有用，事務送出之後就可以馬上删除掉。更新日志在一緻性讀的時候需要使用，但是，如果目前沒有事務再可能使用復原段中的記錄的時候，這些記錄就可以删除掉了。是以，你必須經常送出你的事務，就算這些事務隻是進行一緻性讀操作而已。否則，InnoDB不能删除掉某些更新日志，這樣復原段将變得越來越大。

       復原段中undo日志記錄的實體大小要比其對應的插入或更新的行要小很多。

在多版本方式下，當你使用SQL語句删除某一行的時候，該行并不會馬上從資料庫的實體檔案上移除。隻有當InnoDB能夠删除掉更新日志記錄的時候，那些行及其對應的索引記錄才會真正從實體上删除掉。這個移除操作稱為purge。