写 SQL 时，我们总是遇到一些闹心事：明明逻辑一样，用 SELECT * 就是比写具体列慢半拍；换个数据库跑，带 NULL 的排序结果就乱了；加了 LIMIT，百万数据还是查得费劲。其实这些不是我们 SQL 写得差，而是没有摸清 SQL 执行里那些 “不怎么说但特别关键” 的门道。接下来我们就结合 MySQL、PostgreSQL、ClickHouse 等等这些常用数据库，扒一扒 5 个颠覆认知的冷知识和 3 个行业默认规则，让我们不光记住语法，而且理解执行背后的逻辑与默认规则，帮助我们从 “会写 SQL” 变成 “懂执行”，以后那些慢查询、结果不一致的坑，我们就可以提前就避开。

一、SQL执行顺序的5个冷知识

1、冷知识1：SELECT *不会改变逻辑顺序，但会破坏物理执行路径

现象：同样的查询逻辑，我们使用 SELECT * 比显式列名慢得多，甚至可能改变优化器的执行计划。

对比：

-- 慢：SELECT * 在索引未覆盖所有字段时，会强制回表
SELECT * 
FROM orders 
WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY user_id;

-- 快：显式列名 + 索引覆盖，避免回表
SELECT user_id, SUM(amount) 
FROM orders 
WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY user_id;

原理：

• SELECT * 要求加载表中所有列，若索引未覆盖所有字段（如：(order_time, user_id, amount) 缺少 status），数据库需执行“回表”（Bookmark Lookup / Table Access by Index Row ID），从索引页跳转到主表数据页读取完整行，大幅增加I/O开销。
• 显式列名时，若索引包含“筛选+分组+计算”等所需字段（如：(order_time, user_id, amount)），可实现“索引覆盖”（Covering Index），直接在索引页完成查询，跳过回表步骤，执行顺序中减少“数据页读取”环节。

结论：避免使用SELECT *：我们显式写所需列名，不仅提升性能（优化执行顺序，减少回表），还能避免表结构变更导致的字段冗余与兼容性问题。

2、冷知识2：NULL值不影响GROUP BY分组逻辑，但破坏ORDER BY跨库一致性

现象：GROUP BY 会将所有 NULL 值归为同一组，但 ORDER BY 时 NULL 分组的排序位置在不同数据库中不一致。

原理：

• SQL标准规定：GROUP BY 将 NULL 视为“相等值”进行分组（即所有 NULL 归为一组）。
• 但 ORDER BY 中 NULL 的排序位置（NULLS FIRST / NULLS LAST）在 SQL:2003 才标准化，各数据库实现不同。

结论：若字段可能为 NULL，分组前显式处理：我们使用 COALESCE(user_id, -1) 或 CASE WHEN user_id IS NULL THEN -1 ELSE user_id END，确保跨库结果一致。

3、冷知识3：LIMIT不终止全量排序？不一定，Top-N 优化可破局

现象：即使 LIMIT 10，数据库仍可能对百万行数据全排序，但现代优化器可避免此问题。

示例：

-- 可能全排序（若无索引）
SELECT user_id, SUM(amount) 
FROM orders 
WHERE order_time >= '2024-01-01'
GROUP BY user_id
ORDER BY SUM(amount) DESC
LIMIT 10;

原理：

• 若无索引支持，数据库需对所有聚合结果排序才能取 Top 10，时间复杂度 O(N log N)。
• 但若存在 (order_time, amount) 索引，或优化器启用 Top-N 优化（如：堆排序），可仅维护前10个最大值，复杂度降至 O(N log K)，K=10 时接近线性。

结论：为 LIMIT + ORDER BY 查询创建联合索引：如：(筛选字段, 排序列 DESC)（需满足 “筛选字段是等值 / 范围筛选，且排序列在筛选字段之后” 的逻辑），让优化器使用索引扫描或 Top-N 优化，避免全排序。

说明：以上索引建议针对 “非聚合场景”（如：SELECT id FROM orders WHERE order_time >= '2024-01-01' ORDER BY id LIMIT 10）有效，但对 “GROUP BY + 聚合排序” 场景，索引需包含 “筛选 + 分组 + 聚合依赖字段”。

4、冷知识4：递归 CTE不会自动“反向迭代”，但可优化执行路径

现象：递归 CTE（如：部门层级查询）的执行效率受数据结构影响极大，MySQL 递归 CTE 的迭代方向由我们通过 JOIN 条件定义，优化器不会主动修改迭代逻辑（如：父查子不会自动转为子查父）。

示例（MySQL 递归 CTE）：

WITH RECURSIVE dept_tree AS (
  SELECT dept_id, parent_dept_id FROM dept WHERE dept_id = 1  -- 根节点
  UNION ALL
  SELECT d.dept_id, d.parent_dept_id 
  FROM dept d 
  JOIN dept_tree t ON d.parent_dept_id = t.dept_id  -- 父查子：逐层向下
)
SELECT * FROM dept_tree;

