Mysql引擎介紹及InnoDB邏輯存儲(chǔ)結(jié)構(gòu)

對(duì)于后端開(kāi)發(fā)來(lái)說(shuō)，數(shù)據(jù)庫(kù)是我們?nèi)粘ｉ_(kāi)發(fā)中幾乎都會(huì)使用到的。而且對(duì)于許多大型應(yīng)用來(lái)說(shuō)，往往數(shù)據(jù)庫(kù)就是限制其性能的瓶頸所在。在以前的大多數(shù)時(shí)間里面，對(duì)數(shù)據(jù)庫(kù)的認(rèn)知，始終停留在一個(gè)比較淺的層面里。遂決定翻閱相關(guān)的書(shū)籍、博客和官方文檔，讓自己對(duì)數(shù)據(jù)庫(kù)有一個(gè)全面的了解。

MySQL架構(gòu)

以下是MySQL大體的組件結(jié)構(gòu)

摘自https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html

如果只關(guān)心和聚焦于客戶端向MySQL發(fā)送一條sql語(yǔ)句大致主要會(huì)涉及到的Server層的組件，可以查看下圖：

摘自MySQL實(shí)戰(zhàn)45講

上面這張圖的箭頭標(biāo)識(shí)和注釋也說(shuō)得比較清楚了。如果想要更加詳細(xì)的說(shuō)明，可以自行查閱相關(guān)文獻(xiàn)資料。需要說(shuō)明的是在MySQL8.0中，已經(jīng)將查詢緩存整個(gè)去掉了。

MySQL內(nèi)置存儲(chǔ)引擎介紹

上圖介紹的MySQL架構(gòu)大致可以分成Server層和存儲(chǔ)引擎層的。MySQL提供的基于插件式的存儲(chǔ)引擎，使得我們可以根據(jù)不同的需要選擇不同的引擎，甚至是在同一個(gè)schema中的不同表，也可以使用不同的存儲(chǔ)引擎。而實(shí)際的數(shù)據(jù)，也是存儲(chǔ)在存儲(chǔ)引擎中的。不同的存儲(chǔ)引擎的架構(gòu)和對(duì)數(shù)據(jù)的組織方式也有所不同。正是這些不同，決定了這些存儲(chǔ)引擎提供了不同的特性和功能。

內(nèi)置存儲(chǔ)引擎

我們可以使用show engines來(lái)查看當(dāng)前mysql內(nèi)置了哪些存儲(chǔ)引擎

show engines

三種常見(jiàn)的存儲(chǔ)引擎區(qū)別及介紹

InnoDB

InnoDB從5.1版本以后，已經(jīng)取代了MyISAM，成為MySQL默認(rèn)的存儲(chǔ)引擎了?？梢詮纳媳砩峡吹?，InnoDB是唯一支持事務(wù)、XA和Savepoints的內(nèi)置存儲(chǔ)引擎。同時(shí)，它還支持行鎖、外鍵約束等。InnoDB表基于聚簇索引建立，并且采用MVCC來(lái)支持高并發(fā)，同時(shí)實(shí)現(xiàn)了ANSI SQL92定義的四種隔離級(jí)別，并在引擎內(nèi)部實(shí)現(xiàn)了redo log和undo log。這些特性組合在一起，使得InnoDB成為了一個(gè)適合處理大量數(shù)據(jù)的高性能事務(wù)引擎。對(duì)于DBA來(lái)說(shuō)，結(jié)合server層的bin log組成的日志系統(tǒng)機(jī)制，使得使用InnoDB作為數(shù)據(jù)存儲(chǔ)引擎的數(shù)據(jù)庫(kù)具備安全的崩潰恢復(fù)能力和快速穩(wěn)定的復(fù)制性能，這些都是其它存儲(chǔ)引擎所不具備的。所以在Oracle收購(gòu)MySQL以后，沒(méi)有了版權(quán)問(wèn)題，InnoDB就毫無(wú)爭(zhēng)議地成為了MySQL的默認(rèn)存儲(chǔ)引擎了。

