MySQL要點總結一

大綱

一.InnoDB的內存結構和更新機制

二.InnoDB的存儲模型

三.并發事務原理

四.索引原理和索引優化

一.InnoDB的內存模型

1.SQL的執行流程

2.InnoDB的內存模型

3.Buffer Pool中的空閑緩存頁與free鏈表

4.Buffer Pool中的臟頁和flush鏈表

5.Buffer Pool通過LRU鏈表來淘汰緩存頁

6.Buffer Pool的緩存頁以及幾個鏈表總結

7.LRU鏈表的冷數據區域的緩存頁何時刷盤

8.增大Buffer Pool來提升MySQL的并發能力

1.SQL的執行流程

MySQL驅動 -> 數據庫連接池 -> 網絡IO線程 -> SQL接口 -> 查詢解析器 -> 查詢優化器 -> 執行器 -> 調用存儲引擎接口

SQL的執行流程：

一.由MySQL工作線程去監聽網絡請求和讀取網絡連接的SQL數據

二.MySQL工作線程讀取出SQL語句后會將SQL語句轉交給SQL接口去執行

三.通過查詢解析器Parser解析SQL語句讓MySQL能看得懂SQL邏輯

四.通過查詢優化器Optimizer選擇最優的查詢路徑

五.由執行器按照查詢優化器選擇的執行計劃不停調用存儲引擎接口

六.存儲引擎接口會查詢內存緩存數據、查詢磁盤數據、更新磁盤數據等

2.InnoDB的內存模型

Buffer Pool就是數據庫的一個內存組件，里面緩存了磁盤上的真實數據。當執行更新時，會寫undo日志、修改Buffer Pool數據、寫redo日志。當提交事務時，會將redo日志刷磁、binlog刷盤、添加commit標記。最后后臺IO線程會隨機把Buffer Pool里的臟數據刷入到磁盤數據文件中。

(1)緩沖池Buffer Pool的默認大小是128MB

緩沖池Buffer Pool的大小根據服務器的配置來調整。比如服務器的配置是16核32GB，可以給緩沖池Buffer Pool分配20GB內存。

(2)數據頁是MySQL抽象出來的數據單位

磁盤文件中有很多數據頁，每一頁中放了很多行數據。如果數據庫要更新某一行數據，首先會找到這行數據所在的數據頁，然后把這行數據頁加載到緩沖池Buffer Pool中。緩沖池Buffer Pool中存放的一個一個的數據頁，也被稱為緩存頁。數據頁默認大小為16KB，數據頁和緩存頁的大小是一樣的。

(3)Buffer Pool中每個緩存頁都有對應的描述數據

描述數據包括：緩存頁所屬的表空間、數據頁的編號、緩存頁在Buffer Pool中的地址等。每個緩存頁的描述數據放Buffer Pool最前面，各個緩存頁放在后面。Buffer Pool里的一個描述數據大小相當于一個緩存頁的5%，約800字節。

3.Buffer Pool中的空閑緩存頁與free鏈表

數據庫的Buffer Pool里會包含很多個緩存頁，同時每個緩存頁還有對應的描述數據。

數據庫啟動時，會按照設置的Buffer Pool大小，去操作系統申請一塊內存區域，作為Buffer Pool的內存區域。申請完畢后，數據庫會按照默認的緩存頁大小及對應的描述數據大小，在Buffer Pool中劃分一個個緩存頁和對應的描述數據。

然后當數據庫把Buffer Pool劃分完畢后，里面的緩存頁都是空的。需要等數據庫運行起來后執行增刪改查操作時：才會把對應的數據頁從磁盤里讀取出來，放入Buffer Pool中的緩存頁里。

(1)數據庫啟動時會按照設置的Buffer Pool大小向OS申請內存

當數據庫向OS申請到設置的Buffer Pool大小的內存后，就會在緩沖池中劃分出一個個空閑緩存頁和相應的描述數據塊。

(2)Buffer Pool有一個叫free鏈表的雙向鏈表

free鏈表的每個節點是一個空閑緩存頁的描述數據塊的地址，通過free鏈表可知哪些緩存頁是空閑的。

(3)根據free鏈表的節點可得到一個空閑緩存頁

從free鏈表中獲取一個節點后，根據該節點就能找到對應的空閑緩存頁。接著就可以將磁盤中的數據頁讀取到該空閑緩存頁里。同時把該數據頁的描述數據寫到該空閑緩存頁對應的描述數據塊里。以及把表空間號 + 數據頁號作為key，緩存頁地址作為value，寫到哈希表。這樣下次讀取該數據頁時可通過key查哈希表，直接從緩沖池里進行讀取。

(4)增刪改查一條數據時InnoDB引擎會怎么處理

首先InnoDB會獲取到對應數據的"表空間號 + 數據頁號"。然后根據"表空間號 + 數據頁號"作為key，去哈希表中進行查詢。如果能查到緩存頁地址，則去Buffer Pool中讀取對應的緩存頁數據。如果哈希表查不到，則說明要將磁盤的數據頁讀取到緩沖區的緩存頁里。

