01 | 基礎(chǔ)架構(gòu)：一條SQL查詢語(yǔ)句是如何執(zhí)行的捶朵？
MySql 邏輯架構(gòu)圖
執(zhí)行 create table 建表的時(shí)候并淋，如果不指定引擎類型，默認(rèn)使用InnoDB兔毙。
不同的存儲(chǔ)引擎共用一個(gè)Server 層。
連接器
連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限叙量、維持和管理連接。
mysql -h$ip -P$port -u$user -p
一個(gè)用戶成功建立連接后品山，即使你用管理員賬號(hào)對(duì)這個(gè)用戶的權(quán)限做了修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限涯呻。修改完成后，只有再新建連接才會(huì)使用新的權(quán)限設(shè)置市栗。
查看連接信息
客戶端如果太長(zhǎng)時(shí)間沒(méi)動(dòng)靜蛛淋，連接器就會(huì)自動(dòng)將它斷開(kāi)嘹悼。這個(gè)時(shí)間是由參數(shù) wait_timeout 控制的其监，默認(rèn)值是8小時(shí)抖苦。如果在連接被斷開(kāi)之后，客戶端再次發(fā)送請(qǐng)求的話，就會(huì)收到一個(gè)錯(cuò)誤提醒： Lost connection to MySQL server during query。
查詢緩存
mysql> select SQL_CACHE * from T where id = 10
MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了伏伐。
分析器
詞法分析（識(shí)別關(guān)鍵字，操作堤器，表名，列名）
語(yǔ)法分析 (判斷是否符合語(yǔ)法）
優(yōu)化器
優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引府蛇；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候妆距，決定各個(gè)表的連接順序塞颁。
優(yōu)化器階段完成后，這個(gè)語(yǔ)句的執(zhí)行方案就確定下來(lái)了，然后進(jìn)入執(zhí)行器階段。
執(zhí)行器
開(kāi)始執(zhí)行的時(shí)候动分，要先判斷一下你對(duì)這個(gè)表 T 有沒(méi)有執(zhí)行查詢的權(quán)限喇肋。如果沒(méi)有，就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤
印荔。如果命中查詢緩存殷绍，會(huì)在查詢緩存返回結(jié)果的時(shí)候，做權(quán)限驗(yàn)證牧牢。查詢也會(huì)在優(yōu)化器之前調(diào)用 precheck 驗(yàn)證權(quán)限。
如果有權(quán)限掌唾，就打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義挽拔，去調(diào)用這個(gè)引擎提供的接口术裸。
在數(shù)據(jù)庫(kù)的慢查詢?nèi)罩局锌吹揭粋€(gè)rows_examined 的字段，表示這個(gè)語(yǔ)句執(zhí)行過(guò)程中掃描了多少行轰传，這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)航的時(shí)候累加的。
在有些場(chǎng)景下倔既，執(zhí)行器調(diào)用一次恕曲，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟rows_examined并不是完全相同的叉存。
02 | 日志系統(tǒng)：一條SQL更新語(yǔ)句是如何執(zhí)行的码俩？
查詢語(yǔ)句的那一套流程，更新語(yǔ)句也是同樣會(huì)走一遍歼捏。
mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2;
- 執(zhí)行語(yǔ)句前要先連接數(shù)據(jù)庫(kù)稿存，這是連接器的工作。
- 在一個(gè)表上有更新的時(shí)候瞳秽，跟這個(gè)表有關(guān)的查詢緩存會(huì)失效瓣履，所以這條語(yǔ)句就會(huì)把表 T 上所有緩存結(jié)果都清空。
- 分析器會(huì)通過(guò)詞法和語(yǔ)法解析知道這是一條更新語(yǔ)句练俐。
- 優(yōu)化器決定要使用 ID 這個(gè)索引袖迎。
- 執(zhí)行器負(fù)責(zé)具體執(zhí)行，找到這一行腺晾，然后更新燕锥。
與查詢流程不一樣的是，更新流程還涉及兩個(gè)重要的日志模塊：redo log 和binlog悯蝉。
redo log
WAL 的全稱是 Write-Ahead Logging归形，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤鼻由，也就是先寫粉板暇榴，等不忙的時(shí)再寫賬本厚棵。
binlog
MySQL 整體來(lái)看，其實(shí)就有兩塊：一塊是 Server 層,它主要做的是 MySQL 功能層面的事情蔼紧；還有一塊是引擎層婆硬，負(fù)責(zé)存儲(chǔ)相關(guān)事宜。
粉板 redo log 是 InnoDB 引擎特有的日志奸例，而server層也有自己的日志彬犯，稱為 binlog（歸檔日志）。
redolog 與 binlog的區(qū)別：
有了對(duì)這兩個(gè)日志的概念性理解哩至，我們?cè)賮?lái)看執(zhí)行器和 InnoDB 引擎在執(zhí)行這個(gè)簡(jiǎn)單的 update 語(yǔ)句時(shí)的內(nèi)部流程躏嚎。
將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，這就是"兩階段提交"菩貌。
由于 redo log 和 binlog 是兩個(gè)獨(dú)立的邏輯卢佣，如果不用兩階段提交，要么就是先寫完 redo log 再寫 binlog,或者采用反過(guò)來(lái)的順序箭阶。我們看看這兩種方式會(huì)有什么問(wèn)題虚茶。
可以看到，如果不使用“兩階段提交”仇参，那么數(shù)據(jù)庫(kù)的狀態(tài)就有可能和用它的日志恢復(fù)出來(lái)的庫(kù)的狀態(tài)不一致嘹叫。
binlog作用
- 恢復(fù)數(shù)據(jù)庫(kù)（全量備份 + 備份時(shí)間點(diǎn)后的binglog)
- 擴(kuò)容搭建備庫(kù)（全量備份 + 備份時(shí)間點(diǎn)后的binglog)
- 同步數(shù)據(jù)
03 | 事務(wù)隔離：為什么你改了我還看不見(jiàn)？
簡(jiǎn)單來(lái)說(shuō)诈乒，事務(wù)就是要保證一組數(shù)據(jù)庫(kù)操作罩扇，要么全部成功，要么全部失敗
在 MySQL 中怕磨，事務(wù)支持是在引擎層實(shí)現(xiàn)的喂饥。MySQL 原生的 MyISAM 引擎就不支持事務(wù)，這也是MyISAM 被 InnoDB 取代的重要原因之一肠鲫。
ACID（Atomicity员帮、Consistency、Isolation导饲、Durability捞高，即原子性、一致性渣锦、隔離性硝岗、持久性）。
mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
你可以在 information_schema 庫(kù)的innodb_trx 這個(gè)表中查詢長(zhǎng)事務(wù)袋毙，比如下面這個(gè)語(yǔ)句查詢超過(guò)60s的事務(wù)
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
04 | 深入淺出索引（上）
索引的出現(xiàn)其實(shí)就是為了提高數(shù)據(jù)查詢的效率辈讶，就像書的目錄一樣。
索引的常見(jiàn)模型
哈希表
哈希表是一種以鍵 - 值（key-value）存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu),輸入待查找的值即 key娄猫，就可以找到其對(duì)應(yīng)的值即 Value.
用一個(gè)哈希函數(shù)把 key 換算成一個(gè)確定的位置贱除，然后把 把 value 放在數(shù)組的這個(gè)位置。
多個(gè) key 值經(jīng)過(guò)哈希函數(shù)的換算媳溺，會(huì)出現(xiàn)同一個(gè)值的情況（哈希沖突）月幌。處理這種情況的一種方法是，拉出一個(gè)鏈表悬蔽。
增刪改扯躺，等值查詢很快， 但因?yàn)椴皇怯行虻男ВＫ饕鰠^(qū)間查詢的速度是很慢的录语。
哈希表這種結(jié)構(gòu)適用于只有等值查詢的場(chǎng)景，比如Memcached 及其他一些 NoSQL 引擎禾乘。有序數(shù)組
有序數(shù)組在等值查詢和范圍查詢場(chǎng)景中的性能就都非常優(yōu)秀澎埠。
如果僅僅看查詢效率，有序數(shù)組就是最好的數(shù)據(jù)結(jié)構(gòu)了始藕。但是蒲稳，在需要更新數(shù)據(jù)的時(shí)候就麻煩了，你往中間插入一個(gè)記錄就必須得挪動(dòng)后面所有的記錄伍派，成本太高江耀。
有序數(shù)組索引只適用于靜態(tài)存儲(chǔ)引擎。二叉搜索樹(shù)
二叉搜索樹(shù)的特點(diǎn)是：每個(gè)節(jié)點(diǎn)的左兒子小于父節(jié)點(diǎn)诉植，父節(jié)點(diǎn)又小于右兒子節(jié)點(diǎn)祥国。查詢復(fù)雜度是O(logN)。
當(dāng)然為了維持 O(log(N)) 的查詢復(fù)雜度晾腔，你就需要保持這棵樹(shù)是平衡二叉樹(shù)舌稀。為了做這個(gè)保證，更新的時(shí)間復(fù)雜度也是O(logN)建车。
覆蓋索引
由于覆蓋索引可以減少樹(shù)的搜索次數(shù)扩借，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段缤至。
最左前綴原則
極客時(shí)間版權(quán)所有: https://time.geekbang.org/column/article/0?cid=139
不只是索引的全部定義潮罪，只要滿足最左前綴，就可以利用索引來(lái)加速檢索领斥。這個(gè)最左前綴可以是聯(lián)合索引的最左 N 個(gè)字段嫉到，也可以是字符串索引的最左 M 個(gè)字符。
查詢條件匹配復(fù)合索引最左的N個(gè)字段月洛，就可以使用該聯(lián)合索引何恶。
字符串查詢條件可以根據(jù)字符串最左M個(gè)字符，來(lái)匹配索引嚼黔。
在建立聯(lián)合索引的時(shí)候细层，如何安排索引內(nèi)的字段順序惜辑？
第一原則是，如果通過(guò)調(diào)整順序疫赎，可以少維護(hù)一個(gè)索引盛撑，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。
例如捧搞，有a,b 聯(lián)合查詢的需求抵卫，又有b獨(dú)立查詢的需求，這時(shí)應(yīng)該建聯(lián)合索引（b,a),而不是(a,b) 胎撇。
索引條件下推（index condition pushdown)
MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)介粘， 可以在索引遍歷過(guò)程對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄晚树，減少回表次數(shù)姻采。
例如有聯(lián)合索引(a,b), 查詢條件是a=x and b=y and c=z, 在Mysql5.6前，先在聯(lián)合索引中找到a=x的所有記錄题涨，然后回表在主鍵索引中匹配b,c條件偎谁。利用索引下推則可以直接在索引(a,b）中匹配a,b 條件，減少回表的記錄數(shù)纲堵。
06 | 全局鎖和表鎖 ：給表加個(gè)字段怎么有這么多阻礙巡雨？
同步過(guò)程中，如果加全局鎖席函，關(guān)聯(lián)的表數(shù)據(jù)（例如訂單铐望、余額）會(huì)有一致性問(wèn)題。
事務(wù)中的 MDL 鎖(讀鎖茂附、寫鎖)正蛙，在語(yǔ)句執(zhí)行開(kāi)始時(shí)申請(qǐng)，但是語(yǔ)句結(jié)束后并不會(huì)馬上釋放营曼，而會(huì)等到整個(gè)事務(wù)提交后再釋放乒验。
07 | 行鎖功過(guò)：怎么減少行鎖對(duì)性能的影響？
MySQL 的行鎖是在引擎層由各個(gè)引擎自己實(shí)現(xiàn)的蒂阱。但并不是所有的引擎都支持行鎖锻全，比如MyISAM引擎就不支持行鎖。不支持行鎖意味著并發(fā)控制只能使用表鎖录煤，對(duì)于這種引擎的表鳄厌，同一張表上任何時(shí)刻只能有一個(gè)更新在執(zhí)行，這會(huì)影響到業(yè)務(wù)并發(fā)度妈踊。InnoDB 是支持行鎖的了嚎，這也是 MyISAM 被 InnoDB替代的重要原因之一。
在 InnoDB 事務(wù)中，行鎖是在需要的時(shí)候才加上的歪泳，但并不是不需要了就立刻釋放萝勤，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段協(xié)議夹囚。
如果你的事務(wù)中需要鎖多個(gè)行纵刘，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放荸哟。
死鎖和死鎖檢測(cè)
