[[399878]]

本文轉(zhuǎn)載自微信公眾號(hào)「yes的練級(jí)攻略」，作者是Yes呀。轉(zhuǎn)載本文請(qǐng)聯(lián)系yes的練級(jí)攻略公眾號(hào)。

你好，我是 yes。

先來個(gè)，開局一張圖。

這圖算是第一版本，本來還想填充地更詳細(xì)些，但是看著感覺好冗余，暫時(shí)就先這樣吧，主要是用來標(biāo)注一些關(guān)鍵點(diǎn)，便于復(fù)習(xí)。

其實(shí)對(duì)咱們后端開發(fā)而言，對(duì) MySQL 接觸有很多，但是又接觸不多。接觸很多指的是我們經(jīng)常寫 SQL 一直在用它，接觸不多指的是我們也僅僅只是寫 SQL，一些配置相關(guān)的包括第一手掌控那都是 DBA 在搞。

這系列文章我就篩選出和我們開發(fā)息息相關(guān)的 MySQL 知識(shí)點(diǎn)。我打算先做一個(gè)總覽，只 BFS，也就是說不會(huì)很扣細(xì)節(jié)，先成面。

等之后的文章再慢慢 DFS，各個(gè)擊破。當(dāng)然面試題也會(huì)同步更新，后面都會(huì)有滴。

MySQL 體系結(jié)構(gòu)

這個(gè)非常重要，理解了之后后面的一些知識(shí)點(diǎn)才能懂，比如索引下推。

MySQL 體系結(jié)構(gòu)可以分為兩大塊來看，分別是：Server 和存儲(chǔ)引擎。

當(dāng)客戶端與 MySQL 建立連接之后，一條 SQL 語句經(jīng)過 TCP 從客戶端傳輸?shù)?Server ，Server 會(huì)先將語句進(jìn)行詞法分析與語法分析，這個(gè)工作是分析器做的。

如果語法有問題，那這個(gè)錯(cuò)誤相信大家都不陌生：You have an error in your SQL syntax; check the manual......

確認(rèn)語法沒問題之后，會(huì)再經(jīng)由優(yōu)化器來決策這條語句是否需要重寫，如何選擇驅(qū)動(dòng)表，如何選擇合適的索引等操作，目的就是讓語句更高效的執(zhí)行。

我們平日里用的 explain 其實(shí)就是讓 MySQL 告訴我們它的優(yōu)化決定策略是怎樣的。

至此，MySQL 已經(jīng)知道該做什么和怎么做了，此時(shí)就是執(zhí)行器干活時(shí)候了，它會(huì)調(diào)用存儲(chǔ)引擎的接口來執(zhí)行語句。

第一個(gè)關(guān)鍵點(diǎn)來了。

例如我現(xiàn)在要執(zhí)行一條select * from yes where name='yes的練級(jí)攻略';這條語句，name 這一列沒有索引。

此時(shí)流程如下：

1. Server 調(diào)用存儲(chǔ)引擎的返回這個(gè)表的第一行這個(gè)接口，此時(shí) Server 拿到第一行數(shù)據(jù)。
2. Server 通過 where 條件判斷 name 是否等于yes的練級(jí)攻略，如果是則放到結(jié)果集中，不是則跳過。
3. Server 繼續(xù)調(diào)用存儲(chǔ)引擎的接口來下一行!，然后再通過 where 條件來判斷。
4. 如此循環(huán)往復(fù)，直到最后一行記錄。
5. 不會(huì)等結(jié)果全部收集完畢了才返回給客戶端，等集滿net_buffer大小的結(jié)果就會(huì)發(fā)送，也就是邊查邊發(fā)。

從以上流程可以得知，where 的條件如果用不上索引，那是在 Server 層做過濾的，如果你平日 exlplain 時(shí)候從 extra 里看到 using where，那就是在 Server 層利用 where 做了過濾的意思。

然后就是存儲(chǔ)引擎的接口。MySQL 的存儲(chǔ)引擎是插件式的，一個(gè)數(shù)據(jù)庫里面的不同表可以用不同的存儲(chǔ)引擎，而 Server 都是同一個(gè)，所以需要規(guī)定好統(tǒng)一的接口，這樣 Server 才好調(diào)用不同的存儲(chǔ)引擎。

