MySQL存儲引擎介紹

MySQL體系結構

整個MySQL Server由以下組成

• 連接配接層

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

• 服務層

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

• 引擎層

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

• 存儲層

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

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

存儲引擎

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

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

Oracle，SqlServer等資料庫隻有一種存儲引擎。MySQL提供了

插件式

的存儲引擎架構。是以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。

MySQL5.0支援的存儲引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事務安全表，其他存儲引擎是非事務安全表。

建立新表時如果不指定存儲引擎，那麼系統就會使用預設的存儲引擎，MySQL5.5之前的預設存儲引擎是MyISAM，5.5之後就改為了InnoDB。

可以通過指定

show engines

， 來查詢目前資料庫支援的存儲引擎 ，檢視Mysql資料庫預設的存儲引擎

特點	InnoDB	MyISAM	MEMORY	MERGE	NDB
存儲限制	64TB	有	有	沒有	有
事務安全	支援
鎖機制	行鎖(适合高并發)	表鎖	表鎖	表鎖	行鎖
B樹索引	支援	支援	支援	支援	支援
哈希索引	支援
全文索引	支援(5.6版本之後)	支援
叢集索引	支援
資料索引	支援	支援	支援
索引緩存	支援	支援	支援	支援	支援
資料可壓縮	支援
空間使用	高	低	N/A	低	低
記憶體使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支援外鍵	支援

InnoDB

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

InnoDB存儲引擎不同于其他存儲引擎的特點

• 行鎖(适合高并發)
• 支援事務

• 支援外鍵限制

  MySQL支援外鍵的存儲引擎隻有InnoDB ， 在建立外鍵的時候， 要求父表必須有對應的索引 ， 子表在建立外鍵的時候， 也會自動的建立對應的索引。

  在建立索引時， 可以指定在删除、更新父表時，對子表進行的相應操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

RESTRICT和NO ACTION相同， 是指限制在子表有關聯記錄的情況下， 父表不能更新；

CASCADE表示父表在更新或者删除時，更新或者删除子表對應的記錄；

SET NULL 則表示父表在更新或者删除的時候，子表的對應字段被SET NULL 。

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* 子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 
那麼在主表删除記錄的時候， 如果子表有對應記錄， 則不允許删除
主表在更新記錄的時候， 如果子表有對應記錄， 則子表對應更新 */
           

存儲方式

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

①. 使用共享表空間存儲， 這種方式建立的表的表結構儲存在.frm檔案中， 資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個檔案。

②. 使用多表空間存儲， 這種方式建立的表的表結構仍然存在 .frm 檔案中，但是每個表的資料和索引單獨儲存在 .ibd 中。

MyISAM

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

檔案存儲方式

每個MyISAM在磁盤上存儲成3個檔案，其檔案名都和表名相同，但拓展名分别是 ：

.frm (存儲表定義)；

.MYD(MYData , 存儲資料)；

.MYI(MYIndex , 存儲索引)；

存儲引擎的選擇

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

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

本文參考www.itheima.com學習資料，隻用于本人學習筆記總結，不可轉載，如有侵權請諒解。