mysql查詢流程

標籤：set   二進位日誌   join   日誌功能   shadow   清理   詞條   strong   insert   

轉自：http://www.cnblogs.com/annsshadow/p/5037667.html

前言：　　一直是想知道一條SQL語句是怎麼被執行的，它執行的順序是怎樣的，然後查看總結各方資料，就有了下面這一篇博文了。　　本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。  一、MySQL架構總覽：　　架構最好看圖，再配上必要的解說文字。　　根據參考書籍中一圖為原本，再在其上添加上了自己的理解。 　　從中我們可以看到，整個架構分為兩層，上層是MySQLD的被稱為的‘SQL Layer’，下層是各種各樣對上提供介面的儲存引擎，被稱為‘Storage Engine Layer’。其它各個模組和組件，從名字上就可以簡單瞭解到它們的作用，這裡就不再累述了。  二、查詢執行流程　　下面再向前走一些，容我根據自己的認識說一下查詢執行的流程是怎樣的： 1.串連　　1.1用戶端發起一條Query請求，監聽用戶端的‘串連管理模組’接收請求　　1.2將請求轉寄到‘串連進/線程模組’　　1.3調用‘使用者模組’來進行授權檢查　　1.4通過檢查後，‘串連進/線程模組’從‘線程串連池’中取出閒置被緩衝的連接線程和用戶端請求對接，如果失敗則建立一個新的串連請求  2.處理　　2.1先查詢快取，檢查Query語句是否完全符合，接著再檢查是否具有許可權，都成功則直接取資料返回　　2.2上一步有失敗則轉交給‘命令解析器’，經過詞法分析，文法分析後產生解析樹　　2.3接下來是預先處理階段，處理解析器無法解決的語義，檢查許可權等，產生新的解析樹　　2.4再轉交給對應的模組處理　　2.5如果是SELECT查詢還會經由‘查詢最佳化工具’做大量的最佳化，產生執行計畫　　2.6模組收到請求後，通過‘存取控制模組’檢查所串連的使用者是否有訪問目標表和目標欄位的許可權　　2.7有則調用‘表管理模組’，先是查看table cache中是否存在，有則直接對應的表和擷取鎖，否則重新開啟表檔案　　2.8根據表的meta資料，擷取表的儲存引擎類型等資訊，通過介面調用對應的儲存引擎處理　　2.9上述過程中產生資料變化的時候，若開啟日誌功能，則會記錄到相應二進位記錄檔中  3.結果　　3.1Query請求完成後，將結果集返回給‘串連進/線程模組’　　3.2返回的也可以是相應的狀態標識，如成功或失敗等　　3.3‘串連進/線程模組’進行後續的清理工作，並繼續等待請求或斷開與用戶端的串連   一圖小總結    三、SQL解析順序　　接下來再走一步，讓我們看看一條SQL語句的前世今生。　　首先看一下樣本語句

SELECT DISTINCT    < select_list >FROM    < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE    < where_condition >GROUP BY    < group_by_list >HAVING    < having_condition >ORDER BY    < order_by_condition >LIMIT < limit_number >

　　然而它的執行順序是這樣的

 1 FROM <left_table> 2 ON <join_condition> 3 <join_type> JOIN <right_table> 4 WHERE <where_condition> 5 GROUP BY <group_by_list> 6 HAVING <having_condition> 7 SELECT  8 DISTINCT <select_list> 9 ORDER BY <order_by_condition>10 LIMIT <limit_number>

　　雖然自己沒想到是這樣的，不過一看還是很自然和諧的，從哪裡擷取，不斷的過濾條件，要選擇一樣或不一樣的，排好序，那才知道要取前幾條呢。既然如此了，那就讓我們一步步來看看其中的細節吧。  準備工作　　1.建立測試資料庫

create database testQuery

　　2.建立測試表

CREATE TABLE table1(    uid VARCHAR(10) NOT NULL,    name VARCHAR(10) NOT NULL,    PRIMARY KEY(uid))ENGINE=INNODB DEFAULT CHARSET=UTF8;CREATE TABLE table2(    oid INT NOT NULL auto_increment,    uid VARCHAR(10),    PRIMARY KEY(oid))ENGINE=INNODB DEFAULT CHARSET=UTF8;

　　3.插入資料

