练习SQL语句（约束与事务）电商后台管理系统核心表设计（基于SpringBoot+MySQL）

为购物车表（ShoppingCart）添加主键约束，要求包含用户ID和商品ID的组合主键。：用户下单时，减少商品库存并生成订单记录，要求使用事务确保两个操作同时成功或失败。，当父表（如商品表）记录被删除时，子表（如 SKU 表）关联记录会自动删除。：设置外键约束，当用户表中的用户被删除时，自动删除其关联的订单记录。：模拟用户余额转账，A向B转账100元，要求事务保证原子性。：商品列表页的联合查询（

m0_75236543

1818人浏览 · 2025-03-05 10:38:38

m0_75236543 · 2025-03-05 10:38:38 发布

一、电商后台管理系统核心表设计（基于SpringBoot+MySQL）

以下是电商系统的核心表设计，结合业务需求与性能优化原则：

1. 用户表（user）

字段名	类型	约束与说明	示例数据
user_id	BIGINT	主键，自增	100001
username	VARCHAR(50)	唯一索引，用户名唯一性约束 6	"john_doe"
password	VARCHAR(100)	加密存储（BCrypt）	"2a10$..."
role	VARCHAR(20)	检查约束（'user'/'admin'/'super_admin'） 1	"user"
phone	VARCHAR(20)	唯一索引	"13800138000"
created_at	DATETIME	默认当前时间	2025-03-05 10:00:00

2. 商品表（product）

字段名	类型	约束与说明	示例数据
product_id	BIGINT	主键，自增	200001
name	VARCHAR(100)	全文索引	"iPhone 15 Pro"
category_id	BIGINT	外键约束（关联分类表）	300001
price	DECIMAL(10,2)	检查约束（>0）	8999.00
status	TINYINT	检查约束（0:下架/1:上架）	1

3. 商品SKU表（product_sku）

字段名	类型	约束与说明	示例数据
sku_id	BIGINT	主键，自增	400001
product_id	BIGINT	外键约束（关联商品表）	200001
color	VARCHAR(20)		"深空灰"
storage	VARCHAR(20)		"256GB"
stock	INT	检查约束（>=0）	100

4. 订单表（order）

字段名	类型	约束与说明	示例数据
order_id	VARCHAR(50)	主键（业务主键，如"20250305123456"）	"20250305123456"
user_id	BIGINT	外键约束（关联用户表）	100001
total_amount	DECIMAL(10,2)	检查约束（>=0）	17998.00
status	VARCHAR(20)	检查约束（待付款/待发货/已完成/已取消）	"待发货"
created_at	DATETIME	默认当前时间	2025-03-05 10:05:00

5. 订单详情表（order_detail）

字段名	类型	约束与说明	示例数据
detail_id	BIGINT	主键，自增	500001
order_id	VARCHAR(50)	外键约束（关联订单表）	"20250305123456"
sku_id	BIGINT	外键约束（关联SKU表）	400001
quantity	INT	检查约束（>0）	2
price	DECIMAL(10,2)	冗余字段（下单时价格）	8999.00

二、约束设计题目（10题）

主键约束：如何确保user.user_id的唯一性？
考察点：自增主键与唯一索引的区别。

答案: 主键约束（PRIMARY KEY）是确保字段唯一性的核心机制，它强制字段值唯一且非空。通过结合自增属性（AUTO_INCREMENT），主键可自动生成唯一递增的整数值。

与唯一索引的区别：

主键约束每个表只能有一个，且字段不允许 NULL；唯一索引（UNIQUE）可以有多个，允许一个 NULL 值

主键自动创建聚集索引（决定数据物理存储顺序），而唯一索引是非聚集索引

外键约束：若删除商品表中的某条记录，如何级联删除相关SKU记录？

在定义外键时添加 ON DELETE CASCADE，当父表（如商品表）记录被删除时，子表（如 SKU 表）关联记录会自动删除。
示例：
CREATE TABLE sku (
    sku_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE
);
考察点：外键级联操作（ON DELETE CASCADE）

检查约束：如何限制order.status字段只能为预设状态？

答案：
ENUM 类型：直接限定字段取值范围。
CREATE TABLE orders (
    status ENUM('pending', 'paid', 'shipped') DEFAULT 'pending'
);
触发器：通过 BEFORE INSERT/UPDATE 触发器验证值合法性。
考察点：ENUM 简单高效，但修改状态需修改表结构；触发器更灵活但维护成本高

