談談MySQL的存儲引擎

微信掃1掃關注我的公眾號或搜索添加“MySQL技術的學習分享”，可以更快速更實時地獲得我的最新文章。

MySQL的存儲引擎是MySQL體系架構中的重要組成部份，也是MySQL體系結構的核心，插件式的存儲引擎更是它區分于其它數據庫的重要特點。它處于MySQL體系架構中Server端底層，是底層物理結構的實現，用于將數據以各種不同的技術方式存儲到文件或內存中，不同的存儲引擎具有不同的存儲機制、索引技能和鎖定水平。常見的MySQL存儲引擎有InnoDB、MyISAM、Memory、Archive等等，它們具有各自的特點，我們可以根據不同的具體利用來建立對應的存儲引擎表。

在談不同的存儲引擎之前，我們需要先理解幾個基本概念：

（1）  事務

   事務是1組原子性的SQL語句或說是1個獨立的工作單元，如果數據庫引擎能夠成功對數據庫利用這組SQL語句，那末就履行，如果其中有任何1條語句由于崩潰或其它緣由沒法履行，那末所有的語句都不會履行。也就是說，事務內的語句，要末全部履行成功，要末全部履行失敗。

舉個銀行利用的典型例子：

   假定銀行的數據庫有兩張表：支票表和儲蓄表，現在某個客戶A要從其支票賬戶轉移2000元到其儲蓄賬戶，那末最少需求3個步驟：

a.檢查A的支票賬戶余額高于2000元；

b.從A的支票賬戶余額中減去2000元；

c.在A的儲蓄賬戶余額中增加2000元。

    這3個步驟必須要打包在1個事務中，任何1個步驟失敗，則必須要回滾所有的步驟，否則A作為銀行的客戶便可能要莫名損失2000元，就出問題了。這就是1個典型的事務，這個事務是不可分割的最小工作單元，全部事務中的所有操作要末全部提交成功，要末全部失敗回滾，不可能只履行其中1部份，這也是事務的原子性特點。

（2）  讀鎖和寫鎖

   不管什么時候，只要有多個SQL需要同1時刻修改數據，都會產生并發控制的問題。

   假定1個公共郵箱，用戶A正在讀取郵箱，同時，用戶B正在刪除郵箱中的某個郵件，會產生甚么結果呢？客戶A可能讀取時會報錯退出，也可能會讀取到不1致的郵箱數據。如果把郵箱當作數據庫中的1張表，可見其存在一樣的問題。

    解決這類經典問題的方法就是并發控制，即在處理并發讀或寫時，可以通過實現1個由兩種類型的鎖組成的鎖系統來解決問題。這兩種鎖就是同享鎖和排他鎖，也叫讀鎖和寫鎖。

     讀鎖是同享的，即相互不阻塞的，多個客戶在同1時刻可以讀取同1資源，互不干擾。寫鎖是排他的，即1個寫鎖會阻塞其它的寫鎖和讀鎖，只有這樣，才能確保給定時間內，只有1個用戶能履行寫入，避免其它用戶讀取正在寫入的同1資源。寫鎖優先級高于讀鎖。

（3）  行鎖和表鎖

    實際數據庫系統中每時每刻都在產生鎖定，鎖也是有粒度的，提高同享資源并發行的方式就是讓鎖更有選擇性，盡可能只鎖定需要修改的部份數據，而不是所有的資源，因此要進行精確的鎖定。但是由于加鎖也需要消耗資源，包括取得鎖、檢查鎖是不是消除、釋放鎖等，都會增加系統的開消。所謂的鎖策略就是要在鎖的開消和數據的安全性之間尋求平衡，這類平衡也會影響性能。  

    每種MySQL存儲引擎都有自己的鎖策略和鎖粒度，最經常使用的兩種重要的鎖策略分別是表鎖和行鎖。

    表鎖是開消最小的策略，會鎖定整張表，用戶對表做寫操作時，要先取得寫鎖，這會阻塞其它用戶對該表的所有讀寫操作。沒有寫鎖時，其它讀取的用戶才能取得讀鎖，讀鎖之間是不相互阻塞的。行鎖可以最大成都支持并發處理，但也帶來了最大的鎖開消，它只對指定的記錄加鎖，其它進程還是可以對同1表中的其它記錄進行操作。表級鎖速度快，但沖突多，行級鎖沖突少，但速度慢。      

理解了上面幾個概念，我們就能夠很好地分辨不同存儲引擎之間的區分了。

1. InnoDB存儲引擎

MySQL存儲引擎可以分為官方存儲引擎和第3方存儲引擎，InnoDB就是強大的第3方存儲引擎，具有較好的性能和自動崩潰恢復特性，目前利用極其廣泛，是當前MySQL存儲引擎中的主流，它在事務型存儲和非事務型存儲中都很流行。

InnoDB存儲引擎支持事務、支持行鎖、支持非鎖定讀、支持外鍵。

如非特別緣由，利用建表時都可以首選斟酌使用InnoDB。InnoDB也是1個非常好的值得花時間去深入學習的存儲引擎，后續計劃專題研究這個存儲引擎，這里就暫不贅述其詳細內容了。

2. MyISAM存儲引擎

