1.?sql操作流程圖

2.?流程圖解析

1）前臺(tái)操作觸發(fā)mysql服務(wù)器執(zhí)行請(qǐng)求

前臺(tái)用戶各種操作觸發(fā)mysql執(zhí)行想帅，通過(guò)web項(xiàng)目中自帶的數(shù)據(jù)庫(kù)連接池:dbcp膝迎、c3p0析恢、druid等液样，與數(shù)據(jù)庫(kù)服務(wù)器建立網(wǎng)絡(luò)連接

數(shù)據(jù)庫(kù)連接池中的線程監(jiān)聽(tīng)到請(qǐng)求后铐达，將接收到的sql語(yǔ)句通過(guò)sql接口響應(yīng)給查詢解析器委粉，查詢解析器按照sql語(yǔ)法解析出查詢哪些表的哪個(gè)字段，查詢條件是啥娶桦；再通過(guò)查詢優(yōu)化器處理贾节，選擇該sql最優(yōu)的一套執(zhí)行計(jì)劃，然后執(zhí)行器負(fù)責(zé)調(diào)用存儲(chǔ)引擎的一系列接口衷畦，執(zhí)行該計(jì)劃完成整個(gè)sql語(yǔ)句的執(zhí)行

2）InnoDB存儲(chǔ)引擎-緩沖池完成基本更新操作

具體執(zhí)行這些執(zhí)行計(jì)劃得要存儲(chǔ)引擎來(lái)完成栗涂，如圖所示，首次更新user表中id=10的這條數(shù)據(jù)祈争，緩沖池一開(kāi)始肯定沒(méi)有該條數(shù)據(jù)的斤程，得要先從磁盤中將被更新的原始數(shù)據(jù)加載到緩沖池中

同時(shí)為了保證并發(fā)更新數(shù)據(jù)安全問(wèn)題，會(huì)對(duì)這條數(shù)據(jù)先加鎖菩混，防止其他事務(wù)更新

接著將更新前的值先備份到undo log 中忿墅，便于回滾

最后更新緩存頁(yè)中的數(shù)據(jù)為最新的數(shù)據(jù)，至此就完成了在緩沖池中的執(zhí)行流程

3）Redo Log 和 BinLog保證事務(wù)的可靠性

緩沖池中更新完數(shù)據(jù)后沮峡，需要將本次更新的數(shù)據(jù)順序?qū)懙絩edo log和bin log日志中(此時(shí)信息還在內(nèi)存中疚脐，后續(xù)的刷盤策略如圖所示)，一般為了保證數(shù)據(jù)不丟失會(huì)配置雙寫策略邢疙，redo log 落盤后棍弄，寫bin log 落盤，再將bin log 的文件名疟游、文件所在路徑信息以及commit標(biāo)記同步順序?qū)懙絩edo log中(其中以commit標(biāo)記是否更新到redo log中呼畸，是判定事務(wù)是否提交成功的一個(gè)重要標(biāo)準(zhǔn))，redo log和bin log分別在物理和邏輯層面為本次事務(wù)颁虐、提供數(shù)據(jù)上的一致性保障蛮原，如圖

4）將事務(wù)的操作持久化

前面的一系列操作后，InnoDB存儲(chǔ)引擎后臺(tái)還有一個(gè)IO線程另绩，會(huì)在數(shù)據(jù)庫(kù)壓力的低峰期間儒陨，將緩沖池中被事務(wù)更新花嘶、但還沒(méi)來(lái)得及寫到磁盤中的數(shù)據(jù)(臟數(shù)據(jù)，因?yàn)榇疟P數(shù)據(jù)和內(nèi)存數(shù)據(jù)已經(jīng)不一致了)給刷到磁盤中框全，完成事務(wù)的持久化，如圖

3.?buffer pool

1）內(nèi)存結(jié)構(gòu)

查詢參數(shù)：innodb_buffer_pool_size干签，默認(rèn)大小128M津辩。

注意：buffer pool中緩存頁(yè)對(duì)應(yīng)磁盤中的數(shù)據(jù)頁(yè)，另外還有一塊內(nèi)存關(guān)于數(shù)據(jù)的描述信息容劳，大概是數(shù)據(jù)頁(yè)的5%左右喘沿， 800字節(jié)左右，所以一塊buffer pool實(shí)際內(nèi)存要比128M大一點(diǎn)竭贩。

2）3大鏈表

l?free鏈表

buffer pool中并沒(méi)有單獨(dú)開(kāi)辟空間來(lái)維護(hù)free鏈表蚜印，而是在 描述數(shù)據(jù) 中維護(hù)著兩個(gè)指針，free_pre和free_next留量，分別向上一個(gè)free鏈表的節(jié)點(diǎn)窄赋，和下一個(gè)free鏈表的節(jié)點(diǎn)，構(gòu)成了free鏈表楼熄。

數(shù)據(jù)加載到buffer pool中的緩存頁(yè)中忆绰，把free鏈表上對(duì)那個(gè)的緩存頁(yè)節(jié)點(diǎn)刪除掉。