原理：

• MySQL 8.0+ 的递归 CTE 采用 迭代展开（Iterative Expansion）：从锚点开始，逐层执行 UNION ALL，直到无新行。
• 它不会自动反转递归方向。所谓“反向迭代”是某些图数据库或 OLAP 引擎的特性，MySQL 不支持。
• 优化点在于：物化中间结果、避免重复计算、限制递归深度（cte_max_recursion_depth）。

结论：深层层级慎用递归CTE：若层级过深（>100），建议我们改用“闭包表”（Closure Table）或“路径枚举”（Path Enumeration）设计，避免性能雪崩。

5. 冷知识5：JOIN的驱动表选择影响谓词下推能力

现象：小表驱动大表时，WHERE 条件可提前下推；大表驱动时，可能被迫延迟执行。

对比：

-- 优化器选择：小表A驱动大表B → 谓词下推
SELECT * FROM A JOIN B ON A.id = B.id WHERE B.x = 1;
-- 执行路径：A全量加载 → B先过滤x=1（1万行） → JOIN（100 * 1万）

-- 强制大表驱动 → 谓词无法下推
SELECT * FROM B JOIN A ON B.id = A.id WHERE B.x = 1;
-- 执行路径：B全量加载（100万） → JOIN A（100万*100） → 再过滤

原理：

• 优化器通过统计信息选择“小表驱动”，并执行 谓词下推（Predicate Pushdown），将 WHERE 条件提前到 JOIN 前执行，减少中间结果集。
• 若统计信息过时（与超时意思不一样），优化器可能选错驱动表。

结论：信任优化器，但保持统计信息新鲜：定期执行 ANALYZE TABLE，避免因统计信息偏差导致执行计划劣化。

二、SQL执行顺序的3个行业默认规则（共识）

1、默认规则1：WHERE只做“行级筛选”，业务逻辑放子查询

原则：WHERE 仅用在剔除明显的无效数据（如：时间范围、状态码），复杂判断放 CTE 或子查询。

推荐写法：

WITH user_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE order_time BETWEEN '2024-01-01' AND '2024-12-31'
    AND order_status = '已完成'
  GROUP BY user_id
)
SELECT user_id FROM user_orders WHERE order_count >= 2;  -- 复购用户

避免写法：

SELECT user_id FROM orders
WHERE order_time BETWEEN '2024-01-01' AND '2024-12-31'
  AND order_status = '已完成'
  AND user_id IN (SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*)>=2);

理由：分离“过滤”与“业务逻辑”，提升可读性与可维护性。

2、默认规则2：窗口函数只计算，不筛选

原则：窗口函数仅用在计算排名、累计值等衍生指标，筛选操作必须用子查询+WHERE。

推荐写法：

WITH ranked_sales AS (
  SELECT 
    product_id,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rk
  FROM daily_sales
)
SELECT product_id FROM ranked_sales WHERE rk <= 3;

错误写法（语法不支持）：

SELECT product_id 
FROM daily_sales 
WHERE RANK() OVER (ORDER BY sales DESC) <= 3;  -- 报错：窗口函数不能在WHERE中使用

理由：执行顺序中，窗口函数在 SELECT 阶段，WHERE 在其之前，无法引用。

3、默认规则3：跨库SQL优先使用“执行顺序兼容语法”

原则：在多数据库环境（MySQL/PG/ClickHouse），避免依赖专属特性，确保执行顺序一致。

推荐写法：

SELECT
  sale_date,
  AVG(sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 显式帧范围，跨库一致
  ) AS avg_7d
FROM (
  SELECT sale_date, sales FROM daily_sales WHERE sale_date >= '2024-01-01'
) tmp;

避免写法：

WITH cte AS (...)
SELECT AVG(sales) OVER (ORDER BY sale_date)  -- 依赖默认帧范围，跨库可能不同
FROM cte;

理由：CTE 物化行为、窗口函数默认帧、NULL 排序等在各数据库中差异大，显式定义更安全。

看完这些我们会发现，写 SQL，光记语法真不够。从规避“SELECT * 回表”、“NULL 排序异常”、“递归CTE性能雪崩”等隐性陷阱，再到遵循“WHERE 过滤先行”、“窗口函数分离计算与筛选”等行业规范，要想写出高可维护 SQL，每一步都得顺着执行机制优化。这些经验不是凭空得来的，而是无数前辈踩过无数的坑总结出来的干货。记住：搞懂执行背后的逻辑，比死记硬写重要得多。照着这些方法练习，以后不管面对大数据量，还是多数据库等跨库场景，我们都能稳稳地写出高效又靠谱的 SQL，能预判执行计划差异，真正进阶成懂行的 SQL 高手。