1.InnoDB邏輯存儲結構

表空間->段->區->頁->行->數據

表空間：覆蓋了所有的數據和索引，系統表在系統表空間，還有默認表空間等
段：多個段組成表空間
區：多個區組成段，一般每個區的大小通常是1M
頁：默認64個連續的頁組成一個區，每個頁的大小默認是16KB
行：多行數據在都在頁里。

MySQL每次去磁盤讀取數據到內存時，都至少將一個頁的數據加載到內存里。
在數據庫中，不論讀取一行還是多行都需要將一個頁或者多個頁的數據加載到內存中。頁是MySQL讀取數據的最小單位。

2.MySQL的物理存儲結構

2.1. 5.7版本

InnoDB
表&索引數據：ibd文件
表結構：frm文件
redo log文件：用于崩潰恢復的日志文件之一

系統元數據：存儲元數據和存儲引擎無關
MyISAM
表結構：frm
數據：MYD
索引：MYI

沒有redo log文件

2.2. 8.0版本

InnoDB
表數據&索引：ibd文件
表結構：被剔除了，表結構被放到系統元數據里了，information_schema
redo log文件：用于崩潰恢復的日志文件之一

MyISAM
表結構：被剔除了，表結構被放到系統元數據里了，information_schema
數據：MYD
索引：MYI

MySQL8.0推出了DDL原子性操作，針對原子操作可以通過information_schema
來實現定義表信息的一致性和可靠性。

3.MySQL常見存儲引擎的區別

功能	InnoDB	MyISAM
聚簇索引	支持	不支持
表鎖	支持	支持
行鎖	支持	不支持
數據緩存	支持（buffer pool）	不支持
外鍵	支持	不支持
MVCC	支持	不支持
事務	支持	不支持

4.AHI

自適應hash索引，innodb特有。
當AHI發現某些索引值使用的非常頻繁，則會建立hash索引來提升查詢效率。
AHI的操作不需要做任何操作，默認即可。在AHI生成自適應hash索引后，查詢效率可以從B+Tree的O（logn）提升到O(1)。

5.Buffer Pool

Buffer Pool是MySQL主存中的一個區域。使用InnoDB存儲引擎時，會將數據從磁盤中拉取到Buffer Pool中。而這些數據是以頁的形式存在。官方文檔建議80%的物理內存都配置給Buffer Pool中。
當Buffer Pool 的數據發生修改和數據庫不一致時，MySQL通過Page Cleaner線程池（默認1個線程，可配置）負責臟頁刷盤，避免阻塞前臺查詢，將Buffer Pool中的數據同步到idb文件中。
并且在Buffer Pool發生修改之前，會先將數據變化的日志寫入到redo log（通過log buffer實現）中。

5.1.存儲結構和內存淘汰機制

存儲結構是將整個Buffer Pool分成了兩個區域
New Sublist 5/8 最經常被訪問的數據
Old Sublist 3/8

內部數據都是頁，頁是基于鏈表鏈接的。
內存淘汰機制是LRU（最近最少使用Least Recently Used）
當需要將磁盤中獲取的頁存儲到Buffer Pool時，會先將這個頁的數據存放到Old Sublist的head位置。如果某個頁沒操作，慢慢的會被放到Old Sublist的tail位置。當又有數據需要存放到Buffer Pool時，如果內存不足，則會淘汰Old Sublist的tail位置的數據。
當某個頁的數據被操作了（需要滿足一定規則，被高頻操作），就會放到New Sublist的head位置。
內存淘汰時有限淘汰old的tail部。當old區域沒有內存可以淘汰時，才回去淘汰new區域的內存。

5.2.多線程問題

buffer pool是一個共享區域，多個線程同時操作時會產生指針問題。所以操作需要用到鎖。
buffer pool是支持多實例的，可以通過innodb_buffer_pool_instances去設置實例個數，最大設置64個，默認1個。多個實例時，每個實例至少分配1個G內存才會生效。
注意：多個buffer pool之間的數據是不一樣的！知識根據hash將數據分布到了不同的buffer pool中。

