了解掌握MySQL數(shù)據(jù)庫(kù)的架構(gòu)設(shè)計(jì)、文件系統(tǒng)，有利于更全面、系統(tǒng)的掌握MySQL數(shù)據(jù)庫(kù)，是進(jìn)階精通MySQL的必修課。

MySQL邏輯架構(gòu)

總體分為客戶端連接器(Connectors)和服務(wù)器端(MySQL Server)兩大部分。應(yīng)用程序客戶端或可視化數(shù)據(jù)庫(kù)客戶端通過提供的連接器連接到服務(wù)端來使用MySQL提供的服務(wù)。

架構(gòu)圖

整體架構(gòu)分為客戶端連接器(Connectors)和服務(wù)器端(MySQL Server)兩大部分。其中服務(wù)器端分為3層，包括SQL層、可拔插存儲(chǔ)引擎層、文件系統(tǒng)（物理結(jié)構(gòu)），核心在SQL層、可拔插存儲(chǔ)引擎層。

MySQL邏輯架構(gòu)圖

客戶端(Connectors)

即連接器Connectors，包括MySQL提供的原生C語(yǔ)言API、實(shí)現(xiàn)了JDBC的連接器驅(qū)動(dòng)程序等。基本上，大部分編程語(yǔ)言都實(shí)現(xiàn)了連接MySQL數(shù)據(jù)庫(kù)的程序包，通過這些程序包連接到MySQL數(shù)據(jù)庫(kù)的程序或應(yīng)用，也可以認(rèn)為是MySQL的客戶端。

• Native C API
• JDBC
• ODBC
• .NET
• php
• Python
• Perl
• Ruby

服務(wù)器端(Server)

服務(wù)器端主要由SQL層、可拔插存儲(chǔ)引擎層、文件系統(tǒng)三部分。

其中SQL層(SQL Layer)包含以下6個(gè)組件

• 系統(tǒng)管理和控制工具(Management Services & Utilities )MySQL提供的一些系統(tǒng)管理工具，比如mysql、mysqladmin等命令行工具
• 連接池(Connection Pool)連接池組件負(fù)責(zé)身份認(rèn)證authentication、連接池的實(shí)現(xiàn)(線程復(fù)用、連接限制)、檢查內(nèi)存，緩存等
• SQL接口(SQL Interfaces)實(shí)現(xiàn)各種語(yǔ)句DDL、DML等，以及存儲(chǔ)過程、函數(shù)、觸發(fā)器、視圖等接口，負(fù)責(zé)命令分發(fā)
• 解析器(Parser)解析SQL語(yǔ)句，負(fù)責(zé)對(duì)SQL語(yǔ)句的詞法和語(yǔ)法解析，形成初級(jí)語(yǔ)法樹
• 優(yōu)化器(Optimizer)對(duì)初級(jí)語(yǔ)法樹進(jìn)行優(yōu)化，生成計(jì)劃，選擇合理的索引
• 緩存和緩沖(Caches & Buffers)緩沖提交的SQL請(qǐng)求等；對(duì)查詢結(jié)果進(jìn)行緩存，如果有開啟。8.0版本后，緩存Cache已棄用。

在5.5版本后，MySQL默認(rèn)使用存儲(chǔ)引擎是InnoDB。存儲(chǔ)引擎是以表為單位應(yīng)用的，創(chuàng)建表時(shí)可以指定使用的存儲(chǔ)引擎，比如

CREATE TABLE tb_name(columns) ENGINE=InnoDB|MyISAM|Memory

以下是MySQL支持的常見引擎

MySQL支持的常見引擎

在5.7版本中，CREATE TABLE語(yǔ)句創(chuàng)建表時(shí)默認(rèn)使用的存儲(chǔ)引擎為InnoDB，可以省略指定。目前使用比較多的存儲(chǔ)引擎包括InnoDB和MyISAM。InnoDB與MyISAM的區(qū)別如下表所示，開發(fā)人員可以按照業(yè)務(wù)需求使用不同的引擎

