邏輯架構(gòu)

如果能在頭腦中構(gòu)建出一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖，就會有助于深入理解MySQL服務(wù)器形病。下圖展示了MySQL的邏輯架構(gòu)圖客年。

MySQL服務(wù)器邏輯架構(gòu)圖

大體來說，MySQL可以分為Server層和存儲引擎層兩部分漠吻。
Server層包括連接器量瓜、查詢緩存、分析器途乃、優(yōu)化器绍傲、執(zhí)行器等，涵蓋MySQL的大多數(shù)核心服務(wù)功能耍共，以及所有的內(nèi)置函數(shù)（例如：日期烫饼、時間、數(shù)學(xué)和加密函數(shù)）试读，所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)：等钩骇。

：一個數(shù)據(jù)庫里可以存在不同存儲引擎建立的表比藻。

而存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和提取铝量。其架構(gòu)模式是插件式的，支持InnoDB银亲、MyISAM慢叨、Memory等多個存儲引擎。現(xiàn)在最長用的存儲引擎是InnoDB务蝠，它從MySQL 5.5.5版本開始作為MySQL的默認(rèn)存儲引擎拍谐，之前版本的默認(rèn)存儲引擎為MyISAM。

從圖中不難看出馏段，不同的存儲引擎共用一個Server層赠尾，也就是從連接器到執(zhí)行器的部分。下面我們依次來介紹下各個組件毅弧。

連接器

連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限当窗、維持和管理連接够坐。

在客戶端程序發(fā)起連接時，需要攜帶主機(jī)信息崖面、用戶名元咙、密碼等信息，連接器會對客戶端提供的信息進(jìn)行驗(yàn)證巫员。如果驗(yàn)證失敗庶香，服務(wù)端就會拒絕連接；驗(yàn)證成功，連接器會到權(quán)限表里面查出你擁有的權(quán)限，并里以供后續(xù)的查詢緩存历帚、分析器嵌溢、執(zhí)行器使用。

每個客戶端連接都會在服務(wù)器進(jìn)程中擁有一個線程腻异，這個連接的查詢只會在這個單獨(dú)線程中執(zhí)行。當(dāng)客戶端與服務(wù)器斷開連接時，服務(wù)器并不會立即把該線程銷毀膳灶，而是會把線程緩存起來，以備后用立由，因此不需要為每一個新建的連接創(chuàng)建或者銷毀線程轧钓。但如果緩存的線程太多，就會影響性能锐膜，服務(wù)端通過參數(shù)max_connections來限制最大允許的連接數(shù)量從而限制線程數(shù)毕箍，該參數(shù)默認(rèn)值為151。

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

連接完成后枣耀，如果沒有后續(xù)動作霉晕，這個連接就處于空閑狀態(tài)庭再，你可以通過show processlist命令看到所有的連接。其中牺堰，Comman列會顯示連接的當(dāng)前狀態(tài)拄轻，Sleep表示該連接正處于空閑狀態(tài)。

mysql> show processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+
|  4 | root | 172.17.0.1:55024 | NULL | Query   |    0 | starting | show processlist |
|  5 | root | 172.17.0.1:55026 | NULL | Sleep   |    4 |          | NULL             |
+----+------+------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

如果客戶端長時間沒有動靜伟葫，連接器就會自動斷開連接恨搓。MySQL提供了參數(shù)wait_timeout來控制最大空閑時間，默認(rèn)值為8小時（28800秒）筏养。

mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

數(shù)據(jù)庫里面斧抱，連接有長、短連接之分渐溶。長連接是指客戶端連接成功后辉浦，一直使用同一個連接來發(fā)送請求；短連接是指每次完成一個或幾個很少的請求后就斷開連接茎辐，下次請求再重新建立一個連接宪郊。
由于建立連接的過程比較復(fù)雜，也比較耗時拖陆，所以一般的應(yīng)用建立連接時都是用的長連接（連接池）弛槐。但長連接也有自己的問題，由于MySQL執(zhí)行過程中臨時使用的內(nèi)存是管理在連接對象里的依啰，這些資源會在連接斷開的時候釋放乎串。如果連接一直不斷開，可能會導(dǎo)致內(nèi)存占用過大速警，產(chǎn)生OOM叹誉，被系統(tǒng)強(qiáng)行殺掉。

要解決這個問題闷旧，考慮以下兩種方案：

• 定期斷開連接桂对。使用一段時間或完成一定的請求數(shù)量，或執(zhí)行過一個占用內(nèi)存的大查詢之后鸠匀，斷開重連蕉斜。
• 如果是MySQL 5.7或更新版本，可以通過執(zhí)行mysql_reset_connection來重新初始化連接缀棍。這個過程不需要重連和重新做權(quán)限驗(yàn)證宅此，但是會把連接恢復(fù)到剛剛創(chuàng)建完時的狀態(tài)。

查詢緩存

連接成功后爬范，在檢查查詢緩存之前父腕，MySQL只做一件事情，就是通過一個大小寫不敏感的檢查看看SQL語句是不是以SEL開頭青瀑。

檢查未通過璧亮，不會去查詢緩存查找萧诫，直接走后續(xù)分析流程。

檢查通過枝嘶，會先到查詢緩存看看帘饶，之前是不是執(zhí)行過這條語句。如果查找到了群扶，則直接把緩存結(jié)果返回給客戶端及刻，不用再去底層的表查找了，效率比較高竞阐。如果沒找到缴饭，執(zhí)行正常的查詢流程，并把查詢結(jié)果保存到查詢緩存中骆莹。查詢語句及其結(jié)果是以key-value對的形式颗搂，緩存在內(nèi)存中的，key是查詢語句幕垦，value是查詢結(jié)果峭火。

