一、引文

最近我正在學(xué)習(xí) MySQL 的面試相關(guān)八股，打算單獨(dú)開(kāi)一個(gè) MySQL 專(zhuān)欄來(lái)記錄自己每天的學(xué)習(xí)，內(nèi)容主要來(lái)自小林coding和 JavaGuide。今天了解到了 MySQL 的架構(gòu)和執(zhí)行流程，沒(méi)想到一條簡(jiǎn)單的 sql 語(yǔ)句居然在 MySQL 中經(jīng)歷了這么多的流程。

二、MySQL 執(zhí)行流程

MySQL 執(zhí)行流程大概分為服務(wù)層和存儲(chǔ)引擎層兩部分，服務(wù)層中主要涉及到建立連接、查詢緩存、SQL語(yǔ)句解析、預(yù)處理、優(yōu)化、執(zhí)行幾個(gè)階段，存儲(chǔ)引擎層則是把執(zhí)行結(jié)果拿到的數(shù)據(jù)返回。

1.服務(wù)層

（1）連接器

連接器這一環(huán)主要是讓客戶端（如 Java 程序、TablePlus等）基于 TCP 連接到 MySQL 的服務(wù)端。第一步肯定是先啟動(dòng) MySQL 服務(wù)，然后基于 TCP 協(xié)議完成三次握手，連接層要做的事情第一個(gè)就是校驗(yàn)?zāi)愕挠脩裘兔艽a是否正確，只有你輸入正確時(shí)，才會(huì)建立連接，同時(shí)記錄你這個(gè)賬號(hào)的權(quán)限，并在此后該連接的過(guò)程中都會(huì)基于剛連接時(shí)保存的權(quán)限一直執(zhí)行該權(quán)限的邏輯。

在 MySQL 中也有長(zhǎng)連接和短連接的概念，所謂的短連接就是每建立一次 MySQL 客戶端連接，只能執(zhí)行一條 SQL 語(yǔ)句，然后就立馬斷開(kāi)連接。而長(zhǎng)連接就是一次客戶端連接中可以執(zhí)行多條 SQL 語(yǔ)句。

長(zhǎng)連接與短連接的性能差異極大，在 MySQL 中頻繁地創(chuàng)建銷(xiāo)毀連接都是極其消耗資源的，原因主要出在了巨大的“握手”開(kāi)銷(xiāo)上，如網(wǎng)絡(luò)層面的 TCP 三次握手、MySQL 協(xié)議握手、TLS/SSL握手以及每次創(chuàng)建連接 MySQL 都要去系統(tǒng)表查詢用戶權(quán)限并加載到內(nèi)存中。

與此同時(shí) MySQL 的連接與內(nèi)存是密切相關(guān)的。默認(rèn)情況下，連接器每建立一次新的連接，MySQL 都會(huì)對(duì)應(yīng)創(chuàng)建一個(gè)新的工作線程，即便這個(gè)線程是 Sleep 狀態(tài)，也會(huì)占用相應(yīng)的內(nèi)存。為避免大量線程創(chuàng)建導(dǎo)致內(nèi)存溢出以及 CPU 頻繁切換線程，MySQL 還提供了線程池功能，讓少量線程服務(wù)于大量連接，進(jìn)而減小內(nèi)存損耗。

一個(gè)連接占用的內(nèi)存主要分為兩部分：固定內(nèi)存和臨時(shí)內(nèi)存。

(1) 固定內(nèi)存 (Thread Static Memory)

這是連接建立后立即分配的，直到連接斷開(kāi)才釋放：

• 線程棧 (Thread Stack)：存儲(chǔ)線程執(zhí)行時(shí)的局部變量、函數(shù)調(diào)用信息。由參數(shù) thread_stack 控制（默認(rèn) 256KB 左右）。
• 連接信息：存儲(chǔ)用戶信息、權(quán)限、當(dāng)前數(shù)據(jù)庫(kù)名、狀態(tài)變量等。
• 網(wǎng)絡(luò)緩存 (Net Buffer)：用于存放客戶端發(fā)送的 SQL 語(yǔ)句和服務(wù)器返回的結(jié)果。由 net_buffer_length 控制。

(2) 會(huì)話級(jí)臨時(shí)內(nèi)存 (Session Private Memory)

