Agent Skills
› NeverSight/learn-skills.dev
› database-optimization
database-optimization
GitHub专注于数据库性能优化的专家技能，涵盖慢查询分析、N+1问题解决、索引设计、缓存策略实施及迁移优化。支持PostgreSQL和MySQL等数据库，旨在通过EXPLAIN ANALYZE等手段显著提升查询效率和系统性能。
Trigger Scenarios
优化慢速SQL查询
修复N+1查询问题
设计或审查数据库索引
实现Redis/Memcached缓存策略
提升整体数据库性能
Install
npx skills add NeverSight/learn-skills.dev --skill database-optimization -g -y
SKILL.md
Frontmatter
{
"tag": "skill",
"name": "database-optimization",
"type": "skill",
"author": "Joseph OBrien",
"status": "unpublished",
"updated": "2025-12-23",
"version": "1.0.1",
"description": "SQL query optimization and database performance specialist. Use when optimizing slow queries, fixing N+1 problems, designing indexes, implementing caching, or improving database performance. Works with PostgreSQL, MySQL, and other databases."
}
Database Optimization
This skill optimizes database performance including query optimization, indexing strategies, N+1 problem resolution, and caching implementation.
When to Use This Skill
- When optimizing slow database queries
- When fixing N+1 query problems
- When designing indexes
- When implementing caching strategies
- When optimizing database migrations
- When improving database performance
What This Skill Does
- Query Optimization: Analyzes and optimizes SQL queries
- Index Design: Creates appropriate indexes
- N+1 Resolution: Fixes N+1 query problems
- Caching: Implements caching layers (Redis, Memcached)
- Migration Optimization: Optimizes database migrations
- Performance Monitoring: Sets up query performance monitoring
How to Use
Optimize Queries
Optimize this slow database query
Fix the N+1 query problem in this code
Specific Analysis
Analyze query performance and suggest indexes
Optimization Areas
Query Optimization
Techniques:
- Use EXPLAIN ANALYZE
- Optimize JOINs
- Reduce data scanned
- Use appropriate indexes
- Avoid SELECT *
Index Design
Strategies:
- Index frequently queried columns
- Composite indexes for multi-column queries
- Avoid over-indexing
- Monitor index usage
- Remove unused indexes
N+1 Problem
Pattern:
# Bad: N+1 queries
users = User.all()
for user in users:
posts = Post.where(user_id=user.id) # N queries
# Good: Single query with JOIN
users = User.all().includes(:posts) # 1 query
Examples
Example 1: Query Optimization
Input: Optimize slow user query
Output:
## Database Optimization: User Query
### Current Query
```sql
SELECT * FROM users
WHERE email = 'user@example.com';
-- Execution time: 450ms
Analysis
- Full table scan (no index on email)
- Scanning 1M+ rows
Optimization
-- Add index
CREATE INDEX idx_users_email ON users(email);
-- Optimized query
SELECT id, email, name FROM users
WHERE email = 'user@example.com';
-- Execution time: 2ms
Impact
- Query time: 450ms → 2ms (99.5% improvement)
- Index size: ~50MB
## Best Practices
### Database Optimization
1. **Measure First**: Use EXPLAIN ANALYZE
2. **Index Strategically**: Not every column needs an index
3. **Monitor**: Track slow query logs
4. **Cache**: Cache expensive queries
5. **Denormalize**: When justified by read patterns
## Reference Files
- **`references/query_patterns.md`** - Common query optimization patterns, anti-patterns, and caching strategies
## Related Use Cases
- Query optimization
- Index design
- N+1 problem resolution
- Caching implementation
- Database performance improvement
Version History
- e0220ca Current 2026-07-05 21:28