InnoDB與MyISAM的區(qū)別

一般情況下，InnoDB、MyISAM、Memory等幾個(gè)存儲(chǔ)引擎可以滿足大部分的一個(gè)應(yīng)用場(chǎng)景。下面是這三個(gè)存儲(chǔ)引擎的使用場(chǎng)景或選型依據(jù)

• InnoDB

支持事務(wù)，支持外鍵，支持崩潰修復(fù)和并發(fā)控制。對(duì)事務(wù)的完整性要求比較高(比如銀行)，要求實(shí)現(xiàn)并發(fā)控制(比如售票)，頻繁更新、刪除數(shù)據(jù)，這類應(yīng)用場(chǎng)景建議使用InnoDB，因?yàn)橹С质聞?wù)的提交(commit)和回滾(rollback)。

• MyISAM

插入數(shù)據(jù)快，空間和內(nèi)存使用比較低。如果主要用于插入新記錄和讀取記錄，或應(yīng)用的完整性和并發(fā)性要求比較低，可以考慮選擇MyISAM。

• Memory

數(shù)據(jù)存儲(chǔ)在內(nèi)存中，處理速度較快，但安全性不高。如果需要很快的讀寫速度，對(duì)數(shù)據(jù)安全性要求不高，數(shù)據(jù)作為臨時(shí)處理使用或不需要持久保存，可以選擇Memory。它對(duì)表的大小有要求，不能建立太大的表。

注意，因?yàn)榇鎯?chǔ)引擎是作用在表上的，所以同一個(gè)數(shù)據(jù)庫(kù)，可以使用多個(gè)存儲(chǔ)引擎，即不同的表可以有不同的存儲(chǔ)引擎。如果一個(gè)表要求比較高的事務(wù)處理，可以選擇InnoDB；而查詢比較高的表，可以使用MyISAM；如果該數(shù)據(jù)庫(kù)需要一個(gè)用于查詢的臨時(shí)表，也可以考慮使用Memory存儲(chǔ)引擎。總之，根據(jù)不同的一個(gè)用場(chǎng)景，可以使用一種存儲(chǔ)引擎或多種存儲(chǔ)引擎組合使用。

文件系統(tǒng)

• 數(shù)據(jù)文件

包括表定義文件、表數(shù)據(jù)、索引等

• 日志文件

包括通用日志文件、二級(jí)制文件binlog、中繼日志relaylog、重做日志redolog、回滾日志undolog、錯(cuò)誤日志errlog，以及慢查詢?nèi)罩緎lowlog等

執(zhí)行流程

下圖是MySQL執(zhí)行一條語(yǔ)句的簡(jiǎn)單流程圖

簡(jiǎn)單流程圖

物理架構(gòu)

即文件系統(tǒng)，MySQL的文件組織架構(gòu)。MySQL是通過文件系統(tǒng)對(duì)數(shù)據(jù)和索引進(jìn)行存儲(chǔ)的。從物理結(jié)構(gòu)可以分為數(shù)據(jù)索引文件和日志文件兩大類。其中，數(shù)據(jù)索引文件采用隨機(jī)IO的方式寫入磁盤存儲(chǔ)，日志文件采用順序IO方式寫入磁盤存儲(chǔ)。兩種寫入磁盤的方式對(duì)比如下

磁盤寫入方式對(duì)比

可以使用下面的命令查看這些文件的保存目錄，如果沒有另外指定，一般情況下在/var/lib/mysql目錄下

show variables like '%datadir%';

數(shù)據(jù)文件

這里主要介紹比較常用的兩種存儲(chǔ)引擎InnoDB和MyISAM的數(shù)據(jù)文件構(gòu)成。

InnoDB數(shù)據(jù)文件包括以下三種格式

• .frm文件

主要存放與數(shù)據(jù)表相關(guān)的信息，包括表結(jié)構(gòu)的定義信息

• .ibd文件