像上面提到的返回這個(gè)表的第一行就是一個(gè)標(biāo)準(zhǔn)的接口，如果 name 這一列有索引的話，那就是走返回符合這個(gè)條件的第一行。從這里我們也可以得知走索引更好，因?yàn)檫@樣能利用索引快速過濾得到正確的數(shù)據(jù)，不走索引就是一條一條拉到 Server 層走 where 過濾。

還有就是上面提到的 MySQL 是邊查邊發(fā)的，其實(shí)稍微想想就知道，如果 MySQL 要等結(jié)果集全了之后再發(fā)送數(shù)據(jù)給客戶端，這樣的設(shè)計(jì)不僅慢，而且如此多的查詢需要緩存完整的結(jié)果集， MySQL 的內(nèi)存早就擠爆了。

至此，我相信你腦海里應(yīng)該可以浮現(xiàn)一條 SQL 的執(zhí)行路徑了，你已經(jīng)有點(diǎn)感覺了。

我再來豐富一下上面的圖，把優(yōu)化器之類的加上去。

對(duì)了，你可能在別的地方會(huì)看到還有個(gè)緩存組件，用于查詢緩存，具體做法就是將一個(gè)查詢語句作為 key ，將上一次請(qǐng)求的結(jié)果作為 value，存儲(chǔ)在緩存組件中，當(dāng)同樣的語句來查詢的時(shí)候即可立馬返回結(jié)果，不需要經(jīng)歷詞法、語法分析等以下的步驟。

這個(gè)東西在 MySQL 8.0 之后就被砍了，并且只要表有數(shù)據(jù)改動(dòng)緩存就失效了，在我們常見的 OLTP 場(chǎng)景下是個(gè)雞肋，索性就不畫了，清爽比較重要。

接下來，咱們看下兩大存儲(chǔ)引擎。

InnoDB 與 MyISAM

對(duì)于我們而言，最重要的是 InnoDB 這個(gè)存儲(chǔ)引擎，而 MyISAM 作為 5.5.8 版本之前的默認(rèn)引擎，那也得關(guān)注一波，畢竟人家也當(dāng)了這么久的老大哥，這點(diǎn)面子還是要給的。

我們先來看下MyISAM

MyISAM 是基于 ISAM 引擎而來的，支持全文檢索、數(shù)據(jù)壓縮、空間函數(shù)，不支持事務(wù)和行級(jí)鎖，只有表級(jí)別鎖，它適用于 OLAP 場(chǎng)景，也就是分析類的，基本上都是讀取，不會(huì)有什么寫入動(dòng)作的場(chǎng)景。

它的數(shù)據(jù)和索引是分離存儲(chǔ)的，也就是不在一個(gè)文件上，并且數(shù)據(jù)庫只會(huì)緩存索引文件，數(shù)據(jù)文件的緩存直接交給操作系統(tǒng)搞定。這有點(diǎn)奇怪，一般而言這種重要數(shù)據(jù)都會(huì)自行緩存管理，不過這好像也沒出啥問題?(不知道是否有做什么其他處理)

MyISAM 的索引也是 B+ 樹，只是不像 InnoDB 那種葉子節(jié)點(diǎn)會(huì)存儲(chǔ)完整的數(shù)據(jù)，MyISAM 的數(shù)據(jù)是獨(dú)立于索引單獨(dú)存儲(chǔ)的，所以主鍵和非主鍵索引差別不大。

還有一個(gè)情況就是 MyISAM 不支持崩潰后的安全恢復(fù)，而 InnoDB 有個(gè) redolog 可以支持安全恢復(fù)。

再有一點(diǎn)就是 MyISAM 寫入性能差。

因?yàn)殒i的粒度太粗了，不支持行鎖，只有表鎖，所以寫入的時(shí)候會(huì)對(duì)整張表加鎖。不過有個(gè)并發(fā)插入的開關(guān)，開啟之后當(dāng)數(shù)據(jù)中間沒有空洞的時(shí)候，也就是插入的新數(shù)據(jù)是從末尾插入時(shí)，讀取數(shù)據(jù)是不會(huì)阻塞的。

InnoDB

InnoDB 支持事務(wù)，實(shí)現(xiàn)了四種標(biāo)準(zhǔn)的隔離級(jí)別，利用 MVCC 來支持高并發(fā)，默認(rèn)事務(wù)隔離級(jí)別為可重復(fù)讀，支持行鎖，利用行鎖+間隙鎖提供可重復(fù)讀級(jí)別下防止幻讀的能力，支持崩潰后的數(shù)據(jù)安全恢復(fù)。

