為了充分發(fā)揮MySQL的性能并順利地使用，就必須理解其設(shè)計。MySQL的靈活性體現(xiàn)在很多方面。例如，你可以通過配置使它在不同的硬件上都運行得很好，也可以支持多種不同的數(shù)據(jù)類型。但是，MySQL最重要、最與眾不同的特性是它的存儲引擎架構(gòu)：

• 查詢處理(Query Processing)。
• 其他系統(tǒng)任務(wù)(Server Task)。
• 數(shù)據(jù)的存儲/提取。

這種處理和存儲分離的設(shè)計可以在使用時根據(jù)性能、特性，以及其他需求來選擇數(shù)據(jù)存儲的方式。

1.1 MySQL邏輯架構(gòu)

MySQL服務(wù)器邏輯架構(gòu)圖.png

存儲引擎不會去解析SQL。InnoDB是一個例外，它會解析外鍵定義，因為MySQL服務(wù)器本身沒有實現(xiàn)該功能

1.1.1 連接管理與安全性

每個客戶端連接都會在服務(wù)器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中執(zhí)行，該線程只能輪流在某個CPU核心或者CPU中運行。服務(wù)器會負責(zé)緩存線程，因此不需要為每一個新建的連接創(chuàng)建或者銷毀線程。

MySQL 5.5或者更新的版本提供了一個API，支持線程池(Thread-Pooling)插件，可以使用池中少量的線程來服務(wù)大量的連接。（筆者注：漲見識了，沒想到在MySQL也可以使用線程池，理論上這是一個優(yōu)化點）

當(dāng)客戶端(應(yīng)用)連接到MySQL服務(wù)器時，服務(wù)器需要對其進行認證。認證基于用戶名、原始主機信息（筆者注：另類的權(quán)限設(shè)置優(yōu)化？）和密碼。如果使用了安全套接字(SSL)的方式連接，還可以使用X.509證書認證。一旦客戶端連接成功，服務(wù)器會繼續(xù)驗證該客戶端是否具有執(zhí)行某個特定查詢的權(quán)限(例如，是否允許客戶端對world數(shù)據(jù)庫的Country表執(zhí)行SELECT語句)。

1.1.2 優(yōu)化與執(zhí)行

MySQL會解析查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)(解析樹)，然后對其進行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等（筆者注：這應(yīng)該屬于邏輯結(jié)構(gòu)的第二層）。用戶可以通過特殊的關(guān)鍵字提示(hint)優(yōu)化器，影響它的決策過程。也可以請求優(yōu)化器解釋(explain)優(yōu)化過程的各個因素，使用戶可以知道服務(wù)器是如何進行優(yōu)化決策的，并提供一個參考基準(zhǔn)，便于用戶重構(gòu)查詢和schema、修改相關(guān)配置，使應(yīng)用盡可能高效運行。第6章我們將討論更多優(yōu)化器的細節(jié)。
??優(yōu)化器并不關(guān)心表使用的是什么存儲引擎，但存儲引擎對于優(yōu)化查詢是有影響的。優(yōu)化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數(shù)據(jù)的統(tǒng)計信息。例如，某些存儲引擎的某種索引，可能對一些特定的查詢有優(yōu)化。關(guān)于索引與schema的優(yōu)化，請參見第4章和第5章。
??對于SELECT語句，在解析查詢之前，服務(wù)器會先檢查查詢緩存(Query Cache)，如果能夠在其中找到對應(yīng)的查詢，服務(wù)器就不必再執(zhí)行查詢解析、優(yōu)化和執(zhí)行的整個過程，而是直接返回查詢緩存中的結(jié)果集。第7章詳細討論了相關(guān)內(nèi)容。

1.2 并發(fā)控制

無論何時，只要有多個查詢需要在同一時刻修改數(shù)據(jù)，都會產(chǎn)生并發(fā)控制的問題。本章的目的是討論MySQL在兩個層面的并發(fā)控制:服務(wù)器層與存儲引擎層。并發(fā)控制是一個內(nèi)容龐大的話題，有大量的理論文獻對其進行過詳細的論述。本章只簡要地討論MySQL如何控制并發(fā)讀寫，因此讀者需要有相關(guān)的知識來理解本章接下來的內(nèi)容。
??以Unix系統(tǒng)的email box為例，典型的mbox文件格式是非常簡單的。一個mbox郵箱中的所有郵件都串行在一起，彼此首尾相連。這種格式對于讀取和分析郵件信息非常友好，同時投遞郵件也很容易，只要在文件末尾附加新的郵件內(nèi)容即可。
??但如果兩個進程在同一時刻對同一個郵箱投遞郵件，會發(fā)生什么情況?顯然，郵箱的數(shù)據(jù)會被破壞，兩封郵件的內(nèi)容會交叉地附加在郵箱文件的末尾。設(shè)計良好的郵箱投遞系統(tǒng)會通過鎖(lock)來防止數(shù)據(jù)損壞。如果客戶試圖投遞郵件，而郵箱已經(jīng)被其他客戶鎖住，那就必須等待，直到鎖釋放才能進行投遞。

1.2.1 讀寫鎖

從郵箱中讀取數(shù)據(jù)沒有這樣的麻煩，即使同一時刻多個用戶并發(fā)讀取也不會有什么問題。因為讀取不會修改數(shù)據(jù)，所以不會出錯。但如果某個客戶正在讀取郵箱，同時另外一個用戶試圖刪除編號為25的郵件，會產(chǎn)生什么結(jié)果?結(jié)論是不確定，讀的客戶可能會報錯退出，也可能讀取到不一致的郵箱數(shù)據(jù)。所以，為安全起見，即使是讀取郵箱也需要特別注意。
??如果把上述的郵箱當(dāng)成數(shù)據(jù)庫中的一張表，把郵件當(dāng)成表中的一行記錄，就很容易看出，同樣的問題依然存在。從很多方面來說，郵箱就是一張簡單的數(shù)據(jù)庫表。修改數(shù)據(jù)庫表中的記錄，和刪除或者修改郵箱中的郵件信息，十分類似。
??解決這類經(jīng)典問題的方法就是并發(fā)控制，其實非常簡單。在處理并發(fā)讀或者寫時，可以通過實現(xiàn)一個由兩種類型的鎖組成的鎖系統(tǒng)來解決問題。這兩種類型的鎖通常被稱為共享鎖(shared lock)和排他鎖(exclusive lock)，也叫讀鎖(read lock)和寫鎖(writelock )。（筆者注：以后跟別人說，你知道shared lock嗎？）
??這里先不討論鎖的具體實現(xiàn)，描述一下鎖的概念如下：讀鎖是共享的，或者說是相互不阻塞的。多個客戶在同一時刻可以同時讀取同一個資源，而互不干擾。寫鎖則是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，這是出于安全策略的考慮，只有這樣，才能確保在給定的時間里，只有一個用戶能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源。
??在實際的數(shù)據(jù)庫系統(tǒng)中，每時每刻都在發(fā)生鎖定，當(dāng)某個用戶在修改某一部分?jǐn)?shù)據(jù)時，MySQL會通過鎖定防止其他用戶讀取同一數(shù)據(jù)。大多數(shù)時候，MySQL鎖的內(nèi)部管理都是透明的。

