一、前言

在之前的博文中己經(jīng)介紹過了mysql的編譯安裝，那么這次將介紹一下從一條query語句執(zhí)行過程來剖析mysql是如何工作的。

二、簡介（來源于維基百科）

MySQL（官方發(fā)音為/maskjul/ "My S-Q-L",[1]，但也經(jīng)常讀作/masikwl/ "My Sequel"）原本是一個開放源代碼的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，原開發(fā)者為瑞典的MySQL AB公司，該公司于2008年被升陽微系統(tǒng)（Sun Microsystems）收購。2009年，甲骨文公司（Oracle）收購升陽微系統(tǒng)公司，MySQL成為Oracle旗下產(chǎn)品。

MySQL在過去由于性能高、成本低、可靠性好，已經(jīng)成為最流行的開源數(shù)據(jù)庫，因此被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。隨著MySQL的不斷成熟，它也逐漸用于更多大規(guī)模網(wǎng)站和應(yīng)用，比如維基百科、Google和Facebook等網(wǎng)站。非常流行的開源軟件組合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收購后，Oracle大幅調(diào)漲MySQL商業(yè)版的售價，且甲骨文公司不再支持另一個自由軟件項目OpenSolaris的發(fā)展，因此導(dǎo)致自由軟件社區(qū)們對于Oracle是否還會持續(xù)支持MySQL社區(qū)版（MySQL之中唯一的免費版本）有所隱憂，因此原先一些使用MySQL的開源軟件逐漸轉(zhuǎn)向其它的數(shù)據(jù)庫。例如維基百科已于2013年正式宣布將從MySQL遷移到MariaDB數(shù)據(jù)庫

三、mysql特性（來源于維基百科）

• 使用C和C++編寫，并使用了多種編譯器進行測試，保證源代碼的可移植性。

• 支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell NetWare、NetBSD、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統(tǒng)。

• 為多種編程語言提供了API。這些編程語言包括C、C++、C#、VB.NET、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。

• 支持多線程，充分利用CPU資源，支持多用戶。

• 優(yōu)化的SQL查詢算法，有效地提高查詢速度。

• 既能夠作為一個單獨的應(yīng)用程序在客戶端服務(wù)器網(wǎng)絡(luò)環(huán)境中運行，也能夠作為一個程序庫而嵌入到其他的軟件中。

• 提供多語言支持，常見的編碼如中文的GB 2312、BIG5，日文的Shift JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名。

• 提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑。

• 提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具。

• 可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫。

四、工作原理（之前己經(jīng)介紹過如何編譯安裝mysql）

1、先上架構(gòu)圖。

2、邏輯模塊組成

2.1宏觀解析

總的來說，msyql可以看成二層架構(gòu)，第一層我們通常叫做sql layer，在mysql數(shù)據(jù)庫系統(tǒng)處理底層數(shù)據(jù)之間的工作都是在這一層完成的，包括權(quán)限判斷，sql解析，執(zhí)行的計劃優(yōu)化，query cache的處理等等；第二層就是存儲引擎，我們通常叫做Storage ?Engine layer,也就是底層數(shù)據(jù)存取操作實現(xiàn)部分，由多種存儲引擎共同組成。

sql layer中包含了多個子模塊，下面做一下簡單的介紹 1、初始化模塊 初始化模塊就是在mysql server啟動的時候，對整個系統(tǒng)做各種各樣的初始化操作，比如各種buffer,cache結(jié)構(gòu)的初始化和內(nèi)存空間的申請，各種系統(tǒng)變量的初始化設(shè)定，各種存儲引擎的初始化設(shè)置，等等。

2、核心API

核心API模塊主要是為了提供一些需要非常高效的底層操作功能的優(yōu)化實現(xiàn)，包括各種的底層的數(shù)據(jù)結(jié)構(gòu)的實現(xiàn)，特殊算法的實現(xiàn)，字符串處理，數(shù)字處理等，小文件I/O，格式化輸出，以及最重要的內(nèi)存管理部分。

3、網(wǎng)絡(luò)交互模塊