6.Change Buffer

Change Buffer是在mysql中使用二級索引（非聚集索引）去寫數據時優化的一個策略。實在進行DML（CRUD）操作的一個優化。
如果寫的操作是非聚簇索引，并且對應的數據頁不在Buffer Pool中，此時不會立即將磁盤中的數據庫頁加載到Buffer Pool中。而是先將寫操作扔到Change Buffer中，做一個緩沖。當要修改的這個數據頁被讀取時，再將Change Buffer中的記錄合并到Buffer Pool中。減少磁盤IO次數，提高性能。

注：Change Buffer占用的是Buffer Pool的內存。
如果業務場景寫多讀少，則可以調大Change Buffer的占用空間。

整體流程：
當更新一條記錄時，該記錄在BP中，直接修改對應的頁，進行一次內存操作。
當更新一條記錄時，該記錄不在BP中，在不影響一致性的前提下，會將更新操作緩存在CB中，不去做磁盤IO操作。
當查到該記錄時，會將這個記錄扔到BP中，并將CB中與這條數據的相關的操作合并到BF中。

6.1.如果Change bufferr同步之前，mysql宕機了怎么辦？

通過redo log可以實現事務提交之后，數據不會丟失。

7.Log Buffer

innodb特有的，是存儲要寫入磁盤的日志文件的一片內存區域。主要是redo log。默認占用內存16M。
減少寫操作時，日志寫入的IO損耗。

8.Redo Log

innodb獨有，讓mysql有了崩潰恢復能力（一般配合bin log）。
redo log的結構：存儲表空間號+數據頁號+偏移量+具體修改的數據…
在修改buffer pool和change buffer之前，會先記錄redo log（將日志寫到log buffer中）。
將log Buffer中的數據刷新到磁盤有的觸發時機根據配置項進行配置:
innodb_flush_log_at_trx_commit: 0 1 2
0:每次事務提交，不刷盤，后臺線程去刷盤，每秒一次，如果log buffer超過設置空間的一半，也會刷盤。
1:每次事務提交后，立即刷盤（默認值），此時后臺線程仍然運行。
2:每次事務提交后，將log buffer的數據刷新到系統內存中

思考：如果設置為1的話，那么如果事務還沒有提交的時候，后臺線程將log buffer中的數據刷新到了系統內存中，那么事務回退是否會造成數據不一致的問題？

8.1.redo log的存儲形式

redo log是順序讀寫的。并且redo log是以文件組的形式存在的。5.7中默認是2個文件，可以配置為多個文件，每個文件的大小一致。
在寫redo log時會用到兩個指針：
write ops：記錄當前要寫的位置，一邊寫，一邊往后移。
check point：記錄當前要擦除的位置，一邊刪，一邊后移。當文件寫滿了，需要刪除之前的數據，才能繼續寫。

8.2.為什么數據不直接寫到具體的表，而是先寫到redo log中？

redo log是順序寫入，速度快。如果寫到表里，表中的數據，地址是隨機的，會影響事務提交對的效率。如果要回滾事務，還得隨機尋址。

9.bin log

bin log文件不依賴于存儲引擎而存在，記錄數據的寫操作。
主要用于MySQL的主從復制，數據備份和數據的一致性。

9.1.bin log存儲格式

分為三種
statement：存儲每個造成數據修改的sql，如在sql中使用了不確定函數比如now（），那么恢復數據時，可能導致數據不一致。
row：（默認）不但具備sql，還具備當前行的數據，避免了函數導致的問題。但是會占用更多的數據。
max：混合，會判斷當前數據是否會造成不一致問題，如果會則使用row，不會則使用statement。

9.2.bin log存儲數據的時機