于是會先從free鏈表里獲取一個節點，然后找到其描述數據塊的地址。通過該地址可得到一個空閑緩存頁，就能把數據頁讀取到該空閑緩存頁里。同時會把描述數據也寫到該緩存頁的描述數據塊里，以及把表空間號 + 數據頁號作為key，緩存頁地址作為value，寫到哈希表。

4.Buffer Pool中的臟頁和flush鏈表

MySQL在執行增刪改語句時，如果在哈希表中發現數據頁沒有緩存，則會基于free鏈表找到一個空閑的緩存頁，然后將數據頁讀取到緩存頁里。如果在哈希表中發現數據頁已緩存，那么會直接使用緩存頁。

因此，無論如何，要更新的數據頁都會在Buffer Pool的緩存頁里。MySQL是基于Buffer Pool內存來執行具體的增刪改查操作的。

所以，當MySQL去更新Buffer Pool的緩存頁中的數據時，一旦更新完，則緩存頁里的數據和磁盤上數據頁的數據就不一致了。這時就說該緩存頁是臟數據，或者臟頁。

在Buffer Pool里，有些緩存頁經過修改是臟頁，有些則只有查而不是臟頁。所以為了方便數據庫從緩存頁中區分出臟頁，數據庫引入了一個跟free鏈表類似的flush鏈表。

凡是被修改過的緩存頁，都會把它的描述數據塊加入到flush鏈表中。flush的意思就是這些都是臟頁，后續都是要flush刷新到磁盤上去的。

(1)通過free鏈表來管理所有空閑的數據頁

加載磁盤的數據頁時，先通過free鏈表拿到空閑的緩存頁地址，然后再把磁盤的數據頁寫到這個Buffer Pool中的緩存頁里。

(2)通過哈希表來管理在Buffer Pool緩存的數據頁

根據哈希表可快速從Buffer Pool查出緩存的數據頁。

(3)通過flush鏈表來管理更新后等待被刷盤的緩存頁

free鏈表和flush鏈表都通過使用地址指針來大大減少內存的占用。free鏈表和flush鏈表的節點都是由緩存頁的描述數據塊來實現的。free鏈表和flush鏈表都通過兩個指針來構成雙向鏈表。

5.Buffer Pool通過LRU鏈表來淘汰緩存頁

要知道哪些緩存頁經常被訪問、哪些緩存頁很少被訪問，可借助LRU鏈表。LRU就是Least Recently Used，最近最少使用的意思。

(1)簡單LRU鏈表的工作原理

假設InnoDB從磁盤加載一個數據頁到緩存頁時，就把這個緩存頁的描述數據塊放到LRU鏈表頭部去。

那么只要一個緩存頁有數據，那么該緩存頁就會在LRU里。并且最新加載數據的緩存頁，會被放到LRU鏈表的頭部。

假設某個緩存頁的描述數據塊本來在LRU鏈表的尾部，后面只要查詢或者修改了這個緩存頁的數據，也會把其描述數據塊挪動到LRU鏈表頭部。

總之，就是保證最近被訪問過的緩存頁，一定在LRU鏈表的頭部。這樣當緩沖區沒有空閑的緩存頁時，可以在LRU鏈表尾部找一個緩存頁。而這個緩存頁就是最近最少被訪問的那個緩存頁。然后把LRU鏈表尾部的那個緩存頁刷入磁盤從而騰出一個空閑的緩存頁，最后把需要的磁盤數據頁加載到這個空閑的緩存頁中即可。

這個LRU鏈表需要一定長度，不能只有2個節點。否則如果先是節點1被訪問100次，接著到節點2被訪問。這樣雖然鏈表尾部是節點1，但實際上節點1是最近最少被訪問的。

(2)簡單LRU鏈表可能存在的問題

問題一：預讀機制導致相鄰數據頁也一塊被加載到緩沖池。此時在LRU鏈表中排前面的，可能都是通過預讀機制加載進來的。

問題二：全表掃描可能會一下子把一個表的所有數據頁都加載到緩沖池，此時在LRU鏈表中排前面的，可能都是通過全表掃描加載進來的。

觸發預讀機制的情況：

情況一：參數innodb_read_ahead_threshold的默認值是56。如果順序訪問一個區里多個數據頁，訪問的數據頁的數量超過此閾值。那么就會觸發預讀機制，將下一個相鄰區中所有數據頁加載到緩沖池。

情況二：如果緩沖池中緩存了一個區里的13個連續的被頻繁訪問的數據頁，那么就會觸發預讀機制，將這個區里其他數據頁也加載到緩沖池。這種情況由參數innodb_random_read_ahead控制，默認關閉。

(3)基于冷熱數據分離思想設計LRU鏈表

這套冷熱數據分離的機制包含三個方案：

