MySQL的邏輯查詢語句的執行順序

来源:https://www.cnblogs.com/changxin7/archive/2019/09/20/11557492.html
-Advertisement-
Play Games

一 SELECT語句關鍵字的定義順序 二 SELECT語句關鍵字的執行順序 三 準備表和數據 \1. 新建一個測試資料庫TestDB； 2.創建測試表table1和table2； 3.插入測試數據； 準備工作做完以後，table1和table2看起來應該像下麵這樣 四 準備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告訴我們的。現在有了和兩個表，我們到底從哪個表開始，還是從兩個表進行某種聯繫以後再開始呢？它們之間如何產生聯繫呢？——笛卡爾積

關於什麼是笛卡爾積，請自行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過濾，只有符合的記錄才會輸出到虛擬表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萬行，然後再進行讀取，而掃描的過程是一個非常低效的過程。所以，對於大數據處理時，是非常有必要在應用層建立一定的緩存機制（現在的大數據處理，大都使用緩存）


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 資料庫系統原理（第二章關係資料庫 ）
    所有關係的集合，構成一個關係資料庫。 以關係模型作為數據的邏輯模型，並採用關係作為數據組織方式的一類數 據庫，其資料庫操作建立在關係代數的基礎上。 ...
  • Mybatis之ResultMap的使用詳解
    Mybatis的定義 MyBatis 是一款優秀的持久層框架，它支持定製化 SQL、存儲過程以及高級映射。MyBatis 避免了幾乎所有的 JDBC 代碼和手動設置參數以及獲取結果集。MyBatis 可 以使用簡單的 XML 或註解來配置和映射原生信息，將介面和 Java 的 POJOs(Plain ...
  • 第十六章、初識資料庫
    [TOC] 第十六章、初識資料庫 一、資料庫 二、資料庫的組成 三、資料庫的分類 四、卸載資料庫 五、安裝資料庫 六、連接資料庫 七、用戶信息查看 八、資料庫的基本操作 九、表的基本操作 十、記錄的基本操作 1）查看某個資料庫中的某個表的所有記錄，如果在對應資料庫中，可以直接查找表 mysql : ...
  • 〈三〉ElasticSearch的認識：搜索、過濾、排序
    ElasticSearch第三篇，關於搜索、過濾、排序簡單講解。 ...
  • CentOS 7.6安裝MySQL 5.7GA版
    centos7 內部集成了mariadb，而安裝mysql的話會和mariadb的文件衝突，所以需要先卸載掉mariadb。 ...
  • win中Oracle簡易客戶端和plsql的配置
    連接資料庫有2種方式：在本機安裝Oracle資料庫或者是安裝一個oracle簡易客戶端當然，簡易客戶端跟oracle資料庫比較少了一些功能連接方式：1）簡易連接sqlplus scott/[email protected]:1521/study註意最後的study是服務名，別搞錯了這種ora-12514... ...
  • 鬆軟科技課堂:sqlserver--數據類型
    SQL Server 數據類型(文章來源：鬆軟科技www.sysoft.net.cn) Character 字元串： Unicode 字元串： Binary 類型： Number 類型： 固定精度和比例的數字。允許從 -10^38 +1 到 10^38 -1 之間的數字。 p 參數指示可以存儲的最大 ...
  • SQL DROP INDEX 語句
    SQL DROP INDEX 語句 我們可以使用 DROP INDEX 命令刪除表格中的索引。 用於 Microsoft SQLJet (以及 Microsoft Access) 的語法: 用於 MS SQL Server 的語法: 用於 IBM DB2 和 Oracle 語法: 用於 MySQL ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類