[[399878]]

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

你好，我是 yes。

先來個，開局一張圖。

這圖算是第一版本，本來還想填充地更詳細些，但是看著感覺好冗余，暫時就先這樣吧，主要是用來標注一些關鍵點，便于復習。

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

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

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

MySQL 體系結構

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

MySQL 體系結構可以分為兩大塊來看，分別是：Server 和存儲引擎。

當客戶端與 MySQL 建立連接之后，一條 SQL 語句經過 TCP 從客戶端傳輸到 Server ，Server 會先將語句進行詞法分析與語法分析，這個工作是分析器做的。

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

確認語法沒問題之后，會再經由優化器來決策這條語句是否需要重寫，如何選擇驅動表，如何選擇合適的索引等操作，目的就是讓語句更高效的執行。

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

至此，MySQL 已經知道該做什么和怎么做了，此時就是執行器干活時候了，它會調用存儲引擎的接口來執行語句。

第一個關鍵點來了。

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

此時流程如下：

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

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

然后就是存儲引擎的接口。MySQL 的存儲引擎是插件式的，一個數據庫里面的不同表可以用不同的存儲引擎，而 Server 都是同一個，所以需要規定好統一的接口，這樣 Server 才好調用不同的存儲引擎。

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

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

至此，我相信你腦海里應該可以浮現一條 SQL 的執行路徑了，你已經有點感覺了。

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

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

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

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

InnoDB 與 MyISAM

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

我們先來看下MyISAM

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

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

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

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

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

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

InnoDB

InnoDB 支持事務，實現了四種標準的隔離級別，利用 MVCC 來支持高并發，默認事務隔離級別為可重復讀，支持行鎖，利用行鎖+間隙鎖提供可重復讀級別下防止幻讀的能力，支持崩潰后的數據安全恢復。

對了，還有支持外鍵，不過一般互聯網項目都不會用外鍵的，性能太差，利用業務代碼來實現約束即可。

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

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

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

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

而標準的 SQL 隔離級別定義里面，可重復讀是預防不了幻讀的，只是 InnoDB 利用 Next-key Lock 在可重復讀里面實現了防止幻讀的出現。

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

這是因為標準是標準，如何實現還是看具體的數據庫。

日志

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

還有慢查詢日志、錯誤日志、查詢日志。

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

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

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

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

索引

其實我之前寫的兩個故事已經把索引講了，可以點藍字查看。

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

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

如果是主鍵索引：

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

如果是非主鍵索引：

• 和主鍵索引的差別就在于葉子節點存儲索引列和主鍵，沒有完整的數據。

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

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

Hash 等值查詢優勢，范圍查詢不行。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最后

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

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

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