Mysql資料庫儲存引擎

簡單介紹

儲存引擎就是指表的類型。資料庫的儲存引擎決定了表在電腦中的儲存方式。儲存引擎的概念是MySQl的特點，而且是一個插入式的儲存引擎概念。這就決定了MySQl資料庫中的表可以使用不同的儲存方式儲存。使用者可以根據自己的不同要求，選擇不同的儲存方式、是否進行交易處理等。

查詢方式及內容解析

使用SHOW ENGINES語句可以查看MySQL資料庫支援的儲存引擎類型。查詢方法如下：

SHOW ENGINES；

SHOW ENGUNES語句可以使用“；”結束，也可以使用“\g”或者“\G”結束。“\g”與“；”的作用相同，“\G”可以讓結果顯示的更加美觀。

mysql> SHOW ENGINES\G*************************** 1. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 2. row ***************************      Engine: InnoDB     Support: DEFAULT     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES*************************** 3. row ***************************      Engine: MyISAM     Support: YES     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO###############中間已省略###################*************************** 8. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO          XA: NO  Savepoints: NO8 rows in set (0.11 sec)

解析：查詢結果中，Engine參數指儲存引擎名稱；Support參數說明MySQL是否支援該類引擎，YES表示支援；Comment參數指對該引擎的評論；Transactions 參數表示是否支援交易處理，YES表示支援；XA參數表示是否分布式交易處理XA規範，YES表示支援；Savepoints參數表示是否支援儲存點，以便交易回復到儲存點，YES表示支援。

從查詢結果中可以看出，MySQL支援的引擎參數包括MyISAM、MEMORY、InnoDB、ARCHIVE和MRG_MYISAM等。其中InnoDB為預設的儲存引擎。可使用語句查詢預設的儲存引擎此代碼如下：

SHOW VARIABLES LIKE 'storage_engine';

代碼執行的結果如下：

mysql> SHOW VARIABLES LIKE 'storage_engine';+----------------+--------+| Variable_name  | Value  |+----------------+--------+| storage_engine | InnoDB |+----------------+--------+1 row in set (0.10 sec)

解析：結果顯示預設的儲存引擎為InnoDB。如果想修改預設的儲存引擎可以在設定檔my.ini中修改。將”default-storage-engine=InnoDB”更改為”default-storage-engine=MyISAM”。然後重啟服務，修改生效。

使用SHOW TABLESTATUS可以查看某個庫中所有表支援的儲存引擎類型查詢方法如下：

mysql> USE hellodbDatabase changedmysql> SHOW TABLE STATUS\G*************************** 7. row ***************************           Name: toc         Engine: MyISAM        Version: 10     Row_format: Fixed           Rows: 0 Avg_row_length: 0    Data_length: 0Max_data_length: 2533274790395903   Index_length: 1024      Data_free: 0 Auto_increment: 1    Create_time: 2013-08-12 16:17:23    Update_time: 2013-08-12 16:17:23     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:        Comment:

解析：結果顯示對於MySQL來說所有表預設都是使用MyISAM儲存引擎，其中Name：表名；Engine：使用的儲存引擎；Version：所用儲存引擎的版本；Row_format：行格式，對於MyISAM儲存引擎來說常見的有Dynamic變長記錄）, Fixed定長記錄）, Compressed壓縮以後存放的行）, compactInnoDB中常見的）；Rows: 表中的行數對於其他儲存引擎來說這個值是估算的）；Avg_row_length: 行的平均位元組數；Data_length：表的資料量，單位為位元組；Max_data_length：表的容量上限不同的儲存引擎容量上限不同）；Index_length：索引資料量，單位位元組；Data_free：已經分配出去，但未儲存資料的儲存空間；Auto_increment：具有自動成長屬性的欄位上，下一個自動成長的值；Create_time：表的建立時間；Update_time: 資料最近一次的更新時間；Check_time: 使用CHECK命令最近一次檢查表的時間；myisamchk; Checksum: 表的校正和；Create_options:建立表時指定的其它選項；Comment: 對於MyISAM表，儲存的是建立表時的comment表選項指定的資訊；對InnoDB表來講，儲存對應的資料表空間剩餘的資料表空間資訊。

