Mysql架構(gòu)圖解讀
Mysql服務(wù)端架構(gòu)分為Server層和存儲引擎層(可插拔式),Server層主要包含了連接器、緩存模塊、分析器、優(yōu)化器、執(zhí)行器;可插拔的存儲引擎主要有InnoDB、MyISAM、Memory。
當(dāng)一個請求進(jìn)入后的執(zhí)行流程如下圖的箭頭所示:
一、Server層
1、連接器
連接器主要負(fù)責(zé)與Mysql客戶端建立連接,權(quán)限認(rèn)證,維持和管理鏈接。
當(dāng)開始連接服務(wù)器時需要使用賬號、密碼登錄服務(wù)器,執(zhí)行命令:
mysql -h $ip -P $port -u $root -p $password
如果用戶名驗證不通過則會報錯:Access denied for user;成功則繼續(xù)調(diào)用權(quán)限表,獲取用戶權(quán)限信息,存儲到類似本地線程變量中,后續(xù)分析器、優(yōu)化器中會使用。當(dāng)給當(dāng)前用戶權(quán)限進(jìn)行修改后,自己需要重新登錄才能獲取新的權(quán)限信息,類似我們自己寫權(quán)限相關(guān)代碼時,會將權(quán)限、角色等相關(guān)信息存儲到線程本地變量 ThreadLocal 中。
當(dāng)與服務(wù)器建立鏈接后,我們可以使用 show processlist; 命令查看當(dāng)前鏈接到服務(wù)器的客戶端鏈接信息,如果status為sleep等狀態(tài)表示處于空閑狀態(tài)。記得自己剛開始開發(fā)項目時,客戶端沒有配置好,服務(wù)器請求量很低,特別是晚上,第二天來就會看到報錯:Lost connection to MySQL server during query。就是在連接器模塊報錯的,服務(wù)端配置 wait_timeout 可以設(shè)置客戶端再次請求時,判斷是否超時,為8小時。
與服務(wù)器端的鏈接分為長鏈接和短連接。短連接本身每次都會執(zhí)行TCP的三次握手和四次揮手,并且建立鏈接后會進(jìn)行認(rèn)證和權(quán)限相關(guān)的操作,我們需要防止客戶端并發(fā)量遞增時對服務(wù)器端的短連接風(fēng)暴。現(xiàn)在開發(fā)項目Mysql客戶端基本都基于線程池實現(xiàn),線程本身是復(fù)用的長鏈接,長鏈接的弊端是會在服務(wù)端線程內(nèi)部存儲大量的查詢緩存等信息,只有斷開鏈接時才會釋放。所以最好是使用長鏈接的同時,當(dāng)執(zhí)行了比較大的事務(wù)之后,手動斷開鏈接,釋放資源。在 5.7版本之后,mysql增加了mysql_reset_connection參數(shù)可以將鏈接初始化到剛剛創(chuàng)建鏈接時的狀態(tài),我們可以在長事務(wù)或者定時執(zhí)行該操作。
2、緩存模塊
緩存模塊是一直比較弊病的模塊,在 Mysql 8之后已經(jīng)廢棄掉了。由于查詢時根據(jù)整個sql進(jìn)行hash計算,我們知道hash計算哪怕原字符有一個標(biāo)點(diǎn)不同那么hash值也完全不同,所以一定要sql一模一樣才能匹配緩存。
但是整個表只要有一點(diǎn)修改操作就會將所有緩存進(jìn)行刪除,即可能費(fèi)了很大的力氣進(jìn)行緩存,但是還沒執(zhí)行緩存查詢就被刪除了。
所以緩存只適用于配置表等基本不會變動的數(shù)據(jù),但是個人理解,隨著redis等旁路緩存的普及,基于所有的項目都有緩存,那么數(shù)據(jù)庫的緩存弊病多使用。
- query_cache_type 設(shè)置為 off/on 緩存是否開啟;
- query_cache_size = 0:緩存大小,可以設(shè)置如:128M;
- 也可以將 query_cache_type 設(shè)置為 :DEMAND, 在需要緩存時顯示調(diào)用,如: select SQL_CAHCE * from table where id = 100;
3、分析器(分析需要做什么)
讀執(zhí)行的sql進(jìn)行詞法和語法分析,詞法分析需要解析如select是一個查詢語句,查詢的具體表名,查詢的條件等;語法分析如 sql字符串 是否符合sql規(guī)范,是否符合Mysql 自己的sql規(guī)范等。
一般遇到的
you have an error in your SQL syntax use near...
就是在該模塊報出的。
4、優(yōu)化器(分析怎么做)
優(yōu)化器主要負(fù)責(zé)索引的選擇、多表關(guān)聯(lián)時的 join 順序,這些都具體在后面專門進(jìn)行分析。
5、執(zhí)行器(執(zhí)行過程)
執(zhí)行器負(fù)責(zé)具體調(diào)用底層的存儲引擎接口,處理數(shù)據(jù)。
rows_examined調(diào)用底層引擎接口查詢的條數(shù)或次數(shù),有可能這里調(diào)用一次疊加一次,但是內(nèi)部可能查詢了多行數(shù)據(jù)。
二、存儲引擎層
常用的存儲引擎有InnoDB、MyISAM、Memory,現(xiàn)在很多公司的數(shù)據(jù)庫規(guī)范直接規(guī)定創(chuàng)建表只能使用 InnoDB,不僅僅是因為支持事務(wù),還與運(yùn)維時候的數(shù)據(jù)備份等相關(guān)。
在 Mysql 5.5后,將默認(rèn)的存儲引擎從 MyISAM變更為 InnoDB,只是個人還是比較喜歡或者建議在創(chuàng)建表時,顯示設(shè)置存儲引擎 engine = Innodb。
Memory引擎作為臨時表的默認(rèn)存儲引擎,當(dāng)執(zhí)行復(fù)雜sql或數(shù)據(jù)量比較大需要使用臨時表;或者我們自己手動基于臨時表實現(xiàn)業(yè)務(wù)時,都會用到Memory存儲引擎,所以還是需要關(guān)注的。
存儲引擎需要關(guān)注具體的數(shù)據(jù)結(jié)構(gòu):
InnoDB只支持B+樹的數(shù)據(jù)結(jié)構(gòu),而Memory引擎支持B+樹和Hash索引。
B+樹支持快速的讀寫,并且時間復(fù)雜度為O(logN),支持區(qū)間查詢。
Hash索引讀寫的時間復(fù)雜度近似O(1),比較適合內(nèi)存中使用,不支持區(qū)間查詢。
三、查詢流程
select name from table_1 where email = 'XXX'; // 比如該表使用 innoDB引擎
- 1、【連接器】先使用長鏈接或者短鏈接,使用用戶名和密碼進(jìn)行登錄操作;
- 2、【連接器】執(zhí)行上面的select語句,到連接器;
- 3、【緩存模塊】判斷是否有開啟緩存,或者要查詢緩存,有查詢完成后需要回執(zhí)緩存;
- 4、【解析器】檢查sql的詞法、語法分析,是否sql有問題;
- 5、【優(yōu)化器】判斷是否有索引,是否需要進(jìn)行優(yōu)化等(這里沒有join等操作);
email無索引:
- 6、【執(zhí)行器】、調(diào)用innoDB引擎接口獲取表的第一條數(shù)據(jù),判斷 eamil是否相等,如果是則放入結(jié)果集中,疊加器rows_examined++
- 7、【執(zhí)行器】、重復(fù)執(zhí)行上面的動作,一直到表的最后一行
- 8、【執(zhí)行器】、將結(jié)果集返回給客戶端
email有索引:(之前一直以為是innoDB直接返回了所有滿足條件的結(jié)果集給執(zhí)行器)
- 6、【執(zhí)行器】、調(diào)用InnoDB的滿足條件的第一行數(shù)據(jù),內(nèi)部查詢走索引
- 7、【執(zhí)行器】、獲取滿足條件的下一行
- 8、【執(zhí)行器】、返回結(jié)果集
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
Centos 6.3將Mysql 5.1.61升級為mysql 5.6.19遇到的問題及解決方式
mysql5.6.19已經(jīng)發(fā)布很久了,一直沒有去升級,最近做項目需要mysql5.5以上,索性直接上5.6.19吧,原本以為升級這種事情,分分鐘就完成了,沒想到還是出了各種問題,下面把部分記錄分享給大家2014-07-07mysql中replace into與insert into區(qū)別
本文主要介紹了mysql中replace into與insert into區(qū)別,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01Mysql數(shù)據(jù)庫的日志管理、備份與回復(fù)詳細(xì)圖文教程
備份的主要目的是災(zāi)難恢復(fù),備份還可以測試應(yīng)用、回滾數(shù)據(jù)修改、查詢歷史數(shù)據(jù)、審計等,這篇文章主要給大家介紹了關(guān)于Mysql數(shù)據(jù)庫的日志管理、備份與回復(fù)的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-08-08