Mysql架構(gòu)圖解讀

更新時(shí)間：2024年08月13日 09:21:05 作者：it_lihongmin

這篇文章主要介紹了Mysql架構(gòu)圖,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

一、Server層

1、連接器

連接器主要負(fù)責(zé)與Mysql客戶端建立連接，權(quán)限認(rèn)證，維持和管理鏈接。

當(dāng)開(kāi)始連接服務(wù)器時(shí)需要使用賬號(hào)、密碼登錄服務(wù)器，執(zhí)行命令：

mysql -h $ip -P $port -u $root -p $password

如果用戶名驗(yàn)證不通過(guò)則會(huì)報(bào)錯(cuò)：Access denied for user；成功則繼續(xù)調(diào)用權(quán)限表，獲取用戶權(quán)限信息，存儲(chǔ)到類似本地線程變量中，后續(xù)分析器、優(yōu)化器中會(huì)使用。當(dāng)給當(dāng)前用戶權(quán)限進(jìn)行修改后，自己需要重新登錄才能獲取新的權(quán)限信息，類似我們自己寫權(quán)限相關(guān)代碼時(shí)，會(huì)將權(quán)限、角色等相關(guān)信息存儲(chǔ)到線程本地變量 ThreadLocal 中。

當(dāng)與服務(wù)器建立鏈接后，我們可以使用 show processlist； 命令查看當(dāng)前鏈接到服務(wù)器的客戶端鏈接信息，如果status為sleep等狀態(tài)表示處于空閑狀態(tài)。記得自己剛開(kāi)始開(kāi)發(fā)項(xiàng)目時(shí)，客戶端沒(méi)有配置好，服務(wù)器請(qǐng)求量很低，特別是晚上，第二天來(lái)就會(huì)看到報(bào)錯(cuò)：Lost connection to MySQL server during query。就是在連接器模塊報(bào)錯(cuò)的，服務(wù)端配置 wait_timeout 可以設(shè)置客戶端再次請(qǐng)求時(shí)，判斷是否超時(shí)，為8小時(shí)。

與服務(wù)器端的鏈接分為長(zhǎng)鏈接和短連接。短連接本身每次都會(huì)執(zhí)行TCP的三次握手和四次揮手，并且建立鏈接后會(huì)進(jìn)行認(rèn)證和權(quán)限相關(guān)的操作，我們需要防止客戶端并發(fā)量遞增時(shí)對(duì)服務(wù)器端的短連接風(fēng)暴?，F(xiàn)在開(kāi)發(fā)項(xiàng)目Mysql客戶端基本都基于線程池實(shí)現(xiàn)，線程本身是復(fù)用的長(zhǎng)鏈接，長(zhǎng)鏈接的弊端是會(huì)在服務(wù)端線程內(nèi)部存儲(chǔ)大量的查詢緩存等信息，只有斷開(kāi)鏈接時(shí)才會(huì)釋放。所以最好是使用長(zhǎng)鏈接的同時(shí)，當(dāng)執(zhí)行了比較大的事務(wù)之后，手動(dòng)斷開(kāi)鏈接，釋放資源。在 5.7版本之后，mysql增加了mysql_reset_connection參數(shù)可以將鏈接初始化到剛剛創(chuàng)建鏈接時(shí)的狀態(tài)，我們可以在長(zhǎng)事務(wù)或者定時(shí)執(zhí)行該操作。

2、緩存模塊

緩存模塊是一直比較弊病的模塊，在 Mysql 8之后已經(jīng)廢棄掉了。由于查詢時(shí)根據(jù)整個(gè)sql進(jìn)行hash計(jì)算，我們知道hash計(jì)算哪怕原字符有一個(gè)標(biāo)點(diǎn)不同那么hash值也完全不同，所以一定要sql一模一樣才能匹配緩存。

但是整個(gè)表只要有一點(diǎn)修改操作就會(huì)將所有緩存進(jìn)行刪除，即可能費(fèi)了很大的力氣進(jìn)行緩存，但是還沒(méi)執(zhí)行緩存查詢就被刪除了。

所以緩存只適用于配置表等基本不會(huì)變動(dòng)的數(shù)據(jù)，但是個(gè)人理解，隨著redis等旁路緩存的普及，基于所有的項(xiàng)目都有緩存，那么數(shù)據(jù)庫(kù)的緩存弊病多使用。

