MySQL查詢SQL語句的執(zhí)行順序的方法

更新時間：2025年10月20日 08:49:50 作者：學亮編程手記

本文詳細介紹了MySQL SELECT語句各子句的邏輯執(zhí)行順序及各階段作用,并結(jié)合常見場景說明執(zhí)行流程,強調(diào)WHERE與HAVING區(qū)別、別名使用限制、JOIN條件及子查詢執(zhí)行特點,對優(yōu)化和理解SQL查詢有重要指導意義,需要的朋友可以參考下

標準執(zhí)行順序

MySQL在執(zhí)行一條SELECT查詢時，邏輯上的執(zhí)行順序如下：

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

讓我詳細解釋每個階段：

1. FROM階段

首先確定數(shù)據(jù)來源，包括表連接操作。如果有JOIN，會先執(zhí)行笛卡爾積或根據(jù)JOIN條件進行表連接。多個表的JOIN按從左到右的順序執(zhí)行。

2. WHERE階段

對FROM階段產(chǎn)生的結(jié)果集進行條件過濾，篩選出符合條件的行。此時還不能使用SELECT中定義的別名，因為SELECT還沒執(zhí)行。

3. GROUP BY階段

按照指定的列對數(shù)據(jù)進行分組，為聚合函數(shù)的計算做準備。

4. HAVING階段

對分組后的結(jié)果進行過濾，通常與聚合函數(shù)配合使用。HAVING可以使用SELECT中的別名（某些MySQL版本支持）。

5. SELECT階段

選擇要返回的列，執(zhí)行計算、函數(shù)調(diào)用等操作，生成最終的列。

6. DISTINCT階段

如果有DISTINCT關(guān)鍵字，對結(jié)果集去重。

7. ORDER BY階段

對結(jié)果集進行排序。可以使用SELECT中定義的別名。

8. LIMIT階段

限制返回的行數(shù)，通常用于分頁。

常見場景示例

場景1：基礎(chǔ)查詢

SELECT name, age
FROM users
WHERE age > 18
ORDER BY age DESC
LIMIT 10;

執(zhí)行順序：FROM users → WHERE age > 18 → SELECT name, age → ORDER BY age DESC → LIMIT 10

場景2：聚合查詢

SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

執(zhí)行順序：FROM employees → WHERE status = ‘active’ → GROUP BY department → HAVING COUNT() > 5 → SELECT department, COUNT(), AVG(salary) → ORDER BY avg_salary DESC

場景3：多表JOIN

SELECT u.name, o.order_date, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2025-01-01'
ORDER BY o.order_date DESC;

執(zhí)行順序：FROM users u → INNER JOIN orders o → WHERE o.order_date > ‘2025-01-01’ → SELECT u.name, o.order_date, o.amount → ORDER BY o.order_date DESC

場景4：子查詢

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

執(zhí)行順序：先執(zhí)行子查詢得到平均工資 → FROM employees → WHERE salary > 子查詢結(jié)果 → SELECT name, salary → ORDER BY salary DESC

場景5：DISTINCT去重

SELECT DISTINCT city
FROM customers
WHERE country = 'China'
ORDER BY city;

執(zhí)行順序：FROM customers → WHERE country = ‘China’ → SELECT city → DISTINCT → ORDER BY city

場景6：復雜的多表關(guān)聯(lián)與分組

SELECT d.name as dept_name, COUNT(e.id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id AND e.status = 'active'
WHERE d.region = 'East'
GROUP BY d.id, d.name
HAVING employee_count > 0
ORDER BY employee_count DESC
LIMIT 5;

執(zhí)行順序：FROM departments d → LEFT JOIN employees e (包含ON條件) → WHERE d.region = ‘East’ → GROUP BY d.id, d.name → HAVING employee_count > 0 → SELECT d.name, COUNT(e.id) → ORDER BY employee_count DESC → LIMIT 5