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 tables
Transactions: NO
        XA: NO
  Savepoints: NO
*************************** 2. row ***************************
    Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
        XA: YES
  Savepoints: YES
*************************** 3. row ***************************
    Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
        XA: NO
  Savepoints: NO
###############中間已省略###################
*************************** 8. row ***************************
    Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
        XA: NO
  Savepoints: NO
8 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 hellodb
Database changed
mysql> SHOW TABLE STATUS\G
*************************** 7. row ***************************
              Name: toc
            Engine: MyISAM
          Version: 10
      Row_format: Fixed
              Rows: 0
 Avg_row_length: 0
     Data_length: 0
Max_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（壓縮以后存放的行）, compact（InnoDB中常見的）；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 （社區版）提供一個可伸縮的分析型數據庫引擎，主要為數據倉庫、商業智能、以及對實時性要求不嚴格的應用而開發。基于 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