设计一个用户表（User），包含用户ID、用户名、邮箱、注册时间、最后登录时间等字段，请说明表结构设计、索引策略、事务处理场景（如用户注册、密码修改），以及如何应对用户量增长（如百万级用户）。

微软Product Manager Intern难度：中等

答案

1) 【一句话结论】用户表设计需兼顾数据一致性、查询效率与可扩展性，通过合理字段设计、多级索引策略及事务控制，并规划分库分表等方案应对百万级用户增长。

2) 【原理/概念讲解】
老师：咱们先讲表结构设计。用户表的核心字段要考虑“唯一性”和“查询需求”。比如用户ID，用UUID（全局唯一，避免自增ID冲突），作为主键；用户名和邮箱必须唯一，所以设唯一索引（防止重复注册/邮箱冲突）；注册时间、最后登录时间这类时间字段，用时间戳（便于排序和范围查询）。

索引策略上，主键索引（用户ID）是必须的，唯一索引（用户名、邮箱）用于快速查找和约束，普通索引（注册时间、最后登录时间）用于按时间范围查询（比如“最近7天注册的用户”）。

事务处理场景要保证原子性。比如用户注册时，插入用户记录、发送验证邮件、更新缓存这三个步骤必须一起成功或一起失败，所以用事务控制。密码修改时，先验证原密码，再更新新密码，避免数据不一致（比如原密码错误但新密码更新了）。

应对百万级用户增长，得考虑分库分表。比如按用户ID哈希分表（每个表约100万用户），或者按注册年份分库（2020年用户放db0，2021年放db1），减少单表压力；读写分离（主库写，从库读，提升读性能）；缓存（用Redis缓存用户信息，减少数据库查询次数）。

3) 【对比与适用场景】

索引类型对比（B树 vs 哈希）：
| 索引类型 | 定义 | 特性 | 使用场景 | 注意点 |
| --- | --- | --- | --- | --- |
| B树索引 | 树形结构，支持范围查询 | 查询效率高，支持排序 | 按时间范围查询（如最近30天注册用户）、排序查询 | 不支持等值查询 |
| 哈希索引 | 哈希表结构，等值查询快 | 等值查询效率极高 | 按用户ID、用户名等唯一字段查询 | 不支持范围查询 |
事务隔离级别对比：
| 隔离级别 | 定义 | 特性 | 使用场景 | 注意点 |
| --- | --- | --- | --- | --- |
| 读未提交 | 读脏数据 | 最宽松 | 测试环境，允许脏读 | 可能导致数据不一致 |
| 读已提交 | 读已提交数据 | 避免脏读 | 一般业务场景 | 可能存在不可重复读 |
| 可重复读 | 避免脏读、不可重复读 | 保证事务内一致性 | 密码修改、订单修改等 | 可能存在幻读 |
| 串行化 | 最严格 | 避免脏读、不可重复读、幻读 | 高并发场景，如金融交易 | 性能最低 |

4) 【示例】

用户表SQL定义：

CREATE TABLE User (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    register_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_time TIMESTAMP NULL DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    UNIQUE (username),
    UNIQUE (email)
);

用户注册事务示例：

BEGIN;
INSERT INTO User (user_id, username, email, register_time, password_hash) 
VALUES (gen_random_uuid(), 'testuser', 'test@example.com', NOW(), crypt('password123', gen_salt('bf')));
-- 发送验证邮件（调用外部服务）
-- 更新Redis缓存
COMMIT;

密码修改事务示例：

BEGIN;
UPDATE User 
SET password_hash = crypt('newpassword456', gen_salt('bf')) 
WHERE user_id = ? AND password_hash = crypt('oldpassword789', gen_salt('bf'));
-- 更新Redis缓存
COMMIT;

5) 【面试口播版答案】
“首先，用户表设计要兼顾数据一致性、查询效率与可扩展性。字段设计上，用户ID用UUID（全局唯一，避免自增冲突），用户名和邮箱设唯一索引（保证唯一性，避免重复）；时间字段用时间戳（便于排序和查询）。索引策略上，主键索引（用户ID）是必须的，唯一索引（用户名、邮箱）用于快速查找和约束，普通索引（注册时间、最后登录时间）用于按时间范围查询。

事务处理方面，用户注册时，插入用户记录、发送验证邮件、更新缓存需原子性，用事务保证；密码修改时，先验证原密码，再更新新密码，避免数据不一致。应对百万级用户增长，采用分库分表（如按user_id哈希分表，或按时间分库），读写分离（主库写，从库读），缓存（Redis缓存用户信息，减少数据库压力）。”

6) 【追问清单】

问题：索引选择依据是什么？
回答要点：根据查询需求，B树索引适合范围查询（如按时间排序），哈希索引适合等值查询（如按用户ID查询）。
问题：事务隔离级别如何选？
回答要点：根据业务需求，密码修改等场景用“可重复读”避免脏读和不可重复读；高并发场景用“串行化”保证一致性，但需权衡性能。
问题：分库分表的具体方案？
回答要点：按user_id哈希分表（每个表约100万用户），或按注册年份分库（如2020年用户放db0，2021年放db1），减少单表压力。

7) 【常见坑/雷区】

字段类型选择错误（如用户名用非唯一索引导致冲突）；
事务未处理外键或依赖（如注册时未处理邮箱唯一性）；
未考虑分库分表时的跨表查询问题（如多表关联查询性能下降）；
缓存未考虑一致性（如更新后未及时刷新，导致缓存与数据库数据不一致）；
索引过多导致写性能下降（如主键索引外额外添加过多普通索引）。