一. MySQL體繫結構

1. 連接層

最上層是一些客戶端和鏈接服務，包含本地sock通信和大多數基於客戶端/服務端工具實現的類似於TCP/IP的通信。主要完成一些類似於連接處理、授權認證及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基於SSL的安全鏈接。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。

2. 服務層

第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成緩存的查詢，SQL的分析和優化，部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如過程、函數等。在該層，伺服器會解析查詢並創建相應的內部解析樹，並對其完成相應的優化如確定表的查詢順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3. 引擎層

存儲引擎層，存儲引擎真正的負責了MySQL中數據的存儲和提取，伺服器通過API和存儲引擎進行通信。不同的存儲引擎具有不同的功能，這樣我們可以根據自己的需求來選取合適的存儲引擎。資料庫中的索引是在存儲引擎層實現的。

4. 存儲層

數據存儲層，主要是將數據（如：redolog、undolog、數據、索引、二進位日誌、錯誤日誌、查詢日誌、慢查詢日誌等）存儲在文件系統之上，並完成與存儲引擎的交互。

和其他資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在存儲引擎上，插件式的存儲引擎架構，將查詢處理和其他的系統任務以及數據的存儲提取分離。這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎。

二. 存儲引擎介紹

存儲引擎就是存儲數據、建立索引、更新/查詢數據等技術的實現方式。存儲引擎是基於表的，而不是基於庫的，所以存儲引擎也可稱為表類型。我們可以在創建表的時候，來指定選擇的存儲引擎，如果沒有指定將自動選擇預設的存儲引擎。

1. 建表時指定存儲引擎

CREATE TABLE 表名( 
	欄位1 欄位1類型 [ COMMENT 欄位1註釋 ] , 
	...... 
	欄位n 欄位n類型 [COMMENT 欄位n註釋 ] 
) ENGINE = INNODB [ COMMENT 表註釋 ] ;

(1). 案例1：查詢建表語句 --- 預設存儲引擎：InnoDB

show create table account;

2. 查詢當前資料庫支持的存儲引擎

show engines;

(1). 案例1：創建表my_myisam，並指定MyISAM存儲引擎

create table my_myisam( 
	id int, 
	name varchar(10) 
) engine = MyISAM;

(2). 創建表my_memory，指定Memory存儲引擎

create table my_memory( 
	id int, 
	name varchar(10) 
) engine = Memory;

三. 存儲引擎特點

1. InnoDB

(1). 介紹

InnoDB是一種兼顧高可靠性和高性能的通用存儲引擎，在MySQL 5.5之後，InnoDB是預設的MySQL存儲引擎。

(2). 特點

• DML操作遵循ACID模型，支持事務；

• 行級鎖，提高併發訪問性能；

• 支持外鍵FOREIGN KEY約束，保證數據的完整性和正確性；

(3). 文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會對應這樣一個表空間，存儲該表的表結構（frm-早期的、sdi-新版的）、數據和索引。

參數：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果該參數開啟，代表對於InnoDB引擎的表，每一張表都對應一個ibd文件。我們直接打開MySQL的數據存放目錄，這個目錄下有很多文件夾，不同的文件夾代表不同的資料庫，我們直接打開yun3k文件夾。

可以看到裡面有很多的idb文件，每一個ibd文件就對應一張表，比如：我們有一張表account，就會有這樣一個account.ibd文件，而在這個ibd文件中不僅存放表結構、數據、還會存放該表對應的索引信息。而該文件是基於二進位存儲的，不能直接基於記事本打開，我們可以使用mysql提供的一個指令ibd2sdi，通過該指令就可以從ibd文件中提取sdi信息，而sdi數據字典信息中就包含該表的表結構。

(4). 邏輯存儲結構

• 表空間：InnoDB存儲引擎邏輯結構的最高層，ibd文件其實就是表空間文件，在表空間中可以包含多個Segment段。

• 段：表空間是由各個段組建的，常見的段有數據段、索引段、回滾段等。InnoDB中對於段的管理，都是引擎自身完成，不需要人為對其控制，一個段中包含多個區。

• 區：區是表空間的單元結構，每個區的大小為1M。預設情況下，InnoDB存儲引擎頁大小為16k，即一個區中一共有64個連續的頁。

• 頁：頁是組成區的最小單元，頁也是InnoDB存儲引擎磁碟管理的最小單元，每個頁的大小預設為16KB。為了保證頁的連續性，InnoDB存儲引擎每次從磁碟申請4-5個區。

• 行：InnoDB存儲引擎是面向行的，也就是說數據是按行進行存放的，在每一行中除了定義表時所指定的欄位以外，還包含兩個隱藏欄位(後面會詳細介紹)。

2. MyISAM

(1). 介紹

MyISAM是MySQL早期的預設存儲引擎。

(2). 特點

不支持事務，不支持外鍵

支持表鎖，不支持行鎖

訪問速度快

(3). 文件

xxx.sdi：存儲表結構信息

xxx.MYD：存儲數據

xxx.MYI：存儲索引

3. Memory

(1). 介紹

Memory引擎的表數據是存儲在記憶體中的，由於受到硬體問題或斷電問題的影響，只能將這些表作為臨時表或緩存使用。

(2). 特點

記憶體存放

hash索引（預設）

(3). 文件

xxx.sdi：存儲表結構信息

4. InnoDB、MyISAM和Memory的區別及特點

四. 存儲引擎的選擇

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對於複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行結合。

• InnoDB：是Mysql的預設存儲引擎，支持事務、外鍵。如果應用對事務的完整性有比較高的要求，在併發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操作，那麼InnoDB存儲引擎是比較合適的選擇。

• MyISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、併發性要求不是很高，那麼選擇這個存儲引擎是非常合適的。

• Memory：將所有數據保存在記憶體中，訪問速度快，通常用於臨時表及緩存。MEMORY的缺陷就是對錶的大小有限制，太大的表無法緩存在記憶體中，而且無法保障數據的安全。

更多mysql學習請關註微信公眾號”雲哥技術yun3k”，回覆”mysql學習”，免費領取mysql全套學習資料。