MyISAM

在MySQL5.1及之前的版本，MyISAM都是默認(rèn)的存儲(chǔ)引擎。雖然不支持事務(wù)、不支持行級(jí)鎖，崩潰后無(wú)法安全恢復(fù)。但是還MyISAM還是提供了許多其它的特性，包括全文索引、壓縮、空間函數(shù)(GIS)等。這些特性在某些場(chǎng)景下的性能是高于其它存儲(chǔ)引擎的，比如需要存儲(chǔ)和批量查詢歸檔日志數(shù)據(jù)，MyISAM引擎能提供較高的處理效率。

Memory

Memory存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，為查詢和引用其他表數(shù)據(jù)提供快速訪問(wèn)。因?yàn)槭谴嬖趦?nèi)存中，所以數(shù)據(jù)訪問(wèn)的速度一般也要快于其它存儲(chǔ)引擎，同時(shí)Memory支持Hash索引，因此在單值查找的速度非?？?。同時(shí)，MySQL在執(zhí)行查詢的過(guò)程中需要使用臨時(shí)表來(lái)保存中間結(jié)果，內(nèi)部使用的臨時(shí)表就是Memory表（如果結(jié)果集大小超出Memory表的限制，則會(huì)轉(zhuǎn)換成MyISAM表）。

使用哪一種引擎需要靈活選擇，一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同引擎以滿足各種性能和實(shí)際需求，使用合適的存儲(chǔ)引擎，將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能

InnoDB內(nèi)存/磁盤結(jié)構(gòu)及存儲(chǔ)邏輯結(jié)構(gòu)

InnoDB總體架構(gòu)

https://dev.mysql.com/doc/refman/5.5/en/innodb-architecture.html

上面這張圖是InnoDB存儲(chǔ)引擎在內(nèi)存和磁盤上的對(duì)應(yīng)結(jié)構(gòu)。
這里分別取兩個(gè)最常操作的update和select操作大致描述一下內(nèi)部的流轉(zhuǎn)機(jī)制（在默認(rèn)的可重復(fù)讀級(jí)別下, 同時(shí)略去了所有有關(guān)加鎖釋放鎖的操作）：
1.select * from xxx where id=1 語(yǔ)句：
（1）引擎接收到執(zhí)行計(jì)劃，會(huì)創(chuàng)建一個(gè)trx_id。
（2）查詢是否在這個(gè)內(nèi)存中，如果在，則返回行。如果在change buffer中或者不在內(nèi)存中，則從磁盤中讀入內(nèi)存（在change buffer中還要涉及merge操作更新內(nèi)存中的數(shù)據(jù)）。引擎層拿到數(shù)據(jù)，返回給到server層的執(zhí)行器。
（3）如果Innodb發(fā)現(xiàn)某二級(jí)索引被頻繁訪問(wèn)，會(huì)對(duì)該索引上創(chuàng)建一個(gè)哈希索引。下次同樣的查詢過(guò)來(lái)，會(huì)直接走這個(gè)自適應(yīng)哈希索引。

2.update xxx set xxx where id=1 語(yǔ)句：
（1）引擎接收到執(zhí)行計(jì)劃，會(huì)為該事務(wù)創(chuàng)建一個(gè)trx_id。
（2）查詢是否在這個(gè)內(nèi)存中，如果在，則返回行。如果在change buffer中或者不在內(nèi)存中，則從磁盤中讀入內(nèi)存（在change buffer中還要涉及merge操作更新內(nèi)存中的數(shù)據(jù)）。引擎層拿到數(shù)據(jù)，返回給到server層的執(zhí)行器。
（3）執(zhí)行器更新這行的相關(guān)列數(shù)據(jù)，再通過(guò)API接口調(diào)用引擎層，更新修改后的行數(shù)據(jù)更新到內(nèi)存中。同時(shí)寫(xiě)入redo log，此時(shí)處于prepare階段。
（4）引擎層告知執(zhí)行器已經(jīng)已經(jīng)執(zhí)行完成，隨時(shí)可以提交事務(wù)。
（5）server層的執(zhí)行器將該操作寫(xiě)入bin log
（6）執(zhí)行期通過(guò)API接口告知引擎提交事務(wù)，引擎把剛剛寫(xiě)入的redo log標(biāo)記為commit狀態(tài)，更新完成。（兩階段提交）

