MySQL儲存引擎選擇

標籤：

MySQL 的儲存引擎可能是所有關係型資料庫產品中最具有特色的了，不僅可以同時使用多種儲存引擎，而且每種儲存引擎和MySQL之間使用外掛程式方式這種非常松的耦合關係。
由於各儲存引擎功能特性差異較大，這篇文章主要是介紹如何來選擇合適的儲存引擎來應對不同的業務情境。
MyISAM
1.特性
　　不支援事務：MyISAM儲存引擎不支援事務，所以對事務有要求的業務情境不能使用
　　表級鎖定：其鎖定機制是表級索引，這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其並發效能
　　讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀
　　只會緩衝索引：MyISAM可以通過key_buffer緩衝以大大提高訪問效能減少磁碟IO，但是這個緩衝區只會緩衝索引，而不會快取資料
2.適用情境
　　不需要事務支援(不支援)
　　並發相對較低(鎖定機制問題)
　　資料修改相對較少(阻塞問題)
　　以讀為主
　　資料一致性要求不是非常高
3.最佳實務
　　盡量索引(緩衝機制)
　　調整讀寫優先順序，根據實際需求確保重要操作更優先
　　啟用延遲插入改善大批量寫入效能
　　盡量順序操作讓insert資料都寫入到尾部，減少阻塞
　　分解大的操作，降低單個操作的阻塞時間
　　降低並發數，某些高並發情境通過應用來進行排隊機制
　　對於相對靜態資料，充分利用Query Cache可以極大的提高訪問效率
　　MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的資料訪問

InnoDB
1.特性
　　具有較好的事務支援：支援4個交易隔離等級，支援多版本讀
　　行級鎖定：通過索引實現，全表掃描仍然會是表鎖，注意間隙鎖的影響
　　讀寫阻塞與交易隔離等級相關
　　具有非常高效的緩衝特性：能緩衝索引，也能快取資料
　　整個表和主鍵以Cluster方式儲存，組成一顆平衡樹
　　所有Secondary Index都會儲存主鍵資訊
2.適用情境
　　需要事務支援(具有較好的事務特性)
　　行級鎖定對高並發有很好的適應能力，但需要確保查詢是通過索引完成
　　資料更新較為頻繁的情境
　　資料一致性要求較高
　　硬體裝置記憶體較大，可以利用InnoDB較好的緩衝能力來提高記憶體利用率，儘可能減少磁碟 IO
3.最佳實務
　　主鍵儘可能小，避免給Secondary index帶來過大的空間負擔
　　避免全表掃描，因為會使用表鎖
　　儘可能緩衝所有的索引和資料，提高響應速度
　　在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動認可
　　合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性
　　避免主鍵更新，因為這會帶來大量的資料移動
 
NDBCluster
1.特性
　　分布式：分布式儲存引擎，可以由多個NDBCluster儲存引擎組成叢集分別存放整體資料的一部分
　　支援事務：和Innodb一樣，支援事務
　　可與mysqld不在一台主機：可以和mysqld分開存在於獨立的主機上，然後通過網路和mysqld通訊互動
　　記憶體需求量巨大：新版本索引以及被索引的資料必須存放在記憶體中，老版本所有資料和索引必須存在與記憶體中
2.適用情境
　　具有非常高的並發需求
　　對單個請求的響應並不是非常的critical
　　查詢簡單，過濾條件較為固定，每次請求資料量較少，又不希望自己進行水平Sharding
3.最佳實務
　　儘可能讓查詢簡單，避免資料的跨節點傳輸
　　儘可能滿足SQL節點的計算效能，大一點的叢集SQL節點會明顯多餘Data節點
　　在各節點之間儘可能使用萬兆網路環境互聯，以減少資料在網路層傳輸過程中的延時
　　註：以上三個儲存引擎是目前相對主流的儲存引擎，還有其他類似如：Memory，Merge，CSV，Archive等儲存引擎的使用情境都相對較少，這裡就不一一分析了，如果有朋友感興趣，後面再補充吧。

原文地址：http://isky000.com/database/mysql-performance-tuning-storage-engine

MySQL儲存引擎選擇