來(lái)自公眾號(hào)：后端技術(shù)學(xué)堂
作者：撿田螺的小男孩

學(xué)習(xí)關(guān)系型數(shù)據(jù)庫(kù)MySQL是很好的切入點(diǎn)肛冶，大部分人學(xué)習(xí)和工作中用慣了CRUD州袒，對(duì)面試官刨根問(wèn)底的靈魂拷問(wèn)你還能對(duì)答如流嗎镰踏？我們有必要了解一些更深層次的數(shù)據(jù)庫(kù)基礎(chǔ)原理纺非。

整理了面試中，關(guān)于MySQL事務(wù)和存儲(chǔ)引擎10個(gè)FAQ（Frequently asked questions）搀缠，你想知道的都在這里。

什么是事務(wù)近迁？

事務(wù)就是「一組原子性的SQL查詢」艺普，或者說(shuō)一個(gè)獨(dú)立的工作單元。如果數(shù)據(jù)庫(kù)引擎能夠成功地對(duì)數(shù)據(jù)庫(kù)應(yīng)用該組查詢的全部語(yǔ)句鉴竭，那么就執(zhí)行該組查詢歧譬。如果其中有任何一條語(yǔ)句因?yàn)楸罎⒒蚱渌驘o(wú)法執(zhí)行，那么所有的語(yǔ)句都不會(huì)執(zhí)行搏存。也就是說(shuō)瑰步，事務(wù)內(nèi)的語(yǔ)句，要么全部執(zhí)行成功祭埂，要么全部執(zhí)行失敗面氓。

事務(wù)控制語(yǔ)法知道嗎？

1BEGIN 或 START TRANSACTION 顯式地開啟一個(gè)事務(wù)蛆橡；
2COMMIT / COMMIT WORK二者是等價(jià)的舌界。提交事務(wù)，并使已對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有修改成為永久性的泰演；
3ROLLBACK / ROLLBACK WORK呻拌。回滾會(huì)結(jié)束用戶的事務(wù)睦焕，并撤銷正在進(jìn)行的所有未提交的修改藐握；
4SAVEPOINT identifier 在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有多個(gè) SAVEPOINT垃喊；
5RELEASE SAVEPOINT identifier 刪除一個(gè)事務(wù)的保存點(diǎn)猾普；
6ROLLBACK TO identifier 把事務(wù)回滾到標(biāo)記點(diǎn)；
7SET TRANSACTION 用來(lái)設(shè)置事務(wù)的隔離級(jí)別本谜。InnoDB 存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED初家、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE

用通俗的語(yǔ)言說(shuō)說(shuō)你理解的事務(wù)

用銀行業(yè)務(wù)舉個(gè)栗子乌助，用戶lemon有兩銀行卡溜在，一張是招商銀行CMBC的工資卡，另一張是工商銀行ICBC的儲(chǔ)蓄卡他托，每月10號(hào)發(fā)工資都要把招行卡的100萬(wàn)轉(zhuǎn)到建設(shè)銀行儲(chǔ)蓄卡賬戶掖肋。記住這里的銀行縮寫后面就是對(duì)應(yīng)的數(shù)據(jù)表名稱，你要記不住赏参，我給你理一理志笼。

1招商銀行（CMBC）：“存么沿盅？白癡！”
2中國(guó)工商銀行（ICBC）： “愛存不存籽腕！”
3中國(guó)建設(shè)銀行（CCB）： “存嗡呼？存不纸俭？”
4中國(guó)銀行（BC）： “不存皇耗！”
5中國(guó)農(nóng)業(yè)銀行（ABC）： “啊，不存揍很！”
6民生銀行（CMSB）：“存么郎楼？SB！"
7興業(yè)銀行（CIB）：“存一百窒悔∥卦”
8國(guó)家開發(fā)銀行（CDB）：“存點(diǎn)吧！”
9匯豐銀行（HSBC）：“還是不存简珠！”

image

這個(gè)轉(zhuǎn)賬的操作可以簡(jiǎn)化抽成一個(gè)事務(wù)阶界，包含如下步驟：

1. 查詢CMBC賬戶的余額是否大于100萬(wàn)

2. 從CMBC賬戶余額中減去100萬(wàn)

3. 在ICBC賬戶余額中增加100萬(wàn)

以下語(yǔ)句對(duì)應(yīng)創(chuàng)建了一個(gè)轉(zhuǎn)賬事務(wù)：

