前言
當(dāng)我們?cè)?Mysql 的命令行里輸入一條 SELECT 語(yǔ)句后，可能馬上就會(huì)返回我們想要的數(shù)據(jù)，可能要等很久才返回?cái)?shù)據(jù)，也可能執(zhí)行的過(guò)程中報(bào)了一些錯(cuò)誤。
一條簡(jiǎn)單的 SELECT 的語(yǔ)句背后到底發(fā)生了什么事，它的執(zhí)行邏輯是怎樣的？
我們來(lái)看看當(dāng)輸入一條 SELECT 語(yǔ)句后，Mysql 背后發(fā)生了一些什么事。
MySql 邏輯結(jié)構(gòu)
Mysql 分為客戶端和服務(wù)端。
我們平時(shí)使用的命令行工具是客戶端，客戶端把命令發(fā)送到服務(wù)端，服務(wù)端處理完后，把結(jié)果返回給客戶端。
服務(wù)端是 Mysql 最核心的部分，所有的操作基本都在服務(wù)端完成。
MySql服務(wù)端
在服務(wù)端里又分為 Server 層和存儲(chǔ)引擎層部分。
Server 層里面包含了連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等。
大多數(shù)的核心功能都在這一層，比如所有的內(nèi)置函數(shù)，跨存儲(chǔ)引擎的功能，比如存儲(chǔ)過(guò)程 、觸發(fā)器，視圖等。
如圖，Mysql 的每一次完整操作，都是按順序的上到下執(zhí)行。
客戶端發(fā)起請(qǐng)求 -> 連接器建立連接 -> 查詢緩存或者直接到分析器進(jìn)行 SQL 語(yǔ)句分析 -> 優(yōu)化器優(yōu)化 SQL 執(zhí)行效率 -> 執(zhí)行器操存儲(chǔ)引擎讀取數(shù)據(jù)、返回?cái)?shù)據(jù)。
連接器
連接器負(fù)責(zé)客戶端跟服務(wù)端建立連接、獲取權(quán)限、維持和管理連接。
當(dāng)我們?cè)诳蛻舳藞?zhí)行mysql -u$user -p&pwd命令時(shí)，就會(huì)向服務(wù)器發(fā)起一個(gè)連接請(qǐng)求。
完成TCP握手后，請(qǐng)求來(lái)到連接器這里，連接器首先會(huì)進(jìn)行身份認(rèn)證，也就是看我們輸入的用戶名、密碼是否正確。
如果不正確，則返回Access denied for user錯(cuò)誤給客戶端，結(jié)束流程。
如果認(rèn)證通過(guò)，連接器會(huì)進(jìn)一步到權(quán)限表里查詢用戶所擁有的全部權(quán)限，并加載到內(nèi)存中，在該連接的生命周期內(nèi)，權(quán)限的判斷都基于這里查出來(lái)的權(quán)限。
也就是說(shuō)，如果在連接成功后，管理員修改了該用戶的權(quán)限是不會(huì)馬上生效的，只有該用戶重新連接，重新加載權(quán)限才會(huì)生效。
連接成功后，如果沒(méi)有后續(xù)動(dòng)作，這個(gè)連接會(huì)處于空閑狀態(tài)（Sleep)，并且當(dāng)長(zhǎng)時(shí)間沒(méi)有動(dòng)作時(shí)，Mysql會(huì)自動(dòng)斷開(kāi)該連接，默認(rèn)等待時(shí)長(zhǎng)是8小時(shí)，可以通過(guò)修改 wait_timeout 變量來(lái)修改該時(shí)長(zhǎng)。
長(zhǎng)連接和短連接
長(zhǎng)鏈接指連接成功后，后續(xù)的請(qǐng)求會(huì)一直使用同一個(gè)連接。
短連接指在每次執(zhí)行完很少的幾次查詢后就斷開(kāi)連接，下次查詢?cè)僦匦逻B接。
因?yàn)檫B接的建立過(guò)程比較復(fù)雜，所以建議盡量使用長(zhǎng)連接。
使用長(zhǎng)連接的好處是減少頻繁連接所造成的網(wǎng)絡(luò)開(kāi)銷和系統(tǒng)開(kāi)銷，缺點(diǎn)是內(nèi)存的占用可能會(huì)不斷上漲。
因?yàn)?Mysql 在執(zhí)行過(guò)程中所使用的臨時(shí)內(nèi)存是管理在連接對(duì)象中的，只有當(dāng)連接斷開(kāi)時(shí)，這些使用的資源才會(huì)被釋放掉。
如果長(zhǎng)時(shí)間得不到釋放，而長(zhǎng)連接的不斷增加，內(nèi)存占用也不斷上漲，最后可能會(huì)觸發(fā) OOM (Out Of Memory)，導(dǎo)致 Mysql 被系統(tǒng)強(qiáng)行殺死。
針對(duì)這種情況，參考的解決方案有兩種：
- 定期斷開(kāi)長(zhǎng)連接，使用一段時(shí)間，或者判斷執(zhí)行過(guò)一個(gè)占用內(nèi)存大的查詢時(shí)，斷開(kāi)連接，之后查詢?cè)僦匦逻B接。
- 如果用的是 5.7 或者更新的版本，可以在每次執(zhí)行完一個(gè)比較大的操作后，執(zhí)行 mysql_reset_connection 命令初始化連接資源。這個(gè)過(guò)程不需要重連和權(quán)限驗(yàn)證，只是把連接的狀態(tài)恢復(fù)到剛剛創(chuàng)建的狀態(tài)。
查詢緩存
Mysql 在每次查詢時(shí)， 都會(huì)先在查詢緩存看看有沒(méi)有緩存，如果有，直接取緩存的結(jié)果返回給客戶端；如果沒(méi)有，再繼續(xù)往下執(zhí)行。
查詢緩存優(yōu)點(diǎn)是把上一次查詢的結(jié)果以 key-value 的形式緩存下來(lái)，SQL 語(yǔ)句為 key, 結(jié)果集為 value。
在下次查詢時(shí)如果是相同的查詢語(yǔ)句，直接從緩存返回去，就不用繼續(xù)往下執(zhí)行復(fù)雜的邏輯，節(jié)省了時(shí)間和資源。
在讀多寫(xiě)少的環(huán)境下，可以很大的提升效率。
但查詢緩存有一個(gè)弊端，當(dāng)對(duì)一個(gè)表進(jìn)行更新時(shí)，針對(duì)這個(gè)表的所有緩存都會(huì)失效被清空。
所以當(dāng)在一個(gè)寫(xiě)多讀少的環(huán)境下使用查詢緩存，緩存不但利用率不高，反而不斷的更新和失效會(huì)給數(shù)據(jù)庫(kù)帶來(lái)很大的壓力。
所以不建議使用查詢緩存，這個(gè)功能在 Mysql 8.0 開(kāi)始也被刪除掉了。
分析器
分析器負(fù)責(zé)對(duì) SQL 語(yǔ)句進(jìn)行詞法分析和語(yǔ)法分析。
詞法分析是分析這條 SQL 語(yǔ)句是什么類型的語(yǔ)句，是查詢語(yǔ)句還是更新語(yǔ)句等，然后把 SQL 語(yǔ)句里面的表名和字段名識(shí)別出來(lái)，并檢查表和字段是否存在。
語(yǔ)法分析是分析這條 SQL 語(yǔ)句的語(yǔ)法是否正確，比如 SELECT 這個(gè)關(guān)鍵字有沒(méi)有錯(cuò)，是不是缺少了 FROM 關(guān)鍵字等等。
如果詞法或者語(yǔ)法不正確，就會(huì)拋出錯(cuò)誤給客戶端，并結(jié)束流程。
優(yōu)化器
SQL 語(yǔ)句經(jīng)過(guò)分析器分析無(wú)誤了，就要針對(duì) SQL 語(yǔ)句進(jìn)行優(yōu)化，比如在多個(gè)索引中，選擇哪一個(gè)索引效率會(huì)更快；JOIN 語(yǔ)句選擇哪一張去連接別一張表。
總的來(lái)說(shuō)，優(yōu)化器做的事就是采取不同的選擇、不同的策略去盡可能的讓 SQL 語(yǔ)句的執(zhí)行效率更加高。
執(zhí)行器
到了執(zhí)行器，就開(kāi)始準(zhǔn)備操作存儲(chǔ)引擎，獲取數(shù)據(jù)，封裝結(jié)果集。
在開(kāi)始執(zhí)行之前，執(zhí)行器首先會(huì)判斷用戶有沒(méi)有操作這張表的權(quán)限，如果沒(méi)有則返回錯(cuò)誤；有則打開(kāi)表開(kāi)始讀取數(shù)據(jù)。
執(zhí)行的流程大概如下：
- 調(diào)用 InnoDB 引擎接口讀取這個(gè)表的第一行，判斷這行的數(shù)據(jù)是否符合查詢條件，符合則放到結(jié)果集中，不符合則跳過(guò)。
- 調(diào)用引擎接口取下一行，重復(fù) 1 的判斷邏輯，直到這個(gè)表的最后一行。
- 執(zhí)行器把所有滿足條件的行封裝成結(jié)果集返回給客戶端。
至此，這個(gè)語(yǔ)句就執(zhí)行完成了。
