全解MySQL之架構篇：自頂向下深入剖析MySQL整體架構

引言

無論你是前端還是後端，隻要是一個合格的開發者，對于MySQL這個名詞相信都不陌生，MySQL逐漸成為了最受歡迎的關系型資料庫，無論你是大前端，亦或是Java、Go、Python、C/C++、PHP....等這些語言的程式員，對于MySQL是必然要掌握的核心技術之一，程式員不能沒有MySQL，就像西方不能失去耶路撒冷一般。

當然，MySQL也不僅僅是唯一的資料庫，與它類似的關系型資料庫競品還有很多，例如Oracle、SQLServer、PostgreSQL、DB2....，這其中使用最為廣泛的是Oracle，但Oracle實際上并不怎麼受程式員歡迎，或者說Oracle并不怎麼受中小企業的Boss歡迎，原因嘛大家都清楚，無非因為它收費罷了。

也正是由于Oracle收費的原因，才導緻MySQL像如今這麼流行，正所謂時勢造英雄，MySQL作為免費的開源資料庫，也正是抓住了這個風口，是以才越發流行。對于MySQL，用一句話形容很貼切：“天不生我MySQL，程式設計萬古如長夜”。

一、MySQL概述與系列預告

MySQL資料庫是由瑞典的MySQL AB公司開發的，後面這家企業被Sun公司收購，最後Sun公司又被Oracle以74億美元收購，是以本質上MySQL現在隸屬于Oracle旗下，是以大家也會發現，MySQL後面的高版本會有收費版出現。

實際上如果MySQL沒有并入Oracle的話，是有很大幾率問鼎資料庫榜首的，造化弄人。

當然，雖然MySQL出了收費版，但Oracle也沒有趕盡殺絕，而是向MySQL的使用者給出了《十項承諾》，是以我們如今依舊可以使用開源版的MySQL。

不過對于這些理論概念就不過多介紹了，因為對于資料庫的基礎操作知識相信大家都已具備

二、MySQL整體結構淺析

MySQL與我們開發項目時相同，為了能夠合理的規劃整體架構設計，也會将整個MySQL服務抽象成幾個大的子產品，然後在内部進行實作，是以先來看看MySQL的整體架構，開局先上一張圖：

從上往下看，依次會分為網絡連接配接層、系統服務層、存儲引擎層、以及檔案系統層，往往編寫SQL後，都會遵守着MySQL的這個架構往下走。

• 連接配接層：主要是指資料庫連接配接池，會負責處理所有用戶端接入的工作。
• 服務層：主要包含SQL接口、解析器、優化器以及緩存緩沖區四塊區域。
• 存儲引擎層：這裡是指MySQL支援的各大存儲引擎，如InnoDB、MyISAM等。
• 檔案系統層：涵蓋了所有的日志，以及資料、索引檔案，位于系統硬碟上。

OK~，除了上述的四層外，還有用戶端，這個用戶端可以是各類程式設計語言，如Java、Go、Python、C/C++、PHP、Node、.Net....，也可以是一些資料庫的可視化軟體，例如Navicat、SQLyog等，也可以是mysql-cli指令行工具。總之，隻要能與MySQL建立網絡連接配接，都可以被稱為是MySQL的用戶端。

MySQL-Server就是上述圖中的那玩意兒，一般來說，用戶端負責編寫SQL，而服務端則負責SQL的執行與資料的存儲。

對MySQL的整體架構有了簡單了解後，接下來詳細的拆解一下MySQL-Server的每個層面。

三、網絡連接配接層

當一個用戶端嘗試與MySQL建立連接配接時，MySQL内部都會派發一條線程負責處理該用戶端接下來的所有工作。而資料庫的連接配接層負責的就是所有用戶端的接入工作，MySQL的連接配接一般都是基于TCP/IP協定建立網絡連接配接，是以凡是可以支援TCP/IP的語言，幾乎都能與MySQL建立連接配接。

其實MySQL還支援另一種連接配接方式，就是Unix系統下的Socket直連，但這種方式一般使用的較少。

