MySQL 存儲引擎 | innodb和myisam的差別

此文檔基于MySQL5.7

目錄

存儲引擎概念

存儲引擎有哪些

InnoDB存儲引擎的詳細資訊

InnoDB架構

InnoDB的主要優勢

InnoDB和ACID模型

InnoDB和MyISAM特性對比

常用的存儲引擎sql

存儲引擎概念

儲存引擎之于MySQL，猶如發動機之于汽車。不過mysql對存儲引擎做了很好的封裝，是以對于使用者來說，存儲引擎是個黑盒。

存儲引擎有哪些

自2010釋出的5.5版本MySQL起，InnoDB成為預設的存儲引擎。除InnoDB外，MySQL還提供了多個替代存儲引擎。

MySQL 存儲引擎

存儲引擎	特性
InnoDB	InnoDB 是一種兼顧了高可靠性和高性能的通用存儲引擎。在MySQL 5.7中， InnoDB 是預設的MySQL存儲引擎。除非您配置了其他預設存儲引擎，否則發出 CREATE TABLE 不帶 ENGINE  子句的語句将建立一個 InnoDB 表。詳細特性見下文。
MyISAM	在MySQL5.1及之前的版本，MyISAM是預設的存儲引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數（GIS）等，但MyISAM不支援事務和行級别鎖，而且有一個毫無疑問的缺陷就是崩潰後無法安全恢複。正是由于MyISAM引擎的緣故，即使MySQL支援事務已經很長時間了，在很多人的概念中MySQL還是非事務型的資料庫。盡管MyISAM有些缺點，但它絕不是一無是處的。對于隻讀的資料，或者表比較小、可以忍受修複（repair）操作，則依然可以使用MyISAM引擎。詳細特性見下文。
MEMORY	如果需要快速的通路資料，并且這些資料不會被修改，重新開機後丢失也沒關系，那麼使用Memory表是非常有用的。Memory表至少比MyISAM表要快一個數量級，因為所有的資料都儲存在記憶體中，不需要進行磁盤I/O。Memory表的結構在重新開機後會保留，單資料會丢失。
CSV	CSV引擎可以将普通的CSV檔案（逗号分割值的檔案）作為MySQL的表來處理，但這種表不支援索引。CSV引擎可以在資料庫運作時拷入或拷出檔案。可以将Excel等電子表格軟體中的資料存儲為CSV檔案，然後複制到MySQL資料目錄下，就能在MySQL中打開使用。同樣，如果将資料寫入到一個CSV引擎表，其他的外部資料也能立即從表的資料檔案中讀取CSV格式的資料。是以CSV引擎可以作為一種資料交換的機制，非常有用。
ARCHIVE	Archive存儲引擎隻支援INSERT和SELECT操作，在MySQL5.1之前也不支援索引。Archive存儲引擎會緩存所有的寫并利用zlib對插入的行進行壓縮，是以比MyISAM表的磁盤I/O更少。但是每次SELECT查詢都需要執行全表掃描。是以Archive表适合日志和資料采集類應用，這類應用做資料分析時往往需要全表掃描。或者在一些需要更快速的INSERT操作的場合下也可以使用。 Archive存儲引擎支援行級鎖和專用的緩沖區，是以可以實作高并發的插入。在一個查詢開始知道傳回表中存在的所有行數之前，Archive存儲引擎會阻止其他的SELECT執行，以實作一緻性讀。另外，也實作了批量插入在完成之前對讀操作是不可見的。這種機制模仿了事務和MVCC的一些特性，但Archive存儲引擎不是一個事務型的引擎，而是一個針對高速插入和壓縮做了優化的簡單引擎。
BLACKHOLE	BLACKHOLE引擎沒有實作任何的存儲機制，它會丢棄所有的插入的資料，不做任何儲存。但是伺服器會記錄BLACKHOLE表的日志，是以可以用于複制資料庫的備庫，或者隻是簡單的記錄到日志。這種特殊的存儲引擎可以在一些特殊的複制架構和日志稽核時發揮作用。但這種應用方式我們碰到很多問題，是以不推薦使用。
MERGE	Meger引擎是MyISAM引擎的一個變種。Meger表是由多個MyISAM表合并而來的虛拟表。如果将MySQL用于日志或者資料倉庫類應用，該引擎可以發揮作用。但是引入分區功能後，該引擎以已經被放棄。
FEDERATED	Federated引擎是通路其他MySQL伺服器的一個代理，它會建立一個到遠端MySQL伺服器的用戶端連接配接，并将查詢傳輸到遠端伺服器執行，然後提取或者發送需要的資料。最初設計垓存儲引擎是為了和企業級資料庫如Microsoft SQL Server和Oracle的類似特性競争的，可以說更多的是一種市場行為。盡管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，是以預設是禁用的。MariaDB使用了它的一個後續改進版本，叫做FederatedX。
EXAMPLE	EXAMPLE 存儲引擎是一個存根引擎，什麼都不做。其目的是作為MySQL源代碼中的一個示例，該示例說明如何開始編寫新的存儲引擎。

