【MySQL】DQL 指令的執行順序與效能優化

回覆文章分享
【MySQL】DQL 指令的執行順序與效能優化

本篇重點

  • 了解 DQL 指令的執行順序，從 FROM 到 LIMIT/OFFSET
  • 每個步驟會產生一張虛擬表供下一步驟使用，透過執行順序，可以減少不必要的資料處理，提升查詢效能
  • 各個 DQL 指令的使用重點

在 MySQL 查詢語言（DQL，Data Query Language）中，每條 SELECT 查詢語句包含多個不同的指令。了解執行順序，能幫助理解查詢如何處理，並且優化 SQL 查詢，減少不必要的篩選與計算，提高效能。

DQL 指令的執行順序

雖然撰寫 SQL 語句時通常按照 SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT … 的順序，但實際上 MySQL 的執行順序是不同的，主要執行順序如下：

FROM → JOIN … ON → WHERE → GROUP BY → AGG_FUNC → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

每執行一個步驟都會產生一張虛擬表給下一個步驟使用，但實際上虛擬表是看不到的，只能看到最終的搜尋結果。

範例資料

mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 創建資料表
CREATE TABLE users (
uid INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age VARCHAR(50),
team VARCHAR(50),
email VARCHAR(100)
);

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
uid INT,
commodity_id INT,
price INT
);

-- 新增資料
INSERT INTO users (name, age, team, email) VALUES ('John', 17, 'a', 'john@example.com');
INSERT INTO users (name, age, team, email) VALUES ('tom', 22, 'b', 'tom@example.com');
INSERT INTO users (name, age, team, email) VALUES ('cindy', 35, 'a', 'cindy@example.com');
INSERT INTO users (name, age, team, email) VALUES ('tom', 30, 'a', 'tom2@example.com');

INSERT INTO orders (uid, commodity_id, price) VALUES (2, 5, 50);
INSERT INTO orders (uid, commodity_id, price) VALUES (2, 8, 30);
INSERT INTO orders (uid, commodity_id, price) VALUES (3, 1, 100);
INSERT INTO orders (uid, commodity_id, price) VALUES (4, 16, 50);

(1) FROM

mysql
1
SELECT [欄位名稱] FROM [表格名稱];
  • 來源資料表的讀取

範例：

mysql
1
2
3
SELECT * FROM users;

SELECT * FROM orders;

(2) JOIN … ON

mysql
1
2
SELECT [欄位名稱] FROM [表格1]
JOIN [表格2] ON [表格1].[欄位] = [表格2].[欄位];
  • 當有 JOIN 操作時，MySQL 會先依據 ON 條件來篩選需要 JOIN 的資料，這有助於減少不必要的數據傳輸，提升查詢效能
  • JOIN … ON 比 WHERE 更早執行，因此 ON 條件能過濾掉不必要的資料，提高效能

範例：

mysql
1
2
3
SELECT u.uid, u.name, o.order_id, o.commodity_id, o.price
FROM users u
JOIN orders o ON u.uid = o.uid AND o.price >= 50; -- 在 JOIN 的時候先做篩選

(3) WHERE

mysql
1
SELECT [欄位名稱] FROM [表格名稱] WHERE [篩選條件];
  • 在 FROM 抓取完表之後，會根據 WHERE 條件來篩選符合條件的資料
  • WHERE 會在 GROUP BY 之前執行，因此 WHERE 不能對聚合函數 (SUM、COUNT) 進行篩選

範例：

mysql
1
2
3
4
5
6
7
8
9
10
SELECT u.uid, u.name, o.order_id, o.commodity_id, o.price
FROM users u
JOIN orders o ON u.uid = o.uid
WHERE u.uid = 2;

-- 因為 JOIN … ON 比 WHERE 更早執行，所以執行 WHERE u.uid = 2 的時候，已經只剩 price >= 50 的資料
SELECT u.uid, u.name, o.order_id, o.commodity_id, o.price
FROM users u
JOIN orders o ON u.uid = o.uid AND o.price >= 50
WHERE u.uid = 2;

(4) GROUP BY

mysql
1
2
SELECT [分組欄位], [聚合函數] FROM [表格名稱]
GROUP BY [分組欄位];
  • 將符合 WHERE 條件的資料分組
  • GROUP BY 後只能 SELECT 分組的欄位或聚合函數 (如 SUM、COUNT、AVG)，如果 SELECT 分組以外的欄位會是同一分組中任意一行的值，因此可能會與預期結果不符

範例：

mysql
1
2
3
4
5
6
7
8
9
SELECT team
FROM users
GROUP BY team;

-- 因為 WHERE 比 GROUP BY 更早執行，所以執行 GROUP BY team 的時候，已經只剩 age >= 30 的資料
SELECT team
FROM users
WHERE age >= 30
GROUP BY team;

在查詢每個群組中的最新或特定條件的資料時，可以使用 GROUP BY 搭配聚合函數來篩選出所需的關鍵值（例如最新的 ID 或日期），再透過 JOIN 將篩選出的關鍵值與原表進行關聯，確保獲取完整的詳細資訊。
這種方式適用於各種情境，例如查詢每位使用者的最新留言、每個產品的最高價格紀錄、每個客戶的最近一次購買紀錄等。

範例：

查詢每個 uid 的最新訂單可以使用 GROUP BY 搭配 MAX()

  • 先找出 orders 表中每個 uid 最新的 order_id（假設 order_id 越大代表越新）
  • 然後 JOIN 回 orders 表，獲取完整的訂單資訊
mysql
1
2
3
4
5
6
7
8
SELECT o.*
FROM orders o
JOIN (
    SELECT uid, MAX(order_id) AS latest_order
    FROM orders
    GROUP BY uid
) latest_orders
ON o.uid = latest_orders.uid AND o.order_id = latest_orders.latest_order;