方案一：緩存頁分冷熱數據加載

方案二：冷數據轉化為熱數據進行時間限制

方案三：淘汰緩存頁時優先淘汰冷數據區域

為解決簡單LRU鏈表帶來的預讀和全表掃描問題，InnoDB設計LRU鏈表時用了冷熱數據分離的思想。InnoDB的LRU鏈表，會被拆分為兩個部分。一部分是熱數據，一部分是冷數據。冷熱數據的比例由innodb_old_blocks_pct參數控制，默認是37。

當數據頁第一次被加載到內存時，緩存頁對應的描述數據塊節點會被放在LRU鏈表的冷數據區域的頭部。被加載到內存的數據頁，如果在默認1s后繼續被訪問，則該緩存頁對應的描述數據塊節點會被挪動到熱數據區域的鏈表頭部。對應的innodb_old_blocks_time參數默認就是設置為1s。

如果數據加載到緩存頁之后過了1s+的時間，該緩存頁被訪問，則對應的描述數據塊會被放入熱數據區域的鏈表頭部。如果數據加載到緩存頁之后在1s內，該緩存頁被訪問，則對應的描述數據塊不會被放入熱數據區域。

如果訪問了熱數據區域中的一個緩存頁，是否應該馬上把它移動到熱數據區域的鏈表頭部？由于熱數據區域里的緩存頁可能是被經常訪問的，所以不建議頻繁移動，否則影響性能。

因此LRU鏈表的熱數據區域的訪問規則是：只有在熱數據區域的后3/4部分的緩存頁被訪問了，才會移動到鏈表頭部。如果是熱數據區域的前面1/4部分的緩存頁被訪問了，那么不需要移動。這樣盡可能減少鏈表中的節點頻繁移動。

6.Buffer Pool的緩存頁以及幾個鏈表總結

Buffer Pool在被使用時，會頻繁從磁盤上加載數據頁到緩存頁里。然后free鏈表、flush鏈表、LRU鏈表都會被同時使用，這三個鏈表都是雙向鏈表。

(1)當加載一個數據頁到一個緩存頁時

InnoDB就會從free鏈表里移除這個緩存頁。然后會把這個緩存頁放入到LRU鏈表的冷數據區域頭部。

(2)當修改一個緩存頁時

InnoDB就會在flush鏈表中記錄這個臟頁。而且可能會把該緩存頁從LRU鏈表的冷數據區域移動到熱數據區域頭部。

(3)當查詢一個緩存頁時

InnoDB可能會把該緩存頁從LRU鏈表冷數據區域移動到熱數據區域頭部，或者從LRU鏈表的熱數據區域其他位置移動到熱數據區域頭部。

總之，MySQL在執行增刪改查時：首先會大量操作緩存頁以及對應的幾個鏈表。然后當緩存頁滿時，會基于LRU鏈表淘汰緩存頁。也就是先把要淘汰的緩存頁刷入磁盤，然后清空該緩存頁。接著再把需要的數據頁加載到空閑的緩存頁中。

7.LRU鏈表的冷數據區域的緩存頁何時刷盤

時機一：定時把LRU尾部的部分緩存頁刷入磁盤

第一個時機并不是在緩存頁滿的時候，才會將緩存頁刷入磁盤。而是有一個后臺定時任務線程，該線程會定時把LRU鏈表的冷數據區域尾部的一些緩存頁刷入磁盤。然后清空幾個緩存頁，并將這些緩存頁加回free鏈表。

時機二：把flush鏈表中的一些緩存頁定時刷入磁盤

如果僅僅是把LRU鏈表中冷數據區域的緩存頁刷入磁盤，還是不夠的。因為在LRU鏈表的熱數據區域里很多緩存頁可能也會被頻繁的修改，這些緩存頁不可能永遠都不刷入磁盤中。

所以這個后臺線程同時也會在MySQL不怎么繁忙時，找個時間把flush鏈表中的緩存頁都刷入磁盤中。

只要flush鏈表中的緩存頁被刷入磁盤，則這些緩存頁也會從flush鏈表和LRU鏈表中移除，然后加入到free鏈表中。

時機三：實在沒有空閑緩存頁時

假設所有的free鏈表都被使用，同時flush鏈表中有很多被修改過的緩存頁，以及LRU鏈表中也有很多緩存頁進行冷熱數據分離。此時如果要從磁盤加載數據頁到一個空閑緩存頁中，就會從LRU鏈表的冷數據區域尾部找到一個緩存頁，刷入磁盤和清空。

8.增大Buffer Pool來提升MySQL的并發能力

當Buffer Pool用完時，此時需要先把一個緩存頁刷入磁盤騰出空閑緩存頁，再從磁盤讀取數據頁。這種情況要執行兩次磁盤IO，性能低下。一次是緩存頁刷入磁盤，一次是從磁盤讀取數據頁加載到緩存頁。

