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

MySQL的架構(gòu)具備靈活性，因?yàn)樗巡煌拇鎯?chǔ)引擎作為插件。

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

我們重點(diǎn)討論InnoDB,因?yàn)樗荕ySQL的默認(rèn)存儲(chǔ)引擎。

MySQL物理架構(gòu)

配置文件

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

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

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

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

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

MySQL邏輯架構(gòu)

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

SQL執(zhí)行

MySQL連接

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

TABLESPACE

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

Pages

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

Extents

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

Segments

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

InnoDB存儲(chǔ)引擎

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

InnoDB 內(nèi)存中組件

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

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的一部分，在磁盤(pán)上，也是系統(tǒng)tablespace的一部分。送印即使數(shù)據(jù)庫(kù)重啟…毛意思??！無(wú)力…保留原文

Redo log buffer

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

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

變量：innodb_log_buffer_size (default 16M)

在磁盤(pán)上的組件

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

除了存儲(chǔ)表數(shù)據(jù)之外，InnoDB也支持查找表元信息，存儲(chǔ)和檢索MVCC信息以?xún)冬F(xiàn)服從ACID和事務(wù)隔離性等原則。它包含幾種類(lèi)型的InnoDB對(duì)象信息。

其包含的文件：

• 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選項(xiàng)，你可以將每個(gè)新創(chuàng)建的表存儲(chǔ)到不同的tablespace中。這種做法的優(yōu)點(diǎn)是減少磁盤(pán)上數(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存儲(chǔ)多個(gè)表信息，在MySQL 5.7.6時(shí)引入。用戶(hù)只能使用CREATE TABLESPACE創(chuàng)建一個(gè)這樣的表空間。

TABLESPACE選項(xiàng)可以在使用CREATE TABLE命令創(chuàng)建一個(gè)表然后 ALTER TABLE 將表移入通用空間時(shí)發(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中的存儲(chǔ)區(qū)域，由系統(tǒng)內(nèi)部表（供mysql服務(wù)器使用的表）和對(duì)象元數(shù)據(jù)（表，索引，列信息）組成

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

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

REDO logs

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

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

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

UNDO日志和UNDO表空間

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

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

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

– 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’

臨時(shí)表空間

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

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

innodb_temp_data_file_path = ibtmp1:12M:autoextend (default)

And All SET !!

存儲(chǔ)引擎

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é)

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

最新評(píng)論