唯一约束：如何防止用户注册重复的手机号？

答案：
在手机号字段添加 UNIQUE 约束：
ALTER TABLE user ADD UNIQUE (phone);
联合唯一索引：若需组合字段唯一（如区域码+手机号）：
ALTER TABLE user ADD UNIQUE (area_code, phone);
考察点：需预先清理历史重复数据，避免唯一约束报错

默认值约束：如何自动填充订单的创建时间？

答案：
使用 DEFAULT CURRENT_TIMESTAMP：
CREATE TABLE orders (
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
扩展：若需同时自动更新修改时间，可添加 ON UPDATE CURRENT_TIMESTAMP

非空约束：为何product.price必须为非空？

答案：
非空约束（NOT NULL）确保字段值有效，避免业务逻辑错误（如价格计算异常）。
考察点：需结合业务规则设计，例如价格若允许为空需明确默认处理逻辑

索引优化：为商品名称添加全文索引是否合理？

答案：

合理场景：商品名称需模糊查询（如 LIKE '%关键词%'）且文本较长时，全文索引（FULLTEXT）比普通索引更高效。

替代方案：高并发场景建议使用 Elasticsearch 等搜索引擎

字段长度限制：username字段长度设为50的依据是什么？

答案：

业务需求：参考行业标准（如多数平台用户名限制 3-20 字符），预留冗余。

存储优化：VARCHAR(50) 平衡存储空间与扩展性，避免过长浪费或过短截断

数据类型选择：为何订单总金额使用DECIMAL而非FLOAT？

答案：
DECIMAL 提供精确小数计算（如 DECIMAL(10,2) 表示最大 99999999.99），避免 FLOAT/DOUBLE 的浮点精度丢失问题（如 0.1+0.2≠0.3）

级联更新：修改用户ID时如何同步更新订单表？

答案：
外键定义时添加 ON UPDATE CASCADE：
ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE;
注意：频繁更新主键可能影响性能，建议使用不可变代理主键（如 UUID）

三、事务设计题目（10题）

库存扣减：用户下单时，如何保证库存减少与订单创建的事务原子性？
考察点：事务的ACID特性与悲观锁
支付超时：若支付超时，如何回滚订单状态并释放库存？
考察点：定时任务与事务补偿机制
并发修改：两个管理员同时修改同一商品价格，如何避免数据覆盖？
考察点：乐观锁（版本号机制）
批量操作：如何用事务保证批量导入商品数据的完整性？
考察点：批量插入与事务回滚
分布式事务：跨服务调用（如订单服务与库存服务）时如何保证一致性？
考察点：Seata框架或最终一致性方案
死锁处理：多用户同时抢购同一商品导致死锁，如何排查与解决？
考察点：死锁检测与超时重试
日志记录：如何在事务中记录操作日志且不影响主业务？
考察点：异步日志与事务传播机制
数据一致性：订单状态与物流状态不一致时，如何修复？
考察点：事务补偿与对账机制
嵌套事务：退款操作涉及订单状态更新与支付回调，如何设计嵌套事务？
考察点：PROPAGATION_NESTED的使用
事务隔离级别：为何订单查询需使用READ COMMITTED隔离级别？
考察点：脏读与性能平衡

四、综合考察题目（10题）

分库分表：当订单表数据量过亿时，如何设计分片策略？
参考方案：按用户ID哈希分片
缓存穿透：高频查询不存在的商品ID，如何防止击穿数据库？
参考方案：布隆过滤器+空值缓存
SQL优化：商品列表页的联合查询（商品表+SKU表）缓慢，如何优化？
参考方案：冗余字段或ES搜索引擎
数据归档：历史订单数据影响查询性能，如何设计归档策略？
参考方案：按时间分区表+冷热分离
安全设计：如何防止SQL注入攻击？
参考方案：预编译语句与MyBatis参数化查询
高并发场景：秒杀活动中如何避免超卖？
参考方案：Redis分布式锁+库存预扣减
数据迁移：从MySQL迁移至TiDB时如何保证业务不停机？
参考方案：双写+增量同步
监控告警：如何实时监控数据库慢查询？
参考方案：Prometheus+慢日志分析
备份恢复：误删商品表后如何快速恢复？
参考方案：全量备份+Binlog增量恢复
权限控制：如何实现不同角色管理员的数据访问隔离？
参考方案：行级权限控制（如MyBatis拦截器）

以下是针对电商数据库的SQL练习设计，包含建表语句、数据插入示例，以及约束、事务和混合题目的题目集：

二、数据库表结构设计及数据插入

1. 用户表（Users）

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50) NOT NULL UNIQUE,
    Password VARCHAR(255) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO Users (Username, Password, Email) VALUES
