cs-note/hhs/MySQL/02-SQL核心/08-DQL SELECT 全解析.md

tags, create time

tags	create time
MySQL SELECT 查询执行顺序 GROUP BY 分页 窗口函数 CTE	2026-05-16 00:00

DQL — SELECT 全解析

概述

SELECT 是 MySQL 中使用频率最高的语句，也是最容易被误解的语句。理解其内部执行顺序是写出正确且高效查询的第一步。

本文覆盖 SELECT 从基础语法到进阶优化的所有核心场景：

模块	内容	关键词
执行顺序	书写顺序 vs 执行顺序、完整示例	WHERE / GROUP BY / HAVING / ORDER BY
关键字详解	DISTINCT、GROUP BY 优化、条件过滤	索引利用、聚合
条件表达式	CASE 分支、IF 三目运算	数据变形、行转列
窗口函数	排名、前后行访问、累计计算、帧子句	PARTITION BY / ROWS BETWEEN
子查询与 CTE	标量子查询、EXISTS、WITH、递归 CTE	可读性、复用性
深分页优化	延迟关联、游标分页、性能对比	LIMIT 陷阱
性能贴士	常见反模式与修复方案	Covering Index、索引失效

SQL 书写顺序 vs 执行顺序

flowchart LR
    W["⑥ SELECT"] --> A["① FROM"]
    A --> B["② JOIN"]
    B --> C["③ ON"]
    C --> D["④ WHERE"]
    D --> E["⑤ GROUP BY"]
    E --> F["⑦ HAVING"]
    F --> G["⑧ DISTINCT"]
    G --> H["⑨ ORDER BY"]
    H --> I["⑩ LIMIT / OFFSET"]
    
    style W fill:#C44569,color:#fff
    style A fill:#00B6BC,color:#fff
    style I fill:#4FC08D,color:#fff

[!QUESTION] 为什么理解这个很重要？ 因为 SQL 不是按照你写的顺序执行的——而是按数字顺序！这意味着：

• WHERE 在 SELECT 之前执行 → 不能在 WHERE 中使用 SELECT 定义的别名
• GROUP BY 在 HAVING 之前 → WHERE 过滤行，HAVING 过滤分组
• ORDER BY 在 LIMIT 之前 → 先排序再截断

一个完整的例子

SELECT 
    DATE(created_at) AS order_date,          -- ⑥ 计算列
    COUNT(*) AS cnt,                          -- 聚合函数
    SUM(amount) AS total                      -- 聚合函数
FROM orders                                 -- ① 确定数据源
WHERE status = 'paid'                       -- ④ 先过滤行
  AND created_at >= '2026-01-01'            -- 过滤条件
GROUP BY DATE(created_at)                    -- ⑤ 按日期分组
HAVING COUNT(*) > 5                          -- ⑦ 过滤分组
ORDER BY total DESC                          -- ⑨ 排序
LIMIT 10;                                    -- ⑩ 取前 10 页

SELECT 关键字详解

DISTINCT

-- 去重查询
SELECT DISTINCT department FROM employees;

-- DISTINCT 作用于所有选中的列组合
SELECT DISTINCT country, city FROM customers;
-- 返回的是 (country, city) 的唯一组合

[!QUESTION] DISTINCT 一定快吗？ 不一定。DISTINCT 本质上是 GROUP BY 的简化版——MySQL 内部可能通过 temporary table + filesort 去重。当数据量大时，它的代价不亚于一次普通的分组聚合。

替代方案：如果去重目的是为下拉框提供选项，可以用 SELECT DISTINCT department FROM employees LIMIT 50; 限制返回量；更优的做法是在应用层缓存选项列表。

GROUP BY 优化

-- ✅ 好：GROUP BY 走索引
-- idx_status_dept = (status, department)，可以直接按 department 分组
SELECT department, COUNT(*) 
FROM employees 
WHERE status = 'active'
GROUP BY department;

-- ❌ 差：GROUP BY 无法利用索引（LIKE 左模糊导致索引失效）
SELECT department, COUNT(*) 
FROM employees 
WHERE name LIKE '%chen%'                     -- 左模糊导致索引失效
GROUP BY department;
-- EXPLAIN: Using where; Using temporary

HAVING 与 WHERE 的选择

-- ✅ WHERE 过滤行（早过滤，减少数据量）
SELECT department, AVG(salary) 
FROM employees 
WHERE hire_date >= '2024-01-01'              -- 先筛选最近入职的人
GROUP BY department
HAVING AVG(salary) > 15000;                  -- 再过滤平均工资

-- ❌ 把能放 WHERE 的条件放到 HAVING 里
-- 虽然结果一样，但效率更低
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department
HAVING hire_date >= '2024-01-01'             -- 错！HAVING 不能用非聚合列
    AND AVG(salary) > 15000;

条件表达式 — CASE / IF / IFNULL

在 SELECT 中插入"逻辑判断"，是做数据变形（Pivot、区间分组）的核心技能。

CASE 表达式

SQL 中的 switch-case——标准 SQL 可移植性最好的分支语法：