雖然MySQL是基于TCP/IP協定棧實作的連接配接建立工作，但并非使用HTTP協定建立連接配接的，一般建立連接配接的具體協定，都會根據不同的用戶端實作，如jdbc、odbc...這類的。在這裡先暫且不糾結連接配接MySQL時的協定類型，先來看看一般是怎麼連接配接MySQL的？如下：

mysql -h 127.0.0.1 -uroot -p123456

例如上述這條指令，-h表示MySQL所在的伺服器IP位址，-u表示本次連接配接所使用的使用者名，-p則代表着目前使用者的賬号密碼，當執行這條指令後，會與MySQL-Server建立網絡連接配接，也就是會經曆《TCP的三次握手過程》。當然，MySQL也支援SSL加密連接配接，如果采用這種方式建立連接配接，那還會經過《SSL多次握手過程》，當握手結束，網絡建立成功後，則會開始正式的資料庫連接配接建立工作。

TCP網絡連接配接建立成功後，MySQL服務端與用戶端之間會建立一個session會話，緊接着會對登入的使用者名和密碼進行效驗，MySQL首先會查詢自身的使用者表資訊，判斷輸入的使用者名是否存在，如果存在則會判斷輸入的密碼是否正确，如若密碼錯誤或使用者名不存在就會傳回1045的錯誤碼，如下資訊：

ERROR 1045 (28000): Access denied for user 'zhuzi'@'localhost' (using password: YES)

如果你在連接配接資料庫的過程中，出現了上述的錯誤資訊，那絕對是你輸入的使用者名或密碼錯誤導緻的，當賬号及密碼正确時，此時就會進入MySQL的指令行，接下來可以執行SQL操作。

但實際上，在使用者名和密碼都正确的情況下，MySQL還會做一些些小動作，也就是會進行授權操作，查詢每個使用者所擁有的權限，并對其授權，後續SQL執行時，都會先判斷是否具備執行相應SQL語句的權限，然後再執行。

OK~，經過上述流程後資料庫連接配接就建立成功了，資料庫連接配接建立成功後，MySQL與用戶端之間會采用半全工的通訊機制工作，與之對應的還有“雙全工、單工”的工作模式：

• 雙全工：代表通訊的雙方在同一時間内，即可以發送資料，也可以接收資料。
• 半全工：代表同一時刻内，單方要麼隻能發送資料，要麼隻能接受資料。
• 單工：目前連接配接隻能發送資料或隻能接收資料，也就是“單向類型的通道”。

到這裡，MySQL也會“安排”一條線程維護目前用戶端的連接配接，這條線程也會時刻辨別着目前連接配接在幹什麼工作，可以通過show processlist;指令查詢所有正在運作的線程：

執行結果如下(root賬号可以查詢所有線程)：

• Id：目前線程的ID值，可以利用這個ID，使用kill強殺線程。
• User：目前線程維護的資料庫連接配接，與之對應的使用者是誰。
• Host：與目前線程保持連接配接關系的用戶端位址（IP+Port）。
• db：目前線程在哪個資料庫中執行SQL。
• Command：目前線程正在執行的SQL類型，如： Create DB：正在執行建立資料庫的操作。 Drop DB：正在執行删除資料庫的操作。 Execute：正在執行預編譯的SQL（PreparedStatement）。 Close Stmt：正在關閉一個PreparedStatement。 Query：正在執行普通的SQL語句。 Sleep：正在等待用戶端發送SQL語句。 Quit：目前用戶端正在退出連接配接。 Shutdown：正在關閉MySQL服務端。
• Time：表示目前線程處于目前狀态的時間，機關是秒。
• State：表示目前線程的狀态，有如下幾種： Updating：目前正在執行update語句，比對資料做修改操作。 Sleeping：正在等待用戶端發送新的SQL語句。 Starting：目前正在處理用戶端的請求。 Checking table：目前正在表中查詢資料。 Locked：目前線程被阻塞，其他線程擷取了執行需要的鎖資源。 Sending Data：目前執行完成了Select語句，正在将結果傳回給用戶端。
• Info：一般記錄目前線程正在執行的SQL，預設顯示前一百個字元，檢視完整的SQL可以使用show full processlist;指令。

