前言

一直是想知道一條SQL語(yǔ)句是怎么被執(zhí)行的，它執(zhí)行的順序是怎樣的，然后查看總結(jié)各方資料，就有了下面這一篇文章了。

這篇筆記主要記錄mysql的基礎(chǔ)架構(gòu)，一條查詢(xún)語(yǔ)句是如何執(zhí)行的。

下面話(huà)不多說(shuō)了，來(lái)一起看看詳細(xì)的介紹吧

比如，在我們從student表中查詢(xún)一個(gè)id=2的信息

select * from student where id=2;

在解釋這條語(yǔ)句執(zhí)行流程之前，我們看看mysql的基礎(chǔ)架構(gòu)。

圖來(lái)自極客時(shí)間的mysql實(shí)踐,該圖是描述的是MySQL的邏輯架構(gòu)。

• server層包括連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視等。
• 存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎，平常我們比較常用的是innoDB引擎

連接器

我們?cè)谑褂脭?shù)據(jù)庫(kù)之前，需要連接到數(shù)據(jù)庫(kù)，連接語(yǔ)句是

mysql -h $ip -u $username -p $password

而我們的連接器就是處理這個(gè)過(guò)程的，連接器的主要功能是負(fù)責(zé)跟客戶(hù)端建立連接、獲取權(quán)限、維持和管理連接，連接器在使用的過(guò)程中如果該用戶(hù)的權(quán)限改變，是不會(huì)馬上生效的，因?yàn)橛脩?hù)權(quán)限是在連接的時(shí)候讀取的，只能重新連接才可以更新權(quán)限

連接器與客戶(hù)端通信的協(xié)議是tcp協(xié)議的，連接以后可以使用show processlist;看到執(zhí)行的連接數(shù)

同時(shí)在連接時(shí)間內(nèi)超過(guò)8小時(shí)是sleep的狀態(tài)會(huì)自動(dòng)斷開(kāi)，這個(gè)是mysql默認(rèn)設(shè)置，如果一直不斷開(kāi)，那么這個(gè)過(guò)程可以叫做一個(gè)長(zhǎng)連接。

與之對(duì)應(yīng)的有短連接，短連接是指在執(zhí)行一條或幾條的以后斷開(kāi)連接。

當(dāng)不斷使用長(zhǎng)連接的時(shí)候會(huì)占用很大的內(nèi)存資源，在mysql5.7以后可以使用mysql_reset_connection語(yǔ)句來(lái)重新初始化資源。

查詢(xún)緩存

經(jīng)過(guò)連接以后，就連接上數(shù)據(jù)庫(kù)了，這個(gè)時(shí)候可以執(zhí)行語(yǔ)句了。

執(zhí)行語(yǔ)句的時(shí)候，mysql首先是去查詢(xún)緩存，之前有沒(méi)有執(zhí)行過(guò)這樣的語(yǔ)句，mysql會(huì)將之前執(zhí)行過(guò)的語(yǔ)句和結(jié)果以key-value的形式存儲(chǔ)起來(lái)（當(dāng)然有一定的存儲(chǔ)和實(shí)效時(shí)間）。如果存在緩存，則直接返回緩存的結(jié)果。

緩存的工作流程是

• 服務(wù)器接收SQL，以SQL和一些其他條件為key查找緩存表
• 如果找到了緩存，則直接返回緩存
• 如果沒(méi)有找到緩存，則執(zhí)行SQL查詢(xún)，包括原來(lái)的SQL解析，優(yōu)化等。
• 執(zhí)行完SQL查詢(xún)結(jié)果以后，將SQL查詢(xún)結(jié)果緩存入緩存表

當(dāng)然，如果這個(gè)表修改了，那么使用這個(gè)表中的所有緩存將不再有效，查詢(xún)緩存值得相關(guān)條目將被清空。所以在一張被反復(fù)修改的表中進(jìn)行語(yǔ)句緩存是不合適的，因?yàn)榫彺骐S時(shí)都會(huì)實(shí)效，這樣查詢(xún)緩存的命中率就會(huì)降低很多，不是很劃算。

當(dāng)這個(gè)表正在寫(xiě)入數(shù)據(jù)，則這個(gè)表的緩存（命中緩存，緩存寫(xiě)入等）將會(huì)處于失效狀態(tài)，在Innodb中，如果某個(gè)事務(wù)修改了這張表，則這個(gè)表的緩存在事務(wù)提交前都會(huì)處于失效狀態(tài)，在這個(gè)事務(wù)提交前，這個(gè)表的相關(guān)查詢(xún)都無(wú)法被緩存。

一般來(lái)說(shuō)，如果是一張靜態(tài)表或者是很少變化的表就可以進(jìn)行緩存，這樣的命中率就很高。

下面來(lái)說(shuō)說(shuō)緩存的使用時(shí)機(jī)，衡量打開(kāi)緩存是否對(duì)系統(tǒng)有性能提升是一個(gè)很難的話(huà)題

