PC客户端使用SQLite存储用户配置或缓存数据，如何设计表结构以支持高效查询？请举例说明如何处理并发写入（如乐观锁/悲观锁），以及如何优化查询性能（如索引）。
答案
1) 【一句话结论】
设计用户配置表需明确主键（如id）、外键（user_id关联用户表）、字段类型（key/value为TEXT，加version字段做乐观锁），通过事务管理结合索引优化查询，并依据读写比例选择乐观锁（低并发）或悲观锁（高写入），同时考虑SQLite的WAL日志机制保证事务一致性。
2) 【原理/概念讲解】
表结构设计遵循“唯一标识、业务关联、类型适配”原则。主键（如自增id）确保数据唯一，外键（user_id）建立用户与配置的关联。索引通过B-树结构加速查询，复合索引（如user_id+key）能高效定位特定用户配置。事务在并发控制中至关重要，SQLite的WAL日志（Write-Ahead Logging）机制在事务提交时先写入日志，再更新数据，避免数据丢失。乐观锁假设数据未被修改，通过版本号验证一致性；悲观锁假设数据会被修改，直接加锁（如行级锁）阻止冲突。类比：表结构是数据的“骨架”，索引是查询的“导航”，事务是操作的“保险箱”，乐观锁是“版本检查门卫”，悲观锁是“资源锁”。
3) 【对比与适用场景】
| 特性 | 乐观锁（Version-based） | 悲观锁（Lock-based） |
|---|---|---|
| 定义 | 假设数据未被修改，通过版本号验证一致性 | 假设数据会被修改，直接加锁阻止写入 |
| 机制 | 更新时检查版本号是否一致，不一致则重试 | 读取时加锁，写入时释放锁 |
| 适用场景 | 低并发环境，频繁读取（如配置查询），写入较少 | 高并发写入频繁，需要强一致性（如实时数据更新） |
| 注意点 | 版本冲突率高时，重试次数增加，性能下降；需合理设计版本号更新策略（如批量更新时检查版本号范围） | 锁竞争导致性能瓶颈，需控制锁粒度（如行级锁而非表级锁），避免死锁 |
| 锁粒度 | 无锁（仅版本号验证） | 行级锁（或表级锁，需谨慎选择） |
4) 【示例】
用户配置表设计（SQLite）：
CREATE TABLE config (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL, -- 外键关联用户表
key TEXT NOT NULL,
value TEXT NOT NULL,
version INTEGER DEFAULT 1, -- 乐观锁版本号
create_time INTEGER DEFAULT (strftime('%s', 'now')) -- 创建时间戳
);
-- 建立复合索引，加速查询特定用户配置
CREATE INDEX idx_user_key ON config (user_id, key);
并发写入（乐观锁流程，需在事务内执行）：
- 开始事务：BEGIN TRANSACTION;
- 读取数据：SELECT id, user_id, key, value, version FROM config WHERE id = ?;
- 更新配置：UPDATE config SET value = ?, version = version + 1 WHERE id = ? AND version = ?;
- 提交事务：COMMIT;
- 若步骤3中WHERE条件不满足（版本号不一致），则回滚事务（ROLLBACK），并重试（如指数退避，第一次重试1ms，第二次2ms，指数级增长）。
查询优化：查询时使用复合索引，如：
SELECT value FROM config WHERE user_id = ? AND key = ?;
5) 【面试口播版答案】
“面试官您好，针对PC客户端用SQLite存储用户配置，设计表结构时核心是明确主键、外键及字段类型。比如用户配置表用id作为主键，user_id关联用户表，key存储配置键，value存储配置值，加上version字段做乐观锁。查询优化方面，为user_id和key建立复合索引，加速特定用户的配置查询。并发写入用乐观锁，流程是先读取数据获取版本号，更新时检查版本是否一致，不一致则重试（如指数退避），避免数据冲突。比如更新配置时，先查当前版本，更新后版本+1，若检查失败则回滚。同时考虑SQLite的WAL日志机制，事务提交时先写入日志再更新数据，保证数据一致性。如果写入频繁，也可考虑悲观锁，用行级锁控制锁粒度，但需注意避免死锁。”
6) 【追问清单】
- 问题1：版本号字段如何设计？回答要点：用自增整数（初始为1，每次更新递增1），或时间戳（如last_modified字段，记录最后修改时间，适用于批量更新时检查版本号范围）。
- 问题2：高并发下乐观锁的失败率如何应对？回答要点：增加重试次数（如最多重试3次），或调整版本号更新策略（如批量更新时检查版本号范围，避免大量冲突）。
- 问题3：索引选择哪些字段？回答要点：根据查询条件，如查询用户特定配置时，用user_id和key的复合索引；若查询条件包含多个字段，则建立对应复合索引，避免索引失效（全表扫描）。
- 问题4：悲观锁如何实现？回答要点：在读取时加行级锁（如SELECT ... FOR UPDATE），写入时释放锁，适用于写入频繁的场景，但需注意锁粒度，避免表级锁导致性能瓶颈。
- 问题5：索引维护成本？回答要点：索引会增加写入操作的开销（如更新时需要更新索引），但查询效率显著提升，需平衡读写比例，高写入场景下可能需要更谨慎选择索引（如只对查询频率高的字段建索引）。
7) 【常见坑/雷区】
- 坑1：表无主键，导致数据唯一性无法保证，查询时无法定位唯一记录，影响数据操作（如更新或删除特定配置）。
- 坑2：索引选择不当，如单字段索引但查询条件是复合条件（如同时按user_id和key查询），导致索引失效（全表扫描），降低查询性能。
- 坑3：乐观锁版本冲突率高，未考虑重试机制，导致频繁失败，影响业务流程（如更新配置时重试次数过多，增加延迟）。
- 坑4：忽略事务，并发写入时未用事务保证原子性，导致数据不一致（如更新时检查版本号但未提交事务，其他线程修改数据后冲突）。
- 坑5：悲观锁锁粒度过大，如加表级锁，导致整个表不可写，影响性能，应使用行级锁控制锁粒度，避免死锁（如避免循环加锁导致死锁）。