本文記錄了MySQL 5.7的物理和邏輯架構(gòu)，還有其組件。在這個帖子中，我會嘗試用圖去說明SQL語句的執(zhí)行流程和數(shù)據(jù)處理流程。

MySQL的架構(gòu)具備靈活性，因為它把不同的存儲引擎作為插件。

因此，MySQL的架構(gòu)和行為也會隨著存儲引擎的改變而改變。

我們重點討論InnoDB,因為它是MySQL的默認存儲引擎。

MySQL物理架構(gòu)

配置文件

• auto.cnf : 包含 server_uuid
• my.cnf : MySQL配置文件 形形色色的其他文件

 –basedir=dir_name  //MySQL安裝目錄路徑

–datadir=dir_name   //數(shù)據(jù)目錄的路徑，數(shù)據(jù)目錄存儲數(shù)據(jù)，狀態(tài)，日志等

–pid-file=file_name  //MySQL服務(wù)器寫ProcessID的文件路徑

–socket=file_name, -S file_name  //在Unix系統(tǒng)上，使用的Unix套接字文件的名字，
                                 //用于通過管道與本地服務(wù)器建立連接
–log-error=file_name       //記錄錯誤和啟動信息的日志文件名

MySQL邏輯架構(gòu)

• Client:提供連接MySQL服務(wù)器功能的常用工具集
• Server:MySQL實例，真正提供數(shù)據(jù)存儲和數(shù)據(jù)處理功能的MySQL服務(wù)器進程
• mysqld:MySQL服務(wù)器守護程序，在后臺運行。它管理著客戶端請求。mysqld是一個多線程的進程，允許多個會話連接，端口監(jiān)聽連接，管理MySQL實例
• MySQL memory allocation:MySQL的要求的內(nèi)存空間是動態(tài)的，比如 innodb_buffer_pool_size (from 5.7.5), key_buffer_size。每個會話都有獨一無二的執(zhí)行計劃，我們只能共享同一會話域內(nèi)的數(shù)據(jù)集。
• SESSION:為每個客戶端連接分配一個會話，動態(tài)分配和回收。用于查詢處理，每個會話同時具備一個緩沖區(qū)。每個會話是作為一個線程執(zhí)行的
• Parser:檢測SQL語句語法，為每條SQL語句生成SQL_ID，用戶認證也發(fā)生在這個階段
• Optimizer:創(chuàng)造一個有效率的執(zhí)行計劃（根據(jù)具體的存儲引擎）。它將會重寫查詢語句。比如：InnoDB有共享緩沖區(qū)，所以，優(yōu)化器會首先從預(yù)先緩存的數(shù)據(jù)中提取。使用 table statistics optimizer將會為SQL查詢生成一個執(zhí)行計劃。用戶權(quán)限檢查也發(fā)生在這個階段。
• Metadata cache:緩存對象元信息和統(tǒng)計信息
• Query cache:共享在內(nèi)存中的完全一樣的查詢語句。如果完全相同的查詢在緩存命中，MySQL服務(wù)器會直接從緩存中去檢索結(jié)果。緩存是會話間共享的，所以為一個客戶生成的結(jié)果集也能為另一個客戶所用。查詢緩存基于SQL_ID。將SELECT語句寫入視圖就是查詢緩存最好的例子。
• key cache:緩存表索引。MySQL keys是索引。如果索引數(shù)據(jù)量小，它將緩存索引結(jié)構(gòu)和葉子節(jié)點（存儲索引數(shù)據(jù)）。如果索引很大，它只會緩存索引結(jié)構(gòu)，通常供MyISAM存儲引擎使用

SQL執(zhí)行

MySQL連接

InnoDB存儲引擎架構(gòu)

TABLESPACE

InnoDB存儲空間被切分成tablespace,tablespace是一個與多個數(shù)據(jù)文件相關(guān)聯(lián)的邏輯結(jié)構(gòu)。

Pages

• InnoDB最小的數(shù)據(jù)存儲單元被也稱作塊。默認的頁框是16KB,一個頁包含多行。
• 可用頁大小: 4kb,8kb,16kb,32kb,64kb
• 配置變量名 : innodb_page_size，在初始化mysqld時配置

Extents