-- ✅ CASE WHEN：多路分支
SELECT name, salary,
       CASE 
           WHEN salary >= 20000 THEN 'L5+'
           WHEN salary >= 15000 THEN 'L4'
           WHEN salary >= 10000 THEN 'L3'
           ELSE 'L2-'
       END AS level
FROM employees;

-- ✅ CASE WHEN：实现行转列（Pivot）
-- 统计各部门各职级的员工数
SELECT department,
       SUM(CASE WHEN level = 'P' THEN 1 ELSE 0 END) AS individual_count,
       SUM(CASE WHEN level = 'M' THEN 1 ELSE 0 END) AS manager_count,
       SUM(CASE WHEN level = 'D' THEN 1 ELSE 0 END) AS director_count
FROM employees
GROUP BY department;

[!TIP] CASE 位置决定影响范围

• WHERE/CASE → 逐行过滤，走普通索引
• HAVING/CASE → 需要先聚合再过滤，效率较低
• 能用 WHERE 解决的，不要推到 HAVING

-- ❌ 把能写在 WHERE 的判断放到 HAVING
SELECT status, COUNT(*) FROM orders GROUP BY status HAVING status IN ('paid', 'shipped');
-- ✅ WHERE 先缩小范围，再聚合
SELECT status, COUNT(*) FROM orders WHERE status IN ('paid', 'shipped') GROUP BY status;

IF / IFNULL / COALESCE

MySQL 专属的快捷函数，适合简单场景：

-- IF(condition, true_value, false_value) — 三目运算
SELECT name,
       IF(status = 'active', '在职', '离职') AS label,
       IF(salary IS NULL, 0, salary) AS pay
FROM employees;

-- IFNULL(val, default) — 空值替换
SELECT order_id, IFNULL(comments, '暂无评价') AS review
FROM orders;

-- COALESCE(v1, v2, ..., vn) — 返回第一个非 NULL 值
-- MySQL 8.0.19+ 支持多个参数（之前只支持 2 个）
SELECT name, COALESCE(alias, nickname, name) AS display_name;
-- 优先级：别名 > 昵称 > 真实姓名

[!NOTE] CASE vs IF 的选择

场景	推荐	原因
三路以上分支	CASE WHEN	可读性好，易扩展
二选一简单判断	IF()	简洁，但仅限 MySQL
空值兜底	COALESCE()	标准 SQL，比多个 IFNULL 嵌套更优雅

窗口函数 (WINDOW FUNCTIONS)

窗口函数是 MySQL 8.0+ 引入的分析利器——它能在不减少行数的前提下进行聚合计算。

排名函数

-- ROW_NUMBER() / RANK() / DENSE_RANK()
-- 按部门内工资排名（处理并列名次的三种方式）
SELECT name, department, salary,
       ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()          OVER(PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK()    OVER(PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

-- 结果示例：
-- | name  | dept   | salary | rn | rnk | drnk |
-- | Alice | sales  | 20000  | 1  | 1   | 1    |
-- | Bob   | sales  | 20000  | 2  | 1   | 1    |
-- | Carol | sales  | 18000  | 3  | 3   | 2    |

[!TIP] RANK vs DENSE_RANK 的区别

• RANK(20000, 20000, 18000) → 1, 1, 3（跳过第二名）
• DENSE_RANK(20000, 20000, 18000) → 1, 1, 2（不跳号）
• ROW_NUMBER → 1, 2, 3（永远无并列）

前后行访问 — LAG / LEAD

SELECT order_date, amount,
       LAG(amount, 1) OVER(ORDER BY order_date) AS prev_amount,   -- 前一天的金额
       LEAD(amount, 1) OVER(ORDER BY order_date) AS next_amount   -- 后一天的金额
FROM daily_sales;

-- 计算日环比增长率
SELECT order_date, amount,
       ROUND((amount - LAG(amount) OVER(ORDER BY order_date)) / LAG(amount) OVER(ORDER BY order_date) * 100, 2) AS growth_pct
FROM daily_sales;

累计计算 — Running Total / Percentage

-- 累计求和 (Running Total)
SELECT order_date, amount,
       SUM(amount) OVER(ORDER BY order_date) AS running_total
FROM daily_sales;

-- 当前分区内占比
SELECT department, name, salary,
       ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY department), 2) AS dept_pct
FROM employees;

窗口函数执行流程

flowchart TD
    A["FROM / JOIN<br/>确定数据源"] --> B["WHERE<br/>逐行过滤"]
    B --> C["GROUP BY<br/>分组聚合"]
    C --> D["HAVING<br/>分组后过滤"]
    D --> E["SELECT 列计算<br/>包括窗口函数"]
    E --> F["ORDER BY<br/>排序结果"]
    F --> G["LIMIT / OFFSET<br/>截断输出"]
    
    style E fill:#00B6BC,color:#fff
    
    linkStyle 4 stroke-width:3px,fill:none,stroke:#00B6BC

[!NOTE] 窗口函数的"隐形"特性