1.2.2 鎖粒度

一種提高共享資源并發(fā)性的方式就是讓鎖定對象更有選擇性。盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)，而不是所有的資源。更理想的方式是，只對會修改的數(shù)據(jù)片進行精確的鎖定。任何時候，在給定的資源上，鎖定的數(shù)據(jù)量越少，則系統(tǒng)的并發(fā)程度越高，只要相互之間不發(fā)生沖突即可。（筆者注：難道是表鎖和行鎖？）
??問題是加鎖也需要消耗資源。鎖的各種操作，包括獲得鎖、檢查鎖是否已經(jīng)解除、釋放鎖等，都會增加系統(tǒng)的開銷。如果系統(tǒng)花費大量的時間來管理鎖，而不是存取數(shù)據(jù)，那么系統(tǒng)的性能可能會因此受到影響。（筆者注：類似java線程中的“切換上下文”資源損耗？）
??所謂的鎖策略，就是在鎖的開銷和數(shù)據(jù)的安全性之間尋求平衡，這種平衡當(dāng)然也會影響到性能。大多數(shù)商業(yè)數(shù)據(jù)庫系統(tǒng)沒有提供更多的選擇，一般都是在表上施加行級鎖( row-level lock)，并以各種復(fù)雜的方式來實現(xiàn)，以便在鎖比較多的情況下盡可能地提供更好的性能。
??而MySQL則提供了多種選擇。每種MySQL存儲引擎都可以實現(xiàn)自己的鎖策略和鎖粒度。在存儲引擎的設(shè)計中，鎖管理是個非常重要的決定。將鎖粒度固定在某個級別，可以為某些特定的應(yīng)用場景提供更好的性能，但同時卻會失去對另外一些應(yīng)用場景的良好支持。好在MySQL支持多個存儲引擎的架構(gòu)，所以不需要單一的通用解決方案。下面將介紹兩種最重要的鎖策略。（筆者注：我剛才仔細查看了下我們公司，好像只有InnoDB）

表鎖（table lock）

表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略。表鎖非常類似于前文描述的郵箱加鎖機制:它會鎖定整張表。一個用戶在對表進行寫操作(插人、刪除、更新等)前，需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作。只有沒有寫鎖時，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的。
????在特定的場景中，表鎖也可能有良好的性能。例如，READ LOCAL表鎖支持某些類型的并發(fā)寫操作。另外，寫鎖也比讀鎖有更高的優(yōu)先級，因此一個寫鎖請求可能會被插入到讀鎖隊列的前面（寫鎖可以插入到鎖隊列中讀鎖的前面，反之讀鎖則不能插人到寫鎖的前面）。
????盡管存儲引擎可以管理自己的鎖，MySQL本身還是會使用各種有效的表鎖來實現(xiàn)不同的目的。例如，服務(wù)器會為諸如ALTER TABLE之類的語句使用表鎖，而忽略存儲引擎的鎖機制。（筆者注：誠不欺我）

行鎖（row lock）

行級鎖可以最大程度地支持并發(fā)處理（同時也帶來了最大的鎖開銷）。眾所周知，在InnoDB和XtraDB，以及其他一些存儲引擎中實現(xiàn)了行級鎖。行級鎖只在存儲引擎層實現(xiàn)，而MySQL服務(wù)器層（請回顧前文的邏輯架構(gòu)圖）沒有實現(xiàn)。服務(wù)器層完全不了解存儲引擎中的鎖實現(xiàn)。在本章的后續(xù)內(nèi)容以及全書中，所有的存儲引擎都以自己的方式顯現(xiàn)了鎖機制。

1.3 事務(wù)

在理解事務(wù)的概念之前，接觸數(shù)據(jù)庫系統(tǒng)的其他高級特性還言之過早。事務(wù)就是一組原子性的SQL查詢，或者說一個獨立的工作單元。如果數(shù)據(jù)庫引擎能夠成功地對數(shù)據(jù)庫應(yīng)用該組查詢的全部語句，那么就執(zhí)行該組查詢。如果其中有任何一條語句因為崩潰或其他原因無法執(zhí)行，那么所有的語句都不會執(zhí)行。也就是說，事務(wù)內(nèi)的語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。本節(jié)的內(nèi)容并非專屬于MySQL，如果讀者已經(jīng)熟悉了事務(wù)的ACID的概念，可以直接跳轉(zhuǎn)到1.3.4節(jié)。
??銀行應(yīng)用是解釋事務(wù)必要性的一個經(jīng)典例子。假設(shè)一個銀行的數(shù)據(jù)庫有兩張表:支票(checking)表和儲蓄(savings)表?，F(xiàn)在要從用戶Jane的支票賬戶轉(zhuǎn)移200美元到她的儲蓄賬戶，那么需要至少三個步驟:
??1. 檢查支票賬戶的余額高干200美元。
??2. 從支票賬戶余額中減去200美元。
??3. 在儲蓄賬戶余額中增加200美元。
??上述三個步驟的操作必須打包在一個事務(wù)中，任何一個步驟失敗，則必須回滾所有的步驟。
??可以用START TRANSACTION語句開始一個事務(wù)，然后要么使用COMMIT提交事務(wù)將修改的數(shù)據(jù)持久保留，要么使用ROLLBACK撤銷所有的修改。事務(wù)SQL的樣本如下:

    START TRANSACTION;
    UPDATE checking SET balance = balance - 200.00 WHERE id = 9001;
    UPDATE savings SET balance = balance + 200.00 WHERE id = 9002;
    COMMIT;