INSERT INTO table1(uid,name) VALUES(‘aaa‘,‘mike‘),(‘bbb‘,‘jack‘),(‘ccc‘,‘mike‘),(‘ddd‘,‘mike‘);INSERT INTO table2(uid) VALUES(‘aaa‘),(‘aaa‘),(‘bbb‘),(‘bbb‘),(‘bbb‘),(‘ccc‘),(NULL);

　　4.最後想要的結果

SELECT    a.uid,    count(b.oid) AS totalFROM    table1 AS aLEFT JOIN table2 AS b ON a.uid = b.uidWHERE    a. NAME = ‘mike‘GROUP BY    a.uidHAVING    count(b.oid) < 2ORDER BY    total DESCLIMIT 1;

 

！現在開始SQL解析之旅吧！  1. FROM當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之後會產生一個虛擬表VT1。(1-J1)笛卡爾積計算兩個相關聯表的笛卡爾積(CROSS JOIN) ，產生虛擬表VT1-J1。

mysql> select * from table1,table2;+-----+------+-----+------+| uid | name | oid | uid  |+-----+------+-----+------+| aaa | mike |   1 | aaa  || bbb | jack |   1 | aaa  || ccc | mike |   1 | aaa  || ddd | mike |   1 | aaa  || aaa | mike |   2 | aaa  || bbb | jack |   2 | aaa  || ccc | mike |   2 | aaa  || ddd | mike |   2 | aaa  || aaa | mike |   3 | bbb  || bbb | jack |   3 | bbb  || ccc | mike |   3 | bbb  || ddd | mike |   3 | bbb  || aaa | mike |   4 | bbb  || bbb | jack |   4 | bbb  || ccc | mike |   4 | bbb  || ddd | mike |   4 | bbb  || aaa | mike |   5 | bbb  || bbb | jack |   5 | bbb  || ccc | mike |   5 | bbb  || ddd | mike |   5 | bbb  || aaa | mike |   6 | ccc  || bbb | jack |   6 | ccc  || ccc | mike |   6 | ccc  || ddd | mike |   6 | ccc  || aaa | mike |   7 | NULL || bbb | jack |   7 | NULL || ccc | mike |   7 | NULL || ddd | mike |   7 | NULL |+-----+------+-----+------+28 rows in set (0.00 sec)

 

(1-J2)ON過濾基於虛擬表VT1-J1這一個虛擬表進行過濾，過濾出所有滿足ON 謂詞條件的列，產生虛擬表VT1-J2。注意：這裡因為文法限制，使用了‘WHERE‘代替，從中讀者也可以感受到兩者之間微妙的關係；

mysql> SELECT    -> *    -> FROM    -> table1,    -> table2    -> WHERE    -> table1.uid = table2.uid    -> ;+-----+------+-----+------+| uid | name | oid | uid  |+-----+------+-----+------+| aaa | mike |   1 | aaa  || aaa | mike |   2 | aaa  || bbb | jack |   3 | bbb  || bbb | jack |   4 | bbb  || bbb | jack |   5 | bbb  || ccc | mike |   6 | ccc  |+-----+------+-----+------+6 rows in set (0.00 sec)

 

(1-J3)添加外部列如果使用了外串連(LEFT,RIGHT,FULL)，主表（保留表）中的不符合ON條件的列也會被加入到VT1-J2中，作為外部行，產生虛擬表VT1-J3。

mysql> SELECT    -> *    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid;+-----+------+------+------+| uid | name | oid  | uid  |+-----+------+------+------+| aaa | mike |    1 | aaa  || aaa | mike |    2 | aaa  || bbb | jack |    3 | bbb  || bbb | jack |    4 | bbb  || bbb | jack |    5 | bbb  || ccc | mike |    6 | ccc  || ddd | mike | NULL | NULL |+-----+------+------+------+7 rows in set (0.00 sec)

 

下面從網上找到一張很形象的關於‘SQL JOINS‘的解釋圖，如若侵犯了你的權益，請勞煩告知刪除，謝謝。   2. WHERE對VT1過程中產生的暫存資料表進行過濾，滿足WHERE子句的列被插入到VT2表中。注意：此時因為分組，不能使用彙總運算；也不能使用SELECT中建立的別名；與ON的區別：如果有外部列，ON針對過濾的是關聯表，主表（保留表）會返回所有的列；如果沒有添加外部列，兩者的效果是一樣的；應用：對主表的過濾應該放在WHERE；對於關聯表，先條件查詢後串連則用ON，先串連後條件查詢則用WHERE；