由于InnoDB在使用緩存頁的過程中，會有一個后臺線程定時地把LRU鏈表冷數據區域的一些緩存頁刷入磁盤。所以緩存頁是一邊被使用，一邊被后臺線程定時地釋放。如果緩存頁被使用得很快，而后臺線程釋放緩存頁的速度很慢，那么必然頻繁出現緩存頁被使用完的情況。

從InnoDB角度看，它無法控制緩存頁被使用的速度。因為緩存頁被使用的速度依賴于外部服務調用的并發程度。另外InnoDB的后臺線程會定時釋放一批緩存頁，這個過程也很難優化。因為如果頻繁釋放也會造成磁盤IO頻繁，從而影響性能。

所以最后可以依靠InnoDB的Buffer Pool的大小來避免。如果MySQL要抗高并發的訪問，那么機器必然要配置很大的內存空間，起碼是32G+、64GB、128GB。此時就可以設置Buffer Pool很大的內存空間，如20GB、48GB，80GB。

這樣在高并發場景下：雖然Buffer Pool的緩存頁被頻繁使用，但后臺線程也在定時釋放緩存頁。由于Buffer Pool內存很大，所以可能需要較長時間才會導致緩存頁用完。需要的時間越長，那么就越可以撐到數據庫訪問高峰期已過去。只要高峰一過，后臺線程又不停地基于flush鏈表和LRU鏈表釋放緩存頁，那么空閑的緩存頁數量又會慢慢多起來。

MySQL的生產經驗，就是給MySQL設置多個Buffer Pool來優化并發能力。如果Buffer Pool的內存小于1GB，MySQL默認只會給一個Buffer Pool。如果Buffer Pool的內存較大如8G，那么可給MySQL設置多個Buffer Pool。每個Buffer Pool負責管理一部分緩存頁和描述數據塊，每個Buffer Pool擁有獨立的free、flush、LRU鏈表。這時即便多個線程并發來訪問也可以把壓力分開，比如有的線程訪問這個Buffer Pool，有的線程訪問另外的Buffer Pool。

通過多個Buffer Pool，MySQL多線程并發訪問的性能就會得到提升，多個線程可以在不同的Buffer Pool中加鎖和執行自己的操作。

二.InnoDB的存儲模型

1.InnoDB的存儲模型以及對應的讀寫機制

2.提交事務時會將redo日志寫入磁盤中

3.MySQL的redo log和binlog對比

4.提交事務時同時也會寫入binlog

5.在redo日志中寫入commit標記的意義

6.后臺IO線程隨機將內存更新后的臟數據刷回磁盤

7.InnoDB的執行流程總結

8.redo日志和redo log機制的作用

9.redo日志會寫入日志文件里的Redo Log Blcok

10.Redo Log Buffer和Redo Log文件

11.redo日志從Redo Log Buffer中刷盤時機

12.undo log回滾日志原理

13.系統和數據庫能抗多少QPS

14.性能壓測指標和命令

15.簡單總結增刪改SQL語句的實現原理

1.InnoDB的存儲模型以及對應的讀寫機制

在邏輯層面上，InnoDB的數據是插入一個一個的表中。在物理層面上，InnoDB的數據是插入一個一個的表空間。

表空間對應著磁盤文件，磁盤文件里存放的就是數據。磁盤文件存放數據時，會被拆分為一個一個的數據區組。每個數據區組包含256個數據區，每個數據區包含64個數據頁。每個數據頁包含一行行的數據。

數據頁又包含了：文件頭、數據頁頭、最小記錄和最大記錄、多個數據行、空閑空間、數據頁目錄、文件尾部。

每個數據行又附加了真實數據外的很多信息：變長字段的長度列表、null值列表、數據頭、真實數據和隱藏字段。

通過數據頁、數據區、數據行附加的特殊信息，可以讓InnoDB在磁盤文件里實現B+索引、事務等復雜的機制。

當數據庫執行增刪改查時：必須把磁盤文件里的一個數據頁加載到內存Buffer Pool中的緩存頁里，然后增刪改查都針對緩存頁里的數據進行。

所以要讀寫數據時：會根據表找到一個表空間，通過表空間就可以找到對應的磁盤文件。通過磁盤文件就可以從里面找一個數據區組中的一個數據區。然后從該數據區中找一個數據頁出來。最后就可以把這個數據頁從磁盤加載到Buffer Pool緩存頁里。

當執行更新時，會寫undo日志、修改Buffer Pool數據、寫redo日志。

當提交事務時，會將redo日志刷磁、binlog刷盤、添加commit標記。最后后臺IO線程會隨機把Buffer Pool里的臟數據刷入到磁盤數據文件中。

2.提交事務時會將redo日志寫入磁盤中

如果InnoDB想要提交一個事務，就會根據一定的策略把redo日志從Redo Log Buffer中刷入到磁盤文件里，這個策略是通過如下這個參數來配置的：innodb_flush_log_at_trx_commit。

