MySQL體系結構及存儲引擎

前言

本文講解MySQL的體系結構及存儲引擎的概念和各類存儲引擎的特性。

一、MySQL的體系結構

1. 整個MySQL Server由以下組成

Connection Pool : 連接配接池元件（接收到用戶端的請求，進行驗證授權，配置設定線程數，确定最大線程數等等）

Management Services & Utilities : 管理服務和工具元件

SQL Interface : SQL接口元件

Parser : 查詢分析器元件（解析SQL接口）

Optimizer : 優化器元件（優化SQL語句）

Caches & Buffers : 緩沖池元件（用于緩存查詢結果，下次直接從緩存中拿資料）

Pluggable Storage Engines : 插件式存儲引擎（真正的負責了MySQL中資料的存儲和提取）

File System : 檔案系統

2. MySQL Server四層結構

• 連接配接層

  完成一些類似于連接配接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的用戶端提供線程。同樣在該層上可以實作基于SSL的安全連結。伺服器也會為安全接入的每個用戶端驗證它所具有的操作權限。

• 服務層

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

• 引擎層

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

• 存儲層

  資料存儲層， 主要是将資料存儲在檔案系統之上，并完成與存儲引擎的互動。

3. MySQL資料庫在架構上差別于其它資料庫的優點

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

二、存儲引擎概述

和大多數的資料庫不同, MySQL中有一個存儲引擎的概念, 針對不同的存儲需求可以選擇最優的存儲引擎。

存儲引擎就是存儲資料，建立索引，更新查詢資料等等技術的實作方式 。存儲引擎是基于表的，而不是基于庫的。是以存儲引擎也可被稱為表類型。

Oracle，SqlServer等資料庫隻有一種存儲引擎。MySQL提供了插件式的存儲引擎架構，是以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。

三、常見存儲引擎特性

1. MySQL5.0支援的存儲引擎

InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、

ARCHIVE、CSV、BLACKHOLE、FEDERATED等。

其中InnoDB和BDB提供事務安全表，其他存儲引擎是非事務安全表。

2. 查詢目前MySQL資料庫支援的存儲引擎

3. 常用的存儲引擎對比

3.1 InnoDB

• InnoDB存儲引擎是Mysql的預設存儲引擎。InnoDB存儲引擎提供了具有送出、復原、崩潰恢複能力的事務安全。
• 但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留資料和索引。
• InnoDB支援外鍵限制

3.2 MyISAM

MyISAM 不支援事務、也不支援外鍵，其優勢是通路的速度快，對事務的完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個引擎來建立表。

3.3 MEMORY

Memory存儲引擎将表的資料存放在記憶體中。每個MEMORY表實際對應一個磁盤檔案，格式是.frm ，該檔案中隻存儲表的結構，而其資料檔案，都是存儲在記憶體中，這樣有利于資料的快速處理，提高整個表的效率。MEMORY類型的表通路非常地快，因為他的資料是存放在記憶體中的，并且預設使用HASH索引 ， 但是服務一旦關閉，表中的資料就會丢失。

3.4 MERGE

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

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

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

四、存儲引擎選擇依據

在選擇存儲引擎時，應該根據應用系統的特點選擇合适的存儲引擎。對于複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。以下是幾種常用的存儲引擎的使用環境。

• InnoDB : 是Mysql的預設存儲引擎，用于事務處理應用程式，支援外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求資料的一緻性，資料操作除了插入和查詢以外，還包含很多的更新、删除操作，那麼InnoDB存儲引擎是比較合适的選擇。InnoDB存儲引擎除了有效的降低由于删除和更新導緻的鎖定， 還可以確定事務的完整送出和復原，對于類似于計費系統或者财務系統等對資料準确性要求比較高的系統，InnoDB是最合适的選擇。
• MyISAM ： 如果應用是以讀操作和插入操作為主，隻有很少的更新和删除操作，并且對事務的完整性、并發性要求不是很高，那麼選擇這個存儲引擎是非常合适的。
• MEMORY：将所有資料儲存在RAM中，在需要快速定位記錄和其他類似資料環境下，可以提供極快的通路。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在記憶體中，其次是要確定表的資料可以恢複，資料庫異常終止後表中的資料是可以恢複的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到通路結果。
• MERGE：用于将一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個對象引用他們。MERGE表的優點在于可以突破對單個MyISAM表的大小限制，并且通過将不同的表分布在多個磁盤上，可以有效的改善MERGE表的通路效率。這對于存儲諸如資料倉儲等VLDB環境十分合适。