單純的事務(wù)概念并不是故事的全部。試想一下，如果執(zhí)行到第四條語句時服務(wù)器崩潰了，會發(fā)生什么?天知道，用戶可能會損失200美元。再假如，在執(zhí)行到第三條語句和第四條語句之間時，另外一個進程要刪除支票賬戶的所有余額，那么結(jié)果可能就是銀行在不知道這個邏輯的情況下白白給了Jane 200美元。
??除非系統(tǒng)通過嚴(yán)格的ACID測試，否則空談事務(wù)的概念是不夠的。ACID表示原子性( atomicity )、一致性(consistency )、隔離性(isolation)和持久性(durability)。一個運行良好的事務(wù)處理系統(tǒng)，必須具備這些標(biāo)準(zhǔn)特征。

原子性(atomicity)

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

一致性(consistency )

數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另外一個一致性的狀態(tài)。在前面的例子中， 一致性確保了，即使在執(zhí)行第三、四條語句之間時系統(tǒng)崩潰，支票賬戶中也不會損失200美元，因為事務(wù)最終沒有提交，所以事務(wù)中所做的修改也不會保存到數(shù)據(jù)庫中。

隔離性(isolation)

通常來說，一個事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不可見的。在前面的例子中，當(dāng)執(zhí)行完第三條語句、第四條語句還未開始時，此時有另外一個賬戶匯總程序開始運行，則其看到的支票賬戶的余額并沒有被減去200美元。后面我們討論隔離級別(Isolation level)的時候，會發(fā)現(xiàn)為什么我們要說“通常來說”是不可見的。

持久性(durability )

一旦事務(wù)提交，則其所做的修改就會永久保存到數(shù)據(jù)庫中。此時即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會丟失。持久性是個有點模糊的概念，因為實際上持久性也分很多不同的級別。有些持久性策略能夠提供非常強的安全保障，而有些則未必。而且不可能有能做到100%的持久性保證的策略(如果數(shù)據(jù)庫本身就能做到真正的持久性，那么備份又怎么能增加持久性呢?)。在后面的一些章節(jié)中，我們會繼續(xù)討論MySQL中持久性的真正含義。
??事務(wù)的ACID特性可以確保銀行不會弄丟你的錢。而在應(yīng)用邏輯中，要實現(xiàn)這一點非常難，甚至可以說是不可能完成的任務(wù)。一個兼容ACID的數(shù)據(jù)庫系統(tǒng)，需要做很多復(fù)雜但可能用戶并沒有覺察到的工作，才能確保ACID的實現(xiàn)。
??就像鎖粒度的升級會增加系統(tǒng)開銷一樣，這種事務(wù)處理過程中額外的安全性，也會需要數(shù)據(jù)庫系統(tǒng)做更多的額外工作。一個實現(xiàn)了ACID的數(shù)據(jù)庫，相比沒有實現(xiàn)ACID的數(shù)據(jù)庫，通常會需要更強的CPU處理能力、更大的內(nèi)存和更多的磁盤空間。正如本章不斷重復(fù)的，這也正是MySQL的存儲引擎架構(gòu)可以發(fā)揮優(yōu)勢的地方。用戶可以根據(jù)業(yè)務(wù)是否需要事務(wù)處理，來選擇合適的存儲引擎。對于一些不需要事務(wù)的查詢類應(yīng)用，選擇一個非事務(wù)型的存儲引擎，可以獲得更高的性能。即使存儲引擎不支持事務(wù)，也可以通過LOCK TABLES語句為應(yīng)用提供一定程度的保護，這些選擇用戶都可以自主決定。

1.3.1 隔離級別

隔離性其實比想象的要復(fù)雜。在SQL標(biāo)準(zhǔn)中定義了四種隔離級別，每一種級別都規(guī)定了一個事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的，哪些是不可見的。較低級別的隔離通常可以執(zhí)行更高的并發(fā)，系統(tǒng)的開銷也更低。
??每種存儲引擎實現(xiàn)的隔離級別不盡相同。如果熟悉其他的數(shù)據(jù)庫產(chǎn)品，可能會發(fā)現(xiàn)某些特性和你期望的會有些不一樣(但本節(jié)不打算討論更詳細的內(nèi)容)。讀者可以根據(jù)所選擇的存儲引擎，查閱相關(guān)的手冊。
??下面簡單地介紹一下四種隔離級別。

1. READ UNCOMMITTED(未提交讀)

在READ UNCOMMITTED級別，事務(wù)中的修改，即使沒有提交，對其他事務(wù)也都是可見的。事務(wù)可以讀取未提交的數(shù)據(jù)，這也被稱為臟讀(Dirty Read)。這個級別會導(dǎo)致很多問題，從性能上來說READ UNCOMMITTED不會比其他的級別好太多，但卻缺乏其他級別的很多好處，除非真的有非常必要的理由，在實際應(yīng)用中一般很少使用。

2. READ COMMITTED(提交讀)

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

3. REPEATABLE READ(可重復(fù)讀)

REPEATABLE READ解決了臟讀的問題。該級別保證了在同一個事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。但是理論上，可重復(fù)讀隔離級別還是無法解決另外一個幻讀(Phantom Read)的問題。所謂幻讀，指的是當(dāng)某個事務(wù)在讀取某個范圍內(nèi)的記錄時，另外一個事務(wù)又在該范圍內(nèi)插人了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時，會產(chǎn)生幻行(Phantom Row)a InnoDB和XtraDB存儲引擎通過多版本并發(fā)控制(MVCC, Multiversion Concurrency Control)解決T幻讀的Ip7題。本章稍后會做進一步的討論。

可重復(fù)讀是MySQL的默認事務(wù)隔離級別。

4. SERIALIZABLE(可串行化)

SERIALIZABLE是最高的隔離級別。它通過強制事務(wù)串行執(zhí)行，避免了前面說的幻讀 的問題。簡單來說，SERIALIZABLE會在讀取的每一行數(shù)據(jù)上都加鎖，所以可能導(dǎo)致大量的超時和鎖爭用的問題。實際應(yīng)用中也很少用到這個隔離級別，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況下，才考慮采用該級別。

image.png

1.3.2 死鎖

死鎖是指兩個或者多個事務(wù)在同一資源上相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。當(dāng)多個事務(wù)試圖以不同的順序鎖定資源時，就可能會產(chǎn)生死鎖。多個事務(wù)同時鎖定同一個資源時，也會產(chǎn)生死鎖。例如，設(shè)想下面兩個事務(wù)同時處理StockPrice表:
??事務(wù)1：

    START TRANSACTION;
    UPDATE P SET close = 45.50 WHERE id = 4;
    UPDATE P SET close = 19.80 WHERE id = 3;