底層網(wǎng)絡(luò)交互模塊抽象出底層網(wǎng)絡(luò)交互所使用的接口API，實現(xiàn)底層網(wǎng)絡(luò)數(shù)據(jù)的接收與發(fā)送，以方便其他各個模塊調(diào)用，以及對這一部分的維護，所有源代碼都在vio文件夾下面。 4、C/S交互協(xié)議模塊 mysql的C/S交互協(xié)議模塊部分，實現(xiàn)了客戶端與mysql交互過程中的所有協(xié)議。當(dāng)然這些協(xié)議都是建立在現(xiàn)有的OS和網(wǎng)絡(luò)協(xié)議之上的，如TCP/IP以及Unix Socket. 5、用戶模塊 用戶模塊實現(xiàn)的功能，主要包括用戶的登錄連接權(quán)限控制和用戶的授權(quán)管理，他就像mysql 的大門守衛(wèi)一樣，決定是否給來訪者“開門”。 6、訪問控制模塊 實現(xiàn)的功能就是根據(jù)用戶模塊中各用戶的授權(quán)信息，以及數(shù)據(jù)庫自身特有的各種約束，來控制用戶對數(shù)據(jù)的訪問，用戶模塊和訪問控制模塊兩者結(jié)合起來，組成了mysql 整個數(shù)據(jù)庫系統(tǒng)的權(quán)限安全管理的功能。 7、連接管理 連接管理模塊負(fù)責(zé)監(jiān)聽對mysql server的各種請求，接收連接請求，轉(zhuǎn)發(fā)所有連接請求到線程管理模塊。每一個連接上mysql server的客戶端請求都會分配一個獨立連接線程，而連接線程的主要工作就是負(fù)責(zé)mysql server與客戶端通信，接受客戶端的命令請求，傳遞server端的結(jié)果信息。線程管理模塊則負(fù)責(zé)維護這些連接線程，包括線程創(chuàng)建，線程的cache等。 8、Query解析和轉(zhuǎn)發(fā)模塊 在mysql里我們習(xí)慣將所有client端發(fā)送給server端的命令都稱為query，在mysql server里面，連接線程接收到客戶端的一個query后，會直接將該query傳遞給專門負(fù)責(zé)將各種query進行分類然后轉(zhuǎn)發(fā)給各個對應(yīng)的處理模塊，這個模塊就是query解析和轉(zhuǎn)發(fā)模塊，其主要工作就是將query語句進行語義和語法的分析，然后按照不同的操作類型進行分類，然后做出針對性的轉(zhuǎn)發(fā)。 9、query Cache模塊 query Cache模塊在mysql中是一個非常重要的模塊，他的主要功能是將客戶端提交給 MySQL的select類query請求的返回結(jié)果集cache到內(nèi)存中，與該query的一個hash值做一個對應(yīng)。該query所取數(shù)據(jù)的基表發(fā)生任務(wù)數(shù)據(jù)的變化之后，MySQL會自動使該query的cache失效，在讀寫比例非常高的應(yīng)用系統(tǒng)中，query cache 對性能的提高是非常顯著的，當(dāng)然它對內(nèi)存的消耗也是非常大的。 10、Query優(yōu)化器模塊 query 優(yōu)化器，顧名思義，就是優(yōu)化客戶端請求的query，根據(jù)客戶端請求的query語句，和數(shù)據(jù)庫中的一些統(tǒng)計信息。在一系列算法的基礎(chǔ)上進行分析，得出一個最優(yōu)的策略，告訴后面的程序如何取得這個query語句的結(jié)果。 11、表變更管理模塊 表變更管理模塊主要中負(fù)責(zé)完成一些DML和DDL的query，如：update，delete,insert,create table alter table等語句的處理。 12、表維護模塊 表的狀態(tài)檢查，錯誤修復(fù)，以及優(yōu)化和分析等工作都是表維護模塊需要做的事情。 13、系統(tǒng)狀態(tài)管理模塊 系統(tǒng)狀態(tài)管理模塊負(fù)責(zé)在客戶端請求系統(tǒng)狀態(tài)的時候，將各種狀態(tài)的數(shù)據(jù)返回給用戶，像DBA常用的各種show status命令，show variables命令符，所得到的結(jié)果都是由這個模塊返回的。 14、表管理器 這個模塊從名字上看來很容易和上面的表變更和表維護模塊相混淆，但是其功能與變更及維護模塊卻完全不同，每一個mysql的表都是一個表的定義文件，也就是*.frm文件。表管理器的工作主要就是維護這些文件。以及一個cache，該chace中的主要內(nèi)容是各表的結(jié)構(gòu)信息。此外，它還維護table級別的鎖管理。 15、日志記錄模塊 日志記錄模塊主要負(fù)責(zé)整個系統(tǒng)級別邏輯層的日志的記錄，包括error log,binary log,slow log等。 16、復(fù)制模塊 復(fù)制模塊又分為master模塊和slave模塊兩部分，master模塊主要負(fù)責(zé)在replicatin環(huán)境中讀取master端的binary日志，以及與slave端的I/O線程交互等工作。slave模塊比master模塊所要做的事情稍多一些，在系統(tǒng)中主要體現(xiàn)在兩個線程上面。一個是負(fù)責(zé)從master請求和接受binary日志，并寫入本地relay log的I/O線程。另外一個是負(fù)責(zé)從relay log中讀取相關(guān)的日志事件，然后解析成可以在slave端正確執(zhí)行并得到和master端完全相同的結(jié)果的命令并再交給slave執(zhí)行的過程。 17、存儲引擎接口模塊 基本上只有mysql可以實現(xiàn)其底層數(shù)據(jù)存儲引擎的插件式管理 。這個模塊實際上只是一抽象類，但正因為它成功的將各種數(shù)據(jù)處理高度抽象化。才成就了今天mysql可插拔儲引擎的特色。

