一文讀懂mysql的存儲引擎之InnoDB 與 MyISAM

mysql的存儲引擎

概念:

• 存儲資料，為存儲資料建立索引，更新查詢資料。
• 因為在關系資料庫中資料以表的形式存儲，是以存儲引擎也叫表類型。
• MySQL 5.7 支援的存儲引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用SHOW ENGINES;語句檢視系統所支援的引擎類型。
• Support 列的值表示某種引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示該引擎為目前預設的存儲引擎。

存儲引擎類型：

存儲引擎	描述
ARCHIVE	用于資料存檔的引擎，資料被插入後就不能再修改了，且不支援索引。
CSV	在存儲資料時，會以逗号作為資料項之間的分隔符。
BLACKHOLE	會丢棄寫操作，該操作會傳回空内容。
FEDERATED	将資料存儲在遠端資料庫中，用來通路遠端表的存儲引擎。
InnoDB	具備外鍵支援功能的事務處理引擎
MEMORY	置于記憶體的表
MERGE	用來管理由多個 MyISAM 表構成的表集合
MyISAM	主要的非事務處理存儲引擎
NDB	MySQL 叢集專用存儲引擎

InnoDB存儲引擎

• 支援事務，是事務安全的。

• 如果某張表需要執行大量的增（insert）、删（delete）、改（update）操作，出于事務安全方面的考慮，InnoDB存儲引擎是更好的選擇。

• 支援外鍵
• InnoDB存儲引擎的表支援全文索引，大幅提升了InnoDB存儲引擎的文字檢索能力。
• InnoDB表空間分為共享表空間、獨享表空間。
• 适用場景：需要事務支援、行級鎖定對高并發有很好地适應能力，但需要確定查詢是通過索引完成、資料更新較為頻繁。

InnoDB引擎調優：

• 主鍵盡可能小，避免給secondary index帶來過大的空間負擔。
• 避免全表掃描，因為會使用行級表鎖。
• 盡可能緩存所有的索引和資料，提高響應速度，減少磁盤I/O消耗。
• 在執行大量插入操作的時候，盡量自己控制事務而不要使用autocommit自動送出。有開關可以控制送出方式。
• 合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性。

• innodb_flush_log_at_trx_commit=0，表示每隔一秒把log buffer刷到檔案系統中(os buffer)去，并且調用檔案系統的“flush”操作将緩存重新整理到磁盤上去。也就是說一秒之前的日志都儲存在日志緩沖區，也就是記憶體上，如果機器宕掉，可能丢失1秒的事務資料。
• innodb_flush_log_at_trx_commit=1，表示在每次事務送出的時候，都把log buffer刷到檔案系統中(os buffer)去，并且調用檔案系統的“flush”操作将緩存重新整理到磁盤上去。這樣的話，資料庫對IO的要求就非常高了，如果底層的硬體提供的IOPS比較差，那麼MySQL資料庫的并發很快就會由于硬體IO的問題而無法提升。
• innodb_flush_log_at_trx_commit=2，表示在每次事務送出的時候會把log buffer刷到檔案系統中去，但并不會立即刷寫到磁盤。如果隻是MySQL資料庫挂掉了，由于檔案系統沒有問題，那麼對應的事務資料并沒有丢失。隻有在資料庫所在的主機作業系統損壞或者突然掉電的情況下，資料庫的事務資料可能丢失1秒之類的事務資料。這樣的好處，減少了事務資料丢失的機率，而對底層硬體的IO要求也沒有那麼高(log buffer寫到檔案系統中，一般隻是從log buffer的記憶體轉移的檔案系統的記憶體緩存中，對底層IO沒有壓力)。

• 避免主鍵更新，因為會帶來大量的資料移動。

MyISAM存儲引擎：

特點

• 這是MySQL早期（5.5之前）預設的存儲機制，對事物支援不太好。
• 基于傳統的ISAM類型。

• ISAM（ indexed sequential access method）：有索引的順序通路方法，是存儲記錄和檔案的标準方法。

• 不支援事務，不支援外鍵限制，但支援全文索引，這可極大地優化LIKE查詢的效率。
• 表級鎖定（更新時鎖定整個表）：其鎖定機制是表級索引，這雖然可以讓鎖定的實作成本很小但是也同時大大降低了其并發性能。MyISAM不支援行級鎖，隻支援并發插入的表鎖，主要用于高負載查詢。
• 讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀取本身并不會阻塞另外的讀取。
• 不緩存資料，隻緩存索引：MyISAM可以通過key_buffer緩存以大大提高通路性能、減少磁盤I/O，但是這個緩存去隻會緩存索引，而不會緩存資料。
• 讀取速度較快，占用資源相對較少。
• 并發量較小，不适合大量UPDATE。
• MyISAM适用的生産業務場景：如果表主要用于插入新紀錄和讀出新紀錄，那麼選擇MyISAM存儲引擎能實作處理的高效率。如果應用的完整性和并發性要求很低，那麼也可以選擇MyISAM存儲引擎。它是在Web、資料倉庫和其他應用環境下最常用的存儲引擎。單一對資料庫的操作都可以使用MyISAM，所謂單一就是盡量純讀或純寫（insert，update，delete）等。生産建議：沒有特别需求時，一律用InnoDB。

• 不需要事務支援的業務，一般為讀資料比較多的網站應用。
• 并發相對較低的業務。
• 資料修改相對較少的業務。
• 以讀為主的業務。
• 對資料一緻性要求不是非常高的業務。
• 中小型網站的部分業務

MyISAM引擎調優精要

• 盡量使用索引，優先使用MySQL緩存機制。
• 調整讀寫優先級，根據實際需要確定重要操作更優先。
• 啟用延遲插入改善大批量寫入性能（降低寫入頻率，盡可能多條資料一次性寫入）。
• 盡量順序操作讓insert資料都寫入到尾部，減少阻塞。
• 分解大的操作，降低單個操作的阻塞時間。
• 降低并發數，某些高并發場景通過應用進行排隊機制。
• 對于相對靜态的資料，充分利用Query Cache可以極大地提高通路效率。
• MyISAM的count隻有在全表掃描的時候特别高效，帶有其他條件的count都需要進行實際的資料通路。
• 把主從同步的主庫使用InnoDB，從庫使用MyISAM引擎。