使用獨(dú)享表空間也就是用戶表空間存儲(chǔ)表數(shù)據(jù)和索引信息，一張表對(duì)應(yīng)一個(gè).ibd文件

• ibdata文件

使用共享表空間也即系統(tǒng)表空間存儲(chǔ)表數(shù)據(jù)和索引信息，所有表使用一個(gè)或多個(gè)ibdata文件,文件名一般加上后綴0...N，比如ibdata0，ibdata1等

MyISAM數(shù)據(jù)文件包括以下三種格式

• .frm文件

主要存放與數(shù)據(jù)表相關(guān)的信息，包括表結(jié)構(gòu)的定義信息

• .myd文件

主要存儲(chǔ)表數(shù)據(jù)信息

• .myi文件

主要存儲(chǔ)表數(shù)據(jù)文件中任何索引的數(shù)據(jù)數(shù)

日志文件

可以下面的命令查看日志的開啟情況，開啟或配置了對(duì)應(yīng)的日志文件后，才會(huì)生成相應(yīng)的日志文件。

show variables like '%log_%';

日志文件大概分7類，包括錯(cuò)誤日志(error log)、二進(jìn)制日志(bin log)、通用查詢?nèi)罩?general query log)、慢查詢?nèi)罩?slow query log)、重做日志(redo log)、回滾日志(undo log)、中繼日志(relay log)。

• 錯(cuò)誤日志(error log)

錯(cuò)誤日志默認(rèn)是開啟的，從5.5.7版本開始以后無法關(guān)閉，錯(cuò)誤日志記錄了運(yùn)行過程中遇到的所有嚴(yán)重錯(cuò)誤信息，以及MySQL服務(wù)器每次啟動(dòng)和關(guān)閉的詳細(xì)信息。錯(cuò)誤日志所記錄的信息是可以通過log-error和log-warnings來定義的，其中l(wèi)og-err是定義是否啟用錯(cuò)誤日志的功能和錯(cuò)誤日志的存儲(chǔ)位置，log-warnings是定義是否將警告信息也定義至錯(cuò)誤日志中。

-- 可以直接定義為文件路徑，也可以為ON|OFF，默認(rèn)的錯(cuò)誤日志名稱：hostname.err
log_error=/var/log/mysqld.log

-- 只能使用1|0來定義開關(guān)啟動(dòng)，默認(rèn)是啟動(dòng)的
log_warings=1

• 二進(jìn)制日志(bin log)

二進(jìn)制日志記錄數(shù)據(jù)在運(yùn)行過程中的所有變化。bin log通過存儲(chǔ)數(shù)據(jù)庫(kù)所有DDL和DML語(yǔ)句，但不包括SELECT語(yǔ)句，語(yǔ)句以事件的形式保存，描述了數(shù)據(jù)的變更順序，bin log還包括了每個(gè)更新語(yǔ)句的執(zhí)行時(shí)間信息。如果是DDL語(yǔ)句，則直接記錄到bin log日志，而DML語(yǔ)句，必須通過事務(wù)提交才能記錄到bin log日志中。默認(rèn)是不開啟的，需要手動(dòng)開啟。產(chǎn)品環(huán)境建議開啟。

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

• 通用查詢?nèi)罩?general query log)

通用查詢?nèi)罩居涗涍\(yùn)行過程中的所有內(nèi)容，耗性能，一般產(chǎn)品化境中不開啟。可以通過下面的命令查看開通狀態(tài)

show global variables like 'general_log';

可以通過下面的配置開通日志

-- 啟動(dòng)開關(guān)
general_log={ON|OFF}
-- 日志文件變量，而general_log_file如果沒有指定，默認(rèn)名是host_name.log
general_log_file=/PATH/TO/file
-- 記錄類型
log_output={TABLE|FILE|NONE}

• 慢查詢?nèi)罩?slow query log)

