mysql多大_洞悉MySQL底层架构：游走在缓冲与磁盘之间
提起MySQL，其實網(wǎng)上已經(jīng)有一大把教程了，為什么我還要寫這篇文章呢，大概是因為網(wǎng)上很多網(wǎng)站都是比較零散，而且描述不夠直觀，不能系統(tǒng)對MySQL相關(guān)知識有一個系統(tǒng)的學(xué)習(xí)，導(dǎo)致不能形成知識體系。為此我撰寫了這篇文章，試圖讓這些底層架構(gòu)相關(guān)知識更加直觀易懂：
- 盡量以圖文的方式描述技術(shù)原理；
- 涉及到關(guān)鍵的技術(shù)，附加官網(wǎng)或者技術(shù)書籍來源，方便大家進一步擴展學(xué)習(xí)；
- 涉及到的背景知識盡可能做一個交代，比如討論到log buffer的刷盤方式，延伸一下IO寫磁盤相關(guān)知識點。
好了，MySQL從不會到精通系列馬上就要開始了(看完之后還是不會的話..請忽略這句話)。
可能會有同學(xué)問：為啥不直接學(xué)更加先進的TiDB，或者是強大的OceanBase。
其實，MySQL作為老牌的應(yīng)用場景廣泛的關(guān)系型開源數(shù)據(jù)庫，其底層架構(gòu)是很值得我們學(xué)習(xí)的，吸收其設(shè)計精華，那么我們在平時的方案設(shè)計工作中也可以借鑒，如果項目中用的是MySQL，那么就能夠把數(shù)據(jù)庫用的更好了，了解了MySQL底層的執(zhí)行原理，對于調(diào)優(yōu)工作也是有莫大幫助的。本文我重點講述MySQL底層架構(gòu)，涉及到：
- 內(nèi)存結(jié)構(gòu)：buffer pool、log buffer、change buffer，buffer pool的頁淘汰機制是怎樣的；
- 磁盤結(jié)構(gòu)：系統(tǒng)表空間、獨立表空間、通用表空間、undo表空間、redo log；
- 以及IO相關(guān)底層原理、查詢SQL執(zhí)行流程、數(shù)據(jù)頁結(jié)構(gòu)和行結(jié)構(gòu)描述、聚集索引和輔助索引的底層數(shù)據(jù)組織方式、MVCC多版本并發(fā)控制的底層實現(xiàn)原理，以及可重復(fù)讀、讀已提交是怎么通過MVCC實現(xiàn)的。
看完文本文，您將了解到：
1、MySQL架構(gòu)
如下圖為MySQL架構(gòu)涉及到的常用組件：
2、查詢SQL執(zhí)行流程
有如下表格：
我們執(zhí)行以下sql：
select * from t_user where user_id=10000;2.1、MySQL客戶端與服務(wù)器建立連接
如下圖，建立過程：
- 客戶端通過mysql命令發(fā)起連接請求；
- 經(jīng)過三次握手后與服務(wù)端建立TCP連接；
- 連接器接收到請求之后使用用戶密碼進行身份驗證；
- 驗證通過之后，獲取用戶的權(quán)限信息緩存起來，該連接后面都是基于該緩存中的權(quán)限執(zhí)行sql；
對于Java應(yīng)用程序來說，一般會把建立好的連接放入數(shù)據(jù)庫連接池中進行復(fù)用，只要這個連接不關(guān)閉，就會一直在MySQL服務(wù)端保持著，可以通過show processlist命令查看，如下：
注意，這里有個Time，表示這個連接多久沒有動靜了，上面例子是656秒沒有動靜，默認地，如果超過8個小時還沒有動靜，連接器就會自動斷開連接，可以通過wait_timeout參數(shù)進行控制。
2.2、執(zhí)行SQL
如下圖，執(zhí)行sql：
- 服務(wù)端接收到客戶端的查詢sql之后，先嘗試從查詢緩存中查詢該sql是否已經(jīng)有緩存的結(jié)果了，如果有則直接返回結(jié)果，如果沒有則執(zhí)行下一步；
- 分析器拿到sql之后會嘗試對sql語句進行詞法分析和語法分析，校驗語法的正確性，通過之后繼續(xù)往下執(zhí)行；
- 優(yōu)化器拿到分析器的sql之后，開始繼續(xù)解析sql，判斷到需要走什么索引，根據(jù)實際情況重寫sql，最終生成執(zhí)行計劃；
- 執(zhí)行器根據(jù)執(zhí)行計劃執(zhí)行sql，執(zhí)行之前會先進行操作權(quán)限校驗；然后根據(jù)表存儲引擎調(diào)用對飲接口進行查詢數(shù)據(jù)，這里的掃描行數(shù)就是指的接口返回的記錄數(shù)，執(zhí)行器拿到返回記錄之后進一步加工，如本例子：執(zhí)行器拿到select * from t_user where user_id=10000的所有記錄，在依次判斷user_name是不是等于"arthinking"，獲取到匹配的記錄。
3、InnoDB引擎架構(gòu)
如下圖，為存儲引擎的架構(gòu)：
其實內(nèi)存中的結(jié)構(gòu)不太好直接觀察到，不過磁盤的還是可以看到的，我們找到磁盤中MySQL的數(shù)據(jù)文件夾看看：
cd innodb_data_home_dir 查看MySQL 數(shù)據(jù)目錄：
|- ib_buffer_pool // 保存緩沖池中頁面的表空間ID和頁面ID，用于重啟恢復(fù)緩沖池|- ib_logfile0 // redo log 磁盤文件1|- ib_logfile1 // redo log 磁盤文件2，默認情況下，重做日志存在磁盤的這兩個文件中，循環(huán)的方式寫入重做日志|- ibdata1 // 系統(tǒng)表空間文件|- ibtmp1 // 默認臨時表空間文件，可通過innodb_temp_data_file_path屬性指定文件位置|- mysql/|- mysql-bin.000001 // bin log文件|- mysql-bin.000001 // bin log文件...|- mysql-bin.index // bin log文件索引|- mysqld.local.err // 錯誤日志|- mysqld.local.pid // mysql進程號|- performance_schema/ // performance_schema數(shù)據(jù)庫|- sys/ // sys數(shù)據(jù)庫|- test/ // 數(shù)據(jù)庫文件夾 |- db.opt // test數(shù)據(jù)庫配置文件，包含數(shù)據(jù)庫字符集屬性 |- t.frm // 數(shù)據(jù)表元數(shù)據(jù)文件，不管是使用獨立表空間還是系統(tǒng)表空間，每個表都對應(yīng)有一個 |- t.ibd // 數(shù)據(jù)庫表獨立表空間文件，如果使用的是獨立表空間，則一個表對應(yīng)一個ibd文件，否則保存在系統(tǒng)表空間文件中innodb_data_home_dir[1]
ib_buffer_pool[2]
ib_logfile0[3]
ibtmp1[4]
db.opt[5]
接下來我們逐一來介紹。
3.1、buffer pool
buffer pool(緩沖池)是主內(nèi)存中的一個區(qū)域，在InnoDB訪問表數(shù)據(jù)和索引數(shù)據(jù)的時候，會順便把對應(yīng)的數(shù)據(jù)頁緩存到緩沖池中。如果直接從緩沖池中直接讀取數(shù)據(jù)將會加快處理速度。在專用服務(wù)器上，通常將80%左右的物理內(nèi)存分配給緩沖池。
為了提高緩存管理效率，緩沖池把頁面鏈接為列表，使用改進版的LRU算法將很少使用的數(shù)據(jù)從緩存中老化淘汰掉。
3.1.1、緩沖池LRU算法
通過使用改進版的LRU算法來管理緩沖池列表。
當(dāng)需要把新頁面存儲到緩沖池中的時候，將淘汰最近最少使用的頁面，并將新頁面添加到舊子列表的頭部。
該算法運行方式：
- 默認 3/8緩沖池用于舊子列表；
- 當(dāng)新頁面如緩沖池時，首先將其插入舊子列表頭部；
- 重復(fù)訪問舊子列表的頁面，將使其移動至新子列表的頭部；
- 隨著數(shù)據(jù)庫的運行，頁面逐步移至列表尾部，緩沖池中未被方位的頁面最終將被老化淘汰。
相關(guān)優(yōu)化參數(shù)：
- innodb_old_blocks_pct：控制LRU列表中舊子列表的百分比，默認是37，也就是3/8，可選范圍為5~95；
- innodb_old_blocks_time ：指定第一次訪問頁面后的時間窗口，該時間窗口內(nèi)訪問頁面不會使其移動到LRU列表的最前面。默認是1000，也就是1秒。
innodb_old_blocks_time很重要，有了這1秒，對于全表掃描，由于是順序掃描的，一般同一個數(shù)據(jù)頁的數(shù)據(jù)都是在一秒內(nèi)訪問完成的，不會升級到新子列表中，一直在舊子列表淘汰數(shù)據(jù)，所以不會影響到新 子列表的緩存。
3.1.2、關(guān)于磁盤IO的方式
O_DIRECT是innodb_flush_method參數(shù)的一個可選值。
這里先介紹下和數(shù)據(jù)庫性能密切相關(guān)的文件IO操作方法
3.1.2.1、文件IO操作方法
數(shù)據(jù)庫系統(tǒng)是基于文件系統(tǒng)的，其性能和設(shè)備讀寫的機制有密切的關(guān)系。
open：打開文件[6]
int open(const char *pathname, int flags);系統(tǒng)調(diào)用Open會為該進程一個文件描述符fd，常用的flags如下：
- O_WRONLY：表示我們以"寫"的方式打開，告訴內(nèi)核我們需要向文件中寫入數(shù)據(jù)；
- O_DSYNC：每次write都等待物理I/O完成，但是如果寫操作不影響讀取剛寫入的數(shù)據(jù)，則不等待文件屬性更新；
- O_SYNC：每次write都等到物理I/O完成，包括write引起的文件屬性的更新；
- O_DIRECT：執(zhí)行磁盤IO時繞過緩沖區(qū)高速緩存(內(nèi)核緩沖區(qū))，從用戶空間直接將數(shù)據(jù)傳遞到文件或磁盤設(shè)備，稱為直接IO(direct IO)。因為沒有了OS cache，所以會O_DIRECT降低文件的順序讀寫的效率。
write：寫文件[7]
ssize_t write(int fd, const void *buf, size_t count);使用open打開文件獲取到文件描述符之后，可以調(diào)用write函數(shù)來寫文件，具體表現(xiàn)根據(jù)open函數(shù)參數(shù)的不同而不同弄。
fsync & fdatasync：刷新文件[8]
#include int fsync(int fd);int fdatasync(int fd);- fdatasync：操作完write之后，我們可以調(diào)用fdatasync將文件數(shù)據(jù)塊flush到磁盤，只要fdatasync返回成功，則可以認為數(shù)據(jù)已經(jīng)寫到磁盤了；
- fsync：與O_SYNC參數(shù)類似，fsync還會更新文件metadata到磁盤；
- sync：sync只是將修改過的塊緩沖區(qū)寫入隊列，然后就返回，不等實際寫磁盤操作完成；
為了保證文件更新成功持久化到硬盤，除了調(diào)用write方法，還需要調(diào)用fsync。
大致交互流程如下圖：
更多關(guān)于磁盤IO的相關(guān)內(nèi)容，可以閱讀：On Disk IO, Part 1: Flavors of IO[9]
fsync性能問題：除了刷臟頁到磁盤，fsync還會同步文件metadata，而文件數(shù)據(jù)和metadata通常存放在磁盤不同地方，所以fsync至少需要兩次IO操作。
對fsync性能的優(yōu)化建議：由于以上性能問題，如果能夠減少metadata的更新，那么就可以使用fdatasync了。因此需要確保文件的尺寸在write前后沒有發(fā)生變化。為此，可以創(chuàng)建固定大小的文件進行寫，寫完則開啟新的文件繼續(xù)寫。
3.1.2.2、innodb_flush_method
innodb_flush_method定義用于將數(shù)據(jù)刷新到InnoDB數(shù)據(jù)文件和日志文件的方法，這可能會影響I/O吞吐量。
以下是具體參數(shù)說明：
屬性值命令行格式--innodb-flush-method=value系統(tǒng)變量innodb_flush_method范圍全局默認值(Windows)unbuffered默認值(Unix)fsync有效值(Windows)unbuffered, normal有效值(Unix)fsync, O_DSYNC, littlesync, nosync, O_DIRECT, O_DIRECT_NO_FSYNC
比較常用的是這三種：
fsync
默認值，使用fsync()系統(tǒng)調(diào)用來flush數(shù)據(jù)文件和日志文件到磁盤；
O_DSYNC
由于open函數(shù)的O_DSYNC參數(shù)在許多Unix系統(tǒng)上都存中問題，因此InnoDB不直接使用O_DSYNC。
InnoDB用于O_SYNC 打開和刷新日志文件，fsync()刷新數(shù)據(jù)文件。
表現(xiàn)為：寫日志操作是在write函數(shù)完成，數(shù)據(jù)文件寫入是通過fsync()系統(tǒng)調(diào)用來完成；
O_DIRECT
使用O_DIRECT (在Solaris上對應(yīng)為directio())打開數(shù)據(jù)文件，并用于fsync()刷新數(shù)據(jù)文件和日志文件。此選項在某些GNU/Linux版本，FreeBSD和Solaris上可用。
表現(xiàn)為：數(shù)據(jù)文件寫入直接從buffer pool到磁盤，不經(jīng)過操作系統(tǒng)緩沖，日志還是需要經(jīng)過操作系統(tǒng)緩存；
O_DIRECT_NO_FSYNC
在刷新I/O期間InnoDB使用O_DIRECT，并且每次write操作后跳過fsync()系統(tǒng)調(diào)用。
此設(shè)置適用于某些類型的文件系統(tǒng)，但不適用于其他類型的文件系統(tǒng)。例如，它不適用于XFS。如果不確定所使用的文件系統(tǒng)是否需要fsync()(例如保留所有文件元數(shù)據(jù))，請改用O_DIRECT。
如下圖所示：
為什么使用了O_DIRECT配置后還需要調(diào)用fsync()?
參考MySQL的這個bug：Innodb calls fsync for writes with innodb_flush_method=O_DIRECT[10]
Domas進行的一些測試表明，如果沒有fsync，某些文件系統(tǒng)(XFS)不會同步元數(shù)據(jù)。如果元數(shù)據(jù)會更改，那么您仍然需要使用fsync(或O_SYNC來打開文件)。
例如，如果在啟用O_DIRECT的情況下增大文件大小，它仍將寫入文件的新部分，但是由于元數(shù)據(jù)不能反映文件的新大小，因此如果此刻系統(tǒng)發(fā)生崩潰，文件尾部可能會丟失。
為此：當(dāng)重要的元數(shù)據(jù)發(fā)生更改時，請繼續(xù)使用fsync或除O_DIRECT之外，也可以選擇使用O_SYNC。
MySQL從v5.6.7起提供了O_DIRECT_NO_FSYNC選項來解決此類問題。
3.2、Change Buffer
change buffer是一種特殊的數(shù)據(jù)結(jié)構(gòu)，當(dāng)二級索引頁(非唯一索引)不在緩沖池中時，它們會緩存這些更改 。當(dāng)頁面通過其他讀取操作加載到緩沖池中時，再將由INSERT，UPDATE或DELETE操作(DML)產(chǎn)生的change buffer合并到buffer pool的數(shù)據(jù)頁中。
為什么唯一索引不可以使用chage buffer？
針對唯一索引，如果buffer pool不存在對應(yīng)的數(shù)據(jù)頁，還是需要先去磁盤加載數(shù)據(jù)頁，才能判斷記錄是否重復(fù)，這一步避免不了。
而普通索引是非唯一的，插入的時候以相對隨機的順序發(fā)生，刪除和更新也會影響索引樹中不相鄰的二級索引樹，通過使用合并緩沖，避免了在磁盤產(chǎn)生大量的隨機IO訪問獲取普通索引頁。
問題
當(dāng)有許多受影響的行和許多輔助索引要更新時，change buffer合并可能需要幾個小時，在此期間，I/O會增加，可能會導(dǎo)致查詢效率大大降低，即使在事務(wù)提交之后，或者服務(wù)器重啟之后，change buffer合并操作也會繼續(xù)發(fā)生。相關(guān)閱讀：Section 14.22.2, “Forcing InnoDB Recovery”
3.3、自適應(yīng)哈希索引
自適應(yīng)哈希索引功能由innodb_adaptive_hash_index變量啟用 ，或在服務(wù)器啟動時由--skip-innodb-adaptive-hash-index禁用。
3.4、Log Buffer
log buffer(日志緩沖區(qū))用于保存要寫入磁盤上的log file(日志文件)的數(shù)據(jù)。日志緩存區(qū)的內(nèi)容會定期刷新到磁盤。
日志緩沖區(qū)大小由innodb_log_buffer_size變量定義 。默認大小為16MB。較大的日志緩沖區(qū)可以讓大型事務(wù)在提交之前無需將redo log寫入磁盤。
如果您有更新，插入或者刪除多行的事務(wù)，嘗試增大日志緩沖區(qū)的大小可以節(jié)省磁盤I/O。
3.4.1、配置參數(shù)
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit 變量控制如何將日志緩沖區(qū)的內(nèi)容寫入并刷新到磁盤。
該參數(shù)控制是否嚴格存儲ACID還是嘗試獲取更高的性能，可以通過該參數(shù)獲取更好的性能，但是會導(dǎo)致在系統(tǒng)崩潰的過程中導(dǎo)致數(shù)據(jù)丟失。
可選參數(shù)：
- 0，事務(wù)提交之后，日志只記錄到log buffer中，每秒寫一次日志到緩存并刷新到磁盤，尚未刷新的日志可能會丟失；
- 1，要完全符合ACID，必須使用該值，表示日志在每次事務(wù)提交時寫入緩存并刷新到磁盤；
- 2，每次事務(wù)提交之后，日志寫到page cache，每秒刷一次到磁盤，尚未刷新的日志可能會丟失；
innodb_flush_log_at_timeout
innodb_flush_log_at_timeout 變量控制日志刷新頻率。可讓您將日志刷新頻率設(shè)置為N秒(其中N為1 ... 2700，默認值為1)
為了保證數(shù)據(jù)不丟失，請執(zhí)行以下操作：
如果啟用了binlog，則設(shè)置：sync_binlog=1;innodb_flush_log_at_trx_commit=1;
配置效果如下圖所示：
3.5、表空間
一個InnoDB表及其索引可以在建在系統(tǒng)表空間中，或者是在一個 獨立表空間 中，或在 通用表空間。
- 當(dāng)innodb_file_per_table啟用時，通常是將表存放在獨立表空間中，這是默認配置；
- 當(dāng)innodb_file_per_table禁用時，則會在系統(tǒng)表空間中創(chuàng)建表；
- 要在通用表空間中創(chuàng)建表，請使用 CREATE TABLE ... TABLESPACE語法。有關(guān)更多信息，請參見官方文檔 14.6.3.3 General Tablespaces。
表空間概覽圖：
表空間涉及的文件
相關(guān)文件默認在磁盤中的innodb_data_home_dir目錄下：
|- ibdata1 // 系統(tǒng)表空間文件|- ibtmp1 // 默認臨時表空間文件，可通過innodb_temp_data_file_path屬性指定文件位置|- test/ // 數(shù)據(jù)庫文件夾 |- db.opt // test數(shù)據(jù)庫配置文件，包含數(shù)據(jù)庫字符集屬性 |- t.frm // 數(shù)據(jù)表元數(shù)據(jù)文件，不管是使用獨立表空間還是系統(tǒng)表空間，每個表都對應(yīng)有一個 |- t.ibd // 數(shù)據(jù)庫表獨立表空間文件，如果使用的是獨立表空間，則一個表對應(yīng)一個ibd文件，否則保存在系統(tǒng)表空間文件中frm文件
創(chuàng)建一個InnoDB表時，MySQL 在數(shù)據(jù)庫目錄中創(chuàng)建一個.frm文件。frm文件包含MySQL表的元數(shù)據(jù)(如表定義)。每個InnoDB表都有一個.frm文件。
與其他MySQL存儲引擎不同， InnoDB它還在系統(tǒng)表空間內(nèi)的自身內(nèi)部數(shù)據(jù)字典中編碼有關(guān)表的信息。MySQL刪除表或數(shù)據(jù)庫時，將刪除一個或多個.frm文件以及InnoDB數(shù)據(jù)字典中的相應(yīng)條目。
因此，在InnoDB中，您不能僅通過移動.frm 文件來移動表。有關(guān)移動InnoDB 表的信息，請參見官方文檔14.6.1.4 Moving or Copying InnoDB Tables。
ibd文件
對于在獨立表空間創(chuàng)建的表，還會在數(shù)據(jù)庫目錄中生成一個 .ibd表空間文件。
在通用表空間中創(chuàng)建的表在現(xiàn)有的常規(guī)表空間 .ibd文件中創(chuàng)建。常規(guī)表空間文件可以在MySQL數(shù)據(jù)目錄內(nèi)部或外部創(chuàng)建。有關(guān)更多信息，請參見官方文檔14.6.3.3 General Tablespaces。
ibdata文件
系統(tǒng)表空間文件，在 InnoDB系統(tǒng)表空間中創(chuàng)建的表在ibdata中創(chuàng)建。
3.5.1、系統(tǒng)表空間
系統(tǒng)表空間由一個或多個數(shù)據(jù)文件(ibdata文件)組成。其中包含與InnoDB相關(guān)對象有關(guān)的元數(shù)據(jù)(InnoDB 數(shù)據(jù)字典 data dictionary)，以及更改緩沖區(qū)(change buffer)， 雙寫緩沖區(qū)(doublewrite buffer)和撤消日志(undo logs)的存儲區(qū) 。
InnoDB 如果表是在系統(tǒng)表空間中創(chuàng)建的，則系統(tǒng)表空間中也包含表的表數(shù)據(jù)和索引數(shù)據(jù)。
系統(tǒng)表空間的問題
在MySQL 5.6.7之前，默認設(shè)置是將所有InnoDB表和索引保留 在系統(tǒng)表空間內(nèi)，這通常會導(dǎo)致該文件變得非常大。因為系統(tǒng)表空間永遠不會縮小，所以如果先加載然后刪除大量臨時數(shù)據(jù)，則可能會出現(xiàn)存儲問題。
在MySQL 5.7中，默認設(shè)置為 獨立表空間模式，其中每個表及其相關(guān)索引存儲在單獨的 .ibd文件中。此默認設(shè)置使使用Barracuda文件格式的InnoDB功能更容易使用，例如表壓縮，頁外列的有效存儲以及大索引鍵前綴(innodb_large_prefix)。
將所有表數(shù)據(jù)保留在系統(tǒng)表空間或單獨的 .ibd文件中通常會對存儲管理產(chǎn)生影響。
InnoDB在MySQL 5.7.6中引入了通用表空間[11]，這些表空間也由.ibd文件表示 。通用表空間是使用CREATE TABLESPACE語法創(chuàng)建的共享表空間。它們可以在MySQL數(shù)據(jù)目錄之外創(chuàng)建，能夠容納多個表，并支持所有行格式的表。
3.5.2、獨立表空間
MySQL 5.7中，配置參數(shù)：innodb_file_per_table，默認處于啟用狀態(tài)，這是一個重要的配置選項，會影響InnoDB文件存儲，功能的可用性和I/O特性等。
啟用之后，每個表的數(shù)據(jù)和索引是存放在單獨的.ibd文件中的，而不是在系統(tǒng)表空間的共享ibdata文件中。
優(yōu)點
- 您可以更加靈活的選擇數(shù)據(jù)壓縮[12]的行格式，如：默認情況下(innodb_page_size=16K)，前綴索引[13]最多包含768個字節(jié)。如果開啟innodb_large_prefix，且Innodb表的存儲行格式為 DYNAMIC 或 COMPRESSED，則前綴索引最多可包含3072個字節(jié)，前綴索引也同樣適用；
- TRUNCATE TABLE執(zhí)行的更快，并且回收的空間不會繼續(xù)保留，而是讓操作系統(tǒng)使用；
- 可以在單獨的存儲設(shè)備上創(chuàng)建每表文件表空間數(shù)據(jù)文件，以進行I / O優(yōu)化，空間管理或備份。請參見 14.6.1.2 Creating Tables Externally；
缺點
- 獨立表空間中的未使用空間只能由同一個表使用，如果管理不當(dāng)，會造成空間浪費；
- 多個表需要刷盤，只能執(zhí)行多次fsync，無法合并多個表的寫操作，這可能會導(dǎo)致更多的fsync操作總數(shù)；
- mysqld必須為每個表文件空間保留一個打開的文件句柄，如果表數(shù)量多，可能會影響性能；
- 每個表都需要自己的數(shù)據(jù)文件，需要更多的文件描述符；
即使啟用了innodb_file_per_table參數(shù)，每張表空間存放的只是數(shù)據(jù)、索引和插入緩存Bitmap頁，其他數(shù)據(jù)如回滾信息、插入緩沖索引頁、系統(tǒng)事務(wù)信息、二次寫緩沖等還是存放在原來的共享表空間中。
3.5.3、通用表空間
通用表空間使用CREATE TABLESPACE語法創(chuàng)建。
類似于系統(tǒng)表空間，通用表空間是共享表空間，可以存儲多個表的數(shù)據(jù)。
通用表空間比獨立表空間具有潛在的內(nèi)存優(yōu)勢，服務(wù)器在表空間的生存期內(nèi)將表空間元數(shù)據(jù)保留在內(nèi)存中。一個通用表空間通常可以存放多個表數(shù)據(jù)，消耗更少的表空間元數(shù)據(jù)內(nèi)存。
數(shù)據(jù)文件可以放置在MySQL數(shù)據(jù)目錄或獨立于MySQL數(shù)據(jù)目錄。
3.5.4、undo表空間
undo表空間包含undo log。
innodb_rollback_segments變量定義分配給每個撤消表空間的回滾段的數(shù)量。
undo log可以存儲在一個或多個undo表空間中，而不是系統(tǒng)表空間中。
在默認配置中，撤消日志位于系統(tǒng)表空間中。SSD存儲更適合undo log的I/O模式，為此，可以把undo log存放在有別于系統(tǒng)表空間的ssd硬盤中。
innodb_undo_tablespaces 配置選項控制undo表空間的數(shù)量。
3.5.5、臨時表空間
由用戶創(chuàng)建的非壓縮臨時表和磁盤內(nèi)部臨時表是在共享臨時表空間中創(chuàng)建的。
innodb_temp_data_file_path 配置選項指定零時表空間文件的路徑，如果未指定，則默認在 innodb_data_home_dir目錄中創(chuàng)建一個略大于12MB 的自動擴展數(shù)據(jù)文件ibtmp1 。
使用ROW_FORMAT=COMPRESSED屬性創(chuàng)建的壓縮臨時表，是在獨立表空間中的臨時文件目錄中創(chuàng)建的 。
服務(wù)啟動的時候創(chuàng)建臨時表空間，關(guān)閉的時候銷毀臨時表空間。如果臨時表空間創(chuàng)建失敗，則意味著服務(wù)啟動失敗。
3.6、InnoDB底層邏輯存儲結(jié)構(gòu)
在介紹索引之前，我們有必要了解一下InnoDB底層的邏輯存儲結(jié)構(gòu)，因為索引是基于這個底層邏輯存儲結(jié)構(gòu)創(chuàng)建的。截止到目前，我們所展示的都僅僅是物理磁盤中的邏輯視圖，接下來我們就來看看底層的視圖。
3.6.1、ibd文件組織結(jié)構(gòu)
現(xiàn)在我們打開一個表空間ibd文件，看看里面都是如何組織數(shù)據(jù)的？
如下圖，表空間由段(segment)、區(qū)(extent)、頁(page)組成。
InnoDB最小的存儲單位是頁，默認每個頁大小是16k。
而InnoDB存儲引擎是面向行的(row-oriented)，數(shù)據(jù)按行進行存放，每個頁規(guī)定最多允許存放的行數(shù)=16k/2 - 200，即7992行。
段：如數(shù)據(jù)段、索引段、回滾段等。InnoDB存儲引擎是B+樹索引組織的，所以數(shù)據(jù)即索引，索引即數(shù)據(jù)。B+樹的葉子節(jié)點存儲的都是數(shù)據(jù)段的數(shù)據(jù)。
3.6.2、數(shù)據(jù)頁結(jié)構(gòu)[14]
名稱占用空間描述Fil Header38 byte頁的基本信息，如所屬表空間，上一頁和下一頁指針。Page Header56 byte數(shù)據(jù)頁專有的相關(guān)信息Infimun + Supremum26 byte兩個虛擬的行記錄，用于限定記錄的邊界User Records動態(tài)分配實際存儲的行記錄內(nèi)容Free Space動態(tài)調(diào)整尚未使用的頁空間Page Directory動態(tài)調(diào)整頁中某些記錄的相對位置Fil Trailer8 byte校驗頁是否完整
關(guān)于Infimun和Supremum：首次創(chuàng)建索引時，InnoDB會在根頁面中自動設(shè)置一個最小記錄和一個最高記錄，并且永遠不會刪除它們。最低記錄和最高記錄可以視為索引頁開銷的一部分。最初，它們都存在于根頁面上，但是隨著索引的增長，最低記錄將存在于第一或最低葉子頁上，最高記錄將出現(xiàn)在最后或最大關(guān)鍵字頁上。
3.6.3、行記錄結(jié)構(gòu)描述[15]
先來講講Compact行記錄格式，Compact是MySQL5.0引入的，設(shè)計目標是高效的存儲數(shù)據(jù)，讓一個頁能夠存放更多的數(shù)據(jù)，從而實現(xiàn)更快的B+樹查找。
名稱描述變長字段長度列表字段大小最多用2個字節(jié)表示，也就是最多限制長度：2^16=65535個字節(jié)；字段大小小于255字節(jié)，則用1個字節(jié)表示；NULL標志位記錄該行哪些位置的字段是null值記錄頭信息記錄頭信息信息，固定占用5個字節(jié)列1數(shù)據(jù)實際的列數(shù)據(jù)，NULL不占用該部分的空間列2數(shù)據(jù)...
記錄頭用于將連續(xù)的記錄鏈接在一起，并用于行級鎖定。
每行數(shù)據(jù)除了用戶定義的列外，還有兩個隱藏列：
- 6個字節(jié)的事務(wù)ID列；
- 7個字節(jié)的回滾指針列；
- 如果InnoDB沒有指定主鍵，還會增加一個6個字節(jié)的rowid列；
而記錄頭信 息 包 [16]含如下內(nèi)容：
名稱大小(bit)描述()1未知()1未知deleted_flag1該行是否已被刪除min_rec_flag1如果該記錄是預(yù)定義的最小記錄，則為1n_owned4該記錄擁有的記錄數(shù)heap_no13索引堆中該條記錄的排序號record_type3記錄類型：000 普通，001 B+樹節(jié)點指針，010 Infimum，011 Supremum，1xx 保留next_record16指向頁中下一條記錄
更詳細的頁結(jié)構(gòu)參考官網(wǎng)：22.2 InnoDB Page Structure
更詳細的行結(jié)構(gòu)參考官網(wǎng)：22.1 InnoDB Record Structure
更詳細的行格式參考官網(wǎng)：14.11 InnoDB Row Formats
根據(jù)以上格式，可以得出數(shù)據(jù)頁內(nèi)的記錄組織方式：
3.6.3.1、MySQL中varchar最大長度是多少
上面表格描述我們知道，一個字段最長限制是65535個字節(jié)，這是存儲長度的限制。
而MySQL中對存儲是有限制的，具體參考：8.4.7 Limits on Table Column Count and Row Size
- MySQL對每個表有4096列的硬限制，但是對于給定的表，有效最大值可能會更少；
- MySQL表的每行行最大限制為65,535字節(jié)，這是邏輯的限制；實際存儲的時候，表的物理最大行大小略小于頁面的一半。如果一行的長度少于一頁的一半，則所有行都將存儲在本地頁面內(nèi)。如果它超過一頁的一半，那么將選擇可變長度列用于外部頁外存儲，直到該行大小控制在半頁之內(nèi)為止。
而實際能夠存儲的字符是跟編碼有關(guān)的。
背景知識：
MySQL 4.0版本以下，varchar(10)，代表10個字節(jié)，如果存放UTF8漢字，那么只能存3個(每個漢字3字節(jié))；MySQL 5.0版本以上，varchar(10)，指的是10個字符，無論存放的是數(shù)字、字母還是UTF8漢字(每個漢字3字節(jié))，都可以存放10個，最大大小是65532字節(jié)；
因此，Mysql5根據(jù)編碼不同,存儲大小也不同。
那么假設(shè)我們使用的是utf8編碼，那么每個字符最多占用3個字節(jié)，也就是最多定義varchar(21845)個字符，如果是ascii編碼，一個字符相當(dāng)于一個字節(jié)，最多定義varchar(65535)個字符，下面我們驗證下。
我們嘗試創(chuàng)建一個這樣的字段：
CREATE TABLE `t10` ( `id` int(11) NOT NULL, `a` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=ascii ROW_FORMAT=Compact;alter table t10 add `str` varchar(21845) DEFAULT NULL;alter table t10 add `str` varchar(65535) DEFAULT NULL;發(fā)現(xiàn)提示這個錯誤：
mysql> alter table t10 add `str` varchar(65535) DEFAULT NULL;ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs原因是按照以上的行格式介紹，變長字段長度列表記錄也需要占用空間，占用2個字節(jié)，另外這里是允許為空字段，在8位之內(nèi)，所以NULL標志位占用1個字節(jié)，所以我們總共可以存儲的字符數(shù)是：
65535 - 2 - 2 - 4 - 4=65534
其中 -2 個字節(jié)表示變長字段列表，-1表示NULL標志位，兩個-4表示兩個int類型字段占用大小
所以實際上能夠容納的varchar大小為：65524，我們驗證下：
3.6.3.2、行記錄超過頁大小如何存儲
MySQL表的內(nèi)部表示具有65,535字節(jié)的最大行大小限制。InnoDB 對于4KB，8KB，16KB和32KB innodb_page_size 設(shè)置，表的最大行大小(適用于本地存儲在數(shù)據(jù)庫頁面內(nèi)的數(shù)據(jù))略小于頁面的一半 。如果包含 可變長度列的InnoDB 行超過最大行大小，那么將選擇可變長度列用于外部頁外存儲。
可變長度列由于太長而無法容納在B樹頁面上，這個時候會把可變長度列存儲在單獨分配的磁盤頁面上，這些頁面稱為溢出頁面，這些列稱為頁外列。頁外列的值存儲在由溢出頁面構(gòu)成的單鏈接列表中。
InnoDB存儲引擎支持四種行格式：REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。不同的行格式，對溢出的閾值和處理方式有所區(qū)別，詳細參考：14.11 InnoDB Row Formats。
COMPACT行格式處理方式
使用COMPACT行格式的表將前768個字節(jié)的變長列值(VARCHAR， VARBINARY和 BLOB和 TEXT類型)存儲在B樹節(jié)點內(nèi)的索引記錄中，其余的存儲在溢出頁上。
如果列的值等于或小于768個字節(jié)，則不使用溢出頁，因此可以節(jié)省一些I / O。
如果查過了768個字節(jié)，那么會按照如下方式進行存儲：
DYNAMIC行格式處理方式
DYNAMIC行格式提供與COMPACT行格式相同的存儲特性，但改進了超長可變長度列的存儲能力和支持大索引鍵前綴。
InnoDB 可以完全在頁外存儲過長的可變長度列值(針對 VARCHAR， VARBINARY和 BLOB和 TEXT類型)，而聚集索引記錄僅包含指向溢出頁的20字節(jié)指針。大于或等于768字節(jié)的固定長度字段被編碼為可變長度字段。
表中大字段引發(fā)的問題
如果一個表中有過多的可變長度大字段，導(dǎo)致一行記錄太長，而整個時候使用的是COMPACT行格式，那么就可能會插入數(shù)據(jù)報錯。
如，頁面大小事16k，根據(jù)前面描述我們知道，MySQL限制一頁最少要存儲兩行數(shù)據(jù)，如果很多可變長度大字段，在使用COMPACT的情況下，仍然會把大字段的前面768個字節(jié)存在索引頁中，可以算出最多支持的大字段：1024 * 16 / 2 / 768 = 10.67，那么超過10個可變長度大字段就會插入失敗了。
這個時候可以把row format改為：DYNAMIC。
3.7、索引
前面我們了解了InnoDB底層的存儲結(jié)構(gòu)，即：以B+樹的方式組織數(shù)據(jù)頁。另外了解了數(shù)據(jù)頁中的數(shù)據(jù)行的存儲方式。
而構(gòu)建B+樹索引的時候必須要選定一個或者多個字段作為索引的值，如果索引選擇的是主鍵，那么我們就稱為聚集索引，否則就是二級索引。
為什么MySQL使用B+樹？
哈希表雖然可以提供O(1)的單行數(shù)據(jù)操作性能，但卻不能很好的支持排序和范圍查找，會導(dǎo)致全表掃描；B樹可以再非葉子節(jié)點存儲數(shù)據(jù)，但是這可能會導(dǎo)致查詢連續(xù)數(shù)據(jù)的時候增加更多的I/O操作；而B+樹數(shù)據(jù)都存放在葉子節(jié)點，葉子節(jié)點通過指針相互連接，可以減少順序遍歷時產(chǎn)生的額外隨機I/O
更新詳細解釋: 為什么 MySQL 使用 B+ 樹[17]
3.7.1、聚集索引
了解到上面的底層邏輯存儲結(jié)構(gòu)之后，我們進一步來看看InnoDB是怎么通過B+樹來組織存儲數(shù)據(jù)的。
首先來介紹下聚集索引。
聚集索引
主鍵索引的InnoDB術(shù)語。
下面我們創(chuàng)建一張測試表，并插入數(shù)據(jù)，來構(gòu)造一顆B+樹：
CREATE TABLE t20 (id int NOT NULL,a int NOT NULL,b int,c int,PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t20 values(20, 1, 2, 1);insert into t20 values(40, 1, 2, 5);insert into t20 values(30, 3, 2, 4);insert into t20 values(50, 3, 6, 2);insert into t20 values(10, 1, 1, 1);可以看到，雖然我們是id亂序插入的，但是插入之后查出來的確是排序好的：
這個排序就是B+索引樹構(gòu)建的。
我們可以通過這個在線的動態(tài)演示工具來看看B+樹的構(gòu)造過程，最終結(jié)果如下：
實際存放在數(shù)據(jù)庫中的模型因頁面大小不一樣而有所不同，這里為了簡化模型，我們按照B+樹的通用模型來解釋數(shù)據(jù)的存儲結(jié)構(gòu)。
類似的，我們的數(shù)據(jù)也是這種組織形式的，該B+樹中，我們以主鍵為索引進行構(gòu)建，并且把完整的記錄存到對應(yīng)的頁下面：
其中藍色的是索引頁，橙色的是數(shù)據(jù)頁。
每個頁的大小默認為16k，如果插入新的數(shù)據(jù)行，這個時候就要申請新的數(shù)據(jù)頁了，然后挪動部分數(shù)據(jù)過去，重新調(diào)整B+樹，這個過程稱為頁分裂，這個過程會影響性能。
相反的，如果InnoDB索引頁的填充因子下降到之下MERGE_THRESHOLD，默認情況下為50％(如果未指定)，則InnoDB嘗試收縮索引樹以釋放頁面。
自增主鍵的插入是遞增順序插入的，每次添加記錄都是追加的，不涉及到記錄的挪動，不會觸發(fā)葉子節(jié)點的分裂，而一般業(yè)務(wù)字段做主鍵，往往都不是有序插入的，寫成本比較高，所以我們更傾向于使用自增字段作為主鍵。
聚集索引注意事項
- 當(dāng)在表上面定義了PRIMARY KEY之后，InnoDB會把它作為聚集索引。為此，為你的每個表定義一個PRIMARY KEY。如果沒有唯一并且非空的字段或者一組列，那么請?zhí)砑右粋€自增列；
- 如果您沒有為表定義PRIMARY KEY，則MySQL會找到第一個不帶null值的UNIQUE索引，并其用作聚集索引；
- 如果表沒有PRIMARY KEY或沒有合適的UNIQUE索引，則InnoDB 內(nèi)部會生成一個隱藏的聚集索引GEN_CLUST_INDEX，作為行ID，行ID是一個6字節(jié)的字段，隨著數(shù)據(jù)的插入而自增。
聚集索引查找
根據(jù)索引進行查找id=50的記錄，如下圖，沿著B+樹一直往下尋找，最終找到第四頁，然后把該頁加載到buffer pool中，在緩存中遍歷對比查找，由于里面的行記錄是順序組織的，所以很快就可以定位到記錄了。
3.7.2、輔助索引
除了聚集索引之外的所有索引都稱為輔助索引(二級索引)。在InnoDB中，輔助索引中每個記錄都包含該行的主鍵列以及為輔助索引指定的列。
在輔助索引中查找到記錄，可以得到記錄的主鍵索引ID，然后可以通過這個主鍵索引ID去聚集索引中搜索具體的記錄，這個過程稱為回表操作。
如果主鍵較長，則輔助索引將使用更多空間，因此具有短的主鍵是有利的。
下面我們給剛剛的表添加一個組合聯(lián)合索引
-- 添加多一個字段alter table t20 add column d varchar(20) not null default '';-- 添加一個聯(lián)合索引alter table t20 add index idx_abc(a, b, c);添加之后組合索引B+樹如下，其中索引key為abc三個字段的組合，索引存儲的記錄為主鍵ID：
覆蓋索引(Using index)
InnoDB存儲引擎支持覆蓋索引，即從輔助索引中就可以得到查詢的記錄，而不需要回表去查詢聚集索引中的記錄，從而減少大量的IO操作。下面的查詢既是用到了覆蓋索引 idx_abc：
select a, b from t20 where a > 2;執(zhí)行結(jié)果如下：
可以發(fā)現(xiàn)，Extra這一列提示Using index，使用到了覆蓋索引，掃描的行數(shù)為2。注意：這里的掃描行數(shù)指的是MySQL執(zhí)行器從引擎取到兩條記錄，引擎內(nèi)部可能會遍歷到多條記錄進行條件比較。
最左匹配原則
由于InnoDB索引式B+樹構(gòu)建的，因此可以利用索引的“最左前綴”來定位記錄。
也就是說，不僅僅是用到索引的全部定義字段會走索引，只要滿足最左前綴，就可以利用索引來加速檢索。這個最左前綴可以是聯(lián)合索引的最左n個字段。
索引條件下推(Using index condition)
索引條件下推 Index Condition Pushdown (ICP)，是針對MySQL使用索引從表中檢索行的情況的一種優(yōu)化。
為什么叫下推呢，就是在滿足要求的情況下，把索引的條件丟給存儲引擎去判斷，而不是把完整的記錄傳回MySQL Server層去判斷。
ICP支持range, ref, eq_ref, 和 ref_or_null類型的查找，支持MyISAM和InnoDB存儲引擎。
不能將引用子查詢的條件下推，觸發(fā)條件不能下推。詳細規(guī)則參考：Index Condition Pushdown
如果不使用ICP，則存儲引擎將遍歷索引以在聚集索引中定位行，并將結(jié)果返回給MySQL Server層，MySQL Server層繼續(xù)根據(jù)WHERE條件進行篩選行。
啟用ICP后，如果WHERE可以僅使用索引中的列來評估部分條件，則MySQL Server層會將這部分條件壓入WHERE條件下降到存儲引擎。然后，存儲引擎通過使用索引條目來判斷索引條件，在滿足條件的情況下，才回表去查找記錄返回給MySQL Server層。
ICP的目標是減少回表掃描的行數(shù)，從而減少I / O操作。對于InnoDB表，ICP僅用于二級索引。
使用索引下推的時候，執(zhí)行計劃中的Extra會提示：Using index condition，而不是Using index，因為必須回表查詢整行數(shù)據(jù)。Using index代表使用到了覆蓋索引。
3.8、InnoDB Data Directory
InnoDB數(shù)據(jù)字典(Data Directory)存放于系統(tǒng)表空間中，主要包含元數(shù)據(jù)，用于追蹤表、索引、表字段等信息。由于歷史的原因，InnoDB數(shù)據(jù)字典中的元數(shù)據(jù)與.frm文件中的元數(shù)據(jù)重復(fù)了。
3.9、Doublewrite Buffer
雙寫緩沖區(qū)(Doublewrite Buffer)是一個存儲區(qū)，是InnoDB在tablespace上的128個頁(2個區(qū))，大小是2MB[18]。
版本區(qū)別：在MySQL 8.0.20之前，doublewrite緩沖區(qū)存儲區(qū)位于InnoDB系統(tǒng)表空間中。從MySQL 8.0.20開始，doublewrite緩沖區(qū)存儲區(qū)位于doublewrite文件中。
本文基于MySQL 5.7編寫。
操作系統(tǒng)寫文件是以4KB為單位的，那么每寫一個InnoDB的page到磁盤上，操作系統(tǒng)需要寫4個塊。如果寫入4個塊的過程中出現(xiàn)系統(tǒng)崩潰，那么會導(dǎo)致16K的數(shù)據(jù)只有一部分寫是成功的，這種情況下就是partial page write(部分頁寫入)問題。
InnoDB這個時候是沒法通過redo log來恢復(fù)的，因為這個時候頁面的Fil Trailer(Fil Trailer 主要存放FIL_PAGE_END_LSN，主要包含頁面校驗和以及最后的事務(wù))中的數(shù)據(jù)是有問題的。
為此，每當(dāng)InnoDB將頁面寫入到數(shù)據(jù)文件中的適當(dāng)位置之前，都會首先將其寫入雙寫緩沖區(qū)。只有將緩沖區(qū)安全地刷新到磁盤后，InnoDB才會將頁面寫入最終的數(shù)據(jù)文件。
如果在頁面寫入過程中發(fā)生操作系統(tǒng)或者mysqld進程崩潰，則InnoDB可以在崩潰恢復(fù)期間從雙寫緩沖區(qū)中找到頁面的完好副本用于恢復(fù)。恢復(fù)時，InnoDB掃描雙寫緩沖區(qū)，并為緩沖區(qū)中的每個有效頁面檢查數(shù)據(jù)文件中的頁面是否完整。
如果系統(tǒng)表空間文件(“ ibdata文件 ”)位于支持原子寫的Fusion-io設(shè)備上，則自動禁用雙寫緩沖，并且將Fusion-io原子寫用于所有數(shù)據(jù)文件。
3.10、Redo Log
重做日志(Redo Log)主要適用于數(shù)據(jù)庫的崩潰恢復(fù)，用于實現(xiàn)數(shù)據(jù)的完整性。
重做日志由兩部分組成：
- 重做日志緩沖區(qū) Log Buffer；
- 重做日志文件，重做日志文件在磁盤上由兩個名為ib_logfile0和ib_logfile1的物理文件表示。
為了實現(xiàn)數(shù)據(jù)完整性，在臟頁刷新到磁盤之前，必須先把重做日志寫入到磁盤。除了數(shù)據(jù)頁，聚集索引、輔助索引以及Undo Log都需要記錄重做日志。
3.10.1、Redo Log在事務(wù)中的寫入時機
在事務(wù)中，除了寫Redo log，還需要寫binlog，為此，我們先來簡單介紹下binlog。
3.10.1.1、binlog
全寫：Binary Log，二進制log。二進制日志是一組日志文件。其中包含有關(guān)對MySQL服務(wù)器實例進行的數(shù)據(jù)修改的信息。
Redo Log是InnoDB引擎特有的，而binlog是MySQL的Server層實現(xiàn)的，所有引擎都可以使用。
Redo Log的文件是循環(huán)寫的，空間會用完，binlog日志是追加寫的，不會覆蓋以前的日志。
binlog主要的目的：
- 主從同步，主服務(wù)器將二進制日志中包含的事件發(fā)送到從服務(wù)器，從服務(wù)器執(zhí)行這些事件，以保持和主服務(wù)器相同的數(shù)據(jù)更改；
- 某些數(shù)據(jù)恢復(fù)操作需要使用二進制日志，還原到某一個備份點。
binlog主要是用于主從同步和數(shù)據(jù)恢復(fù)，Redo Log主要是用于實現(xiàn)事務(wù)數(shù)據(jù)的完整性，讓InnoDB具有不會丟失數(shù)據(jù)的能力，又稱為crash-safe。
binlog日志的兩種記錄形式：
- 基于SQL的日志記錄：事件包含產(chǎn)生數(shù)據(jù)更改(插入，新增，刪除)的SQL語句；
- 基于行的日志記錄：時間描述對單個行的更改。
混合日志記錄默認情況下使用基于語句的日志記錄，但根據(jù)需要自動切換到基于行的日志記錄。
3.10.1.2、Redo Log在事務(wù)中的寫入時機
簡單的介紹完binlog，我們再來看看Redo Log的寫入流程。
假設(shè)我們這里執(zhí)行一條sql
update t20 set a=10 where id=1;執(zhí)行流程如下：
3.10.2、如何保證數(shù)據(jù)不丟失
前面我們介紹Log Buffer的時候，提到過，為了保證數(shù)據(jù)不丟失，我們需要執(zhí)行以下操作：
- 如果啟用了binlog，則設(shè)置：sync_binlog=1;
- innodb_flush_log_at_trx_commit=1;
sync_binlog=0：表示每次提交事務(wù)都只 write，不 fsync；sync_binlog=1：表示每次提交事務(wù)都會執(zhí)行 fsync；sync_binlog=N(N>1) ：表示每次提交事務(wù)都 write，但累積 N 個事務(wù)后才 fsync。
這兩個的作用相當(dāng)于在上面的流程最后一步，提交事務(wù)接口返回Server層之前，把binlog cache和log buffer都fsync到磁盤中了，這樣就保證了數(shù)據(jù)的落盤，不會丟失，即使奔潰了，也可以通過binlog和redo log恢復(fù)數(shù)據(jù)相關(guān)流程如下：
在磁盤和內(nèi)存中的處理流程如下面編號所示：
其中第四步log buffer持久化到磁盤的時機為：
- log buffer占用的空間即將達到innodb_log_buffer_size一半的時候，后臺線程主動寫盤；
- InnoDB后臺有個線程，每隔1秒會把log buffer刷到磁盤；
- 由于log buffer是所有線程共享的，當(dāng)其他事務(wù)線程提交時也會導(dǎo)致已寫入log buffer但還未提交的事務(wù)的redo log一起刷新到磁盤
其中第五步：臟頁刷新到磁盤的時機為：
- 系統(tǒng)內(nèi)存不足，需要淘汰臟頁的時候，要把臟頁同步回磁盤；
- MySQL空閑的時候；
- MySQL正常關(guān)閉的時候，會把臟頁flush到磁盤。
參數(shù)innodb_max_dirty_pages_pct是臟頁比例上限，默認值是 75%。
為什么第二步 redo log prepare狀態(tài)也要寫磁盤？
因為這里先寫了，才能確保在把binlog寫到磁盤后崩潰，能夠恢復(fù)數(shù)據(jù)：如果判斷到redo log是prepare狀態(tài)，那么查看是否存XID對應(yīng)的binlog，如果存在，則表示事務(wù)成功提交，需要用prepare狀態(tài)的redo log進行恢復(fù)。
這樣即使崩潰了，也可以通過redo log來進行恢復(fù)了，恢復(fù)流程如下：
Redo Log是循環(huán)寫的，如下圖:
- writepos記錄了當(dāng)前寫的位置，一邊寫位置一邊往前推進，當(dāng)writepos與checkpoint重疊的時候就表示logfile寫滿了，綠色部分表示是空閑的空間，紅色部分是寫了redo log的空間；
- checkpoint處標識了當(dāng)前的LSN，每當(dāng)系統(tǒng)崩潰重啟，都會從當(dāng)前checkpoint這個位置執(zhí)行重做日志，根據(jù)重做日志逐個確認數(shù)據(jù)頁是否沒問題，有問題就通過redo log進行修復(fù)。
LSN Log Sequence Number的縮寫。代表日志序列號。在InnoDB中，LSN占用8個字節(jié)，單調(diào)遞增，LSN的含義：
重做日志寫入的總量；checkpoint的位置；頁的版本；
除了重做日志中有LSN，每個頁的頭部也是有存儲了該頁的LSN，我們前面介紹頁面格式的時候有介紹過。
在 頁中LSN表示該頁最后刷新時LSN的大小。[19]
3.11、Undo Logs
上面說的redo log記錄了事務(wù)的行為，可以通過其對頁進行重做操作，但是食物有時候需要進行回滾，這時候就需要undo log了。[20]
關(guān)于Undo Log的存儲：InnoDB中有回滾段(rollback segment)，每個回滾段記錄1024個undo log segment，在每個undo log segment段中進行申請undo頁。系統(tǒng)表空間偏移量為5的頁記錄了所有的rollback segment header所在的頁。
3.11.1、undo log的格式
根據(jù)行為不同分為兩種：
insert undo log
insert undo log：只對事務(wù)本身可見，所以insert undo log在事務(wù)提交后可直接刪除，無需執(zhí)行purge操作；
insert undo log主要記錄了：
next記錄下一個undo log的位置type_cmplundo的類型：insert or update*undo_no記錄事務(wù)的ID*table_id記錄表對象*len1, col1記錄列和值*len2, col2記錄列和值......start記錄undo log的開始位置
假設(shè)在事務(wù)1001中，執(zhí)行以下sql，t20的table_id為10：
insert into t20(id, a, b, c, d) values(12, 2, 3, 1, "init")那么對應(yīng)會生成一條undo log：
update undo log
update undo log：執(zhí)行update或者delete會產(chǎn)生undo log，會影響已存在的記錄，為了實現(xiàn)MVCC(后邊介紹)，update undo log不能再事務(wù)提交時立刻刪除，需要將事務(wù)提交時放入到history list上，等待purge線程進行最后的刪除操作。
update undo log主要記錄了：
next記錄下一個undo log的位置type_cmplundo的類型：insert or update*undo_noundo日志編號*table_id記錄表對象info_bits*DATA_TRX_ID事務(wù)的ID*DATA_ROLL_PTR回滾指針*len1, i_col1n_unique_index*len2, i_col2...n_update_fields以下是update vector信息，表示update操作導(dǎo)致發(fā)送改變的列*pos1, *len1, u_old_col1*pos2, *len2, u_old_col2...n_bytes_below*pos, *len, col1*pos, *len, col2...start記錄undo log的開始位置
假設(shè)在事務(wù)1002中，執(zhí)行以下sql，t20的table_id為10：
update t20 set d="update1" where id=60;那么對應(yīng)會生成一條undo log：
如上圖，每回退應(yīng)用一個undo log，就回退一個版本，這就是MVCC(Multi versioning concurrency control)的實現(xiàn)原理。
下面我們在執(zhí)行一個delete sql：
delete from t20 where id=60;對應(yīng)的undo log變?yōu)槿缦?#xff1a;
如上圖，實際的行記錄不會立刻刪除，而是在行記錄頭信息記錄了一個deleted_flag標志位。最終會在purge線程purge undo log的時候進行實際的刪除操作，這個時候undo log也會清理掉。
3.11.2、MVCC實現(xiàn)原理
如上圖所示，MySQL只會有一個行記錄，但是會把每次執(zhí)行的sql導(dǎo)致行記錄的變動，通過undo log的形式記錄起來，undo log通過回滾指針連接在一起，這樣我們想回溯某一個版本的時候，就可以應(yīng)用undo log，回到對應(yīng)的版本視圖了。
我們知道InnoDB是支持RC(Read Commit)和RR(Repeatable Read)事務(wù)隔離級別的，而這個是通過一致性視圖(consistent read view)實現(xiàn)的。
一個事務(wù)開啟瞬間，所有活躍的事務(wù)(未提交)構(gòu)成了一個視圖數(shù)組，InnoDB就是通過這個視圖數(shù)組來判斷行數(shù)據(jù)是否需要undo到指定的版本：
RR事務(wù)隔離級別
假設(shè)我們使用了RR事務(wù)隔離級別。我們看個例子：
如下圖，假設(shè)id=60的記錄a=1
事務(wù)C啟動的瞬間，活躍的事務(wù)如下圖黃色部分所示：
也就是對于事務(wù)A、事務(wù)B、事務(wù)C，他們能夠看到的數(shù)據(jù)只有是行記錄中的最大事務(wù)IDDATA_TRX_ID<=11的，如果大于，那么只能通過undo進行回滾了。如果TRX_ID=當(dāng)前事務(wù)id，也可以看到，即看到自己的改動。
另外有一個需要注意的：
- 在RR隔離級別下，當(dāng)事務(wù)更新事務(wù)的時候，只能用當(dāng)前讀來獲取最新的版本數(shù)據(jù)來更新，如果當(dāng)前記錄的行鎖被其他事務(wù)占用，就需要進入所等待；
- 在RC隔離級別下，每個語句執(zhí)行都會計算出新的一致性視圖。
所以我們分析上面的例子的執(zhí)行流程：
- 事務(wù)C執(zhí)行update，執(zhí)行當(dāng)前讀，拿到的a=1，然后+1，最終a=2，同時添加一個TRX_ID=11的undo log；
- 事務(wù)B執(zhí)行select，使用快照讀，記錄的DATA_TRX_ID > 11，所以需要通過undo log回滾到DATA_TRX_ID=11的版本，所以拿到的a是1；
- 事務(wù)B執(zhí)行update，需要使用當(dāng)前讀，拿到最新的記錄，a=2，然后加1，最終a=3；
- 事務(wù)B執(zhí)行select，拿到當(dāng)前最新的版本，為自己的事務(wù)id，所以得到a=3；
- 事務(wù)A執(zhí)行select，使用快照讀，記錄的DATA_TRX_ID > 11，所以需要通過undo log回滾到DATA_TRX_ID=11的版本，所以拿到的a是1。
- 如果是RC隔離級別，執(zhí)行select的時候會計算出新的視圖，新的視圖能夠看到的最大事務(wù)ID=14，由于事務(wù)B還沒提交，事務(wù)C提交了，所以可以得到a=2：
總結(jié)
- 數(shù)據(jù)完整性依靠：redo log
- 事務(wù)隔離級別的實現(xiàn)依靠MVCC，MVCC依靠undo log實現(xiàn)
- IO性能提升方式：buffer pool加快查詢效率和普通索引更新的效率，log buffer對日志寫的性能提升
- 查詢性能提升依賴于索引，底層用頁存儲，字段越小頁存儲越多行記錄，查詢效率越快；自增字段作為聚集索引可以加快插入操作；
- 故障恢復(fù)：雙寫緩沖區(qū)、redo log
- 主從同步：binlog
本文內(nèi)容比較多，看完之后需要多梳理，最后大家可以對照著這個思維導(dǎo)圖回憶一下，這些內(nèi)容是否都記住了：
這篇文章的內(nèi)容就差不多介紹到這里了，能夠閱讀到這里的朋友真的是很有耐心，為你點個贊。
本文為arthinking基于相關(guān)技術(shù)資料和官方文檔撰寫而成，確保內(nèi)容的準確性，如果你發(fā)現(xiàn)了有何錯漏之處，煩請高抬貴手幫忙指正，萬分感激。
如果您覺得讀完本文有所收獲的話，可以關(guān)注我的賬號，或者點贊吧，碼字不易，您的支持就是我寫作的最大動力，再次感謝！
總結(jié)
以上是生活随笔為你收集整理的mysql多大_洞悉MySQL底层架构：游走在缓冲与磁盘之间的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python的requests模块功能_
- 下一篇： c++二进制转十进制_二进制，八进制，十