提交時機和事務掛鉤。當事務提交后，會將事務中的內容提交到bin log cache中。
與log buffer不同的是，mysql 會給每一個線程都分配一個bin log cache。因為一個事務中的bin log不能拆分，無論多大的事務，都要保證一次性寫入。如果多個線程共用一個bin log cache，則會發生不同事物的bin log事件交錯存儲。
bin log cache的大小可以自定義，默認32KB。但是如果事務太大，超過了binlog_cache_size怎么辦？mysql會自動擴容內存，不能超過max_binlog_cache_size。但是這個值特別大，基本不用考慮長事務導致cache超出的問題。但是開發中仍然要避免長事務。

binlog cache何時同步到磁盤根據配置項來配置。
sync_binlog 取值0 1 N
0：每次提交時，都會將cache中的數據同步到系統的內存中。至于系統內存同步到硬盤，由操作系統控制。
1：默認值，每次提交事務都會執行fsync操作，確保binlog cache中的數據一定能落到本地磁盤中。數據是完整的。
N：大于1的值，每次提交事務的執行write操作，當積累了N個事務才會主動的去執行fsync。雖然能提高IO瓶頸，但是可能造成數據丟失。

10.二階段提交

redo log讓mysql有了崩潰恢復的能力，在事務的執行過程中會不斷地寫入。
bin log 讓mysql有了集群架構的一致性，僅在事務提交的時候會寫入。

當redo log和bin log文件產生不一致時，如
原先age = 18
執行SQL：update table set age = 38 where id = 1;
假設redo log 在事務還沒有提交的時候，將age = 38持久化到了redo log文件中。
但是事務沒有正常提交，此時bin log中age還是18，此時兩個日志文件發生了不一致。
此時MySQL崩潰重啟。主庫就會出現age = 18，而從庫age = 38。因為主從是根據bin log同步的。而崩潰時根據redo log恢復的。
二階段就是用來解決上述問題。
redo log 的寫入分為了兩個階段：prepare 和commit。
事務還沒有提交的時候，redo log中的數據是prepare階段，而當真正提交了數據之后才是commit階段。
MySQL在恢復時，會判斷如果redo log中的數據處于prepare階段，并且bin log中沒有對應的數據，則不會恢復該數據。
如果redo log中的數據處于prepare階段，并且bin log中有對應的數據，則會恢復該數據。
所以在恢復數據時以bin log為主。

11.undo log

innodb引擎的一個日志文件。
存儲數據的歷史版本，一方面是為了做事務回滾時，可以找到需要回滾的數據內容，另一方面是MVCC的多版本并發控制操作時，需要讀取快照信息，就要在undo log里面去讀。

12.事務的常見問題

12.1.事務的特性

原子性：事務是一個不可分割的工作單元，事務中的所有操作要么全部執行成功，要么全部不執行
隔離性：多個事務并發執行時，一個事務的操作不應影響其他事務，即事務之間相互隔離。
持久性：事務一旦提交，其對數據庫的修改就是永久性的，即使系統崩潰或重啟，數據也不會丟失。
一致性：事務執行前后，數據庫必須從一個一致的狀態轉移到另一個一致的狀態。事務提交后預期的結果和最終的結果是一致的。

12.2.事務的并發問題

臟讀：讀取到其他事務未提交的數據，若該事務回滾，則讀取到的數據無效。
不可重復讀：同一事務中多次讀取同一數據，結果可能不同（其他事務已修改并提交）。
幻讀：同一事務中多次查詢同一范圍的數據，結果集可能不同（其他事務插入了新數據）。

12.3.事務的隔離級別

隔離級別 臟讀 不可重復讀 幻讀 并發性能
讀未提交ru 可能 可能 可能 最高
讀已提交rc 不可能 可能 可能 高
可重復讀rr 不可能 不可能 可能 中
串行化 不可能 不可能 不可能 最低

13.bin log 和 undo log的存儲位置

bin log的存放路徑，根據log_bin配置，默認存放到數據目錄下
8.0版本默認將undo log存放到數據目錄下。