MyISAM存儲引擎是MySQL官方提供的存儲引擎，它在InnoDB出現并完善之前是MySQL存儲引擎的主流，但目前逐步被淘汰主要由于其不支持事務，這也許源于MySQL的開發者認為不是所有的利用都需要事務，所以便存在了這類不支持事務的存儲引擎。

MyISAM不支持事務，不支持行級鎖，支持表鎖，支持全文索引，最大的缺點是崩潰后沒法安全恢復。

MyISAM因設計簡單，數據以緊密格式存儲，所以某些場景下性能很好，但是它的表鎖又帶來了性能問題，如果你發現所有的查詢都長時間處于“Locked”狀態，表鎖就是罪魁罪魁了。

因此，對只讀數據，或表比較小，可以忍耐修復操作的可以仍然使

用MyISAM，對不需要事務的利用，選擇MyISAM存儲引擎，也許可以取得更高的性能，MySQL自帶的默許的information_schema庫中就存在使用MyISAM存儲引擎的表。

| TRIGGERS | CREATETEMPORARY TABLE `TRIGGERS` (

  `TRIGGER_CATALOG` varchar(512) NOT NULLDEFAULT '',

  `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT'',

  `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT'',

  `EVENT_MANIPULATION` varchar(6) NOT NULLDEFAULT '',

  `EVENT_OBJECT_CATALOG` varchar(512) NOT NULLDEFAULT '',

  `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULLDEFAULT '',

  `EVENT_OBJECT_TABLE` varchar(64) NOT NULLDEFAULT '',

  `ACTION_ORDER` bigint(4) NOT NULL DEFAULT'0',

  `ACTION_CONDITION` longtext,

  `ACTION_STATEMENT` longtext NOT NULL,

  `ACTION_ORIENTATION` varchar(9) NOT NULLDEFAULT '',

  `ACTION_TIMING` varchar(6) NOT NULL DEFAULT'',

  `ACTION_REFERENCE_OLD_TABLE` varchar(64)DEFAULT NULL,

  `ACTION_REFERENCE_NEW_TABLE` varchar(64)DEFAULT NULL,

  `ACTION_REFERENCE_OLD_ROW` varchar(3) NOTNULL DEFAULT '',

  `ACTION_REFERENCE_NEW_ROW` varchar(3) NOTNULL DEFAULT '',

  `CREATED` datetime DEFAULT NULL,

  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',

  `DEFINER` varchar(77) NOT NULL DEFAULT '',

  `CHARACTER_SET_CLIENT` varchar(32) NOT NULLDEFAULT '',

  `COLLATION_CONNECTION` varchar(32) NOT NULLDEFAULT '',

  `DATABASE_COLLATION` varchar(32) NOT NULLDEFAULT ''

)ENGINE=MyISAM DEFAULT CHARSET=utf8 |

3. Memory存儲引擎

Memory存儲引擎將表中數據放在內存中，因此速度非常快，但因其支持表鎖，所以并發性能較差，最糟的是這個存儲引擎在數據庫重啟或崩潰以后表中的數據將全部丟失，它只適用于存儲臨時數據的臨時表，MySQL中1般使用這個存儲引擎來寄存查詢的中間結果集，如MySQL自帶的默許的information_schema庫中就存在較多使用Memory存儲引擎的表。

|TABLES | CREATE TEMPORARY TABLE `TABLES` (

  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',

  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',

  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',

  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',

  `ENGINE` varchar(64) DEFAULT NULL,

  `VERSION` bigint(21) unsigned DEFAULT NULL,

  `ROW_FORMAT` varchar(10) DEFAULT NULL,

  `TABLE_ROWS` bigint(21) unsigned DEFAULTNULL,

  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULTNULL,

  `DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,

  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,

  `INDEX_LENGTH` bigint(21) unsigned DEFAULTNULL,

  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,

  `AUTO_INCREMENT` bigint(21) unsigned DEFAULTNULL,

  `CREATE_TIME` datetime DEFAULT NULL,

  `UPDATE_TIME` datetime DEFAULT NULL,

  `CHECK_TIME` datetime DEFAULT NULL,

  `TABLE_COLLATION` varchar(32) DEFAULT NULL,

  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,

  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,

  `TABLE_COMMENT` varchar(2048) NOT NULLDEFAULT ''

) ENGINE=MEMORY DEFAULT CHARSET=utf8| 

4. Archive存儲引擎

   Archive存儲引擎置只支持INSERT和SELECT操作，支持行鎖，但本身其實不是事務安全的存儲引擎，其最大的優點是其具有較好的緊縮比，緊縮比1般可到達1:10，可以將一樣的數據以更小的磁盤空間占用來存儲。

   Archive存儲引擎非常合適存儲歸檔數據，如歷史數據、日志信息數據等等，這類數據常常數據量非常大，并且基本只有INSERT和SELECT操作，使用這個存儲引擎可以非常節儉磁盤空間。

  以某個庫里的有2.5億條記錄的歷史表為例：

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';

+------------+

| TABLE_ROWS |

+------------+

|  251755162 |

+------------+

1 row in set (0.01 sec)

       本來其默許為InnoDB存儲引擎時，該表大小為12G。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