('user1', 'hashed_pwd1', 'user1@example.com'),
('user2', 'hashed_pwd2', 'user2@example.com');

2. 商品表（Products）

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0),
    Stock INT DEFAULT 0,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO Products (Name, Price, Stock) VALUES
('Laptop', 999.99, 50),
('Phone', 699.99, 100);

3. 订单表（Orders）

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    TotalAmount DECIMAL(10,2) CHECK (TotalAmount > 0),
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

-- 插入数据
INSERT INTO Orders (UserID, TotalAmount) VALUES
(1, 999.99),
(2, 1399.98);

4. 订单详情表（OrderDetails）

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT CHECK (Quantity > 0),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 插入数据
INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES
(1, 1, 1),
(2, 1, 1),
(2, 2, 1);

二、约束相关题目（10题）

主键约束
题目：为购物车表（ShoppingCart）添加主键约束，要求包含用户ID和商品ID的组合主键。
答案：
```
ALTER TABLE ShoppingCart ADD PRIMARY KEY (UserID, ProductID);
```
外键约束
题目：在订单表中添加外键约束，确保 UserID 引用用户表的 UserID。
答案：
```
ALTER TABLE Orders ADD FOREIGN KEY (UserID) REFERENCES Users(UserID);
```
唯一约束
题目：为商品表的 Name 字段添加唯一约束，防止重复商品名。
答案：
```
ALTER TABLE Products ADD UNIQUE (Name);
```
非空约束
题目：修改用户表，要求 Email 字段不允许为空。
答案：
```
ALTER TABLE Users MODIFY Email VARCHAR(100) NOT NULL;
```
检查约束
题目：为订单详情表的 Quantity 字段添加检查约束，确保购买数量大于0。
答案：
```
ALTER TABLE OrderDetails ADD CHECK (Quantity > 0);
```
默认值约束
题目：为订单表的 OrderDate 字段设置默认值为当前时间。
答案：
```
ALTER TABLE Orders MODIFY OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
```
复合约束
题目：在购物车表中，用户ID和商品ID的组合不允许重复。
答案：
```
ALTER TABLE ShoppingCart ADD UNIQUE (UserID, ProductID);
```
删除约束
题目：移除商品表的 Name 字段的唯一约束。
答案：
```
ALTER TABLE Products DROP INDEX Name;
```
索引优化
题目：为商品表的 Price 字段添加索引以优化查询速度。
答案：l
```
CREATE INDEX idx_price ON Products(Price);
```
级联操作
题目：设置外键约束，当用户表中的用户被删除时，自动删除其关联的订单记录。
答案：
```
ALTER TABLE Orders ADD FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE;
```

三、事务相关题目（10题）

基本事务
题目：用户下单时，减少商品库存并生成订单记录，要求使用事务确保两个操作同时成功或失败。
答案：
```
START TRANSACTION;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 1;
INSERT INTO Orders (UserID, TotalAmount) VALUES (1, 999.99);
COMMIT;
```

回滚机制
题目：若库存不足时取消订单，回滚事务。
答案：

START TRANSACTION;
UPDATE Products SET Stock = Stock - 5 WHERE ProductID = 1;
IF (SELECT Stock FROM Products WHERE ProductID = 1) < 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

