MySQL 大厂面试真题:悲观锁 vs 乐观锁 + 死锁分析 + 间隙锁,高并发面试必考

📢 一句话导读：本期聚焦 MySQL 分区表，通过订单数据归档、历史数据清理、分区裁剪三个真实场景，搞懂 RANGE/LIST/HASH 分区的使用！

🎯 本期核心看点

✅ RANGE 分区 — 按月分区的设计与查询优化✅ LIST 分区 — 按业务类型分区的使用场景✅ HASH 分区 — 数据均匀分布的应用✅ 分区裁剪 — 查询如何利用分区提升性能

📖 上期答案公布（第 10 期）

📌 题目回顾

第10期围绕 MySQL锁机制 展开，有四个分析任务：

1. 锁机制选择：悲观锁 vs 乐观锁解决库存超卖

2. 锁范围分析：行锁、表锁、间隙锁的锁定范围

3. 死锁分析：死锁发生的时序与预防

4. 隔离级别影响：RC vs RR 对锁的影响

任务一：锁机制选择

✅ 标准答案

悲观锁方案（SELECT ... FOR UPDATE）：

-- 事务A（完整流程）

START TRANSACTION;

-- 1. 加行锁，锁定 product_id=1 的记录

SELECT stock FROM product_stock WHERE product_id = 1 FOR UPDATE;

-- 返回：100

-- 2. 应用层判断库存是否充足（代码逻辑）

-- if (stock >= 1) { 继续 } else { 回滚 }

-- 3. 扣减库存

UPDATE product_stock SET stock = stock - 1 WHERE product_id = 1;

-- 4. 插入订单

INSERT INTO orders (product_id, quantity, order_time) VALUES (1, 1, NOW());

-- 5. 提交事务，释放锁

COMMIT;

-- 1. 先查询当前版本号（应用层）

SELECT stock, version FROM product_stock WHERE product_id = 1;

-- 返回：stock=100, version=5

-- 2. 扣减时检查版本号

UPDATE product_stock

SET stock = stock - 1,

    version = version + 1

WHERE product_id = 1

  AND version = 5          -- 版本号必须和刚才查到的一致

  AND stock >= 1;          -- 防超卖

-- 3. 检查 affected_rows

-- 如果 = 1 → 扣减成功 ✅

-- 如果 = 0 → 版本号已变化（被其他事务修改过），重试或报错 ❌

任务二：锁范围分析

✅ 标准答案

什么是间隙锁（Gap Lock）？

间隙锁是 InnoDB 在 REPEATABLE-READ 隔离级别下，为了防止幻读而加的锁。

示例： 假设表中已有 stock=5, 12, 18, 25

SELECT * FROM product_stock WHERE stock BETWEEN 10 AND 20 FOR UPDATE;

锁定范围：

• ✅ stock=12, 18 的记录（行锁）

• ✅ 10-12 之间的间隙（间隙锁）

• ✅ 12-18 之间的间隙（间隙锁）

• ✅ 18-20 之间的间隙（间隙锁）

间隙锁的作用： 防止其他事务在锁定间隙中插入新记录，避免幻读。

任务三：死锁分析

✅ 标准答案

是否会发生死锁：会 ✅

死锁时序图：

避免死锁的方法：

任务四：隔离级别对锁的影响

✅ 标准答案

1. 锁范围对比：

2. 哪种更容易发生间隙锁？

• REPEATABLE-READ 更容易发生间隙锁（这是该隔离级别的默认行为）

3. 高并发秒杀场景应该选择哪种隔离级别？

推荐：READ-COMMITTED

选择 RC 的原因：

1. 间隙锁会严重降低并发性能

2. 秒杀场景主要防止超卖（行锁已足够），不需要防止幻读

3. 业务层面可以通过 stock >= quantity 条件来保证数据一致性

4. READ-COMMITTED 下 UPDATE 操作的 WHERE 条件同样会加行锁，足以防超卖

🆕 第 11 期新题挑战

📊 难度：⭐⭐⭐⭐（进阶实战级）🔥 适合：处理大数据量（千万级以上）的MySQL开发者

📌 题目描述

某电商平台订单表 orders 已经 5000万行，查询越来越慢，DBA建议 分区表。