這是最容易導(dǎo)致內(nèi)存激增的部分。當(dāng)連接開(kāi)始執(zhí)行復(fù)雜的 SQL 時(shí)，MySQL 會(huì)根據(jù)需要臨時(shí)分配內(nèi)存：

• 排序緩沖區(qū) (Sort Buffer)：執(zhí)行 ORDER BY 或 GROUP BY 時(shí)使用。
• 連接緩沖區(qū) (Join Buffer)：執(zhí)行多表關(guān)聯(lián)查詢時(shí)使用。
• 臨時(shí)表內(nèi)存 (Memory Temporary Table)：執(zhí)行復(fù)雜查詢產(chǎn)生的中間結(jié)果。
• 結(jié)果集緩存：在數(shù)據(jù)發(fā)回客戶端之前暫存數(shù)據(jù)的內(nèi)存。

關(guān)鍵點(diǎn)： 這些內(nèi)存是按需分配的。如果一個(gè) SQL 不需要排序，就不會(huì)分配 sort_buffer。但如果設(shè)置得太大，成千上萬(wàn)個(gè)連接同時(shí)請(qǐng)求時(shí)，內(nèi)存會(huì)瞬間被榨干。

管理與回收機(jī)制

(1) 空閑連接的管理

如果一個(gè)連接執(zhí)行完任務(wù)后沒(méi)有關(guān)閉（長(zhǎng)連接），它會(huì)進(jìn)入 Sleep 狀態(tài)。

• 保留資源：它依然占用線程棧和基礎(chǔ)的網(wǎng)絡(luò)緩存。
• 釋放資源：它會(huì)釋放掉執(zhí)行 SQL 時(shí)臨時(shí)申請(qǐng)的 sort_buffer 等。
• 超時(shí)斷開(kāi)：MySQL 通過(guò) wait_timeout 參數(shù)控制空閑連接的壽命。如果一個(gè)連接空閑時(shí)間超過(guò)這個(gè)值，MySQL 會(huì)強(qiáng)制殺掉該連接并回收內(nèi)存。

(2) 線程緩存 (Thread Cache)

為了避免頻繁創(chuàng)建和銷(xiāo)毀線程（這是很消耗資源的），MySQL 實(shí)現(xiàn)了一個(gè) Thread Cache：

• 當(dāng)客戶端斷開(kāi)連接時(shí)，MySQL 不會(huì)直接銷(xiāo)毀線程，而是把它放入緩存池中。
• 當(dāng)下個(gè)連接進(jìn)來(lái)時(shí)，直接從池里撈出一個(gè)現(xiàn)成的線程使用。
• 由 thread_cache_size 參數(shù)控制緩存的數(shù)量。

常見(jiàn)指令：

show processlist // 查看 MySQL 服務(wù)端被多少客戶端連接的情況

show variables like 'wait_timeout' // 查看 MySQL 中客戶端連接的最大空閑時(shí)長(zhǎng)

show variables like 'max_connections' // 查看 MySQL 中最大客戶端連接數(shù)量

kill connection id // 刪除 MySQL 的某個(gè)客戶端連接，提供連接對(duì)應(yīng)的 id 即可

（2）查詢緩存

連接器建立連接之后，客戶端就可以發(fā)送 SQL 語(yǔ)句給 MySQL 服務(wù)端了，拿到 SQL 語(yǔ)句先解析第一個(gè)字段查看是什么類(lèi)型的 SQL 語(yǔ)句。如果發(fā)現(xiàn)是 SELECT，那就路由到查詢緩存里，查詢緩存里面的數(shù)據(jù)是k-v形式的鍵值對(duì)，key為 SQL 查詢語(yǔ)句，value 為該查詢語(yǔ)句返回的結(jié)果。如果拿著客戶端發(fā)來(lái)的 SQL 語(yǔ)句命中了緩存，那就可以直接返回 value，反之再走下面的解析器層。

也許是受到了 Redis 的影響，讓我一開(kāi)始覺(jué)得這功能設(shè)計(jì)的很好。但仔細(xì)一想會(huì)發(fā)現(xiàn)，MySQL 里面的表不同于 專(zhuān)門(mén)做緩存的 Redis，MySQL 里面的數(shù)據(jù)肯定是更容易更新的，一旦表中數(shù)據(jù)更新那就意味著查詢緩存失效，需要重建緩存。如果遇到一個(gè)重建條件非常苛刻的緩存，好不容易重建完，表又更新了，這緩存還一次沒(méi)用呢，是不是就十分浪費(fèi)資源。所以從 MySQL 8 起查詢緩存這一環(huán)就直接被去除了。

