基于query語句解析mysql工作原理

一、前言

在之前的博文中己經介紹過了mysql的編譯安裝，那麼這次将介紹一下從一條query語句執行過程來剖析mysql是如何工作的。

MySQL（官方發音為/maskjul/ "My S-Q-L",[1]，但也經常讀作/masikwl/ "My Sequel"）原本是一個開放源代碼的關系資料庫管理系統，原開發者為瑞典的MySQL AB公司，該公司于2008年被升陽微系統（Sun Microsystems）收購。2009年，甲骨文公司（Oracle）收購升陽微系統公司，MySQL成為Oracle旗下産品。

MySQL在過去由于性能高、成本低、可靠性好，已經成為最流行的開源資料庫，是以被廣泛地應用在Internet上的中小型網站中。随着MySQL的不斷成熟，它也逐漸用于更多大規模網站和應用，比如維基百科、Google和Facebook等網站。非常流行的開源軟體組合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收購後，Oracle大幅調漲MySQL商業版的售價，且甲骨文公司不再支援另一個自由軟體項目OpenSolaris的發展，是以導緻自由軟體社群們對于Oracle是否還會持續支援MySQL社群版（MySQL之中唯一的免費版本）有所隐憂，是以原先一些使用MySQL的開源軟體逐漸轉向其它的資料庫。例如維基百科已于2013年正式宣布将從MySQL遷移到MariaDB資料庫

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

支援AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell NetWare、NetBSD、OpenBSD、OS/2 Wrap、Solaris、Windows等多種作業系統。

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

支援多線程，充分利用CPU資源，支援多使用者。

優化的SQL查詢算法，有效地提高查詢速度。

既能夠作為一個單獨的應用程式在用戶端伺服器網絡環境中運作，也能夠作為一個程式庫而嵌入到其他的軟體中。

提供多語言支援，常見的編碼如中文的GB 2312、BIG5，日文的Shift JIS等都可以用作資料表名和資料列名。

提供TCP/IP、ODBC和JDBC等多種資料庫連接配接途徑。

提供用于管理、檢查、優化資料庫操作的管理工具。

可以處理擁有上千萬條記錄的大型資料庫。

1、先上架構圖。

<a href="http://s3.51cto.com/wyfs02/M02/23/DD/wKioL1NFT0KT0W8QAANmCYptt-Q282.jpg" target="_blank"></a>

2、邏輯子產品組成

2.1宏觀解析

總的來說，msyql可以看成二層架構，第一層我們通常叫做sql layer，在mysql資料庫系統處理底層資料之間的工作都是在這一層完成的，包括權限判斷，sql解析，執行的計劃優化，query cache的處理等等；第二層就是存儲引擎，我們通常叫做Storage  Engine layer,也就是底層資料存取操作實作部分，由多種存儲引擎共同組成。

sql layer中包含了多個子子產品，下面做一下簡單的介紹

1、初始化子產品

初始化子產品就是在mysql server啟動的時候，對整個系統做各種各樣的初始化操作，比如各種buffer,cache結構的初始化和記憶體空間的申請，各種系統變量的初始化設定，各種存儲引擎的初始化設定，等等。

2、核心API

核心API子產品主要是為了提供一些需要非常高效的底層操作功能的優化實作，包括各種的底層的資料結構的實作，特殊算法的實作，字元串處理，數字處理等，小檔案I/O，格式化輸出，以及最重要的記憶體管理部分。

3、網絡互動子產品

底層網絡互動子產品抽象出底層網絡互動所使用的接口API，實作底層網絡資料的接收與發送，以友善其他各個子產品調用，以及對這一部分的維護，所有源代碼都在vio檔案夾下面。

4、C/S互動協定子產品

mysql的C/S互動協定子產品部分，實作了用戶端與mysql互動過程中的所有協定。當然這些協定都是建立在現有的OS和網絡協定之上的，如TCP/IP以及Unix Socket.

5、使用者子產品

使用者子產品實作的功能，主要包括使用者的登入連接配接權限控制和使用者的授權管理，他就像mysql 的大門守衛一樣，決定是否給來訪者“開門”。

6、通路控制子產品

實作的功能就是根據使用者子產品中各使用者的授權資訊，以及資料庫自身特有的各種限制，來控制使用者對資料的通路，使用者子產品和通路控制子產品兩者結合起來，組成了mysql 整個資料庫系統的權限安全管理的功能。

7、連接配接管理

連接配接管理子產品負責監聽對mysql server的各種請求，接收連接配接請求，轉發所有連接配接請求到線程管理子產品。每一個連接配接上mysql server的用戶端請求都會配置設定一個獨立連接配接線程，而連接配接線程的主要工作就是負責mysql server與用戶端通信，接受用戶端的指令請求，傳遞server端的結果資訊。線程管理子產品則負責維護這些連接配接線程，包括線程建立，線程的cache等。