其實從這個結果上來看，我們能夠很明顯地看到資料庫中各個線程的資訊，這條指令對于以後做線上排查時有很大的作用，目前先簡單了解，接着來看看資料庫連接配接池。

3.1、資料庫連接配接池(Connection Pool)

Connection Pool翻譯過來的意思就是連接配接池，那為什麼需要有這個東西呢？因為前面聊到過，所有的用戶端連接配接都需要一條線程去維護，而線程資源無論在哪裡都屬于寶貴資源，是以不可能無限量建立，是以這裡的連接配接池就相當于Tomcat中的線程池，主要是為了複用線程、管理線程以及限制最大連接配接數的。

連接配接池的最大線程數可以通過參數max-connections來控制，如果到來的用戶端連接配接超出該值時，新到來的連接配接都會被拒絕，關于最大連接配接數的一些指令主要有兩條：

• show variables like '%max_connections%';：查詢目前DB的最大連接配接數。
• set GLOBAL max_connections = 200;：修改資料庫的最大連接配接數為指定值。

對于不同的機器配置，可以适當地調整連接配接池的最大連接配接數大小，以此可以在一定程度上提升資料庫的性能。除了可以查詢最大連接配接數外，MySQL本身還會對用戶端的連接配接數進行統計，對于這點可以通過指令show status like "Threads%";查詢：

其中各個字段的釋義如下：

• Threads_cached：目前空閑的資料庫連接配接數。
• Threads_connected：目前資料庫存活的資料庫連接配接數。
• Threads_created：MySQL-Server運作至今，累計建立的連接配接數。
• Threads_running：目前正在執行的資料庫連接配接數。

對于幾個字段很容易了解，額外要說明的一點是Threads_cached這個字段，從名稱上來看，似乎跟緩存有關系，其實也沒錯，因為這裡是有一個資料庫内部的優化機制。當一個用戶端連接配接斷開後，對于資料庫連接配接卻不會立馬銷毀，而是會先放入到一個緩存連接配接池當中。這樣就能在下次新連接配接到來時，省去了建立線程、配置設定棧空間等一系列動作，但這個值不會是無限大的，一般都在32左右。

連接配接池的優化思想與Java線程池相同，會将資料庫建立出的連接配接對象放入到一個池中，一旦出現新的通路請求會複用這些連接配接，一方面提升了性能，第二方面還節省了一定程度上的資源開銷。

四、系統服務層

學習了MySQL網絡連接配接層後，接下來看看系統服務層，MySQL大多數核心功能都位于這一層，包括用戶端SQL請求解析、語義分析、查詢優化、緩存以及所有的内置函數（例如：日期、時間、統計、加密函數...），所有跨引擎的功能都在這一層實作，譬如存儲過程、觸發器和視圖等一系列服務。

也就是上述這幾部分，主要包含SQL接口、解析器、優化器以及緩存相關的這些部分。當然，也許你會問我還有一個[管理服務&工具元件]呢，這塊其實屬于全局的，屬于MySQL的基礎設施服務，接下來一個個的講一下服務層的各個細節吧。

4.1、SQL接口

SQL接口元件，這個名詞聽上去似乎不太容易了解，其實主要作用就是負責處理用戶端的SQL語句，當用戶端連接配接建立成功之後，會接收用戶端的SQL指令，比如DML、DDL語句以及存儲過程、觸發器等，當收到SQL語句時，SQL接口會将其分發給其他元件，然後等待接收執行結果的傳回，最後會将其傳回給用戶端。

簡單來說，也就是SQL接口會作為用戶端連接配接傳遞SQL語句時的入口，并且作為資料庫傳回資料時的出口。

對于這個元件沒太多好聊的，簡單展開兩點叙述一下後就結束這個話題，第一點是對于SQL語句的類型劃分，第二點則是觸發器。在SQL中會分為五大類：

