1、JDBC Connection實例是線程安全的嗎？
Connection實例是線程安全的嗎？
能不能只創建一次，共享Connection對象？
答案是不能的， Connection不是線程安全的，他會在多線程環境下，導致數據庫操作的混亂，特別是在事務存在的情況下：可能一個線程剛開啓事務con.setAutoCommit(true)，而另一個線程直接提交事務con.commit();
對於單獨查詢的情況，似乎不會出現數據錯亂的情況。是因為在JDBC中，使用了鎖進行同步
源碼：com.mysql.cj.jdbc.CallableStatement#executeQuery
connection本身是線程不安全的，並且connection創建開銷比較大，所以一般使用數據庫連接池來統一的管理connection對象，例如druid連接池，c3p0連接池等等
數據庫連接池
在使用數據庫連接池時，一個線程中所有DB操作都是使用同一個Connection實例嗎？
在Spring環境中，獲取connection源碼如下所示：
源碼：org.springframework.jdbc.datasource.DataSourceUtils#doGetConnection
非事務場景：在非事務創建中(同時沒有使用Spring事務管理器)，每一次訪問數據庫，都是在DataSource中取出一個Connection實例，調用完畢之後歸還資源，因此多次調用，應該是不同的Connection實例
事務場景：在使用事務的情況下，實際上是在ConnectionHolder中獲取的Connection。而ConnectionHolder是在TransactionSynchronizationManager中獲取的resources屬性的值，即connection對象信息
源碼：
org.springframework.transaction.support.TransactionSynchronizationManager#doGetResource
而ThreadLocal<Map<Object,Object>>線程上下文共享。即Connection對於與Thread綁定。因此在事務中無論操作多少次DB，事實上都是操作的同一個Connection對象
2、MySQL架構設計
SQL接口：SQL的標準來接受
SQL解析器：理解這個SQL語句要幹什麼事情
查詢優化器：選擇一個最優的查詢路徑
執行器：執行器就會去根據我們的優化器生成一套執行計劃，然後不停的調用存儲引擎的各種接口去完成SQL語句的執行計劃
存儲引擎：存儲引擎其實就是執行SQL語句的，他會按照一定的步驟去查詢內存數據，更新磁盤數據，查詢磁盤數據，等等，執行諸如此類的一系列的操作，MySQL的架構設計中，SQL接口，SQL解析器，查詢優化器其實都是通用的，他就是一套組件而已，但是存儲引擎的話，他是支持各種各樣的存儲引擎的，比如我們常見的InnoDB、MyISAM、Memory等等
3、InnoDB內存結構
4、Buffer Pool
Buffer Pool: 緩衝池，簡稱BP。其作用是用來緩存表數據與索引數據，減少磁盤IO操作，提供效率
Buffer Pool由緩存數據頁和對緩存數據頁進行描述的控制塊組成，控制塊中存儲着對應緩存頁的所屬的表空間、數據頁的編號、以及對應緩存頁在Buffer Pool中的地址等信息
Buffer Pool默認大小是128M，以Page頁為單位，Page頁默認大小16kb，而控制塊的大小為數據頁的5%，大概是800字節
注意：Buffer Pool大小為128M指的就是緩存頁的大小，控制塊則一般佔5%，所以每次會多申請6M的內存空間用於存放控制塊
如何判斷一個頁是否在Buffer Pool中緩存？
MySQL中有一個哈希表數據結構，它使用表空間號+數據頁號，作為一個key，然後緩存頁對應的控制塊作為value
- 當需要訪問某個頁的數據時，先從哈希表中根據表空間號+頁號看看是否存在對應的緩存頁
- 如果有，則直接使用；如果沒有，就從free鏈表中選出一個空閒的緩存頁，然後把磁盤中對應的頁加載到緩存頁的位置
Page頁
Buffer Pool的底層採用鏈表數據結構管理Page。在InnoDB訪問表記錄和索引時會在Page頁中緩存，以後使用可以減少磁盤IO操作，提升效率
Page分類
Page頁根據狀態分為三種類型 :
free page : 空閒page，未被使用
clean page : 被使用page，數據沒有被修改過
dirty page : 髒頁，被使用page，數據被修改過，頁中數據和磁盤數據產生了不一致
Page如何管理
針對上面所説的三種page類型，InnoDB通過三種鏈表結構來維護和管理
-
free list : 表示空閒緩衝區，管理free page
Buffer Pool的初始化過程中，是先向操作系統申請連續的內存空間，然後把它劃分成若干個控制塊&緩存頁的鍵值對
free鏈表是把所有空閒的緩存頁的控制塊作為一個個的節點放到一個鏈表中，這個鏈表便稱之為free鏈表
基節點：free鏈表中只有一個基節點是不記錄緩存頁信息(單獨申請空間)，它裏面就存放了free鏈表頭節點地址，尾節點地址，還有free鏈表當前有多少個節點
磁盤加載過程 :- 從free鏈表中取出一個空閒的控制塊(對應緩存頁)
- 把該緩存頁對應的控制塊的信息填上(例如：頁所在的表空間、頁號之類的信息)
- 把該緩存頁對應的free鏈表節點(即：控制塊)從連接中移除。表示該緩存頁已經被使用
- flush list : 表示需要刷新到磁盤的緩衝區，管理dirty page，內部page按修改時間排序
InnoDB引擎為了提高處理效率，在每次修改緩存頁後，並不是立刻把修改刷新到磁盤上，而是在未來的某個時間點進行刷新操作，所以需要使用到flush鏈表存儲髒頁，凡是被修改過的緩存頁對應的控制塊都會作為節點加入到flush鏈表
髒頁即存在於flush鏈表，也在LRU鏈表中，但是兩種互不影響，LRU鏈表負責管理page的可用性和釋放，而flush鏈表負責管理髒頁的刷盤操作 -
LRU list : 表示正在使用的緩衝區，管理clean page和dirty page
普通LRU算法
LRU = Least Recently Used(最近最少使用) : 就是末尾淘汰法，新數據從鏈表頭部增加，釋放空間的從末尾淘汰
- 當要訪問某個頁時，如果不在Buffer Pool，需要把該頁加載到緩衝池，並且把緩存頁對應的控制塊作為節點添加到LRU鏈表的頭部
- 當要訪問某個頁時，如果在Buffer Pool中，則直接把該頁對應的控制塊移動到LRU鏈表的頭部
- 當需要釋放空間時，從末尾淘汰
普通LRU鏈表的優缺點：
優點：所有最近使用的數據都在鏈表表頭，最近未使用的數據都在鏈表表尾，保證數據能最快獲取到
缺點：
1、 如果發生全表掃描(比如：沒有建立合適的索引or查詢時使用select * 等)，則有很大可能將真正熱數據淘汰掉
2、 由於MySQL中存在預讀機制，很多預讀的頁都會被放到LRU鏈表的表頭。如果這些預讀的頁都沒有用到的話，這樣，會導致很多尾部的緩存頁很快就會被淘汰
改進型LRU算法
緩衝區以midpoint為基點，前面鏈表為new列表區，存放經常訪問的數據，佔63%；後面的鏈表稱為old列表區，存放使用較少數據，佔37%。加入元素時並不是從表頭插入，而是從中間midpoint位置插入(就是説從磁盤中心讀出的數據會放在冷數據區的頭部)，如果數據很快被訪問，那麼page久會向new列表頭部移動，如果數據沒有被訪問，會逐步向old尾部移動，等待淘汰
冷數據區的數據頁什麼時候會被轉到熱數據區呢？ - 就是説，必須是一個數據頁被加載到緩存頁之後，在1s之後，你訪問了這個緩存頁，它才會被挪到熱數據區域的鏈表頭部去
- 1s這個時間是由參數 innodb_old_blocks_time 控制的
5、Change Buffer
Change Buffer : 寫緩衝區，是針對二級索引(輔助索引)頁的更新優化措施
作用：在進行DML操作時，如果請求的是輔助索引(非唯一鍵索引)沒有在緩衝池中，並不立刻將磁盤頁加載到緩衝池，而是在Change Buffer記錄緩衝變更，等未來數據被讀取時，再將數據合併恢復到Change Buffer中，從而減少磁盤IO的寫
Change Buffer佔用BufferPool空間，默認佔25%，最大允許50%。可根據讀寫業務量來進行調整。參數 innodb_change_buffer_max_size
更新流程
寫緩衝區，僅適用於非唯一普通索引頁，為什麼？
如果在索引設置唯一性，在進行修改時，InnoDB必須要做唯一性校驗，因此必須查詢磁盤，做一次IO操作。會直將記錄插入到Buffer Pool中，然後再緩衝池修改，不會在ChangeBuffer操作
什麼情況下進行merge?
將change buffer中的操作應用到原數據頁，得到最新結果的過程稱為merge
change buffer，實際上它是可以持久化的數據。也就是説，change buffer 在內存中有拷貝，也會被寫入到磁盤上，以下情況會進行持久化：
- 訪問這個數據頁會觸發merge
- 系統有後台線程會定期merge
-
在數據庫正常關閉(shutdown)的過程中，也會執行merge操作
Change Buffer的使用場景
change buffer的主要目的就是將記錄的變更動作緩存下來，所以在merge發生之前應當儘可能多的緩存變更信息，這樣change buffer的優勢發揮的就越明顯
應用場景：對於寫多讀少的業務來説，頁面在寫完以後馬上被訪問的概率比較小，此時change buffer的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統
Buffer Pool內存結構
6、InnoDB 磁盤結構
InnoDB磁盤主要包含Tablespaces、InnoDB Data Dictionary、DoubleWrite Buffer、redo log和undo logs
表空間(Tablespaces)
表空間(Tablespaces) : 用於存儲表結構(t_user.frm)和數據(t_user.ibd)，InnoDB表空間類型包括系統表空間、File-Per-Table表空間、常規表空間，Undo表空間、臨時表空間等
-
系統表空間(The System Tablespace)
- 包括InnoDB數據字典，
DoubleWrite Buffer，Change Buffer，Undo Logs的存儲區域。系統表空間也默認包含任何用户在系統表空間創建的表數據和索引數據 - 系統表空間是一個共享的表空間因為它是被多個表共享的。該表空間的數據文件通過參數innodb_data_file_path控制，默認值是 ibdata1:12M:autoextend(文件名為ibdata1、12M、自動擴展)
- 包括InnoDB數據字典，
-
獨立表空間(File-Per-Table Tablespaces)
- 默認開啓，獨立表空間是一個單表表空間，該表創建於自己的數據文件中，而非創建系統表空間中。當
innodb_file_per_table選項開啓時，表將被創建於獨立表空間中。否則，innodb將被創建於系統表空間中
- 每個表文件表空間由一個.ibd數據文件代表，該文件默認被創建於數據庫目錄中
- 獨立表空間的好處
在獨立表空間下，刪除或者清理表後，存儲空間會立刻返回給操作系統。而在共享表空間下，表空間數據文件的大小不會縮小
可以通過複製File-per-table表空間的對應表的數據文件到其他mysql數據庫實例的表空間下，實現表的導入遷移
- 默認開啓，獨立表空間是一個單表表空間，該表創建於自己的數據文件中，而非創建系統表空間中。當
-
通用表空間(General Tablespaces)
- 通用表空間為通過create tablespace語法創建的共享表空間。通用表空間可以創建於mysql數據目錄外的其他表空間，其可以容納多張表，且支持所有的行格式
- 通用表空間和系統表空間類似，也是共享的表空間，一個文件能夠存儲多個表數據。相比
File-per-table表空間，通用表空間由於多表共享表空間，消耗的內存會更少一點，具有潛在的內存優勢(佔用的磁盤空間會更少)。
首先ts1.ibd必須是chown -R mysql:mysql ts1.ibd，有mysql權限
-
撤銷表空間(Undo Tablespaces)
- 撤銷表空間，用來保存回滾日誌，即undo logs。撤銷表空間由一個或多個包含Undo日誌文件組成，會在
undo tablespaces下生成undo_001和undo_002供2個文件 - 回滾表空間的文件必須以”.ibu”作為擴展後綴名
- 可以通過
innodb_undo_directory屬性查看回滾表空間的位置。默認路徑是mysql的數據存儲路徑 - InnoDB使用的undo表空間由
innodb_undo_tablespaces配置選項控制，默認為0。參數值為0表示使用系統表空間ibdata1；大於0表示使用undo_001、undo_002等
- 撤銷表空間，用來保存回滾日誌，即undo logs。撤銷表空間由一個或多個包含Undo日誌文件組成，會在
-
臨時表空間(Temporary Tablespaces)
- 分為會話臨時表空間和全局臨時表空間兩種。會話臨時表空間存儲的是用户創建的臨時表和磁盤內部的臨時表。全局臨時表空間存儲用户臨時表的回滾段(rollback segments)
- Session臨時表空間在session請求時創建的，最大分配2個，一個是用户創建的臨時表空間，一個是優化器創建的臨時表空間。全局臨時表空間默認是數據目錄的ibtmp1文件，所有臨時表共享，可以通過
innodb_temp_data_file_path屬性指定臨時表空間的位置
數據字典
InnoDB數據字典由內部系統表組成，這些表包含用於查找表、索引、和表字段等對應的元數據。元數據物理上位於InnoDB系統表空間中。在MySQL8.0之前，由於歷史原因，數據字典元數據在一定程度上與InnoDB表元數據文件(.frm文件)中存儲的信息重疊
注意：MySQL8.0之後，將所有原先存放於數據字典文件中的信息，全部存放到數據庫系統表中，即將之前版本的.frm，.opt等文件都移除了，不再通過文件的方式存儲數據字典信息
雙寫緩衝區
- 什麼是寫失效(部分頁失效)
InnoDB的頁和操作系統的頁大小不一致，InnoDB頁大小一般為16kb，操作系統頁大小為4k，InnoDB的頁寫入到磁盤時，一個頁需要分4次寫
如果存儲引擎正在寫入頁的數據到磁盤時發生了宕機，可能出現頁只寫了一部分的情況，比如只寫了4k就宕機了，這種情況叫做部分寫失效(partial page write)，可能會導致數據丟失
有經驗的DBA可能會想到，如果發生寫失效，MySQL可以根據redo log進行恢復。這是一個辦法，但是必須清楚認識到，redo log中記錄的是對頁的物理修改，如偏移量800，寫’aaa’記錄。如果這個頁本身已經發生了損壞，再對其進行重做是沒有意義的
- 雙寫緩衝區 DoubleWrite Buffer
為了解決寫失效問題，InnoDB實現了double write buffer，它位於系統表空間，是一個存儲區域。在Buffer Pool的page頁刷新到磁盤整整的位置前，會先將數據存在DoubleWrite緩衝區。這樣在宕機重啓時，如果出現數據頁損壞，那麼在應用redo log之前，需要通過該頁的副本還原該頁，然後再進行redo log重做，double write實現了InnoDB引擎數據頁的可靠性
-
數據雙寫流程
- step1 : 當進行緩衝池的髒頁刷到磁盤的操作時，並不會直接寫磁盤，每次髒頁刷新必須要先寫double write
- step2 : 通過memcpy函數將髒頁複製到內存中的double write buffer
- step3 : double write buffer再分兩次，每次1M，順序寫入共享表空間的物理磁盤上，第一次寫
- step4 : 在完成double write頁的寫入後，再將double write buffer中的頁寫入各個表的獨立表空也文件中(數據文件.ibd)，第二次寫
- 為什麼要寫兩次
可能有的同學有疑問，為啥寫兩次，刷一次數據文件保存數據不就可以了，為什麼還要寫共享表空間？其實是因為共享表空間是在ibdata文件中劃出2MB連續的空間，專門給double write髒頁用的，由於在這個過程中，double write也的存儲是連續的，因此寫入磁盤為順序寫，性能很高，完成double write後，再將髒頁寫入實際的各個表空間文件，這時寫入就是離散的了
7、重做日誌
WAL(Write-Ahead Logging)機制
WAL(Write-Ahead Logging)，預寫日誌(日誌先行)，是一種數據安全寫入機制。就是寫先日誌，然後再寫磁盤。MySQL中的redo log就是採用WAL機制
為什麼使用WAL？
磁盤的寫是隨機的，比較耗性能，所以如果把每一次的更新操作都寫入log中，那麼就成了順序寫操作，實際更新操作由後台線程在根據log異步寫入。這樣對client端，延遲就降低了。並且，由於順序寫入大概率是在一個磁盤塊內，這樣產生的io次數也大大降低。所以WAL的核心在於將隨機寫轉換為了順序寫，降低了客户端的延遲，提升了吞吐量
redo log基本概念
redo log : 包含兩部分，一個是內存中的日誌緩衝 : redo log buffer，另一個是磁盤上的日誌文件 ，redo log file
MySQL每執行一條DML語句，先將記錄寫入redo log buffer，後續某個時間點再一次性將多個操作記錄寫到redo log file。當故障發生致使內存數據丟失後，InnoDB會在重啓時，經過重放redo log，將page恢復到崩潰之前的狀態(實現事務性的持久性)
redo log持久化策略
緩衝區數據一般情況下是無法直接寫入磁盤的，中間必須經過操作系統(OS Buffer)。因此，redo log buffer寫入redo log file實際上是先寫入OS Cache，然後再通過系統調用fsync()將其刷到redo log file.
redo buffer持久化到redo log的策略，可通過 innodb_flush_log_at_trx_commit設置 :
一般建議選擇取值2，因為MySQL掛了沒有損失，整個服務掛了才會損失1秒的事務提交數據，默認是1
redo log日誌格式
物理日誌vs 邏輯日誌
- 物理日誌 : 記錄的是每一個Page頁中具體存儲的值是多少，在這個數據頁上做了什麼修改，比如：某個事務將系統表空間中的第100個數據頁中偏移量為1000處的那個字節的值改為2
-
邏輯日誌：記錄的是每一個page頁中具體數據是怎麼變動的，他會記錄一個變動的過程或SQL語句邏輯，比如：把一個page頁中的一個數據從1改為2，再從2改為3，邏輯日誌會記錄1->2，2->3這個數據變化的過程
redo日誌屬於物理日誌，只是記錄一下事務對數據庫做了哪些修改
type : 該條日誌的類型
space Id : 表空間ID
page number : 頁號
data : 該條redo日誌的具體內容redo log日誌類型
redo log根據在頁面中寫入數據的多少，將redo log日誌劃分為幾種不同的類型 :
- MLOG_1BYTE(type=1) : 表示在頁面的某個偏移量處寫入1字節的redo日誌類型
- MLOG_2BYTE(type=2) : 表示在頁面的某個偏移量處寫入2字節的redo日誌類型
- MLOG_4BYTE(type=4) : 表示在頁面的某個偏移量處寫入4字節的redo日誌類型
- MLOG_8BYTE(type=8) : 表示在頁面的某個偏移量處寫入8字節的redo日誌類型
-
MLOG_WRITE_STRING(type=30) : 表示在頁面的某個偏移量處寫入一串數據，但是因為不能確定寫入的具體數據佔用多少字節，所以需要在日誌結構中添加一個Len字段
redo log寫入機制
每個InnoDB存儲引擎至少有1個重做日誌文件組(group)，每個文件組下至少有兩個重做日誌文件，默認的為ib_logfile0、ib_logfile1
日誌組中每個重做日誌的大小一致，並循環使用
InnoDB以環形方式寫入數據到重做日誌文件，當文件1滿了的時候，會自動切換到日誌文件2，當重做日誌文件2也滿時，再切換到重做日誌1
write pos : 表示日誌當前記錄的位置，當ib_logfile_4寫滿後，會從ib_logfile_1開始記錄
check point : 表示將日誌記錄的修改寫進磁盤，完成數據落盤，數據落盤後checkpoint會將日誌上的相關記錄擦出掉，即write pos > checkpoint 之間的部分是redo log空着的部分(順指針)，用於記錄新的記錄，checkpoint -> write pos之間是redo log待落盤的數據修改記錄如果write pos追上checkpoint，表示寫滿，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint推進以下
redo log相關參數
- innodb_log_buffer_size : log buffer的大小，通常設置為8M ~ 16M(因為MySQL每秒都會將日誌緩衝區的內容刷到日誌文件，因此無需設置超過1秒所需的內存空間)
- innodb_log_file_size : 事務日誌的大小，默認48M
- innodb_log_files_group = 2 : 事務日誌組中的事務日誌個數，默認2個
- innodb_log_group_home_dir = ./ : 事務日誌組路徑，當前目錄表示數據目錄
8、撤銷日誌(undo log)
undo log基本概念
undo log是一種用於撤銷回退的日誌，在事務沒提交之前，MySQL會先記錄更新前的數據到undo log日誌文件裏，當事務回滾或者數據崩潰時，可以利用undo log來進行回滾，回到事務開始之前的狀態
undo log的作用
- 提供回滾操作(undo log實現事務的原子性)
在數據修改的時候，不僅記錄了redo log，還記錄了想對應的undo log，如果因為某些原因導致事務失敗或回滾了，可以藉助undo log進行回滾
undo log和redo log記錄物理日誌不一樣，它是邏輯日誌。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄 - 提供多版本控制(MVCC)(undo log實現多版本併發控制MVCC)
MVCC，即多版本控制。在MySQL數據庫InnoDB存儲引擎中，用undo log來實現多版本併發控制(MVCC)，當讀取的某一行被其他事務鎖定，它可以從undo log中分析出來該行記錄以前的數據版本是怎樣的，從而讓用户能投讀取到當前事務之氣的數據(快照讀)
undo log的工作原理
在更新數據之前，MySQL會提前生成undo log日誌，當事務提交的時候，並不會立即刪除undo log，因為後面可能需要進行回滾操作，要執行回滾(rollback)操作時，從緩存中讀取數據。undo log日誌的刪除是通過後台purge線程進行處理的
undo log的存儲機制
為了保證事務併發操作時，在寫各自的undo log時不產生衝突，InnoDB採用回滾的方式來維護undo log的併發寫入和持久化
rollback segment稱為回滾段 共有128個，每個回滾段中有1024個undo log segment，即支持128 * 1024個undo操作
undo log日誌裏面不僅存放着數據更新前的記錄，還記錄着RowID、事務ID、回滾指針。
其中事務ID每次遞增，回滾指針第一次如果是insert語句的話，回滾指針為NULL，第二次update之後的undo log的回滾指針就會指向剛剛那一條undo log日誌，以此類推，就會形成一條undo log的回滾鏈，方便找到該記錄的歷史版本
undo log相關參數
MySQL與undo相關的參數設置：
- innodb_max_undo_log_size
表示每一個undo log對應的日誌文件的最大值，默認最大值為1GB大小，默認初始化大小為10MB
日誌文件達到該閾值之後，且參數 innodb_undo_log_truncate=ON，才會觸發truncate回收(收縮)動作，被truncate後的表空間文件大小縮小到undo log表空間數據文件默認的10MB大小。否則即便是到達最大之後，也不會自動回收undo log的表空間 - innodb_undo_directory
指定undo log日誌的存儲目錄，默認值為./ - innodb_undo_logs
在MySQL5.6版本之後，可以通過此參數自定義多個rollback segment，默認值為128 - innodb_undo_tablespaces
設置undo獨立表空間個數，範圍為0-128，默認為0，0表示不開啓獨立undo表空間且undo日誌存儲在ibdata文件中
當DB寫壓力較大時，可以設置獨立undo表空間，把undo從ibdata文件中分離開來，指定innodb_undo_directory目錄存放，可以指定到SSD，加快undo log的讀寫性能 - innodb_undo_log_truncate
表示是否開啓自動收縮undo log的表空間操作。如果配置為ON，並且配置了2個或2個以上的undo log表空間數據文件，當某一個日誌文件大小超過設置的最大值之後，就會自動的收縮數據文件，配置為2的原因是，在表空間收縮的時候，至少有一個undo log是在線的 - innodb_purge_rseg_truncate_frequency
控制回收(收縮)undo log的頻率。undo log空間在它的回滾段沒有得到釋放之前不會收縮，想要增加釋放回滾區間的頻率，就得降低innodb_purge_rseg_truncate_frequency設定值，該值越小，undo表空間被嘗試truncate的頻率越高。默認值為128次
9、二進制日誌(Binlog)
Binlog基本概念
binlog是一個二進制格式的文件，用於記錄用户對數據庫更新的SQL語句信息，例如更改數據庫表和更改內容的SQL語句都會記錄在binlog裏，但是不會記錄SELECT和SHOW這類操作
默認情況下，binlog日誌是二進制格式的，不能使用查詢文本工具的命令(比如，cat,vi等)查看，可以使用mysqlbinlog解析查看
開啓Binlog日誌有以下兩個重要的使用場景：
- 主從複製：在主庫中開啓Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog後實現數據恢復達到主從數據一致性
- 數據恢復：通過mysqlbinlog工具來恢復數據
Binlog日誌三種模式
- ROW(row-based replication，RBR) : 日誌中會記錄每一行數據被修改的情況，然後再slave端對相同的數據進行修改
優點：能情況記錄每一行數據的修改細節，能完全實現主從數據同步和數據的恢復
缺點：批量操作，會產生大量的日誌，尤其是alter table會讓日誌暴漲 - STATEMENT(statement-based replication，SBR) : 記錄每一條修改數據的SQL語句(批量修改時，記錄的不是單SQL語句，而是批量修改的SQL語句事件)，slave在複製的時候SQL進程會解析成和原來master端執行過的相同SQL再次執行。簡稱SQL語句複製
優點：日誌量小，減少磁盤IO，提升存儲和恢復速率
缺點：在某些情況下會導致數據不一致，比如last_insert_id()、now()等函數
- MIXED(mixed-based replication，MBR) : 以上兩種模式的混合使用，一般會使用STATEMENT模式保存binlog，對於STATEMENT模式無法複製的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇寫模式
企業場景如何選擇Binlog的模式
- 如果生產中使用MySQL的特殊功能相對少(存儲過程、觸發器、函數)。選擇默認的語句模式，Statement Level
- 如果生成中使用MySQL的特殊功能較多的，可以選擇Mixed模式
- 如果生產中使用MySQL的特殊功能較多，又希望數據最大化一致，此時最好Row Level模式，但是要注意，該模式的binlog非常”沉重”
Binlog文件結構
MySQL的binlog文件中記錄的是對數據庫的各種修改操作，用來標識修改操作的數據結構是Log Event。不同的修改操作對應不同的log event。比如常用的log event有 : Query Event、Row Event、Xid Event等。binlog文件的內容就是各種Log Event的集合
Binlog寫入機制
binlog什麼時候刷新到磁盤？binlog刷數據到磁盤跟參數sync_binlog相關
- 如果設置為0，則表示MySQl不控制binlog的刷新，由文件系統去控制日誌的刷新
-
如果設置為不為0的值，則標識每sync_binlog次事務，MySQL調用文件系統刷新binlog到磁盤中
寫入 :
Redo log和Binlog的區別
- redo log是InnoDB存儲引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用
- redo log是物理日誌，記錄的是在”XXX數據頁上做了XXX的修改”；binlog是邏輯日誌，記錄的是原始邏輯，其記錄的是對應的SQL語句
- redo log 是循環寫的，空間一定會用完，需要write pos和check point搭配；binlog是追加寫，寫到一定大小會切換到下一個，並不會覆蓋以前的日誌
- redo log作為服務器異常宕機後數據自動恢復使用，binlog可以作為主從複製和數據恢復使用。binlog沒有自動crash-safe能力
Binlog命令操作
- 使用sql命令查看binlog文件
啓動binlog
- 啓動成功之後，我們可以登錄查看我們的配置是否起作用
- 查看binlog文件列表
- 查看正在寫入的binlog文件
- 查看binlog文件信息
- mysql提供了一個用於查看binlog日誌的工具，叫做mysqlbinlog
10、新版本結構演進
MySQL 5.7
- 將undo日誌從共享表空間ibdata文件中分離出來，可以在安裝MySQL時由用户自定指定文件大小和數量
- 增加了temporary臨時空間，裏面存儲着臨時表和臨時查詢結構數據集的數據
- Buffer Pool大小可以動態修改，無需重啓數據庫實例
MySQL 8.0
- 將InnoDB表的數據字典和undo都從共享表空間ibdata中徹底分離出來了，以前需要ibdata中數據字典與獨立表空間ibd文件中數據字典一直才行，8.0版本就不需要了
- temporary臨時表空間也可以配置多個物理文件，而且均為InnoDB存儲引擎並能創建索引，這樣加快了處理的速度
- 用户可以像Oracle數據庫那這樣設置一些表空間，每個表空間對應多個物理文件，每個表空間可以給多個表使用，但一個表只能存儲在一個表空間中
- 將DoubleWrite Buffer從共享表空間ibdata中也分離出來了
11、InnoDB線程模型
IO Thread
在InnoDB中使用大量的AIO(Asyn IO)來做讀寫處理，這樣可以極大提高數據庫的性能。在InnoDB1.0版本之前共有4個IO Thread，分別是write、read、insert buffer和log thread，後來版本將read thread和write thread分別增加到4個，一共10個了
- read thread : 負責讀取操作，將數據從磁盤加載到緩存page頁。4個
- write thred : 負責寫操作，將緩衝增液刷新到磁盤。4個
- log thread : 負責將日誌緩衝區內容刷新到磁盤。1個
- insert buffer thread : 負責將寫緩衝刷新到磁盤。1個
Purge Thread
事務提交之後，其使用undo日誌將不再需要，因此需要Purge Thread回收已經分配的undo頁
Page Clenaer Thread
作用是將髒數據刷新到磁盤，髒數據刷盤後相應的redo log也就可以覆蓋，即可以同步數據，又能達到redo log循環使用的目的。會調用write thread線程處理
Master Thread
Master Thread是InnoDB的主線程，負責調度其他線程，優先級最高。作用是將緩衝池中的數據異步刷新到磁盤，保證數據的一致性。包括：髒頁的刷新(page cleaner thread)、undo頁回收(purge thread)、redo日誌刷新(log thread)、合併寫緩衝等
12、InnoDB數據文件
InnoDB表空間文件結構分為：Tablespace(表空間) -> Segment(段) -> Extent(區) -> Page(頁) -> Row(行)
- Tablespace
表空間能夠看做是InnoDB存儲引擎邏輯結構的最高層，用於存儲多個ibd數據文件，用於存儲表的記錄和索引。一個文件包含多個段
表空間分為：系統表空間、獨立表空間、通用表空間、臨時表空間、undo表空間 - 段(Segment)
段是磁盤上空間分配和回收的申請者，是一個邏輯概念，用來管理物理文件，常見的段有數據段、索引段、回滾段等，其中索引段是非葉子節點部分，而數據段就是葉子節點部分，回滾段用於數據的回滾和版本控制 - 區(Extend)
區是由連續頁組成的空間，每個區的默認大小都是1MB，一個區中有64個連續的頁。為了保證區中頁的連續性，擴展的時候InnoDB存儲引擎一次從磁盤申請4~5個區 - 頁(Page)
區是由連續的頁(Page)組成的空間，默認每一個頁的存儲大小16kb，頁，用於存儲多個Row行記錄。包含很多頁類型，比如數據頁，undo頁，系統頁，事務數據頁，大的BLOB對象頁 - 行(Row)
InnoDB存儲引擎是面向行的(row-oriented)，也就是説數據的存放按行進行存放
行，包含了記錄的字段值，DB_ROW_ID、事務ID(TRX ID)、回滾指針(Roll pointer)、字段指針(Field pointers)等信息
Page結構
Page是整個InnoDB存儲的基本構件，也是InnoDB磁盤管理的最小單位，與數據庫相關的所有內容都存儲在這種Page結構裏
Page分為幾種類型，常見的頁類型有數據頁(B-tree Node)、Undo頁(Undo Log Page)系統頁(System Page)、事務數據頁(Transaction System Page)等
File Header : 文件頭，描述頁信息
Page Header : 頁頭，頁的狀態
Infimum + Supremum : 最大和最小記錄，這是兩個虛擬的行記錄
User Records : 用户記錄，存儲數據記錄
Free Space : 空間空間，頁中還沒有別使用的空間
Page Directory : 頁目錄，存儲用户記錄的先對位置
File Trailer : 文件尾，校驗頁是否完整
頁結構整體上可以分為三大部分，分別為通用部分(文件頭、文件尾)、存儲記錄空間、索引部分
- 通用部分(File Header & File Trailer)
主要指文件頭和文件尾，將頁的內容進行封裝，通過文件頭和文件尾校驗的CheckSum方式來確保頁的傳輸是完整的
其中比較重要的是在文件頭中的FIL_PAGE_PREV和FIL_PAGE_NEXT字段，通過這兩個字段，我們可以找到該頁的上一頁和下一頁，實際上所有頁通過兩個字段可以形成一條雙向鏈表
- 記錄部分(User Records & Free Space)
頁的主要作用是存儲記錄，所以”最小和最大記錄”和”用户記錄”部分佔了頁結構的主要空間。另外空閒空間也是靈活的部分，當有新的記錄插入時，會從空閒空間中進行分配用於存儲新記錄
- 索引部分(Page Directory)
數據頁中行記錄按照主鍵由小到大順序串聯成一個單鏈表(頁中記錄是以單向鏈表形式進行存儲的)，單鏈表的鏈表頭尾最小記錄，鏈表尾為最大記錄。並且為了更快速地定位到指定的行記錄，通過 Page Directory 實現目錄的功能，藉助 Page Directory使用二分法快速找到需要查找的行記錄
InnoDB文件存儲格式
show table status;
一般情況下，如果row_format為REDUNDANT、COMPACT，文件格式為Antelope；如果row_format為DYNAMIC和COMPRESSED，文件格式為Barracuda
-
File文件格式(File-Format)
在早期的InnoDB版本中，文件格式只有一種，隨着InnoDB引擎的發展，出現了新文件格式，用於支持新的功能。目前InnoDB只支持兩種文件格式：Antelope和Barracuda- Antelope : 先前未命名，最原始的InnoDB文件格式，它支持兩種行格式：COMPACT和REDUNDANT，MySQL 5.6及以前版本默認格式為Antelope
- Barracuda : 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和DYNAMIC
通過innodb_file_format配置參數可以設置InnoDB文件格式，之前默認值為Antelope，5.7開始之後改為Barracuda
- Row行格式(Row_format)
表的行格式決定了它的行是如何物理存儲的，這反過來又影響查詢和DML操作的性能。如果單個page頁中容納更多行，查詢和檢索查詢可以更加快地工作，緩衝池中所需要的內存更少，寫入更新時需要I/O更少
InnoDB存儲引擎支持四種行格式：Redundant、Compact、Dynamic和Compressed
查詢MySQL使用的行格式，默認為Dynamic
指定語法格式 :
-
COMPACT行記錄格式
compact設計目標是高效地存儲數據，一個頁中存放的行數據越多，其性能就越高
compact行記錄由兩部分組成：記錄放入額外信息和記錄真實數據
記錄額外信息部分
服務器為了描述一條記錄而添加了一些額外信息(元數據信息)，這些額外信息分為3類，分別是：變量字段長度列表、NULL值列表和記錄頭信息- 變長字段長度列表：變長字段的長度是不固定的，所以存儲數據的時候需要把這些數據佔用的字節數也存起來，讀取數據的時候才能根據這個長度列表讀取對應長度的數據。在compact行格式中，把所有變長類型的列的長度都存放在記錄的開頭部分形成一個列表，按照列的順序逆序存放，這個列表就是變長字段長度列表
- NULL值列表：表中某些列可能會存儲NULL 值，如果把這些NULL值都放到記錄的真實數據中會比較浪費空間，所以Compact行格式把這些值為NULL的列存儲到NULL列表中。(如果表中所有列都不允許為NULL，就不存儲NULL值列表)
- 記錄頭信息: 記錄頭信息是由固定的5個字節組成的，5個字節也就是40個二進制位，不同的位代表不同的意思
記錄真實數據部分
記錄的真是數據除了插入的那些列的數據，MySQL會為每個記錄默認添加一些列(也稱為隱藏列)，具體如下：
Compact中的行溢出機制
什麼是溢出行?
MySQL中是以頁為基本單位，進行磁盤與內存之間的數據交互的，我們知道一個頁的大小是16KB，16KB = 16384字節，而一個varchar(m)類型最多可以存儲65532個字節，一些的數據類型，比如TEXT可以存儲更多，如果一個表中存在這樣的大字段，那麼一個頁就無法存儲一條完整的記錄，這時就會發生溢出，多出的數據就會存儲在外部的溢出頁中
總結：如果某些字段信息過長，無法存儲在B數節點中，這時候會被單獨分配空間，此時被稱為溢出頁，該字段稱為頁外列Compact中的行溢出機制
InnoDB規定一頁至少存儲兩條記錄(B+樹特點)，如果頁中只能存放一條記錄，InnoDB存儲引擎會自動將行數據存儲到溢出頁中
當發生行溢出時，數據頁只保存了前768字節的前綴數據，接着是20個字節的偏移量，指向行溢出頁
其他行記錄格式
DYNAMIC和COMPRESSED行記錄格式新格式引入的功能有：數據壓縮、增強型長列數據的頁外存儲和大索引前綴
Compressed和Dynamic行記錄與Compact行記錄格式是類似的，區別是在行溢出時，數據頁不會存儲真是數據的前768字節(完全溢出)，只存儲20個字節的指針來指向溢出頁
Compressed與Dynamic相比，Compressed存儲的行數據會以zlib的算法進行壓縮以節省空間，因此對於BLOB、TEXT、VARCHAR這類大長度類型的數據能夠進行非常有效的存儲
MySQL 5.7默認的行存儲格式是 Dynamic - Redundant
Redundant是MySQl 5.0版本之前InnoDB的行存儲方式
Redundant行記錄格式的首部是一個字段長度偏移列表，同樣是按照列順序逆序放置的。該條記錄中所有列(包含隱藏列、NULL值列表)的長度信息都按照逆序存儲在字段長度偏移列表
13、InnoDB參數優化
Buffer Pool參數優化
緩衝池內存相關
一個大的日誌緩衝區允許大量的事務在提交之前不寫日誌到磁盤。因此，如果你有很多事務的更新，插入或刪除操作，通過設置設個參數會大量的減少磁盤IO的次數
建議：在專用的數據庫服務器上，可以將緩衝池大小設置為服務器物理內存60%~80%
- 查看緩衝大小
- 在線調整InnoDB緩衝大小
- 監控現在調整緩衝池的進度
配置多個 buffer pool 實例
當buffer pool的大小是GB級別的時，將一個buffer pool分割成幾個獨立的實例能降低多個線程同時讀寫緩存頁的競爭性而提高併發性
通過innodb_buffer_pool_instances 參數可以調整實例個數。如果有多個實例，則緩存的數據頁會隨機放置到任意的實例中，且每個實例都有獨立的buffer pool所有的特性
buffer pool可以存放多個instance，每個instance由多個chunk組成。instance的數量範圍和chunk的總數量範圍分別為1-64，1-1000
- innodb_buffer_pool_instances 的默認值是1，最大可以調整為64
待更新。。。，寫文章不易，如感興趣點贊關注，謝謝！