• 执行顺序在 WHERE、GROUP BY、HAVING 之后，ORDER BY 之前
• 因此不能用 WHERE 直接过滤窗口函数的结果——需要套一层子查询：

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) ranked WHERE rn = 1;
-- 作用：每个用户的最新一条订单记录

更详细的帧子句说明见下方「### 帧子句 — ROWS BETWEEN」小节。

帧子句 — ROWS BETWEEN

默认帧范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。用 ROWS BETWEEN 可以更精确控制：

-- 近 7 天滑动窗口均值
SELECT order_date, amount,
       ROUND(AVG(amount) OVER(
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS avg_7day
FROM daily_sales;

[!NOTE] 帧子句速查

语法	含义
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	从分区起点到当前行（默认）
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW	当前行及前 6 行，共 7 行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	整个分区
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING	前后各 2 行的滑动窗口

子查询与 CTE

标量子查询 (Scalar Subquery)

返回单一值，可以像普通列一样使用：

-- WHERE 中的标量子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);   -- 工资高于公司平均的人

行子查询 (Row Subquery / IN)

-- IN 子查询
SELECT name, department
FROM employees
WHERE department IN (SELECT id FROM departments WHERE region = 'APAC');

-- EXISTS：关注"是否存在"而非具体数据，比 IN 更高效
SELECT d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

CTE (Common Table Expression) — WITH 语法

MySQL 8.0+ 推荐用法，比嵌套子查询可读性强很多：

-- 简单 CTE
WITH dept_stats AS (
    SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_stats WHERE avg_salary > 15000;

-- 递归 CTE：处理层级数据（组织架构、分类树等）
WITH RECURSIVE org_chart AS (
    -- 锚点成员：根节点
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归成员：逐层展开
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

[!QUESTION] CTE vs 派生表？

• 可读性：CTE 命名清晰，逻辑分层；派生表层层嵌套，括号匹配困难
• 性能：MySQL 会将非递归 CTE 优化为临时表或内联展开——多数情况下两者性能一致
• 复用：同一个 CTE 可在一个语句中多次引用（派生表不行）

深分页陷阱

LIMIT offset, size 在 offset 很大时性能急剧下降——MySQL 仍需扫描并跳过前面所有行。

-- ❌ 灾难级：扫描 100 万行后丢弃前 999,990 行
SELECT * FROM orders LIMIT 999990, 10;

-- ✅ 方案一：延迟关联（扫主键索引，只回表 10 次）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 999990, 10
) AS tmp ON o.id = tmp.id;

-- ✅✅ 方案二：游标分页（推荐，O(log N) 恒定性能）
SELECT * FROM orders
WHERE id > 999985           -- 上一页最后一条的 ID
ORDER BY id ASC
LIMIT 10;

[!TIP] 分页方案选型

方案	复杂度	支持跳页	适用场景
传统 LIMIT n, m	O(N)	✅	小数据量 (< 1 万行)
延迟关联	O(log N + m)	✅	大数据量、需要精确页码
游标分页	O(log N)	❌	瀑布流、"加载更多"

更完整的分析与调优策略请见 → hhs/MySQL/03-索引与查询优化/18-深分页优化

性能小贴士

SELECT * 的反面教材

-- ❌ 避免 SELECT *
SELECT * FROM users WHERE status = 1;

-- ✅ 只查需要的列
SELECT id, username, email FROM users WHERE status = 1;
-- 好处：减少网络传输、提高 Buffer Pool 命中率、可能触发 Covering Index

函数包裹索引列

-- ❌ 函数包裹导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ 用范围替代函数（走索引范围扫描）
SELECT * FROM users 
WHERE created_at >= '2026-01-01' 
  AND created_at < '2027-01-01';

ORDER BY 与 Using filesort

-- ✅ 排序列有索引 → 直接按索引顺序输出，无需额外排序
SELECT * FROM orders ORDER BY user_id;
-- EXPLAIN Extra: NULL (无 filesort)

-- ⚠️ 混合 ASC/DESC → 无法利用普通 B+Tree 索引排序
SELECT * FROM orders ORDER BY user_id ASC, created_at DESC;
-- EXPLAIN Extra: Using filesort — 需要额外的内存/磁盘排序

[!TIP] 覆盖索引 (Covering Index) 当 SELECT 的列全部包含在某个索引中时，InnoDB 可以直接从索引树返回结果，无需回表。

-- 创建覆盖索引：id + status + updated_at 都在 idx 中
CREATE INDEX idx_cover ON users(status, updated_at);
-- 下面这个查询完全走索引扫描，不回表
SELECT status, updated_at FROM users WHERE status = 1;

验证方法：看 EXPLAIN 的 Extra 列是否出现 Using index。

关联笔记

• hhs/MySQL/02-SQL核心/09-JOIN 原理与优化 — JOIN 的内部执行机制与驱动表选择
• hhs/MySQL/02-SQL核心/10-子查询与派生表 — EXISTS / IN 子查询与 CTE 的性能对比
• hhs/MySQL/03-索引与查询优化/15-EXPLAIN 完全指南 — 通过执行计划识别查询瓶颈