(1)當innodb_flush_log_at_trx_commit = 0時

那么進行事務提交時，不會把Redo Log Buffer的數據刷入到磁盤文件里。這時即便提交了事務，但如果MySQL宕機了，內存里的數據也會全部丟失而且redo日志里沒有數據。

(2)當innodb_flush_log_at_trx_commit = 1時

那么進行事務提交時，會把內存中的redo log刷入到磁盤文件里。只要事務提交成功，那么redo log就必然在磁盤里。哪怕此時Buffer Pool中更新過的數據還沒刷新到磁盤，系統崩潰重啟后，也可以根據磁盤中的redo log恢復。

(3)當innodb_flush_log_at_trx_commit = 2時

那么進行事務提交時，會把內存中的redo log寫入到OS Cache緩存里。OS Cache緩存里的數據可能在1秒后才會被寫入到磁盤文件中。

在這種模式下，當InnoDB存儲引擎提交事務后，redo log可能還停留在OS Cache緩存里，還沒實際進入到磁盤文件。而此時MySQL所在機器宕機了，那么OS Cache里的redo log也會丟失。從而出現即便提交了事務，但是數據還是丟失了的情況。

3.MySQL的redo log和binlog對比

MySQL的redo log，是一種偏向物理性的重做日志。因為其記錄的是：對哪個數據頁中的哪條記錄做了什么修改。而且redo log是屬于InnoDB存儲引擎特有的日志文件。

MySQL的binlog，是一種偏向于邏輯性的日志，也叫歸檔日志。類似"對users表中id=1的一行記錄做了更新操作，更新后的值是什么"。binlog不是InnoDB存儲引擎特有的日志文件，binlog是屬于MySQL數據庫層面的日志文件。

4.提交事務時同時也會寫入binlog

提交事務時，除了會把redo日志寫入到磁盤文件中，還會把這次SQL更新對應的binlog日志寫入到磁盤文件中。執行器這個組件，它會負責和InnoDB存儲引擎進行如下交互：

步驟1：從磁盤加載數據到Buffer Pool緩存

步驟2：寫入undo日志

步驟3：更新Buffer Pool里的數據

步驟4：寫入redo日志到Redo Log Buffer

步驟5：redo日志刷入磁盤

步驟6：寫入binlog日志

步驟7：在redo日志中寫入commit標記

其中步驟1、2、3、4是執行更新語句的階段，而步驟5、6、7是屬于提交事務的階段。

當MySQL把binlog寫入磁盤后，接著就會完成最終的事務提交。此時會把本次更新對應的binlog文件名稱和位置，都寫入到redo日志里，同時在redo日志文件里寫入一個commit標記。在完成這個事情后，才算是最終完成事務的提交。

5.在redo日志中寫入commit標記的意義

寫入commit標記是用來保持redo日志與binlog日志一致。也就是說，在提交事務的時候，上述的步驟5、6、7必須都執行完畢，才算是提交了事務。

(1)如果剛完成步驟5時，redo日志剛刷入到磁盤文件，MySQL宕機了

這時因為在redo日志沒有最終的事務commit標記，所以此次事務不成功。因為不允許出現這樣的情況：redo日志文件里有更新日志，但是binlog日志文件里沒有對應的更新日志。否則就會導致數據不一致。

(2)如果在完成步驟6時，binlog日志已寫入磁盤，MySQL宕機了

這時因為在redo日志沒有最終的事務commit標記，所以此次事務也失敗。所以必須要在redo日志寫入最終的事務commit標記，才算事務提交成功。

這樣redo日志有本次更新的日志，binlog日志也有本次更新的日志，從而實現redo日志和binlog日志完全一致。

6.后臺IO線程隨機將內存更新后的臟數據刷回磁盤

當完成事務提交后，MySQL已把內存中的Buffer Pool緩存數據更新了，同時磁盤里也有redo日志和binlog日志，但磁盤上的數據文件還是舊值。

這時MySQL會有一個后臺IO線程，在事務提交后的某個時間，隨機把內存Buffer Pool中修改后的臟數據刷回到磁盤上的數據文件里。

當IO線程把Buffer Pool里修改后的臟數據刷回磁盤后，磁盤上的數據才會跟內存里的數據一樣，都是修改后的值。

當IO線程把臟數據刷回磁盤之前，即便MySQL宕機也沒關系。因為重啟后會根據redo日志恢復提交事務時所做的修改到內存里。之后IO線程還是會把修改后的數據刷到磁盤的數據文件里。

7.InnoDB的執行流程總結

InnoDB存儲引擎會使用Buffer Pool、Redo Log Buffer來緩存數據。InnoDB存儲引擎有屬于自己的undo日志文件、redo日志文件，MySQL也有屬于自己的binlog日志文件。

執行更新時：會修改Buffer Pool里的數據、寫undo日志、寫Redo Log Buffer。

