一、體系結(jié)構(gòu)圖
MySQL 體系結(jié)構(gòu)由 Client Connectors 層、MySQL Server 層及存儲引擎層組成。
1. Client Connectors 層
負責處理客戶端的連接請求，與客戶端創(chuàng)建連接。目前 MySQL 幾乎支持所有的連接類型，例如常見的 JDBC、Python、Go 等。
2. MySQL Server 層
MySQL Server 層主要包括 Connection Pool、Service & utilities、SQL interface、Parser解析器、Optimizer 查詢優(yōu)化器、Caches 緩存等模塊。
1）Connection Pool 負責處理和存儲數(shù)據(jù)庫與客戶端創(chuàng)建的連接，一個線程負責管理一個連接。Connection Pool 包括了用戶認證模塊，就是用戶登錄身份的認證和鑒權(quán)及安全管理，也就是用戶執(zhí)行操作權(quán)限校驗。
2）Service & utilities 是管理服務(wù)&工具集，包括備份恢復(fù)、安全管理、集群管理服務(wù)和工具。
3） SQL interface 負責接收客戶端發(fā)送的各種 SQL 語句，比如 DML、DDL 和存儲過程等。
4）Parser解析器 會對 SQL 語句進行語法解析生成解析樹。
5）Optimizer 查詢優(yōu)化器會根據(jù)解析樹生成執(zhí)行計劃，并選擇合適的索引，然后按照執(zhí)行計劃執(zhí)行 SQL 語言并與各個存儲引擎交互。
6）Caches 緩存包括各個存儲引擎的緩存部分，比如：InnoDB 存儲的 Buffer Pool、MyISAM 存儲引擎的 key buffer 等，Caches 中也會緩存一些權(quán)限，也包括一些 Session 級別的緩存。
3.存儲引擎層
存儲引擎包括 MyISAM、InnoDB，以及支持歸檔的 Archive 和內(nèi)存的 Memory 等。MySQL是插件式的存儲引擎，只要正確定義與 MySQL Server 交互的接口，任何引擎都可以訪問MySQL，這也是 MySQL 流行的原因之一。
存儲引擎底部是物理存儲層，是文件的物理存儲層，包括二進制日志、數(shù)據(jù)文件、錯誤日志、慢查詢?nèi)罩?、全日志、redo/undo 日志等。
接下來我們用一條 SQL SELECT 語句的執(zhí)行軌跡來說明客戶端與 MySQL 的交互過程，如下圖所示。
①通過客戶端/服務(wù)器通信協(xié)議與 MySQL 建立連接。
②查詢緩存，這是 MySQL 的一個可優(yōu)化查詢的地方，如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句，則將查詢結(jié)果直接返回給客戶端；如果沒有開啟Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進行語法語義解析，并生成解析樹。
③預(yù)處理器生成新的解析樹。
④查詢優(yōu)化器生成執(zhí)行計劃。
⑤查詢執(zhí)行引擎執(zhí)行 SQL 語句，此時查詢執(zhí)行引擎會根據(jù) SQL 語句中表的存儲引擎類型，以及對應(yīng)的 API 接口與底層存儲引擎緩存或者物理文件的交互情況，得到查詢結(jié)果，由MySQL Server 過濾后將查詢結(jié)果緩存并返回給客戶端。若開啟了 Query Cache，這時也會將SQL 語句和結(jié)果完整地保存到 Query Cache 中，以后若有相同的 SQL 語句執(zhí)行則直接返回結(jié)果。
二、存儲引擎概述
存儲引擎是 MySQL 中具體與文件打交道的子系統(tǒng)，它是根據(jù) MySQL AB 公司提供的文件訪問層抽象接口定制的一種文件訪問機制，這種機制就叫作存儲引擎，下面是一些常用的存儲引擎，有遠古時期的 MyISAM、支持事務(wù)的 InnoDB、內(nèi)存類型的 Memory、歸檔類型的 Archive、列式存儲的 Infobright，以及一些新興的存儲引擎，以 RocksDB 為底層基礎(chǔ)的 MyRocks 和 RocksDB，和以分形樹索引組織存儲的 TokuDB，當然現(xiàn)在還有極數(shù)云舟出品的分布式存儲引擎 ArkDB，如下圖所示。
在 MySQL 5.6 版本之前，默認的存儲引擎都是 MyISAM，但 5.6 版本以后默認的存儲引擎就是 InnoDB 了。
InnoDB 存儲引擎的具體架構(gòu)如下圖所示。上半部分是實例層（計算層），位于內(nèi)存中，下半部分是物理層，位于文件系統(tǒng)中。
1. 實例層
我們先來看實例層。實例層分為線程和內(nèi)存。InnoDB 重要的線程有 Master Thread，Master Thread 是 InnoDB 的主線程，負責調(diào)度其他各線程。
1）Master Thread
Master Thread 的優(yōu)先級最高, 其內(nèi)部包含幾個循環(huán)：主循環(huán)（loop）、后臺循環(huán)（background loop）、刷新循環(huán)（flush loop）、暫停循環(huán)（suspend loop）。Master Thread 會根據(jù)其內(nèi)部運行的相關(guān)狀態(tài)在各循環(huán)間進行切換。
大部分操作在主循環(huán)（loop）中完成，其包含 1s 和 10s 兩種操作。
1s 操作主要包括如下：
a.日志緩沖刷新到磁盤（這個操作總是被執(zhí)行，即使事務(wù)還沒有提交）。
b. 最多可能刷 100 個新臟頁到磁盤。
c. 執(zhí)行并改變緩沖的操作。
d. 若當前沒有用戶活動，可能切換到后臺循環(huán)（background loop）等。10s 操作主要包括如下:
a.最多可能刷新 100 個臟頁到磁盤。
b. 合并至多 5 個被改變的緩沖（總是）。
c. 日志緩沖刷新到磁盤（總是）。
d. 刪除無用的 Undo 頁（總是）。
e. 刷新 100 個或者 10 個臟頁到磁盤（總是）產(chǎn)生一個檢查點（總是）等。
2） buf_dump_thread
buf_dump_thread 負責將 buffer pool 中的內(nèi)容 dump 到物理文件中，以便再次啟動 MySQL 時，可以快速加熱數(shù)據(jù)。
3） page_cleaner_thread
page_cleaner_thread負責將 buffer pool 中的臟頁刷新到磁盤，在 5.6 版本之前沒有這個線程，刷新操作都是由主線程完成的，所以在刷新臟頁時會非常影響 MySQL 的處理能力，在5.7 版本之后可以通過參數(shù)設(shè)置開啟多個 page_cleaner_thread。
4） purge_thread
purge_thread 負責將不再使用的 Undo 日志進行回收。
5）read_thread
read_thread 處理用戶的讀請求，并負責將數(shù)據(jù)頁從磁盤上讀取出來，可以通過參數(shù)設(shè)置線程數(shù)量。
6）write_thread
write_thread 負責將數(shù)據(jù)頁從緩沖區(qū)寫入磁盤，也可以通過參數(shù)設(shè)置線程數(shù)量，page_cleaner 線程發(fā)起刷臟頁操作后 write_thread 就開始工作了。
7）redo_log_thread
redo_log_thread 負責把日志緩沖中的內(nèi)容刷新到 Redo log 文件中。
8）insert_buffer_thread
insert_buffer_thread 負責把 Insert Buffer 中的內(nèi)容刷新到磁盤。實例層的內(nèi)存部分主要包含 InnoDB Buffer Pool，這里包含 InnoDB 最重要的緩存內(nèi)容。數(shù)據(jù)和索引頁、undo 頁、insert buffer 頁、自適應(yīng) Hash 索引頁、數(shù)據(jù)字典頁和鎖信息等。additional memory pool 后續(xù)已不再使用。Redo buffer 里存儲數(shù)據(jù)修改所產(chǎn)生的 Redo log。double write buffer 是 double write 所需的 buffer，主要解決由于宕機引起的物理寫入操作中斷，數(shù)據(jù)頁不完整的問題。
2.物理層
下面我們來看看物理層，物理層在邏輯上分為系統(tǒng)表空間、用戶表空間和 Redo日志。
系統(tǒng)表空間里有 ibdata 文件和一些 Undo，ibdata 文件里有 insert buffer 段、double write段、回滾段、索引段、數(shù)據(jù)字典段和 Undo 信息段。
用戶表空間是指以 .ibd 為后綴的文件，文件中包含 insert buffer 的 bitmap 頁、葉子頁（這里存儲真正的用戶數(shù)據(jù)）、非葉子頁。InnoDB 表是索引組織表，采用 B+ 樹組織存儲，數(shù)據(jù)都存儲在葉子節(jié)點中，分支節(jié)點（即非葉子頁）存儲索引分支查找的數(shù)據(jù)值。
Redo 日志中包括多個 Redo 文件，這些文件循環(huán)使用，當達到一定存儲閾值時會觸發(fā)checkpoint 刷臟頁操作，同時也會在 MySQL 實例異常宕機后重啟，InnoDB 表數(shù)據(jù)自動還原恢復(fù)過程中使用。
三、內(nèi)存和物理結(jié)構(gòu)
上面我們介紹了 MySQL InnoDB 存儲引擎的具體架構(gòu)，下面重點講解內(nèi)存和物理結(jié)構(gòu)，如下圖所示。
用戶讀取或者寫入的最新數(shù)據(jù)都存儲在 Buffer Pool 中，如果 Buffer Pool 中沒有找到則會讀取物理文件進行查找，之后存儲到 Buffer Pool 中并返回給 MySQL Server。Buffer Pool 采用LRU 機制，具體的內(nèi)存隊列和刷新機制建議你課后學習了解下，這里不詳細講述。
Buffer Pool 決定了一個 SQL 執(zhí)行的速度快慢，如果查詢結(jié)果頁都在內(nèi)存中則返回結(jié)果速度很快，否則會產(chǎn)生物理讀（磁盤讀），返回結(jié)果時間變長，性能遠不如存儲在內(nèi)存中。但我們又不能將所有數(shù)據(jù)頁都存儲到 Buffer Pool 中，比如物理 ibd 文件有 500GB，我們的機器不可能配置能容得下 500GB 數(shù)據(jù)頁的內(nèi)存，因為這樣做成本很高而且也沒必要。在單機單實例情況下，我們可以配置 Buffer Pool 為物理內(nèi)存的 60%~80%，剩余內(nèi)存用于 session 產(chǎn)生的 sort 和 join 等，以及運維管理使用。如果是單機多實例，所有實例的buffer pool總量也不要超過物理內(nèi)存的80%。開始時我們可以根據(jù)經(jīng)驗設(shè)置一個 Buffer Pool 的經(jīng)驗值，比如 16GB，之后業(yè)務(wù)在 MySQL 運行一段時間后可以根據(jù) show global status like '%buffer_pool_wait%' 的值來看是否需要調(diào)整 Buffer Pool 的大小。
Redo log 是一個循環(huán)復(fù)用的文件集，負責記錄 InnoDB 中所有對 Buffer Pool的物理修改日志，當 Redo log文件空間中，檢查點位置的 LSN 和最新寫入的 LSN 差值（checkpoint_age）達到 Redo log 文件總空間的 75% 后，InnoDB 會進行異步刷新操作，直到降至 75% 以下，并釋放 Redo log 的空間；當 checkpoint_age 達到文件總量大小的 90% 后，會觸發(fā)同步刷新，此時 InnoDB 處于掛起狀態(tài)無法操作。
這樣我們就看到 Redo log 的大小直接影響了數(shù)據(jù)庫的處理能力，如果設(shè)置太小會導(dǎo)致強行 checkpoint 操作頻繁刷新臟頁，那我們就需要將 Redo log 設(shè)置的大一些，5.6 版本之前 Redo log 總大小不能超過 3.8GB，5.7 版本之后放開了這個限制。那既然太小影響性能，是不是設(shè)置得越大越好呢，這個問題留給你課后自己思考。
事務(wù)提交時 log buffer 會刷新到 Redo log 文件中，具體刷新機制由參數(shù)控制，你可以課后學習并根據(jù)自身業(yè)務(wù)特點進行配置。
若參數(shù) innodb_file_per_table=ON，則表示用戶建表時采用用戶獨立表空間，即一個表對應(yīng)一組物理文件，.frm 表定義文件和 .ibd 表數(shù)據(jù)文件。
當然若這個參數(shù)設(shè)置為 OFF，則表示用戶建表存儲在 ibdata 文件中，不建議采用共享表空間，這樣會導(dǎo)致 ibdata 文件過大，而且當表刪除后空間無法回收。獨立表空間可以在用戶刪除大量數(shù)據(jù)后回收物理空間，執(zhí)行一個 DDL 就可以將表空間的高水位降下來了。
新版本特性
這里主要講解一下 MySQL 5.7 版本和 8.0 版本的一些新特點。
MySQL 5.7 版本新特性如下：
將 Undo 從共享表空間 ibdata 文件中分離出來，可以在安裝 MySQL 時由用戶自行指定文件大小和數(shù)量。
增加了 temporary 臨時表空間，里面存儲著臨時表或臨時查詢結(jié)果集的數(shù)據(jù)。
Buffer Pool 大小可以動態(tài)修改，無需重啟數(shù)據(jù)庫實例，這是 DBA 的福音。
MySQL 8.0 版本新特性如下：
將 InnoDB 表的數(shù)據(jù)字典和 Undo 都從共享表空間 ibdata 中徹底分離出來了，以前需要ibdata 文件中數(shù)據(jù)字典與獨立表空間 ibd 文件中數(shù)據(jù)字典一致才行，8.0 版本就不需要了。
temporary 臨時表空間也可以配置多個物理文件，而且均為 InnoDB 存儲引擎并能創(chuàng)建索引，這樣加快了處理的速度。
用戶可以像 Oracle 數(shù)據(jù)庫那樣設(shè)置一些表空間，每個表空間對應(yīng)多個物理文件，每個表空間可以給多個表使用，但一個表只能存儲在一個表空間中。
InnoDB 和 MyISAM
這里對比幾個主流的存儲引擎，如下圖所示。從圖中可以詳細看到 InnoDB 和 MyISAM 的對比。
接下來重點在功能和性能上對比 InnoDB 和 MyISAM。
1）功能對比
InnoDB 和 MyISAM 的功能對比如下圖所示。
InnoDB 支持 ACID 的事務(wù) 4 個特性，而 MyISAM 不支持；
InnoDB 支持 4 種事務(wù)隔離級別，默認是可重復(fù)讀 Repeatable Read 的，MyISAM 不支持；
InnoDB 支持 crash 安全恢復(fù)，MyISAM 不支持；
InnoDB 支持外鍵，MyISAM 不支持；
InnoDB 支持行級別的鎖粒度，MyISAM 不支持，只支持表級別的鎖粒度；
InnoDB 支持 MVCC，MyISAM 不支持；
InnoDB 表最大還可以支持 64TB，支持聚簇索引、支持壓縮數(shù)據(jù)存儲，支持數(shù)據(jù)加密，支持查詢/索引/數(shù)據(jù)高速緩存，支持自適應(yīng)hash索引、空間索引，支持熱備份和恢復(fù)等，如下圖所示。
2）性能對比
在性能對比上，InnoDB 也完勝 MyISAM，如下圖所示。
讀寫混合模式下，隨著 CPU 核數(shù)的增加，InnoDB 的讀寫能力呈線性增長，
在測試用例里，最高可達近 9000 的 TPS，但 MyISAM 因為讀寫不能并發(fā)，它的處理能力跟核數(shù)沒關(guān)系，呈一條水平線，TPS 低于 500。
只讀模式下，隨著 CPU 核數(shù)的增加，InnoDB 的讀寫能力呈線性增長，最高可達近 14000 的 TPS，但 MyISAM 的處理能力不到 3000 TPS。
以上測試僅為說明 InnoDB 比 MyISAM 的處理能力強大，具體 TPS 測試數(shù)據(jù)跟硬件和測試條件不同而有很大差異。
InnoDB 存儲引擎
1）核心特性
InnoDB 存儲引擎的核心特性包括：MVCC、鎖、鎖算法和分類、事務(wù)、表空間和數(shù)據(jù)頁、內(nèi)存線程以及狀態(tài)查詢。其中鎖和事務(wù)會在下一節(jié)課時講解。本課時我們已經(jīng)學習了 InnoDB 的表空間和實例等。思維導(dǎo)圖如下所示，由于時間有限有些內(nèi)容不能展開講，建議你課下多下功夫重點研究。
2）ARIES 三原則
ARIES 三原則，是指 Write Ahead Logging（WAL）。
先寫日志后寫磁盤，日志成功寫入后事務(wù)就不會丟失，后續(xù)由 checkpoint 機制來保證磁盤物理文件與 Redo 日志達到一致性；
利用 Redo 記錄變更后的數(shù)據(jù)，即 Redo 記錄事務(wù)數(shù)據(jù)變更后的值；
利用 Undo 記錄變更前的數(shù)據(jù)，即 Undo 記錄事務(wù)數(shù)據(jù)變更前的值，用于回滾和其他事務(wù)多版本讀。
show engine innodb status\G 的結(jié)果里面有詳細的 InnoDB 運行態(tài)信息，分段記錄的，包括內(nèi)存、線程、信號、鎖、事務(wù)等，請你多多使用，出現(xiàn)問題時從中能分析出具體原因和解決方案。
ps:學習課程筆記，請勿做商業(yè)用途！
