【建議收藏】MySQL 三萬字精華總結(jié) —查詢和事務(wù)（三）

五、MySQL查詢

?

count(*) 和 count(1)和count(列名)區(qū)別 ? ps：這道題說法有點(diǎn)多

執(zhí)行效果上：

• count(*)包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
• count(1)包括了所有列，用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
• count(列名)只包括列名那一列，在統(tǒng)計(jì)結(jié)果的時(shí)候，會(huì)忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計(jì)數(shù)，即某個(gè)字段值為NULL時(shí)，不統(tǒng)計(jì)。

執(zhí)行效率上：

• 列名為主鍵，count(列名)會(huì)比count(1)快
• 列名不為主鍵，count(1)會(huì)比count(列名)快
• 如果表多個(gè)列并且沒有主鍵，則 count(1) 的執(zhí)行效率優(yōu)于 count(*)
• 如果有主鍵，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
• 如果表只有一個(gè)字段，則 select count(*) 最優(yōu)。

?

MySQL中 in和 exists 的區(qū)別？

• exists：exists對(duì)外表用loop逐條查詢，每次查詢都會(huì)查看exists的條件語句，當(dāng)exists里的條件語句能夠返回記錄行時(shí)（無論記錄行是的多少，只要能返回），條件就為真，返回當(dāng)前l(fā)oop到的這條記錄；反之，如果exists里的條件語句不能返回記錄行，則當(dāng)前l(fā)oop到的這條記錄被丟棄，exists的條件就像一個(gè)bool條件，當(dāng)能返回結(jié)果集則為true，不能返回結(jié)果集則為false
• in：in查詢相當(dāng)于多個(gè)or條件的疊加

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。

如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in：

?

UNION和UNION ALL的區(qū)別?

UNION和UNION ALL都是將兩個(gè)結(jié)果集合并為一個(gè)，兩個(gè)要聯(lián)合的SQL語句 字段個(gè)數(shù)必須一樣，而且字段類型要“相容”（一致）；

• UNION在進(jìn)行表連接后會(huì)篩選掉重復(fù)的數(shù)據(jù)記錄（效率較低），而UNION ALL則不會(huì)去掉重復(fù)的數(shù)據(jù)記錄；

• UNION會(huì)按照字段的順序進(jìn)行排序，而UNION ALL只是簡(jiǎn)單的將兩個(gè)結(jié)果合并就返回；

SQL執(zhí)行順序

SELECT DISTINCT FROM   JOIN   ON WHERE  GROUP BY  HAVING ORDER BY LIMIT 

FROM  <left_table>ON <join_condition><join_type> JOIN  <right_table>WHERE  <where_condition>GROUP BY  <group_by_list>HAVING <having_condition>SELECTDISTINCT <select_list>ORDER BY <order_by_condition>LIMIT <limit_number>

• 總結(jié)

?

mysql 的內(nèi)連接、左連接、右連接有什么區(qū)別？

什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？

Join圖

六、MySQL 事務(wù)

?

事務(wù)的隔離級(jí)別有哪些？MySQL的默認(rèn)隔離級(jí)別是什么？

什么是幻讀，臟讀，不可重復(fù)讀呢？

MySQL事務(wù)的四大特性以及實(shí)現(xiàn)原理

MVCC熟悉嗎，它的底層原理？

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你即需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫(kù)操作語句就構(gòu)成一個(gè)事務(wù)！

ACID — 事務(wù)基本要素

事務(wù)是由一組SQL語句組成的邏輯處理單元，具有4個(gè)屬性，通常簡(jiǎn)稱為事務(wù)的ACID屬性。

• A (Atomicity) 原子性：整個(gè)事務(wù)中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣
• C (Consistency) 一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞
• I (Isolation)隔離性：一個(gè)事務(wù)的執(zhí)行不能其它事務(wù)干擾。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)其它并發(fā)事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾
• D (Durability) 持久性：在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫(kù)所作的更改便持久的保存在數(shù)據(jù)庫(kù)之中，并不會(huì)被回滾

并發(fā)事務(wù)處理帶來的問題

