從我的個人博客訪問

MySQL的特性是它的存儲引擎架構(gòu)。

這種設計將查詢處理(Query Processing)及其他系統(tǒng)任務(Server Task) 和數(shù)據(jù)的存儲/提取相分離辩涝。

在使用時根據(jù)具體情況選擇存儲引擎衰腌。

MySQL邏輯架構(gòu)

MySQL服務器邏輯架構(gòu)圖

由上圖可以主要分為三層：

第一層架構(gòu) 主要功能：連接處理新蟆，授權認證，安全等右蕊。

第二層架構(gòu) 大多數(shù)MySQL的核心服務都在這一層：查詢解析琼稻、分析、優(yōu)化饶囚、緩存以及所有的內(nèi)置函數(shù)(eg: 日期帕翻、時間、數(shù)學萝风、加密等函數(shù))嘀掸，所有跨存儲引擎的功能都在這一層實現(xiàn)：存儲過程、觸發(fā)器规惰、視圖等横殴。

第三層架構(gòu) 包含了存儲引擎 負責MySQL中數(shù)據(jù)的存儲和提取。 服務器通過API與存儲引擎進行通信卿拴，API屏蔽了不同引擎之間的差異衫仑。

連接管理與安全性

每個客戶端連接都會在服務器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中執(zhí)行堕花。

服務器會緩存線程或者使用線程池技術文狱，不需要為每個連接創(chuàng)建一個線程，因而使用較少的線程來服務大量的連接缘挽。

當客戶連接服務器的時候瞄崇，服務器會對其進行認證呻粹，基于用戶名、密碼苏研、主機信息等浊。

客戶連接上服務器，執(zhí)行某個操作時摹蘑，服務器會對其進行權限判斷筹燕，判斷該用戶是否有權限進行該項操作。

優(yōu)化與執(zhí)行

MySQL會解析查詢,并創(chuàng)建解析樹衅鹿，然后對其進行各種優(yōu)化撒踪，包括重寫查詢、決定表的讀取順序大渤，以及選取合適的索引等制妄。

用戶可以使用特殊的關鍵字提示(hint)優(yōu)化器，影響其決策過程耕捞。

也可以請求優(yōu)化器解釋(explain)優(yōu)化過程，讓客戶知道服務器如何進行優(yōu)化俺抽。

以便用戶修改查詢和schema纬霞、修改相關配置驱显，使之高效運行。

優(yōu)化器并不關心表使用的是什么存儲引擎埃疫，但存儲引擎對于優(yōu)化查詢是有影響的。

對于SELECT語句栓霜，在解析查詢之前翠桦，服務器會先檢查查詢緩存(Query Cache),如果能找到對應的查詢，服務器就會直接返回查詢緩存中的結(jié)果集胳蛮。

并發(fā)控制

無論何時销凑，只要有多個查詢需要在同一時刻修改數(shù)據(jù)，就會產(chǎn)生并發(fā)控制問題仅炊。

MySQL的并發(fā)控制包含兩個層面： 服務器層和存儲引擎層

MySQL的并發(fā)控制主要靠鎖機制解決斗幼。

讀寫鎖

讀鎖(read lock) 也叫 共享鎖(shared lock)

寫鎖(write lock) 也叫 排他鎖(exclusive lock)

讀鎖是共享的，是相互不阻塞的抚垄。多個客戶可以同時讀取同一個資源而不受干擾蜕窿。

寫鎖是排他的谋逻，一個寫鎖會阻塞其他的讀鎖和寫鎖。

在實際的數(shù)據(jù)庫系統(tǒng)中桐经，每時每刻都會發(fā)生鎖定毁兆。大多數(shù)時候，MySQL鎖的內(nèi)部管理都是透明的阴挣。

鎖粒度

加鎖會消耗資源气堕。鎖的各種操作，包括獲得鎖屯吊，檢查所是否解除送巡，釋放鎖，都會增加系統(tǒng)開銷盒卸。

鎖的粒度越小骗爆，就能對修改的數(shù)據(jù)片進行更精確的鎖定，系統(tǒng)的并發(fā)程度就越高蔽介，但是系統(tǒng)的開銷就越大摘投。