事務(wù)2：

    START TRANSACTION;
    UPDATE P SET close = 20.50 WHERE id = 4;
    UPDATE P SET close = 15.80 WHERE id = 3;

如果湊巧，兩個事務(wù)都執(zhí)行了第一條UPDATE語句，更新了一行數(shù)據(jù)，同時也鎖定了該行數(shù)據(jù)，接著每個事務(wù)都嘗試去執(zhí)行第二條UPDATE語句，卻發(fā)現(xiàn)該行已經(jīng)被對方鎖定，然后兩個事務(wù)都等待對方釋放鎖，同時又持有對方需要的鎖，則陷入死循環(huán)。除非有外部因素介入才可能解除死鎖。
??為了解決這種問題，數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時機制。越復(fù)雜的系統(tǒng)，比如InnoDB存儲引擎，越能檢測到死鎖的循環(huán)依賴，并立即返回一個錯誤。這種解決方式很有效，否則死鎖會導(dǎo)致出現(xiàn)非常慢的查詢。還有一種解決方式，就是當(dāng)查詢的時間達到鎖等待超時的設(shè)定后放棄鎖請求，這種方式通常來說不太好。InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務(wù)進行回滾(這是相對比較簡單的死鎖回滾算法)。（筆者注：InnoBD好強）
??鎖的行為和順序是和存儲引擎相關(guān)的。以同樣的順序執(zhí)行語句，有些存儲引擎會產(chǎn)生死鎖，有些則不會。死鎖的產(chǎn)生有雙重原因:有些是因為真正的數(shù)據(jù)沖突，這種情況通常很難避免，但有些則完全是由于存儲引擎的實現(xiàn)方式導(dǎo)致的。
??死鎖發(fā)生以后，只有部分或者完全回滾其中一個事務(wù)，才能打破死鎖。對于事務(wù)型的系統(tǒng)，這是無法避免的，所以應(yīng)用程序在設(shè)計時必須考慮如何處理死鎖。大多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。

1.3.3 事務(wù)日志

事務(wù)日志可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲引擎在修改表的數(shù)據(jù)時只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志采用的是追加的方式，因此寫日志的操作是磁盤上一小塊區(qū)域內(nèi)的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務(wù)日志的方式相對來說要快得多。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢地刷回到磁盤。目前大多數(shù)存儲引擎都是這樣實現(xiàn)的，我們通常稱之為預(yù)寫式日志(Write-Ahead Logging )，修改數(shù)據(jù)需要寫兩次磁盤。如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒有寫回磁盤，此時系統(tǒng)崩潰，存儲引擎在重啟時能夠自動恢復(fù)這部分修改的數(shù)據(jù)。具體的恢復(fù)方式則視存儲引擎而定。（筆者注：主從庫雖然好，但是如果考慮到服務(wù)器價格，綜合考慮日志很好，但是公司沒怎么用，可能公司大牛不行）

1.3.4 MySQL中的事務(wù)

MySQL提供了兩種事務(wù)型的存儲引擎:InnoDB和NDB Cluster。另外還有一些第三方存儲引擎也支持事務(wù)，比較知名的包括XtraDB和PBXT。后面將詳細討論它們各自的一些特點。

自動提交（AUTOCOMMIT）

MySQL默認采用自動提交(AUTOCOMMIT)模式。也就是說，如果不是顯式地開始一個事務(wù)，則每個查詢都被當(dāng)作一個事務(wù)執(zhí)行提交操作。在當(dāng)前連接中，可以通過設(shè)置AUTOCOMMIT變量來啟用或者禁用自動提交模式：

autocommit

??1或者ON表示啟用，U或者OFF表示禁用。當(dāng)AUTOCOMMIT=0時，所有的查詢都是在一個
事務(wù)中，直到顯式地執(zhí)行COMM工下提交或者ROLLBACK回滾，該事務(wù)結(jié)束，同時又開始了另一個新事務(wù)。修改AUTOCOMMIT對非事務(wù)型的表，比如MyISAM或者內(nèi)存表，不會有任何影響。對這類表來說，沒有COMMIT或者ROLLBACK的概念，也可以說是相當(dāng)于一直處于AUTOCOMMIT啟用的模式。
??另外還有一些命令，在執(zhí)行之前會強制執(zhí)行COMM工T提交當(dāng)前的活動事務(wù)。典型的例子，在數(shù)據(jù)定義語言(DDL)中，如果是會導(dǎo)致大量數(shù)據(jù)改變的操作，比如ALTER TABLE,就是如此。另外還有LOCK TABLES等其他語句也會導(dǎo)致同樣的結(jié)果。如果有需要，請檢查對應(yīng)版本的官方文檔來確認所有可能導(dǎo)致自動提交的語句列表。（筆者注：不止是行鎖，還自動提交。）
??MySQL可以通過執(zhí)行SET TRANSACTION ISOLATION LEVEL命令來設(shè)置隔離級別。新的隔離級別會在下一個事務(wù)開始的時候生效?？梢栽谂渲梦募性O(shè)置整個數(shù)據(jù)庫的隔離級別，也可以只改變當(dāng)前會話的隔離級別：

transaction isolation.png

在事務(wù)中混合使用存儲引擎

MySQL服務(wù)器層不管理事務(wù)，事務(wù)是由下層的存儲引擎實現(xiàn)的。所以在同一個事務(wù)中，使用多種存儲引擎是不可靠的。
??如果在事務(wù)中混合使用了事務(wù)型和非事務(wù)型的表(例如InnoDB和MyISAM表)，在正常提交的情況下不會有什么問題。
??但如果該事務(wù)需要回滾，非事務(wù)型的表上的變更就無法撤銷，這會導(dǎo)致數(shù)據(jù)庫處于不一致的狀態(tài)，這種情況很難修復(fù)，事務(wù)的最終結(jié)果將無法確定。所以，為每張表選擇合適的存儲引擎非常重要。
??在非事務(wù)型的表上執(zhí)行事務(wù)相關(guān)操作的時候，MySQL通常不會發(fā)出提醒，也不會報錯。有時候只有回滾的時候才會發(fā)出一個警告:“某些非事務(wù)型的表上的變更不能被回滾”。但大多數(shù)情況下，對非事務(wù)型表的操作都不會有提示。

隱式和顯式鎖定