• 更新丟失（Lost Update)：事務(wù)A和事務(wù)B選擇同一行，然后基于最初選定的值更新該行時(shí)，由于兩個(gè)事務(wù)都不知道彼此的存在，就會(huì)發(fā)生丟失更新問題
• 臟讀(Dirty Reads)：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
• 不可重復(fù)讀（Non-Repeatable Reads)：事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù)B在事務(wù)A多次讀取的過程中，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果不一致。
• 幻讀（Phantom Reads)：幻讀與不可重復(fù)讀類似。它發(fā)生在一個(gè)事務(wù)A讀取了幾行數(shù)據(jù)，接著另一個(gè)并發(fā)事務(wù)B插入了一些數(shù)據(jù)時(shí)。在隨后的查詢中，事務(wù)A就會(huì)發(fā)現(xiàn)多了一些原本不存在的記錄，就好像發(fā)生了幻覺一樣，所以稱為幻讀。

幻讀和不可重復(fù)讀的區(qū)別：

• 不可重復(fù)讀的重點(diǎn)是修改：在同一事務(wù)中，同樣的條件，第一次讀的數(shù)據(jù)和第二次讀的數(shù)據(jù)不一樣。（因?yàn)橹虚g有其他事務(wù)提交了修改）
• 幻讀的重點(diǎn)在于新增或者刪除：在同一事務(wù)中，同樣的條件,，第一次和第二次讀出來的記錄數(shù)不一樣。（因?yàn)橹虚g有其他事務(wù)提交了插入/刪除）

并發(fā)事務(wù)處理帶來的問題的解決辦法：

• “更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫(kù)事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。

• “臟讀” 、 “不可重復(fù)讀”和“幻讀” ，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問題，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來解決：

• 一種是加鎖：在讀取數(shù)據(jù)前，對(duì)其加鎖，阻止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改。
• 另一種是數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡(jiǎn)稱?MVCC?或 MCC），也稱為多版本數(shù)據(jù)庫(kù)：不用加任何鎖， 通過一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照 （Snapshot)， 并用這個(gè)快照來提供一定級(jí)別 （語句級(jí)或事務(wù)級(jí)） 的一致性讀取。從用戶的角度來看，好象是數(shù)據(jù)庫(kù)可以提供同一數(shù)據(jù)的多個(gè)版本。

事務(wù)隔離級(jí)別

數(shù)據(jù)庫(kù)事務(wù)的隔離級(jí)別有4種，由低到高分別為

• READ-UNCOMMITTED(讀未提交)：?最低的隔離級(jí)別，允許讀取尚未提交的數(shù)據(jù)變更，可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀。
• READ-COMMITTED(讀已提交)：?允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。
• REPEATABLE-READ(可重復(fù)讀)：?對(duì)同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。
• SERIALIZABLE(可串行化)：?最高的隔離級(jí)別，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級(jí)別可以防止臟讀、不可重復(fù)讀以及幻讀。

查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：

show variables like 'tx_isolation'

下面通過事例一一闡述在事務(wù)的并發(fā)操作中可能會(huì)出現(xiàn)臟讀，不可重復(fù)讀，幻讀和事務(wù)隔離級(jí)別的聯(lián)系。

數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。同時(shí)，不同的應(yīng)用對(duì)讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對(duì)“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。

Read uncommitted

讀未提交，就是一個(gè)事務(wù)可以讀取另一個(gè)未提交事務(wù)的數(shù)據(jù)。

事例：老板要給程序員發(fā)工資，程序員的工資是3.6萬/月。但是發(fā)工資時(shí)老板不小心按錯(cuò)了數(shù)字，按成3.9萬/月，該錢已經(jīng)打到程序員的戶口，但是事務(wù)還沒有提交，就在這時(shí)，程序員去查看自己這個(gè)月的工資，發(fā)現(xiàn)比往常多了3千元，以為漲工資了非常高興。但是老板及時(shí)發(fā)現(xiàn)了不對(duì)，馬上回滾差點(diǎn)就提交了的事務(wù)，將數(shù)字改成3.6萬再提交。

分析：實(shí)際程序員這個(gè)月的工資還是3.6萬，但是程序員看到的是3.9萬。他看到的是老板還沒提交事務(wù)時(shí)的數(shù)據(jù)。這就是臟讀。

