MySQL之儲存引擎使用及說明

2023-02-12 06:01:54

一、mysql儲存引擎概述

1.1 儲存引擎

MySQL中的資料用各種不同的技術儲存在檔案（或者記憶體）中。

這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。

通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

例如，如果研究大量的臨時資料，你也許需要使用記憶體儲存引擎。記憶體儲存引擎能夠在記憶體中儲存所有的表格資料。

這些不同的技術以及配套的相關功能在MySQL中被稱作儲存引擎(也稱作表型別)。

MySQL預設設定了許多不同的儲存引擎，可以預先設定或者在MySQL伺服器中啟用。你可以選擇適用於伺服器、資料庫和表格的儲存引擎，以便在選擇如何儲存你的資訊、如何檢索這些資訊以及你需要你的資料結合什麼效能和功能的時候為你提供最大的靈活性。

選擇如何儲存和檢索你的資料的這種靈活性是MySQL為什麼如此受歡迎的主要原因。其它資料庫系統 (包括大多數商業選擇)僅支援一種型別的資料儲存。

1.2 mysql支援的儲存引擎

mysql5.6支援的儲存引擎包括

InnoDB
MyISAM
MEMORY
CSV
BLACKHOLE
FEDERATED
MRG_MYISAM
ARCHIVE
PERFORMANCE_SCHEMA
其中NDB和InnoDB提供事務安全表，其他儲存引擎都是非事務安全表。

二、各種儲存引擎的特性

並行性：某些應用程式比其他應用程式具有很多的顆粒級鎖定要求（如行級鎖定）。
事務支援：並非所有的應用程式都需要事務，但對的確需要事務的應用程式來說，有著定義良好的需求，如ACID相容等。
參照完整性：通過DDL定義的外來鍵，伺服器需要強制保持關聯資料庫的參照完整性。
物理儲存：它包括各種各樣的事項，從表和索引的總的頁大小，到儲存資料所需的格式，到物理磁碟。
索引支援：不同的應用程式傾向於採用不同的索引策略，每種儲存引擎通常有自己的編制索引方法，但某些索引方法（如B-tree索引）對幾乎所有的儲存引擎來說是共同的。
記憶體高速緩衝：與其他應用程式相比，不同的應用程式對某些記憶體高速緩衝策略的響應更好，因此，儘管某些記憶體高速緩衝對所有儲存引擎來說是共同的（如用於使用者連線的高速緩衝，MySQL的高速查詢高速緩衝等），其他高速緩衝策略僅當使用特殊的儲存引擎時才唯一定義。
效能幫助：包括針對並行操作的多I/O執行緒，執行緒並行性，資料庫檢查點，成批插入處理等。
其他目標特性：可能包括對地理空間操作的支援，對特定資料處理操作的安全限制等。

三、各種搜尋引擎介紹

InnoDB：MySql 5.6 版本預設的儲存引擎。InnoDB 是一個事務安全的儲存引擎，它具備提交、回滾以及崩潰恢復的功能以保護使用者資料。InnoDB 的行級別鎖定以及 Oracle 風格的一致性無鎖讀提升了它的多使用者並行數以及效能。InnoDB 將使用者資料儲存在聚集索引中以減少基於主鍵的普通查詢所帶來的 I/O 開銷。為了保證資料的完整性，InnoDB 還支援外來鍵約束。
MyISAM：MyISAM既不支援事務、也不支援外來鍵、其優勢是存取速度快，但是表級別的鎖定限制了它在讀寫負載方面的效能，因此它經常應用於唯讀或者以讀為主的資料場景。
Memory：在記憶體中儲存所有資料，應用於對非關鍵資料由快速查詢的場景。Memory型別的表存取資料非常快，因為它的資料是存放在記憶體中的，並且預設使用HASH索引，但是一旦服務關閉，表中的資料就會丟失
BLACKHOLE：黑洞儲存引擎，類似於 Unix 的 /dev/null，Archive 只接收但卻並不儲存資料。對這種引擎的表的查詢常常返回一個空集。這種表可以應用於 DML 語句需要傳送到從伺服器，但主伺服器並不會保留這種資料的備份的主從設定中。
CSV：它的表真的是以逗號分隔的文字檔案。CSV 表允許你以 CSV 格式匯入匯出資料，以相同的讀和寫的格式和指令碼和應用互動資料。由於 CSV 表沒有索引，你最好是在普通操作中將資料放在 InnoDB 表裡，只有在匯入或匯出階段使用一下 CSV 表。
NDB：(又名 NDBCLUSTER)——這種叢集資料引擎尤其適合於需要最高程度的正常執行時間和可用性的應用。注意：NDB 儲存引擎在標準 MySql 5.6 版本里並不被支援。目前能夠支援
MySql 叢集的版本有：基於 MySql 5.1 的 MySQL Cluster NDB 7.1；基於 MySql 5.5 的 MySQL Cluster NDB 7.2；基於 MySql 5.6 的 MySQL Cluster NDB 7.3。同樣基於 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正處於研發階段。
Merge：允許 MySql DBA 或開發者將一系列相同的 MyISAM 表進行分組，並把它們作為一個物件進行參照。適用於超大規模資料場景，如資料倉儲。
Federated：提供了從多個物理機上聯接不同的 MySql 伺服器來建立一個邏輯資料庫的能力。適用於分散式或者資料市場的場景。
Example：這種儲存引擎用以儲存闡明如何開始寫新的儲存引擎的 MySql 原始碼的例子。它主要針對於有興趣的開發人員。這種儲存引擎就是一個啥事也不做的 “存根”。你可以使用這種引擎建立表，但是你無法向其儲存任何資料，也無法從它們檢索任何索引。

四、儲存引擎相關sql語句

# 檢視當前的預設儲存引擎:

mysql> show variables like "default_storage_engine";

# 查詢當前資料庫支援的儲存引擎

mysql> show engines G;

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 

mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

# 也可以使用alter table語句，修改一個已經存在的表的儲存引擎。

mysql> alter table ai engine = innodb;

# my.ini檔案
[mysqld]
default-storage-engine=INNODB

五、mysql的工作流程

MySQL架構總共四層，在上圖中以虛線作為劃分。

最上層的服務並不是MySQL獨有的，大多數給予網路的使用者端/伺服器的工具或者服務都有類似的架構。比如：連線處理、授權認證、安全等。
第二層的架構包括大多數的MySQL的核心服務。包括：查詢解析、分析、優化、快取以及所有的內建函數（例如：日期、時間、數學和加密函數）。同時，所有的跨儲存引擎的功能都在這一層實現：儲存過程、觸發器、檢視等。
第三層包含了儲存引擎。儲存引擎負責MySQL中資料的儲存和提取。伺服器通過API和儲存引擎進行通訊。這些介面遮蔽了不同儲存引擎之間的差異，使得這些差異對上層的查詢過程透明化。儲存引擎API包含十幾個底層函數，用於執行“開始一個事務”等操作。但儲存引擎一般不會去解析SQL（InnoDB會解析外來鍵定義，因為其本身沒有實現該功能），不同儲存引擎之間也不會相互通訊，而只是簡單的響應上層的伺服器請求。
第四層包含了檔案系統，所有的表結構和資料以及使用者操作的紀錄檔最終還是以檔案的形式儲存在硬碟上。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。