mysql> SELECT    -> *    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘;+-----+------+------+------+| uid | name | oid  | uid  |+-----+------+------+------+| aaa | mike |    1 | aaa  || aaa | mike |    2 | aaa  || ccc | mike |    6 | ccc  || ddd | mike | NULL | NULL |+-----+------+------+------+4 rows in set (0.00 sec)

 

3. GROUP BY這個子句會把VT2中產生的表按照GROUP BY中的列進行分組。產生VT3表。注意：其後處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對於沒有出現的，得用彙總函式；原因：GROUP BY改變了對錶的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少；我的理解是：根據分組欄位，將具有相同分組欄位的記錄歸併成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組欄位裡面的欄位可能會有多個值，多個值是無法放進一條記錄的，所以必須通過彙總函式將這些具有多值的列轉換成單值；

mysql> SELECT    -> *    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘    -> GROUP BY    -> a.uid;+-----+------+------+------+| uid | name | oid  | uid  |+-----+------+------+------+| aaa | mike |    1 | aaa  || ccc | mike |    6 | ccc  || ddd | mike | NULL | NULL |+-----+------+------+------+3 rows in set (0.00 sec)

 

4. HAVING這個子句對VT3表中的不同的組進行過濾，只作用於分組後的資料，滿足HAVING條件的子句被加入到VT4表中。

mysql> SELECT    -> *    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘    -> GROUP BY    -> a.uid    -> HAVING    -> count(b.oid) < 2;+-----+------+------+------+| uid | name | oid  | uid  |+-----+------+------+------+| ccc | mike |    6 | ccc  || ddd | mike | NULL | NULL |+-----+------+------+------+2 rows in set (0.00 sec)

 

5. SELECT這個子句對SELECT子句中的元素進行處理，產生VT5表。(5-J1)計算運算式 計算SELECT 子句中的運算式，產生VT5-J1(5-J2)DISTINCT尋找VT5-1中的重複列，並刪掉，產生VT5-J2如果在查詢中指定了DISTINCT子句，則會建立一張記憶體暫存資料表（如果記憶體放不下，就需要存放在硬碟了）。這張暫存資料表的表結構和上一步產生的虛擬表VT5是一樣的，不同的是對進行DISTINCT操作的列增加了一個唯一索引，以此來除重複資料。

mysql> SELECT    -> a.uid,    -> count(b.oid) AS total    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘    -> GROUP BY    -> a.uid    -> HAVING    -> count(b.oid) < 2;+-----+-------+| uid | total |+-----+-------+| ccc |     1 || ddd |     0 |+-----+-------+2 rows in set (0.00 sec)

 

6.ORDER BY從VT5-J2中的表中，根據ORDER BY 子句的條件對結果進行排序，產生VT6表。注意：唯一可使用SELECT中別名的地方；

mysql> SELECT    -> a.uid,    -> count(b.oid) AS total    -> FROM    -> table1 AS a    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘    -> GROUP BY    -> a.uid    -> HAVING    -> count(b.oid) < 2    -> ORDER BY    -> total DESC;+-----+-------+| uid | total |+-----+-------+| ccc |     1 || ddd |     0 |+-----+-------+2 rows in set (0.00 sec)

 

7.LIMITLIMIT子句從上一步得到的VT6虛擬表中選出從指定位置開始的指定行資料。注意：offset和rows的正負帶來的影響；當位移量很大時效率是很低的，可以這麼做：採用子查詢的方式最佳化，在子查詢裡先從索引擷取到最大id，然後倒序排，再取N行結果集採用INNER JOIN最佳化，JOIN子句裡也優先從索引擷取ID列表，然後直接關聯查詢獲得最終結果

mysql> SELECT    -> a.uid,    -> count(b.oid) AS total    -> FROM    -> table1 AS a    -> LEFT JOIN table2 AS b ON a.uid = b.uid    -> WHERE    -> a. NAME = ‘mike‘    -> GROUP BY    -> a.uid    -> HAVING    -> count(b.oid) < 2    -> ORDER BY    -> total DESC    -> LIMIT 1;+-----+-------+| uid | total |+-----+-------+| ccc |     1 |+-----+-------+1 row in set (0.00 sec)

 

至此SQL的解析之旅就結束了，總結一下：  參考書籍：《MySQL效能調優與架構實踐》《MySQL技術內幕：SQL編程》 

mysql查詢流程