1START TRANSACTION;
2SELECT balance FROM CMBC WHERE username='lemon';
3UPDATE CMBC SET balance = balance - 1000000.00 WHERE username = 'lemon';
4UPDATE ICBC SET balance = balance + 1000000.00 WHERE username = 'lemon';
5COMMIT;

事務(wù)的ACID特性是什么？

ACID其實(shí)是事務(wù)特性的英文首字母縮寫聋庵，具體的含義是這樣的：

• 原子性（atomicity)
  一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元膘融，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾祭玉，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)氧映，不可能只執(zhí)行其中的一部分操作。

• 致性（consistency)
  數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)脱货。在前面的例子中岛都，一致性確保了，即使在執(zhí)行第三振峻、四條語(yǔ)句之間時(shí)系統(tǒng)崩潰臼疫，CMBC賬戶中也不會(huì)損失100萬(wàn)，不然lemon要哭死扣孟，因?yàn)槭聞?wù)最終沒(méi)有提交烫堤，所以事務(wù)中所做的修改也不會(huì)保存到數(shù)據(jù)庫(kù)中。

• 隔離性（isolation)
  通常來(lái)說(shuō)哈打，一個(gè)事務(wù)所做的修改在最終提交以前塔逃，對(duì)其他事務(wù)是不可見的。在前面的例子中料仗，當(dāng)執(zhí)行完第三條語(yǔ)句湾盗、第四條語(yǔ)句還未開始時(shí)，此時(shí)如果有其他人準(zhǔn)備給lemon的CMBC賬戶存錢立轧，那他看到的CMBC賬戶里還是有100萬(wàn)的格粪。

• 持久性（durability)
  一旦事務(wù)提交躏吊，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中。此時(shí)即使系統(tǒng)崩潰帐萎，修改的數(shù)據(jù)也不會(huì)丟失比伏。持久性是個(gè)有點(diǎn)模糊的概念，因?yàn)閷?shí)際上持久性也分很多不同的級(jí)別疆导。有些持久性策略能夠提供非常強(qiáng)的安全保障赁项，而有些則未必。而且「不可能有能做到100%的持久性保證的策略」否則還需要備份做什么澈段。

image

什么是臟讀悠菜、不可重復(fù)讀、幻讀

臟讀

在事務(wù)A修改數(shù)據(jù)之后提交數(shù)據(jù)之前败富，這時(shí)另一個(gè)事務(wù)B來(lái)讀取數(shù)據(jù)悔醋，如果不加控制，事務(wù)B讀取到A修改過(guò)數(shù)據(jù)兽叮，之后A又對(duì)數(shù)據(jù)做了修改再提交芬骄，則B讀到的數(shù)據(jù)是臟數(shù)據(jù)，此過(guò)程稱為臟讀Dirty Read鹦聪。

image

不可重復(fù)讀

一個(gè)事務(wù)內(nèi)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間账阻，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了變更椎麦、或者某些記錄已經(jīng)被刪除了宰僧。

image

幻讀

事務(wù)A在按查詢條件讀取某個(gè)范圍的記錄時(shí)，事務(wù)B又在該范圍內(nèi)插入了新的滿足條件的記錄观挎，當(dāng)事務(wù)A再次按條件查詢記錄時(shí)琴儿，會(huì)產(chǎn)生新的滿足條件的記錄（幻行 Phantom Row）

image

不可重復(fù)讀與幻讀有什么區(qū)別？

• 不可重復(fù)讀的重點(diǎn)是修改：在同一事務(wù)中嘁捷，同樣的條件造成，第一次讀的數(shù)據(jù)和第二次讀的「數(shù)據(jù)不一樣」。（因?yàn)橹虚g有其他事務(wù)提交了修改）

• 幻讀的重點(diǎn)在于新增或者刪除：在同一事務(wù)中雄嚣，同樣的條件晒屎，第一次和第二次讀出來(lái)的「記錄數(shù)不一樣」。（因?yàn)橹虚g有其他事務(wù)提交了插入/刪除）

四個(gè)隔離級(jí)別知道嗎缓升？解決了什么問(wèn)題

SQL實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別鼓鲁，每一種級(jí)別都規(guī)定了一個(gè)事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的港谊，哪些是不可見的骇吭。低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開銷歧寺。

image

各個(gè)隔離級(jí)別可以不同程度的解決臟讀燥狰、不可重復(fù)讀棘脐、幻讀。隔離級(jí)別各有所長(zhǎng)龙致，沒(méi)有完美的解決方案蛀缝，脫離業(yè)務(wù)場(chǎng)景談具體實(shí)施都是耍流氓。

image