對(duì)了，還有支持外鍵，不過一般互聯(lián)網(wǎng)項(xiàng)目都不會(huì)用外鍵的，性能太差，利用業(yè)務(wù)代碼來實(shí)現(xiàn)約束即可。

InnoDB 的主鍵索引稱為聚簇索引，也就是數(shù)據(jù)和索引是放在一起的，這與 MyISAM 有所不同，并且它的輔助索引(非主鍵索引)只存儲(chǔ)索引值與主鍵，因此當(dāng)輔助索引不能覆蓋查詢的列時(shí)，需要通過找到的主鍵再去聚簇索引查詢數(shù)據(jù)，這個(gè)過程稱之為回表。

它之所以能取代 MyISAM 成為默認(rèn)引擎就是因?yàn)槭聞?wù)的支持，崩潰后的數(shù)據(jù)安全恢復(fù)，比較出名的就是 MVCC 、Next-key Lock、redolog、WAL、undolog。

還有 changebuffer、double write、read ahead、自適應(yīng)哈希索引等，這些之后的文章都會(huì)細(xì)細(xì)的盤一盤。

再提一下幻讀吧，幻讀指的是后面的查詢結(jié)果比前面查詢的結(jié)果多了，比如查詢 id 大于100的人，在同一個(gè)事務(wù)里的兩次查詢，第一次查出 50 條，第二次查出 51 條，這就叫幻讀。

而標(biāo)準(zhǔn)的 SQL 隔離級(jí)別定義里面，可重復(fù)讀是預(yù)防不了幻讀的，只是 InnoDB 利用 Next-key Lock 在可重復(fù)讀里面實(shí)現(xiàn)了防止幻讀的出現(xiàn)。

所以有些人可能會(huì)覺得奇怪，在網(wǎng)上看到一個(gè)表格里面說可重復(fù)讀是預(yù)防不了幻讀呀，怎么 InnoDB 的可重復(fù)讀又可以防止幻讀。

這是因?yàn)闃?biāo)準(zhǔn)是標(biāo)準(zhǔn)，如何實(shí)現(xiàn)還是看具體的數(shù)據(jù)庫。

日志

MySQL 的日志其實(shí)有很多，我們所關(guān)心的就是二進(jìn)制日志(binlog)、重做日志(redolog)、undolog(回滾日志)。

還有慢查詢?nèi)罩尽㈠e(cuò)誤日志、查詢?nèi)罩尽?/p>

這里還需要區(qū)分，什么叫邏輯日志，什么叫物理日志。

邏輯日志說白了可以認(rèn)為記錄的就是一條 SQL，屬于邏輯上的記錄。

物理日志說白了可以認(rèn)為就是內(nèi)存里面的某個(gè)地址的值是xxx，這樣粗略的理解先，之后再盤。

對(duì)了，binlog 是屬于 Server 的，redolog 和 undolog 是屬于 InnoDB 的，這個(gè)要搞清楚。

索引

其實(shí)我之前寫的兩個(gè)故事已經(jīng)把索引講了，可以點(diǎn)藍(lán)字查看。

索引這個(gè)知識(shí)點(diǎn)基本上等于面試必問，這里的重點(diǎn)就是 B+樹是如何存儲(chǔ)數(shù)據(jù)的，主鍵索引和非主鍵索引有什么區(qū)別。

這里先說下，主鍵索引和非主鍵索引，在 InnoDB 里又稱聚蔟索引和輔助索引(二級(jí)索引)。

如果是主鍵索引：

• 非葉子節(jié)點(diǎn)存儲(chǔ)主鍵和頁號(hào)
• 葉子節(jié)點(diǎn)存儲(chǔ)完整的數(shù)據(jù)
• 葉子節(jié)點(diǎn)之間有雙向鏈表鏈接，便于范圍查詢
• 葉子節(jié)點(diǎn)內(nèi)部有頁目錄，內(nèi)部記錄是單鏈表鏈接，通過頁目錄二分再遍歷鏈表即可得到對(duì)應(yīng)記錄。
• B+ 樹只能幫助快速定位到的是頁，而不是記錄。
• 頁大小默認(rèn)16k，是按照主鍵大小排序的，所以無序的記錄插入因?yàn)榕判驎?huì)插入到頁中間，又因?yàn)槿萘坑邢迺?huì)導(dǎo)致頁分裂存儲(chǔ)，性能比較差，所以主鍵要求有序。

如果是非主鍵索引：

• 和主鍵索引的差別就在于葉子節(jié)點(diǎn)存儲(chǔ)索引列和主鍵，沒有完整的數(shù)據(jù)。

