SQL查詢語句是如何執(zhí)行的

大家好！這是我的第一篇文章。

這篇文章我將介紹一條sql查詢語句是如何執(zhí)行的

下面是mysql架構(gòu)圖：

總的來說，mysql可以分為兩部分：服務(wù)器層和存儲(chǔ)引擎層。

服務(wù)器層包括連接器、查詢緩存、解析器、優(yōu)化器、執(zhí)行器等，包含mysql的大部分核心服務(wù)功能，以及所有內(nèi)置功能（如日期、時(shí)間、數(shù)學(xué)和加密）功能）。所有跨存儲(chǔ)引擎的功能，例如存儲(chǔ)過程、觸發(fā)器和視圖都在這一層實(shí)現(xiàn)。

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和檢索。其架構(gòu)是基于插件的，支持innodb、myisam、memory等多種存儲(chǔ)引擎。從mysql 5.5.5開始，innodb成為mysql的默認(rèn)存儲(chǔ)引擎。

創(chuàng)建表時(shí)可以使用帶有engine=memory的create table語句指定內(nèi)存引擎。

不同的存儲(chǔ)引擎共享同一個(gè)server層

連接器

第一步是連接數(shù)據(jù)庫，這需要連接器。連接器負(fù)責(zé)與客戶端建立連接、獲取權(quán)限并維護(hù)和管理連接。連接命令為：

mysql -h$ip -p$port -u$user -p 

該命令用于與服務(wù)器建立連接。完成經(jīng)典的 tcp 握手后，連接器將使用提供商的用戶名和密碼來驗(yàn)證您的身份。

• 如果用戶名或密碼不正確，您將收到“用戶錯(cuò)誤導(dǎo)致訪問被拒絕”的消息，并且客戶端程序?qū)⒔K止。
• 如果身份驗(yàn)證成功，連接器將從權(quán)限表中檢索當(dāng)前帳戶的權(quán)限。此連接期間的所有權(quán)限檢查都依賴于此初始檢索。

這意味著一旦連接成功建立，管理員對(duì)用戶權(quán)限所做的任何更改都不會(huì)影響現(xiàn)有連接的權(quán)限。只有新連接才會(huì)使用更新后的權(quán)限設(shè)置。

連接建立后，如果沒有后續(xù)動(dòng)作，則連接進(jìn)入空閑狀態(tài)，可以使用show processlist命令查看：

如果客戶端長時(shí)間處于不活動(dòng)狀態(tài)，連接器將自動(dòng)斷開連接。持續(xù)時(shí)間由 wait_timeout 參數(shù)控制，默認(rèn)為 8 小時(shí)。

如果連接終止并且客戶端發(fā)送請(qǐng)求，則會(huì)收到錯(cuò)誤消息：在查詢期間丟失與 mysql 服務(wù)器的連接。要繼續(xù)，您需要重新連接，然后執(zhí)行請(qǐng)求。

在數(shù)據(jù)庫中，持久連接是指客戶端在成功連接后為連續(xù)請(qǐng)求保持相同的連接。短連接是指在幾次查詢后斷開連接并重新連接以進(jìn)行后續(xù)查詢。

由于連接過程比較復(fù)雜，建議開發(fā)過程中盡量減少連接的創(chuàng)建，即盡可能使用持久連接。

但是，當(dāng)使用持久連接時(shí)，mysql 的內(nèi)存使用量可能會(huì)顯著增加，因?yàn)閳?zhí)行期間使用的臨時(shí)內(nèi)存是在連接對(duì)象內(nèi)管理的。僅當(dāng)連接終止時(shí)，這些資源才會(huì)被釋放。如果長連接不斷累積，可能會(huì)導(dǎo)致內(nèi)存使用過多，導(dǎo)致系統(tǒng)強(qiáng)行終止mysql（oom），導(dǎo)致意外重啟。

解決方案：

1. 定期斷開持久連接。使用連接一段時(shí)間或執(zhí)行消耗過多內(nèi)存的查詢后，請(qǐng)斷開連接并重新連接以進(jìn)行后續(xù)查詢。
2. 如果您使用的是 mysql 5.7 或更高版本，您可以在執(zhí)行資源密集型操作后使用 mysql_reset_connection 來重新初始化連接資源。此過程不需要重新連接或重新驗(yàn)證，而是將連接重置為剛剛創(chuàng)建的狀態(tài)。

查詢緩存

注意：從 mysql 8.0 開始，查詢緩存功能已被完全刪除，因?yàn)槠浔状笥诶?

