MySQL的儲存引擎

mysql主要有四類儲存引擎，目前主要使用InnoDB作為儲存引擎。

0. 儲存引擎的檢視和修改

• 檢視當前資料庫的預設儲存引擎

  show variables like 'default_storage_engine';
  

• 檢視當前資料庫所支援的儲存引擎

  show engine;//語句1
  show variables like 'have_%';//語句2
  

• 檢視支援事務處理的儲存引擎

  select engine from information_schema.engines where transactions='yes';
  

• 設定新表的儲存引擎

  create table 表名 (欄位名 欄位型別) engine=儲存引擎;//語句1
  create table 表名 (欄位名 欄位型別) type=儲存引擎;//語句2
  

1. MyISAM

1. 檔案組成
   • .frm檔案：表結構定義，frame，可以理解成對整體框架的儲存
   • .MYD檔案：資料檔案，儲存的是具體的資料庫資料條目
   • .MYI檔案：索引檔案，儲存的是資料庫表項的索引檔案
     如果用圖書管理系統做比喻的話，那麼，.frm檔案存放的是書架本身，.MYD檔案儲存具體的書籍，.MYI檔案儲存檢索書籍的索引目錄。
     .MYI檔案和.MYD檔案可以儲存在不同的檔案目錄中，從而分散IO讀寫壓力，提高存取速度，具體操作可在建立表的時候，通過DATA DIRECTORY和INDEX DIRECTORY屬性進行設定。
2. 適用範圍
   由於MyISAM不支援事務，不支援外來鍵，存取速度快的特點，適用於以下特點的資料庫：
   - 不要求事務完整性
   - 操作主要是查詢SELECT和INSERT
3. 安全性
   MyISAM表中有一個標誌，用於儲存上次退出表是否是正常退出，每次啟動該表之前會檢查該標誌，如果上次是異常退出，則考慮進行檢查和修復。
   可使用CHECK TABLE和REPAIR TABLE命令進行表的檢查和修改。
4. 支援的儲存格式
   • 靜態表：每個條目長度固定
   • 動態表：每個條目長度不固定
   • 壓縮表：壓縮儲存，節省空間

三者的特點如下所示：

1. 優缺點
   1. 優點：存取速度快，
   2. 缺點：不支援事務，不支援外來鍵，不支援行級鎖，不支援崩潰後的安全恢復，不支援並行插入（效能方面）

2. InnoDB

1. 自動增長列
   指的是InnoDB支援使用者手動插入的條目遵循索引項的自動增長，而不需要使用者自己設定。

   然而，在InnoDB中，自動增長列必須是索引項，如果是組合索引，則是其中的第一列；而對於MyISAM，則可以是任意資料項。

   建立一個表之後，其自動增長列的起始值預設是1，也可以在建立表的時候進行修改：

   CREATE TABLE t1 (
       id INT NOT NULL AUTO_INCREMENT,
       name CHAR(30) NOT NULL,
       PRIMARY KEY (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=某個起始數位;
   

   在插入條目的時候，也可以通過ALTER TABLE命令進行修改：

   ALTER TABLE 具體條目 AUTO_INCREMENT=某個起始數位;
   

2. 外來鍵約束

   • 在本文介紹的四個儲存引擎中，只有InnoDB支援外來鍵約束。
   • 外來鍵約束可以指定資料表中，對父表的操作如何影響子表，具體引數如下：
     • restrict：父表的刪除和更新不會對子表產生影響
     • cascade：級聯操作，父表的刪除和更新操作會影響子表，
     • set null：父表的刪除和更新將會使得子表中相關條目設定為null值
     • no action：效果等同於restrict，父表的刪除和更新操作不會對子表產生影響。
   • 外來鍵約束的開關
     根據set forgein_key_checks=0或者1的值，可以開啟（值為1）或者關閉（值為0）外來鍵約束。

3. 優缺點

   1. 優點：支援回滾等事務處理
   2. 缺點：存取效率低，花費記憶體儲存索引結構，佔用記憶體較大

3. MEMORY

1. 使用記憶體內容
   MEMORY表使用記憶體中的資料進行儲存管理，因此，在使用期間需要足夠的記憶體空間，當使用結束之後，需要進行記憶體釋放，命令如下

   DROP FROM MEMORY 表名;
   TRUNCATE table 表名;
   

2. hash結構
   MEMORY表的索引結構為hash，因此，有很快的存取速度，但是也引申出對於資料庫操作指令的響應效能問題：
   • 相等比較：=，<=>，效能較好
   • 範圍比較：>，<，BETWEEN，IN，LIKE，效能較差
   • 排序比較：order by，效能較差
     可通過改進為B樹結構來提高效能
3. 檔案組成：
   使用記憶體中的資料來儲存，只需要一個.frm檔案，用於儲存表結構定義。
4. 固定長度儲存
   建立該表的時候，需要設定固定長度，內部的條目長度固定，可設定最大行數來確定所需要的記憶體大小，max_heap_table_size引數用於設定最大行數。
   因此，對於TEXT、BLOB等可變長度資料型別並不支援，但是對於VARCHAR資料型別，在實際的資料庫操作中依然看作固定長度，因此可支援VARCHAR資料型別。
5. 優缺點
   根據上文的分析，可得出MEMORY表的優缺點如下：
   • 優點： 存取速度快，使用hash儲存對於相等比較操作效能良好，使用記憶體中的資料而不用額外生成新檔案
   • 缺點： 只支援固定長度的資料條目，對於可變長度的資料型別不支援，且使用hash索引，對於範圍、排序類的資料比較操作效能較差，需要額外釋放記憶體。

4. MERGE

1. 檔案組成
   • .frm: 儲存表的框架資訊
   • .MRG：儲存表的定義資訊
2. 具體操作
   Merge儲存引擎相當於MyISAM的一個集合，需要多個表結構相同的MyISAM組合，本身並不存在資料，僅僅是一個結構，增刪改查等操作還是需要對於具體的MyISAM表進行操作。
3. 優缺點
   • 優點： 管理多個MyISAM表，方便操作
   • 缺點： 本身是多個MyISAM表的複合表，因此，MyISAM表的缺點都有。

5. 總結

對上述四種儲存引擎進行比較，得出如下表格：

6. 參考部落格

• 參考部落格-1