InnoDB采用的是兩階段鎖定協(xié)議(two-phase locking protocol )。在事務(wù)執(zhí)行過程中，隨時都可以執(zhí)行鎖定，鎖只有在執(zhí)行COMMIT或者ROLLBACK的時候才會釋放，并且所有的鎖是在同一時刻被釋放。前面描述的鎖定都是隱式鎖定，InnoDB會根據(jù)隔離級別在需要的時候自動加鎖。
??另外，InnoDB也支持通過特定的語句進行顯式鎖定，這些語句不屬于SQL規(guī)范：
??SELECT ... LOCK IN SHARE MODE
??SELECT ... FOR UPDATE
??MySQL也支持LOCK TABLES和UNLOCK TABLES語句，這是在服務(wù)器層實現(xiàn)的，和存儲引擎無關(guān)。它們有自己的用途，但并不能替代事務(wù)處理。如果應(yīng)用需要用到事務(wù)，還是應(yīng)該選擇事務(wù)型存儲引擎。
??經(jīng)?？梢园l(fā)現(xiàn)，應(yīng)用已經(jīng)將表從MyISAM轉(zhuǎn)換到InnoDB，但還是顯式地使用LOCK TABLES語句。這不但沒有必要，還會嚴(yán)重影響性能，實際上InnoDB的行級鎖工作得更好。（筆者注：沒見過這個語句）

LOCK TABLES和事務(wù)之間相互影響的話，情況會變得非常復(fù)雜，在某些MySQL版本中甚至?xí)a(chǎn)生無法預(yù)料的結(jié)果。因此，本書建議，除了事務(wù)中禁用了AU下。COMMIT,可以使用LOCK TABLES之外，其他任何時候都不要顯式地執(zhí)行LOCK TABLES，不管使用的是什么存儲引擎。

1.4 多版本并發(fā)控制

MySQL的大多數(shù)事務(wù)型存儲引擎實現(xiàn)的都不是簡單的行級鎖?；谔嵘l(fā)性能的考慮，它們一般都同時實現(xiàn)了多版本并發(fā)控制(MVCC)。不僅是MySQL，包括Oracle、PostgreSQL等其他數(shù)據(jù)庫系統(tǒng)也都實現(xiàn)了MVCC，但各自的實現(xiàn)機制不盡相同，因為MVCC沒有一個統(tǒng)一的實現(xiàn)標(biāo)準(zhǔn)。
??可以認為MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現(xiàn)機制有所不同，但大都實現(xiàn)了非阻塞的讀操作，寫操作也只鎖定必要的行。
??MVCC的實現(xiàn)，是通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)的。也就是說，不管需要執(zhí)行多長時間，每個事務(wù)看到的數(shù)據(jù)都是一致的。根據(jù)事務(wù)開始的時間不同，每個事務(wù)對同一張表，同一時刻看到的數(shù)據(jù)可能是不一樣的。如果之前沒有這方面的概念，這句話聽起來就有點迷惑。熟悉了以后會發(fā)現(xiàn)，這句話其實還是很容易理解的。
??前面說到不同存儲引擎的MVCC實現(xiàn)是不同的，典型的有樂觀(optimistic)并發(fā)控制和悲觀(pessimistic)并發(fā)控制。下面我們通過InnoDB的簡化版行為來說明MVCC是如何工作的。
??InnoDB的MVCC，是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。這兩個列，一個保存了行的創(chuàng)建時間，一個保存行的過期時間(或刪除時間)。當(dāng)然存儲的并不是實際的時間值，而是系統(tǒng)版本號(system version number)。每開始一個新的事務(wù)，系統(tǒng)版本號都會自動遞增。事務(wù)開始時刻的系統(tǒng)版本號會作為事務(wù)的版本號，用來和查詢到的每行記錄的版本號進行比較。下面看一下在REPEATABLE READ隔離級別下， MVCC具體是如何操作的。

SELECT

InnoDB會根據(jù)以下兩個條件檢查每行記錄:
??a. InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(也就是，行的系統(tǒng)版本號小于或等于事務(wù)的系統(tǒng)版本號)，這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過的。
??b.行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號。這可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除。只有符合上述兩個條件的記錄，才能返回作為查詢結(jié)果。

INSERT

InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號作為行版本號。

DELETE

InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號作為行刪除標(biāo)識。（筆者注：不是完全刪除嗎？）

UPDATE

InnoDB為插入一行新記錄，保存當(dāng)前系統(tǒng)版本號作為行版本號，同時保存當(dāng)前系統(tǒng)版本號到原來的行作為行刪除標(biāo)識。（筆者注：原來行？）
??保存這兩個額外系統(tǒng)版本號，使大多數(shù)讀操作都可以不用加鎖。這樣設(shè)計使得讀數(shù)據(jù)操作很簡單，性能很好，并且也能保證只會讀取到符合標(biāo)準(zhǔn)的行。不足之處是每行記錄都需要額外的存儲空間，需要做更多的行檢查工作，以及一些額外的維護工作。
??MVCC只在REPEATABLE READ和READ CONONITTED兩個隔離級別下工作。其他兩個隔離級別都和MVCC不兼容，因為READ UNCONP}IITTED總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行。而SERIALIZABLE則會對所有讀取的行都加鎖。

1.5 MySQL的存儲引擎

本節(jié)只是概要地描述MySQL的存儲引擎，而不會涉及太多細節(jié)。因為關(guān)于存儲引擎的討論及其相關(guān)特性將會貫穿全書，而且本書也不是存儲引擎的完全指南，所以有必要閱讀相關(guān)存儲引擎的官方文檔。
??在文件系統(tǒng)中，MySQL將每個數(shù)據(jù)庫（也可以稱之為schema）保存為數(shù)據(jù)目錄下的一個子目錄。創(chuàng)建表時，MySQL會在數(shù)據(jù)庫子目錄下創(chuàng)建一個和表同名的.. frm文件保存表的定義。例如創(chuàng)建一個名為MyTabte的表，MySQL會在MyTable.frm文件中保存該表的定義。因為MySQL使用文件系統(tǒng)的目錄和文件來保存數(shù)據(jù)庫和表的定義，大小寫敏感性和具體的平臺密切相關(guān)。在Windows中，大小寫是不敏感的，而在類Unix中則是敏感的。不同的存儲引擎保存數(shù)據(jù)和索引的方式是不同的，但表的定義則是在MySQL服務(wù)層統(tǒng)一處理的。（筆者注：看到了frm文件，但是打開亂碼，不知道怎么打開？）
??可以使用SHOW TABLE STATUS命令(在MySQL 5.0以后的版本中，也可以查詢INFORMATION_ SCHEMA中對應(yīng)的表)顯示表的相關(guān)信息。例如，對于mysqt數(shù)據(jù)庫中的user表:

table status.png

Name

表名。

Engine

表的存儲引擎類型。在舊版本中，該列的名字叫Type，而不是Engineo

Row format

行的格式。對于MyISAM表，可選的值為Dynamic, Fixed或者Compressed,Dynamic的行長度是可變的，一般包含可變長度的字段，如VARCHAR或BLOBo Fixed的行長度則是固定的，只包含固定長度的列，如CHAR和INTEGER。Compressed的行則只在壓縮表中存在，請參考第19頁“MyISAM壓縮表”一節(jié)。

Rows

表中的行數(shù)。對于MyISAM和其他一些存儲引擎，該值是精確的，但對于InnoDB ,該值是估計值。

Avg_row length

平均每行包含的字節(jié)數(shù)。

Data_tength

表數(shù)據(jù)的大小(以字節(jié)為單位)。

Max_data_tength

表數(shù)據(jù)的最大容量，該值和存儲引擎有關(guān)。

Index_tength

索引的大小(以字節(jié)為單位)。

Data free

對于MyISAM表，表示已分配但目前沒有使用的空間。這部分空間包括了之前刪除的行，以及后續(xù)可以被INSERT利用到的空間。

Auto increment

下一個AUTO INCREMENT的值。

Create time

表的創(chuàng)建時間。

Update time

表數(shù)據(jù)的最后修改時間。

Check time

使用CKECK TABLE命令或者myisamchk工具最后一次檢查表的時間。

Collation

表的默認字符集和字符列排序規(guī)則。

Checksum

如果啟用，保存的是整個表的實時校驗和。

Create_options

創(chuàng)建表時指定的其他選項。

Comment

該列包含了一些其他的額外信息。對于MyISAM表，保存的是表在創(chuàng)建時帶的注釋。對于InnoDB表，則保存的是InnoDB表空間的剩余空間信息。如果是一個視圖，則該列包含“VIEW”的文本字樣。

1.5.1 InnoDB存儲引擎

InnoDB是MySQL的默認事務(wù)型引擎，也是最重要、使用最廣泛的存儲引擎。它被設(shè)計用來處理大量的短期(short-lived)事務(wù)，短期事務(wù)大部分情況是正常提交的，很少會被回滾。InnoDB的性能和自動崩潰恢復(fù)特性，使得它在非事務(wù)型存儲的需求中也很流行。除非有非常特別的原因需要使用其他的存儲引擎，否則應(yīng)該優(yōu)先考慮InnoDB引擎。如果要學(xué)習(xí)存儲引擎，InnoDB也是一個非常好的值得花最多的時間去深入學(xué)習(xí)的對象，收益肯定比將時間平均花在每個存儲引擎的學(xué)習(xí)上要高得多。

InnoDB的歷史

InnoDB有著復(fù)雜的發(fā)布歷史，了解一下這段歷史對于理解InnoDB很有幫助。2008年，發(fā)布了所謂的InnoDB plugin，適用于MySQL 5.1版本，但這是Oracle創(chuàng)建的下一代InnoDB引擎，其擁有者是InnoDB而不是MySQL。這基于很多原因，這些原因如果要一一道來，恐怕得喝掉好幾桶啤酒。MySQL默認還是選擇了集成舊的InnoDB引擎。當(dāng)然用戶可以自行選擇使用新的性能更好、擴展性更佳的InnoDB plugin來覆蓋舊的版本。直到最后，在Oracle收購TSun公司后發(fā)布的MySQL 5.5中才徹底使用InnoDB plugin替代了舊版本的InnoDB(是的，這也意味著InnoDB plugin已經(jīng)是原生編譯了，而不是編譯成一個插件，但名字已經(jīng)約定俗成很難更改)。
??這個現(xiàn)代的InnoDB版本，也就是MySQL 5.1中所謂的InnoDB plugin，支持一些新特性，諸如利用排序創(chuàng)建索引(building index by sorting)、刪除或者增加索引時不需要復(fù)制全表數(shù)據(jù)、新的支持壓縮的存儲格式、新的大型列值如BLOB的存儲方式，以及文件格式管理等。很多用戶在MySQL 5.1中沒有使用nnoDB plugin,或許是因為他們沒有注意到有這個區(qū)別。所以如果你使用的是MySQL 5.1，一定要使用InnoDB plugin，真的比舊版本的InnoDB要好很多。
??InnoDB是一個很重要的存儲引擎，很多個人和公司都對其貢獻代碼，而不僅僅是Oracle公司的開發(fā)團隊。一些重要的貢獻者包括Google, Yasufumi Kinoshita, Percona,Facebook等，他們的一些改進被直接移植到官方版本，也有一些由InnoDB團隊重新實現(xiàn)。在過去的幾年間，InnoDB的改進速度大大加快，主要的改進集中在可測量性、可擴展性、可配置化、性能各種新特性和對Window:的支持等方面。

InnoDB概覽

InnoDB的數(shù)據(jù)存儲在表空間(tablespace)中，表空間是由InnoDB管理的一個黑盒子，由一系列的數(shù)據(jù)文件組成。在MySQL 4.1以后的版本中，InnoDB可以將每個表的數(shù)據(jù)和索引存放在單獨的文件中。InnoDB也可以使用裸設(shè)備作為表空間的存儲介質(zhì)，但現(xiàn)代的文件系統(tǒng)使得裸設(shè)備不再是必要的選擇。
??InnoDB采用MVCC來支持高并發(fā)，并且實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別。其默認級別是REPEATABLE READ(可重復(fù)讀)，并且通過間隙鎖(next-key locking)策略防止幻讀的出現(xiàn)。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。
??InnoDB表是基于聚簇索引建立的，我們會在后面的章節(jié)詳細討論聚簇索引。InnoDB的索引結(jié)構(gòu)和MySQL的其他存儲引擎有很大的不同，聚簇索引對主鍵查詢有很高的性能。不過它的二級索引(secondary index，非主鍵索引)中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會很大。因此，若表上的索引較多的話，主鍵應(yīng)當(dāng)盡可能的小。InnoDB的存儲格式是平臺獨立的，也就是說可以將數(shù)據(jù)和索引文件從Intel平臺復(fù)制到PowerPC或者Sun SPARC平臺。
??InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時采用的可預(yù)測性預(yù)讀，能夠自動在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引(adaptive hash index)，以及能夠加速插入操作的插入緩沖區(qū)(insert buffer)等。本書后面將更詳細地討論這些內(nèi)容。
??InnoDB的行為是非常復(fù)雜的，不容易理解。如果使用了InnoDB引擎，筆者強烈建議閱讀官方手冊中的“InnoDB事務(wù)模型和鎖”一節(jié)。如果應(yīng)用程序基于InnoDB構(gòu)建，則事先了解一下InnoDB的MVCC架構(gòu)帶來的一些微妙和細節(jié)之處是非常有必要的。存儲引擎要為所有用戶甚至包括修改數(shù)據(jù)的用戶維持一致性的視圖，是非常復(fù)雜的工作。
??作為事務(wù)型的存儲引擎，InnoDB通過一些機制和工具支持真正的熱備份，Oracle提供的MySQL Enterprise Backup, Percona提供的開源的XtraBackup都可以做到這一點。MySQL的其他存儲引擎不支持熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味著停止讀取。