• DML：資料庫操作語句，比如update、delete、insert等都屬于這個分類。
• DDL：資料庫定義語句，比如create、alter、drop等都屬于這個分類。
• DQL：資料庫查詢語句，比如最常見的select就屬于這個分類。
• DCL：資料庫控制語句，比如grant、revoke控制權限的語句都屬于這個分類。
• TCL：事務控制語句，例如commit、rollback、setpoint等語句屬于這個分類。

再來聊一聊MySQL的觸發器，這東西估計大部分小夥伴沒用過，但它在有些情景下還較為實用，不過想要了解觸發器是什麼，首先咱們還得先了解存儲過程。

存儲過程：是指提前編寫好的一段較為常用或複雜SQL語句，然後指定一個名稱存儲起來，然後先經過編譯、優化，完成後，這個“過程”會被嵌入到MySQL中。

也就是說，[存儲過程]的本質就是一段預先寫好并編譯完成的SQL，而我們要聊的觸發器則是一種特殊的存儲過程，但[觸發器]與[存儲過程]的不同點在于：存儲過程需要手動調用後才可執行，而觸發器可由某個事件主動觸發執行。在MySQL中支援INSERT、UPDATE、DELETE三種事件觸發，同時也可以通過AFTER、BEFORE語句聲明觸發的時機，是在操作執行之前還是執行之後。

說簡單一點，[MySQL觸發器]就類似于Spring架構中的AOP切面。

OK~，至此就先打住，對于這些概念暫且了解到這裡，後續會專門去聊MySQL的存儲過程、觸發器、視圖等這些特殊的操作。

4.2、解析器

用戶端連接配接發送的SQL語句，經過SQL接口後會被分發到解析器，解析器這東西其實在所有語言中都存在，Java、C、Go...等其他語言都有，解析器的作用主要是做詞法分析、語義分析、文法樹生成...這類工作的，Java源碼在編寫後，會經曆這個過程，SQL語言同樣類似。

而解析器這一步的作用主要是為了驗證SQL語句是否正确，以及将SQL語句解析成MySQL能看懂的機器碼指令。稍微拓展一點大家就明白了，好比如我們編寫如下一條SQL：

select * form user;

然後運作會得到如下錯誤資訊：

ERROR 1064 (42000): You have an error in your SQL syntax; check....

在上述SQL中，我們将from寫成了form，結果運作時MySQL提升文法錯誤了，MySQL是如何發現的呢？就是在詞法分析階段，檢測到了存在文法錯誤，是以抛出了對應的錯誤碼及資訊。當然，如果SQL正确，則會進行下一步工作，生成MySQL能看懂的執行指令。

4.3、優化器

解析器完成相應的詞法分析、文法樹生成....等一系列工作後，緊接着會來到優化器，優化器的主要職責在于生成執行計劃，比如選擇最合适的索引，選擇最合适的join方式等，最終會選擇出一套最優的執行計劃。

當然，在這裡其實有很多資料也會聊到，存在一個執行器的抽象概念，實際上執行器是不存在的，是以前面聊到過，每個用戶端連接配接在MySQL中都用一條線程維護，而線程是作業系統的最小執行機關，是以所謂的執行器，本質上就是線程本身。

優化器生成了執行計劃後，維護目前連接配接的線程會負責根據計劃去執行SQL，這個執行的過程實際上是在調用存儲引擎所提供的API。

4.4、緩存&緩沖

這塊較為有趣，主要分為了讀取緩存與寫入緩沖，讀取緩存主要是指select語句的資料緩存，當然也會包含一些權限緩存、引擎緩存等資訊，但主要還是select語句的資料緩存，MySQL會對于一些經常執行的查詢SQL語句，将其結果儲存在Cache中，因為這些SQL經常執行，是以如果下次再出現相同的SQL時，能從記憶體緩存中直接命中資料，自然會比走磁盤效率更高，對于Cache是否開啟可通過指令查詢。

• show global variables like "%query_cache_type%";：查詢緩存是否開啟。
• show global variables like "%query_cache_size%";：查詢緩存的空間大小。

同時還可以通過show status like'%Qcache%';指令查詢緩存相關的統計資訊。