(5) AGG_FUNC

mysql
1
2
SELECT [分組欄位], [聚合函數]([欄位]) FROM [表格名稱]
GROUP BY [分組欄位];
  • 聚合函數計算（如 SUM、AVG、COUNT）
  • GROUP BY 分組後，才能使用聚合函數
  • 可以使用 AS 將欄位重新命名

範例：

mysql
1
2
3
4
5
6
7
8
9
SELECT team, COUNT(*) AS team_count, AVG(age) AS age_avg
FROM users
GROUP BY team;

-- 因為 WHERE 比 GROUP BY 更早執行，所以執行 GROUP BY team 的時候，已經只剩 age >= 30 的資料
SELECT team, COUNT(*) AS team_count, AVG(age) AS age_avg
FROM users
WHERE age >= 30
GROUP BY team;

(6) HAVING

mysql
1
2
3
SELECT [分組欄位], [聚合函數]([欄位]) FROM [表格名稱]
GROUP BY [分組欄位]
HAVING [條件];
  • 針對聚合函數的結果篩選

範例：

mysql
1
2
3
4
5
-- 針對聚合函數的結果篩選 team_count > 1 的資料
SELECT team, COUNT(*) AS team_count
FROM users
GROUP BY team
HAVING team_count > 1;

(7) SELECT

mysql
1
SELECT [欄位名稱] FROM [表格名稱];
  • 選擇要顯示的欄位（* 為選擇所有欄位）
  • 所有條件已經套用，查詢結果已經確定

範例：

mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * FROM users;
SELECT * FROM orders;

SELECT u.uid, u.name, o.order_id, o.commodity_id, o.price
FROM users u
JOIN orders o ON u.uid = o.uid AND o.price >= 50;

SELECT u.uid, u.name, o.order_id, o.commodity_id, o.price
FROM users u
JOIN orders o ON u.uid = o.uid
WHERE u.uid = 2;

SELECT team, COUNT(*) AS team_count, AVG(age) AS age_avg
FROM users
GROUP BY team;

(8) DISTINCT

mysql
1
SELECT DISTINCT [欄位名稱] FROM [表格名稱];
  • 和 SELECT 搭配使用，在 SELECT 完成後過濾掉重複的資料
  • DISTINCT 是對整個查詢結果去除重複資料
  • DISTINCT 用於多個欄位是根據這些欄位的組合值來判斷是否重複，不是單獨判斷某一欄位

範例：

mysql
1
2
3
4
5
6
SELECT DISTINCT uid FROM orders;

SELECT DISTINCT name FROM users;

-- 根據多欄位去除重複項
SELECT DISTINCT name, age, email FROM users;

(9) ORDER BY

mysql
1
2
SELECT [欄位名稱] FROM [表格名稱]
ORDER BY [欄位名稱] [ASC|DESC];
  • 在 SELECT 完成後，結果會按照 ORDER BY 進行排序
  • 未指定排序，預設為 ASC 升冪，順序由小排到大，使用 DESC 為降冪，順序由大排到小
  • 排序可能會影響效能，特別是在大量數據時，要確保排序欄位有索引

範例：

mysql
1
2
3
4
5
SELECT * FROM orders ORDER BY price;

SELECT * FROM users ORDER BY team ASC;

SELECT * FROM users ORDER BY age DESC;

(10) LIMIT / OFFSET

mysql
1
2
SELECT [欄位名稱] FROM [表格名稱]
LIMIT [筆數] OFFSET [跳過筆數];
  • 取出特定範圍的資料
  • LIMIT 用於限制返回的筆數，如果 LIMIT 使用兩個參數，第一個參數為資料跳過的筆數，第二個參數為跳過後選取的筆數
  • OFFSET 用於跳過前幾筆資料，不能單獨使用，必須與 LIMIT 搭配使用

健忘筆記

LIMIT 使用兩個參數，例如 LIMIT 20, 10，解讀為跳過 20 筆記錄後，再取 10 筆記錄

範例：

mysql
1
2
3
4
5
6
SELECT * FROM users LIMIT 3;

-- 以下兩個查詢的結果相同
SELECT * FROM users LIMIT 1, 2;

SELECT * FROM users LIMIT 2 OFFSET 1;

提升查詢效能的方式

建立適當的索引

  • WHERE、JOIN、ORDER BY 相關的欄位應建立索引，減少資料庫掃描時間。

例如：

mysql
1
2
-- 對 users 表的 age 欄位建立索引
CREATE INDEX idx_users_age ON users(age);

先篩選再分組

  • WHERE 會在 GROUP BY 之前執行，因此應確保 WHERE 先過濾掉不必要的數據，減少 GROUP BY 的負擔。

避免 SELECT *，只選取需要的欄位

  • 只選擇必要的欄位可以減少 I/O 負擔，提高查詢效能。

結論

了解 DQL 指令的執行順序能編寫更高效的 SQL 查詢。透過優化 WHERE 篩選順序、善用索引、避免 SELECT * 等方式，可以顯著提升查詢效能。

【MySQL】DQL 指令的執行順序與效能優化

https://forgetfulengineer.github.io/Backend/MySQL/Execution-Order-and-Performance-Optimization-of-DQL-Commands/

作者

健忘工程師

發表於

2025-03-25

更新於

2025-03-25

許可協議

你可能也想看

【MySQL】解析資料庫語言類型 DDL、DML、DCL、DQL
【PHP】4種判斷變數或陣列是否存在的方法
【PHP】解析邏輯運算子 and、or、&&、|| 之間的差異

評論

follow.it

歡迎訂閱電子報！追蹤【健忘工程師】的最新資訊～

廣告

最新文章

廣告

×
複製完成