步步深入:MySQL架构总览-gt;查询执行流程-gt;SQL解析顺序
生活随笔
收集整理的這篇文章主要介紹了
步步深入:MySQL架构总览-gt;查询执行流程-gt;SQL解析顺序
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
轉載自?https://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.最后想要的結果SELECTa.uid,count(b.oid) AS total FROMtable1 AS a LEFT JOIN table2 AS b ON a.uid = b.uid WHEREa. NAME = 'mike' GROUP BYa.uid HAVINGcount(b.oid) < 2 ORDER BYtotal DESC LIMIT 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編程》尾聲: 嗯,到這里這一次的深入了解之旅就差不多真的結束了,雖然也不是很深入,只是一些東西將其東拼西湊在一起而已,參考了一些以前看過的書籍,大師之筆果然不一樣。而且在這過程中也是get到了蠻多東西的,最重要的是更進一步意識到,計算機軟件世界的宏大呀~ 另由于本人才疏學淺,其中難免存在紕漏錯誤之處,若發現勞煩告知修改,感謝~ 如需轉載,請保留AnnsShadoW和本文地址http://www.cnblogs.com/annsshadow/p/5037667.html總結
以上是生活随笔為你收集整理的步步深入:MySQL架构总览-gt;查询执行流程-gt;SQL解析顺序的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 京东面试题:Java中 ++i 的操作是
- 下一篇: MyBatis的association示