表鎖(table lock)

表鎖是MySQL中最基本的一種鎖策略，并且是開銷最小的策略,它會鎖定整張表虹蓄。

當一個用戶在對表進行寫操作(插入犀呼、刪除、更新等)前薇组，需要先獲取寫鎖外臂，這會阻塞其他用戶對該表的所有讀寫操作细移。

只有在沒有寫鎖時恩袱，其他用戶才能獲得讀鎖旬牲。

盡管存儲引擎可以管理自己的鎖虫几，MySQL本身還是會使用各種有效的表鎖來實現(xiàn)不同的目的赘艳。

比如蕾管，服務器會為諸如ALTER TABLE之類的語句使用表鎖娇掏，而忽略存儲引擎的鎖機制勋眯。

行級鎖(row lock)

行級鎖可以最大程度的支持并發(fā)操作(同時也帶來了最大的鎖開銷)

行級鎖只在存儲引擎層次實現(xiàn)，而在MySQL服務器層沒有實現(xiàn)孽江，服務器層完全不了解存儲引擎中的鎖實現(xiàn)岗屏。

所有存儲引擎都以自己的方式實現(xiàn)了鎖機制漱办。

事務

事務就是一組原子性的SQL查詢，或者說一個獨立的工作單元暇屋。

事務內(nèi)的語句咐刨，要么全部成功扬霜，要么全部失敗著瓶。

事務的ACID特性材原，表示原子性(atomicity)华糖、一致性(consistency)瘟裸、隔離性(isolation)话告、永久性(durability)

用戶可以根據(jù)業(yè)務是否需要事務處理沙郭，來選擇合適的存儲引擎病线。

對于不需要事務的查詢類應用鲤嫡，可以選擇一個非事務型的存儲引擎暖眼。

隔離級別

在SQL標準中定義了4種隔離級別诫肠，每一種級別都規(guī)定了一個事務中所做的修改栋豫，哪些在事務內(nèi)和事務間是可見的丧鸯，哪些是不可見的骡送。

較低級別的隔離通乘猓可以執(zhí)行更高的并發(fā)派敷，系統(tǒng)的開銷也更低篮愉。

READ UNCOMMITTED (未提交讀)

在這個級別试躏，事務中的修改设褐，即便沒有提交助析，對其他事務也是可見的外冀。事務可以讀取未提交的數(shù)據(jù)雪隧，這也被成為臟讀（Dirty Read）

這個級別會導致很多問題，性能也不會比別的級別好很多藕畔，也沒有別的級別的優(yōu)點劫流，實際應用中很少使用祠汇。

READ COMMITTED (提交讀)

大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別都是這個（mysql不是)可很。

在這個級別，一個事務開始時苇本，只能‘看到’已經(jīng)提交的事務所作的修改瓣窄。即：一個事務從開始知道提交之前俺夕，所作的修改對其他事務都是不可見的劝贸。

場景：

在一個事務內(nèi)映九，多次讀同一個數(shù)據(jù)件甥。在這個事務還沒有結(jié)束時言缤，另一個事務也訪問該同一數(shù)據(jù)管挟。
那么僻孝，在第一個事務的兩次讀數(shù)據(jù)之間。
由于第二個事務的修改您单，那么第一個事務讀到的數(shù)據(jù)可能不一樣虐秦。
這樣就發(fā)生了在一個事務內(nèi)兩次讀到的數(shù)據(jù)是不一樣的悦陋。

因而這個級別有時候也叫做不可重復讀

REPEATABLE READ (可重復讀)

這個級別是MySQL默認的事務隔離級別

這個級別解決了臟讀的問題俺驶，保證在同一個事務中的多次讀取同樣記錄的結(jié)果是一致的暮现，但是該級別無法解決幻讀(Phantom Read)的問題

所謂幻讀：就是當一個事務在讀取某個范圍的記錄時栖袋，另一個事務又在該范圍插入了新的記錄抚太，之前的事務再次讀取該范圍的數(shù)據(jù)時凭舶，會產(chǎn)生幻行(Phantom Row)

SERIALIZABLE (可串行化)

這個級別是最高的隔離級別帅霜，它通過強制事務串行執(zhí)行身冀，避免了前面的幻讀的的問題。

