如果把 MySQL SQL 分层，大概可以分为：

基础层：CRUD、JOIN、GROUP BY、ORDER BY
进阶层：子查询、窗口函数、CTE、锁、索引优化
高级层（生产实战）：统计分析、去重、分页优化、幂等、防超卖、分布式场景下的数据一致性等

你问的是 “穷尽 MySQL SQL 进阶在生产中的巧妙应用”，下面我按照实际工作中 Java 后端最常见、最容易被面试官追问的场景整理。

1. 去重保留最新数据

场景

用户表误插入重复手机号，只保留最新一条。

DELETE t1
FROM user t1
JOIN user t2
ON t1.phone = t2.phone
AND t1.id < t2.id;

保留最早的数据

DELETE t1
FROM user t1
JOIN user t2
ON t1.phone = t2.phone
AND t1.id > t2.id;

生产中用于：

清洗历史数据
导入Excel后的去重

2. Top N 查询

每个部门工资最高的人

MySQL8：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY dept_id
               ORDER BY salary DESC
           ) rn
    FROM employee
) t
WHERE rn = 1;

实际应用：

每个游戏区充值最多玩家
每个国家消费最高用户

3. Top3

SELECT *
FROM (
    SELECT *,
           DENSE_RANK() OVER(
               PARTITION BY dept_id
               ORDER BY salary DESC
           ) rk
    FROM employee
) t
WHERE rk <= 3;

生产：

排行榜。

4. 累计求和

销售额累计：

SELECT
    date,
    amount,
    SUM(amount) OVER(
        ORDER BY date
    ) total
FROM sales;

结果：

date      amount total
1号       100    100
2号       200    300
3号       50     350

生产：

游戏流水统计
用户累计充值

5. 环比增长

SELECT
    month,
    revenue,
    LAG(revenue) OVER(ORDER BY month) pre,
    revenue -
    LAG(revenue) OVER(ORDER BY month)
FROM report;

生产：

运营报表。

6. 找连续登录

签到：

SELECT user_id,
       COUNT(*)
FROM (
    SELECT *,
           DATE_SUB(login_date,
                    INTERVAL ROW_NUMBER()
                    OVER(PARTITION BY user_id
                         ORDER BY login_date) DAY
                    ) grp
    FROM login_log
) t
GROUP BY user_id, grp;

生产：

连续签到奖励。

7. 漏斗分析

统计：

注册

↓

实名认证

↓

充值

↓

下单

SELECT
COUNT(DISTINCT user_id)
FROM event_log
WHERE event='register';

依次统计。

生产：

运营转化率分析。

8. 防止超卖

库存：

UPDATE goods
SET stock = stock - 1
WHERE id = 1
AND stock > 0;

判断：

rows > 0

表示抢购成功。

比：

SELECT stock...
UPDATE ...

安全得多。

生产：

秒杀。

9. 乐观锁

UPDATE account
SET balance=balance-100,
    version=version+1
WHERE id=1
AND version=10;

生产：

账户系统。

10. 悲观锁

SELECT *
FROM account
WHERE id=1
FOR UPDATE;

生产：

资金扣减。

11. SKIP LOCKED

多个消费者：

SELECT *
FROM task
WHERE status=0
LIMIT 100
FOR UPDATE SKIP LOCKED;

生产：

任务调度。

避免抢同一个任务。

12. 幂等

唯一索引：

UNIQUE(order_no)

插入：

INSERT IGNORE INTO orders(...)
VALUES(...);

或者：

INSERT INTO orders(...)
VALUES(...)
ON DUPLICATE KEY UPDATE
update_time=NOW();

生产：

MQ消费。

13. UPSERT

不存在：

插入。

存在：

更新。

INSERT INTO user(id,name)
VALUES(1,'Tom')
ON DUPLICATE KEY UPDATE
name=VALUES(name);

生产：

同步ES数据。

14. EXISTS优化

慢：

SELECT *
FROM A
WHERE id IN (
    SELECT id FROM B
);

