1.前言

一條sql語句到底在執(zhí)行時(shí)經(jīng)歷了什么？探究這個(gè)問題是學(xué)習(xí)mysql的重要步驟，面試時(shí)常被問到，也使得學(xué)習(xí)mysql時(shí)也有了知識(shí)框架的支撐，明白我們背的知識(shí)點(diǎn)到底用在哪里，筆者覺得這一點(diǎn)還是很重要的。

注：對(duì)一個(gè)知識(shí)點(diǎn)的總結(jié)不僅包含知識(shí)點(diǎn)本身，還包含對(duì)該知識(shí)點(diǎn)的聯(lián)想，這個(gè)聯(lián)想是在面試時(shí)可能被追問的，也可以自己主動(dòng)說出來（我還知道。。。）加分的。

2.知識(shí)點(diǎn)

MySQL 執(zhí)行流程是怎樣的？

首先要知道的是，我們可以把mysql分成兩層，server層和數(shù)據(jù)庫引擎層，前者主要是對(duì)我們的查詢進(jìn)行處理（主要包括 {連接器}，{查詢緩存}、{解析器}、{預(yù)處理器、優(yōu)化器、執(zhí)行器} 等），后者是數(shù)據(jù)真正存儲(chǔ)的地方（從 MySQL 5.5 版本開始， InnoDB 成為了 MySQL 的默認(rèn)存儲(chǔ)引擎）。

一條查詢的執(zhí)行流程如下：

第一步：通過連接器連接 MySQL 服務(wù)

mysql -h$ip -u$user -p

[連接器聯(lián)想1]: 連接經(jīng)過TCP 三次握手，斷開經(jīng)過四次揮手

[連接器聯(lián)想2]: 如果用戶密碼都沒有問題，連接器就會(huì)獲取該用戶的權(quán)限，然后保存起來，后續(xù)該用戶在此連接里的任何操作，都會(huì)基于連接開始時(shí)讀到的權(quán)限進(jìn)行權(quán)限邏輯的判斷，意思是管理員修改已登錄用戶的權(quán)限需要等他重新登錄才生效

[連接器聯(lián)想3]: 如何查看 MySQL 服務(wù)被多少個(gè)客戶端連接了？show processlist[連接器聯(lián)想4]: 空閑連接會(huì)一直占用著嗎？MySQL 定義了空閑連接的最大空閑時(shí)長，由 wait_timeout 參數(shù)控制的，默認(rèn)值是 8 小時(shí)（28880秒），如果空閑連接超過了這個(gè)時(shí)間，連接器就會(huì)自動(dòng)將它斷開。

[連接器聯(lián)想5]: MySQL 的連接數(shù)有限制嗎？最大連接數(shù)由 max_connections 參數(shù)控制，超過這個(gè)值，系統(tǒng)就會(huì)拒絕接下來的連接請(qǐng)求，并報(bào)錯(cuò)提示“Too many connections”。

[連接器聯(lián)想6]: 怎么解決長連接占用內(nèi)存的問題？MySQL 的連接也跟 HTTP 一樣，有短連接和長連接的概念，長連接的好處就是可以減少建立連接和斷開連接的過程，但是，使用長連接后可能會(huì)占用內(nèi)存增多，因?yàn)?MySQL 在執(zhí)行查詢過程中臨時(shí)使用內(nèi)存管理連接對(duì)象，這些連接對(duì)象資源只有在連接斷開時(shí)才會(huì)釋放。有兩種解決方式。第一種，定期斷開長連接。第二種，客戶端主動(dòng)重置連接。

MySQL 5.7 版本實(shí)現(xiàn)了 mysql_reset_connection() 函數(shù)的接口來重置連接，達(dá)到釋放內(nèi)存的效果。這個(gè)過程不需要重連和重新做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)。

[連接器聯(lián)想7]: 連接器的工作？與客戶端進(jìn)行 TCP 三次握手建立連接；校驗(yàn)客戶端的用戶名和密碼，如果用戶名或密碼不對(duì)，則會(huì)報(bào)錯(cuò)；如果用戶名和密碼都對(duì)了，會(huì)讀取該用戶的權(quán)限，然后后面的權(quán)限邏輯判斷都基于此時(shí)讀取到的權(quán)限；

第二步：查詢緩存

連接器得工作完成后，客戶端就可以向 MySQL 服務(wù)發(fā)送 SQL 語句了，MySQL 服務(wù)收到 SQL 語句后，就會(huì)解析出 SQL 語句的第一個(gè)字段，看看是什么類型的語句。

如果 SQL 是查詢語句（select 語句），MySQL 就會(huì)先去查詢緩存（ Query Cache ）里查找緩存數(shù)據(jù)。

但是其實(shí)查詢緩存挺雞肋的。對(duì)于更新比較頻繁的表，查詢緩存的命中率很低的，因?yàn)橹灰粋€(gè)表有更新操作，那么這個(gè)表的查詢緩存就會(huì)被清空。

所以，MySQL 8.0 版本直接將server層查詢緩存刪掉了。

第三步：解析SQL

在正式執(zhí)行 SQL 查詢語句之前， MySQL 會(huì)先對(duì) SQL 語句做解析，這個(gè)工作交由「解析器」來完成。

解析器會(huì)做兩件事情:詞法分析、 語法分析。