那怎么解決臟讀呢？Read committed！讀提交，能解決臟讀問題。

Read committed

讀提交，顧名思義，就是一個(gè)事務(wù)要等另一個(gè)事務(wù)提交后才能讀取數(shù)據(jù)。

事例：程序員拿著信用卡去享受生活（卡里當(dāng)然是只有3.6萬），當(dāng)他埋單時(shí)（程序員事務(wù)開啟），收費(fèi)系統(tǒng)事先檢測(cè)到他的卡里有3.6萬，就在這個(gè)時(shí)候??！程序員的妻子要把錢全部轉(zhuǎn)出充當(dāng)家用，并提交。當(dāng)收費(fèi)系統(tǒng)準(zhǔn)備扣款時(shí)，再檢測(cè)卡里的金額，發(fā)現(xiàn)已經(jīng)沒錢了（第二次檢測(cè)金額當(dāng)然要等待妻子轉(zhuǎn)出金額事務(wù)提交完）。程序員就會(huì)很郁悶，明明卡里是有錢的…

分析：這就是讀提交，若有事務(wù)對(duì)數(shù)據(jù)進(jìn)行更新（UPDATE）操作時(shí)，讀操作事務(wù)要等待這個(gè)更新操作事務(wù)提交后才能讀取數(shù)據(jù)，可以解決臟讀問題。但在這個(gè)事例中，出現(xiàn)了一個(gè)事務(wù)范圍內(nèi)兩個(gè)相同的查詢卻返回了不同數(shù)據(jù)，這就是不可重復(fù)讀。

那怎么解決可能的不可重復(fù)讀問題？Repeatable read ！

Repeatable read

重復(fù)讀，就是在開始讀取數(shù)據(jù)（事務(wù)開啟）時(shí)，不再允許修改操作。MySQL的默認(rèn)事務(wù)隔離級(jí)別

事例：程序員拿著信用卡去享受生活（卡里當(dāng)然是只有3.6萬），當(dāng)他埋單時(shí)（事務(wù)開啟，不允許其他事務(wù)的UPDATE修改操作），收費(fèi)系統(tǒng)事先檢測(cè)到他的卡里有3.6萬。這個(gè)時(shí)候他的妻子不能轉(zhuǎn)出金額了。接下來收費(fèi)系統(tǒng)就可以扣款了。

分析：重復(fù)讀可以解決不可重復(fù)讀問題。寫到這里，應(yīng)該明白的一點(diǎn)就是，不可重復(fù)讀對(duì)應(yīng)的是修改，即UPDATE操作。但是可能還會(huì)有幻讀問題。因?yàn)榛米x問題對(duì)應(yīng)的是插入INSERT操作，而不是UPDATE操作。

什么時(shí)候會(huì)出現(xiàn)幻讀？

事例：程序員某一天去消費(fèi)，花了2千元，然后他的妻子去查看他今天的消費(fèi)記錄（全表掃描FTS，妻子事務(wù)開啟），看到確實(shí)是花了2千元，就在這個(gè)時(shí)候，程序員花了1萬買了一部電腦，即新增INSERT了一條消費(fèi)記錄，并提交。當(dāng)妻子打印程序員的消費(fèi)記錄清單時(shí)（妻子事務(wù)提交），發(fā)現(xiàn)花了1.2萬元，似乎出現(xiàn)了幻覺，這就是幻讀。

那怎么解決幻讀問題？Serializable！

Serializable 序列化

Serializable 是最高的事務(wù)隔離級(jí)別，在該級(jí)別下，事務(wù)串行化順序執(zhí)行，可以避免臟讀、不可重復(fù)讀與幻讀。簡(jiǎn)單來說，Serializable會(huì)在讀取的每一行數(shù)據(jù)上都加鎖，所以可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用問題。這種事務(wù)隔離級(jí)別效率低下，比較耗數(shù)據(jù)庫(kù)性能，一般不使用。

比較

需要說明的是，事務(wù)隔離級(jí)別和數(shù)據(jù)訪問的并發(fā)性是對(duì)立的，事務(wù)隔離級(jí)別越高并發(fā)性就越差。所以要根據(jù)具體的應(yīng)用來確定合適的事務(wù)隔離級(jí)別，這個(gè)地方?jīng)]有萬能的原則。

MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是?REPEATABLE-READ（可重讀）。我們可以通過SELECT @@tx_isolation;命令來查看，MySQL 8.0 該命令改為SELECT @@transaction_isolation;

這里需要注意的是：與 SQL 標(biāo)準(zhǔn)不同的地方在于InnoDB 存儲(chǔ)引擎在REPEATABLE-READ（可重讀）事務(wù)隔離級(jí)別下使用的是Next-Key Lock 算法，因此可以避免幻讀的產(chǎn)生，這與其他數(shù)據(jù)庫(kù)系統(tǒng)(如 SQL Server)是不同的。所以說InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀）已經(jīng)可以完全保證事務(wù)的隔離性要求，即達(dá)到了 SQL標(biāo)準(zhǔn)的?SERIALIZABLE(可串行化)隔離級(jí)別，而且保留了比較好的并發(fā)性能。

因?yàn)楦綦x級(jí)別越低，事務(wù)請(qǐng)求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別都是READ-COMMITTED(讀已提交):，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失。

MVCC 多版本并發(fā)控制

MySQL的大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)都不是簡(jiǎn)單的行級(jí)鎖?；谔嵘l(fā)性考慮，一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制（MVCC），包括Oracle、PostgreSQL。只是實(shí)現(xiàn)機(jī)制各不相同。

可以認(rèn)為 MVCC 是行級(jí)鎖的一個(gè)變種，但它在很多情況下避免了加鎖操作，因此開銷更低。雖然實(shí)現(xiàn)機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作，寫操作也只是鎖定必要的行。

MVCC 的實(shí)現(xiàn)是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的。也就是說不管需要執(zhí)行多長(zhǎng)時(shí)間，每個(gè)事物看到的數(shù)據(jù)都是一致的。

典型的MVCC實(shí)現(xiàn)方式，分為樂觀（optimistic）并發(fā)控制和悲觀（pressimistic）并發(fā)控制。下邊通過 InnoDB的簡(jiǎn)化版行為來說明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通過在每行記錄后面保存兩個(gè)隱藏的列來實(shí)現(xiàn)。這兩個(gè)列，一個(gè)保存了行的創(chuàng)建時(shí)間，一個(gè)保存行的過期時(shí)間（刪除時(shí)間）。當(dāng)然存儲(chǔ)的并不是真實(shí)的時(shí)間，而是系統(tǒng)版本號(hào)（system version number）。每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來和查詢到的每行記錄的版本號(hào)進(jìn)行比較。

REPEATABLE READ（可重讀）隔離級(jí)別下MVCC如何工作：

• SELECT

  InnoDB會(huì)根據(jù)以下兩個(gè)條件檢查每行記錄：

  只有符合上述兩個(gè)條件的才會(huì)被查詢出來

• InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行，這樣可以確保事務(wù)讀取的行，要么是在開始事務(wù)之前已經(jīng)存在要么是事務(wù)自身插入或者修改過的

• 行的刪除版本號(hào)要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)，這樣可以確保事務(wù)讀取到的行在事務(wù)開始之前未被刪除

• INSERT：InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)

• DELETE：InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)

• UPDATE：InnoDB為插入的一行新紀(jì)錄保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來的行作為刪除標(biāo)識(shí)

保存這兩個(gè)額外系統(tǒng)版本號(hào)，使大多數(shù)操作都不用加鎖。使數(shù)據(jù)操作簡(jiǎn)單，性能很好，并且也能保證只會(huì)讀取到符合要求的行。不足之處是每行記錄都需要額外的存儲(chǔ)空間，需要做更多的行檢查工作和一些額外的維護(hù)工作。

MVCC 只在 COMMITTED READ（讀提交）和REPEATABLE READ（可重復(fù)讀）兩種隔離級(jí)別下工作。

事務(wù)日志

InnoDB 使用日志來減少提交事務(wù)時(shí)的開銷。因?yàn)槿罩局幸呀?jīng)記錄了事務(wù)，就無須在每個(gè)事務(wù)提交時(shí)把緩沖池的臟塊刷新(flush)到磁盤中。

事務(wù)修改的數(shù)據(jù)和索引通常會(huì)映射到表空間的隨機(jī)位置，所以刷新這些變更到磁盤需要很多隨機(jī) IO。