• 一組頁組成一個區(qū)，InnoDB為了更好的I/O吞吐率，每次讀寫都是按照區(qū)為單位。
• 一組16KB的頁，一個區(qū)可以1MB，雙寫緩沖區(qū)（Doublewrite buffer ）每次分配/讀/寫都是以區(qū)為單位。

Segments

• 4個區(qū)構(gòu)成一個Segments

InnoDB存儲引擎

• ACID事務(wù)支持
• 行鎖模式
• 事務(wù)REDO&UNDO支持
• 多數(shù)據(jù)文件
• 邏輯對象結(jié)構(gòu)（InnoDB數(shù)據(jù)和日志緩沖區(qū)）
• InnoDB數(shù)據(jù)是百分百的具備邏輯結(jié)構(gòu)，數(shù)據(jù)物理存儲。
• InnoDB讀取物理數(shù)據(jù)，創(chuàng)建邏輯結(jié)構(gòu)[Blocks and Rows]
• 邏輯存儲稱為TABLESPACE

InnoDB 內(nèi)存中組件

• InnoDB buffer pool
• InnoDB存儲引擎的核心緩沖區(qū)。在這個緩沖區(qū)之中，加載表和索引數(shù)據(jù)
• InnoDB緩存表數(shù)據(jù)和索引數(shù)據(jù)的主要區(qū)域
• 占據(jù)80%以上的物理內(nèi)存，在專用數(shù)據(jù)庫服務(wù)器中
• 所有會話的共享緩沖區(qū)
• InnoDB使用LRU頁面置換算法

Change buffer

In a memory change buffer is a part of InnoDB buffer pool and on disk,
it is part of system tablespace, so even after database restart index
changes remain buffered.Change buffer is a special data structure that
caches changes to secondary index pages when affected pages not in the
buffer pool.
memory change buffer是InnoDB buffer pool的一部分，在磁盤上，也是系統(tǒng)tablespace的一部分。送印即使數(shù)據(jù)庫重啟…毛意思啊！無力…保留原文

Redo log buffer

redo logs緩沖區(qū)，保存寫到redo log(重放日志)的數(shù)據(jù)。周期性的將緩沖區(qū)內(nèi)的數(shù)據(jù)寫入redo日志中。將內(nèi)存中的數(shù)據(jù)寫入磁盤的行為由innodb_log_at_trx_commit 和 innodb_log_at_timeout 調(diào)節(jié)。

較大的redo日志緩沖區(qū)允許大型事務(wù)在事務(wù)提交前不進行寫磁盤操作。

變量：innodb_log_buffer_size (default 16M)

在磁盤上的組件

系統(tǒng)表空間（tablespace）

除了存儲表數(shù)據(jù)之外，InnoDB也支持查找表元信息，存儲和檢索MVCC信息以兌現(xiàn)服從ACID和事務(wù)隔離性等原則。它包含幾種類型的InnoDB對象信息。

其包含的文件：

• Table Data Pages
• Table Index Pages
• Data Dictionary
• MVCC Control Data
• Undo Space
• Rollback Segments
• Double Write Buffer (Pages Written in the Background to avoid OS
  caching) Insert Buffer (Changes to Secondary Indexes)

變量：

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

激活

innodb_file_per_table選項，你可以將每個新創(chuàng)建的表存儲到不同的tablespace中。這種做法的優(yōu)點是減少磁盤上數(shù)據(jù)文件中的碎片

通用tablespace

Shared tablespace to store multiple table data. Introduce in MySQL 5.7.6. A user has to create this using CREATE TABLESPACE syntax. TABLESPACE option can be used with CREATE TABLE to create a table and ALTER TABLE to move a table in general table.

共享的tablespace存儲多個表信息，在MySQL 5.7.6時引入。用戶只能使用CREATE TABLESPACE創(chuàng)建一個這樣的表空間。

TABLESPACE選項可以在使用CREATE TABLE命令創(chuàng)建一個表然后 ALTER TABLE 將表移入通用空間時發(fā)揮作用。

– Memory advantage over innodb_file_per_table storage method.
– Support both Antelope and Barracuda file formats.
– Supports all row formats and associated features.
– Possible to create outside data directory.

InnoDB數(shù)據(jù)字典

