第一講：基礎(chǔ)架構(gòu)：一條SQL查詢語(yǔ)句是如何執(zhí)行的粗截？
1鸣驱、MySQL 的基本架構(gòu)示意圖
大體來說Mysql分為兩部分：Server層和數(shù)據(jù)引擎。
Server層包括連接器盗棵、查詢緩存泽铛、分析器尚辑、優(yōu)化器、執(zhí)行器盔腔。涵蓋大多數(shù)核心功能杠茬，以及所有內(nèi)置函數(shù)，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)弛随，比如存儲(chǔ)過程瓢喉、觸發(fā)器、試圖等撵幽。
存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的提取和存儲(chǔ)灯荧，支持InnoDB、MyISAM盐杂、Memory 等多個(gè)存儲(chǔ)引擎逗载，Mysql5.5.5后InnoDB成為默認(rèn)引擎。如果在建表是想選擇其他引擎可以engine=memory來指定使用內(nèi)存引擎創(chuàng)建表链烈。
不同的存儲(chǔ)引擎共用一個(gè) Server 層厉斟，也就是從連接器到執(zhí)行器的部分。
2强衡、連接器
第一步必須要連接到Mysql上擦秽，這時(shí)用到的是連接器。
連接器負(fù)責(zé)跟客戶端建立連接漩勤、獲取權(quán)限感挥、維持和管理連接。
客戶端長(zhǎng)時(shí)間不使用越败，連接器會(huì)自動(dòng)斷開触幼，這個(gè)時(shí)間是由參數(shù) wait_timeout 控制的，默認(rèn)值是 8 小時(shí)究飞。如果斷開后置谦，再次發(fā)送請(qǐng)求堂鲤，會(huì)提示Lost connection to MySQL server during query。這時(shí)需要重連媒峡。
數(shù)據(jù)庫(kù)里面長(zhǎng)連接是瘟栖，長(zhǎng)連接成功后，客戶端持續(xù)的請(qǐng)求使用同一個(gè)連接谅阿。短連接是客戶端幾次查詢后就斷開半哟，下次查詢?cè)僦匦陆⑦B接。推薦使用長(zhǎng)連接奔穿，建立連接過程是復(fù)雜的镜沽，但有些時(shí)候內(nèi)存長(zhǎng)得特別快，這是因?yàn)?MySQL 在執(zhí)行過程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的贱田。這些資源會(huì)在連接斷開的時(shí)候才釋放。所以如果長(zhǎng)連接累積下來嘴脾，可能導(dǎo)致內(nèi)存占用太大男摧，被系統(tǒng)強(qiáng)行殺掉（OOM），從現(xiàn)象看就是 MySQL 異常重啟了译打。
長(zhǎng)連接占用內(nèi)存過多解決方案
（1）耗拓、定期斷開長(zhǎng)連接，重連奏司。
（2）乔询、Mysql5.7版本后，每次執(zhí)行完比較大的操作韵洋，通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源竿刁。這個(gè)過 程不需要不需要重連和重做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)搪缨。
3食拜、查詢緩存
第二步，建立好了連接副编，接下來執(zhí)行sql负甸，查詢緩存。
Mysql拿到sql首先會(huì)檢查是不是之前執(zhí)行過的sql痹届，所以會(huì)先查詢緩存呻待，之前執(zhí)行過的sql及結(jié)果可能會(huì)以key-value形式存在于緩存中。如果能直接找到key队腐，就直接返給客戶端蚕捉。
如果緩存沒命中，則執(zhí)行后續(xù)過程香到，執(zhí)行完成后鱼冀，執(zhí)行結(jié)果會(huì)被存入緩存报破。
使用緩存弊大于利，緩存失效非常頻繁千绪，只要有一個(gè)表更新充易，這個(gè)表上的查詢緩存會(huì)全部失效，可能之前存的緩存還沒有用到荸型，就全失效了盹靴，這對(duì)壓力大的數(shù)據(jù)庫(kù)來說，查詢緩存命中率特別低瑞妇。靜態(tài)表稿静，比如配置表，長(zhǎng)時(shí)間不更新推薦使用緩存辕狰。
注意的是改备，MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個(gè)功能了蔓倍。
4悬钳、分析器
如果沒有命中緩存，接下來就真正執(zhí)行sql偶翅，首先要知道sql要干什么默勾，就要對(duì)sql進(jìn)行解析。
首先進(jìn)行? 詞法分析? 分析出字符串是什么意思聚谁，比如select->查詢語(yǔ)句母剥，把T識(shí)別為表名T，ID識(shí)別為列ID
然后進(jìn)行? 語(yǔ)法分析? 要遵從語(yǔ)法規(guī)則比如select->elect會(huì)報(bào)錯(cuò)You have an error in your SQL syntax
5形导、優(yōu)化器
經(jīng)過了分析器环疼，知道了sql要干什么，接下來進(jìn)行優(yōu)化器處理骤宣。
優(yōu)化器是當(dāng)你sql中有多個(gè)索引時(shí)秦爆，決定使用哪個(gè)索引，或者在一個(gè)語(yǔ)句中有多個(gè)join表關(guān)聯(lián)憔披，決定各個(gè)表的連接順序等限。
例如 select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
這里有兩種執(zhí)行順序，先取c=10或者先取d=20芬膝，邏輯上一樣望门，但是效率有所不同，優(yōu)化器就是起選擇作用锰霜。
6筹误、執(zhí)行器
知道要做什么了，接下來就是執(zhí)行器癣缅。
執(zhí)行前會(huì)判斷下有沒有表T的執(zhí)行權(quán)限厨剪，如沒有會(huì)返回錯(cuò)誤哄酝。如果是查詢緩存，則是返回的時(shí)候驗(yàn)證權(quán)限祷膳，(查詢也會(huì)在優(yōu)化器之前調(diào)用 precheck 驗(yàn)證權(quán)限)陶衅。
為什么對(duì)權(quán)限的檢查不在優(yōu)化器之前做？
有些時(shí)候直晨，SQL語(yǔ)句要操作的表不只是SQL字面上那些搀军。比如如果有個(gè)觸發(fā)器，得在執(zhí)行器階段（過程中）才能確定勇皇。優(yōu)化器階段前是無能為力的罩句。
mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
如果有權(quán)限就打開表執(zhí)行，會(huì)根據(jù)表的引擎定義敛摘，去使用引擎提供的接口门烂。
比如上面sql，id字段沒有索引兄淫，則：
1）诅福、調(diào)用Innodb引擎接口取第一行判斷id是不是10，是加入到結(jié)果集拖叙，不是跳過。
2）赂乐、調(diào)用引擎接口取下一行薯鳍，重復(fù)1），直到這個(gè)表最后一行挨措。
3）挖滤、執(zhí)行器把上述過程所有滿足條件的行組成結(jié)果集返回給客戶端。
至此一條sql執(zhí)行完成浅役。
對(duì)于有索引的表斩松，邏輯差不多，第一次調(diào)的是“滿足條件的第一條”觉既，第二次調(diào)的是“滿足條件的下一條”這些接口引擎中有定義惧盹。
數(shù)據(jù)庫(kù)慢日志中可以查到rows_examined字段，代表一共掃描了多少行瞪讼，這是執(zhí)行器每次調(diào)用引擎累加的钧椰。
有些情況下，執(zhí)行器調(diào)用一次符欠，引擎會(huì)掃描多行嫡霞，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的。這個(gè)后面會(huì)學(xué)習(xí)希柿。
第二講：日志系統(tǒng)：一條SQL更新語(yǔ)句是如何執(zhí)行的诊沪？
1养筒、更新語(yǔ)句執(zhí)行
mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2;
查詢語(yǔ)句的流程更新語(yǔ)句會(huì)再走一遍，首先連接數(shù)據(jù)庫(kù)端姚，然后在一個(gè)表上有更新晕粪，則這條語(yǔ)句就會(huì)把表 T 上所有緩存結(jié)果都清空，然后分析器分析這是一條更新語(yǔ)句寄锐，優(yōu)化器決定使用ID這個(gè)索引兵多，然后執(zhí)行器找到這一行更新。
與之前不同的是更新流程涉及到兩個(gè)重要日志模塊：redo log（重做日志）和 binlog（歸檔日志）橄仆。
2剩膘、重要的日志模塊：redo log
關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤盆顾。
當(dāng)有一條記錄需要更新的時(shí)候怠褐，InnoDB引擎會(huì)先把記錄寫到 redo log (粉板)里面，并更新內(nèi)存您宪，這個(gè)時(shí)候就算更新完成了奈懒，同時(shí)，InnoDB會(huì)在適當(dāng)?shù)臅r(shí)候?qū)⑦@個(gè)記錄更新到磁盤中宪巨，而這個(gè)操作往往是在系統(tǒng)比較空閑的時(shí)候做磷杏，類似打烊以后掌柜把粉板更新到賬本上。
InnoDB 的 redo log 是固定大小的捏卓，比如可以配置為一組 4 個(gè)文件极祸，每個(gè)文件的大小是 1GB，那么這塊“粉板”總共就可以記錄 4GB 的操作怠晴。從頭開始寫遥金，寫到末尾就又回到開頭循環(huán)寫，如下面這個(gè)圖所示：
write pos是當(dāng)前寫位置蒜田，一邊寫一邊后移稿械，check point是當(dāng)前要擦除記錄位置，這兩個(gè)指針都是循環(huán)的冲粤。擦除的記錄需要更新到記錄文件美莫。
兩指針中間位置是當(dāng)前“粉板”還空著的空間，如果write pos追上check point色解，說明“粉板”滿了茂嗓，需要停下來擦除一下記錄，
有了 redo log科阎，InnoDB 就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟述吸，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為 crash-safe。
3蝌矛、重要的日志模塊：binlog
mysql主要有兩塊道批，Server和存儲(chǔ)引擎，Server主要做的是mysql功能層面的事入撒，存儲(chǔ)引擎主要做的是存儲(chǔ)相關(guān)隆豹，那么redo log相當(dāng)于是存儲(chǔ)引擎（InnoDB）特有的日志。而Server層也有自己特有的日志 binlog 茅逮，稱為歸檔日志璃赡。
1）、為什么會(huì)有兩份日志献雅？
簡(jiǎn)單來說是當(dāng)時(shí)Mysql沒有InnoDB引擎碉考，Mysql自帶引擎MyISAM不支持?crash-safe?能力，binlog 日志只能用于歸檔挺身。
2）侯谁、這兩種日志有以下三點(diǎn)不同
a、redo log是InnoDB特有的章钾，binlog是Server層實(shí)現(xiàn)的墙贱，所有公用的。
b贱傀、redo log是物理日志惨撇，記錄了“某頁(yè)改了什么”，binlog是邏輯日志府寒，記錄的是這個(gè)語(yǔ)句的原始邏輯串纺，比如“給 ID=2 這一行的 c 字段加 1”。
c椰棘、redo log是循環(huán)寫的，空間固定會(huì)用完榄笙，binlog是可以"追加寫"邪狞，不會(huì)覆蓋以前的日志。
3）茅撞、執(zhí)行器和InnoDB執(zhí)行一條更新語(yǔ)句流程
深色部分執(zhí)行器執(zhí)行帆卓，淺色部分引擎執(zhí)行。
總結(jié)起來分五步米丘，1剑令、執(zhí)行引擎取id=2的這行，引擎直接搜索到這行看是否在內(nèi)存頁(yè)中拄查，是返回吁津，否磁盤讀入內(nèi)存；2、引擎c+1碍脏，寫入新行梭依；3、引擎將新行更新到內(nèi)存典尾，更新寫入redo log役拴，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了钾埂，隨時(shí)可以提交事務(wù)河闰；4、執(zhí)行器生成這個(gè)操作的binlog褥紫，并把 binlog 寫入磁盤姜性；5、引擎提交事務(wù)故源，redo log 處于 commit 狀態(tài)污抬。
最后三步，將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit绳军，這就是"兩階段提交"印机。
兩階段提交是為了讓兩份日志之間的邏輯一致
簡(jiǎn)單說，redo log 和 binlog 都可以用于表示事務(wù)的提交狀態(tài)门驾，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致射赛。
4）、怎樣讓數(shù)據(jù)庫(kù)恢復(fù)到半個(gè)月內(nèi)任意一秒的狀態(tài)奶是？
首先取最近一次的全量備份楣责，然后取binlog注意執(zhí)行到要恢復(fù)的時(shí)間點(diǎn)
四、總結(jié)
主要學(xué)習(xí)兩個(gè)日志聂沙，物理日志redo log 和 邏輯日志binlog秆麸。
redo log 保證了crash-safe 能力。innodb_flush_log_at_trx_commit 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候及汉，表示每次事務(wù)的 redo log 都直接持久化到磁盤沮趣。這個(gè)參數(shù)建議設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失坷随。
sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候房铭，表示每次事務(wù)的 binlog 都持久化到磁盤。這個(gè)參數(shù)也建議設(shè)置成 1温眉，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失缸匪。
日志系統(tǒng)的"兩階段提交"，兩階段提交是跨系統(tǒng)維持?jǐn)?shù)據(jù)邏輯性一致時(shí)常用的一個(gè)方案类溢。
三凌蔬、事務(wù)隔離：為什么你改了我還看不見？
1、前言
事務(wù)就是要保證一組數(shù)據(jù)庫(kù)操作龟梦，要么全部成功隐锭，要么全部失敗。
Mysql支持多引擎计贰，但并非所有引擎都支持事務(wù)钦睡，比如MyISAM不支持事務(wù)，也是被InnoDB替換的重要原因之一躁倒。
2荞怒、隔離性于隔離級(jí)別
1）、事務(wù)特征秧秉，ACID褐桌，原子性、一致性象迎、隔離性荧嵌、持久性。
2）砾淌、多事務(wù)同時(shí)執(zhí)行時(shí)可能出現(xiàn)啦撮，臟讀、不可重復(fù)讀汪厨、幻讀赃春。
臟讀：讀了一個(gè)事務(wù)沒有提交的數(shù)據(jù)
不可重復(fù)讀：事務(wù)A執(zhí)行中還未最終提交，另一個(gè)事務(wù)B修改了數(shù)據(jù)并且提交劫乱，事務(wù)A兩次讀取數(shù)據(jù)不一致
幻讀：一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)织中，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)
3）、事務(wù)的隔離級(jí)別衷戈，隔離的越嚴(yán)狭吼，性能就越低
讀未提交：事務(wù)還沒被提交時(shí)，其他事務(wù)就能看到殖妇。
讀已提交：事務(wù)提交后搏嗡，才能夠被其他事務(wù)看到
可重復(fù)讀：一個(gè)事務(wù)在執(zhí)行過程中，總是跟這個(gè)事務(wù)開啟時(shí)看到的數(shù)據(jù)是一致的拉一，在此級(jí)別下為提交的變更其他事務(wù)也看不見
串行化：對(duì)同一行數(shù)據(jù)，讀會(huì)加讀鎖旧乞，寫會(huì)加寫鎖蔚润，當(dāng)出現(xiàn)讀寫沖突時(shí)，后執(zhí)行的事務(wù)必須等待前一個(gè)事務(wù)執(zhí)行完才能繼續(xù)執(zhí)行
總結(jié)起來：
讀未提交：別人改數(shù)據(jù)的事務(wù)尚未提交尺栖，我在我的事務(wù)中也能讀到嫡纠。
讀已提交：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中才能讀到。
可重復(fù)讀：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交除盏，我在我的事務(wù)中也不去讀叉橱。
串行化：我的事務(wù)尚未提交，別人就別想改數(shù)據(jù)者蠕。
這4種隔離級(jí)別窃祝，并行性能依次降低，安全性依次提高踱侣。
4）粪小、Oracle默認(rèn)的隔離級(jí)別是“讀已提交”，transaction-isolation設(shè)置當(dāng)前事務(wù)級(jí)別抡句，show variables查看當(dāng)前隔離級(jí)別
使用“可重復(fù)讀”隔離級(jí)別好處：事務(wù)啟動(dòng)時(shí)的視圖可以認(rèn)為是靜態(tài)的探膊，不受其他事務(wù)更新的影響。
3待榔、事務(wù)隔離的實(shí)現(xiàn)
每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作逞壁。同一條記錄在系統(tǒng)中可以存在多個(gè)版本，這就是數(shù)據(jù)庫(kù)的多版本并發(fā)控制（MVCC）锐锣。
1）腌闯、回滾日志什么時(shí)候刪除？
系統(tǒng)會(huì)判斷當(dāng)沒有事務(wù)需要用到這些回滾日志的時(shí)候刺下，回滾日志會(huì)被刪除绑嘹。
2）、什么時(shí)候不需要了橘茉？
當(dāng)系統(tǒng)里么有比這個(gè)回滾日志更早的read-view的時(shí)候工腋。
3）、為什么盡量不要使用長(zhǎng)事務(wù)畅卓。
長(zhǎng)事務(wù)意味著系統(tǒng)里面會(huì)存在很老的事務(wù)視圖擅腰，在這個(gè)事務(wù)提交之前，回滾記錄都要保留翁潘，這會(huì)導(dǎo)致大量占用存儲(chǔ)空間趁冈。除此之外，長(zhǎng)事務(wù)還占用鎖資源拜马，可能會(huì)拖垮庫(kù)渗勘。
四、事務(wù)的啟動(dòng)方式
1）俩莽、顯式啟動(dòng)事務(wù)語(yǔ)句旺坠， begin 或 start transaction。配套的提交語(yǔ)句是 commit扮超，回滾語(yǔ)句是 rollback取刃。
2）蹋肮、set autocommit=0，這個(gè)命令會(huì)將這個(gè)線程的自動(dòng)提交關(guān)掉璧疗。意味著如果你只執(zhí)行一個(gè) select 語(yǔ)句坯辩，這個(gè)事務(wù)就啟動(dòng)了，而且并不會(huì)自動(dòng)提交崩侠。這個(gè)事務(wù)持續(xù)存在直到你主動(dòng)執(zhí)行 commit 或 rollback 語(yǔ)句漆魔，或者斷開連接。
建議：set autocommit=1, 通過顯式語(yǔ)句的方式來啟動(dòng)事務(wù)啦膜，可以解決有些客戶端啟動(dòng)時(shí)set autocommit=0有送，這導(dǎo)致接下來的每次查詢都在事務(wù)中，如果是長(zhǎng)鏈接僧家，就導(dǎo)致了長(zhǎng)事務(wù)雀摘。
但如果考慮多次交互問題建議在set autocommit=1的情況下使用 commit work and chain 語(yǔ)法，commit是提交事務(wù)八拱，? ? ? ? commit work and chain是提交事務(wù)并自動(dòng)啟動(dòng)下一個(gè)事務(wù)阵赠，這樣省去了begain的開銷。
查詢長(zhǎng)事務(wù)方法：下面這個(gè)語(yǔ)句肌稻，用于查找持續(xù)時(shí)間超過 60s 的事務(wù)清蚀。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
第四講、深入淺出索引（上）
一句話簡(jiǎn)單來說爹谭，索引就跟書的目錄一樣枷邪，就是為了提高數(shù)據(jù)的查詢效率。
一诺凡、索引的常見模型
1东揣、常見的索引模型：哈希表、有序數(shù)組腹泌、搜索樹嘶卧。
2、哈希表：鍵 - 值（key-value）存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)凉袱。
3芥吟、哈希的思路：把值放在數(shù)組里，用一個(gè)哈希函數(shù)把 key 換算成一個(gè)確定的位置专甩，然后把 value 放在數(shù)組的這個(gè)位置钟鸵。解決哈希沖突的辦法是拉出一個(gè)鏈表。
4涤躲、哈希表適用場(chǎng)景：只有等值查詢棺耍。
5、有序數(shù)組：按順序存儲(chǔ)篓叶，查詢用二分法就可以快速查詢烈掠，時(shí)間復(fù)雜度是：O(log(N))，有序數(shù)組在等值查詢和范圍查詢場(chǎng)景中的性能就都非常優(yōu)秀缸托，有序數(shù)組查詢效率高左敌，更新效率低。
6俐镐、有序數(shù)組適用場(chǎng)景：只適用于靜態(tài)存儲(chǔ)引擎矫限。（一些不會(huì)修改的數(shù)據(jù)）
7、二叉搜索樹：左節(jié)點(diǎn) < 根節(jié)點(diǎn) < 右節(jié)點(diǎn)佩抹。查詢和更新時(shí)間復(fù)雜度都是O(log(N))叼风。
8、數(shù)據(jù)庫(kù)存儲(chǔ)大多不用二叉樹棍苹，原因是索引不僅存在內(nèi)存中无宿，還要寫到磁盤，樹過高的話枢里，查詢效率慢孽鸡，所以適用n叉樹。
二栏豺、InnoDB中的索引模型
1彬碱、InnoDB中的索引模型：B+樹。每一個(gè)索引在 InnoDB 里面對(duì)應(yīng)一棵 B+ 樹奥洼。
2巷疼、索引類型：主鍵索引和非主鍵索引。
主鍵索引葉子結(jié)點(diǎn)存的是整行數(shù)據(jù)灵奖，也稱為聚簇索引嚼沿。
非主鍵索引葉子結(jié)點(diǎn)存的是主鍵的值，也稱為二級(jí)索引桑寨。
3伏尼、主鍵索引與非主鍵索引區(qū)別：主鍵索引只需要搜索ID這棵B+樹就能拿到數(shù)據(jù)。非主鍵索引是先搜索索引拿到主鍵值尉尾，再到? 主鍵索引樹搜索一次爆阶，這個(gè)過程稱為回表。
4沙咏、基于非主鍵索引的查詢需要多掃描一棵索引樹辨图，所以盡量使用主鍵索引查詢。
三肢藐、索引維護(hù)
1故河、B+ 樹為了維護(hù)索引有序性，在插入新值的時(shí)候需要做必要的維護(hù)吆豹。
2鱼的、一個(gè)數(shù)據(jù)頁(yè)滿了理盆，按照B+算法，新增加一個(gè)數(shù)據(jù)頁(yè)凑阶，然后挪動(dòng)部分過去猿规，這個(gè)過程叫頁(yè)分裂。會(huì)導(dǎo)致性能下降宙橱，空間利用率降低大概50%姨俩。當(dāng)相鄰的兩個(gè)數(shù)據(jù)頁(yè)利用率很低時(shí)會(huì)合并，叫做分裂過程的逆過程师郑。
3环葵、從性能和存儲(chǔ)空間方面考量，自增主鍵往往是更合理的選擇宝冕。主鍵長(zhǎng)度越小张遭，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小猬仁。