1.5.2 MyISAM存儲引擎

在MySQL 5.1及之前的版本，MyISAM是默認的存儲引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(shù)(GIS)等，但MyISAM不支持事務(wù)和行級鎖，而且有一個毫無疑問的缺陷就是崩潰后無法安全恢復(fù)。正是由于MyISAM引擎的緣故，即使MySQL支持事務(wù)已經(jīng)很長時間了，在很多人的概念中MySQL還是非事務(wù)型的數(shù)據(jù)庫。盡管MyISAM引擎不支持事務(wù)、不支持崩潰后的安全恢復(fù)，但它絕不是一無是處的。對于只讀的數(shù)據(jù)，或者表比較小、可以忍受修復(fù)(repair)操作，則依然可以繼續(xù)使用MyISAM(但請不要默認使用MyISAM，而是應(yīng)當(dāng)默認使用InnoDB ) （筆者注：不支持事務(wù)？基本不考慮了）

存儲

MyISAM會將表存儲在兩個文件中:數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴展名。MyISAM表可以包含動態(tài)或者靜態(tài)(長度固定)行。MySQL會根據(jù)表的定義來決定采用何種行格式。MyISAM表可以存儲的行記錄數(shù)，一般受限于可用的磁盤空間，或者操作系統(tǒng)中單個文件的最大尺寸。
??在MySQL 5.0中，MyISAM表如果是變長行，則默認配置只能處理256TB的數(shù)據(jù)，因為指向數(shù)據(jù)記錄的指針長度是6個字節(jié)。而在更早的版本中，指針長度默認是4字節(jié)，所以只能處理4GB的數(shù)據(jù)。而所有的MySQL版本都支持8字節(jié)的指針。要改變MyISAM表指針的長度(調(diào)高或者調(diào)低)，可以通過修改表的MAX_ROWS和AVG_ROW_LENGTH選項的值來實現(xiàn)，兩者相乘就是表可能達到的最大大小。修改這兩個參數(shù)會導(dǎo)致重建整個表和表的所有索引，這可能需要很長的時間才能完成。

MyISAM特性

作為MySQL最早的存儲引擎之一，MyISAM有一些已經(jīng)開發(fā)出來很多年的特性，可以滿足用戶的實際需求。

1.加鎖與并發(fā)

MyISAM對整張表加鎖，而不是針對行。讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排他鎖。但是在表有讀取查詢的同時，也可以往表中插入新的記錄(這被稱為并發(fā)插入，CONCURRENT INSERT ) 。

2.修復(fù)

對于MyISAM表，MySQL可以手工或者自動執(zhí)行檢查和修復(fù)操作，但這里說的修復(fù)和事務(wù)恢復(fù)以及崩潰恢復(fù)是不同的概念。執(zhí)行表的修復(fù)可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的?？梢酝ㄟ^CHECK TABLE mytable檢查表的錯誤，如果有錯誤可以通過執(zhí)行REPAIR TABLE mytable進行修復(fù)。另外，如果MySQL服務(wù)器已經(jīng)關(guān)閉，也可以通過myisamchk命令行工具進行檢查和修復(fù)操作。

3.索引特性

對于MyISAM表，即使是BLOB和TEXT等長字段，也可以基于其前500個字符創(chuàng)建索引。MyISAM也支持全文索引，這是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢。關(guān)于索引的更多信息請參考第5章。

4.延遲更新索引健(Delayed Key Write)

創(chuàng)建MyISAM表的時候，如果指定了DELAY KEY WRITE選項，在每次修改執(zhí)行完成時，不會立刻將修改的索引數(shù)據(jù)寫入磁盤，而是會寫到內(nèi)存中的鍵緩沖區(qū)(in-memory key buffer)，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時候才會將對應(yīng)的索引塊寫人到磁盤。這種方式可以極大地提升寫入性能，但是在數(shù)據(jù)庫或者主機崩潰時會造成索引損壞，需要執(zhí)行修復(fù)操作。延遲更新索引鍵的特性，可以在全局設(shè)置，也可以為單個表設(shè)置。

MyISAM壓縮表

如果表在創(chuàng)建并導(dǎo)入數(shù)據(jù)以后，不會再進行修改操作，那么這樣的表或許適合采用MyISAM壓縮表。
??可以使用myisampack對MyISAM表進行壓縮(也叫打包pack)。壓縮表是不能進行修改的(除非先將表解除壓縮，修改數(shù)據(jù)，然后再次壓縮)。壓縮表可以極大地減少磁盤空間占用，因此也可以減少磁盤I/O，從而提升查詢性能。壓縮表也支持索引，但索引也是只讀的。
以現(xiàn)在的硬件能力，對大多數(shù)應(yīng)用場景，讀取壓縮表數(shù)據(jù)時的解壓帶來的開銷影響并不大，而減少I/O帶來的好處則要大得多。壓縮時表中的記錄是獨立壓縮的，所以讀取單行的時候不需要去解壓整個表(甚至也不解壓行所在的整個頁面)。

MyISAM性能

MyISAM引擎設(shè)計簡單，數(shù)據(jù)以緊密格式存儲，所以在某些場景下的性能很好。MyISAM有一些服務(wù)器級別的性能擴展限制，比如對索引鍵緩沖區(qū)(key cache)的Mutex鎖，MariaDB基于段(segment )的索引鍵緩沖區(qū)機制來避免該問題。但MyISAM最典型的性能問題還是表鎖的問題，如果你發(fā)現(xiàn)所有的查詢都長期處于“Locked"狀態(tài)，那么毫無疑問表鎖就是罪魁禍?zhǔn)住?/p>