8、Query解析和轉發子產品

在mysql裡我們習慣将所有client端發送給server端的指令都稱為query，在mysql server裡面，連接配接線程接收到用戶端的一個query後，會直接将該query傳遞給專門負責将各種query進行分類然後轉發給各個對應的處理子產品，這個子產品就是query解析和轉發子產品，其主要工作就是将query語句進行語義和文法的分析，然後按照不同的操作類型進行分類，然後做出針對性的轉發。

9、query Cache子產品

query Cache子產品在mysql中是一個非常重要的子產品，他的主要功能是将用戶端送出給 MySQL的select類query請求的傳回結果集cache到記憶體中，與該query的一個hash值做一個對應。該query所取資料的基表發生任務資料的變化之後，MySQL會自動使該query的cache失效，在讀寫比例非常高的應用系統中，query cache 對性能的提高是非常顯著的，當然它對記憶體的消耗也是非常大的。

10、Query優化器子產品

query 優化器，顧名思義，就是優化用戶端請求的query，根據用戶端請求的query語句，和資料庫中的一些統計資訊。在一系列算法的基礎上進行分析，得出一個最優的政策，告訴後面的程式如何取得這個query語句的結果。

11、表變更管理子產品

表變更管理子產品主要中負責完成一些DML和DDL的query，如：update，delete,insert,create table alter table等語句的處理。

12、表維護子產品

表的狀态檢查，錯誤修複，以及優化和分析等工作都是表維護子產品需要做的事情。

13、系統狀态管理子產品

系統狀态管理子產品負責在用戶端請求系統狀态的時候，将各種狀态的資料傳回給使用者，像DBA常用的各種show status指令，show variables指令符，所得到的結果都是由這個子產品傳回的。

14、表管理器

這個子產品從名字上看來很容易和上面的表變更和表維護子產品相混淆，但是其功能與變更及維護子產品卻完全不同，每一個mysql的表都是一個表的定義檔案，也就是*.frm檔案。表管理器的工作主要就是維護這些檔案。以及一個cache，該chace中的主要内容是各表的結構資訊。此外，它還維護table級别的鎖管理。

15、日志記錄子產品

日志記錄子產品主要負責整個系統級别邏輯層的日志的記錄，包括error log,binary log,slow log等。

16、複制子產品

複制子產品又分為master子產品和slave子產品兩部分，master子產品主要負責在replicatin環境中讀取master端的binary日志，以及與slave端的I/O線程互動等工作。slave子產品比master子產品所要做的事情稍多一些，在系統中主要展現在兩個線程上面。一個是負責從master請求和接受binary日志，并寫入本地relay log的I/O線程。另外一個是負責從relay log中讀取相關的日志事件，然後解析成可以在slave端正确執行并得到和master端完全相同的結果的指令并再交給slave執行的過程。

17、存儲引擎接口子產品

基本上隻有mysql可以實作其底層資料存儲引擎的插件式管理 。這個子產品實際上隻是一抽象類，但正因為它成功的将各種資料處理高度抽象化。才成就了今天mysql可插拔儲引擎的特色。

2.2微觀解析

1、發起連接配接

當client apps發起一條sql語句為例(select * from mysql.user;)由監聽用戶端的連接配接管理子產品會将連接配接請求轉給線程管理子產品，去請求一個連接配接線程，而這時就到了線程管理子產品，連接配接線程子產品在接在連接配接請求後，首先會檢查目前連接配接線程池中是否有被cache的空閑的連接配接線程，如果有，就取出一個和用戶端請求連接配接上，如果沒有空閑的連接配接線程，則建立一個新的連接配接請求。當然，連接配接線程子產品并不是在收到連接配接請求後馬上就會取出一個連接配接線程和用戶端連接配接，而是首先通過調用使用者子產品來進行授權檢查，隻有用戶端請求通過了授權檢查後，他才會将用戶端請求和負責請求的連接配接線程連上。

2、請求Query

①、如果是一個 Query 類型的請求，會将控制權交給 Query 解析器。 Query 解析器首先解析,而解析（包含對文法，表、視圖是否存在判斷，怎麼執行，比較那個方案更優)會消耗很多資源：cpu、IO、memory，如果還有其它使用者執行同樣的操作，那麼第一次執行的語句将被緩存在cache中(是否緩存可以通過SQL_CACHE和SQL_N0_CACHE手動控制緩存)，如果存在，就直接執行執行計劃，将cache中的資料傳回給連接配接線程子產品，然後通過與用戶端的連接配接的線程将資料傳輸給用戶端。（cache的作用是緩存sql語名,或sql語句所對應的執行計劃;）

②、如果不是一個可以被cache的query類型，或者cache中沒有該query的資料，那麼query将被繼續傳回query解析器（此前查找cache所用的時間将白白浪費），讓 query解析器進行相應處理，再通過 query 分發器分發給相關處理子產品。