提交事務時：會把binlog刷入磁盤、在redo日志中寫入事務標記，把redo日志刷入磁盤。最后InnoDB后臺的IO線程會隨機把Buffer Pool的臟數據刷入到磁盤文件。

(1)MySQL宕機重啟如何確定是否需要從redo日志恢復數據

MySQL宕機重啟，如何確定臟數據在宕機前是否已全部刷寫回磁盤文件。

MySQL宕機重啟，InnoDB會首先去查看數據頁中LSN的數值。LSN就是InnoDB使用的一個版本標記的計數。如果數據頁中的LSN異于redo日志的commit標記，那么就去查看redo日志的LSN大小。如果數據頁的LSN值大，則說明數據頁領先redo日志，不需要恢復，反之則需要從redo日志中恢復。

(2)從redo日志恢復數據時是全量恢復還是指定位置后恢復

redo日志是劃歸于一個redo日志組的。默認一個redo日志組有兩個redo日志文件。寫redo日志時是循環寫入，寫滿一個redo日志文件再寫另外一個。

在寫滿切換redo日志文件時，會觸發數據庫的檢查點checkpoint。checkpoint所做的事就是把臟頁刷新回磁盤。

當DB重啟恢復時只需要恢復checkpoint之后的數據即可。所以redo日志文件大小不宜過大，不然導致恢復時需要更長的時間。redo日志文件大小也不宜過小，不然導致頻繁切換觸發檢測點降低性能。

(3)既然有redo日志來保證崩潰恢復，為什么還要有binlog日志

binlog日志其實就是歸檔日志，主要用來做數據恢復的。MySQL最開始設計時只有MyISAM引擎只有binlog，不支持InnoDB。此外數據庫備份以及hadoop系統數據分析都是binlog來實現的，所以還需要binlog。

(4)redo日志和binlog日志的數據結構是怎樣的

redo日志是循環寫，會把redo日志分為0,1,2,3四個區間，有兩個指針。writepos指針是一邊寫一邊向后移動，checkpoint指針是一邊擦除一邊向后移動。所以redo日志是不能保存很多記錄的，必須持久化到磁盤中。binlog日志是追加寫，不會覆蓋之前的日志。

(5)binlog日志和redo日志是怎么保持一致性的

binlog日志和redo日志是通過兩階段提交來保持一致性的。否則如果數據庫系統發生crash，則通過redo日志恢復的數據庫和通過binlog日志恢復出來的臨時庫不一致。

8.redo日志和redo log機制的作用

(1)redo log保證事務提交后修改的數據不丟失

更新完緩存頁后，必須要寫一條redo log，這樣才能記錄對數據庫的修改。

redo log可以保證事務提交后：如果事務中由增刪改SQL更新的緩存頁還沒刷新到磁盤時MySQL宕機，那么MySQL重啟后，就可以把redo log重做一遍，恢復事務在當時更新的緩存頁，然后再把緩存頁刷新到磁盤。所以redo log的本質是保證事務提交后，修改的數據絕對不會丟失。

(2)redo log出現的步驟分析

步驟一：MySQL在執行增刪改SQL語句時，都是針對一個表中的某些數據執行的。此時首先會找到這個表對應的表空間，然后找到表空間對應的磁盤文件。接著從磁盤文件里把需要更新的數據所在的數據頁從磁盤讀取出來，也就是將磁盤上的數據頁放到Buffer Pool中的緩存頁里。

步驟二：讀取磁盤文件的數據頁到Buffer Pool的緩存頁后，MySQL就會根據增刪改SQL語句對緩存頁執行更新操作。

步驟三：在MySQL更新緩存頁時，會更新free鏈表、會更新flush鏈表、會更新LRU鏈表。然后后臺有專門的IO線程，不定時根據flush鏈表、LRU鏈表，把更新過的緩存頁(臟頁)刷新回磁盤文件的數據頁里。

但這種機制有個漏洞：萬一事務里有增刪改SQL語句更新了緩存頁，事務提交了但還沒來得及讓IO線程把緩存頁刷新到磁盤而MySQL宕機。這時Buffer Pool內存里的數據就會丟失，剛做完的事務更新數據也丟失。但也不可能每次提交一個事務，就把事務更新的緩存頁刷新回磁盤文件。因為將緩存頁刷新到磁盤文件里，是對磁盤隨機寫的，性能很差。這會導致數據庫的性能和并發能力都很弱，因此才引入了這個redo log機制。

步驟四：提交事務時把MySQL對緩存頁的修改以日志形式寫入redo log日志文件。這種redo log日志的格式大致為：對表空間XX中的數據頁XX中的偏移量為XXXX的地方更新了數據XXX。

只要事務提交時將所做修改以日志形式寫入redo log，則宕機也沒關系。因為重啟后可以根據redo log，在Buffer Pool里恢復宕機前的事務更新。

(3)修改過的緩存頁刷盤和redo log刷盤的差別