1.5.3 MySQL內(nèi)建的其他存儲引擎

1.5.4 第三方存儲引擎

1.5.5 選擇合適的引擎

這么多存儲引擎，我們怎么選擇?大部分情況下，InnoDB都是正確的選擇，所以O(shè)racle在MySQL 5.5版本時終于將InnoDB作為默認的存儲引擎了。對于如何選擇存儲引擎，可以簡單地歸納為一句話:“除非需要用到某些InnoDB不具備的特性，并且沒有其他辦法可以替代，否則都應(yīng)該優(yōu)先選擇InnoDB引擎”。例如，如果要用到全文索引，建議優(yōu)先考慮InnoDB加上Sphinx的組合，而不是使用支持全文素引的MyISAM。當(dāng)然，如果不需要用到InnoDB的特性，同時其他引擎的特性能夠更好地滿足需求，也可以考慮一下其他存儲引擎。舉個例子，如果不在乎可擴展能力和并發(fā)能力，也不在乎崩潰后的數(shù)據(jù)丟失問題，卻對InnoDB的空間占用過多比較敏感，這種場合下選擇MyISAM就比較合適。

1.5.6 轉(zhuǎn)換表的引擎

??ALTER TABLE

Change Engine

??上述語法可以適用任何存儲引擎。但有一個問題:需要執(zhí)行很長時間。MySQL會按行將數(shù)據(jù)從原表復(fù)制到一張新的表中，在復(fù)制期間可能會消耗系統(tǒng)所有的I/O能力，同時原表上會加上讀鎖。所以，在繁忙的表上執(zhí)行此操作要特別小心。一個替代方案是采用接下來將討論的導(dǎo)出與導(dǎo)入的方法，手工進行表的復(fù)制。如果轉(zhuǎn)換表的存儲引擎，將會失去和原引擎相關(guān)的所有特性。例如，如果將一張InnoDB表轉(zhuǎn)換為MyISAM，然后再轉(zhuǎn)換回InnoDB，原InnoDB表上所有的外鍵將丟失。

??導(dǎo)出與導(dǎo)入

??為了更好地控制轉(zhuǎn)換的過程，可以使用mysqldump工具將數(shù)據(jù)導(dǎo)出到文件，然后修改文件中CREATE TABLE語句的存儲引擎選項，注意同時修改表名，因為同一個數(shù)據(jù)庫中不能存在相同的表名，即使它們使用的是不同的存儲引擎。同時要注意mysqldump默認會自動在CREATE TABLE語句前加上。ROP TABLE語句，不注意這一點可能會導(dǎo)致數(shù)據(jù)丟失。

??創(chuàng)建與查詢(CREATE和SELECT)

??第三種轉(zhuǎn)換的技術(shù)綜合了第一種方法的高效和第二種方法的安全。不需要導(dǎo)出整個表的數(shù)據(jù)，而是先創(chuàng)建一個新的存儲引擎的表，然后利用INSERT···SELECT語法來導(dǎo)數(shù)據(jù)：

like

??數(shù)據(jù)量不大的話，這樣做工作得很好。如果數(shù)據(jù)量很大，則可以考慮做分批處理，每一段數(shù)據(jù)執(zhí)行事務(wù)提交操作，以避免大事務(wù)產(chǎn)生過多的重復(fù)運行以下語句(最小值x和最大值y進行相應(yīng)的替換)undo。假設(shè)有主鍵字段針對id,將數(shù)據(jù)導(dǎo)入到新表:

  START TRANSACTION;
  INSERT INTO innodb_table SELECT * FROM user WHERE id BETWEEN x AND y;
  COMMIT;

??這樣操作完成以后，新表是原表的一個全量復(fù)制，原表還在，如果需要可以刪除原表。如果有必要，可以在執(zhí)行的過程中對原表加鎖，以確保新表和原表的數(shù)據(jù)一致。Percona Toolkit提供了一個pt-online-schema-change的工具(基于Facebook的在線schema變更技術(shù))，可以比較簡單、方便地執(zhí)行上述過程，避免手工操作可能導(dǎo)致的失誤和煩瑣。

1.6 MySQL時間線（Timeline）

1.7 MySQL的開發(fā)模式

1.8 總結(jié)

??MySQL擁有分層的架構(gòu)。上層是服務(wù)器層的服務(wù)和查詢執(zhí)行引擎，下層則是存儲引擎。雖然有很多不同作用的插件API，但存儲引擎API還是最重要的。如果能理解MySQL在存儲引擎和服務(wù)層之間處理查詢時如何通過API來回交互，就能抓住MySQL的核心基礎(chǔ)架構(gòu)的精髓。
??MySQL最初基于ISAM構(gòu)建（后來被MyISAM取代），其后陸續(xù)添加了更多的存儲引擎和事務(wù)支持。MySQL有一些怪異的行為是由于歷史遺留導(dǎo)致的。例如，在執(zhí)行ALTER TABLE時，MySQL提交事務(wù)的方式是由于存儲引擎的架構(gòu)直接導(dǎo)致的，并且數(shù)據(jù)字典也保存在frm文件中（這并不是說InnoDB會導(dǎo)致ALTER變成非事務(wù)型的。對于InnoDB來說，所有的操作都是事務(wù)）。
??當(dāng)然，存儲引擎API的架構(gòu)也有一些缺點。有時候選擇多并非好事，而在MySQL 5.0和MySQL 5.1中有太多的存儲引擎可以選擇。InnoDB對于95%以上的用戶來說都是最佳選擇，所以其他的存儲引擎可能只是讓事情變得復(fù)雜難搞，當(dāng)然也不可否認某些情況下某些存儲引擎能更好地滿足需求。
??Oracle一開始收購了InnoDB，之后又收購了MySQL，在同一個屋檐下對于兩者都是有
利的。InnoDB和MySQL服務(wù)器之間可以更快地協(xié)同發(fā)展。MySQL依然基于GPL協(xié)議開放全部源代碼，社區(qū)和客戶都可以獲得堅固而穩(wěn)定的數(shù)據(jù)庫，MySQL正在變得越來越可擴展和有用。
??筆者注：三層邏輯架構(gòu)，讀寫鎖（shared lock，exclusive lock），表鎖行鎖，事務(wù)的ACID（atomicity、consistency、isolation、durabillity），四大隔離級別，InnoDB默認隔離級別，MVCC基本原理，命令：SHOW TABLE STATUS，以及轉(zhuǎn)換引擎的三種方式。