③、如果解析器解析結果是一條未被 cache 的select語句，則将控制權交給 Optimizer，也就是 Query 優化器子產品，主要負責對sql語句進行解析（prase）利用内部算法對sql進行解析，生成解析樹（parse tree）及執行計劃（execution plan）。（解析：一條sql語句有N條執行方案，當選擇最優的方案過程，是最消耗資源的）執行sql，交由通路控制子產品執行後續操作，并傳回結果（execute and return)

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

⑤、如果是一些更新統計資訊、檢測、修複和整理類的 query 則會交給表維護子產品去處理。

⑥、複制相關的query 則轉交給複制子產品去進行相應的處理。

⑦、請求狀态的query 則轉交給了狀态收集報告子產品。實際上表變更管理子產品根據所對應的處理請求的不同，是分别由 insert 處理器、delete處理器、update 處理器、create 處理器，以及 alter 處理器這些小子產品來負責不同的 DML和 DDL 的。在各個子產品收到 Query 解析與分發子產品分發過來的請求後，首先會通過通路控制子產品檢查連接配接使用者是否有通路目标表以及目标字段的權限，如果有，就會調用表管理子產品請求相應的表，并擷取對應的鎖。表管理子產品首先會檢視該表是否已經存在于table cache 中，如果已經打開則直接進行鎖相關的處理，如果沒有在 cache 中，則需要再打開表檔案擷取鎖， 然後将打開的表交給表變更管理子產品。當表變更管理子產品“擷取”打開的表之後，就會根據該表的相關 meta 信，判斷表的存儲引擎類型和其他相關資訊。根據表的存儲引擎類型，送出請求給存儲引擎接口子產品，調用對應的存儲引擎實作子產品，進行相應處理。不過，對于表變更管理子產品來說，可見的僅是存儲引擎接口子產品所提供的一系列 “标準”接口，底層存儲引擎實作子產品的具體實作，對于表變更管理子產品來說是透明的。他隻需要調用對應的接口，并指明表類型，接口子產品會根據表類型調用正确的存儲引擎來進行相應的處理。

3、取回結果

當一條 query 或者一個 command 處理完成（成功或者失敗）之後，控制權都會交還給連接配接線程子產品。如果處理成功，則将處理結果（可能是一個 Result set，也可能是成功或者失敗的辨別）通過連接配接線程回報給用戶端。如果處理過程中發生錯誤，也會将相應的錯誤資訊發送給用戶端，然後連接配接線程子產品會進行相應的清理工作，并繼續等待後面的請求，重複上面提到的過程，或者完成用戶端斷開連接配接的請求。如果在上面的過程中，相關子產品使資料庫中的資料發生了變化，而且 MySQL 打開了 binlog 功能，則對應的處理子產品還會調用日志處理子產品将相應的變更語句以更新事件的形式記錄到相關參數指定的二進制日志檔案中。

緩存相關注意事項：

1、cache查找方式

mysql利用内部的hash算法來取得該sql的hash值，然後在cache裡查找是否存在該hash值；假設存在，則将此sql與cache中的進行比較；假設“相同”，就将利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是緩存命中的過程。誠然，如果上面的兩個假設中任有一個不成立，那麼優化器都将進行建立解析樹、生成執行計劃的動作。

2、解析的弊端

建立解析樹、生成執行計劃對于sql的執行來說是開銷昂貴的動作，是以，應當極力避免優化器建立解析樹、生成執行計劃的動作。

3、不會緩存的資料

查詢語句中有一些不确定資料時，例如NOW(), CURRENT_TIME()；一般來說，如果查詢中包含使用者自定義函數、存儲函數、使用者變量、臨時表、mysql庫中系統表、或者任何包含權限的表，一般都不會緩存；

4、緩存會帶來額外開銷

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

5、如何判斷指令率

mysql&gt; SHOW GLOBAL STATUS LIKE'Qcache%';

6、計算命中率

⑴、第一種方式

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

公式：Qcache_hits/(Com_select+Qcache_hits)

⑵、第二種方式

可以通過linux系統指令iostat 1 10或vmstat 1 10

⑶、第三種方式

也應該參考另外一個名額：命中和寫入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，則表明緩存也是有效的。能達到10:1，為比較理想的情況。

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

7、緩存優化使用思路

⑴、批量寫入而非多次單個寫入；

⑵、緩存空間不宜過大，因為大量緩存同時失效時會導緻伺服器假死；

⑶、必要時，使用SQL_CACHE和SQL_N0_CACHE手動控制緩存；

⑷、對寫密集型的應用場景來說，禁用緩存反而能提高性能。

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

本文轉自 jinlinger 51CTO部落格，原文連結：http://blog.51cto.com/essun/1393124，如需轉載請自行聯系原作者