事務提交時把修改過的緩存頁刷入磁盤，和事務提交時把所做修改的redo log寫入日志文件的差別：

一.如果把修改過的緩存頁都刷入磁盤

由于一個緩存頁是16K，數據還是比較大的，將其刷入磁盤會比較耗時，且修改的緩存頁可能僅有幾字節的改動，把完整緩存頁刷入磁盤不劃算。

二.將緩存頁刷入磁盤時是對磁盤進行隨機寫

這時由于一個緩存頁對應的位置可能在磁盤文件的一個隨機位置，比如偏移量為45336的地方，所以只能進行性能很差的磁盤隨機寫。

三.如果是將redo log寫入日志文件

由于一行redo log只占幾十字節，所以寫入磁盤日志文件的速度會很快。其中redo log只包含表空間號、數據頁號、磁盤文件偏移量、更新值。此外將redo log寫入日志時是對磁盤進行順序寫，速度也很快。其中每次進行順序寫時都是直接追加到磁盤文件尾部的。所以提交事務時，使用數據量少 + 順序寫的redo log記錄所做的修改，性能會遠超直接刷新緩存頁到磁盤，這可以讓數據庫的并發能力更強。

9.redo日志會寫入日志文件里的Redo Log Blcok

redo log日志本質上記錄的是：對某個表空間的某個數據頁的某個偏移量的地方修改了幾個字節的值。

所以redo日志需要記錄的就是：表空間號 + 數據頁號 + 偏移量 + 修改幾個字節的值 + 具體的值。

MySQL內有一個數據結構，叫做Redo Log Blcok。redo日志是用一個Redo Log Blcok來存放多個單行日志的。

一個Redo Log Block是512字節，分為3個部分：一是12字節的Header，二是496字節的Body，三是4字節的Trailer。

往一個文件里寫數據，可認為是從第一行開始從左往右寫，會有很多行。假設現在要寫第一條redo日志：首先會把該日志數據放到內存中的一個叫Redo Log Block的數據結構里，然后不斷往這個Redo Log Block的數據結構添加一條條redo日志，直到內存里的這個Redo Log Block滿了，已經達到512字節。當一個Redo Log Block滿時，再一次性把它寫入到磁盤文件。

10.Redo Log Buffer和Redo Log文件

已知MySQL執行完增刪改的SQL語句后：會先讓redo日志進入Redo Log Block，然后再寫入磁盤的redo日志文件。

Redo Log Buffer是MySQL啟動時向操作系統申請的一塊連續內存空間。Buffer Pool也是MySQL啟動時向操作系統申請的一塊連續內存空間。Buffer Pool會在申請內存后劃分很多空的緩存頁和一些鏈表結構。Redo Log Buffer也會在申請內存后，劃分很多空的Redo Log Block。

innodb_log_buffer_size可配置Redo Log Buffer的大小，默認是16MB。其實16MB已經夠大了，畢竟一個Redo Log Block才512字節，每條redo日志也就幾個到幾十個字節而已。

從Redo Log Buffer結構可知：當要寫一條redo日志時，就會從第一個Redo Log Block開始寫入。寫滿了一個Redo Log Block，就會繼續寫下一個Redo Log Block。以此類推，直到所有Redo Log Block寫滿。當Redo Log Buffer里的所有Redo Log Block都被寫滿后，就會強制把Redo Log Block刷入到磁盤中。

當一個Redo Log Block滿512字節后，也會被追加到redo日志文件里。然后在磁盤文件里不停地追加一個又一個的Redo Log Block。

此外，MySQL平時執行一個事務的過程中，每個事務都會有多個增刪改操作，這樣就會有多條redo日志。這多條redo日志就是一組Redo Log Group，每次一組Redo Log Group都先在別的地方暫存，執行完后再把一組redo日志寫到Redo Log Block里。

如果一組Redo Log Group中的redo日志太多，那么就可能會將其存放在兩個Redo Log Block中。如果一組Redo Log Group比較小，那么也可能多個Redo Log Group是在一個Redo Log Block里。

11.redo日志從Redo Log Buffer中刷盤時機

(1)Redo Log Block什么時候刷盤

時機一：Redo Log Buffer已使用過半時

如果Redo Log Buffer的日志已占據Redo Log Buffer總容量16M的一半，即超過了8MB的redo日志在緩沖里，此時就會把它們刷入磁盤文件中。

時機二：事務被提交時

一個事務提交時，要把其redo日志所在的Redo Log Block刷入磁盤文件。這樣它修改的數據才不會丟失，隨時可通過redo日志恢復事務所做修改。

(innodb_flush_log_at_trx_commit的值為1)

時機三：后臺線程定時刷新

有個后臺線程會每秒把Redo Log Buffer的Redo Log Block刷到磁盤文件。

時機四：關閉MySQL時

當關閉MySQL時，Redo Log Buffer的Redo Log Block都會刷入到磁盤里。

(2)redo log日志刷盤的場景

