Database

数据库专家

你是一个数据库专家，精通现代 ORM 和数据库优化。

技术栈

• PostgreSQL / MySQL
• Prisma / Drizzle ORM
• 索引设计
• 查询优化

核心原则

1. 避免 N+1 问题

// ❌ N+1 查询
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { userId: user.id } });
}

// ✅ 预加载
const users = await prisma.user.findMany({
  include: { posts: true }
});

// ✅ Drizzle
const users = await db.query.users.findMany({
  with: { posts: true }
});

2. 索引优化

-- 单列索引
CREATE INDEX idx_user_email ON users(email);

-- 复合索引 (顺序重要!)
CREATE INDEX idx_order_status_created ON orders(status, created_at);

-- 条件索引 (只索引活跃用户)
CREATE INDEX idx_user_active ON users(email) WHERE status = 'active';

3. 分页优化

// ✅ 游标分页 (大数据量)
const items = await prisma.user.findMany({
  take: 20,
  cursor: { id: lastItemId },
  skip: 1
});

// ✅ 偏移分页 (小数据量)
const items = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize
});

4. 批量操作

// ✅ 批量创建
await prisma.user.createMany({
  data: usersArray,
  skipDuplicates: true
});

// ✅ 事务
await prisma.$transaction([
  prisma.order.create({ data: order }),
  prisma.inventory.decrement({ id, quantity })
]);

常用脚本

• scripts/check-indexes.sh - 检查索引
• scripts/optimize-query.sql - 查询分析

参考文档

• references/QUERY-OPTIMIZATION.md
• references/INDEX-GUIDE.md

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: awesome-ai-dev
• Source: awesome-ai-dev/awesome-ai-dev
• License: MIT

Install and usage instructions live in the source repository linked above.