MySql優化之體系結構及儲存引擎（總結分享）

這篇文章為大家帶來了關於mysql體系架構以及儲存引擎總結的相關知識，希望對大家有幫助。

一、MySQL結構體系

總體上， 我們可以把MySQL 分成三層，分層再詳細一點的話可以將儲存引擎層再分為引擎層和儲存層：

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

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

引擎層
儲存引擎層， 儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器透過API和儲存引擎進行通訊。不同的儲存引擎有不同的功能，讓我們可以根據自己的需要，來選取合適的儲存引擎。

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

二、MySQL Server組成部分

MySQL結構各層又由一個或數個部分構成：

連接層組成部分

Connectors：互動介面

MySQL向外提供的互動元件，如java,.net ,php等語言可以透過此元件來操作SQL語句，實現與SQL的互動

#服務層組成部分

Connection Pool : 連接池元件
用於管理、緩衝使用者的連接，執行緒處理等需要快取的需求

Management Services & Utilities : 管理服務與工具元件
用於系統管理和控制工具，例如備份還原、Mysql複製、叢集、安全管理等

#SQL Interface : SQL介面元件
用於接受使用者的SQL指令，如DML,DDL和預存程序等，並將最終結果傳回給使用者

Parser : 查詢分析器元件
SQL指令傳遞到解析器的時候會被解析器驗證與解析，先分析SQL指令語法的合法性，並嘗試將SQL指令分解成資料結構，若分解失敗，提示SQL語句不合理

Optimizer : 最佳化器元件
SQL語句在查詢之前會使用查詢最佳化器對查詢進行最佳化。
如：select id,name from user where age = 20;

1. 這個select 查詢先根據where 語句進行選取，而不是先將表全部查詢出來以後再進行age過濾
2. 這個select查詢先根據id和name進行屬性投影，而不是將屬性全部取出以後再進行過濾
3. #將這兩個查詢條件聯接起來產生最終查詢結果

Caches & Buffers : 緩衝池元件
如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取資料。這個快取機制是由一系列小型快取組成的。例如表格緩存，記錄緩存，key緩存，權限緩存等

引擎層組成部分

Pluggable Storage Engines : 儲存引擎
儲存引擎就是儲存數據，建立索引，更新查詢資料等等技術的實作方式 。儲存引擎是基於表的，而不是基於庫的。所以儲存引擎也可稱為表格型別。對於表格的創建，資料的存儲，檢索，更新等都是由MySQL儲存引擎完成的，這也是MySQL儲存引擎在MySQL中扮演的重要角色。

MYSQL提供了插件式的儲存引擎架構，所以使用者可以根據不同的需求為資料表選擇不同的儲存引擎，使用者也可以依照自己的需求編寫自己的儲存引擎，或是編寫儲存引擎。甚至一個庫中不同的表使用不同的儲存引擎，這些都是允許的。

儲存層組成部分

#File System : 檔案系統

用於實際儲存MySQL資料庫檔案和一些日誌檔案等的系統。常見的如Linux、Windows等。

三、儲存引擎

常用的儲存引擎差異

在MySQL 5.5 版本之前， 預設的儲存引擎是MyISAM， 它是MySQL 隨附的。 5.5 版本之後預設的儲存引擎改成了InnoDB， 它是第三方公司為MySQL開發的。為什麼要改呢？

最主要的原因還是InnoDB支援事務， 支援行層級的鎖定， 對於業務一致性要求高的場景來說更適合。

不同儲存引擎建表語句：

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

InnoDB
InnoDB儲存引擎是如今MySQL的預設儲存引擎。 InnoDB儲存引擎提供了具有提交、回滾、崩潰復原能力的事務安全性。但是比較MyISAM的儲存引擎，InnoDB寫的處理效率差一些，並且會佔用更多的磁碟空間以保留資料和索引。

InnoDB儲存引擎不同於其他儲存引擎的特性：

1. 交易控制
2. 外鍵限制

InnoDB 儲存表和索引有以下兩種方式：

1. 使用共享表空間存儲， 這種方式建立的表的表結構保存在.frm檔案中，資料和索引保存在innodb_data_home_dir 和innodb_data_file_path定義的表空間中，可以是多個文件。
2. 使用多表空間存儲， 這種方式建立的表的表結構仍然存在 .frm檔案中，但是每個表的資料和索引單獨保存在 .ibd 中。

InnoDB 適合的使用環境：
InnoDB 是MySQL的預設儲存引擎，用於事務處理應用程序，支援外鍵。如果應用程式對交易的完整性有比較高的要求，在並發條件下要求資料的一致性，資料操作除了插入和查詢意外，還包含很多的更新、刪除操作，那麼InnoDB儲存引擎是比較合適的選擇。

InnoDB儲存引擎除了有效的降低由於刪除和更新導致的鎖定， 還可以確保事務的完整提交和回滾，對於類似於計費系統或者財務系統等對數據準確性要求比較高的系統，InnoDB是最適合的選擇。

MyISAM
MyISAM 不支援交易、也不支援外鍵，其優點是存取的速度快，對交易的完整性沒有要求或以SELECT、INSERT為主的應用基本上都可以使用這個引擎來建立表格。

有以下兩個比較重要的特點：

• 不支援交易
• 檔案儲存方式

每個MyISAM在磁碟上儲存成3個文件，其檔案名稱都和表名相同，但拓展名分別是：

• .frm (儲存表定義)；
• .MYD(MYData ,儲存資料);
• .MYI(MYIndex , 儲存索引);

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

MEMORY
Memory儲存引擎將資料表的資料存放在記憶體中。每個MEMORY表實際對應一個磁碟文件，格式是.frm ，該文件中只存儲表的結構，而其數據文件，都是存儲在內存中，這樣有利於數據的快速處理，提高整個表的效率。

MEMORY類型的表存取非常地快，因為他的資料是存放在記憶體中的，並且預設使用HASH索引 ， 但是服務一旦關閉，表中的資料就會遺失。

MEMORY 適合的使用環境：
將所有資料保存在RAM中，在需要快速定位記錄和其他類似資料環境下，可以提供幾塊的存取。

MEMORY的缺陷就是對錶的大小有限制，太大的表無法緩存在記憶體中，其次是要確保表的資料可以恢復，資料庫異常終止後表中的資料是可以恢復的。 MEMORY表通常用於更新較不頻繁的小表，用以快速取得存取結果。

MERGE
MERGE儲存引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，MERGE表本身並沒有儲存數據，對MERGE類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行的。

對於MERGE類型表的插入操作，是透過INSERT_METHOD子句定義插入的表，可以有3個不同的值，使用FIRST 或LAST 值使得插入操作被相應地作用在第一或最後一個表上，不定義這個子句或定義為NO，表示不能對這個MERGE表執行插入操作。

可以對MERGE表進行DROP操作，但這個操作只是刪除MERGE表的定義，對內部的表是沒有任何影響的。

MERGE 適合的使用環境：
用來將一系列等同的MyISAM表以邏輯方式組合在一起，並作為一個物件引用他們。

MERGE表的優點是可以突破單一MyISAM資料表的大小限制，並且透過將不同的表分佈在多個磁碟上，可以有效的改善MERGE表的存取效率。這對於儲存諸如資料倉儲等VLDB環境十分合適。

推薦學習：mysql影片教學

以上是MySql優化之體系結構及儲存引擎（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！