（3）解析器

解析器主要做兩件事，第一件事是詞法分析，這一步主要是把輸入轉(zhuǎn)化成若干個(gè)Token，其中Token包含key和非key。比如，一個(gè)簡(jiǎn)單的SQL如下所示：

SELECT name FROM userInfo

分析之后，會(huì)得到4個(gè)Token，其中有2個(gè)Keyword，分別為select和from：

第二件事就是語(yǔ)法分析，拿到上一步詞法分析的結(jié)果后，再判斷 SQL 語(yǔ)句是否符合語(yǔ)法規(guī)范，如果沒(méi)問(wèn)題那就構(gòu)建語(yǔ)法樹(shù)，方便后續(xù)獲取 SQL 類(lèi)型、表名、字段名。如果寫(xiě)錯(cuò)了（如 selec），會(huì)報(bào)錯(cuò)：You have an error in your SQL syntax。

（4）預(yù)處理器

如果寫(xiě)了一個(gè)語(yǔ)法完全正確的樹(shù)，但是表或者字段不存在，還是在解析的時(shí)候報(bào)錯(cuò)，因?yàn)榻馕銎魈幚碇螅€有一個(gè)預(yù)處理器，它用來(lái)判斷解析樹(shù)的語(yǔ)義是否正確，也就是表名和字段名是否存在。預(yù)處理后生成一個(gè)新的解析樹(shù)。此外它還可以進(jìn)行擴(kuò)展字段，比如將 select * 中的 * 符號(hào)，擴(kuò)展為表上的所有列；

（5）優(yōu)化器

這是 MySQL 的“大腦”。當(dāng)一個(gè) SQL 有多種執(zhí)行路徑時(shí)，優(yōu)化器會(huì)決定使用哪一種。

• 選擇索引：如果表有多個(gè)索引，決定用哪個(gè)。
• 多表關(guān)聯(lián)（Join）：決定表的連接順序（先查小表還是先查大表）。
• 目的：尋找執(zhí)行成本最低（通常是 IO 最少）的方案，生成執(zhí)行計(jì)劃。

由于距離學(xué) MySQL 已經(jīng)過(guò)去好幾個(gè)月了，早就已經(jīng)忘記了 B+ 樹(shù)索引、回表、覆蓋索引這些概念了，所以當(dāng)時(shí)看上圖的案例有點(diǎn)暈，如果大家有相同的感覺(jué)可以跟我在下面一起復(fù)習(xí)：

Q：兩種 B+ 樹(shù)索引的區(qū)別

在 InnoDB 存儲(chǔ)引擎中，根據(jù)葉子節(jié)點(diǎn)存放內(nèi)容的不同，索引分為兩類(lèi)：

主鍵索引的 B+ 樹(shù)（又稱(chēng)：聚簇索引 - Clustered Index）

• 鍵值：表的主鍵 id。
• 葉子節(jié)點(diǎn)存什么：存放的是完整的整行行記錄。
• 特點(diǎn)：索引即數(shù)據(jù)。只要找到了主鍵 id，就能直接獲取到這行數(shù)據(jù)的所有字段（product_no, name, price 等）。

二級(jí)索引的 B+ 樹(shù)（又稱(chēng)：輔助索引 - Secondary Index）

• 鍵值：你在哪列建索引，鍵值就是哪列。比如圖片中的 name。
• 葉子節(jié)點(diǎn)存什么：存放的是對(duì)應(yīng)記錄的主鍵值。
• 特點(diǎn)：它不包含整行數(shù)據(jù)。如果你通過(guò) name 查到了某條記錄，你只能順便得到它的 id。

Q：什么是“回表”？

通常情況下，如果你執(zhí)行：
SELECT price FROM product WHERE name = 'apple';

• 查詢會(huì)在 二級(jí)索引 (name) 的 B+ 樹(shù)中找到 apple。
• 從二級(jí)索引的葉子節(jié)點(diǎn)拿到對(duì)應(yīng)的 主鍵 id（假設(shè)是 1）。
• 關(guān)鍵動(dòng)作：拿著 id=1 再去 主鍵索引 的 B+ 樹(shù)里查一遍，為了拿到 price 字段。

