mysql 存儲引擎深入了解

基礎語句

檢視表語句
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET utf8mb4 NOT NULL,
  `sex` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '性别1男2女 未知0',
  `age` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

檢視表資訊
mysql> show table status like 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 22
    Create_time: 2019-08-02 14:49:29
    Update_time: 2019-08-06 09:37:37
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
           

InnoDB 存儲引擎

InnoDB 是mysql 預設事務型引擎，也是最重要、使用最廣泛的存儲引擎。InnoDB 的性能和自動崩潰恢複的特性，使得他在非事務性存儲的需求也很流行

• InnoDB的資料存儲在表空間，表空間是由inno管理的一個黑盒子，由一系列資料檔案組成。
• InnoDB·采用MVCC來支援高并發，并實作了四個标準的隔離級别。預設級别是REPEATEBLE READ （可重複讀），并通過間隙鎖（next-key locking）政策防止幻讀的出現。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，防止幻影行的插入。
• InnoDB表是基于聚簇索引建立的，InnoDB的索引結構和mysql其他存儲引擎有很大不同，聚簇索引對主鍵查詢有很高的性能。不過他的二級索引（費主鍵是以）中必須包含主鍵列，是以如果逐漸很大的話，其他的所有索引都會很大，是以表索引比較多的話主鍵應當盡量小。
• InnoDB内部做了很多優化，

1. 從磁盤讀取資料時采用可預測性預讀，
2. 能自動在記憶體中建立hash索引以加速讀操作的自适應哈希
3. 加速插入操作的插入緩沖區 等

• InnoDB通過一些機制和工具支援真正的熱備份，mysql其他存儲引擎不支援熱備份

MyISAM 存儲引擎

在mysql5.1及之前的版本， MyISAM 是預設的存儲引擎，MyISAM提供了大量的特性，包括 全文索引、壓縮、空間函數GIS等， 但不支援事務和行級鎖，并且崩潰後無法安全恢複

存儲

MyISAM會将表存儲在兩個檔案：資料檔案.MYD和索引檔案.MYI，

MyISAM特性

-加鎖和并發

MyISAM對整張表加鎖，而不是針對行，讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖，

但是在表讀取查詢的同時，也可以往表裡插入新記錄**（這被稱為并發插入 CONCURRENT INSERT）**

• 修複

  MyISAM表可以手動或自動執行檢查和修複操作，執行表的修複可能導緻一些資料丢失，而且修複操作非常慢。

查詢表
    mysql> check table user_mi;
   +---------------+-------+----------+----------+
   | Table         | Op    | Msg_type | Msg_text |
   +---------------+-------+----------+----------+
   | study.user_mi | check | status   | OK       |
   +---------------+-------+----------+----------+
   修複表
   mysql> repair table user_mi;
   +---------------+--------+----------+----------+
   | Table         | Op     | Msg_type | Msg_text |
   +---------------+--------+----------+----------+
   | study.user_mi | repair | status   | OK       |
   +---------------+--------+----------+----------+
           

• 索引特性

  對于MyISAM表，即使是BLOB和TEXT等長字段，也可以基于前500個字元建立索引。

  MyISAM也支援全文索引，這是一種基于分詞建立的索引，可以支援複雜查詢

• MyISAM壓縮表

  如果表不會在進行修改操作 那麼這樣的表适合MyISAM壓縮表可以使用 myisampack

[email protected] mysql$ myisampack  study/user_mi
	study/user_mi is too small to compress
           

• MyISAM性能

  MyISAM引擎設計簡單，資料以緊密格式存儲，是以在某些場景下性能很好。但是最典型的性能問題還是表鎖問題，如果你發現所有的查詢都長期處于 "Locked"狀态，那麼毫無疑問表鎖導緻的。

Archive引擎

Archive 隻支援 INSERT 和SELECT操作 在5.1之前也不支援索引。

• Archive引擎會緩存所有的寫并利用zlib對插入的行進行壓縮，比Myisam表的磁盤I/O更少。但是每次 SELECTE查詢都要全表掃描。是以适合做日志和資料采集類應用，或者在一些更需要快速INSERT操作場合下使用。
• Archive引擎支援行級鎖和專用的緩沖區，是以可以實作高并發插入。在一個查詢開始直到傳回表中存在的所有行數之前，Archive會足赤其他的SELECT執行，以實作一緻性讀。另外也實作了批量插入完成前對讀操作不可見。

Blackhole引擎

Blackhole引擎沒有實作任何存儲機制，它會丢棄所有插入的資料，不做任何儲存。但是伺服器會記錄Blackhole表的日志，是以可以用于複制資料到備庫，或隻是簡單的記錄到日志。

CSV引擎

CSV引擎可以将普通的CSV檔案（逗号分隔值的檔案）作為mysql表處理，但不支援索引。可以作為一種資料交換的機制。

Federated引擎

Federated引擎是通路其他mysql伺服器的一個代理，他會建立一個到遠端mysql伺服器的用戶端連接配接，并将查詢傳輸到遠端服務期執行，然後提取或發送需要的資料。

Memory引擎

如果需要快速的通路資料，并且這些資料不會被修改，重新開機後丢失也沒關系，可以使用Memory。

所有的資料存儲在記憶體中，無需磁盤I/O,Memory表的結構重新開機後會保留但資料會丢失

• 用于查找lookup或映射mapping表，例如将郵編和州名映射的表
• 用于緩存周期性聚合資料的結果

• 用于儲存資料分析中産生的中間資料。

  Memory支援hash索引，是以查找速度非常快。Memory是表級鎖，是以并發寫入性能低。

  不支援BLOB或TEXT類型的列，并且每行長度固定，即使制定了varchar也會在實際存儲的時候轉換成char，可能會導緻記憶體浪費。

  如果mysql執行查詢的過程中需要使用臨時表儲存中間結果，内部使用的臨時表就是Memory表。如果中間結果太大，超過了Memory限制或包含Blob或Text字段，則會使用myisam。

Merge引擎

merge引擎是myisam的一個變種。引入分區功能後已廢棄。

NDB叢集引擎

叢集存儲引擎

第三方存儲引擎 略

選擇适合的引擎

大部分情況InnoDB都是正确的選擇，Mysql5.5 開始講InnoDB作為預設存儲引擎。一句話：除非需要某些InnoDB不具備的特性，并且沒有其他辦法替代，否則都應選用InnoDB引擎

例如

• 如果用到全文索引，建議優先考慮InnoDB加上Sphinx組合，而不是選擇myisam
• 除非萬不得已，不建議混用多種存儲引擎，會帶來一系列複雜的問題和潛在的bug和邊界問題。