2.2微觀解析

1、發(fā)起連接

當(dāng)client apps發(fā)起一條sql語句為例(select * from mysql.user;)由監(jiān)聽客戶端的連接管理模塊會將連接請求轉(zhuǎn)給線程管理模塊，去請求一個連接線程，而這時就到了線程管理模塊，連接線程模塊在接在連接請求后，首先會檢查當(dāng)前連接線程池中是否有被cache的空閑的連接線程，如果有，就取出一個和客戶端請求連接上，如果沒有空閑的連接線程，則建立一個新的連接請求。當(dāng)然，連接線程模塊并不是在收到連接請求后馬上就會取出一個連接線程和客戶端連接，而是首先通過調(diào)用用戶模塊來進行授權(quán)檢查，只有客戶端請求通過了授權(quán)檢查后，他才會將客戶端請求和負(fù)責(zé)請求的連接線程連上。

2、請求Query

①、如果是一個 Query 類型的請求，會將控制權(quán)交給 Query 解析器。 Query 解析器首先解析,而解析（包含對語法，表、視圖是否存在判斷，怎么執(zhí)行，比較那個方案更優(yōu))會消耗很多資源：cpu、IO、memory，如果還有其它用戶執(zhí)行同樣的操作，那么第一次執(zhí)行的語句將被緩存在cache中(是否緩存可以通過SQL_CACHE和SQL_N0_CACHE手動控制緩存)，如果存在，就直接執(zhí)行執(zhí)行計劃，將cache中的數(shù)據(jù)返回給連接線程模塊，然后通過與客戶端的連接的線程將數(shù)據(jù)傳輸給客戶端。（cache的作用是緩存sql語名,或sql語句所對應(yīng)的執(zhí)行計劃;）

②、如果不是一個可以被cache的query類型，或者cache中沒有該query的數(shù)據(jù)，那么query將被繼續(xù)傳回query解析器（此前查找cache所用的時間將白白浪費），讓 query解析器進行相應(yīng)處理，再通過 query 分發(fā)器分發(fā)給相關(guān)處理模塊。

③、如果解析器解析結(jié)果是一條未被 cache 的select語句，則將控制權(quán)交給 Optimizer，也就是 Query 優(yōu)化器模塊，主要負(fù)責(zé)對sql語句進行解析（prase）利用內(nèi)部算法對sql進行解析，生成解析樹（parse tree）及執(zhí)行計劃（execution plan）。（解析：一條sql語句有N條執(zhí)行方案，當(dāng)選擇最優(yōu)的方案過程，是最消耗資源的）執(zhí)行sql，交由訪問控制模塊執(zhí)行后續(xù)操作，并返回結(jié)果（execute and return)

④、如果是 DML 或者是 DDL 語句，則會交給表變更管理模塊。

⑤、如果是一些更新統(tǒng)計信息、檢測、修復(fù)和整理類的 query 則會交給表維護模塊去處理。

⑥、復(fù)制相關(guān)的query 則轉(zhuǎn)交給復(fù)制模塊去進行相應(yīng)的處理。