這個(gè)回到主鍵索引再查一遍的過(guò)程，就叫 “回表”。回表會(huì)增加磁盤(pán) I/O，降低性能。

Q：什么是覆蓋索引？

覆蓋索引并不是一種索引類(lèi)型，而是一種查詢現(xiàn)象：
當(dāng)一個(gè)索引包含（覆蓋）了查詢語(yǔ)句中需要的所有字段時(shí)，MySQL 就可以直接從這個(gè)索引中返回?cái)?shù)據(jù)，而不需要回表。

結(jié)合你圖片中的例子：

查詢語(yǔ)句：SELECT id FROM product WHERE id > 1 AND name LIKE 'i%';

• 查詢需要的字段：id（在 SELECT 里）和 name（在 WHERE 里）。
• 二級(jí)索引 name 的內(nèi)容：它本身是按 name 排序的，且它的葉子節(jié)點(diǎn)里存的就是 id。
• 結(jié)論：這個(gè) name 索引已經(jīng)包含了你查詢所需要的全部信息（name 和 id）。

此時(shí)：
MySQL 引擎只需要掃描 name 這棵 B+ 樹(shù)，就能過(guò)濾出滿足 i% 條件的記錄，并且直接從這棵樹(shù)里把 id 拿出來(lái)返回。它根本不需要去翻主鍵索引那棵樹(shù)。 這就叫“覆蓋索引”。

復(fù)習(xí)了上述概念我們也就能明白為什么優(yōu)化器最后幫我們決定使用普通索引，做覆蓋索引優(yōu)化。而不是說(shuō)直接查主鍵索引。一來(lái)是主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的信息過(guò)多，查詢主鍵索引導(dǎo)致的磁盤(pán) I/O 也就更多，自然更耗時(shí)間，相較之下二級(jí)索引的葉子節(jié)點(diǎn)里面只存了主鍵和索引字段。另一個(gè)原因就是我們查的是主鍵ID，它已經(jīng)存在于二級(jí)索引的葉子節(jié)點(diǎn)了，因此沒(méi)必要回表，節(jié)省了大筆開(kāi)銷(xiāo)。

常見(jiàn)指令：

explain + 查詢 SQL 語(yǔ)句 // 輸出這條 SQL 語(yǔ)句的執(zhí)行計(jì)劃

（6）執(zhí)行器

開(kāi)始執(zhí)行 SQL。

• 權(quán)限校驗(yàn)：在執(zhí)行前，再次確認(rèn)用戶對(duì)該表是否有執(zhí)行權(quán)限。
• 調(diào)用接口：根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，循環(huán)調(diào)用存儲(chǔ)引擎提供的 API 接口。

索引下堆：索引下堆是一種查詢優(yōu)化策略，它能夠減少二級(jí)索引查詢過(guò)程中的回表操作，其本質(zhì)在于把應(yīng)該由 Server 層做的判斷交給了存儲(chǔ)引擎層。在小林coding中案例如下：

使用索引下堆前后的關(guān)鍵我用紅線標(biāo)注了起來(lái)，主要就是 reward 是否等于 1000000 這個(gè)判斷由 Server 層轉(zhuǎn)交給了存儲(chǔ)引擎層，它的好處在于不用為了讓 Server 層判斷這一條件而特意回表，可以看到如果該條件不成立完全可以在存儲(chǔ)引擎層就直接跳過(guò)該二級(jí)索引，進(jìn)而節(jié)省了一部分的回表操作開(kāi)銷(xiāo)。

2.存儲(chǔ)引擎層

存儲(chǔ)引擎是數(shù)據(jù)真正存放的地方。

• 常見(jiàn)的引擎：InnoDB（默認(rèn)，支持事務(wù)）、MyISAM。
• 執(zhí)行器每調(diào)用一次引擎接口，引擎就會(huì)去磁盤(pán)或內(nèi)存（Buffer Pool）中查找數(shù)據(jù)，并將結(jié)果返回給執(zhí)行器。

到此這篇關(guān)于MySQL | 從SQL到數(shù)據(jù)的完整路徑的文章就介紹到這了,更多相關(guān)mysql從sql到數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論