隔离级别
题目：设置事务隔离级别为 READ COMMITTED，避免脏读。
答案：
```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
保存点
题目：在事务中设置保存点，允许部分回滚。
答案：
```
START TRANSACTION;
SAVEPOINT sp1;
-- 执行操作
ROLLBACK TO sp1;
COMMIT;
```

转账场景
题目：模拟用户余额转账，A向B转账100元，要求事务保证原子性。
答案：

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE UserID = 'A';
UPDATE Accounts SET Balance = Balance + 100 WHERE UserID = 'B';
COMMIT;

批量插入
题目：使用事务批量插入100条测试数据到商品表。
答案：
```
START TRANSACTION;
-- 循环插入语句
COMMIT;
```

死锁处理
题目：如何检测并解决事务死锁？
答案：

SHOW ENGINE INNODB STATUS; -- 查看死锁日志
-- 调整事务顺序或重试机制

嵌套事务
题目：MySQL是否支持嵌套事务？若否，如何模拟？
答案：

-- MySQL不支持，可通过保存点模拟
SAVEPOINT sp1;
-- 子事务操作
RELEASE SAVEPOINT sp1;

并发控制
题目：使用悲观锁（SELECT FOR UPDATE）防止库存超卖。
答案：

START TRANSACTION;
SELECT Stock FROM Products WHERE ProductID = 1 FOR UPDATE;
-- 更新库存
COMMIT;

分布式事务
题目：跨数据库的订单和库存更新如何保证一致性？
答案：

-- 使用XA事务协议
XA START 'order_transaction';
-- 跨库操作
XA END 'order_transaction';
XA COMMIT 'order_transaction';

四、混合题目（10题）

约束与事务结合
题目：在事务中插入订单时，若外键引用的用户不存在，如何处理？
答案：

START TRANSACTION;
-- 插入前检查用户是否存在
IF (SELECT UserID FROM Users WHERE UserID = 3) IS NULL THEN
    ROLLBACK;
ELSE
    INSERT INTO Orders (UserID, TotalAmount) VALUES (3, 500);
    COMMIT;
END IF;

唯一索引冲突
题目：并发事务中插入相同用户名导致唯一约束冲突，如何避免？
答案：

-- 使用事务和行级锁
START TRANSACTION;
SELECT * FROM Users WHERE Username = 'user3' FOR UPDATE;
-- 插入操作
COMMIT;

检查约束与事务回滚
题目：若订单金额为负数，事务如何回滚？
答案：

START TRANSACTION;
INSERT INTO Orders (UserID, TotalAmount) VALUES (1, -100);
-- 触发检查约束，自动回滚

级联删除与事务
题目：删除用户时级联删除其所有订单，用事务保证完整性。
答案：
```
START TRANSACTION;
DELETE FROM Users WHERE UserID = 1;
-- 外键级联删除订单
COMMIT;
```
批量更新与事务
题目：将商品价格统一上涨10%，要求事务回滚可撤销。
答案：
```
START TRANSACTION;
UPDATE Products SET Price = Price * 1.1;
-- 若需要撤销
ROLLBACK;
```
索引优化与查询
题目：查询价格大于500的商品，如何利用索引优化？
答案：
```
CREATE INDEX idx_price ON Products(Price);
SELECT * FROM Products WHERE Price > 500;
```
事务隔离与脏读
题目：在 READ UNCOMMITTED 隔离级别下，如何避免脏读？
答案：
```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
复合主键与插入冲突
题目：插入重复的购物车记录（相同用户和商品），如何处理？
答案：
```
-- 使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
INSERT IGNORE INTO ShoppingCart (UserID, ProductID) VALUES (1, 1);
```
事务与日志记录
题目：在事务中记录操作日志，确保日志与数据操作一致。
答案：
```
START TRANSACTION;
INSERT INTO Orders (...) VALUES (...);
INSERT INTO Logs (Action) VALUES ('Order created');
COMMIT;
```

高并发场景
题目：100个用户同时抢购10件商品，如何避免超卖？
答案：

-- 使用事务和行级锁
START TRANSACTION;
SELECT Stock FROM Products WHERE ProductID = 1 FOR UPDATE;
IF Stock >= 1 THEN
    UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 1;
    COMMIT;
ELSE
    ROLLBACK;
END IF;

五.事务设计题目（10）答案：

一、库存扣减的事务原子性保证

解决方案：

数据库事务 + 悲观锁
在事务中使用 SELECT ... FOR UPDATE 锁定库存记录，确保查询与扣减操作的原子性。例如：
```
BEGIN;
SELECT stock FROM product WHERE id = 1001 FOR UPDATE;  -- 锁定行
UPDATE product SET stock = stock - 1 WHERE id = 1001;
COMMIT;
```
此方式通过行级锁防止其他事务同时修改库存，保障原子性
Redis + Lua脚本
利用 Redis 单线程特性，通过 Lua 脚本实现原子操作：
```
local stock = redis.call('GET', KEYS[1])
if tonumber(stock) >= tonumber(ARGV[1]) then
    redis.call('DECRBY', KEYS[1], ARGV[1])
    return 1
end
return 0
```
脚本执行时，Redis 保证原子性且无上下文切换开销

