关系数据库是过去半个世纪最成功的数据模型之一。它基于严密的数学理论——关系代数，并通过 SQL 这种声明式语言实现了数据的增删改查。无论是 MySQL、PostgreSQL 还是 Oracle，其核心都是这套关系模型。本篇文章从关系代数理论出发，系统梳理 SQL 的逻辑执行顺序、窗口函数、CTE 递归查询以及性能优化基础。
1. 关系代数：SQL 的理论根基
关系代数由 Edgar F. Codd 于 1970 年提出，定义了一套对关系（表）进行操作的运算集合。SQL 中的绝大多数操作都可以映射到关系代数。
1.1 核心运算符
| 关系代数 | 符号 | SQL 对应 | 说明 |
|---|---|---|---|
| 选择 | sigma | WHERE | 按条件过滤行 |
| 投影 | pi | SELECT / DISTINCT | 选择列，可去重 |
| 并 | cup | UNION | 合并两个相容关系 |
| 差 | - | EXCEPT / MINUS | 在第一个关系中但不在第二个中 |
| 笛卡尔积 | times | CROSS JOIN | 所有行的组合 |
| 连接 | bowtie | JOIN | 按条件关联两个关系 |
| 重命名 | rho | AS | 列或表别名 |
选择（sigma）按谓词过滤行，投影（pi）选择列。两者是最基础的组合：
π name, salary (σ salary > 50000 (employees))
↓
SELECT name, salary FROM employees WHERE salary > 50000;
连接（bowtie）是关系代数中计算开销最大的算子。物理执行时细化为三种策略：
- Nested Loop Join：对小表逐行扫描，对大表走索引探测。适合小表驱动场景。
- Hash Join：先对小表构建哈希表，再逐行扫描大表探测。适合无索引的大表关联。
- Merge Join：要求两张表在连接列上有序，通过双指针归并。适合已排序数据。
1.2 算子等价变换
优化器利用代数等价律选择代价最低的执行路径。最典型的例子是选择下推：
σ p (R bowtie S) ≡ σ p (R) bowtie S （当 p 只涉及 R 的属性时）
此变换能尽早减少参与连接的数据量，是几乎所有关系型数据库优化器的默认策略。
2. SQL 逻辑执行顺序
SQL 语句的书写顺序与执行顺序不一致。引擎按以下顺序处理：
FROM → JOIN
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
DISTINCT / WINDOW
↓
ORDER BY
↓
LIMIT
FROM 阶段构造笛卡尔积或按 JOIN 条件过滤；WHERE 淘汰不满足谓词的整行；GROUP BY 将数据切分为桶，此后聚合函数才能计算；HAVING 对聚合结果二次过滤；SELECT 投影列并执行标量表达式；窗口函数在 SELECT 之后执行，可访问 ORDER BY 前的完整结果集。
理解这一顺序能解释两个常见限制：
-- ❌ WHERE 中不能引用 SELECT 中的列别名
SELECT name AS full_name FROM employees WHERE full_name LIKE 'A%';
-- ✅ 改用子查询或直接使用原始列名
SELECT name AS full_name FROM employees WHERE name LIKE 'A%';
-- ✅ 聚合过滤必须用 HAVING，因为 WHERE 在 GROUP BY 之前
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
3. 窗口函数
窗口函数在行间进行计算，同时保留原始行粒度，与 GROUP BY 的"折叠"行为形成鲜明对比。
3.1 排序与排名
SELECT
name,
department,
salary,
-- 连续排名，无并列间隙
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
-- 并列同排名，下一排名跳号
RANK() OVER (ORDER BY salary DESC) AS rk,
-- 并列同排名，下一排名不跳号
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr,
-- 按部门分区排名
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rk
FROM employees;
3.2 偏移与分析
SELECT
employee_id,
month,
amount,
-- 上月销售额
LAG(amount, 1, 0) OVER (
PARTITION BY employee_id
ORDER BY month
) AS prev_month,
-- 下月销售额
LEAD(amount, 1, 0) OVER (
PARTITION BY employee_id
ORDER BY month
) AS next_month,
-- 3 个月移动平均
AVG(amount) OVER (
PARTITION BY employee_id
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma3
FROM sales;
3.3 分桶
NTILE 将结果集均分为 N 个桶，常用于百分位分析：
SELECT
user_id,
total_spend,
NTILE(4) OVER (ORDER BY total_spend) AS quartile
FROM orders_summary;
4. CTE 与递归查询
4.1 基础 CTE
CTE（Common Table Expression）通过 WITH 子句定义临时结果集，提升可读性并允许自引用。执行器通常将其展开为子查询或物化临时表。
WITH high_earners AS (
SELECT id, name, salary, department
FROM employees
WHERE salary > 100000
), dept_stats AS (
SELECT department, COUNT(*) AS cnt
FROM high_earners
GROUP BY department
)
SELECT h.*, d.cnt
FROM high_earners h
JOIN dept_stats d ON h.department = d.department;
4.2 递归 CTE：组织架构
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS level,
CAST(name AS VARCHAR(500)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.level + 1,
ot.path || ' > ' || e.name
FROM employees e
INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, REPEAT(' ', level) || name AS display_name, level, path
FROM org_tree
ORDER BY path;
4.3 递归 CTE：图遍历
WITH RECURSIVE graph_path AS (
SELECT from_node, to_node,
ARRAY[from_node, to_node] AS path,
1 AS depth,
weight AS total_weight
FROM edges
WHERE from_node = 'A'
UNION ALL
SELECT e.from_node, e.to_node,
gp.path || e.to_node,
gp.depth + 1,
gp.total_weight + e.weight
FROM edges e
INNER JOIN graph_path gp ON e.from_node = gp.to_node
WHERE NOT e.to_node = ANY(gp.path) -- 避免环路
AND gp.depth < 10
)
SELECT * FROM graph_path
WHERE to_node = 'Z'
ORDER BY total_weight
LIMIT 1;
5. 分页查询与优化
传统 OFFSET/LIMIT 在大页码时性能退化，因为引擎仍需扫描 OFFSET 之前的所有行。
-- CTE 分页：先编号再过滤
WITH page AS (
SELECT id, title, created_at,
ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
FROM articles
)
SELECT id, title, created_at
FROM page
WHERE rn BETWEEN 10001 AND 10050;
键集分页利用严格排序列实现 O(log N) 跳跃，是生产环境深分页的推荐方案：
SELECT id, title, created_at
FROM articles
WHERE created_at < '2026-01-01 00:00:00'
OR (created_at = '2026-01-01 00:00:00' AND id < 99999)
ORDER BY created_at DESC, id DESC
LIMIT 50;
6. SQL 优化基础
查询优化器基于代价模型（Cost Model）选择执行计划，代价通常以 I/O 块读取数和 CPU 运算量估算。
6.1 索引与覆盖扫描
当查询所需全部列均含于非聚集索引中时，引擎可直接通过索引页返回结果，无需回表：
-- 假设 idx_name 包含 (name, salary)
SELECT name, salary FROM employees WHERE name = 'Alice';
-- 覆盖扫描，无需访问数据页
6.2 隐式转换陷阱
-- 字段类型为 VARCHAR，传入整数字面量
WHERE phone = 13800138000
-- 引擎将 phone 列全部转换为 INT，索引失效
6.3 避免 SELECT *
SELECT * 增大网络传输量，且难以利用覆盖索引。在生产代码中应显式列出所需列，同时在 ORM 中配置明确的字段加载白名单。
延伸阅读
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。