談?wù)凪ySQL的存儲(chǔ)引擎

微信掃1掃關(guān)注我的公眾號(hào)或搜索添加“MySQL技術(shù)的學(xué)習(xí)分享”，可以更快速更實(shí)時(shí)地獲得我的最新文章。

MySQL的存儲(chǔ)引擎是MySQL體系架構(gòu)中的重要組成部份，也是MySQL體系結(jié)構(gòu)的核心，插件式的存儲(chǔ)引擎更是它區(qū)分于其它數(shù)據(jù)庫(kù)的重要特點(diǎn)。它處于MySQL體系架構(gòu)中Server端底層，是底層物理結(jié)構(gòu)的實(shí)現(xiàn)，用于將數(shù)據(jù)以各種不同的技術(shù)方式存儲(chǔ)到文件或內(nèi)存中，不同的存儲(chǔ)引擎具有不同的存儲(chǔ)機(jī)制、索引技能和鎖定水平。常見(jiàn)的MySQL存儲(chǔ)引擎有InnoDB、MyISAM、Memory、Archive等等，它們具有各自的特點(diǎn)，我們可以根據(jù)不同的具體利用來(lái)建立對(duì)應(yīng)的存儲(chǔ)引擎表。

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

（1）  事務(wù)

   事務(wù)是1組原子性的SQL語(yǔ)句或說(shuō)是1個(gè)獨(dú)立的工作單元，如果數(shù)據(jù)庫(kù)引擎能夠成功對(duì)數(shù)據(jù)庫(kù)利用這組SQL語(yǔ)句，那末就履行，如果其中有任何1條語(yǔ)句由于崩潰或其它緣由沒(méi)法履行，那末所有的語(yǔ)句都不會(huì)履行。也就是說(shuō)，事務(wù)內(nèi)的語(yǔ)句，要末全部履行成功，要末全部履行失敗。

舉個(gè)銀行利用的典型例子：

   假定銀行的數(shù)據(jù)庫(kù)有兩張表：支票表和儲(chǔ)蓄表，現(xiàn)在某個(gè)客戶A要從其支票賬戶轉(zhuǎn)移2000元到其儲(chǔ)蓄賬戶，那末最少需求3個(gè)步驟：

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

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

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

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

（2）  讀鎖和寫鎖

   不管什么時(shí)候，只要有多個(gè)SQL需要同1時(shí)刻修改數(shù)據(jù)，都會(huì)產(chǎn)生并發(fā)控制的問(wèn)題。

   假定1個(gè)公共郵箱，用戶A正在讀取郵箱，同時(shí)，用戶B正在刪除郵箱中的某個(gè)郵件，會(huì)產(chǎn)生甚么結(jié)果呢？客戶A可能讀取時(shí)會(huì)報(bào)錯(cuò)退出，也可能會(huì)讀取到不1致的郵箱數(shù)據(jù)。如果把郵箱當(dāng)作數(shù)據(jù)庫(kù)中的1張表，可見(jiàn)其存在一樣的問(wèn)題。

    解決這類經(jīng)典問(wèn)題的方法就是并發(fā)控制，即在處理并發(fā)讀或?qū)憰r(shí)，可以通過(guò)實(shí)現(xiàn)1個(gè)由兩種類型的鎖組成的鎖系統(tǒng)來(lái)解決問(wèn)題。這兩種鎖就是同享鎖和排他鎖，也叫讀鎖和寫鎖。

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

（3）  行鎖和表鎖

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

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

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

理解了上面幾個(gè)概念，我們就能夠很好地分辨不同存儲(chǔ)引擎之間的區(qū)分了。

1. InnoDB存儲(chǔ)引擎

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

InnoDB存儲(chǔ)引擎支持事務(wù)、支持行鎖、支持非鎖定讀、支持外鍵。

如非特別緣由，利用建表時(shí)都可以首選斟酌使用InnoDB。InnoDB也是1個(gè)非常好的值得花時(shí)間去深入學(xué)習(xí)的存儲(chǔ)引擎，后續(xù)計(jì)劃專題研究這個(gè)存儲(chǔ)引擎，這里就暫不贅述其詳細(xì)內(nèi)容了。

2. MyISAM存儲(chǔ)引擎

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

MyISAM不支持事務(wù)，不支持行級(jí)鎖，支持表鎖，支持全文索引，最大的缺點(diǎn)是崩潰后沒(méi)法安全恢復(fù)。

MyISAM因設(shè)計(jì)簡(jiǎn)單，數(shù)據(jù)以緊密格式存儲(chǔ)，所以某些場(chǎng)景下性能很好，但是它的表鎖又帶來(lái)了性能問(wèn)題，如果你發(fā)現(xiàn)所有的查詢都長(zhǎng)時(shí)間處于“Locked”狀態(tài)，表鎖就是罪魁罪魁了。

因此，對(duì)只讀數(shù)據(jù)，或表比較小，可以忍耐修復(fù)操作的可以仍然使

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

| 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存儲(chǔ)引擎

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

|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存儲(chǔ)引擎

   Archive存儲(chǔ)引擎置只支持INSERT和SELECT操作，支持行鎖，但本身其實(shí)不是事務(wù)安全的存儲(chǔ)引擎，其最大的優(yōu)點(diǎn)是其具有較好的緊縮比，緊縮比1般可到達(dá)1:10，可以將一樣的數(shù)據(jù)以更小的磁盤空間占用來(lái)存儲(chǔ)。

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

  以某個(gè)庫(kù)里的有2.5億條記錄的歷史表為例：

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

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

| TABLE_ROWS |

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

|  251755162 |

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

1 row in set (0.01 sec)

       本來(lái)其默許為InnoDB存儲(chǔ)引擎時(shí)，該表大小為12G。

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)