InnoDB 假設(shè)使用常規(guī)磁盤，隨機(jī)IO比順序IO昂貴得多，因?yàn)橐粋€(gè)IO請(qǐng)求需要時(shí)間把磁頭移到正確的位置，然后等待磁盤上讀出需要的部分，再轉(zhuǎn)到開始位置。

InnoDB 用日志把隨機(jī)IO變成順序IO。一旦日志安全寫到磁盤，事務(wù)就持久化了，即使斷電了，InnoDB可以重放日志并且恢復(fù)已經(jīng)提交的事務(wù)。

InnoDB 使用一個(gè)后臺(tái)線程智能地刷新這些變更到數(shù)據(jù)文件。這個(gè)線程可以批量組合寫入，使得數(shù)據(jù)寫入更順序，以提高效率。

事務(wù)日志可以幫助提高事務(wù)效率：

• 使用事務(wù)日志，存儲(chǔ)引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。
• 事務(wù)日志采用的是追加的方式，因此寫日志的操作是磁盤上一小塊區(qū)域內(nèi)的順序I/O，而不像隨機(jī)I/O需要在磁盤的多個(gè)地方移動(dòng)磁頭，所以采用事務(wù)日志的方式相對(duì)來說要快得多。
• 事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢刷回到磁盤。
• 如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身沒有寫回到磁盤，此時(shí)系統(tǒng)崩潰，存儲(chǔ)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這一部分修改的數(shù)據(jù)。

目前來說，大多數(shù)存儲(chǔ)引擎都是這樣實(shí)現(xiàn)的，我們通常稱之為預(yù)寫式日志（Write-Ahead Logging），修改數(shù)據(jù)需要寫兩次磁盤。

事務(wù)的實(shí)現(xiàn)

事務(wù)的實(shí)現(xiàn)是基于數(shù)據(jù)庫(kù)的存儲(chǔ)引擎。不同的存儲(chǔ)引擎對(duì)事務(wù)的支持程度不一樣。MySQL 中支持事務(wù)的存儲(chǔ)引擎有 InnoDB 和 NDB。

事務(wù)的實(shí)現(xiàn)就是如何實(shí)現(xiàn)ACID特性。

事務(wù)的隔離性是通過鎖實(shí)現(xiàn)，而事務(wù)的原子性、一致性和持久性則是通過事務(wù)日志實(shí)現(xiàn) 。

?

事務(wù)是如何通過日志來實(shí)現(xiàn)的，說得越深入越好。

事務(wù)日志包括：重做日志redo和回滾日志undo

• redo log（重做日志） 實(shí)現(xiàn)持久化和原子性

  在innoDB的存儲(chǔ)引擎中，事務(wù)日志通過重做(redo)日志和innoDB存儲(chǔ)引擎的日志緩沖(InnoDB Log Buffer)實(shí)現(xiàn)。事務(wù)開啟時(shí)，事務(wù)中的操作，都會(huì)先寫入存儲(chǔ)引擎的日志緩沖中，在事務(wù)提交之前，這些緩沖的日志都需要提前刷新到磁盤上持久化，這就是DBA們口中常說的“日志先行”(Write-Ahead Logging)。當(dāng)事務(wù)提交之后，在Buffer Pool中映射的數(shù)據(jù)文件才會(huì)慢慢刷新到磁盤。此時(shí)如果數(shù)據(jù)庫(kù)崩潰或者宕機(jī)，那么當(dāng)系統(tǒng)重啟進(jìn)行恢復(fù)時(shí)，就可以根據(jù)redo log中記錄的日志，把數(shù)據(jù)庫(kù)恢復(fù)到崩潰前的一個(gè)狀態(tài)。未完成的事務(wù)，可以繼續(xù)提交，也可以選擇回滾，這基于恢復(fù)的策略而定。

  在系統(tǒng)啟動(dòng)的時(shí)候，就已經(jīng)為redo log分配了一塊連續(xù)的存儲(chǔ)空間，以順序追加的方式記錄Redo Log，通過順序IO來改善性能。所有的事務(wù)共享redo log的存儲(chǔ)空間，它們的Redo Log按語句的執(zhí)行順序，依次交替的記錄在一起。

