mysql中執(zhí)行查詢語句的流程分析

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

比如，在我們從student表中查詢一個id=2的信息

select * from student where id=2;

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

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

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

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

連接器

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

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

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

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

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

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

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

查詢緩存

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

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

緩存的工作流程是

• 服務(wù)器接收SQL，以SQL和一些其他條件為key查找緩存表

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

• 如果沒有找到緩存，則執(zhí)行SQL查詢，包括原來的SQL解析，優(yōu)化等。

• 執(zhí)行完SQL查詢結(jié)果以后，將SQL查詢結(jié)果緩存入緩存表

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

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

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

下面來說說緩存的使用時機(jī)，衡量打開緩存是否對系統(tǒng)有性能提升是一個很難的話題

• 通過緩存命中率判斷, 緩存命中率 = 緩存命中次數(shù) (Qcache_hits) / 查詢次數(shù) (Com_select)

• 通過緩存寫入率, 寫入率 = 緩存寫入次數(shù) (Qcache_inserts) / 查詢次數(shù) (Qcache_inserts)

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

分析器

在查詢緩存實(shí)效或者是無緩存的時候，這個時候MySQL的server就會利用分析器來分析語句，分析器也叫解析器。

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

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

這個時候如果分析器分析出這個語句有問題的時候會報錯，比如ERROR 1064 (42000): You have an error in your SQL syntax

優(yōu)化器

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

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

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

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

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

• 提前終止查詢（比如：使用Limit時，查找到滿足數(shù)量的結(jié)果集后會立即終止查詢）

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

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

執(zhí)行器

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

執(zhí)行器在執(zhí)行的時候首先判斷該用戶對該表有沒有執(zhí)行權(quán)限，如果沒有則會返回denied之類的錯誤提示。

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

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

總結(jié)

MySQL得到sql語句后，大概流程如下：

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

1.查詢緩存：首先查詢緩存看是否存在k-v緩存

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

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

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

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

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

感謝各位的閱讀！關(guān)于“mysql中執(zhí)行查詢語句的流程分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！