二、支付超时回滚与库存释放

解决方案：

定时任务扫描超时订单
使用分布式定时任务（如 ElasticJob）定期扫描未支付订单，触发回滚逻辑：

@Scheduled(cron = "0 */5 * * * ?")  // 每5分钟扫描
public void rollbackTimeoutOrders() {
    List<Order> orders = orderService.findUnpaidOrders();
    orders.forEach(order -> {
        orderService.cancelOrder(order.getId());
        inventoryService.releaseStock(order.getProductId());
    });
}

事务补偿机制
记录事务日志，通过补偿任务恢复状态。例如：
- 订单表增加 status 字段标识“待支付”/“已取消”；
- 支付失败时，触发补偿接口更新状态并释放库存

三、并发修改价格的数据覆盖问题

解决方案：乐观锁（版本号机制）

数据库表中增加 version 字段，更新时校验版本：
```
UPDATE product SET price = 200, version = version + 1 
WHERE id = 1001 AND version = 1;  -- 版本匹配才更新
```
若影响行数为0，表示数据已被修改，需重试或提示用户

CAS（Compare and Swap）
结合业务层重试策略，例如

int retryCount = 0;
while (retryCount < 3) {
    Product product = productDao.getById(1001);
    boolean success = productDao.updatePrice(product.getId(), newPrice, product.getVersion());
    if (success) break;
    retryCount++;
}

四、批量导入商品数据的事务完整性

解决方案：

批量插入事务控制

@Transactional(rollbackFor = Exception.class)
public void batchImport(List<Product> products) {
    jdbcTemplate.batchUpdate("INSERT INTO product (...) VALUES (?,?,?)", 
        products, 
        100,  // 批量提交间隔
        (ps, product) -> { ps.setString(1, product.getName()); ... });
}

单事务包裹所有插入操作，失败时整体回滚

分批次提交
若数据量过大，分批次提交并记录断点：

for (int i = 0; i < total; i += BATCH_SIZE) {
    List<Product> batch = products.subList(i, i + BATCH_SIZE);
    productService.batchImport(batch);  // 每批次独立事务
}

五、分布式事务一致性保障

解决方案：

Seata AT模式
- 全局事务协调：通过 Seata 的 TC（事务协调器）管理全局事务状态。
- 分支事务注册：订单服务和库存服务分别注册分支事务，由 TC 协调提交或回
```
@GlobalTransactional
public void createOrder(OrderRequest request) {
    orderService.create(request);  // 本地事务
    inventoryService.deductStock(request.getProductId());  // 远程服务
}
```
最终一致性（消息队列）
- 订单创建后发送消息到 MQ；
- 库存服务消费消息扣减库存，失败时重试或人工干预。

六、死锁排查与解决

解决方案：

死锁检测与日志分析
- MySQL：执行 SHOW ENGINE INNODB STATUS 查看死锁日志；
- 调整锁顺序：统一按固定顺序获取锁（如先锁商品 A，再锁商品 B）

超时重试

SET innodb_lock_wait_timeout = 5;  -- 锁等待超时5秒

代码层捕获超时异常并重试。

七、异步日志记录与事务传播

解决方案：

异步线程池 + 事务传播

@Async
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void logOperation(LogEntry log) {
    logRepository.save(log);  // 独立事务，不影响主业务
}

使用 @Async 异步执行，REQUIRES_NEW 保证日志事务独立提交

消息队列解耦
将日志发送到 Kafka/RocketMQ，由消费者异步写入数据库。

八、订单与物流状态一致性修复

解决方案：

定时对账任务
对比订单与物流系统的状态，发现不一致时触发补偿：

SELECT order_id FROM orders 
WHERE status = 'SHIPPED' 
AND NOT EXISTS (SELECT 1 FROM logistics WHERE order_id = orders.id AND status = 'DELIVERED');

人工补偿界面
提供后台界面手动修正状态，并记录操作日志

九、嵌套事务设计（退款场景）

解决方案：

PROPAGATION_NESTED

