美團面試官：講清楚MySQL結構體系，立刻發offer

故事

#繼續跟大家分享，我去上海美團面試遇到的技術問題，當時，回答的也是馬馬虎虎的，不能說不好，也不能說好，反正就是沒有給面試官一種爽的感覺。

害，很多東西都是，平常感覺還行，一旦到了面試的時候啥都想不起來。

雖然，本人搞Java開發快五年了（2017），用過Oracle資料庫（銀行裡的系統），但大多時候都是使用MySQL資料庫，但面對這個問題依然是一臉懵逼（硬著頭皮也扯了一些），還以為面試官要問索引、慢查詢、效能最佳化之類的（因為這些都是網路找點面試題背過了）。

今天我們就來聊聊MySQL的架構體系，儘管咱們是Java開發人員，但在日常開發過程中也會經常和MySQL資料庫打交道。如果公司有DBA能幹點事還稍微好點，如果是沒有DBA或DBA沒什麼用的情況下，我們還是很有必要了解MySQL的整個體系的，況且在面試中遇到了也是一個加分項。

想要知道一條SQL是怎麼查詢的，只要對MySQL整個體系搞清楚了，才能說出個123。

所以於情於理，我們很有必要學習一下MySQL的架構體系的。

平常，我和小夥伴們聊天的時候，常常會把MySQL當做我們開發的一個軟體系統，既然是軟體系統，那就有個架構圖，以及架構是如何分層的，每一層的功能是什麼。

MySQL是什麼？

• MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，目前屬於Oracle公司。
• MySQL是一種關聯資料庫管理系統，將資料保存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。
• MySQL是開源的，所以你不需要支付額外的費用。
• MySQL支援大型的資料庫，可以處理擁有上千萬筆記錄的大型資料庫。
• MySQL使用標準的SQL資料語言形式。
• MySQL可以允許多個系統上，並且支援多種語言，這些程式語言包括C、C 、Python、Java、Ped、PHP、Eifel 、Ruby和TCL等。
• MySQL對PHP有很好的支持，PHP是目前最受歡迎的Web開發語言。
• MySQL支援大型資料庫，支援5000萬筆記錄的資料倉庫，32位元系統表檔案最大可支援4GB，64位元系統支援最大的表文件為8TB。
• MySQL是可以客製化的，採用了GPL協議，你可以修改原始碼來開發自己的MySQL系統。

請注意MySQL拼寫，另外，很多人可能有疑問，為什麼MySQL的logo是一隻海豚？

下面我們就來看看MySQL的整體架構圖。

MySQL架構圖

# #再來看看我們所發展的系統架構圖：

######################其實還蠻相似的，都有分層的概念。既然我們開發的軟體系統能進行分層，那麼###MySQL###能分層嗎？ ###

答案是：能，下面我們就來聊聊MySQL的分層情況以及每一層的功能。

架構圖分層

#上面的架構圖我們可以將其拆分，並做簡要的說明。

連接層

與客戶端打交道，上面已經寫明了能支持的語言。客戶端的連結支援的協定很多，例如我們在 Java 開發中的 JDBC。

這一層是不是有點像我們專案中的網關層？如果對網關不熟悉，那我們可以理解我controller層。

服務層

#這一層，就相當於我們業務系統中的service層，大雜燴，相關業務的操作、程式碼最佳化、快取等都在這裡面。

連接池

主要是負責儲存和管理客戶端與資料庫的鏈接，一個執行緒負責管理一個連接。自從引入了連線池以後，官方報告：當資料庫的連線數達到128後，使用連線池與沒有連線池的效能是提升了n倍（反正就是效能大大的提升了）。

連線建立完成後，就可以執行select語句了。執行邏輯就會先來到快取模組。

快取

MySQL拿到一個查詢要求後，會先到查詢快取看看，之前是不是執行過這條語句。先前執行過的語句及其結果會以key-value對的形式儲存在記憶體中。 key是查詢的語句，value是查詢的結果。如果你的查詢能夠直接在這個快取中找到key（命中），那麼這個value就會直接回傳給客戶端。

如果在快取中未命中，就會繼續後面的執行階段。執行完成後，執行結果會存入查詢快取。這裡可以看到，如果查詢命中緩存，MySQL不需要執行後面的複雜操作，就可以直接回傳結果，這個效率會很高。

但是大多數情況下我會建議你不要使用查詢緩存，為什麼呢？因為查詢快取往往弊大於利。

查詢快取的失效非常頻繁，只要有對一個表的某一筆資料更新，這個表上所有的查詢快取都會被清空。

因此可能很費力地把結果存起來，還沒使用呢，就被一個更新全清空了。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低。除非你的業務就是有一張靜態表，很久才會更新一次。

例如：一個系統設定表，那麼這張表上的查詢才適合使用查詢快取。

好在MySQL也提供了這種「按需使用」的方式。你可以將參數query_cache_type設定成DEMAND，這樣對於預設的SQL語句都不使用查詢快取。

