MySQL死锁排查 | Java后端面试知识库

一句话答案

SHOW ENGINE INNODB STATUS 查看死锁日志，分析两个事务的加锁顺序，保持一致的资源访问顺序预防死锁。

核心要点

MySQL 8.0 引入了多项现代化特性，面试中最常考察的有以下几个：

1. 窗口函数（Window Functions）

窗口函数可以在不减少行数的前提下，对每一行计算聚合/排名值。

常用窗口函数：

ROW_NUMBER()  → 连续唯一排名（1, 2, 3, 4）
RANK()        → 并列跳号排名（1, 1, 3, 4）
DENSE_RANK()  → 并列不跳号排名（1, 1, 2, 3）
LAG(col, n)   → 取当前行往前第 n 行的值（同比/环比）
LEAD(col, n)  → 取当前行往后第 n 行的值
SUM() OVER()  → 累计求和

语法示例：

sql

-- 按部门分组，对薪资降序排名
SELECT name, dept, salary,
       RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rk
FROM emp;

典型场景：

排名查询：不需要自连接或子查询，一条 SQL 搞定
同比环比：用 LAG(amount, 1) 取上一期数据直接计算
累计求和：SUM(amount) OVER(ORDER BY create_time) 按时间累加

2. CTE（Common Table Expressions，公用表表达式）

CTE 通过 WITH 子句定义临时结果集，支持递归查询。

语法示例：

sql

-- 递归查询：查询某员工的所有上级（组织架构树）
WITH RECURSIVE cte AS (
    SELECT id, name, manager_id FROM emp WHERE id = 100   -- 起始条件
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM emp e JOIN cte ON e.id = cte.manager_id           -- 递归条件
)
SELECT * FROM cte;

CTE 的优势：

比子查询更易读     → 先定义后使用，逻辑清晰
支持递归           → 树形结构查询（组织架构、分类层级遍历）
可被多次引用       → 同一个 CTE 可以在主查询中多次 SELECT，避免重复子查询

3. JSON 支持增强

MySQL 8.0 大幅增强了 JSON 数据类型的函数和索引支持。

常用 JSON 函数：

sql

-- 提取 JSON 字段
SELECT JSON_EXTRACT(config, '$.host') FROM app_config;
-- 简写语法
SELECT config->>'$.host' FROM app_config;

-- 判断 JSON 数组是否包含某值
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"electronics"');

-- 聚合为 JSON 数组
SELECT dept, JSON_ARRAYAGG(name) AS members FROM emp GROUP BY dept;

-- JSON 转关系表（JSON_TABLE，8.0.4+）
SELECT jt.name, jt.age
FROM user_data,
     JSON_TABLE(info, '$[*]' COLUMNS(
         name VARCHAR(50) PATH '$.name',
         age INT PATH '$.age'
     )) AS jt;

JSON 索引方案：

sql

-- 通过 generated column 对 JSON 字段建索引
ALTER TABLE app_config
    ADD COLUMN host VARCHAR(255) GENERATED ALWAYS AS (config->>'$.host') STORED,
    ADD INDEX idx_host (host);

适用场景： 半结构化数据、配置存储、动态扩展字段（避免频繁 ALTER TABLE）

4. 其他重要特性

不可见索引（INVISIBLE INDEX）
  → ALTER INDEX idx_name INVISIBLE;
  → 索引仍然维护但优化器不使用，用于灰度测试删除索引的影响
  → 确认无影响后再 DROP INDEX，安全可控

降序索引（Descending Index）
  → CREATE INDEX idx ON t(col1 ASC, col2 DESC);
  → 8.0 之前 DESC 被忽略，实际仍是升序；8.0 真正支持降序存储
  → 适合 ORDER BY col1 ASC, col2 DESC 的混合排序场景

原子 DDL
  → DDL 操作（CREATE TABLE、DROP TABLE 等）要么全部成功，要么全部回滚
  → 8.0 之前 DDL 非原子性，中途失败可能导致数据字典不一致

追问与易错

追问方向：

间隙锁导致的死锁怎么避免？
业务层怎么处理死锁？
INNODB STATUS 死锁信息怎么读？

易错点：

❌ 死锁只发生在并发写入——SELECT FOR UPDATE 也会
❌ 遇到死锁就加大超时——应分析根因

💡 记忆锚点

MySQL 死锁排查三步走：SHOW ENGINE INNODB STATUS 调出"监控录像"，看两个事务各持什么锁、各等什么锁，找到交叉点。预防就是让所有人过门都按同一个顺序——统一资源访问顺序，别你先锁 A 再锁 B、他先锁 B 再锁 A。业务层要 catch 死锁异常并重试。

一句话答案 ​

一句话答案