• 通過(guò)緩存命中率判斷, 緩存命中率 = 緩存命中次數(shù) (Qcache_hits) / 查詢(xún)次數(shù) (Com_select)
• 通過(guò)緩存寫(xiě)入率, 寫(xiě)入率 = 緩存寫(xiě)入次數(shù) (Qcache_inserts) / 查詢(xún)次數(shù) (Qcache_inserts)
• 通過(guò) 命中-寫(xiě)入率 判斷, 比率 = 命中次數(shù) (Qcache_hits) / 寫(xiě)入次數(shù) (Qcache_inserts), 高性能MySQL中稱(chēng)之為比較能反映性能提升的指數(shù),一般來(lái)說(shuō)達(dá)到3:1則算是查詢(xún)緩存有效,而最好能夠達(dá)到10:1

分析器

在查詢(xún)緩存實(shí)效或者是無(wú)緩存的時(shí)候，這個(gè)時(shí)候MySQL的server就會(huì)利用分析器來(lái)分析語(yǔ)句，分析器也叫解析器。

MySQL分析器由兩部分組成，第一部分是用來(lái)詞法分析掃描字符流,根據(jù)構(gòu)詞規(guī)則識(shí)別單個(gè)單詞，MySQL使用Flex來(lái)生成詞法掃描程序在sql/lex.h中定義了MySQL關(guān)鍵字和函數(shù)關(guān)鍵字,用兩個(gè)數(shù)組存儲(chǔ)；第二部分的功能是語(yǔ)法分析在詞法分析的基礎(chǔ)上將單詞序列組成語(yǔ)法短語(yǔ),最后生成語(yǔ)法樹(shù),提交給優(yōu)化器語(yǔ)法分析器使用Bison,在sql/sql_yacc.yy中定義了語(yǔ)法規(guī)則。然后根據(jù)關(guān)系代數(shù)理論生成語(yǔ)法樹(shù)。

上面解釋分析器太官方和復(fù)雜了，其實(shí)分析器主要是用來(lái)進(jìn)行“詞法分析”然后知道這個(gè)數(shù)據(jù)庫(kù)語(yǔ)句是要干嘛，代表啥意思。

這個(gè)時(shí)候如果分析器分析出這個(gè)語(yǔ)句有問(wèn)題的時(shí)候會(huì)報(bào)錯(cuò)，比如ERROR 1064 (42000): You have an error in your SQL syntax

優(yōu)化器

在分析器分析完了以后知道這個(gè)語(yǔ)句是干嘛的時(shí)候，接下來(lái)是專(zhuān)門(mén)用一個(gè)優(yōu)化器進(jìn)行語(yǔ)句優(yōu)化，優(yōu)化器的任務(wù)是發(fā)現(xiàn)執(zhí)行SQL查詢(xún)的最佳方案。大多數(shù)查詢(xún)優(yōu)化器，包括MySQL的查詢(xún)優(yōu)化器，總或多或少地在所有可能的查詢(xún)?cè)u(píng)估方案中搜索最佳方案。

優(yōu)化器主要是選擇一個(gè)最佳的執(zhí)行方案，執(zhí)行方案是為了減少開(kāi)銷(xiāo)，提高執(zhí)行效率。

MySQL的優(yōu)化器是一個(gè)非常復(fù)雜的部件，它使用了非常多的優(yōu)化策略來(lái)生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃：

• 重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢(xún)時(shí)，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）
• 優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見(jiàn)下文）
• 提前終止查詢(xún)（比如：使用Limit時(shí)，查找到滿(mǎn)足數(shù)量的結(jié)果集后會(huì)立即終止查詢(xún)）
• 優(yōu)化排序（在老版本MySQL會(huì)使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對(duì)其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對(duì)于I/O密集型應(yīng)用，效率會(huì)高很多）

隨著MySQL的不斷發(fā)展，優(yōu)化器使用的優(yōu)化策略也在不斷的進(jìn)化，這里僅僅介紹幾個(gè)非常常用且容易理解的優(yōu)化策略而已。

執(zhí)行器

在分析器知道語(yǔ)句要干什么，優(yōu)化器知道怎么做以后，下面就到了執(zhí)行的階段，執(zhí)行是交給執(zhí)行器的。

執(zhí)行器在執(zhí)行的時(shí)候首先判斷該用戶(hù)對(duì)該表有沒(méi)有執(zhí)行權(quán)限，如果沒(méi)有則會(huì)返回denied之類(lèi)的錯(cuò)誤提示。

如果有權(quán)限，則會(huì)打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器會(huì)根據(jù)表定義的引擎，去使用該引擎的接口。

最后執(zhí)行語(yǔ)句得到數(shù)據(jù)返回給客戶(hù)端。

總結(jié)

MySQL得到sql語(yǔ)句后，大概流程如下：

0.連接器負(fù)責(zé)和客戶(hù)端進(jìn)行通信

1.查詢(xún)緩存：首先查詢(xún)緩存看是否存在k-v緩存

2.解析器：負(fù)責(zé)解析和轉(zhuǎn)發(fā)sql

3.預(yù)處理器：對(duì)解析后的sql樹(shù)進(jìn)行驗(yàn)證

4.優(yōu)化器：得到一個(gè)執(zhí)行計(jì)劃

5.查詢(xún)執(zhí)行引擎：執(zhí)行器執(zhí)行語(yǔ)句得到數(shù)據(jù)結(jié)果集

6.將數(shù)據(jù)放回給調(diào)用端。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)VeVb武林網(wǎng)的支持。