• undo log（回滾日志）??實(shí)現(xiàn)一致性

  undo log 主要為事務(wù)的回滾服務(wù)。在事務(wù)執(zhí)行的過程中，除了記錄redo log，還會(huì)記錄一定量的undo log。undo log記錄了數(shù)據(jù)在每個(gè)操作前的狀態(tài)，如果事務(wù)執(zhí)行過程中需要回滾，就可以根據(jù)undo log進(jìn)行回滾操作。單個(gè)事務(wù)的回滾，只會(huì)回滾當(dāng)前事務(wù)做的操作，并不會(huì)影響到其他的事務(wù)做的操作。

  Undo記錄的是已部分完成并且寫入硬盤的未完成的事務(wù)，默認(rèn)情況下回滾日志是記錄下表空間中的（共享表空間或者獨(dú)享表空間）

二種日志均可以視為一種恢復(fù)操作，redo_log是恢復(fù)提交事務(wù)修改的頁(yè)操作，而undo_log是回滾行記錄到特定版本。二者記錄的內(nèi)容也不同，redo_log是物理日志，記錄頁(yè)的物理修改操作，而undo_log是邏輯日志，根據(jù)每行記錄進(jìn)行記錄。

?

又引出個(gè)問題：你知道MySQL 有多少種日志嗎？

• 錯(cuò)誤日志：記錄出錯(cuò)信息，也記錄一些警告信息或者正確的信息。

• 查詢?nèi)罩?/strong>：記錄所有對(duì)數(shù)據(jù)庫(kù)請(qǐng)求的信息，不論這些請(qǐng)求是否得到了正確的執(zhí)行。

• 慢查詢?nèi)罩?/strong>：設(shè)置一個(gè)閾值，將運(yùn)行時(shí)間超過該值的所有SQL語句都記錄到慢查詢的日志文件中。

• 二進(jìn)制日志：記錄對(duì)數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作。

• 中繼日志：中繼日志也是二進(jìn)制日志，用來給slave 庫(kù)恢復(fù)

• 事務(wù)日志：重做日志redo和回滾日志undo

?

分布式事務(wù)相關(guān)問題，可能還會(huì)問到 2PC、3PC，，，

MySQL對(duì)分布式事務(wù)的支持

分布式事務(wù)的實(shí)現(xiàn)方式有很多，既可以采用 InnoDB 提供的原生的事務(wù)支持，也可以采用消息隊(duì)列來實(shí)現(xiàn)分布式事務(wù)的最終一致性。這里我們主要聊一下 InnoDB 對(duì)分布式事務(wù)的支持。

MySQL 從 5.0.3 ?InnoDB 存儲(chǔ)引擎開始支持XA協(xié)議的分布式事務(wù)。一個(gè)分布式事務(wù)會(huì)涉及多個(gè)行動(dòng)，這些行動(dòng)本身是事務(wù)性的。所有行動(dòng)都必須一起成功完成，或者一起被回滾。

在MySQL中，使用分布式事務(wù)涉及一個(gè)或多個(gè)資源管理器和一個(gè)事務(wù)管理器。

如圖，MySQL 的分布式事務(wù)模型。模型中分三塊：應(yīng)用程序（AP）、資源管理器（RM）、事務(wù)管理器（TM）:

• 應(yīng)用程序：定義了事務(wù)的邊界，指定需要做哪些事務(wù)；
• 資源管理器：提供了訪問事務(wù)的方法，通常一個(gè)數(shù)據(jù)庫(kù)就是一個(gè)資源管理器；
• 事務(wù)管理器：協(xié)調(diào)參與了全局事務(wù)中的各個(gè)事務(wù)。

分布式事務(wù)采用兩段式提交（two-phase commit）的方式：

• 第一階段所有的事務(wù)節(jié)點(diǎn)開始準(zhǔn)備，告訴事務(wù)管理器ready。
• 第二階段事務(wù)管理器告訴每個(gè)節(jié)點(diǎn)是commit還是rollback。如果有一個(gè)節(jié)點(diǎn)失敗，就需要全局的節(jié)點(diǎn)全部rollback，以此保障事務(wù)的原子性。