Agent Skills
› cosmicstack-labs/mercury-agent-skills
› database-design
database-design
GitHub提供数据库设计指南，涵盖SQL/NoSQL的模式设计、规范化理论、索引策略、迁移管理及查询优化技巧，旨在构建高效可扩展的数据架构。
触发场景
需要设计新的数据库表结构或Schema
分析并优化慢查询性能
规划数据库版本迁移方案
安装
npx skills add cosmicstack-labs/mercury-agent-skills --skill database-design -g -y
SKILL.md
Frontmatter
{
"name": "database-design",
"metadata": {
"tags": [
"database",
"sql",
"nosql",
"schema",
"indexing",
"migrations"
],
"author": "cosmicstack-labs",
"version": "1.0.0",
"category": "backend"
},
"description": "Schema design, normalization, indexing, migrations, and query optimization for SQL and NoSQL"
}
Database Design
Design efficient, scalable database schemas.
Normalization
| Normal Form | Rule | Violation Example |
|---|---|---|
| 1NF | Atomic columns, no repeating groups | phone_numbers: "555-0100,555-0200" |
| 2NF | 1NF + all non-key cols depend on full PK | Orders table with product details |
| 3NF | 2NF + no transitive dependencies | Employee with department_name (dept info in dept table) |
When to denormalize: Read-heavy workloads, reporting, caching layers.
Indexing Strategy
Index Types
| Index | Use Case | Tradeoff |
|---|---|---|
| B-Tree | General purpose, equality + range | Default, usually best |
| Hash | Equality only | Fast lookups, no sorting |
| GIN | Array/JSON | Slightly slower writes |
| BRIN | Huge sorted tables | Very small size |
Rules
- Index columns used in WHERE, JOIN, ORDER BY
- Index foreign keys
- Covering indexes for frequent queries
- Don't over-index (slows writes, uses space)
- Drop unused indexes (use pg_stat_user_indexes)
Migrations
- One file per change, sequential naming
- Always reversible (up/down pair)
- Test on staging before production
- Run in transactions where possible
- Avoid locking tables on large tables (use pt-online-schema-change)
Query Optimization
- EXPLAIN ANALYZE every slow query
- Look for: Seq scans, nested loops, temp files
- Common fixes: missing index, bad join order, OR conditions
- N+1 problem: batch find / includes / joins
- Limit offsets for pagination (use cursor-based)
版本历史
- 38e2523 当前 2026-07-05 19:36
