當前位置：
首頁 > 知識 > sql語句的執行順序以及流程（詳細掌握）

sql語句的執行順序以及流程（詳細掌握）

知識 07-14

程序員對sql語句的執行順序的透徹掌握，是避免編程中各種bug和錯誤，歧義語句的不二法則。

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

如果你知道每個關鍵字的意思，作用，如果你還用過的話，那再好不過了。但是，你知道這些語句，它們的執行順序你清楚么？如果你非常清楚，你就沒有必要再浪費時間繼續閱讀了；如果你不清楚，非常好，你應該慶幸你閱讀到了這麼好的一篇文章。

準備工作

  1. 新建一個測試資料庫TestDB；
  2. create database TestDB;
  3. 創建測試表table1和table2；
  4. CREATE TABLE table1
  5. (
  6. customer_id VARCHAR(10) NOT NULL,
  7. city VARCHAR(10) NOT NULL,
  8. PRIMARY KEY(customer_id)
  9. )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  11. CREATE TABLE table2
  12. (
  13. order_id INT NOT NULL auto_increment,
  14. customer_id VARCHAR(10),
  15. PRIMARY KEY(order_id)
  16. )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  17. 插入測試數據；
  18. INSERT INTO table1(customer_id,city) VALUES("163","hangzhou");
  19. INSERT INTO table1(customer_id,city) VALUES("9you","shanghai");
  20. INSERT INTO table1(customer_id,city) VALUES("tx","hangzhou");
  21. INSERT INTO table1(customer_id,city) VALUES("baidu","hangzhou");
  23. INSERT INTO table2(customer_id) VALUES("163");
  24. INSERT INTO table2(customer_id) VALUES("163");
  25. INSERT INTO table2(customer_id) VALUES("9you");
  26. INSERT INTO table2(customer_id) VALUES("9you");
  27. INSERT INTO table2(customer_id) VALUES("9you");
  28. INSERT INTO table2(customer_id) VALUES("tx");
  29. INSERT INTO table2(customer_id) VALUES(NULL);
  30. 準備工作做完以後，table1和table2看起來應該像下面這樣：
  31. mysql> select * from table1;
  32. +-------------+----------+
  33. | customer_id | city |
  34. +-------------+----------+
  35. | 163 | hangzhou |
  36. | 9you | shanghai |
  37. | baidu | hangzhou |
  38. | tx | hangzhou |
  39. +-------------+----------+
  40. 4 rows in set (0.00 sec)
  42. mysql> select * from table2;
  43. +----------+-------------+
  44. | order_id | customer_id |
  45. +----------+-------------+
  46. | 1 | 163 |
  47. | 2 | 163 |
  48. | 3 | 9you |
  49. | 4 | 9you |
  50. | 5 | 9you |
  51. | 6 | tx |
  52. | 7 | NULL |
  53. +----------+-------------+
  54. 7 rows in set (0.00 sec)
  55. 準備SQL邏輯查詢測試語句
  56. SELECT a.customer_id, COUNT(b.order_id) as total_orders
  57. FROM table1 AS a
  58. LEFT JOIN table2 AS b
  59. ON a.customer_id = b.customer_id
  60. WHERE a.city = "hangzhou"
  61. GROUP BY a.customer_id
  62. HAVING count(b.order_id) < 2
  63. ORDER BY total_orders DESC;
  64. 使用上述SQL查詢語句來獲得來自杭州，並且訂單數少於2的客戶。

好吧，這些測試表和測試數據均來自《MySQL技術內幕：SQL編程》，這應該不算抄襲吧，借鑒借鑒啊。

萬事俱備，只欠東風。接下來開始這篇文章最正式的部分吧。

SQL邏輯查詢語句執行順序

還記得上面給出的那一長串的SQL邏輯查詢規則么？那麼，到底哪個先執行，哪個後執行呢？現在，我先給出一個查詢語句的執行順序：

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

上面在每條語句的前面都標明了執行順序號，不要問我怎麼知道這個順序的。我也是讀各種「武林秘籍」才得知的，如果你有功夫，去閱讀一下MySQL的源碼，也會得出這個結果的。

好了，上面我標出了各條查詢規則的執行先後順序，那麼各條查詢語句是如何執行的呢？這就是我今天這篇博文的重點內容。Go on…