• Qcache_free_blocks：查詢緩存中目前還有多少剩餘的blocks。
• Qcache_free_memory：查詢緩存的記憶體大小。
• Qcache_hits：表示有多少次查詢SQL命中了緩存。
• Qcache_inserts：表示有多少次查詢SQL未命中緩存然後走了磁盤。
• Qcache_lowmem_prunes：這個值表示有多少條緩存資料從記憶體中被淘汰。
• Qcache_not_cached：表示由于自己設定了緩存規則後，有多少條資料不符合緩存條件。
• Qcache_queries_in_cache：表示目前緩存中緩存的資料數量。
• Qcache_total_blocks：目前緩存區中blocks的數量。

當然，由于我是MySQL5.7版本，是以對于這些依舊可以查詢到，但是在高版本的MySQL中，移除了查詢緩存區，畢竟命中率不高，而且查詢緩存這一步還要帶來額外開銷，同時一般程式都會使用Redis做一次緩存，是以結合多方面的原因就移除了查詢緩存的設計。

簡單了解了查詢緩存後，再來看看寫入緩沖，這也是我說的比較有趣的點，緩沖區的設計主要是：為了通過記憶體的速度來彌補磁盤速度較慢對資料庫造成的性能影響。在資料庫中讀取某頁資料操作時，會先将從磁盤讀到的頁存放在緩沖區中，後續操作相同頁的時候，可以基于記憶體操作。

一般來說，當你對資料庫進行寫操作時，都會先從緩沖區中查詢是否有你要操作的頁，如果有，則直接對記憶體中的資料頁進行操作（例如修改、删除等），對緩沖區中的資料操作完成後，會直接給用戶端傳回成功的資訊，然後MySQL會在背景利用一種名為Checkpoint的機制，将記憶體中更新的資料刷寫到磁盤。

MySQL在設計時，通過緩沖區能減少大量的磁盤IO，進而進一步提高資料庫整體性能。畢竟每次操作都走磁盤，性能自然上不去的。

PS：後續高版本的MySQL移除了查詢緩存區，但并未移除緩沖區，這是兩個概念，請切記！

同時緩沖區是與存儲引擎有關的，不同的存儲引擎實作也不同，比如InnoDB的緩沖區叫做innodb_buffer_pool，而MyISAM則叫做key_buffer。

五、存儲引擎層

存儲引擎也可以了解成MySQL最重要的一層，在前面的服務層中，聚集了MySQL所有的核心邏輯操作，而引擎層則負責具體的資料操作以及執行工作。

如果有小夥伴研究過Oracle、SQLServer等資料庫的實作，應該會發現這些資料庫隻有一個存儲引擎，因為它們是閉源的，是以僅有官方自己提供的一種引擎。而MySQL則因為其開源特性，是以存在很多很多款不同的存儲引擎實作，MySQL為了能夠正常搭載不同的存儲引擎運作，是以引擎層是被設計成可拔插式的，也就是可以根據業務特性，為自己的資料庫選擇不同的存儲引擎。

MySQL的存儲引擎主要分為官方版和民間版，前者是MySQL官方開發的，後者則是第三方開發的。存儲引擎在MySQL中，相關的規範标準被定義成了一系列的接口，如果你也想要使用自己開發的存儲引擎，那麼隻需要根據MySQL AB公司定義的準則，編寫對應的引擎實作即可。

MySQL目前有非常多的存儲引擎可選擇，其中最為常用的則是InnoDB與MyISAM引擎，可以通過show variables like '%storage_engine%';指令來檢視目前所使用的引擎。其他引擎如下：

存儲引擎是MySQL資料庫中與磁盤檔案打交道的子系統，不同的引擎底層通路檔案的機制也存在些許細微差異，引擎也不僅僅隻負責資料的管理，也會負責庫表管理、索引管理等，MySQL中所有與磁盤打交道的工作，最終都會交給存儲引擎來完成。

六、檔案系統層