⑦、請求狀態(tài)的query 則轉(zhuǎn)交給了狀態(tài)收集報告模塊。實際上表變更管理模塊根據(jù)所對應(yīng)的處理請求的不同，是分別由 insert 處理器、delete處理器、update 處理器、create 處理器，以及 alter 處理器這些小模塊來負(fù)責(zé)不同的 DML和 DDL 的。在各個模塊收到 Query 解析與分發(fā)模塊分發(fā)過來的請求后，首先會通過訪問控制模塊檢查連接用戶是否有訪問目標(biāo)表以及目標(biāo)字段的權(quán)限，如果有，就會調(diào)用表管理模塊請求相應(yīng)的表，并獲取對應(yīng)的鎖。表管理模塊首先會查看該表是否已經(jīng)存在于table cache 中，如果已經(jīng)打開則直接進行鎖相關(guān)的處理，如果沒有在 cache 中，則需要再打開表文件獲取鎖， 然后將打開的表交給表變更管理模塊。當(dāng)表變更管理模塊“獲取”打開的表之后，就會根據(jù)該表的相關(guān) meta 信，判斷表的存儲引擎類型和其他相關(guān)信息。根據(jù)表的存儲引擎類型，提交請求給存儲引擎接口模塊，調(diào)用對應(yīng)的存儲引擎實現(xiàn)模塊，進行相應(yīng)處理。不過，對于表變更管理模塊來說，可見的僅是存儲引擎接口模塊所提供的一系列 “標(biāo)準(zhǔn)”接口，底層存儲引擎實現(xiàn)模塊的具體實現(xiàn)，對于表變更管理模塊來說是透明的。他只需要調(diào)用對應(yīng)的接口，并指明表類型，接口模塊會根據(jù)表類型調(diào)用正確的存儲引擎來進行相應(yīng)的處理。

3、取回結(jié)果

當(dāng)一條 query 或者一個 command 處理完成（成功或者失敗）之后，控制權(quán)都會交還給連接線程模塊。如果處理成功，則將處理結(jié)果（可能是一個 Result set，也可能是成功或者失敗的標(biāo)識）通過連接線程反饋給客戶端。如果處理過程中發(fā)生錯誤，也會將相應(yīng)的錯誤信息發(fā)送給客戶端，然后連接線程模塊會進行相應(yīng)的清理工作，并繼續(xù)等待后面的請求，重復(fù)上面提到的過程，或者完成客戶端斷開連接的請求。如果在上面的過程中，相關(guān)模塊使數(shù)據(jù)庫中的數(shù)據(jù)發(fā)生了變化，而且 MySQL 打開了 binlog 功能，則對應(yīng)的處理模塊還會調(diào)用日志處理模塊將相應(yīng)的變更語句以更新事件的形式記錄到相關(guān)參數(shù)指定的二進制日志文件中。

緩存相關(guān)注意事項：

1、cache查找方式

mysql利用內(nèi)部的hash算法來取得該sql的hash值，然后在cache里查找是否存在該hash值；假設(shè)存在，則將此sql與cache中的進行比較；假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計劃，而省略了優(yōu)化器的相關(guān)工作。這也就是緩存命中的過程。誠然，如果上面的兩個假設(shè)中任有一個不成立，那么優(yōu)化器都將進行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。

2、解析的弊端

創(chuàng)建解析樹、生成執(zhí)行計劃對于sql的執(zhí)行來說是開銷昂貴的動作，所以，應(yīng)當(dāng)極力避免優(yōu)化器創(chuàng)建解析樹、生成執(zhí)行計劃的動作。

3、不會緩存的數(shù)據(jù)

查詢語句中有一些不確定數(shù)據(jù)時，例如NOW(), CURRENT_TIME()；一般來說，如果查詢中包含用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、mysql庫中系統(tǒng)表、或者任何包含權(quán)限的表，一般都不會緩存；

4、緩存會帶來額外開銷

每個查詢都得先檢查是否命中，查詢結(jié)果要先緩存；

5、如何判斷命令率

mysql> SHOW GLOBAL STATUS LIKE'Qcache%';

6、計算命中率

⑴、第一種方式

mysql>SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits'OR Variable_name='Com_select';

公式：Qcache_hits/(Com_select+Qcache_hits)

⑵、第二種方式

可以通過linux系統(tǒng)命令iostat 1 10或vmstat 1 10

⑶、第三種方式

也應(yīng)該參考另外一個指標(biāo)：命中和寫入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，則表明緩存也是有效的。能達(dá)到10:1，為比較理想的情況。

mysql> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits'OR Variable_name='Com_select'orvariable_name='Qcache_inserts';

7、緩存優(yōu)化使用思路 ⑴、批量寫入而非多次單個寫入； ⑵、緩存空間不宜過大，因為大量緩存同時失效時會導(dǎo)致服務(wù)器假死； ⑶、必要時，使用SQL_CACHE和SQL_N0_CACHE手動控制緩存； ⑷、對寫密集型的應(yīng)用場景來說，禁用緩存反而能提高性能。

========================================完=========================================

本文轉(zhuǎn)自 jinlinger 51CTO博客，原文鏈接：http://blog.51cto.com/essun/1393124，如需轉(zhuǎn)載請自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的基于query语句解析mysql工作原理的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。