執行FROM語句

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

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

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

  2. +-------------+----------+----------+-------------+
  3. | customer_id | city | order_id | customer_id |
  4. +-------------+----------+----------+-------------+
  5. | 163 | hangzhou | 1 | 163 |
  6. | 9you | shanghai | 1 | 163 |
  7. | baidu | hangzhou | 1 | 163 |
  8. | tx | hangzhou | 1 | 163 |
  9. | 163 | hangzhou | 2 | 163 |
  10. | 9you | shanghai | 2 | 163 |
  11. | baidu | hangzhou | 2 | 163 |
  12. | tx | hangzhou | 2 | 163 |
  13. | 163 | hangzhou | 3 | 9you |
  14. | 9you | shanghai | 3 | 9you |
  15. | baidu | hangzhou | 3 | 9you |
  16. | tx | hangzhou | 3 | 9you |
  17. | 163 | hangzhou | 4 | 9you |
  18. | 9you | shanghai | 4 | 9you |
  19. | baidu | hangzhou | 4 | 9you |
  20. | tx | hangzhou | 4 | 9you |
  21. | 163 | hangzhou | 5 | 9you |
  22. | 9you | shanghai | 5 | 9you |
  23. | baidu | hangzhou | 5 | 9you |
  24. | tx | hangzhou | 5 | 9you |
  25. | 163 | hangzhou | 6 | tx |
  26. | 9you | shanghai | 6 | tx |
  27. | baidu | hangzhou | 6 | tx |
  28. | tx | hangzhou | 6 | tx |
  29. | 163 | hangzhou | 7 | NULL |
  30. | 9you | shanghai | 7 | NULL |
  31. | baidu | hangzhou | 7 | NULL |
  32. | tx | hangzhou | 7 | NULL |
  33. +-------------+----------+----------+-------------+

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

執行ON過濾

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

  2. +-------------+----------+----------+-------------+
  3. | customer_id | city | order_id | customer_id |
  4. +-------------+----------+----------+-------------+
  5. | 163 | hangzhou | 1 | 163 |
  6. | 163 | hangzhou | 2 | 163 |
  7. | 9you | shanghai | 3 | 9you |
  8. | 9you | shanghai | 4 | 9you |
  9. | 9you | shanghai | 5 | 9you |
  10. | tx | hangzhou | 6 | tx |
  11. +-------------+----------+----------+-------------+

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

添加外部行

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

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

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

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

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

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

  2. +-------------+----------+----------+-------------+
  3. | customer_id | city | order_id | customer_id |
  4. +-------------+----------+----------+-------------+
  5. | 163 | hangzhou | 1 | 163 |
  6. | 163 | hangzhou | 2 | 163 |
  7. | 9you | shanghai | 3 | 9you |
  8. | 9you | shanghai | 4 | 9you |
  9. | 9you | shanghai | 5 | 9you |
  10. | tx | hangzhou | 6 | tx |
  11. | baidu | hangzhou | NULL | NULL |
  12. | NULL | NULL | 7 | NULL |
  13. +-------------+----------+----------+-------------+

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

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

| baidu | hangzhou | NULL | NULL |

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

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

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

執行WHERE過濾

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

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

但是在使用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，就會得到以下內容：

  2. +-------------+----------+----------+-------------+
  3. | customer_id | city | order_id | customer_id |
  4. +-------------+----------+----------+-------------+
  5. | 163 | hangzhou | 1 | 163 |
  6. | baidu | hangzhou | NULL | NULL |
  7. | tx | hangzhou | 6 | tx |
  8. +-------------+----------+----------+-------------+

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

執行HAVING過濾

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

  2. +-------------+----------+----------+-------------+
  3. | customer_id | city | order_id | customer_id |
  4. +-------------+----------+----------+-------------+
  5. | baidu | hangzhou | NULL | NULL |
  6. | tx | hangzhou | 6 | tx |
  7. +-------------+----------+----------+-------------+

這就是虛擬表VT6。

SELECT列表

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

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

  2. +-------------+--------------+
  3. | customer_id | total_orders |
  4. +-------------+--------------+
  5. | baidu | 0 |
  6. | tx | 1 |
  7. +-------------+--------------+

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

執行DISTINCT子句

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

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

執行ORDER BY子句

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

  2. +-------------+--------------+
  3. | customer_id | total_orders |
  4. +-------------+--------------+
  5. | tx | 1 |
  6. | baidu | 0 |
  7. +-------------+--------------+

可以看到這是對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萬行，然後再進行讀取，而掃描的過程是一個非常低效的過程。所以，對於大數據處理時，是非常有必要在應用層建立一定的緩存機制。

sql語句的執行順序以及流程（詳細掌握）

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！


請您繼續閱讀更多來自 程序員小新人學習 的精彩文章:

※HashMap源碼分析
※使用Python列表方法模擬約瑟夫環問題

TAG:程序員小新人學習 |