各種儲存引擎特性比較：

在實際工作中，選擇一個合適的儲存引擎是一個很複雜的問題。每種儲存引擎都有各自的優勢，不能籠統的說誰比誰更好。下面將詳解不同環境經常用到的儲存引擎和針對各個儲存引擎的特點進行對比，給出不同的選擇建議。

• InnoDB儲存引擎

InnoDB是Mysql資料庫的一種儲存引擎。InnoDB給Mysql的表提供了 事務、復原、崩潰修複能力、多版本並發控制的事務安全、間隙鎖可以有效防止幻讀的出現）、支援輔助索引、聚簇索引、自適應hash索引、支援熱備、行級鎖。還有InnoDB是Mysql上唯一一個提供了外鍵約束的引擎。

InnoDB儲存引擎中，建立的表的表結構是單獨儲存的並且儲存在.frm檔案中。資料和索引儲存在一起的並且儲存在資料表空間中。但是預設情況下mysql會將資料庫的所有InnoDB表格儲存體在一個資料表空間中的。其實這種方式管理起來非常的不方便而且還不支援進階功能所以建議每個表格儲存體為一個資料表空間實現方式為：使用伺服器變數innodb_file_per_table = 1。

如果需要頻繁的進行更新、刪除操作的資料庫也可選擇InnoDB儲存引擎。因為該儲存引擎可以實現事務提交和復原。

• MyISAM儲存引擎

MyISAM儲存引擎是Mysql中常見的儲存引擎，MyISAM儲存引擎是基於ISAM儲存引擎發展起來的。MyISAM支援全文索引、壓縮存放、空間索引空間函數）、表級鎖、延遲更新索引鍵。但是MyISAM不支援事務、行級鎖、更無法忍受的是崩潰後不能保證完全恢複只能手動修複）。

MyISAM儲存引擎的表格儲存體成3個檔案。檔案的名字和表的名字相同。副檔名包含frm、MYD、MYI。其中frm為副檔名的檔案儲存體表的結構；MYD為副檔名的檔案儲存體資料，其是MYData的縮寫；MYI為副檔名的檔案儲存體索引，其為MYIndex的縮寫。

MyISAM儲存引擎的插入資料很快，空間和記憶體使用量比較低。如果表主要是用於插入新記錄和讀出記錄，那麼選擇MyISAM儲存引擎能夠實現處理的高效率。如果應用的完整性、並發性要求很低，也可以選擇MyISAM儲存引擎。

• ARCHIVE

ARCHIVE，見名之意可看出是歸檔，所以歸檔之後很多的進階功能就不再支援了僅支援插入insert）和查詢select）兩種功能， ARCHIVE儲存引擎之前還不支援索引在Mysql5.5以後開始支援索引了），但是它擁有很好的壓縮機制。通常用於做倉庫使用。

ARCHIVE儲存引擎適用於儲存日誌資訊或其他按時間序列實現的資料擷取類的應用情境中。

• CSV

CSV是將資料檔案儲存為CSV格式的的檔案的，可以方便的匯入到其他資料庫中去例如：excel表格，SQLserver等等），由此需要在資料庫間自由共用資料時才偶爾建議使用此儲存引擎。並且它也不支援索引；個人認為僅適用於資料交換。

• BLACKHOME

BLACKHOME叫做黑洞，也就是說沒有儲存機制，任何資料都會被丟棄，但是會記錄二進位日誌。一般在Mysql複製轉送伺服器）中經常用到，這個在Mysql複製部落格中將詳細介紹，敬請關注。

• FEDERATED

FEDERATED可以實現跨伺服器整理表，簡單說就是它可以訪問遠程伺服器上資料的儲存引擎，所以說它不再本地建立資料只會自動的建立一個串連到其他伺服器上連結，有點類似於代理的功能，預設都是禁用的。

• MEMORY儲存引擎