當(dāng)mysql收到查詢請(qǐng)求時(shí)，它首先檢查查詢緩存，看看這個(gè)查詢之前是否已經(jīng)執(zhí)行過。之前執(zhí)行過的查詢及其結(jié)果以鍵值對(duì)的形式緩存在內(nèi)存中。鍵是查詢語句，值是結(jié)果。如果在查詢緩存中找到該鍵，則將該值直接返回給客戶端。

如果在查詢緩存中未找到查詢，則過程繼續(xù)。

為什么查詢緩存弊大于利？

查詢緩存失效的情況非常頻繁地發(fā)生。對(duì)表的任何更新都會(huì)清除與該表相關(guān)的所有查詢緩存，導(dǎo)致緩存命中率非常低，除非該表是靜態(tài)配置表。

mysql提供了一種“按需”的方法來使用查詢緩存。通過將參數(shù)query_cache_type設(shè)置為demand，sql語句默認(rèn)不會(huì)使用查詢緩存。要使用查詢緩存，可以顯式指定 sql_cache:

select sql_cache * from t where id=10; 

解析器

如果查詢緩存沒有命中，則語句執(zhí)行過程開始。 mysql 首先需要了解要做什么，因此它會(huì)解析 sql 語句。

解析器首先執(zhí)行詞法分析。輸入的 sql 語句由字符串和空格組成，mysql 會(huì)對(duì)其進(jìn)行分析，以識(shí)別每個(gè)部分代表的含義。例如select標(biāo)識(shí)為查詢語句，t標(biāo)識(shí)為表名，id標(biāo)識(shí)為列。

詞法分析之后，進(jìn)行語法分析。語法分析器根據(jù)詞法分析的結(jié)果判斷sql語句是否符合mysql的語法規(guī)則。

如果存在語法錯(cuò)誤，將會(huì)顯示類似“您的 sql 語法有錯(cuò)誤”的錯(cuò)誤消息。例如，在以下查詢中，select 關(guān)鍵字拼寫錯(cuò)誤：

mysql> elect * from t where id=1;  error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'elect * from t where id=1' at line 1 

優(yōu)化器

解析之后，mysql 知道你想做什么。接下來，優(yōu)化器確定如何執(zhí)行。

當(dāng)一個(gè)表有多個(gè)索引時(shí)，優(yōu)化器決定使用哪個(gè)索引，或者當(dāng)查詢涉及多個(gè)表時(shí)，優(yōu)化器決定表連接的順序。例如，在以下查詢中：

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20; 

查詢可以從檢索 t1 或 t2 中的值開始。兩種方法產(chǎn)生相同的邏輯結(jié)果，但它們的性能可能不同。優(yōu)化器的作用是選擇最有效的計(jì)劃。

優(yōu)化階段結(jié)束后，流程進(jìn)入執(zhí)行器。

執(zhí)行者

執(zhí)行器開始執(zhí)行查詢。

執(zhí)行前，首先檢查當(dāng)前連接是否有查詢表的權(quán)限。如果沒有，則返回錯(cuò)誤，指示權(quán)限不足。 （從查詢緩存返回結(jié)果時(shí)也會(huì)執(zhí)行權(quán)限檢查。）

如果授予權(quán)限，則打開表并繼續(xù)執(zhí)行。在此過程中，執(zhí)行器根據(jù)表的引擎定義與存儲(chǔ)引擎進(jìn)行交互。

例如，假設(shè)表 t 在 id 列上沒有索引。執(zhí)行者的執(zhí)行過程如下：

1. 調(diào)用innodb引擎接口取出表的第一行，檢查id值是否為10，如果不是則跳過；如果是，則將其添加到結(jié)果集中。
2. 調(diào)用引擎接口獲取“下一行”，重復(fù)相同的邏輯，直到檢查完所有行。
3. 執(zhí)行器將累積的結(jié)果集返回給客戶端。

至此，查詢完成。

對(duì)于索引表，該過程涉及使用引擎的預(yù)定義方法來迭代獲取“第一個(gè)匹配行”和“下一個(gè)匹配行”。

慢查詢?nèi)罩局校瑀ows_examined字段表示查詢執(zhí)行過程中掃描的行數(shù)。每次執(zhí)行器調(diào)用引擎檢索數(shù)據(jù)行時(shí)，該值都會(huì)累加。

在某些情況下，對(duì)執(zhí)行器的單次調(diào)用可能涉及在引擎內(nèi)部掃描多行。因此，引擎掃描的行數(shù)不一定等于 rows_examined.

結(jié)尾

感謝您的閱讀！希望文章對(duì)您有所幫助。