• MySQL的存儲引擎
  • 0. 存儲引擎的查看和修改
  • 1. MyISAM
  • 2. InnoDB
  • 3. MEMORY
  • 4. MERGE
  • 5. 總結
  • 6. 參考博客

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