面試官：你說對MySQL事務(wù)很熟？那我問你10個問題
學(xué)習(xí)關(guān)系型數(shù)據(jù)庫MySQL是很好的切入點，大部分人學(xué)習(xí)和工作中用慣了CRUD，對面試官刨根問底的靈魂拷問你還能對答如流嗎？我們有必要了解一些更深層次的數(shù)據(jù)庫基礎(chǔ)原理。
整理了面試中，關(guān)于MySQL事務(wù)和存儲引擎10個FAQ（Frequently asked questions），你想知道的都在這里。
什么是事務(wù)？
事務(wù)就是「一組原子性的SQL查詢」，或者說一個獨立的工作單元。如果數(shù)據(jù)庫引擎能夠成功地對數(shù)據(jù)庫應(yīng)用該組查詢的全部語句，那么就執(zhí)行該組查詢。如果其中有任何一條語句因為崩潰或其他原因無法執(zhí)行，那么所有的語句都不會執(zhí)行。也就是說，事務(wù)內(nèi)的語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。
事務(wù)控制語法知道嗎？
- 1BEGIN 或 START TRANSACTION 顯式地開啟一個事務(wù)；
- 2COMMIT / COMMIT WORK二者是等價的。提交事務(wù)，并使已對數(shù)據(jù)庫進(jìn)行的所有修改成為永久性的；
- 3ROLLBACK / ROLLBACK WORK。回滾會結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改；
- 4SAVEPOINT identifier 在事務(wù)中創(chuàng)建一個保存點，一個事務(wù)中可以有多個 SAVEPOINT；
- 5RELEASE SAVEPOINT identifier 刪除一個事務(wù)的保存點；
- 6ROLLBACK TO identifier 把事務(wù)回滾到標(biāo)記點；
- 7SET TRANSACTION 用來設(shè)置事務(wù)的隔離級別。InnoDB 存儲引擎提供事務(wù)的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE
用通俗的語言說說你理解的事務(wù)
用銀行業(yè)務(wù)舉個栗子，用戶lemon有兩銀行卡，一張是招商銀行CMBC的工資卡，另一張是工商銀行ICBC的儲蓄卡，每月10號發(fā)工資都要把招行卡的100萬轉(zhuǎn)到建設(shè)銀行儲蓄卡賬戶。記住這里的銀行縮寫后面就是對應(yīng)的數(shù)據(jù)表名稱，你要記不住，我給你理一理。
- 1招商銀行（CMBC）：“存么？白癡！”
- 2中國工商銀行（ICBC）： “愛存不存！”
- 3中國建設(shè)銀行（CCB）： “存？存不？”
- 4中國銀行（BC）： “不存！”
- 5中國農(nóng)業(yè)銀行（ABC）： “啊，不存！”
- 6民生銀行（CMSB）：“存么？SB！"
- 7興業(yè)銀行（CIB）：“存一百。”
- 8國家開發(fā)銀行（CDB）：“存點吧！”
- 9匯豐銀行（HSBC）：“還是不存！”
這個轉(zhuǎn)賬的操作可以簡化抽成一個事務(wù)，包含如下步驟：
- 查詢CMBC賬戶的余額是否大于100萬
- 從CMBC賬戶余額中減去100萬
- 在ICBC賬戶余額中增加100萬
以下語句對應(yīng)創(chuàng)建了一個轉(zhuǎn)賬事務(wù)：
- 1START TRANSACTION;
- 2SELECT balance FROM CMBC WHERE username='lemon';
- 3UPDATE CMBC SET balancebalance = balance - 1000000.00 WHERE username = 'lemon';
- 4UPDATE ICBC SET balancebalance = balance + 1000000.00 WHERE username = 'lemon';
- 5COMMIT;
事務(wù)的ACID特性是什么？
ACID其實是事務(wù)特性的英文首字母縮寫，具體的含義是這樣的：
- 原子性（atomicity)
一個事務(wù)必須被視為一個不可分割的最小工作單元，整個事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務(wù)來說，不可能只執(zhí)行其中的一部分操作。
- 致性（consistency)
數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另外一個一致性的狀態(tài)。在前面的例子中，一致性確保了，即使在執(zhí)行第三、四條語句之間時系統(tǒng)崩潰，CMBC賬戶中也不會損失100萬，不然lemon要哭死，因為事務(wù)最終沒有提交，所以事務(wù)中所做的修改也不會保存到數(shù)據(jù)庫中。
- 隔離性（isolation)
通常來說，一個事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不可見的。在前面的例子中，當(dāng)執(zhí)行完第三條語句、第四條語句還未開始時，此時如果有其他人準(zhǔn)備給lemon的CMBC賬戶存錢，那他看到的CMBC賬戶里還是有100萬的。
- 持久性（durability)
一旦事務(wù)提交，則其所做的修改就會永久保存到數(shù)據(jù)庫中。此時即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會丟失。持久性是個有點模糊的概念，因為實際上持久性也分很多不同的級別。有些持久性策略能夠提供非常強的安全保障，而有些則未必。而且「不可能有能做到100%的持久性保證的策略」否則還需要備份做什么。
ACID
什么是臟讀、不可重復(fù)讀、幻讀
臟讀
在事務(wù)A修改數(shù)據(jù)之后提交數(shù)據(jù)之前，這時另一個事務(wù)B來讀取數(shù)據(jù)，如果不加控制，事務(wù)B讀取到A修改過數(shù)據(jù)，之后A又對數(shù)據(jù)做了修改再提交，則B讀到的數(shù)據(jù)是臟數(shù)據(jù)，此過程稱為臟讀Dirty Read。
臟讀
不可重復(fù)讀
一個事務(wù)內(nèi)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了變更、或者某些記錄已經(jīng)被刪除了。
不可重復(fù)讀
幻讀
事務(wù)A在按查詢條件讀取某個范圍的記錄時，事務(wù)B又在該范圍內(nèi)插入了新的滿足條件的記錄，當(dāng)事務(wù)A再次按條件查詢記錄時，會產(chǎn)生新的滿足條件的記錄（幻行 Phantom Row）
幻讀
不可重復(fù)讀與幻讀有什么區(qū)別？
- 不可重復(fù)讀的重點是修改：在同一事務(wù)中，同樣的條件，第一次讀的數(shù)據(jù)和第二次讀的「數(shù)據(jù)不一樣」。（因為中間有其他事務(wù)提交了修改）
- 幻讀的重點在于新增或者刪除：在同一事務(wù)中，同樣的條件，第一次和第二次讀出來的「記錄數(shù)不一樣」。（因為中間有其他事務(wù)提交了插入/刪除）
四個隔離級別知道嗎？解決了什么問題
SQL實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別，每一種級別都規(guī)定了一個事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的，哪些是不可見的。低級別的隔離級一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開銷。
隔離級別
各個隔離級別可以不同程度的解決臟讀、不可重復(fù)讀、幻讀。隔離級別各有所長，沒有完美的解決方案，脫離業(yè)務(wù)場景談具體實施都是耍流氓。
隔離級別對比
MySQL中哪些存儲引擎支持事務(wù)？
MySQL中InnoDB和NDB Cluster存儲引擎提供了事務(wù)處理能力，以及其他支持事務(wù)的第三引擎。
什么是自動提交？
MySQL默認(rèn)采用自動提交AUTOCOMMIT模式。也就是說，如果不是顯式地開始一個事務(wù)，則每個查詢都被當(dāng)作一個事務(wù)執(zhí)行提交操作。
對于MyISAM或者內(nèi)存表這些事務(wù)型的表，修改AUTOCOMMIT不會有任何影響。對這類表來說，沒有COMMIT或者ROLLBACK的概念，也可以說是相當(dāng)于一直處于AUTOCOMMIT啟用的模式。
在事務(wù)中可以混合使用存儲引擎嗎？
盡量不要在同一個事務(wù)中使用多種存儲引擎，MySQL服務(wù)器層不管理事務(wù)，事務(wù)是由下層的存儲引擎實現(xiàn)的。
如果在事務(wù)中混合使用了事務(wù)型和非事務(wù)型的表（例如InnoDB和MyISAM表）,在正常提交的情況下不會有什么問題。
但如果該事務(wù)需要回滾，非事務(wù)型的表上的變更就無法撤銷，這會導(dǎo)致數(shù)據(jù)庫處于不一致的狀態(tài)，這種情況很難修復(fù)，事務(wù)的最終結(jié)果將無法確定。所以，為每張表選擇合適的存儲引擎非常重要。
MySQL存儲引擎類型有哪些？
最常用的存儲引擎是InnoDB引擎和MyISAM存儲引擎，InnoDB是MySQL的默認(rèn)事務(wù)引擎。
查看數(shù)據(jù)庫表當(dāng)前支持的引擎 ：
- 1show table status from 'your_db_name' where name='your_table_name';
- 2查詢結(jié)果表中的`Engine`字段指示存儲引擎類型。
InnoDB存儲引擎的特點和應(yīng)用場景？
InnoDB是MySQL的默認(rèn)「事務(wù)引擎」，被設(shè)置用來處理大量短期（short-lived）事務(wù)，短期事務(wù)大部分情況是正常提交的，很少會回滾。
更多InnoDB事務(wù)模型相關(guān)，參考MySQL官方手冊，這里貼一下鏈接：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html
歷史
現(xiàn)代MySQL版本中的InnoDB在歷史上叫InnoDB plugin，這個MySQL插件在2008年被開發(fā)出來，直到2010在Oracle收購了Sun公司后，發(fā)布的MySQL5.5才正式使用InnoDB plugin替代了舊版本的InnoDB，至此 「備胎」成功轉(zhuǎn)正成為MySQL的御用引擎而不再是插件，你看一個插件都這么努力。
特點
采用多版本并發(fā)控制（MVCC，MultiVersion Concurrency Control）來支持高并發(fā)。并且實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別，通過間隙鎖next-key locking策略防止幻讀的出現(xiàn)。
引擎的表基于聚簇索引建立，聚簇索引對主鍵查詢有很高的性能。不過它的二級索引secondary index非主鍵索引中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會很大。因此，若表上的索引較多的話，主鍵應(yīng)當(dāng)盡可能的小。另外InnoDB的存儲格式是平臺獨立。
InnoDB做了很多優(yōu)化，比如：磁盤讀取數(shù)據(jù)方式采用的可預(yù)測性預(yù)讀、自動在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引（adaptive hash index)，以及能夠加速插入操作的插入緩沖區(qū)（insert buffer)等。
InnoDB通過一些機制和工具支持真正的熱備份，MySQL的其他存儲引擎不支持熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味著停止讀取。
MyISAM存儲引擎的特點和應(yīng)用場景？
MyISAM是MySQL 5.1及之前的版本的默認(rèn)的存儲引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(shù)（GIS)等，但MyISAM不「支持事務(wù)和行級鎖」，對于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，依然可以使用它。
特性
MyISAM「不支持行級鎖而是對整張表加鎖」。讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖。但在表有讀取操作的同時，也可以往表中插入新的記錄，這被稱為并發(fā)插入。
MyISAM表可以手工或者自動執(zhí)行檢查和修復(fù)操作。但是和事務(wù)恢復(fù)以及崩潰恢復(fù)不同，可能導(dǎo)致一些「數(shù)據(jù)丟失」，而且修復(fù)操作是非常慢的。
對于MyISAM表，即使是BLOB和TEXT等長字段，也可以基于其前500個字符創(chuàng)建索引，MyISAM也支持「全文索引」，這是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢。
如果指定了DELAY_KEY_WRITE選項，在每次修改執(zhí)行完成時，不會立即將修改的索引數(shù)據(jù)寫入磁盤，而是會寫到內(nèi)存中的鍵緩沖區(qū)，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時候才會將對應(yīng)的索引塊寫入磁盤。這種方式可以極大的提升寫入性能，但是在數(shù)據(jù)庫或者主機崩潰時會造成「索引損壞」，需要執(zhí)行修復(fù)操作。
InnoDB與MyISAM對比
說了這么多估計看一眼也沒記住，給你一張表，簡單羅列兩種引擎的主要區(qū)別，如下圖。
引擎對比
其他存儲引擎
MySQL還支持其他一些存儲引擎，比如memory引擎、NDB集群引擎、CSV引擎，由于這些引擎沒有上述InnoDB 和MyISAM 常用，這里不作介紹，感興趣可以去翻MySQL文檔了解。這里同樣給出官方鏈接：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
引擎列表
再說兩句
這一篇是MySQL基礎(chǔ)篇，我力求用通俗易懂和圖表結(jié)合的形式給大家梳理這塊知識，越是基礎(chǔ)和底層的知識越容易被考察掌握程度，以上知識點都可能成為面試中的一個考察點，相信看完對MySQL事務(wù)和存儲引擎應(yīng)該有一個比較完整的理解。
最后，感謝各位的閱讀，文章的目的是分享對知識的理解，若文中出現(xiàn)明顯紕漏也歡迎指出，我們一起在探討中學(xué)習(xí)。
