MySQL 效能優化之儲存引擎選擇

離上一篇文章已經有很長時間沒有更新這個MySQL資料庫效能優化專題了，時間太緊加上人之惰性，今天這裡將之前就規劃好的關於儲存引擎選擇方面的內容更新出來，希望對大家有所幫助吧

MySQL 的儲存引擎可能是所有關係型資料庫產品中最具有特色的了，不僅可以同時使用多種儲存引擎，而且每種儲存引擎和MySQL之間使用外掛方式這種非常鬆的耦合關係。

由於各儲存引擎功能特性差異較大，這篇文章主要是介紹如何來選擇合適的儲存引擎來應對不同的業務場景。

• MyISAM
  • 特性
    1. 不支援事務：MyISAM儲存引擎不支援事務，所以對事務有要求的業務場景不能使用
    2. 表級鎖定：其鎖定機制是表級索引，這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其併發效能
    3. 讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀
    4. 只會快取索引：MyISAM可以通過key_buffer快取以大大提高訪問效能減少磁碟IO，但是這個快取區只會快取索引，而不會快取資料
  • 適用場景
    1. 不需要事務支援（不支援）
    2. 併發相對較低（鎖定機制問題）
    3. 資料修改相對較少（阻塞問題）
    4. 以讀為主
    5. 資料一致性要求不是非常高
  • 最佳實踐
    1. 儘量索引（快取機制）
    2. 調整讀寫優先順序，根據實際需求確保重要操作更優先
    3. 啟用延遲插入改善大批量寫入效能
    4. 儘量順序操作讓insert資料都寫入到尾部，減少阻塞
    5. 分解大的操作，降低單個操作的阻塞時間
    6. 降低併發數，某些高併發場景通過應用來進行排隊機制
    7. 對於相對靜態的資料，充分利用Query Cache可以極大的提高訪問效率
    8. MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的資料訪問
• InnoDB
  • 特性
    1. 具有較好的事務支援：支援4個事務隔離級別，支援多版本讀
    2. 行級鎖定：通過索引實現，全表掃描仍然會是表鎖，注意間隙鎖的影響
    3. 讀寫阻塞與事務隔離級別相關
    4. 具有非常高效的快取特性：能快取索引，也能快取資料
    5. 整個表和主鍵以Cluster方式儲存，組成一顆平衡樹
    6. 所有Secondary Index都會儲存主鍵資訊
  • 適用場景
    1. 需要事務支援（具有較好的事務特性）
    2. 行級鎖定對高併發有很好的適應能力，但需要確保查詢是通過索引完成
    3. 資料更新較為頻繁的場景
    4. 資料一致性要求較高
    5. 硬體裝置記憶體較大，可以利用InnoDB較好的快取能力來提高記憶體利用率，儘可能減少磁碟 IO
  • 最佳實踐
    1. 主鍵儘可能小，避免給Secondary index帶來過大的空間負擔
    2. 避免全表掃描，因為會使用表鎖
    3. 儘可能快取所有的索引和資料，提高響應速度
    4. 在大批量小插入的時候，儘量自己控制事務而不要使用autocommit自動提交
    5. 合理設定innodb_flush_log_at_trx_commit引數值，不要過度追求安全性
    6. 避免主鍵更新，因為這會帶來大量的資料移動
• NDBCluster
  • 特性
    1. 分散式：分散式儲存引擎，可以由多個NDBCluster儲存引擎組成叢集分別存放整體資料的一部分
    2. 支援事務：和Innodb一樣，支援事務
    3. 可與mysqld不在一臺主機：可以和mysqld分開存在於獨立的主機上，然後通過網路和mysqld通訊互動
    4. 記憶體需求量巨大：新版本索引以及被索引的資料必須存放在記憶體中，老版本所有資料和索引必須存在與記憶體中
  • 適用場景
    1. 具有非常高的併發需求
    2. 對單個請求的響應並不是非常的critical
    3. 查詢簡單，過濾條件較為固定，每次請求資料量較少，又不希望自己進行水平Sharding
  • 最佳實踐
    1. 儘可能讓查詢簡單，避免資料的跨節點傳輸
    2. 儘可能滿足SQL節點的計算效能，大一點的叢集SQL節點會明顯多餘Data節點
    3. 在各節點之間儘可能使用萬兆網路環境互聯，以減少資料在網路層傳輸過程中的延時

注：以上三個儲存引擎是目前相對主流的儲存引擎，還有其他類似如：Memory，Merge，CSV，Archive等儲存引擎的使用場景都相對較少，這裡就不一一分析了，如果有朋友感興趣，後面再補充吧。

作者：Sky.Jian | 可以任意轉載, 但轉載時務必以超連結形式標明文章原始出處 和 作者資訊 及 版權宣告 
連結：http://isky000.com/database/mysql-performance-tuning-storage-engine

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1624669/，如需轉載，請註明出處，否則將追究法律責任。