[解釋器聯(lián)想1]: 詞法分析：MySQL 會(huì)根據(jù)你輸入的字符串識(shí)別出關(guān)鍵字出來，例如，SQL語句 select username from userinfo，在分析之后，會(huì)得到4個(gè)Token，其中有2個(gè)Keyword，分別為select和from.
[解釋器聯(lián)想2]: 語法分析：根據(jù)詞法分析的結(jié)果，語法解析器會(huì)根據(jù)語法規(guī)則，判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法，如果沒問題就會(huì)構(gòu)建出 SQL 語法樹，這樣方便后面模塊獲取 SQL 類型、表名、字段名、 where 條件等等。
[解釋器聯(lián)想3]: 解如果我們輸入的 SQL 語句語法不對(duì)，就會(huì)在解析器這個(gè)階段報(bào)錯(cuò)。（釋器的主要作用）
[解釋器聯(lián)想4]: 解釋器只負(fù)責(zé)檢查語法和構(gòu)建語法樹，但是不會(huì)去查表或者字段存不存在。

第四步：執(zhí)行 SQL

解析SQL無誤后，執(zhí)行SQL需要經(jīng)過三個(gè)步驟：預(yù)處理器、優(yōu)化器、執(zhí)行器。

預(yù)處理器

• 檢查 SQL 查詢語句中的表或者字段是否存在；
• 將 select * 中的 * 符號(hào)，擴(kuò)展為表上的所有列；

優(yōu)化器

優(yōu)化器主要負(fù)責(zé)將 SQL 查詢語句的執(zhí)行計(jì)劃確定下來，比如在表里面有多個(gè)索引的時(shí)候，優(yōu)化器會(huì)基于查詢成本的考慮，來決定選擇使用哪個(gè)索引。

[優(yōu)化器聯(lián)想1]: 要想知道優(yōu)化器選擇了哪個(gè)索引，我們可以在查詢語句最前面加個(gè) explain 命令，這樣就會(huì)輸出這條 SQL 語句的執(zhí)行計(jì)劃。explain select * from product where id = 1[優(yōu)化器聯(lián)想2]: 一般來講普通索引查詢效率高于主鍵索引，當(dāng)索引覆蓋時(shí)會(huì)先考慮普通索引的B+樹上查詢，這就是執(zhí)行計(jì)劃，是優(yōu)化器決定的。

執(zhí)行器

確定了執(zhí)行計(jì)劃，接下來 MySQL 就真正開始執(zhí)行語句了，在執(zhí)行的過程中，執(zhí)行器就會(huì)和存儲(chǔ)引擎交互了，交互是以記錄為單位的。

• 主鍵索引查詢 select * from product where id = 1; 讓InnoDB引擎通過主鍵索引B+樹搜索id=1的記錄。
• 全表掃描 select * from product where name = 'iphone'; 查詢條件沒有用到索引，觸發(fā)全表掃描，查詢每一條記錄判斷是否滿足條件。
• 索引下推 （MySQL 5.6 推出的查詢優(yōu)化策略）

[索引下推聯(lián)想1]: 索引下推能夠減少二級(jí)索引在查詢時(shí)的回表操作，提高查詢的效率，因?yàn)樗鼘?Server 層部分負(fù)責(zé)的事情，交給存儲(chǔ)引擎層去處理了。select * from t_user where age > 20 and reward = 100000;不使用索引下推（MySQL 5.6 之前的版本）時(shí)，定位到 age > 20 的一條記錄，獲取主鍵值，然后進(jìn)行回表操作，將完整的記錄返回給 Server 層，Server 層再判斷該記錄的 reward 是否等于 100000。而使用索引下推后，判斷記錄的 reward 是否等于 100000 的工作交給了存儲(chǔ)引擎層：定位到 age > 20 的第一條記錄，存儲(chǔ)引擎定位到二級(jí)索引后，先不執(zhí)行回表操作，而是先判斷一下該索引中包含的列（reward列）的條件（reward 是否等于 100000）是否成立。如果條件不成立，則直接跳過該二級(jí)索引。如果成立，則執(zhí)行回表操作，將完成記錄返回給 Server 層。

MySQL 執(zhí)行流程是怎樣的？總結(jié)：

（總結(jié)只是簡單總結(jié)，也就是被問到時(shí)該說的，上面的知識(shí)點(diǎn)，是可能被追問時(shí)涉及的，或者自己說出來的加分項(xiàng)。）

• 連接器：建立連接，管理連接、校驗(yàn)用戶身份；
• 查詢緩存：查詢語句如果命中查詢緩存則直接返回，否則繼續(xù)往下執(zhí)行。MySQL 8.0 已刪除該模塊；
• 解析 SQL，通過解析器對(duì) SQL 查詢語句進(jìn)行詞法分析、語法分析，然后構(gòu)建語法樹，方便后續(xù)模塊讀取表名、字段、語句類型；
• 執(zhí)行 SQL：執(zhí)行 SQL 共有三個(gè)階段：
  • 預(yù)處理階段：檢查表或字段是否存在；將 select * 中的 * 符號(hào)擴(kuò)展為表上的所有列。
  • 優(yōu)化階段：基于查詢成本的考慮， 選擇查詢成本最小的執(zhí)行計(jì)劃；
  • 執(zhí)行階段：根據(jù)執(zhí)行計(jì)劃執(zhí)行 SQL 查詢語句，從存儲(chǔ)引擎讀取記錄，返回給客戶端；

到此這篇關(guān)于MySql一條查詢語句的執(zhí)行流程究竟是怎么樣的文章就介紹到這了,更多相關(guān)MySql查詢語句執(zhí)行流程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論