数据库基础 database-fundamentals

---

名称: 数据库基础 描述: 复习模式设计、SQL查询、ORM模式。适用于初级开发者创建模式、编写查询、添加迁移、使用Prisma/MongoDB/PostgreSQL，或询问“这个SQL安全吗”、“N+1”、“索引”时。

数据库基础复习

“您的数据库是基础。如果构建错误，其上的一切都会崩溃。”

何时应用

在审查以下内容时激活此技能：

• 模式设计和迁移
• SQL/NoSQL查询
• ORM模型定义
• 数据关系
• 索引创建
• 查询性能

---

审查清单

模式设计

• [ ] 规范化：数据是否适当规范化（没有过度重复）？
• [ ] 反规范化理由：如果反规范化，是否有性能原因？
• [ ] 主键：每个表是否有明确的主键？
• [ ] 外键：关系是否在数据库级别强制执行？
• [ ] 数据类型：是否使用适当的数据类型（不是所有都用TEXT）？

索引

• [ ] 基于查询：是否为频繁查询的列创建索引？
• [ ] 复合索引：多列查询是否被覆盖？
• [ ] 不过度索引：是否有不必要的索引减慢写入？
• [ ] 唯一约束：唯一字段是否在数据库级别强制执行？

查询

• [ ] 无N+1：相关记录是否批量获取？
• [ ] 选择特定字段：是否避免使用SELECT *？
• [ ] 分页：列表查询是否限制结果？
• [ ] 参数化：所有查询是否参数化（无字符串拼接）？

迁移

• [ ] 可逆：此迁移可以回滚吗？
• [ ] 无数据丢失：现有数据会在此迁移中存活吗？
• [ ] 测试过：是否针对生产类似数据进行了测试？
• [ ] 增量式：大型变更是否分解为较小的迁移？

---

常见错误（反模式）

1. N+1查询问题

❌ // 1个查询获取用户 + N个查询获取帖子
   const users = await User.findAll();
   for (const user of users) {
     user.posts = await Post.findAll({ where: { userId: user.id } });
   }

✅ // 1个查询带JOIN
   const users = await User.findAll({
     include: [{ model: Post }]
   });

   // 或2个查询带IN子句
   const users = await User.findAll();
   const userIds = users.map(u => u.id);
   const posts = await Post.findAll({ where: { userId: userIds } });

2. 缺失索引

❌ // 频繁查询，但无索引
   SELECT * FROM orders WHERE user_id = ?
   SELECT * FROM products WHERE category = ? AND status = 'active'

✅ CREATE INDEX idx_orders_user_id ON orders(user_id);
   CREATE INDEX idx_products_category_status ON products(category, status);

3. 到处使用SELECT *

❌ SELECT * FROM users; // 返回50列

✅ SELECT id, name, email FROM users; // 仅需要的内容

4. 字符串拼接（SQL注入）

❌ db.query(`SELECT * FROM users WHERE email = '${email}'`);

✅ db.query('SELECT * FROM users WHERE email = ?', [email]);

5. 破坏性迁移

❌ -- 无法回滚
   DROP TABLE users;
   ALTER TABLE orders DROP COLUMN status;

✅ -- 添加新的、迁移数据、然后删除旧的（在单独的迁移中）
   -- 迁移1：添加新列
   ALTER TABLE orders ADD COLUMN status_new VARCHAR(20);
   -- 迁移2：复制数据
   UPDATE orders SET status_new = status;
   -- 迁移3：删除旧的（在验证后）
   ALTER TABLE orders DROP COLUMN status;

---

苏格拉底式问题

向初级开发者提问这些问题，而不是直接给出答案：

1. 模式：“为什么选择这种数据类型？”
2. 关系：“如果删除此相关记录会发生什么？”
3. 索引：“哪些列一起被查询？它们有索引吗？”
4. N+1：“此操作执行了多少个查询？”
5. 迁移：“如果我们需要回滚，会发生什么？”

---

规范化快速参考

形式	规则	示例问题
1NF	无重复组	tags: "js,react,node" 应该是一个单独的表
2NF	无部分依赖	订单项价格从产品中重复
3NF	无传递依赖	存储城市和邮政编码（邮政编码决定城市）

何时反规范化

• 读写频繁、写入稀少的负载
• 计算的聚合（如订单总计）
• 缓存频繁访问的派生数据

---

索引策略

-- 单列索引（最常见）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（用于多列查询）
-- 顺序很重要！最选择性的列在前
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 部分索引（用于过滤查询）
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 唯一索引（强制执行约束）
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

索引经验法则

1. 索引WHERE子句中的列
2. 索引JOIN条件中的列
3. 索引ORDER BY中的列（如果与WHERE一起使用）
4. 不要对写密集型表过度索引
5. 考虑为多列查询使用复合索引

---

查询优化清单

1. [ ] 使用EXPLAIN分析查询计划
2. [ ] 避免SELECT * - 指定列
3. [ ] 使用LIMIT进行分页
4. [ ] 为WHERE/JOIN列添加索引
5. [ ] 在可能时使用WHERE而不是HAVING
6. [ ] 避免在WHERE中对索引列使用函数
7. [ ] 对大型子查询使用EXISTS而不是IN

---

需要指出的危险信号

标志	要问的问题
循环中的查询	“我们能在一个查询中获取所有这些数据吗？”
无分页	“如果有100万条记录怎么办？”
SELECT *	“我们需要所有50列吗？”
查询中的字符串	“这个是否受SQL注入保护？”
外键上无索引	“在此表上的JOIN速度如何？”
迁移中的DROP TABLE	“我们如何回滚这个？”
所有内容用TEXT	“这个应该用INT或DATE代替吗？”
无外键约束	“什么防止了孤儿记录？”

---

ORM最佳实践

// 预加载（避免N+1）
const users = await User.findAll({
  include: [{ model: Post, attributes: ['id', 'title'] }]
});

// 选择特定字段
const users = await User.findAll({
  attributes: ['id', 'name', 'email']
});

// 分页
const users = await User.findAll({
  limit: 20,
  offset: (page - 1) * 20
});

// 复杂操作的原生查询
const results = await sequelize.query(
  'SELECT ... 复杂查询 ...',
  { type: QueryTypes.SELECT }
);