@Transactional
public void refund(String orderId) {
    orderService.updateStatus(orderId, "REFUNDING");  // 主事务
    try {
        paymentService.refund(orderId);  // 嵌套事务，独立回滚
    } catch (Exception e) {
        // 嵌套事务回滚，主事务继续
    }
}

嵌套事务回滚不影响外层事务，适用于部分操作可独立失败的场景

十、订单查询的隔离级别选择

选择原因：

避免脏读：READ COMMITTED 保证读取已提交数据，不会读到未提交的中间状态。
性能平衡：相比 REPEATABLE READ，减少锁竞争和 MVCC 版本链维护开销，适合高并发查询场景

六.混合题目（10题）答案：

1. 分库分表：当订单表数据量过亿时，如何设计分片策略？

参考方案：按用户ID哈希分片

策略设计：
- 采用 哈希分片，以用户ID作为分片键，将订单数据均匀分布到多个库/表中，避免单节点负载过高
- 针对多维度查询（如买家、卖家、订单号），可结合 RANGE_HASH算法，例如订单号后N位与用户ID后N位一致，同时支持订单号和用户维度的查询
- 对于卖家维度查询，可 冗余分片，异步生成卖家维度的分表数据
- 分片数计算：根据未来2-3年数据量预估，单表控制在100万-5000万条

2. 缓存穿透：高频查询不存在的商品ID，如何防止击穿数据库？

参考方案：布隆过滤器+空值缓存

布隆过滤器：在缓存层前拦截非法请求，快速判断商品ID是否存在
空值缓存：查询不存在的商品时，缓存空值并设置短过期时间（如5分钟），减少重复穿透
补充措施：对参数合法性校验（如ID格式），拦截明显恶意请求

3. SQL优化：商品列表页的联合查询（商品表+SKU表）缓慢，如何优化？

参考方案：冗余字段或ES搜索引擎

冗余字段：在商品表中冗余高频查询的SKU字段（如价格、库存），避免联表查询
ES搜索引擎：将商品与SKU数据同步到Elasticsearch，利用倒排索引加速复杂查询
索引优化：为商品表与SKU表的关联字段（如商品ID）添加联合索引，并优先使用覆盖索引减少回表。

4. 数据归档：历史订单数据影响查询性能，如何设计归档策略？

参考方案：按时间分区表+冷热分离

分区表：按订单创建时间按月/年分区，结合分区裁剪（Partition Pruning）提升查询效率
冷热分离：将1年以上的订单迁移到历史表（如order_history），主表仅保留近期数据
异步迁移：通过定时任务（如SQL Server Agent）分批迁移数据，避免锁表影响在线业务

5. 安全设计：如何防止SQL注入攻击

参考方案：预编译语句与MyBatis参数化查询

参数化查询：使用MyBatis的#{}占位符，避免拼接SQL语句
动态SQL规范：优先使用<if>标签而非${}拼接条件，防止恶意输入篡改逻辑
输入校验：对用户输入进行白名单过滤（如数字ID校验）

6. 高并发场景：秒杀活动中如何避免超卖？

参考方案：Redis分布式锁+库存预扣减

库存预扣减：在Redis中缓存商品库存，扣减时通过原子操作（如DECR）保证一致性
分布式锁：使用Redisson或Lua脚本实现锁机制，确保单线程处理库存扣减
异步落库：扣减成功后通过消息队列（如Kafka）异步更新数据库，降低主库压力

7. 数据迁移：从MySQL迁移至TiDB时如何保证业务不停机？

参考方案：双写+增量同步

双写过渡：业务层同时写入MySQL和TiDB，逐步切换读请求到TiDB
增量同步：使用工具（如NineData）实时同步MySQL的Binlog到TiDB，保障数据一致性
验证切换：数据同步完成后，对比数据一致性并短暂停机切换流量

8. 监控告警：如何实时监控数据库慢查询？

参考方案：Prometheus+慢日志分析

慢日志采集：启用MySQL慢查询日志，设置阈值（如2秒）
Prometheus配置：通过Exporter收集慢查询次数、耗时等指标，结合Grafana可视化
告警规则：设置阈值触发告警（如每分钟超过10次慢查询），通知到钉钉/邮件

9. 备份恢复：误删商品表后如何快速恢复？

参考方案：全量备份+Binlog增量恢复

