日志（可用來恢復(fù)數(shù)據(jù)）
注：MySQL 整體來看，一共有兩塊：一塊是 Server 層缤弦，它主要做的是 MySQL 功能層面的事情领迈；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜碍沐。它們各有自己的日志系統(tǒng)狸捅。 redo log 是 InnoDB 引擎特有的日志，而 Server 層也有自己的日志累提，稱為 binlog（歸檔日志）尘喝。
日志的作用：因?yàn)槊恳淮蔚母虏僮鞫夹枰獙戇M(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄斋陪，然后再更新朽褪，整個(gè)過程 IO 成本、查找成本都很高无虚。為了解決這個(gè)問題缔赠，MySQL 的設(shè)計(jì)者就用了類似酒店掌柜粉板的思路來提升更新效率。
redo?Log（重做日志）（屬于InnoDB引擎）（粉板）
Mysql采用WAL（write-Ahead Logging）技術(shù)友题，它的關(guān)鍵點(diǎn)在于先寫日志再寫磁盤嗤堰。這個(gè)日志就是Redo Log。當(dāng)一條記錄需要更新的時(shí)候度宦，InnoDB引擎會(huì)把記錄先寫到Redo Log中踢匣，并更新內(nèi)存，等到合適的時(shí)間戈抄，再將記錄更新到磁盤中离唬。這個(gè)更新往往在系統(tǒng)比較空閑的時(shí)候做，但是當(dāng)Redo Log的空間用完的時(shí)候划鸽，就需要先將記錄更新输莺，以便接下來繼續(xù)操作。
InnoDB的Redo Log是固定大小的，比如可以配置為一組 4 個(gè)文件模闲，每個(gè)文件的大小是 1GB，那么這塊“粉板”總共就可以記錄 4GB 的操作崭捍。從頭開始寫尸折，寫到末尾就又回到開頭循環(huán)寫，如下面這個(gè)圖所示殷蛇。
write pos 是當(dāng)前記錄的位置实夹，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭粒梦。checkpoint 是當(dāng)前要擦除的位置亮航，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件匀们。
write pos 和 checkpoint 之間的是“粉板”上還空著的部分缴淋，可以用來記錄新的操作。如果 write pos 追上 checkpoint泄朴，表示“粉板”滿了重抖，這時(shí)候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄祖灰，把 checkpoint 推進(jìn)一下钟沛。
有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟局扶，之前提交的記錄都不會(huì)丟失恨统，這個(gè)能力稱為 crash-safe。
Binlog(歸檔日志)（屬于Service層）
有兩份日志的原因：①mysql自帶引擎MyISAM沒有crash-safe能力三妈，而binglog也只能用來歸檔畜埋。
Redo Log和Binlog的不同點(diǎn)：
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的畴蒲，所有引擎都可以使用由捎。
redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”饿凛；binlog 是邏輯日志狞玛，記錄的是這個(gè)語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”涧窒。
redo log 是循環(huán)寫的心肪，空間固定會(huì)用完；binlog 是可以追加寫入的纠吴∮舶埃“追加寫”是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。
兩階段提交
更新一個(gè)數(shù)據(jù)的過程：
1固该、執(zhí)行器先找引擎取 ID=2 這一行锅减。ID 是主鍵，引擎直接用樹搜索找到這一行伐坏。如果 ID=2 這一行所在的數(shù)據(jù)頁(yè)本來就在內(nèi)存中怔匣，就直接返回給執(zhí)行器；否則桦沉，需要先從磁盤讀入內(nèi)存每瞒，然后再返回。
2纯露、執(zhí)行器拿到引擎給的行數(shù)據(jù)剿骨，把這個(gè)值加上 1，比如原來是 N埠褪，現(xiàn)在就是 N+1浓利，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)钞速。
3荞膘、引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面玉工，此時(shí) redo log 處于 prepare 狀態(tài)羽资。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)遵班。
4屠升、執(zhí)行器生成這個(gè)操作的 binlog凝化，并把 binlog 寫入磁盤缨称。
5、執(zhí)行器調(diào)用引擎的提交事務(wù)接口铡恕，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)翰萨，更新完成脏答。
update 語句的執(zhí)行流程圖如下，圖中淺色框表示是在 InnoDB 內(nèi)部執(zhí)行的亩鬼，深色框表示是在執(zhí)行器中執(zhí)行的殖告。
圖中將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，這就是"兩階段提交"雳锋。兩階段提交的主要是為了讓兩份日志之間的邏輯一致黄绩。
先寫 redo log 后寫 binlog。假設(shè)在 redo log 寫完玷过，binlog 還沒有寫完的時(shí)候爽丹，MySQL 進(jìn)程異常重啟筑煮。由于我們前面說過的，redo log 寫完之后粤蝎，系統(tǒng)即使崩潰真仲，仍然能夠把數(shù)據(jù)恢復(fù)回來，所以恢復(fù)后這一行 c 的值是 1初澎。但是由于 binlog 沒寫完就 crash 了秸应，這時(shí)候 binlog 里面就沒有記錄這個(gè)語句。因此谤狡，之后備份日志的時(shí)候，存起來的 binlog 里面就沒有這條語句卧檐。然后你會(huì)發(fā)現(xiàn)墓懂，如果需要用這個(gè) binlog 來恢復(fù)臨時(shí)庫(kù)的話，由于這個(gè)語句的 binlog 丟失霉囚，這個(gè)臨時(shí)庫(kù)就會(huì)少了這一次更新捕仔，恢復(fù)出來的這一行 c 的值就是 0，與原庫(kù)的值不同盈罐。
先寫 binlog 后寫 redo log榜跌。如果在 binlog 寫完之后 crash，由于 redo log 還沒寫盅粪，崩潰恢復(fù)以后這個(gè)事務(wù)無效钓葫，所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個(gè)日志票顾。所以础浮，在之后用 binlog 來恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來，恢復(fù)出來的這一行 c 的值就是 1奠骄，與原庫(kù)的值不同豆同。
事務(wù)（事務(wù)間隔離）
事務(wù)就是要保證一組數(shù)據(jù)庫(kù)操作，要么全部成功含鳞，要么全部失敗影锈。在MySQL中，事務(wù)支持是在引擎層實(shí)現(xiàn)的蝉绷。（MySQL 原生的 MyISAM 引擎就不支持事務(wù)鸭廷，這也是 MyISAM 被 InnoDB 取代的重要原因之一。）
隔離性與隔離級(jí)別
當(dāng)數(shù)據(jù)庫(kù)上有多個(gè)事務(wù)同時(shí)執(zhí)行的時(shí)候熔吗，就可能出現(xiàn)臟讀（dirty read）靴姿、不可重復(fù)讀（non-repeatable read）、幻讀（phantom read）的問題磁滚，為了解決這些問題佛吓，就有了“隔離級(jí)別”的概念宵晚。
隔離的越嚴(yán)實(shí)，效率就會(huì)越低维雇。
SQL 標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別包括：讀未提交（read uncommitted）淤刃、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（serializable ）吱型。
讀未提交是指逸贾，一個(gè)事務(wù)還沒提交時(shí)，它做的變更就能被別的事務(wù)看到津滞。
讀提交是指铝侵，一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到触徐。
可重復(fù)讀是指咪鲜，一個(gè)事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的撞鹉。當(dāng)然在可重復(fù)讀隔離級(jí)別下疟丙，未提交變更對(duì)其他事務(wù)也是不可見的。
串行化鸟雏，顧名思義是對(duì)于同一行記錄享郊，“寫”會(huì)加“寫鎖”，“讀”會(huì)加“讀鎖”孝鹊。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候炊琉，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行又活。
如下圖A温自、B事務(wù)同時(shí)執(zhí)行，不同隔離級(jí)別看到的不同結(jié)果：
若隔離級(jí)別是“讀未提交”皇钞， 則 V1 的值就是 2悼泌。這時(shí)候事務(wù) B 雖然還沒有提交，但是結(jié)果已經(jīng)被 A 看到了夹界。因此馆里，V2、V3 也都是 2可柿。
若隔離級(jí)別是“讀提交”鸠踪，則 V1 是 1，V2 的值是 2复斥。事務(wù) B 的更新在提交后才能被 A 看到营密。所以， V3 的值也是 2目锭。
若隔離級(jí)別是“可重復(fù)讀”评汰，則 V1纷捞、V2 是 1，V3 是 2被去。之所以 V2 還是 1主儡，遵循的就是這個(gè)要求：事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的。
若隔離級(jí)別是“串行化”惨缆，則在事務(wù) B 執(zhí)行“將 1 改成 2”的時(shí)候糜值，會(huì)被鎖住。直到事務(wù) A 提交后坯墨，事務(wù) B 才可以繼續(xù)執(zhí)行寂汇。所以從 A 的角度看， V1捣染、V2 值是 1骄瓣，V3 的值是 2。
在實(shí)現(xiàn)上液斜，數(shù)據(jù)庫(kù)里面會(huì)創(chuàng)建一個(gè)視圖累贤，訪問的時(shí)候以視圖的邏輯結(jié)果為準(zhǔn)叠穆。在“可重復(fù)讀”隔離級(jí)別下少漆，這個(gè)視圖是在事務(wù)啟動(dòng)時(shí)創(chuàng)建的，整個(gè)事務(wù)存在期間都用這個(gè)視圖硼被。在“讀提交”隔離級(jí)別下示损，這個(gè)視圖是在每個(gè) SQL 語句開始執(zhí)行的時(shí)候創(chuàng)建的。這里需要注意的是嚷硫，“讀未提交”隔離級(jí)別下直接返回記錄上的最新值检访，沒有視圖概念；而“串行化”隔離級(jí)別下直接用加鎖的方式來避免并行訪問仔掸。
事務(wù)隔離的實(shí)現(xiàn)
可重復(fù)讀事務(wù)實(shí)現(xiàn)：
在 MySQL 中脆贵，實(shí)際上每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作。記錄上的最新值起暮，通過回滾操作卖氨，都可以得到前一個(gè)狀態(tài)的值。（不同時(shí)刻啟動(dòng)事務(wù)负懦，會(huì)有不同的讀視圖（read-view）產(chǎn)生）
假設(shè)一個(gè)值從 1 被按順序改成了 2筒捺、3、4纸厉，在回滾日志里面就會(huì)有類似下面的記錄系吭。
當(dāng)前值是 4，但是在查詢這條記錄的時(shí)候颗品，不同時(shí)刻啟動(dòng)的事務(wù)會(huì)有不同的 read-view肯尺。如圖中看到的沃缘，在視圖 A、B蟆盹、C 里面孩灯，這一個(gè)記錄的值分別是 1、2逾滥、4峰档，同一條記錄在系統(tǒng)中可以存在多個(gè)版本，就是數(shù)據(jù)庫(kù)的多版本并發(fā)控制（MVCC）寨昙。對(duì)于 read-view A讥巡，要得到 1，就必須將當(dāng)前值依次執(zhí)行圖中所有的回滾操作得到舔哪。即使現(xiàn)在有另外一個(gè)事務(wù)正在將 4 改成 5欢顷，這個(gè)事務(wù)跟 read-view A、B捉蚤、C 對(duì)應(yīng)的事務(wù)是不會(huì)沖突的抬驴。
盡量不要使用長(zhǎng)事務(wù)。長(zhǎng)事務(wù)意味著系統(tǒng)里面會(huì)存在很老的事務(wù)視圖缆巧。由于這些事務(wù)隨時(shí)可能訪問數(shù)據(jù)庫(kù)里面的任何數(shù)據(jù)布持，所以這個(gè)事務(wù)提交之前，數(shù)據(jù)庫(kù)里面它可能用到的回滾記錄都必須保留陕悬，這就會(huì)導(dǎo)致大量占用存儲(chǔ)空間题暖。
事務(wù)的啟動(dòng)方式
MySQL 的事務(wù)啟動(dòng)方式有以下幾種：
1、顯式啟動(dòng)事務(wù)語句捉超， begin 或 start transaction胧卤。配套的提交語句是 commit，回滾語句是 rollback拼岳。
2枝誊、set autocommit=0，這個(gè)命令會(huì)將這個(gè)線程的自動(dòng)提交關(guān)掉惜纸。意味著如果你只執(zhí)行一個(gè) select 語句叶撒，這個(gè)事務(wù)就啟動(dòng)了，而且并不會(huì)自動(dòng)提交堪簿。這個(gè)事務(wù)持續(xù)存在直到你主動(dòng)執(zhí)行 commit 或 rollback 語句痊乾，或者斷開連接。
有些客戶端連接框架會(huì)默認(rèn)連接成功后先執(zhí)行一個(gè) set autocommit=0 的命令椭更。這就導(dǎo)致接下來的查詢都在事務(wù)中哪审，如果是長(zhǎng)連接，就導(dǎo)致了意外的長(zhǎng)事務(wù)虑瀑。因此湿滓，最好總是使用 set autocommit=1, 通過顯式語句的方式來啟動(dòng)事務(wù)滴须。
鎖
全局鎖和表鎖 增刪改查數(shù)據(jù)(DML)，修改表字段(DDL)
全局鎖
使用場(chǎng)景：做全庫(kù)邏輯備份叽奥。
①M(fèi)YSQL加全局鎖的命令：Flush table with read lock(FTWRL) （相比較②目前優(yōu)選扔水，對(duì)引擎沒有要求）
②官方邏輯備份工具M(jìn)ysqlDump： -single-transaction (缺點(diǎn)：需要引擎支持這個(gè)隔離級(jí)別，single-transaction 方法只適用于所有的表使用事務(wù)引擎的庫(kù)朝氓。)
③不推薦使用：set global readonly=true（相較于①魔市，readonly在有些系統(tǒng)可能用來做其他邏輯，還有就是發(fā)生異常之后不會(huì)釋放赵哲，而①會(huì)釋放）
表鎖：表鎖待德、元數(shù)據(jù)鎖
表鎖。語法：lock tables ... read/write （例線程A： lock tables t1 read, t2 write;則其他線程寫 t1枫夺、讀寫 t2 的語句都會(huì)被阻塞）? ? ? 解鎖：unlock tables（或者客戶端斷開的時(shí)候自動(dòng)釋放）（而對(duì)于 InnoDB 這種支持行鎖的引擎将宪，一般不使用 lock tables 命令來控制并發(fā)，畢竟鎖住整個(gè)表的影響面還是太大橡庞。）
（共享讀鎖较坛，獨(dú)占寫鎖,加上讀鎖，不會(huì)限制別的線程讀扒最，但會(huì)限制別的線程寫丑勤。加上寫鎖，會(huì)限制別的線程讀寫扼倘。線程 A 在執(zhí)行 unlock tables 之前确封，也只能執(zhí)行讀 t1除呵、讀寫 t2 的操作再菊。連寫 t1 都不允許，自然也不能訪問其他表颜曾。）
另一類表級(jí)的鎖是MDL（metadata lock)
mdl需不要顯示調(diào)用纠拔，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。mdl的作用是保證讀書的正確性泛豪。
在 MySQL 5.5 版本中引入了 MDL稠诲，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL 讀鎖诡曙；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候臀叙，加 MDL 寫鎖。
（讀鎖之間不互斥价卤，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查劝萤。
讀寫鎖之間、寫鎖之間是互斥的）
給一個(gè)表加字段慎璧，或者修改字段床嫌，或者加索引跨释，需要掃描全表的數(shù)據(jù)
事務(wù)中的 MDL 鎖，在語句執(zhí)行開始時(shí)申請(qǐng)厌处，但是語句結(jié)束后并不會(huì)馬上釋放鳖谈，而會(huì)等到整個(gè)事務(wù)提交后再釋放。
如何安全的給小表加字段：①考慮在長(zhǎng)事務(wù)中不進(jìn)行DDL阔涉，或者kill掉這個(gè)事務(wù)缆娃。? ②先嘗試拿MDL的寫鎖。
備注
全局鎖主要用在邏輯備份過程中瑰排，對(duì)于引擎全部是InnoDB的庫(kù)來說龄恋，用-Single-transation參數(shù)，對(duì)應(yīng)用更友好凶伙。
表鎖一般是在數(shù)據(jù)庫(kù)引擎不支持行鎖的時(shí)候才會(huì)用到郭毕。
如果你發(fā)現(xiàn)你的應(yīng)用程序里有 lock tables 這樣的語句，你需要追查一下函荣，比較可能的情況是：
要么是你的系統(tǒng)現(xiàn)在還在用 MyISAM 這類不支持事務(wù)的引擎显押，那要安排升級(jí)換引擎；
要么是你的引擎升級(jí)了傻挂，但是代碼還沒升級(jí)乘碑。我見過這樣的情況，最后業(yè)務(wù)開發(fā)就是把 lock tables 和 unlock tables 改成 begin 和 commit金拒，問題就解決了兽肤。
MDL 會(huì)直到事務(wù)提交才釋放，在做表結(jié)構(gòu)變更的時(shí)候绪抛，你一定要小心不要導(dǎo)致鎖住線上查詢和更新资铡。
行鎖
Mysql的行鎖是引擎自己實(shí)現(xiàn)的，這個(gè)InnoDB替代MylSAM的原因之一幢码。
行鎖：行鎖就是針對(duì)數(shù)據(jù)表中行記錄的鎖笤休。比如事務(wù) A 更新了一行，而這時(shí)候事務(wù) B 也要更新同一行症副，則必須等事務(wù) A 的操作完成后才能進(jìn)行更新店雅。（例如：update t set k = k + 1 where id = 1 會(huì)鎖update t set k = k + 2where id = 1? 而不會(huì)鎖update t set k = k + 1 where id = 2 ）
兩階段鎖（見下圖）
事務(wù) B 的 update 語句會(huì)被阻塞，直到事務(wù) A 執(zhí)行 commit 之后贞铣，
事務(wù) B 才能繼續(xù)執(zhí)行闹啦。
事務(wù) A 持有的兩個(gè)記錄的行鎖
事務(wù) A 持有的兩個(gè)記錄的行鎖，都是在 commit 的時(shí)候才釋放的辕坝。
在 InnoDB 事務(wù)中窍奋，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放费变。這個(gè)就是兩階段鎖協(xié)議
因?yàn)閮呻A段鎖的存在摧扇，如果事務(wù)中需要鎖多行，要把最可能造成鎖沖突挚歧、影響并發(fā)度的鎖往后放扛稽。
死鎖和死鎖檢測(cè)（見下圖）
避免死鎖的方式滑负。 一在张、超時(shí)機(jī)制（不推薦，時(shí)間長(zhǎng)影響使用矮慕，時(shí)間容易誤會(huì)鎖的正常等待帮匾。） 二、死鎖檢測(cè)痴鳄。發(fā)現(xiàn)死鎖后主動(dòng)回滾死鎖鏈中的某一事物瘟斜。過程如下：每當(dāng)一個(gè)事務(wù)被鎖的時(shí)候，就要看看它所依賴的線程有沒有被別人鎖住痪寻，如此循環(huán)螺句，最后判斷是否出現(xiàn)了循環(huán)等待，也就是死鎖橡类。缺點(diǎn)：每個(gè)新來的被堵住的線程蛇尚，都要判斷會(huì)不會(huì)由于自己的加入導(dǎo)致了死鎖，這是一個(gè)時(shí)間復(fù)雜度是 O(n) 的操作顾画。假設(shè)有 1000 個(gè)并發(fā)線程要同時(shí)更新同一行取劫，那么死鎖檢測(cè)操作就是 100 萬這個(gè)量級(jí)的。
解決死鎖方案
臨時(shí)把死鎖檢測(cè)關(guān)掉研侣。
控制并發(fā)度谱邪。
減少死鎖的主要方向，就是控制訪問相同資源的并發(fā)事務(wù)量义辕。
注：筆記主要來自于極客時(shí)間的《Mysql實(shí)戰(zhàn)45講》