MySQL中哪些存儲(chǔ)引擎支持事務(wù)目代？

MySQL中InnoDB和NDB Cluster存儲(chǔ)引擎提供了事務(wù)處理能力屈梁，以及其他支持事務(wù)的第三引擎。

什么是自動(dòng)提交像啼？

MySQL默認(rèn)采用自動(dòng)提交AUTOCOMMIT模式俘闯。也就是說(shuō)潭苞，如果不是顯式地開始一個(gè)事務(wù)忽冻，則每個(gè)查詢都被當(dāng)作一個(gè)事務(wù)執(zhí)行提交操作。

對(duì)于MyISAM或者內(nèi)存表這些事務(wù)型的表此疹，修改AUTOCOMMIT不會(huì)有任何影響僧诚。對(duì)這類表來(lái)說(shuō)，沒(méi)有COMMIT或者ROLLBACK的概念蝗碎，也可以說(shuō)是相當(dāng)于一直處于AUTOCOMMIT啟用的模式湖笨。

在事務(wù)中可以混合使用存儲(chǔ)引擎嗎？

盡量不要在同一個(gè)事務(wù)中使用多種存儲(chǔ)引擎蹦骑，MySQL服務(wù)器層不管理事務(wù)慈省，事務(wù)是由下層的存儲(chǔ)引擎實(shí)現(xiàn)的。

如果在事務(wù)中混合使用了事務(wù)型和非事務(wù)型的表（例如InnoDB和MyISAM表）,在正常提交的情況下不會(huì)有什么問(wèn)題眠菇。

但如果該事務(wù)需要回滾边败，非事務(wù)型的表上的變更就無(wú)法撤銷，這會(huì)導(dǎo)致數(shù)據(jù)庫(kù)處于不一致的狀態(tài)捎废，這種情況很難修復(fù)笑窜，事務(wù)的最終結(jié)果將無(wú)法確定。所以登疗，為每張表選擇合適的存儲(chǔ)引擎非常重要排截。

MySQL存儲(chǔ)引擎類型有哪些？

最常用的存儲(chǔ)引擎是InnoDB引擎和MyISAM存儲(chǔ)引擎辐益，InnoDB是MySQL的默認(rèn)事務(wù)引擎断傲。

查看數(shù)據(jù)庫(kù)表當(dāng)前支持的引擎 ：

1show table status from 'your_db_name' where name='your_table_name'; 
2查詢結(jié)果表中的`Engine`字段指示存儲(chǔ)引擎類型。

InnoDB存儲(chǔ)引擎的特點(diǎn)和應(yīng)用場(chǎng)景智政？

InnoDB是MySQL的默認(rèn)「事務(wù)引擎」认罩，被設(shè)置用來(lái)處理大量短期（short-lived）事務(wù)，短期事務(wù)大部分情況是正常提交的女仰，很少會(huì)回滾猜年。

更多InnoDB事務(wù)模型相關(guān)抡锈，參考MySQL官方手冊(cè)，這里貼一下鏈接：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html

歷史

現(xiàn)代MySQL版本中的InnoDB在歷史上叫InnoDB plugin乔外，這個(gè)MySQL插件在2008年被開發(fā)出來(lái)床三，直到2010在Oracle收購(gòu)了Sun公司后，發(fā)布的MySQL5.5才正式使用InnoDB plugin替代了舊版本的InnoDB杨幼，至此 「?jìng)涮ァ钩晒D(zhuǎn)正成為MySQL的御用引擎而不再是插件撇簿，你看一個(gè)插件都這么努力。

image

特點(diǎn)

采用多版本并發(fā)控制（MVCC差购，MultiVersion Concurrency Control）來(lái)支持高并發(fā)四瘫。并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別，通過(guò)間隙鎖next-key locking策略防止幻讀的出現(xiàn)欲逃。

引擎的表基于聚簇索引建立找蜜，聚簇索引對(duì)主鍵查詢有很高的性能。不過(guò)它的二級(jí)索引secondary index非主鍵索引中必須包含主鍵列稳析，所以如果主鍵列很大的話洗做，其他的所有索引都會(huì)很大。因此彰居，若表上的索引較多的話诚纸，主鍵應(yīng)當(dāng)盡可能的小。另外InnoDB的存儲(chǔ)格式是平臺(tái)獨(dú)立陈惰。

InnoDB做了很多優(yōu)化畦徘，比如：磁盤讀取數(shù)據(jù)方式采用的可預(yù)測(cè)性預(yù)讀、自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引（adaptive hash index)抬闯，以及能夠加速插入操作的插入緩沖區(qū)（insert buffer)等井辆。