記錄執(zhí)行較慢的select查詢語(yǔ)句，用于SQL語(yǔ)句調(diào)優(yōu)時(shí)開啟，正常情況下不需要開啟，默認(rèn)亦是關(guān)閉的。可以通過設(shè)置全局變量的方式開啟

-- 開啟慢查詢?nèi)罩?slow_query_log=ON
-- 慢查詢的閾值
long_query_time=3

-- 日志記錄文件如果沒有給出file_name值， 默認(rèn)為主機(jī)名，后綴為-slow.log。
-- 如果給出了文件名，但不是絕對(duì)路徑名，文件則寫入數(shù)據(jù)目錄。
slow_query_log_file=file_name

以上的設(shè)置，記錄執(zhí)行時(shí)間超過long_query_time秒的所有查詢語(yǔ)句

• 重做日志(redo log)

事務(wù)相關(guān)的日志，ACID持久化，崩潰恢復(fù)相關(guān)。

它用于確保事務(wù)的持久性，防止在發(fā)生故障的時(shí)間點(diǎn)，尚有臟頁(yè)未寫入磁盤，在重啟mysql服務(wù)的時(shí)候，根據(jù)redo log進(jìn)行重做，從而達(dá)到事務(wù)的持久性這一特性。其記錄的是物理數(shù)據(jù)頁(yè)面的修改的信息，它是順序?qū)懭雛edo log file的物理文件中去的。

事務(wù)開始之后就產(chǎn)生redo log，redo log的落盤并不是隨著事務(wù)的提交才寫入的，而是在事務(wù)的執(zhí)行
過程中，便開始寫入redo log文件中。

當(dāng)對(duì)應(yīng)事務(wù)的臟頁(yè)寫入到磁盤之后，redo log的使命也就完成了，重做日志占用的空間就可以重用（被覆蓋）。

默認(rèn)情況下，對(duì)應(yīng)的物理文件位于數(shù)據(jù)庫(kù)的data目錄下的ib_logfile1和ib_logfile2。

-- 指定日志文件組所在的路徑，默認(rèn)./ ，表示在數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄下
innodb_log_group_home_dir=./
  
-- 指定重做日志文件組中文件的數(shù)量，默認(rèn)2
innodb_log_files_in_group=2

-- 重做日志文件的大小
innodb_log_file_size=50331648

-- 重做日志緩沖區(qū)大小
innodb_log_buffer_size=16777216

很重要一點(diǎn)，redo log是什么時(shí)候?qū)懭氪疟P的？前面說了是在事務(wù)開始之后逐步寫盤的。之所以說重做日志是在事務(wù)開始之后逐步寫入重做日志文件，而不一定是事務(wù)提交才寫入重做日志文件，原因就是，重做日志有一個(gè)緩存區(qū)，其默認(rèn)大小為8M(或16M)，Innodb存儲(chǔ)引擎先將重做日志內(nèi)容寫入緩存區(qū)中。然后以下三種情況下，都會(huì)將緩沖區(qū)的日志內(nèi)容刷新到磁盤

1. 主線程每秒一次執(zhí)行刷新重做日志緩沖區(qū)到重做日志文件

2. 每個(gè)事務(wù)提交時(shí)會(huì)將重做日志刷新到重做日志文件，如果有配置這個(gè)變量的話

3. 當(dāng)重做日志緩存可用空間少于一半時(shí)，重做日志緩存會(huì)被刷新到重做日志文件

由此可以看出，重做日志通過不止一種方式寫入到磁盤，尤其是對(duì)于第一種方式，重做日志從緩沖區(qū)到重做日志文件是主線程的定時(shí)任務(wù)。

因此重做日志的寫盤，并不一定是隨著事務(wù)的提交才寫入重做日志文件的，而是隨著事務(wù)的開始，逐步
開始的。

另外引用《MySQL技術(shù)內(nèi)幕 Innodb 存儲(chǔ)引擎》（page37）上的原話：
即使某個(gè)事務(wù)還沒有提交，Innodb存儲(chǔ)引擎仍然每秒會(huì)將重做日志緩存刷新到重做日志文件。這一點(diǎn)是必須要知道的，因?yàn)檫@可以很好地解釋再大的事務(wù)的提交（commit）的時(shí)間也是很短暫的。

