[[399878]]

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

你好，我是 yes。

先來個，開局一張圖。

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

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

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

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

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

這個非常重要，理解了之后后面的一些知識點才能懂，比如索引下推。

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

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

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

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

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

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

第一個關(guān)鍵點來了。

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

此時流程如下：

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

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

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

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

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

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

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

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

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

接下來，咱們看下兩大存儲引擎。

InnoDB 與 MyISAM

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

我們先來看下MyISAM

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

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

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

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

再有一點就是 MyISAM 寫入性能差。

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

InnoDB

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

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

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

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

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

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

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

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

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

日志

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

還有慢查詢?nèi)罩?、錯誤日志、查詢?nèi)罩尽?/p>

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

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

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

對了，binlog 是屬于 Server 的，redolog 和 undolog 是屬于 InnoDB 的，這個要搞清楚。

索引

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

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

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

如果是主鍵索引：

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

如果是非主鍵索引：

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

所以說不要有事沒事就 select * ，因為如果本來只要查詢索引列的話，直接利用輔助索引可以直接返回，然后你偏偏要select * ，那就不得不通過 id 再去主鍵索引查找，浪費。

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

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

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

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

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

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

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

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

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

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

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

比如select 名 from yes where 姓 = 陳，這就是利用上面的索引直接返回，因為索引的列覆蓋了需要查詢的結(jié)果，如果你來個 select age，那就需要去主鍵索引查詢了，因為輔助索引沒有 age 這一列的數(shù)據(jù)。

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

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

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

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

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

最后

暫時第一篇就寫這么多了，知識點還是很密集的。

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

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