：如果命中查詢緩存，會在查詢緩存返回結(jié)果時智嚷，做權(quán)限驗(yàn)證。

查詢緩存可以在不同的客戶端之間纺且，也就是說盏道，假如客戶端A剛剛發(fā)送了一個查詢請求，而客戶端B之后也發(fā)送了同樣的查詢請求载碌，那么客戶端B的這次查詢就可以直接使用查詢緩存中的數(shù)據(jù)了猜嘱。

雖然查詢緩存有時候會提高性能，但整體說弊大于利嫁艇，主要基于以下幾點(diǎn)分析：
1朗伶、查詢語句及其結(jié)果是以key-value對的形式保存的，如果兩個查詢請求有任何字符上的不同（例如：空格步咪，注釋等）论皆，都會導(dǎo)致緩存不命中；
2猾漫、如果查詢中包含某些系統(tǒng)函數(shù)点晴、用戶自定義變量和函數(shù)、系統(tǒng)表悯周，則這個請求就不會被緩存粒督。以NOW()或CURRENT_DATE()函數(shù)為例，每次調(diào)用產(chǎn)生的結(jié)果都不一致禽翼，所以不能被緩存屠橄；
3族跛、查詢緩存失效非常頻繁。只要該表的結(jié)構(gòu)或數(shù)據(jù)被修改锐墙，比如對該表使用了INSERT礁哄、UPDATE、DELETE贮匕、TRUNCATE TABLE姐仅、ALTER TABLE、DROP TABLE或DROP DATABASE語句刻盐，則與該表有關(guān)的所有查詢緩存都將變?yōu)闊o效并從查詢緩存中刪除掏膏。

：查詢緩存適用于不經(jīng)常修改的表，比如配置表敦锌。

從MySQL 5.7.20開始馒疹，不推薦使用查詢緩存，在MySQL 8.0中直接將其刪除了乙墙。

分析器

如果查詢緩存沒有命中颖变，接下來就要進(jìn)入正式的執(zhí)行階段了√耄客戶端發(fā)送過來的請求本質(zhì)上就是一段文本腥刹，服務(wù)端需要對文本進(jìn)行解析。解析過程涉及“詞法分析”汉买、“語法分析”兩個階段衔峰。

分析器先做“詞法分析”，從文本中將要查詢的表蛙粘，字段垫卤、各種查詢條件都提取出來。

然后做“語法分析”出牧，判斷請求的SQL語句是否滿足MySQL語法穴肘。

：分析器會在優(yōu)化器之前調(diào)動precheck驗(yàn)證權(quán)限。

優(yōu)化器

經(jīng)過了分析器舔痕，MySQL就知道要做什么了评抚。但是，因?yàn)槲覀儗懙腟QL語句執(zhí)行起來效率可能不高伯复，MySQL的優(yōu)化器會對我們的語句做一些優(yōu)化盈咳，如索引的選取、多表關(guān)聯(lián)（join）時各表的連接順序边翼，外連接轉(zhuǎn)換為內(nèi)連接鱼响、表達(dá)式簡化、子查詢轉(zhuǎn)為連接等一堆東西组底。優(yōu)化的的結(jié)果就是生成一個執(zhí)行計劃丈积。這個執(zhí)行計劃表明了應(yīng)該使用哪些索引筐骇，表之間的連接順序是啥等等，我們可以使用EXPLAIN來查看語句的執(zhí)行計劃江滨。

執(zhí)行器

經(jīng)過優(yōu)化器優(yōu)化后铛纬，就要按照生成的執(zhí)行計劃開始執(zhí)行了。開始執(zhí)行的時候唬滑，要先判斷一下有沒有執(zhí)行的權(quán)限告唆。

：SQL執(zhí)行過程中可能會有觸發(fā)器這種在運(yùn)行時才能確定的過程，分析器工作結(jié)束后的precheck是不能對這種運(yùn)行時涉及到的表進(jìn)行權(quán)限校驗(yàn)的晶密，所以需要在執(zhí)行器階段再次進(jìn)行權(quán)限檢查擒悬。

如果沒有，就會返回沒有權(quán)限的錯誤稻艰。

如果有權(quán)限懂牧，就會按照執(zhí)行計劃調(diào)用底層存儲引擎提供的接口獲取到數(shù)據(jù)后返回給客戶端。

不過需要注意的是尊勿，Server層和存儲引擎層交互時僧凤，一般是以記錄為單位的。以SELECT語句為例元扔，Server層根據(jù)執(zhí)行計劃先向存儲引擎層取一條數(shù)據(jù)躯保，然后判斷是否符合WHERE條件；如果符合澎语，就發(fā)送給客戶端途事，否則就跳過該記錄，然后繼續(xù)向存儲引擎索要下一條記錄咏连；依次類推。

：Server層在判斷某條記錄符合要求后鲁森，其實(shí)是先將其發(fā)送到一個緩沖區(qū)祟滴，等到該緩沖區(qū)滿了，才真正向客戶端發(fā)送記錄歌溉。該緩沖區(qū)大小是由系統(tǒng)變量net_buffer_length控制的垄懂，默認(rèn)為16K。

mysql> select @@net_buffer_length;
+---------------------+
| @@net_buffer_length |
+---------------------+
|               16384 |
+---------------------+
1 row in set (0.00 sec)

常用存儲引擎

總結(jié)

1漫谷、MySQL基本架構(gòu)包括Server層和存儲引擎層，Server層又包括多個組件--連接器蹂析、查詢緩存舔示、分析器碟婆、優(yōu)化器、執(zhí)行器惕稻；
2竖共、在連接器、分析器俺祠、執(zhí)行器里都涉及權(quán)限驗(yàn)證公给。
3、涉及三個系統(tǒng)變量max_connections蜘渣、wait_timeout淌铐、net_buffer_length。