设计一个用户表（User）和订单表（Order）的数据库结构，考虑用户可能注册多个账号、订单与用户关联、订单状态（待支付、已支付、已发货等），请说明表结构、主键、外键、索引设计，以及如何处理高并发下的数据库性能问题（如索引选择、分库分表策略）。

信步科技产品难度：中等

答案

1) 【一句话结论】用户表以自增ID为主键，订单表通过user_id外键关联，订单状态用枚举表示，结合索引优化（user_id、order_status等）、分库分表（用户ID哈希分库+订单ID范围分表），以及乐观锁（version字段）和Redis缓存，有效应对高并发下的数据一致性与查询性能。

2) 【原理/概念讲解】主键是表内唯一标识记录的字段（如用户表的id自增），像身份证号，唯一区分每个用户；外键用于关联其他表（订单表的user_id关联用户表），保证订单归属用户；索引（如B树索引）加速查询（如订单表的user_id索引用于按用户查询订单，order_status索引用于按状态查询），像书的目录，快速定位内容；分库分表（水平扩展）将数据分散到多库/多表，解决单库容量与性能瓶颈（如按用户ID哈希分库，数据均匀分布，避免热点；按订单ID范围分表，便于按时间查询旧订单）；乐观锁通过版本号字段（version）解决并发冲突，更新时检查版本号是否一致，不一致则重试。

3) 【对比与适用场景】分库分表策略对比：

策略	定义	特性	使用场景	注意点
按用户ID哈希分库	根据用户ID哈希值分配到不同数据库实例	数据均匀分布，无热点，水平扩展	用户量极大（百万级以上），需高并发读写	需全局哈希服务，数据迁移复杂（需停机或复杂逻辑）
按订单ID范围分表	根据订单ID的数值范围分配到不同表（如按时间范围，每天分表）	便于按时间范围查询（如查询本月订单），数据增长时自动扩展	订单按时间线性增长，需按时间切分	需定期迁移旧表（如每月迁移旧订单到历史表），查询跨表数据需额外处理

4) 【示例】：用户表（User）：

CREATE TABLE user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
);

订单表（Order）：

CREATE TABLE order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    order_status ENUM('待支付', '已支付', '已发货', '已完成', '已取消') NOT NULL DEFAULT '待支付',
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    version INT NOT NULL DEFAULT 1,  -- 乐观锁版本号
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE NO ACTION  -- 用户删除后订单保留
);

Redis缓存示例：

用户信息缓存：user:info:{id}（存储用户名、邮箱等）
订单状态缓存：order:status:{order_id}（存储当前状态）

5) 【面试口播版答案】（约90秒）：
“面试官您好，我来设计用户表和订单表。首先，用户表以自增ID为主键，包含用户名、邮箱、密码哈希等字段，给username和email建索引，提升按用户名或邮箱查询的性能。订单表通过user_id作为外键关联用户表，订单号唯一，状态用枚举值表示（待支付、已支付等），给user_id和order_status字段建索引，加速按用户或状态查询订单。高并发下，考虑分库分表：按用户ID哈希分库，将不同用户的数据分散到多个数据库实例，避免单库压力；按订单ID范围分表（如每天分表），便于按时间查询旧订单。订单状态变更时，采用乐观锁机制，订单表增加version字段，更新时检查版本号是否一致，若不一致则重试，避免并发冲突。同时，用Redis缓存用户信息（如用户名、邮箱）和订单状态，减少数据库压力。”

6) 【追问清单】：

问：用户删除后订单是否保留？外键约束如何设置？
答：业务要求订单保留，外键约束设为ON DELETE NO ACTION，避免级联删除。
问：分库分表的具体实现方案？
答：使用ShardingSphere等工具，按用户ID哈希分库，按订单ID范围分表（如每天分表），并配置动态调整策略（如用户量增长时增加分库）。
问：乐观锁的重试策略？
答：设置重试次数（如3次）和超时时间（如1秒），超时则放弃或通知用户。
问：缓存更新机制？
答：订单状态变更时，先删除Redis缓存（或设置TTL），保证数据一致性。
问：索引是否覆盖所有高频查询？
答：高频查询字段（user_id、order_status）建索引，避免全表扫描；非查询字段（如password_hash）不建索引。

7) 【常见坑/雷区】：

外键默认SET NULL导致订单丢失，应按业务需求选择NO ACTION。
订单状态用字符串而非枚举，无法校验状态转换合法性。
分库分表后跨库查询复杂，需合理设计分片键，避免热点。
乐观锁重试无策略，可能导致死循环或超时。
忽略缓存，高并发下数据库压力仍大。