上面就是5.5版本下的文件組織，可以看到，每個(gè)schema都有一個(gè)文件夾，文件夾里面有 db.opt和*.frm格式的文件。db.opt存放的是字符集和字符集排序規(guī)則信息（字符文件，可以打開(kāi)），frm文件存的是表結(jié)構(gòu)等信息，官方同時(shí)也給出說(shuō)明，frm里的信息和InnoDB數(shù)據(jù)字典有所重疊，是出于歷史遺留原因（參見(jiàn)）。另外如果你沒(méi)有設(shè)置innodb_file_per_tale=ON那么所有的數(shù)據(jù)文件都將存儲(chǔ)在ibdata1文件中。所以這個(gè)文件會(huì)隨著數(shù)據(jù)的增長(zhǎng)而增長(zhǎng)。同時(shí)我們也可以看到，InnoDB結(jié)構(gòu)圖中5.7相對(duì)與5.5最大的變化，就是對(duì)于ibdata1的拆分，它把原本共享表空間，undo表空間和臨時(shí)表空間從ibdata1中分了出來(lái)。好處當(dāng)然就是結(jié)構(gòu)更加清晰，更加方便獨(dú)立管理，不會(huì)出現(xiàn)之前臨時(shí)表空間不再使用釋放了，ibdata1文件還是那么大等情況。

InnoDB的數(shù)據(jù)邏輯結(jié)構(gòu)

從上面InnoDB的架構(gòu)圖里面的右半部分可以知道，無(wú)論是索引還是數(shù)據(jù)，InnoDB都把它們存在.idb后綴（或者ibdata1）的文件中。而在MyISAM中，索引和數(shù)據(jù)是分別存儲(chǔ)在MYI和MYD文件中的。

下圖是InnoDB的數(shù)據(jù)組織形式

高性能MySQL-InnoDB聚簇索引

從圖中可以看出，InnoDB數(shù)據(jù)其實(shí)就是保存在聚簇索引的葉子節(jié)點(diǎn)中的，并且按照主鍵列順序存儲(chǔ)在數(shù)據(jù)文件中的。
相比之下，MyISAM的數(shù)據(jù)則是按照插入的順序存儲(chǔ)在磁盤上的，其索引的葉子節(jié)點(diǎn)存儲(chǔ)的是可以定位到實(shí)際數(shù)據(jù)的行號(hào)（或者是可以找到其物理位置的地址，這里隱藏了頁(yè)的物理細(xì)節(jié)）。

這兩種數(shù)據(jù)組織形式，使得下面兩種引擎有如下區(qū)別：
1.由于使用聚簇索引，所以無(wú)法同時(shí)把數(shù)據(jù)行存放在兩個(gè)地方，所以一個(gè)表只能有一個(gè)聚簇索引。而InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)也只能存儲(chǔ)聚簇索引上的主鍵值，從而導(dǎo)致二級(jí)索引（除覆蓋索引以外）在查詢數(shù)據(jù)的時(shí)候需要回表。
2.也由于MyISAM的索引不存在聚簇索引，葉子節(jié)點(diǎn)存儲(chǔ)的是實(shí)際數(shù)據(jù)的行號(hào)，所以對(duì)MyISAM而言，主鍵索引和其它索引一樣，不存在定位實(shí)際數(shù)據(jù)塊上的性能差異。

