MySQL（九）之MySQL存儲引擎

一、MVCC

1、簡介

   Multi-Version Concurrency Control 多版本并發控制，大多數的MySQL事務型存儲引擎，如InnoDB，Falcon以及PBXT都不使用一種簡單的行鎖機制。事實上，他們都和另外一種用來增加并發性的被稱為“多版本并發控制（MVCC）”的機制來一起使用。MVCC不隻使用在MySQL中，Oracle、PostgreSQL，以及其他一些資料庫系統也同樣使用它。 你可将MVCC看成行級别鎖的一種妥協，它在許多情況下避免了使用鎖，同時可以提供更小的開銷。根據實作的不同，它可以允許非阻塞式讀，在寫操作進行時隻鎖定必要的記錄。 MVCC會儲存某個時間點上的資料快照。這意味着事務可以看到一個一緻的資料視圖，不管他們需要跑多久。這同時也意味着不同的事務在同一個時間點看到的同一個表的資料可能是不同的。各個存儲引擎對于MVCC的實作各不相同。這些不同中的一些包括樂觀和悲觀并發控制。

2、InnoDB引擎實作原理

   InnoDB：通過為每一行記錄添加兩個額外的隐藏的值來實作MVCC，這兩個值一個記錄這行資料何時被建立，另外一個記錄這行資料何時過期（或者被删除）。但是InnoDB并不存儲這些事件發生時的實際時間，相反它隻存儲這些事件發生時的系統版本号。這是一個随着事務的建立而不斷增長的數字。每個事務在事務開始時會記錄它自己的系統版本号。每個查詢必須去檢查每行資料的版本号與事務的版本号是否相同。

3、使用其的優勢

   使用MVCC多版本并發控制比鎖定模型的主要優點是在MVCC裡， 對檢索（讀）資料的鎖要求與寫資料的鎖要求不沖突， 是以讀不會阻塞寫，而寫也從不阻塞讀。 在資料庫裡也有表和行級别的鎖定機制， 用于給那些無法輕松接受 MVCC 行為的應用。 不過，恰當地使用 MVCC 總會提供比鎖更好地性能。

二、MySQL存儲引擎簡介

   存儲引擎說白了就是如何存儲資料、如何為存儲的資料建立索引和如何更新、查詢資料等技術的實作方法。 因為在關系資料庫中資料的存儲是以表的形式存儲的，是以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）。 在Oracle 和SQL Server等資料庫中隻有一種存儲引擎，所有資料存儲管理機制都是一樣的。而MySql資料庫提供了多種存儲引擎。使用者可以根據不同的需求為資料表選擇不同的存儲引擎，使用者也可以根據自己的需要編寫自己的存儲引擎。

1、檢視系統引擎

檢視目前系統鎖支援的引擎：

MariaDB [hellodb]> SHOW ENGINES\G;      

檢視目前所在庫的所有表的資訊，包括存儲引擎：

MariaDB [hellodb]> SHOW TABLE status\G;      

也可以檢視指定表的所使用的引擎：

文法：SHOW TABLE STATUS [{FROM | IN} db_name]  [LIKE 'pattern' | WHERE expr]

說明：雖然我們既可以使用LIKE或者WHERE來選擇表，但是還是建議使用WHERE來選擇，畢竟是精确比對。

2、如何修改預設存儲引擎

檢視目前系統預設的存儲引擎：

MariaDB [hellodb]> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)      

若想修改預設存儲引擎，然後查出目前系統所能支援的引擎，然後根據實際需要修改。

3、關于表的屬性資訊說明

使用舉例：

MariaDB [hellodb]> SHOW TABLE STATUS FROM hellodb WHERE Name='Students'\G;
*************************** 1. row ***************************
           Name: students
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 27
 Avg_row_length: 25
    Data_length: 680
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 28
    Create_time: 2015-08-29 21:54:14
    Update_time: 2015-08-29 23:05:55
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)      

下面關于各行的資訊說明：

• Name： 表名；
• Engine ：存儲引擎；
• Version：版本；
• Row_format：行格式。

     {DEFAULT|DYNAMIC|FIXED|COMMPRESSED|REDUNDANT|COMPACT}

• Rows：表中的行數，在MyISAM存儲引擎中是準确值，在InnoDB存儲引擎中是估計值；
• Avg_row_length：平均每行包含的位元組數；
• Data_length：表中資料總體大小，機關為位元組；
• Max_data_length：表能夠占用的最大空間，機關為位元組，0表示麼有上限；
• Index_length：索引的大小，機關為位元組；
• Data_free：對于MyISAM表，表示已經配置設定但尚未使用的空間，其中包含此前删除行之後騰出來的空間；
• Auto_increment：下一個AUTO_INCREMENT的值；
• Create_time：表的建立時間；
• Update_time： 表資料的最後一次修改時間；
• Check_time： 使用CHECK TABLE或myisamchk最近一次檢查表的時間；
• Collation：排序規則；
• Checksum：如果啟動，則為表的checksum；
• Create_options：建立表時指定使用的其他選項；
• Comment：表的注釋資訊；

