一、體系結(jié)構(gòu)圖
MySQL 體系結(jié)構(gòu)由 Client Connectors 層痹扇、MySQL Server 層及存儲引擎層組成悯舟。
1. Client Connectors 層
負(fù)責(zé)處理客戶端的連接請求担租，與客戶端創(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 負(fù)責(zé)處理和存儲數(shù)據(jù)庫與客戶端創(chuàng)建的連接狡汉，一個線程負(fù)責(zé)管理一個連接娄徊。Connection Pool 包括了用戶認(rèn)證模塊，就是用戶登錄身份的認(rèn)證和鑒權(quán)及安全管理盾戴，也就是用戶執(zhí)行操作權(quán)限校驗(yàn)寄锐。
2）Service & utilities 是管理服務(wù)&工具集，包括備份恢復(fù)捻脖、安全管理锐峭、集群管理服務(wù)和工具。
3） SQL interface 負(fù)責(zé)接收客戶端發(fā)送的各種 SQL 語句可婶，比如 DML沿癞、DDL 和存儲過程等。
4）Parser解析器 會對 SQL 語句進(jìn)行語法解析生成解析樹矛渴。
5）Optimizer 查詢優(yōu)化器會根據(jù)解析樹生成執(zhí)行計(jì)劃椎扬，并選擇合適的索引，然后按照執(zhí)行計(jì)劃執(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 流行的原因之一汤功。
存儲引擎底部是物理存儲層物邑，是文件的物理存儲層，包括二進(jìn)制日志滔金、數(shù)據(jù)文件色解、錯誤日志、慢查詢?nèi)罩静鸵稹⑷罩久扒edo/undo 日志等。
接下來我們用一條 SQL SELECT 語句的執(zhí)行軌跡來說明客戶端與 MySQL 的交互過程钟病，如下圖所示萧恕。
①通過客戶端/服務(wù)器通信協(xié)議與 MySQL 建立連接刚梭。
②查詢緩存，這是 MySQL 的一個可優(yōu)化查詢的地方票唆，如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句朴读，則將查詢結(jié)果直接返回給客戶端；如果沒有開啟Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進(jìn)行語法語義解析走趋，并生成解析樹衅金。
③預(yù)處理器生成新的解析樹。
④查詢優(yōu)化器生成執(zhí)行計(jì)劃簿煌。
⑤查詢執(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 公司提供的文件訪問層抽象接口定制的一種文件訪問機(jī)制踏施，這種機(jī)制就叫作存儲引擎，下面是一些常用的存儲引擎粉私，有遠(yuǎn)古時期的 MyISAM芹彬、支持事務(wù)的 InnoDB陌宿、內(nèi)存類型的 Memory删窒、歸檔類型的 Archive舟误、列式存儲的 Infobright，以及一些新興的存儲引擎魔招，以 RocksDB 為底層基礎(chǔ)的 MyRocks 和 RocksDB，和以分形樹索引組織存儲的 TokuDB五辽，當(dāng)然現(xiàn)在還有極數(shù)云舟出品的分布式存儲引擎 ArkDB办斑，如下圖所示。
在 MySQL 5.6 版本之前杆逗，默認(rèn)的存儲引擎都是 MyISAM乡翅，但 5.6 版本以后默認(rèn)的存儲引擎就是 InnoDB 了。
InnoDB 存儲引擎的具體架構(gòu)如下圖所示罪郊。上半部分是實(shí)例層（計(jì)算層）蠕蚜，位于內(nèi)存中，下半部分是物理層悔橄，位于文件系統(tǒng)中靶累。
1. 實(shí)例層
我們先來看實(shí)例層腺毫。實(shí)例層分為線程和內(nèi)存。InnoDB 重要的線程有 Master Thread挣柬，Master Thread 是 InnoDB 的主線程潮酒，負(fù)責(zé)調(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)部運(yùn)行的相關(guān)狀態(tài)在各循環(huán)間進(jìn)行切換勃教。
大部分操作在主循環(huán)（loop）中完成，其包含 1s 和 10s 兩種操作匠抗。
1s 操作主要包括如下：
a.日志緩沖刷新到磁盤（這個操作總是被執(zhí)行故源，即使事務(wù)還沒有提交）。
b. 最多可能刷 100 個新臟頁到磁盤戈咳。
c. 執(zhí)行并改變緩沖的操作心软。
d. 若當(dāng)前沒有用戶活動，可能切換到后臺循環(huán)（background loop）等著蛙。10s 操作主要包括如下:
a.最多可能刷新 100 個臟頁到磁盤删铃。
b. 合并至多 5 個被改變的緩沖（總是）。
c. 日志緩沖刷新到磁盤（總是）踏堡。
d. 刪除無用的 Undo 頁（總是）猎唁。
e. 刷新 100 個或者 10 個臟頁到磁盤（總是）產(chǎn)生一個檢查點(diǎn)（總是）等。
2） buf_dump_thread
buf_dump_thread 負(fù)責(zé)將 buffer pool 中的內(nèi)容 dump 到物理文件中顷蟆，以便再次啟動 MySQL 時诫隅，可以快速加熱數(shù)據(jù)。
3） page_cleaner_thread
page_cleaner_thread負(fù)責(zé)將 buffer pool 中的臟頁刷新到磁盤帐偎，在 5.6 版本之前沒有這個線程逐纬，刷新操作都是由主線程完成的，所以在刷新臟頁時會非常影響 MySQL 的處理能力削樊，在5.7 版本之后可以通過參數(shù)設(shè)置開啟多個 page_cleaner_thread豁生。
4） purge_thread
purge_thread 負(fù)責(zé)將不再使用的 Undo 日志進(jìn)行回收。
5）read_thread
read_thread 處理用戶的讀請求漫贞，并負(fù)責(zé)將數(shù)據(jù)頁從磁盤上讀取出來甸箱，可以通過參數(shù)設(shè)置線程數(shù)量。
6）write_thread
write_thread 負(fù)責(zé)將數(shù)據(jù)頁從緩沖區(qū)寫入磁盤迅脐，也可以通過參數(shù)設(shè)置線程數(shù)量芍殖，page_cleaner 線程發(fā)起刷臟頁操作后 write_thread 就開始工作了。
7）redo_log_thread
redo_log_thread 負(fù)責(zé)把日志緩沖中的內(nèi)容刷新到 Redo log 文件中谴蔑。
8）insert_buffer_thread
insert_buffer_thread 負(fù)責(zé)把 Insert Buffer 中的內(nèi)容刷新到磁盤豌骏。實(shí)例層的內(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樱溉，主要解決由于宕機(jī)引起的物理寫入操作中斷，數(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é)點(diǎn)中脱惰，分支節(jié)點(diǎn)（即非葉子頁）存儲索引分支查找的數(shù)據(jù)值。
Redo 日志中包括多個 Redo 文件窿春，這些文件循環(huán)使用拉一，當(dāng)達(dá)到一定存儲閾值時會觸發(fā)checkpoint 刷臟頁操作，同時也會在 MySQL 實(shí)例異常宕機(jī)后重啟谁尸，InnoDB 表數(shù)據(jù)自動還原恢復(fù)過程中使用。
三纽甘、內(nèi)存和物理結(jié)構(gòu)
上面我們介紹了 MySQL InnoDB 存儲引擎的具體架構(gòu)良蛮，下面重點(diǎn)講解內(nèi)存和物理結(jié)構(gòu)，如下圖所示悍赢。
用戶讀取或者寫入的最新數(shù)據(jù)都存儲在 Buffer Pool 中决瞳，如果 Buffer Pool 中沒有找到則會讀取物理文件進(jìn)行查找货徙，之后存儲到 Buffer Pool 中并返回給 MySQL Server。Buffer Pool 采用LRU 機(jī)制皮胡，具體的內(nèi)存隊(duì)列和刷新機(jī)制建議你課后學(xué)習(xí)了解下痴颊，這里不詳細(xì)講述。
Buffer Pool 決定了一個 SQL 執(zhí)行的速度快慢屡贺，如果查詢結(jié)果頁都在內(nèi)存中則返回結(jié)果速度很快蠢棱，否則會產(chǎn)生物理讀（磁盤讀），返回結(jié)果時間變長甩栈，性能遠(yuǎn)不如存儲在內(nèi)存中泻仙。但我們又不能將所有數(shù)據(jù)頁都存儲到 Buffer Pool 中，比如物理 ibd 文件有 500GB量没，我們的機(jī)器不可能配置能容得下 500GB 數(shù)據(jù)頁的內(nèi)存玉转，因?yàn)檫@樣做成本很高而且也沒必要。在單機(jī)單實(shí)例情況下殴蹄，我們可以配置 Buffer Pool 為物理內(nèi)存的 60%~80%究抓，剩余內(nèi)存用于 session 產(chǎn)生的 sort 和 join 等，以及運(yùn)維管理使用袭灯。如果是單機(jī)多實(shí)例刺下，所有實(shí)例的buffer pool總量也不要超過物理內(nèi)存的80%。開始時我們可以根據(jù)經(jīng)驗(yàn)設(shè)置一個 Buffer Pool 的經(jīng)驗(yàn)值妓蛮，比如 16GB怠李，之后業(yè)務(wù)在 MySQL 運(yùn)行一段時間后可以根據(jù) show global status like '%buffer_pool_wait%' 的值來看是否需要調(diào)整 Buffer Pool 的大小。
Redo log 是一個循環(huán)復(fù)用的文件集蛤克，負(fù)責(zé)記錄 InnoDB 中所有對 Buffer Pool的物理修改日志捺癞，當(dāng) Redo log文件空間中，檢查點(diǎn)位置的 LSN 和最新寫入的 LSN 差值（checkpoint_age）達(dá)到 Redo log 文件總空間的 75% 后构挤，InnoDB 會進(jìn)行異步刷新操作髓介，直到降至 75% 以下，并釋放 Redo log 的空間筋现；當(dāng) checkpoint_age 達(dá)到文件總量大小的 90% 后唐础，會觸發(fā)同步刷新，此時 InnoDB 處于掛起狀態(tài)無法操作矾飞。
這樣我們就看到 Redo log 的大小直接影響了數(shù)據(jù)庫的處理能力一膨，如果設(shè)置太小會導(dǎo)致強(qiáng)行 checkpoint 操作頻繁刷新臟頁，那我們就需要將 Redo log 設(shè)置的大一些洒沦，5.6 版本之前 Redo log 總大小不能超過 3.8GB豹绪，5.7 版本之后放開了這個限制。那既然太小影響性能申眼，是不是設(shè)置得越大越好呢瞒津，這個問題留給你課后自己思考蝉衣。
事務(wù)提交時 log buffer 會刷新到 Redo log 文件中，具體刷新機(jī)制由參數(shù)控制巷蚪，你可以課后學(xué)習(xí)并根據(jù)自身業(yè)務(wù)特點(diǎn)進(jìn)行配置病毡。
若參數(shù) innodb_file_per_table=ON，則表示用戶建表時采用用戶獨(dú)立表空間屁柏，即一個表對應(yīng)一組物理文件啦膜，.frm 表定義文件和 .ibd 表數(shù)據(jù)文件。
當(dāng)然若這個參數(shù)設(shè)置為 OFF前联，則表示用戶建表存儲在 ibdata 文件中功戚，不建議采用共享表空間，這樣會導(dǎo)致 ibdata 文件過大似嗤，而且當(dāng)表刪除后空間無法回收啸臀。獨(dú)立表空間可以在用戶刪除大量數(shù)據(jù)后回收物理空間，執(zhí)行一個 DDL 就可以將表空間的高水位降下來了烁落。
新版本特性
這里主要講解一下 MySQL 5.7 版本和 8.0 版本的一些新特點(diǎn)乘粒。
MySQL 5.7 版本新特性如下：
將 Undo 從共享表空間 ibdata 文件中分離出來，可以在安裝 MySQL 時由用戶自行指定文件大小和數(shù)量伤塌。
增加了 temporary 臨時表空間灯萍，里面存儲著臨時表或臨時查詢結(jié)果集的數(shù)據(jù)。
Buffer Pool 大小可以動態(tài)修改每聪，無需重啟數(shù)據(jù)庫實(shí)例旦棉，這是 DBA 的福音。
MySQL 8.0 版本新特性如下：
將 InnoDB 表的數(shù)據(jù)字典和 Undo 都從共享表空間 ibdata 中徹底分離出來了药薯，以前需要ibdata 文件中數(shù)據(jù)字典與獨(dú)立表空間 ibd 文件中數(shù)據(jù)字典一致才行绑洛，8.0 版本就不需要了。
temporary 臨時表空間也可以配置多個物理文件童本，而且均為 InnoDB 存儲引擎并能創(chuàng)建索引真屯，這樣加快了處理的速度。
用戶可以像 Oracle 數(shù)據(jù)庫那樣設(shè)置一些表空間穷娱，每個表空間對應(yīng)多個物理文件绑蔫，每個表空間可以給多個表使用，但一個表只能存儲在一個表空間中泵额。
InnoDB 和 MyISAM
這里對比幾個主流的存儲引擎配深，如下圖所示。從圖中可以詳細(xì)看到 InnoDB 和 MyISAM 的對比嫁盲。
接下來重點(diǎn)在功能和性能上對比 InnoDB 和 MyISAM篓叶。
1）功能對比
InnoDB 和 MyISAM 的功能對比如下圖所示。
InnoDB 支持 ACID 的事務(wù) 4 個特性，而 MyISAM 不支持澜共；
InnoDB 支持 4 種事務(wù)隔離級別，默認(rèn)是可重復(fù)讀 Repeatable Read 的锥腻，MyISAM 不支持嗦董；
InnoDB 支持 crash 安全恢復(fù)，MyISAM 不支持瘦黑；
InnoDB 支持外鍵京革，MyISAM 不支持；
InnoDB 支持行級別的鎖粒度幸斥，MyISAM 不支持匹摇，只支持表級別的鎖粒度；
InnoDB 支持 MVCC甲葬，MyISAM 不支持廊勃；
InnoDB 表最大還可以支持 64TB，支持聚簇索引经窖、支持壓縮數(shù)據(jù)存儲坡垫，支持?jǐn)?shù)據(jù)加密，支持查詢/索引/數(shù)據(jù)高速緩存画侣，支持自適應(yīng)hash索引冰悠、空間索引，支持熱備份和恢復(fù)等配乱，如下圖所示溉卓。
2）性能對比
在性能對比上，InnoDB 也完勝 MyISAM搬泥，如下圖所示桑寨。
讀寫混合模式下，隨著 CPU 核數(shù)的增加佑钾，InnoDB 的讀寫能力呈線性增長西疤，
在測試用例里，最高可達(dá)近 9000 的 TPS休溶，但 MyISAM 因?yàn)樽x寫不能并發(fā)代赁，它的處理能力跟核數(shù)沒關(guān)系，呈一條水平線兽掰，TPS 低于 500芭碍。
只讀模式下，隨著 CPU 核數(shù)的增加孽尽，InnoDB 的讀寫能力呈線性增長窖壕，最高可達(dá)近 14000 的 TPS，但 MyISAM 的處理能力不到 3000 TPS。
以上測試僅為說明 InnoDB 比 MyISAM 的處理能力強(qiáng)大瞻讽，具體 TPS 測試數(shù)據(jù)跟硬件和測試條件不同而有很大差異鸳吸。
InnoDB 存儲引擎
1）核心特性
InnoDB 存儲引擎的核心特性包括：MVCC、鎖速勇、鎖算法和分類晌砾、事務(wù)羽历、表空間和數(shù)據(jù)頁偷拔、內(nèi)存線程以及狀態(tài)查詢。其中鎖和事務(wù)會在下一節(jié)課時講解瓶盛。本課時我們已經(jīng)學(xué)習(xí)了 InnoDB 的表空間和實(shí)例等都伪。思維導(dǎo)圖如下所示呕乎，由于時間有限有些內(nèi)容不能展開講，建議你課下多下功夫重點(diǎn)研究陨晶。
2）ARIES 三原則
ARIES 三原則猬仁，是指 Write Ahead Logging（WAL）。
先寫日志后寫磁盤先誉，日志成功寫入后事務(wù)就不會丟失逐虚，后續(xù)由 checkpoint 機(jī)制來保證磁盤物理文件與 Redo 日志達(dá)到一致性；
利用 Redo 記錄變更后的數(shù)據(jù)谆膳，即 Redo 記錄事務(wù)數(shù)據(jù)變更后的值叭爱；
利用 Undo 記錄變更前的數(shù)據(jù)，即 Undo 記錄事務(wù)數(shù)據(jù)變更前的值漱病，用于回滾和其他事務(wù)多版本讀买雾。
show engine innodb status\G 的結(jié)果里面有詳細(xì)的 InnoDB 運(yùn)行態(tài)信息，分段記錄的杨帽，包括內(nèi)存漓穿、線程、信號注盈、鎖晃危、事務(wù)等，請你多多使用老客，出現(xiàn)問題時從中能分析出具體原因和解決方案僚饭。
ps:學(xué)習(xí)課程筆記，請勿做商業(yè)用途胧砰！
