mysql中執行查詢(xún)語(yǔ)句的流程分析

這篇文章給大家分享的是有關(guān)中執行查詢(xún)語(yǔ)句的流程分析的內容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

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

select * from student where id=2;

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

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

• server層包括連接器、查詢(xún)緩存、分析器、優(yōu)化器、執行器涵蓋 MySQL 的大多數核心服務(wù)功能，以及所有的內置函數所有跨存儲引擎的功能都在這一層實(shí)現，比如存儲過(guò)程、觸發(fā)器、視等。

• 存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory 等多個(gè)存儲引擎，平常我們比較常用的是innoDB引擎

連接器

我們在使用數據庫之前，需要連接到數據庫，連接語(yǔ)句是

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

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

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

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

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

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

查詢(xún)緩存

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

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

緩存的工作流程是

• 接收SQL，以SQL和一些其他條件為key查找緩存表

• 如果找到了緩存，則直接返回緩存

• 如果沒(méi)有找到緩存，則執行SQL查詢(xún)，包括原來(lái)的SQL解析，優(yōu)化等。

• 執行完SQL查詢(xún)結果以后，將SQL查詢(xún)結果緩存入緩存表

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

當這個(gè)表正在寫(xiě)入數據，則這個(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í)機，衡量打開(kāi)緩存是否對系統有性能提升是一個(gè)很難的話(huà)題

• 通過(guò)緩存命中率判斷, 緩存命中率 = 緩存命中次數 (Qcache_hits) / 查詢(xún)次數 (Com_select)

• 通過(guò)緩存寫(xiě)入率, 寫(xiě)入率 = 緩存寫(xiě)入次數 (Qcache_inserts) / 查詢(xún)次數 (Qcache_inserts)

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

分析器

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

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

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

這個(gè)時(shí)候如果分析器分析出這個(gè)語(yǔ)句有問(wèn)題的時(shí)候會(huì )報錯，比如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ā)現執行SQL查詢(xún)的最佳方案。大多數查詢(xún)優(yōu)化器，包括MySQL的查詢(xún)優(yōu)化器，總或多或少地在所有可能的查詢(xún)評估方案中搜索最佳方案。

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

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

• 重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢(xún)時(shí)，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）

• 優(yōu)化MIN()和MAX()函數（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見(jiàn)下文）

• 提前終止查詢(xún)（比如：使用Limit時(shí)，查找到滿(mǎn)足數量的結果集后會(huì )立即終止查詢(xún)）

• 優(yōu)化排序（在老版本MySQL會(huì )使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內存中對其排序，然后再根據排序結果去讀取數據行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數據行，然后根據給定的列排序。對于I/O密集型應用，效率會(huì )高很多）

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

執行器

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

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

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

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

總結

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

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

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

2.解析器：負責解析和轉發(fā)sql

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

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

5.查詢(xún)執行引擎：執行器執行語(yǔ)句得到數據結果集

6.將數據放回給調用端。