三、MyISAM和InnoDB表的存儲結構

1、MyISAM表，每表有三個檔案，都位于資料庫目錄中

        tb_name.frm： 表結構定義

        tb_name.MYD： 資料檔案

        tb_name.MYI： 索引檔案

2、InnoDB表，有兩種存儲方式

• 預設：每表有一個獨立檔案和一個多表共享的檔案；

         tb_name.frm： 表結構的定義，位于資料庫目錄中；

         ibdata#： 共享的表空間檔案，預設位于資料目錄(datadir指向的目錄)中。

• 獨立的表空間：

         tb_name.frm： 每表有一個表結構檔案；

         tb_name.ibd： 一個獨有的表空間檔案。

表空間：table space，由InnoDB管理的特有格式資料檔案，内部可同時存儲資料和索引 。

四、各存儲引擎的特性

1、InnoDB

   InnoDB，是MySQL的資料庫引擎之一，為MySQL AB釋出binary的标準之一。InnoDB由Innobase Oy公司所開發，2006年五月時由甲骨文公司并購。下面列舉幾個其主要功能：

• 事務
• 外鍵
• MVCC 
• 聚簇索引 
• 行級鎖
• 支援輔助索引 ：聚簇索引之外的其它索引，通常稱為輔助索引
• 支援自适應hash索引 
• 支援熱備份 

2、MyISAM

   MyISAM是預設存儲引擎。它基于更老的ISAM代碼，但有很多有用的擴充。（注意MySQL 5.1不支援ISAM）。 每個MyISAM在磁盤上存儲成三個檔案。每一個檔案的名字均以表的名字開始，擴充名指出檔案類型。.frm檔案存儲表定義。資料檔案的擴充名為·MYD (MYData)。

下面列舉幾個其主要功能：

• 全文索引 
• 壓縮：用于實作資料倉庫，能節約存儲空間并提升性能 
• 空間索引 
• 表級鎖 
• 延遲更新索引 

不支援功能：

• 不支援事務、外鍵和行級鎖 
• 崩潰後無法安全恢複資料 

适用場景：

隻讀資料、較小的表、能夠容忍崩潰後的修改操作和資料丢失

3、ARCHIVE

其主要功能：

• 僅支援INSERT和SELECT，支援很好壓縮功能； 
• 适用于存儲日志資訊，或其它按時間序列實作的資料采集類的應用； 

• 不支援事務，不能很好的支援索引； 

4、CSV

• 将資料存儲為CSV格式；
• 僅适用于資料交換場景；

• 不支援索引； 

5、BLACKHOLE

• 沒有存儲機制，任何發往此引擎的資料都會丢棄；
• 其會記錄二進制日志，是以，常用于多級複制架構中作中轉伺服器； 

6、MEMORY

• 儲存資料在記憶體中，記憶體表；
• 常用于儲存中間資料，如周期性的聚合資料等；
• 用于實作臨時表； 
• 支援hash索引，使用表級鎖；

• 不支援BLOB和TEXT資料類型；

7、MRG_MYISAM

• MYISAM的一個變種，能夠将多個MyISAM表合并成一個虛表； 

8、NDB

• MySQL CLUSTER中專用的存儲引擎 

9、第三方的存儲引擎

OLTP（線上事務出理）： 

• XtraDB：增強的InnoDB，由Percona提供； 

• PBXT： MariaDB自帶此存儲引擎 
  • 支援引擎級别的複制、外鍵限制，對SSD磁盤提供适當支援；
  • 支援事務、MVCC
• TokuDB： 使用Fractal Trees索引，适用存儲大資料，擁有很壓縮比；已經被引入MariaDB；

列式存儲引擎：

• Infobright： 目前較有名的列式引擎，适用于海量資料存儲場景，如PB級别，專為資料分析和資料倉庫設計；
• InfiniDB
• MonetDB
• LucidDB

10、開源社群存儲引擎

• Aria：前身為Maria，可了解為增強版的MyISAM(支援崩潰後安全恢複，支援資料緩存)；
• Groona：全文索引引擎，Mroonga是基于Groona的二次開發版；
• OQGraph：由Open Query研發，支援圖結構的存儲引擎；
• SphinxSE：為Sphinx全文搜尋伺服器提供了SQL接口；
• Spider：能資料切分成不同分片，比較高效透明地實作了分片(shared)，并支援在分片上支援并行查詢；

11、如何選擇存儲引擎

• 是否需要事務
• 備份的類型的支援
• 崩潰後的恢複
• 特有的特性