Appearance

MySQL SQL 进阶 ​

SQL 面试不能只会语法，还要能解释逻辑执行顺序、结果是否确定、怎样避免重复扫描和 N+1。本页以 MySQL 8.x 为主。

查询的逻辑执行顺序 ​

常用记忆顺序：

text
FROM / JOIN → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → DISTINCT → ORDER BY → LIMIT

这是逻辑语义，不等于物理执行计划。优化器可以在不改变结果的前提下下推条件、调整 Join 顺序或改写子查询。

聚合与条件聚合 ​

sql
select user_id,
       count(*) as order_count,
       sum(amount) as total_amount,
       sum(case when status = 'PAID' then amount else 0 end) as paid_amount
from orders
where created_at >= '2026-08-01'
group by user_id
having count(*) >= 3;

where 在分组前过滤行，having 在分组后过滤结果。开启 ONLY_FULL_GROUP_BY 后，非聚合列必须满足分组语义，不能依赖“碰巧取到某一行”。

窗口函数 ​

窗口函数保留明细行，同时对窗口计算排名、累计值或前后行：

sql
with ranked as (
  select id, user_id, amount, created_at,
         row_number() over (
           partition by user_id
           order by created_at desc, id desc
         ) as rn
  from orders
)
select id, user_id, amount, created_at
from ranked
where rn <= 3;

常见函数：row_number、rank、dense_rank、lag、lead、sum(...) over (...)。排名必须有稳定排序条件，最好追加唯一键，否则相同排序值的顺序不确定。

CTE 与递归 CTE ​

sql
with recursive category_tree as (
  select id, parent_id, name, 0 as depth
  from category
  where id = ?
  union all
  select c.id, c.parent_id, c.name, t.depth + 1
  from category c
  join category_tree t on c.parent_id = t.id
  where t.depth < 20
)
select * from category_tree;

递归查询必须考虑环、最大深度和结果规模。CTE 主要改善表达，不保证天然更快；是否合并或物化由版本和优化器决定，应查看执行计划。

子查询与 EXISTS ​

查找存在已支付订单的用户：

sql
select u.id, u.username
from users u
where exists (
  select 1
  from orders o
  where o.user_id = u.id
    and o.status = 'PAID'
);

exists 关注是否存在匹配行；in 表达集合成员关系。现代优化器可能把它们转换为半连接，不能用“exists 一定比 in 快”作绝对结论。应检查 NULL 语义、数据分布、索引和执行计划。

特别注意：

sql
where id not in (select nullable_id from blacklist)

子查询只要包含 NULL，三值逻辑可能让结果与预期不同。可确保列非空，或改写为相关的 not exists。

UNION 与 UNION ALL ​

  • union 去重，通常需要额外排序或临时结构。
  • union all 保留重复行，通常成本更低。
  • 能确定结果集互斥或允许重复时，优先表达真实语义，而不是习惯性 union。

NULL 的三值逻辑 ​

SQL 中比较结果可能是 TRUE、FALSE、UNKNOWN：

sql
-- 错误：永远不能用 = NULL 判断
where deleted_at = null

-- 正确
where deleted_at is null

count(*) 统计行数，count(column) 忽略 NULL。sum、avg 也忽略 NULL；空集合结果可能为 NULL，可用 coalesce 明确业务默认值。

稳定分页与深分页 ​

传统分页：

sql
select id, created_at
from orders
where user_id = ?
order by created_at desc, id desc
limit 100000, 20;

偏移越深，扫描并丢弃的记录越多。游标分页：

sql
select id, created_at
from orders
where user_id = ?
  and (created_at < ? or (created_at = ? and id < ?))
order by created_at desc, id desc
limit 20;

配套索引：

sql
create index idx_orders_user_created_id
  on orders(user_id, created_at desc, id desc);

写入语句的工程语义 ​

Upsert ​

sql
insert into account_balance(user_id, balance)
values (?, ?)
on duplicate key update balance = values(balance);

它依赖主键或唯一键发现冲突。要确认受影响行数语义、触发器、副作用以及并发下最终覆盖是否符合业务。

条件更新保证并发正确性 ​

sql
update sku_stock
set available = available - ?, version = version + 1
where sku_id = ?
  and available >= ?
  and version = ?;

应用检查受影响行数判断成功。把判断和修改放在同一条 SQL 中，比“先查库存再更新”少一个竞态窗口。

常见反模式 ​

反模式风险改进
select *网络与回表成本高，接口易受列变更影响只取需要列
循环逐条查询N+1，网络往返放大批量查询、合理 Join、DataLoader
无 order by 依赖返回顺序结果顺序没有保证使用稳定且唯一的排序
大量字符串拼接 SQL注入、计划复用差参数化查询
一次更新海量行长事务、锁、redo、复制延迟按稳定键分批并控制节奏
在索引列上做函数可能无法使用普通索引定位改范围条件或函数索引

面试题 ​

WHERE 和 HAVING 有什么区别 ​

where 过滤分组前的输入行，通常更早缩小数据；having 过滤聚合后的组，可以引用聚合条件。能在 where 表达的行级条件不应无故留到 having。

窗口函数和 GROUP BY 有什么区别 ​

group by 把多行折叠为每组一行；窗口函数保留原行，在指定分区和顺序上计算。Top N、累计值、环比和相邻行比较通常适合窗口函数。

逻辑执行顺序和真实执行顺序一样吗 ​

不一样。逻辑顺序定义 SQL 结果语义；真实执行由优化器根据等价变换和成本选择。判断性能要看执行计划，不能只按书写顺序推测。

字符串、日期、数值、条件、聚合、窗口、JSON、函数索引和自定义存储函数的完整用法，继续阅读 MySQL 函数大全与性能边界。