京東面試:說說MySQL的架構體系

?關注“Java后端技術全棧”

回復“面試”獲取全套面試資料

字數(shù)：3620，閱讀耗時：4分35秒

最近群里一位兄弟在面試中被問到：「MySQL的架構體系是什么」。

雖然他搞java開發(fā)好幾年了，也一直使用的是MySQL數(shù)據(jù)庫，但是面對這個問題依然是一臉懵逼，還以為面試官要問索引、慢查詢、性能優(yōu)化之類的（因為這些都是網(wǎng)上找點面試題背過了）。

但這位面試官不按套路出牌，這位兄弟當場就是臉紅耳赤的，心想nnd居然會這么問。其實面試中面試官的問題有千千萬，有的問題確實背背面試題就能應對，但不是所有的面試題咱們都能背下來的。

今天我們就來聊聊MySQL的架構體系，盡管咱們是java開發(fā)人員，但是在日常開發(fā)過程中也會經(jīng)常和MySQL數(shù)據(jù)庫打交道。如果公司有DBA能干點事還稍微好點，如果是沒有DBA或者DBA沒什么卵用的情況下，我們還是很有必要了解MySQL的整個體系的，況且在面試中遇到了也是一個加分項。

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

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

平時，和小伙伴們聊天的時候，經(jīng)常會把MySQL當做我們開發(fā)的一個軟件系統(tǒng)，既然是軟件系統(tǒng)，那么就有個架構圖，以及架構是如何分層的，每一層的功能是什么。

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

MySQL架構圖

再來看看我們開發(fā)的系統(tǒng)架構圖：

其實還是蠻相似的。都有分層的概念。既然我們開發(fā)的軟件系統(tǒng)能進行分層，那么MySQL能分層嗎？

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

架構圖分層

上面的架構圖我們可以對其進行拆分，并做簡要的說明。

連接層

與客戶端打交道，上面已經(jīng)寫明了能支持的的語言。客戶端的鏈接支持的協(xié)議很多，比如我們在 Java 開發(fā)中的 JDBC。

服務層

連接池

主要是負責存儲和管理客戶端與數(shù)據(jù)庫的鏈接，一個線程負責管理一個連接。自從引入了連接池以后，官方報道：當數(shù)據(jù)庫的連接數(shù)達到128后，使用連接池與沒有連接池的性能是提升了n倍（反正就是性能大大的提升了）。

連接建立完成后，就可以執(zhí)行select語句了。執(zhí)行邏輯就會先來到緩存模塊。

緩存

MySQL拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執(zhí)行過這條語句。之前執(zhí)行過的語句及其結果會以key-value對的形式存儲在內(nèi)存中。key是查詢的語句，value是查詢的結果。如果你的查詢能夠直接在這個緩存中找到key（命中），那么這個value就會被直接返回給客戶端。

如果在緩存中未命中，就會繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結果會被存入查詢緩存中。這里可以看到，如果查詢命中緩存，MySQL不需要執(zhí)行后面的復雜操作，就可以直接返回結果，這個效率會很高。

但是大多數(shù)情況下我會建議你不要使用查詢緩存，為什么呢？因為查詢緩存往往弊大于利。

查詢緩存的失效非常頻繁，只要有對一個表的某一條數(shù)據(jù)更新，這個表上所有的查詢緩存都會被清空。

因此可能很費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低。除非你的業(yè)務就是有一張靜態(tài)表，很長時間才會更新一次。

比如：一個系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。

好在MySQL也提供了這種“按需使用”的方式。你可以將參數(shù)query_cache_type設置成DEMAND，這樣對于默認的SQL語句都不使用查詢緩存。

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

解析器

如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。首先，MySQL需要知道你要做什么，因此需要對SQL語句做解析。

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

做完了詞法分析以后，就要做“語法分析”。根據(jù)詞法分析的結果，語法分析器會根據(jù)語法規(guī)則，判斷你輸入的這個SQL語句是否滿足MySQL語法。

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

比如下面這個案例：

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

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

一般語法錯誤會提示第一個出現(xiàn)錯誤的位置，所以你要關注的是緊接“use near”的內(nèi)容，僅供參考，有時候這個提示也不是非?？孔V。

經(jīng)過分析器對SQL進行了分析，并且沒有報錯。那么此時就進入優(yōu)化器中，對SQL進行優(yōu)化。

優(yōu)化器

優(yōu)化器主要是在我們的數(shù)據(jù)庫表中，如果存在多個多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯(lián)（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

它會在條件查詢上進行優(yōu)化處理。

優(yōu)化器處理完成過后，此時就已經(jīng)確定了SQL的執(zhí)行方案。然后繼續(xù)進入執(zhí)行器中。

執(zhí)行器

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

比如說：生產(chǎn)環(huán)境中，對于大部分開發(fā)人員都只開查詢權限，沒有增刪改權限（部分小公司除外）。

如果有權限，就打開表繼續(xù)執(zhí)行。打開表的時候，執(zhí)行器就會根據(jù)表的引擎定義，去使用這個引擎提供的接口。

存儲引擎層

存儲引擎的概念是MySQL里面才有的，不是所有的關系型數(shù)據(jù)庫都有存儲引擎這個概念 。

數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組織，數(shù)據(jù)庫管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎，還可以獲得特定的功能?，F(xiàn)在許多不同的數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。

因為在關系數(shù)據(jù)庫中數(shù)據(jù)的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型(Table Type，即存儲和操作此表的類型)。