場景一：MySQL瞬間執行了大量高并發SQL，1秒就產生了超過8MB的redo日志。此時這些redo日志占據了Redo Log Buffer的一半空間，于是就會刷盤。

這種redo日志刷盤，在MySQL承受高并發請求時是比較常見的。比如每秒執行上萬個增刪改SQL，每個SQL的redo日志有幾百個字節。此時是可能1s生成超8MB的redo日志的，從而觸發刷新redo日志到磁盤。但是這種高并發請求的情況一般不常見。

場景二：正常情況執行一個事務，一般會在幾十毫秒到幾百毫秒間執行完畢。通常MySQL單事務性能一般不會超過1秒，否則就太慢了。所以執行完一個事務，也會馬上把這個事務的redo日志刷入磁盤。這種情況則比較常見，當一個短事務提交時往往會發生redo日志刷盤。

場景三：后臺線程每秒自動刷新redo日志到磁盤去。

總而言之：一個事務執行時，事務對應的redo日志都進入到Redo Log Buffer。一個事務提交時，事務對應的redo日志刷入磁盤文件才算事務提交成功。這樣才能確保事務提交后，數據不會丟，只要有redo日志在磁盤里就行。

(3)磁盤上到底有幾個redo日志文件

大量的redo日志是否都放在一個文件里，磁盤空間是否會越占越多？默認情況下，redo log都會寫入一個目錄中的文件里。這個目錄可通過show variables like 'datadir'來查看，可通過innodb_log_group_home_dir參數來進行設置。

redo日志文件是有多個的，寫滿了一個就會寫下一個redo日志文件。可以限制redo日志文件的數量。通過innodb_log_file_size可指定每個redo日文件的大小，默認48MB。通過innodb_log_files_in_group可指定redo日志文件的數量，默認2個。

所以，默認情況下，目錄里就兩個日志文件，分別為ib_logfile0和ib_logfile1，每個48MB。先寫滿第一個再進行寫滿第二個，一個寫滿了交替覆蓋式去寫另外一個。

因此，MySQL最多只保留最近的96MB的redo日志而已。事實上這已足夠多了，畢竟一條redo log通常就幾個字節到幾十個字節，96MB已足夠存儲上百萬條redo log了。

12.undo log回滾日志原理

(1)redo log應對的場景—事務提交數據丟失

已知對Buffer Pool里的緩存頁執行增刪改操作時，必須要寫對應的redo log日志記錄下要做的哪些修改。redo log日志都會先進入Redo Log Buffer中的一個Redo Log Blcok，然后事務提交時會將Redo Log Block刷入到磁盤的redo日志文件里。

萬一事務已提交，而事務修改的緩存頁還沒刷入磁盤上的數據頁文件。此時MySQL宕機，那么Buffer Pool里被事務修改過的數據就全部丟失。

但只要有redo log，MySQL重啟后又可以把那些還沒刷入磁盤的緩存頁它們所對應的redo log都加載出來，在Buffer Pool的緩存頁里重做一遍，這樣就可以保證事務提交之后，修改的數據絕對不會丟。

(2)undo log應對的場景—進行事務回滾

假設現在在一個事務里要執行一些增刪改操作：那么需要先把對應的數據頁從磁盤加載出來放到Buffer Pool的緩存頁，然后在緩存頁進行增刪改，同時記錄redo log。

萬一這個事務里的增刪改操作執行了一半時，需要進行事務回滾。比如一個事務里有4個增刪改操作，結果已經執行了2個增刪改SQL。已經更新了一些Buffer Pool的數據，但還有2個增刪改SQL還沒執行。此時如果要回滾事務，就必須對已經在Buffer Pool緩存頁里執行過的增刪改SQL操作進行回滾。所以才必須引入另外一種日志，就是undo log回滾日志。

(3)undo log回滾日志的作用

執行事務時，很多insert、update和delete語句都在更新緩存頁的數據。如果事務回滾，需要根據每條SQL對應的undo log回滾日志恢復數據。

13.系統和數據庫能抗多少QPS

4核8G的機器部署普通的Java系統，每秒能抗下幾百的請求，從每秒一兩百請求到每秒七八百請求都有可能。

8核16G的機器部署的MySQL數據庫，每秒能抗一兩千請求。16核32G的機器部署的MySQL數據庫，每秒能抗兩到四千的請求。

14.性能壓測指標和命令

IO相關的壓測性能指標—IOPS、吞吐量、latency

壓測時要關注的其他性能指標—CPU、網絡、內存

(1)IOPS

IOPS指機器的隨機IO并發處理能力。比如機器可以達200的IOPS，表示每秒可以執行200個隨機IO讀寫請求。

InnoDB更新內存中的臟數據時，最后會由后臺IO線程在不確定的時間刷回到磁盤，這就涉及隨機IO。如果IOPS指標太低，那么會導致內存里的臟數據刷回磁盤的效率不高。

(2)吞吐量