判斷數(shù)據(jù)有沒(méi)有加載到buffer pool中可岂，還需要引入數(shù)據(jù)庫(kù)自己維護(hù)的hash表數(shù)據(jù)結(jié)構(gòu)错敢，會(huì)用 表空間號(hào)+數(shù)據(jù)頁(yè)號(hào)，作為一個(gè)key缕粹，緩存頁(yè)的地址作為value稚茅。

l?flush鏈表

數(shù)據(jù)結(jié)構(gòu)同free鏈表，用于刷臟平斩，臟數(shù)據(jù)指的是緩存頁(yè)中增刪改的數(shù)據(jù)亚享，把flush鏈表上的緩存頁(yè)數(shù)據(jù)刷到磁盤

避免每次刷盤都刷整個(gè)buffer pool中所有數(shù)據(jù)。

數(shù)據(jù)刷入磁盤之后绘面，該緩存頁(yè)從flush鏈表上移除虹蒋，重新添加到free鏈表上。

l?lru鏈表（least recently used）

數(shù)據(jù)結(jié)構(gòu)同free鏈表飒货，記錄著緩存頁(yè)數(shù)據(jù)被操作的命中率魄衅，如果一個(gè)緩存頁(yè)的數(shù)據(jù)經(jīng)常被執(zhí)行增刪改操作，稱之為高命中塘辅，排在鏈表頭部晃虫，相反則排在尾部。

一條數(shù)據(jù)被操作的時(shí)候扣墩，該緩存頁(yè)會(huì)被提升到lru鏈表的頭部哲银。

如果緩存頁(yè)不夠的話扛吞，會(huì)把lru尾部不常操作的數(shù)據(jù)刷入到磁盤，重新把緩存頁(yè)添加到free鏈表中荆责。

注意：由于mysql的預(yù)讀機(jī)制和全表掃描滥比，經(jīng)常會(huì)把相鄰的數(shù)據(jù)頁(yè)和整個(gè)表的數(shù)據(jù)頁(yè)加載到buffer pool中，引起的問(wèn)題就是根本不會(huì)操作的數(shù)據(jù)占據(jù)了lru鏈表比較靠前的位置做院，而可能操作的數(shù)據(jù)被擠到了尾部盲泛，從而造成了尾部數(shù)據(jù)重新加載。

lru鏈表優(yōu)化

參數(shù)：冷熱數(shù)據(jù)比例innodb_old_blocks_pct?默認(rèn)37

??????冷區(qū)數(shù)據(jù)移動(dòng)到熱區(qū)數(shù)據(jù)的時(shí)間閾值innodb_old_blocks_time?默認(rèn)1000毫秒

4.?undo log

buffer pool中更新事務(wù)未提交的數(shù)據(jù)键耕，mysql就宕機(jī)了寺滚，此時(shí)數(shù)據(jù)就需要回滾到未更新之前的狀態(tài)，引入了undo log屈雄，記錄了當(dāng)前事務(wù)操作的逆向操作(也就是操作之前的數(shù)據(jù))村视，保證事務(wù)失敗后數(shù)據(jù)的回滾。

在磁盤數(shù)據(jù)頁(yè)加載到buffer pool中緩存頁(yè)之后酒奶，數(shù)據(jù)更新之前蚁孔，把逆向日志記錄到redo log中。

5.?redo log

buffer pool中更新事務(wù)提交的數(shù)據(jù)惋嚎，如果還沒(méi)來(lái)得及刷入磁盤勒虾，就斷電了，這時(shí)候的數(shù)據(jù)在內(nèi)存中瘸彤，斷掉就丟失了修然；所以引入了redo log機(jī)制，保證事務(wù)提交的時(shí)候质况，以日志的形式記錄到redo log文件中愕宋，此時(shí)斷電，重啟后只需要把之前的事務(wù)根據(jù)redo log重做一次就好结榄，保證事務(wù)提交的數(shù)據(jù)不丟失中贝。

而且redo log采取的是日志追加的順序?qū)懀阅鼙人⒈P的隨機(jī)寫高很多臼朗。

redo log格式：--日志類型+表空間號(hào)+數(shù)據(jù)頁(yè)號(hào)+偏移量+修改幾個(gè)字節(jié)的值+具體的值邻寿。

注意：redo log 并不是一條一條的追加，而是引入了redo log block的數(shù)據(jù)結(jié)構(gòu)视哑，512k绣否，類似于字節(jié)流設(shè)置多少字節(jié)操作一次一樣；mysql啟動(dòng)的時(shí)候就會(huì)在buffer pool中申請(qǐng)一個(gè)redo log buffer空間用來(lái)存放redo log block，默認(rèn)16M。

redo log buffer何時(shí)刷盤呢放航？

1）redo log buffer 寫入的數(shù)據(jù)超過(guò)了總空間的一半

2）Innodb_flush_log_at_trx參數(shù)控制事務(wù)提交后的幾種刷盤策略

3）后臺(tái)線程每隔1s刷盤一次

4）mysql關(guān)閉的時(shí)候