其他的隔離級別中珍促，各個事務還是有一定程度的并發(fā)執(zhí)行猪叙。

可串行化會在讀取的每一行數(shù)據(jù)上都加鎖穴翩，可能會導致大量的超時和鎖爭用的問題芒帕，實際中很少使用背蟆。

死鎖

死鎖是指兩個或者多個事務在同一資源上相互占用冠场，并請求鎖定對方占用的資源碴裙，從而導致惡性循環(huán)的現(xiàn)象点额。

當多個事務試圖以不同的順序鎖定資源時还棱，就可能產(chǎn)生死鎖珍手。

多個事務同時鎖定同一個資源時琳要，也會產(chǎn)生死鎖稚补。

例如下面兩個事務同時處理 StockPrice表：

事務1

    START TRANSACTION;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4;
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 5;
    COMMIT;

事務2

    START TRANSACTION;
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 5;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4;
    COMMIT;

如果剛好兩個事務都執(zhí)行了第一條UPDATE語句课幕，更新了一條數(shù)據(jù)乍惊，同時也鎖定了該行數(shù)據(jù)。

然后每個事務都嘗試去執(zhí)行第二條UPDATE語句撬碟，發(fā)現(xiàn)已經(jīng)被對方鎖定小作，然后都等待對方釋放鎖顾稀，又都同時持有對方需要的鎖静秆，則陷入死循環(huán)抚笔。除非有外部接入才可能解除死鎖殊橙。

為了解決這個問題狱从，數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時機制季研。

死鎖發(fā)生后与涡，只有部分或者完全回滾其中一個事務驼卖，才能打破死鎖。

InnoDB處理死鎖的辦法是：將持有最少行級排他鎖的事務進行回滾儒飒。

事務日志

使用事務日志桩了，存儲引擎在修改表的數(shù)據(jù)時只需要修改其內(nèi)存拷貝井誉，再把該修改行為記錄到持久在硬盤的事務日志中颗圣，不用每次都把修改的數(shù)據(jù)持久到磁盤。

事務采用追加的方式奔则，因此寫日志是對磁盤上一小塊位置的順序IO易茬，速度較快及老。

事務日志持久化之后，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢的刷回到磁盤食铐。如果系統(tǒng)崩潰虐呻，存儲引擎在重啟時可以根據(jù)事務日志恢復數(shù)據(jù)铃慷。

這種稱之為預寫式日志(Write-Ahead Logging),修改數(shù)據(jù)需要寫兩次磁盤蜕该。

MySQL中的事務

MySQL提供了兩種事務型的存儲引擎：InnoDB 和 NDB Cluster堂淡。

自動提交 (AUTOCOMMIT)

MySQL默認采用自動提交模式绢淀。也就是說，如果不是顯示的開始一個事務覆履，則每個查詢都被當做一個事務執(zhí)行操作硝全。

在當前連接中伟众，可以通過設置 AUTOCOMMIT 變量來啟用或者禁用自動提交模式召廷。

    SHOW VARIABLES LIKE 'AUTOCOMMIT';

對于非事務型的表，相當于一直處于AUTOCOMMIT的狀態(tài)治泥。

MySQL可以通過執(zhí)行 SET TRANSACTION ISOLATION LEVEL XXX 命令來設置隔離級別遮精，新的隔離級別會在下一個事務開始時生效仑鸥。

可以在配置文件中設置整個數(shù)據(jù)庫的隔離級別眼俊，也可以只改變當前會話的隔離級別：

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMIT 

在事務中混合使用存儲引擎

MySQL服務器層不管理事務疮胖，事務是由下層的存儲引擎實現(xiàn)的闷板。所以在同一個事務中使用多種存儲引擎是不可靠的遮晚。

如果在事務中混合使用了事務型和非事務型存儲引擎县遣，在正常提交的情況下不會有什么問題。

但是如果該事務需要回滾其兴，非事務型的表上的變更無法撤銷元旬。

在非事務型的表上執(zhí)行事務相關操作的時候守问，MySQL通常不會發(fā)出提醒耗帕，也不會報錯，只有在回滾的時候才會發(fā)一個警告：‘某些非事務型的表上的變更無法被回滾’徙赢。