• MySQL5.5版本(mysql 版本 < 5.5版本) 以前，默認使用的存儲引擎是MyISAM 。
• MySQL5.5版本(mysql 版本 >= 5.5版本) 以后，默認使用的存儲引擎是InnoDB 。

下面對部分相對使用多的引擎進行一個對比：

在實際項目中，大多數(shù)使用InnoDB，然后是MyISAM，至于其他存儲引擎使用的非常至少。

MyISAM 與 InnoDB 引擎的區(qū)別

Mysql5.5 版本之前默認的存儲引擎就是 MyISAM 存儲引擎，MySQL 中比較多的系統(tǒng)表使用 MyISAM 存儲引擎，系統(tǒng)臨時表也會用到 MyISAM 存儲引擎，但是在 Mysql5.5 之后默認的存儲引擎就是 InnoDB 存儲引擎了。

兩個主要原因：

• 第一個原因是MyISAM 是表級鎖定，限制了數(shù)據(jù)庫讀/寫的性能；
• 另外一個原因是MyISAM 不支持事務，基于以上兩點，InnoDB 引擎可以鎖到行。

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

• 是否有事務操作？有，InnoDB。
• 是否存儲并發(fā)修改？有，InnoDB。
• 是否追求快速查詢，且數(shù)據(jù)修改較少？是，MyISAM。
• 是否使用全文索引？如果不引用第三方框架，可以選擇MyISAM，但是可以選用第三方框架和InnDB效率會更高。

系統(tǒng)文件存儲層

系統(tǒng)文件存儲層主要是負責將數(shù)據(jù)庫的數(shù)據(jù)和日志存儲在系統(tǒng)的文件中，同時完成與存儲引擎的之間的打交道，是文件的物理存儲層。

比如：數(shù)據(jù)文件、日志文件、pid文件、配置文件等。

數(shù)據(jù)文件

「db.opt文件」：記錄這個數(shù)據(jù)庫的默認使用的字符集和校驗規(guī)則。

「frm文件」：存儲于邊相關的元數(shù)據(jù)信息，包含表結構的定義信息等，每一張表都會有一個frm文件與之對應。

「MYD文件」：MyISAM存儲引擎專用的文件，存儲MyISAM表的數(shù)據(jù)信息，每一張MyISAM表都有有一個.MYD文件。

「MYI文件」：也是MyISAM存儲引擎專用的文件，存放MyISAM表的索引相關信息，每一張MyISAM表都有對應的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的數(shù)據(jù)文件（包括索引）。InnoDB存儲引擎有兩種表空間方式：獨立表空間和共享表空間。

• 獨享表空間使用ibd文件來存放數(shù)據(jù)，并且每一張InnoDB表存在與之對應的.ibd文件。

• 共享表空間使用ibdata文件，所有表共同使用一個或者多個.ibdata文件。

「ibdata1文件」：系統(tǒng)表空間數(shù)據(jù)文件，存儲表元數(shù)據(jù)、Undo日志等。

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

日志文件

錯誤日志：默認是開啟狀態(tài)，可以通過命令查看：

show?variables?like?'%log_error%';

二進制日志binary log：記錄了對MySQL數(shù)據(jù)庫執(zhí)行的更改操作，并且記錄了語句的發(fā)生時間、執(zhí)行耗時；但是不記錄查詢select、show等不修改數(shù)據(jù)的SQL。主要用于數(shù)據(jù)庫恢復和數(shù)據(jù)庫主從復制。也是大家常說的binlog日志。

show?variables?like?'%log_log%';//查看是否開啟binlog日志記錄。
show?variables?like?'%binllog%';//查看參數(shù)
show?binary?logs;//查看日志文件

慢查詢?nèi)罩荆河涗洸樵償?shù)據(jù)庫超時的所有SQL，默認是10秒。

show?variables?like?'%slow_query%'；//查看是否開啟慢查詢?nèi)罩居涗洝?br />show?variables?'%long_query_time%'；//查看時長

通用查詢?nèi)罩荆河涗浺话悴樵冋Z句；

show?variables?like?'%general%'；

配置文件

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

「pid文件」

pid文件是mysqld應用程序在Linux或者Unix操作系統(tǒng)下的一個進程文件，和許多其他Linux或者Unix服務端程序一樣，該文件放著自己的進程id。

「socket文件」

socket文件也是Linux和Unix操作系統(tǒng)下才有的，用戶在Linux和Unix操作系統(tǒng)下客戶端連接可以不通過TCP/IP網(wǎng)絡而直接使用Unix socket來連接MySQL數(shù)據(jù)庫。

SQL查詢流程圖

總結

MySQL整個系統(tǒng)我們可以看成是我們?nèi)粘ｉ_發(fā)的軟件系統(tǒng)，也有接入層，專門對接外面客戶端的，和我們系統(tǒng)的網(wǎng)關就很像，緩存也就類似我們業(yè)務代碼中使用的緩存，解析器可以理解為業(yè)務系統(tǒng)中參數(shù)解析以及參數(shù)校驗，優(yōu)化層可以當做我們開發(fā)代碼優(yōu)化的手段，然后存儲引擎就相當于我們的持久層，文件系統(tǒng)相當于整個業(yè)務系統(tǒng)中的數(shù)據(jù)庫。

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

不要天天羨慕什么大牛，什么大神，他們也是一步一步來的（個例除外）。自信點，只要你一點一點搞，踏踏實實的干，你也會成為大神的。

文中圖片來源于網(wǎng)絡，侵刪！