• 回滾日志(undo log)

也是事務(wù)相關(guān)的日志，實(shí)現(xiàn)回滾。

它保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個(gè)版本，可以用于回滾，同時(shí)可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀。它是邏輯格式的日志，在執(zhí)行undo的時(shí)候，僅僅是將數(shù)據(jù)從邏輯上恢復(fù)至事務(wù)之前的狀態(tài)，而不是從物理頁(yè)面上操作實(shí)現(xiàn)的，這一點(diǎn)是不同于redo log的。

事務(wù)開始之前，將當(dāng)前的版本生成undo log，undo 也會(huì)產(chǎn)生 redo 來保證undo log的可靠性 。

當(dāng)事務(wù)提交之后，undo log并不能立馬被刪除，而是放入待清理的鏈表，由purge線程判斷是否由其他事務(wù)在使用undo段中表的上一個(gè)事務(wù)之前的版本信息，決定是否可以清理undo log的日志空間。

MySQL5.6之前，undo表空間位于共享表空間的回滾段中，共享表空間文件的默認(rèn)的名稱是ibdata，位于數(shù)據(jù)文件目錄中。

MySQL5.6之后，undo表空間可以配置成獨(dú)立的文件，但是需要提前在配置文件中配置，完成數(shù)據(jù)庫(kù)初始化后生效且不可改變undo log文件的個(gè)數(shù)。如果初始化數(shù)據(jù)庫(kù)之前沒有進(jìn)行相關(guān)配置，那么就無法配置成獨(dú)立的表空間了。

關(guān)于MySQL5.7之后的獨(dú)立undo 表空間配置參數(shù)如下

-- undo獨(dú)立表空間的存放目錄，默認(rèn)值是./
innodb_undo_directory = /data/undospace/ 

--回滾段為128KB，默認(rèn)值得128
innodb_undo_logs = 128 

--指定有4個(gè)undo log文件，默認(rèn)值0
innodb_undo_tablespaces = 4 

如果undo使用的共享表空間，這個(gè)共享表空間中又不僅僅是存儲(chǔ)了undo的信息，共享表空間的默認(rèn)為
與MySQL的數(shù)據(jù)目錄下面，其屬性由參數(shù)innodb_data_file_path配置。

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+ 

undo是在事務(wù)開始之前保存的被修改數(shù)據(jù)的一個(gè)版本，產(chǎn)生undo日志的時(shí)候，同樣會(huì)伴隨類似于保護(hù)事務(wù)持久化機(jī)制的redolog的產(chǎn)生。

默認(rèn)情況下undo文件是保存在共享表空間的，也即ibdata文件中，當(dāng)數(shù)據(jù)庫(kù)中發(fā)生一些大的事務(wù)
性操作的時(shí)候，要生成大量的undo信息，全部保存在共享表空間中的。

因此共享表空間可能會(huì)變得很大，默認(rèn)情況下，也就是undo 日志使用共享表空間的時(shí)候，被“撐大”的共享表空間是不會(huì)也不能自動(dòng)收縮的。

因此，mysql5.7之后的“獨(dú)立undo 表空間”的配置就顯得很有必要了 。

• 中繼日志(relay log)

在主從復(fù)制應(yīng)用場(chǎng)景下，其內(nèi)容為從主機(jī)讀取的bin log日志寫入的內(nèi)容。

總結(jié)

關(guān)于MySQL數(shù)據(jù)庫(kù)的架構(gòu)設(shè)計(jì)，主要了解其邏輯架構(gòu)，執(zhí)行流程，重點(diǎn)掌握InnoDB和MyISAM等引擎的選型，以及日志文件中二進(jìn)制日志、慢查詢?nèi)罩尽⒅刈鋈罩尽⒒貪L日志等日志的原理和配置。