query_cache_type 設(shè)置為 off/on 緩存是否開(kāi)啟；
query_cache_size = 0：緩存大小，可以設(shè)置如：128M；
也可以將 query_cache_type 設(shè)置為：DEMAND，在需要緩存時(shí)顯示調(diào)用，如： select SQL_CAHCE * from table where id = 100;

3、分析器（分析需要做什么）

讀執(zhí)行的sql進(jìn)行詞法和語(yǔ)法分析，詞法分析需要解析如select是一個(gè)查詢語(yǔ)句，查詢的具體表名，查詢的條件等；語(yǔ)法分析如 sql字符串是否符合sql規(guī)范，是否符合Mysql 自己的sql規(guī)范等。

一般遇到的

you have an error in your SQL syntax use near...

就是在該模塊報(bào)出的。

4、優(yōu)化器（分析怎么做）

優(yōu)化器主要負(fù)責(zé)索引的選擇、多表關(guān)聯(lián)時(shí)的 join 順序，這些都具體在后面專門進(jìn)行分析。

5、執(zhí)行器（執(zhí)行過(guò)程）

執(zhí)行器負(fù)責(zé)具體調(diào)用底層的存儲(chǔ)引擎接口，處理數(shù)據(jù)。

rows_examined調(diào)用底層引擎接口查詢的條數(shù)或次數(shù)，有可能這里調(diào)用一次疊加一次，但是內(nèi)部可能查詢了多行數(shù)據(jù)。

二、存儲(chǔ)引擎層

常用的存儲(chǔ)引擎有InnoDB、MyISAM、Memory，現(xiàn)在很多公司的數(shù)據(jù)庫(kù)規(guī)范直接規(guī)定創(chuàng)建表只能使用 InnoDB，不僅僅是因?yàn)橹С质聞?wù)，還與運(yùn)維時(shí)候的數(shù)據(jù)備份等相關(guān)。

在 Mysql 5.5后，將默認(rèn)的存儲(chǔ)引擎從 MyISAM變更為 InnoDB，只是個(gè)人還是比較喜歡或者建議在創(chuàng)建表時(shí)，顯示設(shè)置存儲(chǔ)引擎 engine = Innodb。

Memory引擎作為臨時(shí)表的默認(rèn)存儲(chǔ)引擎，當(dāng)執(zhí)行復(fù)雜sql或數(shù)據(jù)量比較大需要使用臨時(shí)表；或者我們自己手動(dòng)基于臨時(shí)表實(shí)現(xiàn)業(yè)務(wù)時(shí)，都會(huì)用到Memory存儲(chǔ)引擎，所以還是需要關(guān)注的。

存儲(chǔ)引擎需要關(guān)注具體的數(shù)據(jù)結(jié)構(gòu)：

InnoDB只支持B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)，而Memory引擎支持B+樹(shù)和Hash索引。

B+樹(shù)支持快速的讀寫，并且時(shí)間復(fù)雜度為O(logN)，支持區(qū)間查詢。

Hash索引讀寫的時(shí)間復(fù)雜度近似O(1)，比較適合內(nèi)存中使用，不支持區(qū)間查詢。

三、查詢流程

select name from table_1 where email = 'XXX'; // 比如該表使用 innoDB引擎

1、【連接器】先使用長(zhǎng)鏈接或者短鏈接，使用用戶名和密碼進(jìn)行登錄操作；
2、【連接器】執(zhí)行上面的select語(yǔ)句，到連接器；
3、【緩存模塊】判斷是否有開(kāi)啟緩存，或者要查詢緩存，有查詢完成后需要回執(zhí)緩存；
4、【解析器】檢查sql的詞法、語(yǔ)法分析，是否sql有問(wèn)題；
5、【優(yōu)化器】判斷是否有索引，是否需要進(jìn)行優(yōu)化等（這里沒(méi)有join等操作）；

email無(wú)索引：

6、【執(zhí)行器】、調(diào)用innoDB引擎接口獲取表的第一條數(shù)據(jù)，判斷 eamil是否相等，如果是則放入結(jié)果集中，疊加器rows_examined++
7、【執(zhí)行器】、重復(fù)執(zhí)行上面的動(dòng)作，一直到表的最后一行
8、【執(zhí)行器】、將結(jié)果集返回給客戶端

email有索引：（之前一直以為是innoDB直接返回了所有滿足條件的結(jié)果集給執(zhí)行器）