改：

SELECT *
FROM A a
WHERE EXISTS(
    SELECT 1
    FROM B b
    WHERE b.id=a.id
);

生产：

大表关联。

15. 延迟关联分页

错误：

SELECT *
FROM user
LIMIT 1000000,20;

优化：

SELECT u.*
FROM user u
JOIN (
    SELECT id
    FROM user
    LIMIT 1000000,20
) t
ON u.id=t.id;

生产：

后台管理系统。

16. Seek分页（最佳）

SELECT *
FROM user
WHERE id>1000000
ORDER BY id
LIMIT 20;

生产：

无限滚动。

17. 行转列

SELECT
SUM(IF(sex='M',1,0)) male,
SUM(IF(sex='F',1,0)) female
FROM user;

生产：

报表。

18. 列转行

SELECT id,'A',A FROM score
UNION ALL
SELECT id,'B',B FROM score;

生产：

数据迁移。

19. 找重复数据

SELECT phone,
       COUNT(*)
FROM user
GROUP BY phone
HAVING COUNT(*)>1;

生产：

数据治理。

20. 删除重复数据

DELETE t1
FROM user t1
JOIN user t2
ON t1.phone=t2.phone
AND t1.id<t2.id;

21. Gap Lock 防幻读

RR隔离级别：

SELECT *
FROM goods
WHERE id BETWEEN 10 AND 20
FOR UPDATE;

锁住：

(10,20]

之间的插入。

生产：

金融系统。

22. 覆盖索引

SELECT id,name
FROM user
WHERE phone='138';

索引：

(phone,name,id)

无需回表。

生产：

高频查询。

23. 索引下推

SELECT *
FROM user
WHERE age=20
AND name LIKE 'Tom%';

联合索引：

(age,name)

MySQL5.6+：

利用ICP减少回表。

24. 批量更新

错误：

for(...)
 update...

正确：

UPDATE user
SET status=
CASE id
WHEN 1 THEN 0
WHEN 2 THEN 1
END
WHERE id IN(1,2);

生产：

定时任务。

25. 批量插入

错误：

INSERT ...
INSERT ...

正确：

INSERT INTO user(name)
VALUES
('A'),
('B'),
('C');

生产：

导入。

26. 分组取最新

SELECT *
FROM order_info o
WHERE create_time=(
    SELECT MAX(create_time)
    FROM order_info
    WHERE user_id=o.user_id
);

生产：

用户最后一次登录。

27. 时间维度统计

SELECT
DATE(create_time),
COUNT(*)
FROM orders
GROUP BY DATE(create_time);

生产：

日报。

28. 滑动窗口

29. 递归CTE

组织架构：

WITH RECURSIVE dept_tree AS(
    SELECT *
    FROM dept
    WHERE id=1

    UNION ALL

    SELECT d.*
    FROM dept d
    JOIN dept_tree t
    ON d.parent_id=t.id
)
SELECT *
FROM dept_tree;

生产：

菜单树。

30. Explain分析

EXPLAIN ANALYZE
SELECT ...

重点看：

type
rows
filtered
Extra

尤其：

Using filesort
Using temporary

Java后端生产中最有价值的SQL技巧排名

排名	技巧	使用频率
1	防超卖 UPDATE ... stock>0	★★★★★
2	ON DUPLICATE KEY UPDATE	★★★★★
3	FOR UPDATE	★★★★★
4	EXPLAIN 分析SQL	★★★★★
5	覆盖索引	★★★★★
6	Seek分页	★★★★★
7	窗口函数 TopN	★★★★
8	SKIP LOCKED	★★★★
9	批量更新 CASE WHEN	★★★★
10	递归CTE	★★★

如果你是 3~5 年 Java 工程师，真正能把这些 SQL 场景全部熟练掌握，并理解其背后的 索引、锁、MVCC、执行计划，基本已经达到大厂高级工程师面试的 SQL 水平了。

日思日睿

mysql进阶四