InnoDB通過(guò)一些機(jī)制和工具支持真正的熱備份，MySQL的其他存儲(chǔ)引擎不支持熱備份画髓，要獲取一致性視圖需要停止對(duì)所有表的寫入掘剪，而在讀寫混合場(chǎng)景中，停止寫入可能也意味著停止讀取奈虾。

MyISAM存儲(chǔ)引擎的特點(diǎn)和應(yīng)用場(chǎng)景夺谁？

MyISAM是MySQL 5.1及之前的版本的默認(rèn)的存儲(chǔ)引擎。MyISAM提供了大量的特性肉微，包括全文索引匾鸥、壓縮、空間函數(shù)（GIS)等碉纳，但MyISAM不「支持事務(wù)和行級(jí)鎖」勿负，對(duì)于只讀數(shù)據(jù)，或者表比較小劳曹、可以容忍修復(fù)操作奴愉，依然可以使用它琅摩。

特性

MyISAM「不支持行級(jí)鎖而是對(duì)整張表加鎖」。讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖锭硼，寫入時(shí)則對(duì)表加排它鎖房资。但在表有讀取操作的同時(shí)，也可以往表中插入新的記錄檀头，這被稱為并發(fā)插入轰异。

MyISAM表可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作。但是和事務(wù)恢復(fù)以及崩潰恢復(fù)不同暑始，可能導(dǎo)致一些「數(shù)據(jù)丟失」搭独，而且修復(fù)操作是非常慢的。

對(duì)于MyISAM表廊镜，即使是BLOB和TEXT等長(zhǎng)字段牙肝，也可以基于其前500個(gè)字符創(chuàng)建索引，MyISAM也支持「全文索引」期升，這是一種基于分詞創(chuàng)建的索引惊奇，可以支持復(fù)雜的查詢。

如果指定了DELAY_KEY_WRITE選項(xiàng)播赁，在每次修改執(zhí)行完成時(shí)，不會(huì)立即將修改的索引數(shù)據(jù)寫入磁盤吼渡，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)容为，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對(duì)應(yīng)的索引塊寫入磁盤。這種方式可以極大的提升寫入性能寺酪，但是在數(shù)據(jù)庫(kù)或者主機(jī)崩潰時(shí)會(huì)造成「索引損壞」坎背，需要執(zhí)行修復(fù)操作。

InnoDB與MyISAM對(duì)比

說(shuō)了這么多估計(jì)看一眼也沒(méi)記住寄雀，給你一張表得滤，簡(jiǎn)單羅列兩種引擎的主要區(qū)別，如下圖盒犹。

image

其他存儲(chǔ)引擎

MySQL還支持其他一些存儲(chǔ)引擎懂更，比如memory引擎、NDB集群引擎急膀、CSV引擎沮协，由于這些引擎沒(méi)有上述InnoDB 和MyISAM 常用，這里不作介紹卓嫂，感興趣可以去翻MySQL文檔了解慷暂。這里同樣給出官方鏈接：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

image

再說(shuō)兩句

這一篇是MySQL基礎(chǔ)篇，我力求用通俗易懂和圖表結(jié)合的形式給大家梳理這塊知識(shí)晨雳，越是基礎(chǔ)和底層的知識(shí)越容易被考察掌握程度行瑞，以上知識(shí)點(diǎn)都可能成為面試中的一個(gè)考察點(diǎn)奸腺，相信看完對(duì)MySQL事務(wù)和存儲(chǔ)引擎應(yīng)該有一個(gè)比較完整的理解。

最后血久，感謝各位的閱讀洋机，文章的目的是分享對(duì)知識(shí)的理解，若文中出現(xiàn)明顯紕漏也歡迎指出洋魂，我們一起在探討中學(xué)習(xí)绷旗。

References

https://book.douban.com/subject/23008813/
https://juejin.im/post/5c519bb8f265da617831cfff#comment
https://tech.meituan.com/2014/08/20/innodb-lock.html
https://blog.csdn.net/shellching/article/details/8106156
https://coolshell.cn/articles/6790.html
https://zhuanlan.zhihu.com/p/29166694https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
https://www.zhihu.com/question/27876575https://www.runoob.com/mysql/mysql-transaction.html
https://blog.csdn.net/qq_35642036/article/details/82820178?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-taskhttps://github.com/CyC2018/CS-Notes/blob/master/notes/MySQL.md#b-tree-%E5%8E%9F%E7%90%86