mysql五補充部分:SQL邏輯查詢語句執行順序

標籤：alt   from   cond   測試資料   strong   允許   mysql   第一個   left join   

閱讀目錄

• 一 SELECT語句關鍵字的定義順序
• 二 SELECT語句關鍵字的執行順序
• 三 準備表和資料
• 四 準備SQL邏輯查詢測試語句
• 五 執行順序分析

一 SELECT語句關鍵字的定義順序

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>

二 SELECT語句關鍵字的執行順序

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

三 準備表和資料

1. 建立一個測試資料庫TestDB；

create database TestDB;

2.建立測試表table1和table2；

CREATE TABLE table1 (     customer_id VARCHAR(10) NOT NULL,     city VARCHAR(10) NOT NULL,     PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE table2 (     order_id INT NOT NULL auto_increment,     customer_id VARCHAR(10),     PRIMARY KEY(order_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8;

3.插入測試資料；

 INSERT INTO table1(customer_id,city) VALUES(‘163‘,‘hangzhou‘); INSERT INTO table1(customer_id,city) VALUES(‘9you‘,‘shanghai‘); INSERT INTO table1(customer_id,city) VALUES(‘tx‘,‘hangzhou‘); INSERT INTO table1(customer_id,city) VALUES(‘baidu‘,‘hangzhou‘); INSERT INTO table2(customer_id) VALUES(‘163‘); INSERT INTO table2(customer_id) VALUES(‘163‘); INSERT INTO table2(customer_id) VALUES(‘9you‘); INSERT INTO table2(customer_id) VALUES(‘9you‘); INSERT INTO table2(customer_id) VALUES(‘9you‘); INSERT INTO table2(customer_id) VALUES(‘tx‘); INSERT INTO table2(customer_id) VALUES(NULL);

準備工作做完以後，table1和table2看起來應該像下面這樣：

mysql> select * from table1; +-------------+----------+ | customer_id | city     | +-------------+----------+ | 163         | hangzhou | | 9you        | shanghai | | baidu       | hangzhou | | tx          | hangzhou | +-------------+----------+ 4 rows in set (0.00 sec) mysql> select * from table2; +----------+-------------+ | order_id | customer_id | +----------+-------------+ |        1 | 163         | |        2 | 163         | |        3 | 9you        | |        4 | 9you        | |        5 | 9you        | |        6 | tx          | |        7 | NULL        | +----------+-------------+ 7 rows in set (0.00 sec)

四 準備SQL邏輯查詢測試語句

#查詢來自杭州，並且訂單數少於2的客戶。 SELECT a.customer_id, COUNT(b.order_id) as total_orders FROM table1 AS a LEFT JOIN table2 AS b ON a.customer_id = b.customer_id WHERE a.city = ‘hangzhou‘ GROUP BY a.customer_id HAVING count(b.order_id) < 2 ORDER BY total_orders DESC;

五 執行順序分析

在這些SQL語句的執行過程中，都會產生一個虛擬表，用來儲存SQL語句的執行結果（這是重點），我現在就來跟蹤這個虛擬表的變化，得到最終的查詢結果的過程，來分析整個SQL邏輯查詢的執行順序和過程。

執行FROM語句

第一步，執行FROM語句。我們首先需要知道最開始從哪個表開始的，這就是FROM告訴我們的。現在有了<left_table>和<right_table>兩個表，我們到底從哪個表開始，還是從兩個表進行某種聯絡以後再開始呢？它們之間如何產生聯絡呢？——笛卡爾積

關於什麼是笛卡爾積，請自行Google補腦。經過FROM語句對兩個表執行笛卡爾積，會得到一個虛擬表，暫且叫VT1（vitual table 1），內容如下：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 9you        | shanghai |        1 | 163         || baidu       | hangzhou |        1 | 163         || tx          | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        2 | 163         || baidu       | hangzhou |        2 | 163         || tx          | hangzhou |        2 | 163         || 163         | hangzhou |        3 | 9you        || 9you        | shanghai |        3 | 9you        || baidu       | hangzhou |        3 | 9you        || tx          | hangzhou |        3 | 9you        || 163         | hangzhou |        4 | 9you        || 9you        | shanghai |        4 | 9you        || baidu       | hangzhou |        4 | 9you        || tx          | hangzhou |        4 | 9you        || 163         | hangzhou |        5 | 9you        || 9you        | shanghai |        5 | 9you        || baidu       | hangzhou |        5 | 9you        || tx          | hangzhou |        5 | 9you        || 163         | hangzhou |        6 | tx          || 9you        | shanghai |        6 | tx          || baidu       | hangzhou |        6 | tx          || tx          | hangzhou |        6 | tx          || 163         | hangzhou |        7 | NULL        || 9you        | shanghai |        7 | NULL        || baidu       | hangzhou |        7 | NULL        || tx          | hangzhou |        7 | NULL        |+-------------+----------+----------+-------------+

總共有28（table1的記錄條數 * table2的記錄條數）條記錄。這就是VT1的結果，接下來的操作就在VT1的基礎上進行。

執行ON過濾

執行完笛卡爾積以後，接著就進行ON a.customer_id = b.customer_id條件過濾，根據ON中指定的條件，去掉那些不合格資料，得到VT2表，內容如下：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        3 | 9you        || 9you        | shanghai |        4 | 9you        || 9you        | shanghai |        5 | 9you        || tx          | hangzhou |        6 | tx          |+-------------+----------+----------+-------------+

VT2就是經過ON條件式篩選以後得到的有用資料，而接下來的操作將在VT2的基礎上繼續進行。

添加外部行

這一步只有在連線類型為OUTER JOIN時才發生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多數的時候，我們都是會省略掉OUTER關鍵字的，但OUTER表示的就是外部行的概念。

LEFT OUTER JOIN把左表記為保留表，得到的結果為：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        3 | 9you        || 9you        | shanghai |        4 | 9you        || 9you        | shanghai |        5 | 9you        || tx          | hangzhou |        6 | tx          || baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

RIGHT OUTER JOIN把右表記為保留表，得到的結果為：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        3 | 9you        || 9you        | shanghai |        4 | 9you        || 9you        | shanghai |        5 | 9you        || tx          | hangzhou |        6 | tx          || NULL        | NULL     |        7 | NULL        |+-------------+----------+----------+-------------+

FULL OUTER JOIN把左右表都作為保留表，得到的結果為：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        3 | 9you        || 9you        | shanghai |        4 | 9you        || 9you        | shanghai |        5 | 9you        || tx          | hangzhou |        6 | tx          || baidu       | hangzhou |     NULL | NULL        || NULL        | NULL     |        7 | NULL        |+-------------+----------+----------+-------------+

添加外部行的工作就是在VT2表的基礎上添加保留表中被過濾條件過濾掉的資料，非保留表中的資料被賦予NULL值，最後產生虛擬表VT3。

由於我在準備的測試SQL查詢邏輯語句中使用的是LEFT JOIN，過濾掉了以下這條資料：

| baidu       | hangzhou |     NULL | NULL        |

現在就把這條資料添加到VT2表中，得到的VT3表如下：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || 9you        | shanghai |        3 | 9you        || 9you        | shanghai |        4 | 9you        || 9you        | shanghai |        5 | 9you        || tx          | hangzhou |        6 | tx          || baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

接下來的操作都會在該VT3表上進行。

執行WHERE過濾

對添加外部行得到的VT3進行WHERE過濾，只有符合<where_condition>的記錄才會輸出到虛擬表VT4中。當我們執行WHERE a.city = ‘hangzhou‘的時候，就會得到以下內容，並存在虛擬表VT4中：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || 163         | hangzhou |        2 | 163         || tx          | hangzhou |        6 | tx          || baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

但是在使用WHERE子句時，需要注意以下兩點：

1. 由於資料還沒有分組，因此現在還不能在WHERE過濾器中使用where_condition=MIN(col)這類對分組統計的過濾；
2. 由於還沒有進行列的選取操作，因此在SELECT中使用列的別名也是不被允許的，如：SELECT city as c FROM t WHERE c=‘shanghai‘;是不允許出現的。

執行GROUP BY分組

GROU BY子句主要是對使用WHERE子句得到的虛擬表進行分組操作。我們執行測試語句中的GROUP BY a.customer_id，就會得到以下內容(預設只顯示組內第一條)：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163         || baidu       | hangzhou |     NULL | NULL        || tx          | hangzhou |        6 | tx          |+-------------+----------+----------+-------------+

得到的內容會存入虛擬表VT5中，此時，我們就得到了一個VT5虛擬表，接下來的操作都會在該表上完成。

執行HAVING過濾

HAVING子句主要和GROUP BY子句配合使用，對分組得到的VT5虛擬表進行條件過濾。當我執行測試語句中的HAVING count(b.order_id) < 2時，將得到以下內容：

+-------------+----------+----------+-------------+| customer_id | city     | order_id | customer_id |+-------------+----------+----------+-------------+| baidu       | hangzhou |     NULL | NULL        || tx          | hangzhou |        6 | tx          |+-------------+----------+----------+-------------+

這就是虛擬表VT6。

SELECT列表

現在才會執行到SELECT子句，不要以為SELECT子句被寫在第一行，就是第一個被執行的。

我們執行測試語句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders，從虛擬表VT6中選擇出我們需要的內容。我們將得到以下內容：

+-------------+--------------+| customer_id | total_orders |+-------------+--------------+| baidu       |            0 || tx          |            1 |+-------------+--------------+

還沒有完，這隻是虛擬表VT7。

執行DISTINCT子句

如果在查詢中指定了DISTINCT子句，則會建立一張記憶體暫存資料表（如果記憶體放不下，就需要存放在硬碟了）。這張暫存資料表的表結構和上一步產生的虛擬表VT7是一樣的，不同的是對進行DISTINCT操作的列增加了一個唯一索引，以此來除重複資料。

由於我的測試SQL語句中並沒有使用DISTINCT，所以，在該查詢中，這一步不會產生一個虛擬表。

執行ORDER BY子句

對虛擬表中的內容按照指定的列進行排序，然後返回一個新的虛擬表，我們執行測試SQL語句中的ORDER BY total_orders DESC，就會得到以下內容：

+-------------+--------------+| customer_id | total_orders |+-------------+--------------+| tx          |            1 || baidu       |            0 |+-------------+--------------+

可以看到這是對total_orders列進行降序排列的。上述結果會儲存在VT8中。

執行LIMIT子句

LIMIT子句從上一步得到的VT8虛擬表中選出從指定位置開始的指定行資料。對於沒有應用ORDER BY的LIMIT子句，得到的結果同樣是無序的，所以，很多時候，我們都會看到LIMIT子句會和ORDER BY子句一起使用。

MySQL資料庫的LIMIT支援如下形式的選擇：

LIMIT n, m

表示從第n條記錄開始選擇m條記錄。而很多開發人員喜歡使用該語句來解決分頁問題。對於小資料，使用LIMIT子句沒有任何問題，當資料量非常大的時候，使用LIMIT n, m是非常低效的。因為LIMIT的機制是每次都是從頭開始掃描，如果需要從第60萬行開始，讀取3條資料，就需要先掃描定位到60萬行，然後再進行讀取，而掃描的過程是一個非常低效的過程。所以，對於大資料處理時，是非常有必要在應用程式層建立一定的緩衝機制（現在的大資料處理，大都使用緩衝）

 

mysql五補充部分:SQL邏輯查詢語句執行順序