MEMORY儲存引擎是Mysql中的一類特殊的儲存引擎。其使用儲存在記憶體中的記憶體來建立表，而且所有資料儲存在記憶體中。資料安全性很低，但是尋找和插入速度很快。如果記憶體出現異常就會影響到資料的完整性，如果重啟或關機，表中的所有資料就會丟失，因此基於MEMORY儲存引擎的表的生命週期很短，一般都是一次性的。適用於某些特殊情境像尋找和映射，緩衝周期性的彙總資料等等。

• MRG_MYISAM

MRG_MYISAM儲存引擎是合并MyISAM表的，就是將多個MyISAM合并為一個在使用者看來是一個進行工作，其實是多個底層物理檔案在運行工作）。

• 擴充一些第三方儲存引擎
  

1）、OLTP類：

XtraDB：InnoDB的改進版

PBXT：支援複製，外鍵約束，而且支援在固態儲存SSD硬碟）上快速存取資料，是一個不錯的支援事務的儲存引擎，但是此存在的bug已不再修複，被棄用。

TokuDB：圖庫資料庫，在儲存海量資料的方面有著mysql無法比擬的優勢，也有mysql版的，其最大優勢支援分形樹索引結構，這個結構導致它和緩衝無關也就直接導致了就算索引在資料庫檔案中放不下也不會影響效能。一般只適用於大量插入資料的分析型情境。(注釋：這裡的圖不是照片等，而是複雜資料連線的資料結構。)

2）、列式儲存引擎

列式資料庫：此種資料庫最適合儲存大資料，在資料檢索上也很好但是在一定程度上需要反關係儲存，因此可能無法滿足我們關係型資料庫範式的概念所以被稱為Nosql。

下面介紹幾個列式儲存引擎都有兩個版本：社區版、商業版）：

Infobright：適合於數十TB的大環境中、支援資料壓縮，預設情況下mysql不支援列式儲存功能需要定製。使用者眾多，名氣很高。

MonetDB：首先，它的儲存模型是對資料從垂直方向進行切分；其次，MonetDB是第一個利用CPU緩衝對資料的查詢進行最佳化的資料庫系統；此外，MonetDB會自動管理和協調索引機制，最佳化查詢效率。目前使用者不是很多。

InfiniDB：InfiniDB Community Edition 社區版）提供一個可伸縮的AnalyticDB引擎，主要為資料倉儲、商業智慧、以及對即時性要求不嚴格的應用而開發。基於 MySQL 搭建。包括對查詢、交易處理以及大資料量載入的支援。目前使用者不是很多。

LucidDB：是唯一一款專註於資料倉儲和商務智能的開源RDBMS，它使用了列儲存架構，支援位元影像索引，雜湊串連/彙總和頁面級多版本，大部分資料庫最初都注重交易處理能力，而分析功能都是後來才加上去的。相反，LucidDB中的所有組件從一開始就是為滿足靈活的需求，高效能資料整合和大規模資料查詢而設計的，此外，其架構設計徹底從使用者出發，操作簡單。目前使用者不是很多。

3）、社區儲存引擎瞭解）：

Aria：Maria的下一代版本。

Groona：可以精確的實現全文索引引擎，可以替代MyISAM在索引上的特性。

QQGraph：支援圖操作，由Open query研發

Sphinx：外在的伺服器能夠在Mysql基礎上為Mysql構建一個高效的全文索引，通過C++研發，Mysql支援一個社區引擎叫SphinxSE，就是讓Sphinx直接支援Mysql介面，在MariaDB5.5.32上被編譯支援了。

Spider：支援分區，每一個獨立的分區可以實現獨立的查詢。

VPForMySQL：支援垂直資料分割，支援更大層級的資料操作更大層級的資料存放區。

選擇標準：

1、是否需要支援事務？？

2、是否需要使用熱備？？

3、崩潰恢複：能否接受崩潰？？

個人建議：

儲存日誌或按時間增長的資料：MyISAM、ARCHIVE

論壇應用：InnoDB

電商訂單：InnoDB

資料量大：Infobright、NoSQL、Sphinx

個人總結，希望對博友們有用！！650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/124K620T-0.gif" alt="j_0019.gif" />

本文出自 “起點夢想” 部落格，請務必保留此出處http://pangge.blog.51cto.com/6013757/1303893