隱式和顯示鎖定

InnoDB會根據(jù)隔離級別在需要的時候自動加鎖狡赐，這種鎖定叫做隱式鎖定枕屉。

另外，InnoDB也支持通過特定的語句進行顯示鎖定西潘，這些語句不屬于SQL規(guī)范喷市。

    SELECT ... LOCK IN SHARE MODE
    SELECT ... FOR UPDATE

MySQL也支持 LOCK TABLES 和 UNLOCK TABLES 語句品姓，這是在服務器層實現(xiàn)的腹备，與存儲引擎無關植酥，不能替代事務處理友驮。

但是建議任何時候都不要顯示的執(zhí)行 LOCK TABLES喊儡。

多版本并發(fā)控制

MySQL的大多數(shù)事務型存儲引擎實現(xiàn)的都不是簡單的行級鎖艾猜〈以撸基于提升并發(fā)性能的考慮算柳，一般都實現(xiàn)了 多版本并發(fā)控制（MVCC）姓言。

不僅是MySQL,包括Oracle、PostgreSQL等其他數(shù)據(jù)庫系統(tǒng)都實現(xiàn)了MVCC,但實現(xiàn)的機制不盡相同猪杭，因為MVCC沒有統(tǒng)一的實現(xiàn)標準皂吮。

MVCC可以認為是行級鎖的一個變種蜂筹，在很多情況下避免了加鎖操作艺挪，因此開銷更低闺属。

雖然實現(xiàn)機制不同，但大都實現(xiàn)了非阻塞的讀操作亚皂，寫操作也只鎖定必要的行狞谱。

Mysql的存儲引擎

InnoDB 存儲引擎

InnoDB存儲引擎是MySQL自5.5版本以來的默認事務型引擎跟衅，也是最重要伶跷、使用最廣泛的存儲引擎叭莫。

它被設計用來處理大量的短期事務雇初，在非事務行的存儲的需求中也很流行靖诗。

除非有非常特別的原因需要使用其他的存儲引擎刊橘，否則應該優(yōu)先考慮使用InnoDB引擎。

MyISAM 存儲引擎

在MySQL5.1及之前的版本伤为，MyISAM是默認的存儲引擎咒循，MyISAM不支持事務和行級鎖。

其他存儲引擎

Archive引擎

Archive存儲引擎只支持INSERT和SELECT操作

Backhole引擎

Backhole沒有實現(xiàn)任何的存儲機制绞愚，他會丟棄所有插入的數(shù)據(jù)叙甸，不做保存，服務器會記錄Backhole表的日志位衩。

CSV引擎

CSV引擎可以將普通的csv文件作為MySQL的表來處理裆蒸，但是這種引表不支持索引。

Memory引擎

Memory引擎數(shù)據(jù)保存在內(nèi)存中糖驴，查詢速度快僚祷，但是重啟之后數(shù)據(jù)丟失，表結(jié)構(gòu)還會保留贮缕。

NDB集群引擎

MySQL服務器，NDB集群存儲引擎，以及分布式的、share-nothing的雏搂、容災的线椰、高可用的NDB數(shù)據(jù)庫的組合烦绳，被稱為MySQL集群(MySQL Cluster)享扔。

第三方存儲引擎

MySQL從2007年開始提供插件式的存儲引擎API,從此產(chǎn)生了許多為不同目的而設計的存儲引擎

主要有： OLTP類引擎于个、 面向列的存儲引擎羽氮、社區(qū)存儲引擎 等尤筐。

選擇合適的存儲引擎

大多數(shù)情況下，InnoDB都是正確的選擇。

只有遇到一些比較特殊的需求時，才會考慮用其他引擎。

Mysql時間線

1995 MySQL AB公司創(chuàng)建蜓氨。

2008年 MySQL AB公司被SUN收購

2010年 SUN公司被Oracle收購

隨著MySQL被Oracle收購，許多公司開始尋找替代品。

倒向MariaDB: 谷歌(2013年9月) RedHat(2013年6月) 維基百科(2013年4月)

倒向PostreSQL: 蘋果(2011年)