Day 9: 数据库操作

你：设计电商系统的数据库表，包含用户、商品、订单、购物车

你：设计博客系统的表关系，用户、文章、评论、标签

你：为用户表添加合适的索引，优化查询性能

你：用 MongoDB 设计博客系统的文档结构

// 用户文档
{
  _id: ObjectId,
  username: "john_doe",
  email: "john@example.com",
  profile: {
    avatar: "avatar.jpg",
    bio: "前端开发者"
  },
  createdAt: Date
}
 
// 文章文档
{
  _id: ObjectId,
  title: "React 最佳实践",
  content: "文章内容...",
  author: ObjectId,  // 引用用户ID
  tags: ["react", "javascript"],
  comments: [        // 嵌套评论
    {
      _id: ObjectId,
      user: ObjectId,
      content: "很好的文章",
      createdAt: Date
    }
  ]
}

你：用 Mongoose 创建用户和文章模型，包含验证规则

你：查询最近30天发布的文章，按点赞数排序，支持分页

const articles = await Article.find({
  createdAt: { $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) }
})
.populate('author', 'username avatar')
.sort({ likes: -1 })
.skip((page - 1) * limit)
.limit(limit);

你：用 Prisma 设计用户订单系统的数据模型

model User {
  id       Int      @id @default(autoincrement())
  email    String   @unique
  name     String?
  orders   Order[]
  createdAt DateTime @default(now())
}
 
model Order {
  id        Int      @id @default(autoincrement())
  userId    Int
  user      User     @relation(fields: [userId], references: [id])
  total     Decimal
  status    String
  items     OrderItem[]
  createdAt DateTime @default(now())
}

你：查询用户的订单统计信息，包含总金额、订单数量、最近订单时间

你：实现下单功能，减库存和创建订单要在同一个事务中

你：分析这个查询为什么慢，怎么优化

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id;

你：为这个查询添加合适的索引

你：优化大数据量的分页查询，避免 OFFSET 性能问题

-- 第一页
SELECT * FROM articles WHERE id > 0 ORDER BY id LIMIT 20;
 
-- 下一页（假设上一页最后一条记录 id 为 20）
SELECT * FROM articles WHERE id > 20 ORDER BY id LIMIT 20;

你：为商品信息添加 Redis 缓存，设置合适的过期时间

你：实现查询结果缓存，相同查询直接返回缓存数据

你：商品信息更新时，同步更新 Redis 缓存

你：创建数据库迁移脚本，给用户表添加手机号字段

你：从 CSV 文件批量导入用户数据到数据库

你：实现 MySQL 到 MongoDB 的数据同步脚本

你：查询每月的订单统计，包含订单数和总金额

SELECT
  DATE_FORMAT(created_at, '%Y-%m') as month,
  COUNT(*) as order_count,
  SUM(total) as total_amount
FROM orders
WHERE created_at >= '2023-01-01'
GROUP BY month
ORDER BY month;

你：查询销量前10的商品，显示商品信息和销量

你：查询用户最近的5个订单，包含订单商品详情

你：实现商品的全文搜索，支持按名称、描述、标签搜索

你：监控数据库慢查询，记录执行时间超过1秒的查询

你：配置数据库连接池，优化连接使用效率

你：设置自动备份策略，每天凌晨备份数据库

你：检查这个查询是否有 SQL 注入风险，如何修复

// 危险的查询
const query = `SELECT * FROM users WHERE id = ${userId}`;
 
// 安全的查询
const query = `SELECT * FROM users WHERE id = ?`;
db.query(query, [userId]);

你：创建只读用户权限，只能查询特定表

你：对用户敏感信息（手机号、身份证）进行加密存储

你：分析用户表的存储空间使用情况，清理冗余数据

你：用户量太大了，设计用户表的分表策略

你：配置主从数据库，读操作走从库，写操作走主库