這一層則是MySQL資料庫的基礎，本質上就是基于機器實體磁盤的一個檔案系統，其中包含了配置檔案、庫表結構檔案、資料檔案、索引檔案、日志檔案等各類MySQL運作時所需的檔案，這一層的功能比較簡單，也就是與上層的存儲引擎做互動，負責資料的最終存儲與持久化工作。

這一層主要可分為兩個闆塊：①日志闆塊。②資料闆塊。

6.1、日志子產品

在MySQL中主要存在七種常用的日志類型，如下：

• ①binlog二進制日志，主要記錄MySQL資料庫的所有寫操作（增删改）。
• ②redo-log重做/重寫日志，MySQL崩潰時，對于未落盤的操作會記錄在這裡面，用于重新開機時重新落盤（InnoDB專有的）。
• ③undo-logs撤銷/復原日志：記錄事務開始前[修改資料]的備份，用于復原事務。
• ④error-log：錯誤日志：記錄MySQL啟動、運作、停止時的錯誤資訊。
• ⑤general-log正常日志，主要記錄MySQL收到的每一個查詢或SQL指令。
• ⑥slow-log：慢查詢日志，主要記錄執行時間較長的SQL。
• ⑦relay-log：中繼日志，主要用于主從複制做資料拷貝。

上述列出了MySQL中較為常見的七種日志，但實際上還存在很多其他類型的日志，不過一般對調優、排查問題、資料恢複/遷移沒太大幫助，用的較少，是以不再列出。

6.2、資料子產品

前面聊到過，MySQL的所有資料最終都會落盤（寫入到磁盤），而不同的資料在磁盤空間中，存儲的格式也并不相同，是以再列舉出一些MySQL中常見的資料檔案類型：

• db.opt檔案：主要記錄目前資料庫使用的字元集和驗證規則等資訊。
• .frm檔案：存儲表結構的中繼資料資訊檔案，每張表都會有一個這樣的檔案。
• .MYD檔案：用于存儲表中所有資料的檔案（MyISAM引擎獨有的）。
• .MYI檔案：用于存儲表中索引資訊的檔案（MyISAM引擎獨有的）。
• .ibd檔案：用于存儲表資料和索引資訊的檔案（InnoDB引擎獨有的）。
• .ibdata檔案：用于存儲共享表空間的資料和索引的檔案（InnoDB引擎獨有）。
• .ibdata1檔案：這個主要是用于存儲MySQL系統（自帶）表資料及結構的檔案。
• .ib_logfile0/.ib_logfile1檔案：用于故障資料恢複時的日志檔案。
• .cnf/.ini：MySQL的配置檔案，Windows下是.ini，其他系統大多為.cnf。
• ......

上述列舉了一些MySQL中較為常見的資料檔案類型，無論是前面的日志檔案，亦或是現在的資料檔案，這些都是後續深入剖析MySQL時會遇到的，是以在這裡先有個簡單認知，友善後續更好的了解MySQL。

當然，上述并沒有完全列出MySQL所有的日志類型和檔案類型，大家有興趣的可以去自行翻看一下安裝MySQL的目錄，你會找其中找到很多其他類型的日志或資料檔案~

七、MySQL架構篇小結

看到這裡，《MySQL架構篇》就已經接近尾聲啦，本文的主要目的是在于先對MySQL的整體架構有一個基本認知，這也為咱們後續的文章打下了堅實的基礎，因為畢竟想要深入研究一個技術，那定然不能如同管中窺豹一般，僅看一個細節點，而是更應該是先窺其全貌，再深入細節。

這裡也是學習底層、源碼、原理、調優等知識的一個小技巧，如果隻關注于某一個點，很容易出現“不識廬山真面目，隻緣身在此山中”的情況，好比你想要研究“廬山”，但是一上來就抓着裡面的某顆松樹往死裡鑽，這定然是不妥的，更應該的是先從整體出發，先将整個廬山的面貌看清楚，最後再依次根據所觀察到的全貌，逐漸研究每個節點上的細節。

學習底層原理、源碼實作，亦或是做性能調優、線上排查，一定要遵循“先理主幹，再扣細節”的方式。