当前订单表结构：

CREATE TABLE orders (

    order_id INT PRIMARY KEY AUTO_INCREMENT,

    order_date DATETIME NOT NULL,

    customer_id INT NOT NULL,

    product_id INT NOT NULL,

    quantity INT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    status VARCHAR(20) NOT NULL,

    pay_time DATETIME,

    ship_time DATETIME,

    INDEX idx_customer (customer_id),

    INDEX idx_date (order_date)

) ENGINE=InnoDB;

典型查询场景：

1. 按月统计销售报表：WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'

2. 查询某客户的订单：WHERE customer_id = 10001

3. 清理3年前的历史订单：DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 10000

🔧 分析任务（三问）

任务一：分区方案设计

1. 推荐使用哪种分区类型？为什么？

2. 写出完整的 按月分区 建表语句，从 2024年1月 到 2026年12月

3. 新增分区（如2027年1月）的SQL语句怎么写？

4. 删除旧分区（如2023年12月）的SQL语句怎么写？

5. 分区键选择 order_date 还是 order_id？为什么？

任务二：分区裁剪分析

给出以下三个查询的 执行计划分析：

1. 查询是否能利用分区裁剪？

2. 如果不能，如何优化？

-- 查询A

EXPLAIN SELECT * FROM orders 

WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

-- 查询B

EXPLAIN SELECT * FROM orders 

WHERE customer_id = 10001 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';

-- 查询C

EXPLAIN SELECT * FROM orders 

WHERE YEAR(order_date) = 2025;  -- 用函数包裹分区键！

任务三：数据清理优化

公司规定：只保留最近2年订单数据，2年以上的需要归档清理。

-- 当前清理方式（慢！）

DELETE FROM orders WHERE order_date < '2024-01-01' LIMIT 10000;

1. 使用分区表后，如何高效删除2023年全部数据？

2. 删除分区 vs DELETE 删除，性能差多少？

3. 如果要批量删除2024年1月-6月的数据（6个月），如何操作？

4. 归档数据（将旧分区数据迁移到另一个表）的最佳实践是什么？

🧪 测试数据

-- 模拟5000万行数据（示例插入）

-- 实际测试建议先在开发环境用较小数据集验证

-- 创建分区表（2024年1月 到 2026年12月）

CREATE TABLE orders_partitioned (

    order_id INT NOT NULL AUTO_INCREMENT,

    order_date DATETIME NOT NULL,

    customer_id INT NOT NULL,

    product_id INT NOT NULL,

    quantity INT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    status VARCHAR(20) NOT NULL,

    pay_time DATETIME,

    ship_time DATETIME,

    PRIMARY KEY (order_id, order_date)  -- ⚠️ 分区键必须是主键的一部分

)

PARTITION BY RANGE (TO_DAYS(order_date)) (

    PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),

    PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),

    -- ... 以此类推到 2026年12月

    PARTITION p2026_12 VALUES LESS THAN (TO_DAYS('2027-01-01'))

);

-- 插入测试数据

INSERT INTO orders_partitioned (order_date, customer_id, product_id, quantity, amount, status)

SELECT

    DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 1095) DAY) AS order_date,

    FLOOR(1 + RAND() * 10000) AS customer_id,

    FLOOR(1 + RAND() * 1000) AS product_id,

    FLOOR(1 + RAND() * 5) AS quantity,

    ROUND(50 + RAND() * 1000, 2) AS amount,

    CASE WHEN RAND() < 0.9 THEN 'completed' ELSE 'cancelled' END AS status,

    NOW() AS pay_time,

    NOW() AS ship_time

FROM

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) a,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) b,

    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 SELECT 5) c;

✍️ 评论区挑战

把你对分区表的理解写出来吧！ 👇

本期三问考察：

• 📊 分区方案设计（RANGE分区 + 按月分区）

• 🎯 分区裁剪分析（执行计划 + 优化）

• 🗑️ 数据清理优化（DROP PARTITION vs DELETE）

分区表是处理大数据量的必备技能，来实战吧！

📢 下期预告

第 11 期答案 + 详细解析 将在 第 12 期 公布！

下期会重点讲解分区表的底层原理、子分区技术、以及千万级大表的分区最佳实践。