所以說不要有事沒事就 select * ，因?yàn)槿绻緛碇灰樵兯饕械脑挘苯永幂o助索引可以直接返回，然后你偏偏要select * ，那就不得不通過 id 再去主鍵索引查找，浪費(fèi)。

然后就是 B 樹、B+樹、Hash 索引之類的。

Hash 等值查詢優(yōu)勢(shì)，范圍查詢不行。

B+ 樹相比 B 樹來說，葉子節(jié)點(diǎn)用雙向鏈表相連，范圍查詢好。

再者就是最左匹配原則、聯(lián)合索引、覆蓋索引、索引下推了。

最左匹配無非就是 like 需要xx%，不能%xx，稍微思考一下也不難理解，如果要查姓陳的，我通過前綴肯定能把姓陳的都過濾出來，其他的姓氏排除了。如果不給姓氏，想要找名字帶陳的，我就得把所有人的名字都掃描一遍才能知道。

然后就是多列索引的時(shí)候，必須給最左側(cè)索引作為查詢條件，才能利用上索引。

例如上面這樣的一個(gè)多列索引(姓，名)，如果你的查詢條件有姓氏，那就能用上索引，如果沒有姓，只有名字，則用不上。

再說聯(lián)合索引，拿上面的例子來說，如果你分別建立了姓和名兩個(gè)索引，但是經(jīng)常兩個(gè)條件放在一起查詢，那么就應(yīng)該將兩個(gè)索引合二為一，變成上面所說的多列索引，也就是聯(lián)合索引。

當(dāng)然上面的例子不恰當(dāng)，姓名往往放一個(gè)字段就行，我就是舉個(gè)例子。

之所以把索引聯(lián)合了是因?yàn)樗饕木S護(hù)需要開銷，舉個(gè)簡單的例子，如果你插入一條數(shù)據(jù)，那么不僅要插入主鍵索引，你所有的輔助索引都需要插入，那索引多了，開銷自然就大了，刪除更新也是一樣。

覆蓋索引，指的是利用輔助索引可以直接返回?cái)?shù)據(jù)，雖說上文已經(jīng)提了，我還是再說一遍。

比如select 名 from yes where 姓 = 陳，這就是利用上面的索引直接返回，因?yàn)樗饕牧懈采w了需要查詢的結(jié)果，如果你來個(gè) select age，那就需要去主鍵索引查詢了，因?yàn)檩o助索引沒有 age 這一列的數(shù)據(jù)。

索引下推，還是拿上面的索引作為例子，此時(shí)要執(zhí)行select * from yes where 姓 = 陳 and 名 like %南%如果沒有索引下推，那么查詢的情況就是只能利用姓這個(gè)條件，會(huì)把 ID 為 2 和 12 的數(shù)據(jù)都返回，然后都需要回表，再利用 Server 的 where 來做過濾。

而如果用上了索引下推，那么會(huì)把名 like %南%這個(gè)過濾條件也下推給索引，在取出結(jié)果之前先通過 where 過濾了，然后再得到數(shù)據(jù)，這樣直接就排除了 ID 為 2 的數(shù)據(jù)，只需要回表 ID 為 12 的數(shù)據(jù)。

其實(shí)我以前就認(rèn)為查詢本就是按索引下推的方式來查的，想不到這是 5.6 版本之后才出的一個(gè)優(yōu)化。

后來理解了 MySQL 的體系結(jié)構(gòu)之后覺得也正常，畢竟存儲(chǔ)引擎就是個(gè)沒有感情的數(shù)據(jù)讀寫工具人，就像飲水機(jī)(存儲(chǔ)引擎)只會(huì)出冷水或者熱水，適合溫度的水還需要你(Server)自己調(diào)。只不過現(xiàn)在科技在進(jìn)步，所以搞出了可以直接出合適溫度的飲用水的飲水機(jī)。

對(duì)了，索引下推只能在輔助索引上用，這應(yīng)該不難理解吧。

最后

暫時(shí)第一篇就寫這么多了，知識(shí)點(diǎn)還是很密集的。

這篇大致就寫了思維導(dǎo)圖的右上角的小部分，而且還沒有很深入，我是打算把思維導(dǎo)圖上的東西先粗略地過一遍，然后再逐一擊破。

不過其實(shí)也不是很粗略，我覺得大體的重點(diǎn)還是講明白了的吧?如果有建議或者錯(cuò)誤歡迎騷擾。