了解掌握MySQL數據庫的架構設計、文件系統，有利于更全面、系統的掌握MySQL數據庫，是進階精通MySQL的必修課。
MySQL邏輯架構
總體分為客戶端連接器(Connectors)和服務器端(MySQL Server)兩大部分。應用程序客戶端或可視化數據庫客戶端通過提供的連接器連接到服務端來使用MySQL提供的服務。
架構圖
整體架構分為客戶端連接器(Connectors)和服務器端(MySQL Server)兩大部分。其中服務器端分為3層，包括SQL層、可拔插存儲引擎層、文件系統（物理結構），核心在SQL層、可拔插存儲引擎層。
MySQL邏輯架構圖
客戶端(Connectors)
即連接器Connectors，包括MySQL提供的原生C語言API、實現了JDBC的連接器驅動程序等?；旧希蟛糠志幊陶Z言都實現了連接MySQL數據庫的程序包，通過這些程序包連接到MySQL數據庫的程序或應用，也可以認為是MySQL的客戶端。
- Native C API
- JDBC
- ODBC
- .NET
- php
- Python
- Perl
- Ruby
服務器端(Server)
服務器端主要由SQL層、可拔插存儲引擎層、文件系統三部分。
其中SQL層(SQL Layer)包含以下6個組件
- 系統管理和控制工具(Management Services & Utilities )MySQL提供的一些系統管理工具，比如mysql、mysqladmin等命令行工具
- 連接池(Connection Pool)連接池組件負責身份認證authentication、連接池的實現(線程復用、連接限制)、檢查內存，緩存等
- SQL接口(SQL Interfaces)實現各種語句DDL、DML等，以及存儲過程、函數、觸發器、視圖等接口，負責命令分發
- 解析器(Parser)解析SQL語句，負責對SQL語句的詞法和語法解析，形成初級語法樹
- 優化器(Optimizer)對初級語法樹進行優化，生成計劃，選擇合理的索引
- 緩存和緩沖(Caches & Buffers)緩沖提交的SQL請求等；對查詢結果進行緩存，如果有開啟。8.0版本后，緩存Cache已棄用。
在5.5版本后，MySQL默認使用存儲引擎是InnoDB。存儲引擎是以表為單位應用的，創建表時可以指定使用的存儲引擎，比如
CREATE TABLE tb_name(columns) ENGINE=InnoDB|MyISAM|Memory
以下是MySQL支持的常見引擎
MySQL支持的常見引擎
在5.7版本中，CREATE TABLE語句創建表時默認使用的存儲引擎為InnoDB，可以省略指定。目前使用比較多的存儲引擎包括InnoDB和MyISAM。InnoDB與MyISAM的區別如下表所示，開發人員可以按照業務需求使用不同的引擎
InnoDB與MyISAM的區別
一般情況下，InnoDB、MyISAM、Memory等幾個存儲引擎可以滿足大部分的一個應用場景。下面是這三個存儲引擎的使用場景或選型依據
- InnoDB
支持事務，支持外鍵，支持崩潰修復和并發控制。對事務的完整性要求比較高(比如銀行)，要求實現并發控制(比如售票)，頻繁更新、刪除數據，這類應用場景建議使用InnoDB，因為支持事務的提交(commit)和回滾(rollback)。
- MyISAM
插入數據快，空間和內存使用比較低。如果主要用于插入新記錄和讀取記錄，或應用的完整性和并發性要求比較低，可以考慮選擇MyISAM。
- Memory
數據存儲在內存中，處理速度較快，但安全性不高。如果需要很快的讀寫速度，對數據安全性要求不高，數據作為臨時處理使用或不需要持久保存，可以選擇Memory。它對表的大小有要求，不能建立太大的表。
注意，因為存儲引擎是作用在表上的，所以同一個數據庫，可以使用多個存儲引擎，即不同的表可以有不同的存儲引擎。如果一個表要求比較高的事務處理，可以選擇InnoDB；而查詢比較高的表，可以使用MyISAM；如果該數據庫需要一個用于查詢的臨時表，也可以考慮使用Memory存儲引擎?？傊?，根據不同的一個用場景，可以使用一種存儲引擎或多種存儲引擎組合使用。
文件系統
- 數據文件
包括表定義文件、表數據、索引等
- 日志文件
包括通用日志文件、二級制文件binlog、中繼日志relaylog、重做日志redolog、回滾日志undolog、錯誤日志errlog，以及慢查詢日志slowlog等
執行流程
下圖是MySQL執行一條語句的簡單流程圖
簡單流程圖
物理架構
即文件系統，MySQL的文件組織架構。MySQL是通過文件系統對數據和索引進行存儲的。從物理結構可以分為數據索引文件和日志文件兩大類。其中，數據索引文件采用隨機IO的方式寫入磁盤存儲，日志文件采用順序IO方式寫入磁盤存儲。兩種寫入磁盤的方式對比如下
磁盤寫入方式對比
可以使用下面的命令查看這些文件的保存目錄，如果沒有另外指定，一般情況下在/var/lib/mysql目錄下
show variables like '%datadir%';
數據文件
這里主要介紹比較常用的兩種存儲引擎InnoDB和MyISAM的數據文件構成。
InnoDB數據文件包括以下三種格式
- .frm文件
主要存放與數據表相關的信息，包括表結構的定義信息
- .ibd文件
使用獨享表空間也就是用戶表空間存儲表數據和索引信息，一張表對應一個.ibd文件
- ibdata文件
使用共享表空間也即系統表空間存儲表數據和索引信息，所有表使用一個或多個ibdata文件,文件名一般加上后綴0...N，比如ibdata0，ibdata1等
MyISAM數據文件包括以下三種格式
- .frm文件
主要存放與數據表相關的信息，包括表結構的定義信息
- .myd文件
主要存儲表數據信息
- .myi文件
主要存儲表數據文件中任何索引的數據數
日志文件
可以下面的命令查看日志的開啟情況，開啟或配置了對應的日志文件后，才會生成相應的日志文件。
show variables like '%log_%';
日志文件大概分7類，包括錯誤日志(error log)、二進制日志(bin log)、通用查詢日志(general query log)、慢查詢日志(slow query log)、重做日志(redo log)、回滾日志(undo log)、中繼日志(relay log)。
- 錯誤日志(error log)
錯誤日志默認是開啟的，從5.5.7版本開始以后無法關閉，錯誤日志記錄了運行過程中遇到的所有嚴重錯誤信息，以及MySQL服務器每次啟動和關閉的詳細信息。錯誤日志所記錄的信息是可以通過log-error和log-warnings來定義的，其中log-err是定義是否啟用錯誤日志的功能和錯誤日志的存儲位置，log-warnings是定義是否將警告信息也定義至錯誤日志中。
-- 可以直接定義為文件路徑，也可以為ON|OFF，默認的錯誤日志名稱：hostname.err
log_error=/var/log/mysqld.log
-- 只能使用1|0來定義開關啟動，默認是啟動的
log_warings=1
- 二進制日志(bin log)
二進制日志記錄數據在運行過程中的所有變化。bin log通過存儲數據庫所有DDL和DML語句，但不包括SELECT語句，語句以事件的形式保存，描述了數據的變更順序，bin log還包括了每個更新語句的執行時間信息。如果是DDL語句，則直接記錄到bin log日志，而DML語句，必須通過事務提交才能記錄到bin log日志中。默認是不開啟的，需要手動開啟。產品環境建議開啟。
log-bin=mysql-bin
也可以這樣
log-bin=ON|OFF
log_bin_basename=mysql-bin
-- 有必要也可以指定索引
-- log_bin_index=mysql-bin.index
其中mysql-bin是binlog日志文件的basename，binlog日志文件的完整名稱：mysql-bin-000001.log
- 通用查詢日志(general query log)
通用查詢日志記錄運行過程中的所有內容，耗性能，一般產品化境中不開啟?？梢酝ㄟ^下面的命令查看開通狀態
show global variables like 'general_log';
可以通過下面的配置開通日志
-- 啟動開關
general_log={ON|OFF}
-- 日志文件變量，而general_log_file如果沒有指定，默認名是host_name.log
general_log_file=/PATH/TO/file
-- 記錄類型
log_output={TABLE|FILE|NONE}
- 慢查詢日志(slow query log)
記錄執行較慢的select查詢語句，用于SQL語句調優時開啟，正常情況下不需要開啟，默認亦是關閉的?？梢酝ㄟ^設置全局變量的方式開啟
-- 開啟慢查詢日志
slow_query_log=ON
-- 慢查詢的閾值
long_query_time=3
-- 日志記錄文件如果沒有給出file_name值， 默認為主機名，后綴為-slow.log。
-- 如果給出了文件名，但不是絕對路徑名，文件則寫入數據目錄。
slow_query_log_file=file_name
以上的設置，記錄執行時間超過long_query_time秒的所有查詢語句
- 重做日志(redo log)
事務相關的日志，ACID持久化，崩潰恢復相關。
它用于確保事務的持久性，防止在發生故障的時間點，尚有臟頁未寫入磁盤，在重啟mysql服務的時候，根據redo log進行重做，從而達到事務的持久性這一特性。其記錄的是物理數據頁面的修改的信息，它是順序寫入redo log file的物理文件中去的。
事務開始之后就產生redo log，redo log的落盤并不是隨著事務的提交才寫入的，而是在事務的執行
過程中，便開始寫入redo log文件中。
當對應事務的臟頁寫入到磁盤之后，redo log的使命也就完成了，重做日志占用的空間就可以重用（被覆蓋）。
默認情況下，對應的物理文件位于數據庫的data目錄下的ib_logfile1和ib_logfile2。
-- 指定日志文件組所在的路徑，默認./ ，表示在數據庫的數據目錄下
innodb_log_group_home_dir=./
-- 指定重做日志文件組中文件的數量，默認2
innodb_log_files_in_group=2
-- 重做日志文件的大小
innodb_log_file_size=50331648
-- 重做日志緩沖區大小
innodb_log_buffer_size=16777216
很重要一點，redo log是什么時候寫入磁盤的？前面說了是在事務開始之后逐步寫盤的。之所以說重做日志是在事務開始之后逐步寫入重做日志文件，而不一定是事務提交才寫入重做日志文件，原因就是，重做日志有一個緩存區，其默認大小為8M(或16M)，Innodb存儲引擎先將重做日志內容寫入緩存區中。然后以下三種情況下，都會將緩沖區的日志內容刷新到磁盤
1. 主線程每秒一次執行刷新重做日志緩沖區到重做日志文件
2. 每個事務提交時會將重做日志刷新到重做日志文件，如果有配置這個變量的話
3. 當重做日志緩存可用空間少于一半時，重做日志緩存會被刷新到重做日志文件
由此可以看出，重做日志通過不止一種方式寫入到磁盤，尤其是對于第一種方式，重做日志從緩沖區到重做日志文件是主線程的定時任務。
因此重做日志的寫盤，并不一定是隨著事務的提交才寫入重做日志文件的，而是隨著事務的開始，逐步
開始的。
另外引用《MySQL技術內幕 Innodb 存儲引擎》（page37）上的原話：
即使某個事務還沒有提交，Innodb存儲引擎仍然每秒會將重做日志緩存刷新到重做日志文件。這一點是必須要知道的，因為這可以很好地解釋再大的事務的提交（commit）的時間也是很短暫的。
- 回滾日志(undo log)
也是事務相關的日志，實現回滾。
它保存了事務發生之前的數據的一個版本，可以用于回滾，同時可以提供多版本并發控制下的讀（MVCC），也即非鎖定讀。它是邏輯格式的日志，在執行undo的時候，僅僅是將數據從邏輯上恢復至事務之前的狀態，而不是從物理頁面上操作實現的，這一點是不同于redo log的。
事務開始之前，將當前的版本生成undo log，undo 也會產生 redo 來保證undo log的可靠性 。
當事務提交之后，undo log并不能立馬被刪除，而是放入待清理的鏈表，由purge線程判斷是否由其他事務在使用undo段中表的上一個事務之前的版本信息，決定是否可以清理undo log的日志空間。
MySQL5.6之前，undo表空間位于共享表空間的回滾段中，共享表空間文件的默認的名稱是ibdata，位于數據文件目錄中。
MySQL5.6之后，undo表空間可以配置成獨立的文件，但是需要提前在配置文件中配置，完成數據庫初始化后生效且不可改變undo log文件的個數。如果初始化數據庫之前沒有進行相關配置，那么就無法配置成獨立的表空間了。
關于MySQL5.7之后的獨立undo 表空間配置參數如下
-- undo獨立表空間的存放目錄，默認值是./
innodb_undo_directory = /data/undospace/
--回滾段為128KB，默認值得128
innodb_undo_logs = 128
--指定有4個undo log文件，默認值0
innodb_undo_tablespaces = 4
如果undo使用的共享表空間，這個共享表空間中又不僅僅是存儲了undo的信息，共享表空間的默認為
與MySQL的數據目錄下面，其屬性由參數innodb_data_file_path配置。
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
undo是在事務開始之前保存的被修改數據的一個版本，產生undo日志的時候，同樣會伴隨類似于保護事務持久化機制的redolog的產生。
默認情況下undo文件是保存在共享表空間的，也即ibdata文件中，當數據庫中發生一些大的事務
性操作的時候，要生成大量的undo信息，全部保存在共享表空間中的。
因此共享表空間可能會變得很大，默認情況下，也就是undo 日志使用共享表空間的時候，被“撐大”的共享表空間是不會也不能自動收縮的。
因此，mysql5.7之后的“獨立undo 表空間”的配置就顯得很有必要了 。
- 中繼日志(relay log)
在主從復制應用場景下，其內容為從主機讀取的bin log日志寫入的內容。
總結
關于MySQL數據庫的架構設計，主要了解其邏輯架構，執行流程，重點掌握InnoDB和MyISAM等引擎的選型，以及日志文件中二進制日志、慢查詢日志、重做日志、回滾日志等日志的原理和配置。