「注意」：MySQL 8.0版本直接將查詢快取的整個功能刪掉了，標誌著MySQL8.0開始徹底沒有快取這個功能了。

解析器

如果沒有命中查詢緩存，就要開始真正執行語句了。首先，MySQL需要知道你要做什麼，所以需要對SQL語句做解析。

分析器先會做「詞法分析」。你輸入的是由多個字串和空格組成的一條SQL語句，MySQL需要辨識出裡面的字串分別是什麼，代表什麼。

做完了詞法分析以後，就要做「文法分析」。根據詞法分析的結果，語法分析器會根據文法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。

如果我們在拼字SQL時候，少了或寫錯了某個字母，就會收到「You have an error in your SQL syntax」的錯誤提醒。

例如下面這個案例：

#錯誤在於WHERE關鍵字中差了一個E。

同樣，我們使用的SQL如果某個欄位不存在。

一般語法錯誤會提示第一個出現錯誤的位置，所以你要關注的是緊接著「use near」的內容，僅供參考，有時候這個提示也不是非常可靠。

經過分析器對SQL進行了分析，並且沒有報錯。那麼此時就進入優化器中，對SQL進行最佳化。

優化器

優化器主要是在我們的資料庫表中，如果存在多個多個索引的時候，決定使用哪個索引；或在一個語句有多表關聯（join）的時候，決定各個表的連接順序。

比如說：

SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

它會在條件查詢上進行最佳化處理。

優化器處理完成過後，此時就已經確定了SQL的執行方案。然後繼續進入執行器。

執行器

首先，一定是要判斷權限，就是有沒有權限執行這條SQL。工作中可能會對某些客戶端進行權限控制。

比如說：生產環境中，對於大部分開發人員都只開查詢權限，沒有增刪改權限（部分小公司除外）。

如果有權限，就開啟表格繼續執行。打開表格的時候，執行器就會根據表格的引擎定義，去使用這個引擎提供的介面。

儲存引擎層

#這一層，我們可以理解為我們業務系統中的持久層。

儲存引擎的概念是MySQL裡面才有的，不是所有的關聯式資料庫都有儲存引擎這個概念 。

資料庫儲存引擎是資料庫底層軟體組織，資料庫管理系統（DBMS）使用資料引擎進行建立、查詢、更新和刪除資料。不同的儲存引擎提供不同的儲存機制、索引技巧、鎖定等級等功能，使用不同的儲存引擎，還可以獲得特定的功能。現在許多不同的資料庫管理系統都支援多種不同的資料引擎。

因為在關聯式資料庫中資料的儲存是以表格的形式儲存的，所以儲存引擎也可以稱為表格類型(Table Type，即儲存和操作此表的類型)。