這里有一個(gè)有意思的問(wèn)題，如果InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)和MyISAM一樣，存儲(chǔ)的是可以直接找到實(shí)際數(shù)據(jù)的行號(hào)，那豈不是可以避免了回表的問(wèn)題。我個(gè)人感覺(jué)確實(shí)是這樣的。但是那樣會(huì)存在一個(gè)問(wèn)題，那就是行鎖和間隙鎖的加鎖問(wèn)題。我們知道，InnoDB的行鎖其實(shí)質(zhì)就是加在索引上面加的鎖，只要訪問(wèn)到該索引，就會(huì)在對(duì)應(yīng)的索引上面加鎖（包含回表的加鎖）如果不回表的話，那么不同索引上面加鎖并且相互獨(dú)立，那么行鎖和間隙鎖就毫無(wú)意義了。反過(guò)來(lái)說(shuō)，InnoDB為什么會(huì)做一個(gè)回表這樣的邏輯，其實(shí)是在犧牲部分二級(jí)索引定位數(shù)據(jù)頁(yè)的性能，來(lái)?yè)Q取更細(xì)粒度的鎖帶來(lái)的顯著性能提升。另外，如果在二級(jí)索引上存儲(chǔ)的是實(shí)際數(shù)據(jù)的行號(hào)，那在數(shù)據(jù)頁(yè)調(diào)整的時(shí)候，也要對(duì)這些二級(jí)索引進(jìn)行更新，這同時(shí)也會(huì)導(dǎo)致寫(xiě)性能的下降。

InnoDB的行鎖和間隙鎖

前面有提到，InnoDB的鎖是加在索引上的，行鎖的引入減少了鎖競(jìng)爭(zhēng)的情況，從而提高了并發(fā)度。在不同隔離級(jí)別的不同語(yǔ)句下，加鎖情況也是不一樣的。從上圖可以知道，有一些查詢甚至不需要加鎖，通過(guò)基于MVCC實(shí)現(xiàn)的一致性讀就可以達(dá)到對(duì)應(yīng)的隔離級(jí)別，這里又進(jìn)一步提高了并發(fā)度。

總結(jié)

其實(shí)當(dāng)我們大概了解了InnoDB架構(gòu)組件中各個(gè)組件的作用，以及其數(shù)據(jù)存儲(chǔ)的邏輯結(jié)構(gòu)。也就大概明白了為什么InnoDB提供了這么多其它存儲(chǔ)引擎不能提供的相關(guān)特性。例如：

1.redo log及對(duì)應(yīng)的兩階段提交協(xié)議的引入，使得引擎可以提供在系統(tǒng)崩潰的時(shí)候提供安全崩潰恢復(fù)機(jī)制。
2.undo log機(jī)制的引入，每一事務(wù)都有一個(gè)單調(diào)遞增的trx_id,使得Innodb可以基于MVCC對(duì)相關(guān)的事務(wù)做一致性讀（或者稱為快照讀）。
3.InnoDB基于聚簇索引的數(shù)據(jù)組織形式，多數(shù)情況下通過(guò)行鎖，間隙鎖和快照讀實(shí)現(xiàn)了四種隔離級(jí)別。這種方式相比與直接加表鎖，性能更高，更加適合高并發(fā)場(chǎng)景。

通過(guò)根據(jù)其作用推出它能提供的特性，反過(guò)來(lái)也加深我們對(duì)各個(gè)組件的作用的理解。同時(shí)，通過(guò)思考這些組件的設(shè)計(jì)思想來(lái)實(shí)現(xiàn)對(duì)應(yīng)的特性，這本身就是一個(gè)很有趣的過(guò)程。

最后說(shuō)一句(求關(guān)注，別白嫖我)

如果這篇文章對(duì)您有所幫助，或者有所啟發(fā)的話，幫忙掃描下發(fā)二維碼關(guān)注一下，您的支持是我堅(jiān)持寫(xiě)作最大的動(dòng)力。求一鍵三連：點(diǎn)贊、轉(zhuǎn)發(fā)、在看。