一條SQL語句的奇妙旅程

MySQL的邏輯架構

1.  連接器：負責用戶的身份認證和許可權校驗。

2. 查詢緩存：這個在8.0以後的版本已經取締了，但是不影響設計思想的瞭解，即：當有一個SQL進來的時候，先會去匹配SQL語句，如果本地已經有緩存，即直接讀緩存，返回結果。乍一聽挺好的功能，為什麼會被取締呢？這存在一些設計理念的問題，MySQL追求極高的性能，該功能在實際使用過程中弊大於利，寫緩存/清緩存的過程可能會大幅度影響性能

3. 分析器/解析器：這個模塊主要是進行一些語法分析/詞法分析，大家可能覺得與自己關係不太大，其實我們初學SQL語句的時候第一個打交道的就是這個模塊，比如當你輸入錯誤語句的時候，語法的錯誤在這層就已經發現了。

4. 預處理器：這個模塊會確定你輸入的表和列是否真實存在，欄位別名是否有歧義，主要是當你語法沒錯的時候，確定是否符合這個場景。

5. 查詢優化器：這可以說是server層最核心也是實戰最重要的地方了，首先，我們每次可以通過explain+sql來查看當前語句的各個屬性，這些屬性能給我們調優很好的建議。這點會在下篇文章中細談，這篇文章總體是為了打通全鏈路。
   其次，我們可以根據show warnings來查看查詢優化器到底做了哪些優化，從而考慮優化是否得當。

6. 執行引擎：這塊主要是對結果進行過濾和排序，這個模塊的命名有些歧義，其實真實的數據檢索並非在這裡進行，這層進行的是對檢索的結果集進行一次過濾和排序。

7. 存儲引擎：這裡就是真正的核心了，也就是我們一直所說的innodb、myisam等存儲引擎工作的地方，我們所有的查詢檢索任務都是在這裡進行的，所有的存儲也是在這裡進行。這個模塊也會在之後的文章中分析。

現在來假設一條SQL來做全套了，那麼它會是怎樣的一個邏輯呢？

首先我們客戶端與伺服器之間會建立一個連接（這裡的連接是指那幾種進程通信方式中的一種，大部分情況下說的是TCP/IP進行連接的，過程中需要我們mysql -u root -p輸入密碼，就可以進入mysql。

此時我們可以通過SQL語句進行增刪改查，當我們輸入一條語句，倘若在5.8版本之前，這條語句會進入查詢緩存，在這個模塊中先對SQL語句進行匹配，倘若找到完全匹配的緩存，則讀緩存返回結果，倘若沒有匹配的，則會進入下一模塊。查詢到的結果會寫進寫緩存這也是之所以在5.8以後取締掉查詢緩存的原因：寫緩存對性能是有一定的影響的，事實上這個影響甚至大於其對速度的提升。

進入下一模塊以後，會對語句進入一些詞法分析和語法分析，看看有沒有語法上的錯誤，初學者經常被卡在這個模塊，因為語句寫的不規範之類的問題。

再到後來，這條語句會進入預處理器，也就是看看你雖然說的都對，語法都對，但是你搜索的列如果是我根本沒有的，那相當於什麼都沒有，如果一切正常，就再往後走。

此時進入了server層最核心的查詢優化器，這裡MySQL有自己的想法， 他會對你輸入的語句進行一些優化和重排，這裡有一個很細節但是很核心的點：任何關聯進入查詢優化器都會變成嵌套迴圈關聯，也就是說，很多複雜的關聯都會變成類似於左連接之類的關聯，其原因也非常簡單，正如上述：執行引擎是對結果進行過濾和排序，那麼如果我們可以通過優化語句提前對語句進行過濾，這樣就可以大幅度提高性能。當我們查詢Explain+sql語句的時候會出現很多的屬性，我們需要註意的是要避免外部排序的產生，因為這樣會產生巨大的性能影響。

查詢優化器畢竟不是十全十美的，它的很多優化可能是好心沒做好事，最典型的就是臨時表了，首先臨時表這個東西本身是非常好的，它將中間過程的一些結果集存儲在記憶體上，很有利於查詢過程的執行，但是有可能記憶體不足的情況下，會將數據存儲在磁碟上，而磁碟參與讀寫一定會帶來非必要的資源和性能消耗，因此我們必須結合實際情況考究某些屬性的存在是否合理。

查詢優化器之後，查詢進入了執行引擎，在這裡其實並未進行太多的事情，執行引擎主要是調度存儲引擎，然後存儲引擎在硬碟中去檢索，然後拿到結果返回執行引擎，由執行引擎進行結果的過濾和排序，然後返回結果集回到客戶端，如果是在5.8以前的話，會放入寫緩存。

接下來就是核心的問題了：存儲引擎。

我們常用的存儲引擎大概是innodb和myisam，這兩任預設引擎撐起了MySQL的半邊天，現在我會比較粗略的介紹一下兩個引擎，詳細的文章會在後期輸出。

innodb存儲引擎與myisam存儲引擎

首先我們先認識所謂存儲引擎最重要的兩個功能就是存儲與查詢，那麼我們從存儲和查詢兩個方面來分析一下這兩個引擎的特點。

存儲方面：
首先當我們存儲數據的時候，因為這兩個引擎都是基於磁碟的，innodb是將索引和數據放在一起的，這跟其索引結構有關，當進行存儲的時候，innodb將所有的數據都存儲在一起；而myisam則是將索引數據和真實數據分開存儲，這樣其實是通過索引進行檢索，得到的結果是一個真實數據的指針，然後進入數據文件中進行隨機io。這就決定了一點，如果我們是innodb存儲引擎的話，主鍵儘量自增，因為如果非自增，會導致不能進行順序io，性能會有很大的退化。而myisam的數據存儲位置不能輕易移動，因為這樣會導致索引失效，也會影響性能。

查詢方面：
查詢就像翻詞典一樣，索引就像目錄和頁碼，這是非常重要的一部分，很多初學者覺得自己非常少用到索引，其實不然，我們經常用到的主鍵便是最標準的索引，當你的查詢命中索引的時候，便可以進入高速列車，為什麼呢？因為數據結構，在此強調一下，一直很認同程式=數據結構+演算法，數據結構非常重要，如果我們能命中索引，就可以進入索引的數據結構，此時要麼是哈希索引，要麼是b+樹索引，這兩種索引一個的複雜度為O(1)，一個是O(log(M)N），其中M為索引關鍵字，N為總關鍵字數量，很容易看到，這種速度比全表遍歷好了太多了，所以如果命中索引的話，就可以大幅度提高速率。

當然這兩點雖然重要，但是都不是兩種引擎最大的區別，這兩種存儲引擎最大的差別在於事務性和鎖的粒度上，這也是innodb彎道超車最重要的原因。

事務性

隨著資料庫的應用場景越來越多，我們對數據的安全性有了越來越多的需求，myisam不支持事務，這導致這種存儲引擎很快的落時了。

鎖粒度

並不是說myisam完全不考慮數據安全性，只是它的粒度有些太大了，它為了數據的安全直接動用了表級鎖，直接導致性能影響太大。