InnoDB存儲引擎的詳細資訊

InnoDB架構

主要分為記憶體和磁盤兩個大部分

記憶體部分(Memory)又包含Buffer pool（緩沖池）, change buffer Log Buffer 等,通過作業系統緩存與硬碟結構進行互動。采用了自适用hash索引。

緩沖池

緩沖池是主記憶體中的一個區域，在

InnoDB

通路表和索引資料時會在其中進行 高速緩存。緩沖池允許直接從記憶體通路經常使用的資料，進而加快了處理速度。在專用伺服器上，通常将多達80％的實體記憶體配置設定給緩沖池。

為了提高大容量讀取操作的效率，緩沖池被劃分為多個頁面，這些頁面可以潛在地容納多行。為了提高緩存管理的效率，緩沖池被實作為頁面的連結清單。使用最近最少使用（LRU）算法的變體，将很少使用的資料從緩存中老化掉。

知道如何利用緩沖池将經常通路的資料保留在記憶體中是MySQL調優的重要方面。

緩沖池使用LRU算法的變體作為清單進行管理  緩沖池(buffer pool)，這次徹底懂了！！！

采用了自适應哈希索引。

硬碟

硬碟部分包含若幹個表空間,如系統表空間,單檔案表空間,一般表空間,臨時表空間,undo表空間和redo log.

InnoDB的主要優勢

• 其DML操作遵循ACID模型，并具有具有送出，復原和崩潰恢複功能的事務，以保護使用者資料
• 行級鎖定和Oracle風格的一緻讀取可提高多使用者并發性和性能

• InnoDB

  表将您的資料安排在磁盤上，以基于主鍵優化查詢。每個 

  InnoDB

  表都有一個稱為聚集索引的主鍵索引，該索引組織資料以最小化主鍵查找的I / O
• 為了保持資料完整性，

  InnoDB

  支援 

  FOREIGN KEY

  限制。使用外鍵檢查插入，更新和删除，以確定它們不會導緻相關表之間的不一緻

InnoDB和ACID模型

ACID模式是一組資料庫設計原則強調的是，對于業務資料和關鍵任務應用重要的可靠性方面。MySQL包含諸如

InnoDB

存儲引擎嚴格遵守ACID模型，是以資料不會損壞，結果不會因軟體崩潰和硬體故障等異常情況而失真。當依靠符合ACID的功能時，無需重新發明一緻性檢查和崩潰恢複機制。如果有其他軟體保護措施，超可靠的硬體或可以容忍少量資料丢失或不一緻的應用程式，則可以調整MySQL設定，以權衡一些ACID可靠性，以獲得更高的性能或吞吐量。

InnoDB和MyISAM特性對比

特征	InnoDB	MyISAM
B樹索引	是的	是的
備份/時間點恢複（在伺服器中而不是在存儲引擎中實作。）	是的	是的
叢集資料庫支援	不	不
聚集索引	是的	不
壓縮資料	是的	是（僅在使用壓縮行格式時才支援壓縮MyISAM表。将壓縮行格式與MyISAM一起使用的表是隻讀的。）
資料快取	是的	不
加密資料	是（通過加密功能在伺服器中實作；在MySQL 5.7和更高版本中，支援靜态資料加密。）	是（通過加密功能在伺服器中實作。）
外鍵支援	是的	不
全文搜尋索引	是（MySQL 5.6和更高版本提供對FULLTEXT索引的支援。）	是的
地理空間資料類型支援	是的	是的
地理空間索引支援	是（MySQL 5.7和更高版本提供對地理空間索引的支援。）	是的
哈希索引	否（InnoDB在内部将哈希索引用于其“自适應哈希索引”功能。）	不
索引緩存	是的	是的
鎖定粒度	排	排
MVCC	是的	不
複制支援（在伺服器中而不是在存儲引擎中實作。）	是的	是的
儲存限制	64TB	64TB
T樹索引	不	不
交易次數	是的	不
更新資料字典的統計資訊	是的	是的

常用的存儲引擎sql

檢視目前使用的存儲引擎

SHOW ENGINES;

SELECT * FROM INFORMATION_SCHEMA.ENGINES;
           

設定存儲引擎

CREATE TABLE t1 (i INT) ENGINE = INNODB;
           

更新存儲引擎

ALTER TABLE t1 ENGINE = MyISAM;
           

 引用：

MySQL官方文檔

【mysql】Innodb三大特性之adaptive hash index

寫緩沖(change buffer)，這次徹底懂了！！！