在系統(tǒng)tablespace中的存儲區(qū)域，由系統(tǒng)內(nèi)部表（供mysql服務(wù)器使用的表）和對象元數(shù)據(jù)（表，索引，列信息）組成

雙寫緩沖區(qū)（Double write buffer）

系統(tǒng)tablespace的存儲區(qū)域，InnoDB在寫入物理文件之前先將頁從InnoDB buffer pool寫入此空間。mysqld進程突然崩潰會導(dǎo)致部分寫問題。InnoDB可以從這個區(qū)域拿到一個備份。 Variable: inndb_doublewrite (default enable)

REDO logs

用于災(zāi)難恢復(fù)。mysqld啟動的時候，InnoDB會嘗試執(zhí)行自動恢復(fù)，將不完整的事務(wù)更改矯正。還未完成更新數(shù)據(jù)文件的事務(wù)會在mysqld啟動時會根據(jù)此日志記錄中的信息被重放。它使用 LSN(Log Sequence Number)值來重放信息，因為mySQL會為每個事務(wù)賦予一個ID。因為大量數(shù)據(jù)更改不可能及時寫道磁盤，所以得先記錄到redo日志，然后再寫入磁盤。

再redo日志，所有更改都會帶有 row_id, 舊的列值，新的列值, session_id 和時間。

Innodb_log_file_in_group= [# of redo file groups]Innodb_log_file_size= [每個日志文件大小]

UNDO日志和UNDO表空間

UNDO tablespace包含一個或多個undo日志文件。UNDO通過為事務(wù)（MVCC）保存被更改還未提交的值保持讀一致性。未提交值從這個存儲區(qū)域讀取。UNDO日志也被叫做回滾數(shù)據(jù)段。

默認地，UNDO日志是系統(tǒng)表空間的一部分。但MySQL允許UNDO日志置于一個單獨的表空間中 [Introduce in MySQL 5.6]。這需要在初始化mysqld之前進行更改才起作用。

當我們配置單獨UNDO表空間時，系統(tǒng)表空間的UNDO日志就被抑制了，但是一旦配置成單獨的，我們只能刪除UNDO日志的一部分，比如過期日志，而不能刪除它。

– Variables : innodb_undo_tablespace : # of undo tablespaces, default
0 innodb_undo_directory:
Location for undo tablespace,default is，data_dir with 10MB size.
innodb_undo_logs :
# of undo logs, default ，and max value is ‘128’

臨時表空間

為臨時表和相關(guān)對象提供存儲功能，存儲包括臨時表未提交的數(shù)據(jù)。在MySQL 5.7.2引入，用于對臨時表修改的回滾。

ibtmp1每次系統(tǒng)啟動被重新創(chuàng)建，避免REDO日志對臨時表的I/O操作。

innodb_temp_data_file_path = ibtmp1:12M:autoextend (default)

And All SET !!

存儲引擎

Storage engine:
MySQL component that manages physical data (file management) and locations. Storage engine responsible for SQL statement execution and fetching data from data files. Use as a plugin and can load/unload from running MySQL server.Few of them as following,
InnoDB :
Fully transactional ACID.
Offers REDO and UNDO for transactions.
Data storage in tablespace:
Multiple data files
Logical object structure using InnoDB data and log buffer
Row-level locking.
NDB (For MySQL Cluster):
Fully Transactional and ACID Storage engine.
Distribution execution of data and using multiple mysqld.
NDB use logical data with own buffer for each NDB engine.
Offers REDO and UNDO for transactions.
Row-level locking.
MyISAM:
Non-transactional storage engine
Speed for read
Data storage in files and use key, metadata and query cache
– FRM for table structure
– MYI for table index
– MYD for table data
Table-level locking.
MEMORY:
Non-transactional storage engine
All data stored in memory other than table metadata and structure.
Table-level locking.
ARCHIVE:
Non-transactional storage engine,
Store large amounts of compressed and unindexed data.
Allow INSERT, REPLACE, and SELECT, but not DELETE or UPDATE sql operations.
Table-level locking.
CSV:
Stores data in flat files using comma-separated values format.
Table structure need be created within MySQL server (.frm)

Reference List

1.MySQL Architecture and Components

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。