全量备份：每日定时备份全库（如mysqldump），保存到远程存储。
Binlog恢复：根据误删时间点，回放Binlog到备份文件，恢复至误删前状态
验证流程：恢复后通过数据对比工具校验完整性

10. 权限控制：如何实现不同角色管理员的数据访问隔离？

参考方案：行级权限控制（如MyBatis拦截器）

动态SQL改写：在MyBatis拦截器中根据角色ID自动附加条件（如WHERE department_id=#{deptId}）
数据库视图：为不同角色创建视图，限制数据可见范围
字段脱敏：对敏感字段（如手机号）在查询结果中动态掩码

中德AI开发者社区

2万人民币佣金等你来拿，中德社区发起者X.Lab，联合德国优秀企业对接开发项目，领取项目得佣金！！！

更多推荐

模糊神经网络的基础原理剖析(附DeepSeek行业解决方案100+)

中德AI开发者社区

X.Lab的AI报告丨巴黎上演AI巅峰对话！GOSIM大会首日狂掀技术风暴！

中德AI开发者社区

国际AI工程师齐聚！携手X.Lab，共筑中德AI合作新桥梁！

中德AI开发者社区

所有评论(0)

查看更多评论

m0_75236543

@m0_75236543

已为社区贡献1条内容

练习SQL语句（约束与事务）电商后台管理系统核心表设计（基于SpringBoot+MySQL）

m0_75236543

一、电商后台管理系统核心表设计（基于SpringBoot+MySQL）

1. ​用户表（user）​

2. ​商品表（product）​

3. ​商品SKU表（product_sku）​

4. ​订单表（order）​

5. ​订单详情表（order_detail）​

二、约束设计题目（10题）

三、事务设计题目（10题）

四、综合考察题目（10题）

​二、数据库表结构设计及数据插入

1. 用户表（Users）

2. 商品表（Products）

3. 订单表（Orders）

4. 订单详情表（OrderDetails）

​二、约束相关题目（10题）​

​三、事务相关题目（10题）​

​四、混合题目（10题）​

五.事务设计题目（10）答案：

一、库存扣减的事务原子性保证

二、支付超时回滚与库存释放

三、并发修改价格的数据覆盖问题

四、批量导入商品数据的事务完整性

五、分布式事务一致性保障

六、死锁排查与解决

七、异步日志记录与事务传播

八、订单与物流状态一致性修复

九、嵌套事务设计（退款场景）

十、订单查询的隔离级别选择

六.混合题目（10题）答案：

1. ​分库分表：当订单表数据量过亿时，如何设计分片策略？

2. ​缓存穿透：高频查询不存在的商品ID，如何防止击穿数据库？

3. ​SQL优化：商品列表页的联合查询（商品表+SKU表）缓慢，如何优化？

4. ​数据归档：历史订单数据影响查询性能，如何设计归档策略？

5. ​安全设计：如何防止SQL注入攻击

6. ​高并发场景：秒杀活动中如何避免超卖？

7. ​数据迁移：从MySQL迁移至TiDB时如何保证业务不停机？

8. ​监控告警：如何实时监控数据库慢查询？

9. ​备份恢复：误删商品表后如何快速恢复？

10. ​权限控制：如何实现不同角色管理员的数据访问隔离？

所有评论(0)

温馨提示：您尚未绑定手机号

m0_75236543

1. 用户表（user）

2. 商品表（product）

3. 商品SKU表（product_sku）

4. 订单表（order）

5. 订单详情表（order_detail）

二、数据库表结构设计及数据插入

二、约束相关题目（10题）

三、事务相关题目（10题）

四、混合题目（10题）

1. 分库分表：当订单表数据量过亿时，如何设计分片策略？

2. 缓存穿透：高频查询不存在的商品ID，如何防止击穿数据库？

3. SQL优化：商品列表页的联合查询（商品表+SKU表）缓慢，如何优化？

4. 数据归档：历史订单数据影响查询性能，如何设计归档策略？

5. 安全设计：如何防止SQL注入攻击

6. 高并发场景：秒杀活动中如何避免超卖？

7. 数据迁移：从MySQL迁移至TiDB时如何保证业务不停机？

8. 监控告警：如何实时监控数据库慢查询？

9. 备份恢复：误删商品表后如何快速恢复？

10. 权限控制：如何实现不同角色管理员的数据访问隔离？