• MySQL5.5版本(mysql 版本
• MySQL5.5版本(mysql 版本 >= 5.5版本) 以後，預設使用的儲存引擎是InnoDB 。

下面將部分相對使用多的引擎做一個比較：

在實際專案中，大多數使用InnoDB，然後是MyISAM，至於其他儲存引擎使用的非常至少。

我們可以使用指令來檢視MySQL 已提供什麼儲存引擎：

#show engies;

也可以透過指令來查看MySQL 目前預設的儲存引擎：

show variables like '%storage_engine%';

MyISAM與InnoDB引擎的差異

MySQL5.5 版本之前預設的儲存引擎就是MyISAM 儲存引擎，MySQL 中比較多的系統表使用MyISAM 儲存引擎，系統暫存表也會用到MyISAM 儲存引擎，但在Mysql5.5 之後預設的儲存引擎就是InnoDB 儲存引擎了。

如何在兩個儲存引擎中進行選擇？

• 是否有交易操作？有，InnoDB。
• 是否儲存並發修改？有，InnoDB。
• 是否追求快速查詢，且資料修改較少？是，MyISAM。
• 是否使用全文索引？如果不引用第三方框架，可以選擇MyISAM，但可以選用第三方框架和InnDB效率會更高。

InnoDB 儲存引擎主要有下列特點：

1. 支援交易

2. #支援4 個層級的交易隔離

3. 支援多版本讀取

4. #支援行級鎖定

5. #讀寫阻塞與交易隔離層級相關

6. 支援緩存，既能快取索引，也能快取資料

7. 整個表和主鍵以Cluster 方式存儲，組成一顆平衡樹

#當然也不是說InnoDB 一定就是好的，在在實際開發中，還是要根據具體的場景來選擇到底要使用InnoDB 還是MyISAM 。

MyIASM（此引擎在5.5 前的MySQL 資料庫中為預設儲存引擎）特性：

1. MyISAM 沒有提供對資料庫事務的支援

2. #不支援行級鎖定和外鍵

3. #由於2，導致執行INSERT 插入或UPDATE 更新語句時，即執行寫入作業需要鎖定整個表，所以會導致效率降低

4. MyISAM 保存了表格的行數，當執行SELECT COUNT(*) FROM TABLE 時，可以直接讀取相關值，不用全表掃描，速度快。

#兩者差異：

2. ##MyISAM 是非事務安全性的，而InnoDB 是事務安全的
   #######MyISAM 鎖定的粒度是表級的，而InnoDB 則支持行級鎖定#########

3. MyISAM 支援全文類型索引，而 InnoDB 在 MySQL5.6 之前不支援全文索引，從 MySQL5.6 之後開始支援 FULLTEXT 索引了。

使用場景比較：

1. 如果要執行大量select 操作，應該選擇MyISAM

2. #如果要執行大量insert 和update 操作，應該選擇InnoDB

3. 大尺寸的資料集趨向選擇InnoDB 引擎，因為它支援交易處理和故障復原。資料庫的大小決定了故障恢復的時間長短，InnoDB 可以利用交易日誌進行資料恢復，這會比較快。主鍵查詢在 InnoDB 引擎下也會相當快，不過需要注意的是如果主鍵太長也會導致效能問題。

相對來說，InnoDB 在網路公司使用更多。

系統檔案儲存層

這一層，我們同樣的可以理解為我們業務系統中的資料庫。

系統檔案儲存層主要是負責將資料庫的資料和日誌儲存在系統的檔案中，同時完成與儲存引擎的之間的打交道，是檔案的實體儲存層。

例如：資料檔、日誌檔、pid檔、設定檔等。

資料檔

「db.opt檔」：記錄這個資料庫的預設使用的字元集和校驗規則。

「frm檔」：儲存於邊相關的元資料訊息，包含表結構的定義資訊等，每一張表都會有一個frm檔與之對應。

「MYD檔案」：MyISAM儲存引擎專用的文件，儲存MyISAM表的資料信息，每張MyISAM表都有一個.MYD檔。

「MYI文件」：也是MyISAM存储引擎专用的文件，存放MyISAM表的索引相关信息，每一张MyISAM表都有对应的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独立表空间和共享表空间。

• 独享表空间使用ibd文件来存放数据，并且每一张InnoDB表存在与之对应的.ibd文件。
• 共享表空间使用ibdata文件，所有表共同使用一个或者多个.ibdata文件。

「ibdata1文件」：系统表空间数据文件，存储表元数据、Undo日志等。

「ib_logfile0、ib_logfile0文件」：Redo log日志文件。

日志文件

错误日志：默认是开启状态，可以通过命令查看：

show variables like '%log_error%';

二进制日志binary log：记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行耗时；但是不记录查询select、show等不修改数据的SQL。主要用于数据库恢复和数据库主从复制。也是大家常说的binlog日志。

show variables like '%log_log%';//查看是否开启binlog日志记录。
show variables like '%binllog%';//查看参数
show binary logs;//查看日志文件

慢查询日志：记录查询数据库超时的所有SQL，默认是10秒。

show variables like '%slow_query%'；//查看是否开启慢查询日志记录。
show variables '%long_query_time%'；//查看时长

通用查询日志：记录一般查询语句；

show variables like '%general%'；

配置文件

用于存放MySQL所有的配置信息的文件，比如：my.cnf、my.ini等。

「pid文件」

pid文件是mysqld应用程序在Linux或者Unix操作系统下的一个进程文件，和许多其他Linux或者Unix服务端程序一样，该文件放着自己的进程id。

「socket文件」

socket文件也是Linux和Unix操作系统下才有的，用户在Linux和Unix操作系统下客户端连接可以不通过TCP/IP网络而直接使用Unix socket来连接MySQL数据库。

SQL查询流程图

總結

MySQL 整個系統我們可以看成是我們日常開發的軟體系統，也有存取層，專門對接外面客戶端的，和我們系統的網關就很像，緩存也就類似我們業務代碼中使用的緩存，解析器可以理解為業務系統中參數解析以及參數校驗，優化層可以當做我們開發程式碼優化的手段，然後儲存引擎就相當於我們的持久層，檔案系統相當於整個業務系統中的資料庫。

可能比喻不是非常的恰當，但希望大家能領略輕重的意義，目的只有一個，那就是讓大家能輕鬆掌握MySQL的整體情況。

文章部分圖片來自網絡，侵刪！

不要天天羨慕什麼大牛，什麼大神，他們也是一步一步走來的。自信一點，只要你一點一點搞，踏實的干，你也會成為大神的。

推薦閱讀

• #你不動，我就不動- --觀察者模式
  

• 離職後，10天面試 4 家公司的總結
  

• 一套Spring Cloud Alibaba視頻教學
  

• 。後端開發人員，學習之路

• ##後端面試23萬，500多頁，屌！

• 112期面試匯總，建議收藏

以上是美團面試官：講清楚MySQL結構體系，立刻發offer的詳細內容。更多資訊請關注PHP中文網其他相關文章！