设计航空货运管理系统中的“航班-货物-仓库”关联表，要求支持实时查询（如查询某航班当前货物状态），并保证数据一致性（如货物从仓库出库后，航班货物列表实时更新）。请说明数据模型、索引、事务处理方案。
答案
1) 【一句话结论】
采用航班表、货物表、仓库表的多表关联设计，通过外键约束（如ON DELETE RESTRICT）保证数据引用完整性，结合B树索引加速实时查询，并使用ACID事务（含乐观锁版本号）处理并发与边界场景（如航班取消回滚），确保货物出库后航班货物列表实时更新。
2) 【原理/概念讲解】
数据模型上，航班表存储航班信息（flight_id、flight_no、status）；货物表存储货物状态（cargo_id、cargo_info、current_status，如“在库”“在途”），通过flight_id关联航班表、warehouse_id关联仓库表，并添加version字段实现乐观锁；仓库表存储仓库信息。关联通过外键约束（cargo.flight_id FK flight.flight_id、cargo.warehouse_id FK warehouse.warehouse_id），并设置ON DELETE RESTRICT（避免删除航班时货物残留）。
为支持实时查询，在货物表的flight_id（查询特定航班货物）和current_status列建B树索引，支持范围查询（如按航班ID查询所有货物状态）。
事务处理上，出库操作需在事务中执行：更新货物状态为“在途”、关联航班ID，同时更新航班货物数量（事务内同步）；添加version字段实现乐观锁，检测并发冲突时重试事务；航班取消时，事务回滚货物状态至“待处理”，触发调度系统处理。类比：就像仓库管理员（仓库表）把货物（货物表）从仓库取出，放到航班（航班表）上，事务保证“取”和“放”原子操作，不会出现状态未更新或航班列表未同步的情况。
3) 【对比与适用场景】
| 方案 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 单表存储 | 所有信息（航班、货物、仓库）存一个表，用字段区分 | 数据冗余，查询需解析字段，效率低 | 小规模系统，数据量少 | 不支持实时查询，数据一致性难保证 |
| 多表关联 | 航班表、货物表、仓库表通过外键关联 | 数据结构清晰，查询通过JOIN，效率高 | 大规模系统，需实时查询 | 需外键约束，事务处理复杂 |
| 索引类型 | 适合场景 | 特性 | 适用情况 |
|---|---|---|---|
| B树索引 | 范围查询（如按flight_id查询所有货物） | 支持范围扫描，查询效率高 | 实时查询，如查询某航班货物状态 |
| 哈希索引 | 精确匹配查询（如按cargo_id查询） | 查询速度快，但无法支持范围查询 | 查询特定货物，不涉及范围 |
4) 【示例】
表结构：
- 航班表（
flight）：flight_id(PK),flight_no,status,cargo_count - 货物表（
cargo）：cargo_id(PK),cargo_info,current_status,flight_id(FK),warehouse_id(FK),version(INT, 自增) - 仓库表（
warehouse）：warehouse_id(PK),location
事务示例（SQL伪代码，含乐观锁）：
BEGIN TRANSACTION;
-- 检查货物状态和版本号（乐观锁）
UPDATE cargo
SET current_status = '在途', flight_id = 101, version = version + 1
WHERE cargo_id = 202 AND warehouse_id = 3 AND current_status = '在库' AND version = 5;
-- 更新航班货物数量
UPDATE flight SET cargo_count = cargo_count + 1 WHERE flight_id = 101;
COMMIT;
查询示例（SQL）：
SELECT * FROM cargo
WHERE flight_id = 101 AND current_status = '在途';
5) 【面试口播版答案】
面试官您好，针对航空货运管理系统的“航班-货物-仓库”关联，我设计的是多表结构：航班表、货物表、仓库表通过外键关联。货物表存储货物状态，关联航班ID和仓库ID。为支持实时查询，在货物表的flight_id（查询特定航班货物）和current_status列建B树索引；为保证数据一致性，所有出库操作通过ACID事务处理，同时更新货物状态为“在途”并关联航班，提交后航班货物列表实时更新。比如货物出库时，事务内同步状态和航班信息，确保查询结果准确。另外，添加version字段实现乐观锁，处理并发冲突，航班取消时触发事务回滚货物状态，避免数据不一致。
6) 【追问清单】
- 如何处理并发出库导致的数据冲突？
回答要点：使用乐观锁（version字段），检测并发时版本号是否变化，若冲突则重试事务。 - 如果航班取消，货物状态如何回滚？
回答要点：在航班表添加取消标志，当航班取消时，触发器或事务内更新货物表状态为“待处理”，并通知调度系统处理。 - 索引选择B树还是哈希？
回答要点：选择B树索引，因为支持范围查询（如按flight_id查询所有货物），而哈希索引不支持范围查询，无法满足实时查询需求。
7) 【常见坑/雷区】
- 忽略事务隔离级别导致脏读：若使用读未提交，可能导致查询到未提交的出库数据，影响准确性。
- 单表存储导致查询慢：若将所有信息存一个表，查询时需解析字段，效率低，无法支持实时查询。
- 外键约束不严格：若货物表的外键未设为外键或约束，可能导致数据不一致，如货物关联无效的航班或仓库。
- 索引选择错误：若用哈希索引支持范围查询，会导致查询失败，影响实时查询性能。
- 事务未提交导致数据不一致：若出库操作未提交事务，货物状态未更新，航班货物列表未同步，导致查询结果错误。