Back to Details

database-optimization

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Optimization

数据库性能优化

This skill optimizes database performance including query optimization, indexing strategies, N+1 problem resolution, and caching implementation.
本技能可优化数据库性能，包括查询优化、索引策略制定、N+1问题解决及缓存实现。

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
  • 优化慢数据库查询时
  • 修复N+1查询问题时
  • 设计索引时
  • 实现缓存策略时
  • 优化数据库迁移时
  • 提升数据库整体性能时

What This Skill Does

技能功能

  1. Query Optimization: Analyzes and optimizes SQL queries
  2. Index Design: Creates appropriate indexes
  3. N+1 Resolution: Fixes N+1 query problems
  4. Caching: Implements caching layers (Redis, Memcached)
  5. Migration Optimization: Optimizes database migrations
  6. Performance Monitoring: Sets up query performance monitoring
  1. 查询优化：分析并优化SQL查询语句
  2. 索引设计：创建合适的索引
  3. N+1问题解决：修复N+1查询问题
  4. 缓存实现：搭建缓存层（Redis、Memcached）
  5. 迁移优化：优化数据库迁移流程
  6. 性能监控：配置查询性能监控

How to Use

使用方法

Optimize Queries

优化查询

Optimize this slow database query
Fix the N+1 query problem in this code
Optimize this slow database query
Fix the N+1 query problem in this code

Specific Analysis

专项分析

Analyze query performance and suggest indexes
Analyze query performance and suggest indexes

Optimization Areas

优化领域

Query Optimization

查询优化

Techniques:
  • Use EXPLAIN ANALYZE
  • Optimize JOINs
  • Reduce data scanned
  • Use appropriate indexes
  • Avoid SELECT *
技巧：
  • 使用EXPLAIN ANALYZE
  • 优化JOIN操作
  • 减少扫描的数据量
  • 使用合适的索引
  • 避免使用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

N+1问题

Pattern:
python
undefined
示例：
python
undefined

Bad: N+1 queries

不良写法：N+1查询

users = User.all() for user in users: posts = Post.where(user_id=user.id) # N queries
users = User.all() for user in users: posts = Post.where(user_id=user.id) # N次查询

Good: Single query with JOIN

优化写法：单次JOIN查询

users = User.all().includes(:posts) # 1 query
undefined
users = User.all().includes(:posts) # 1次查询
undefined

Examples

示例

Example 1: Query Optimization

示例1：查询优化

Input: Optimize slow user query
Output:
markdown
undefined
输入：优化慢用户查询
输出：
markdown
undefined

Database Optimization: User Query

数据库优化：用户查询

Current Query

当前查询

sql
SELECT * FROM users
WHERE email = 'user@example.com';
-- Execution time: 450ms
sql
SELECT * FROM users
WHERE email = 'user@example.com';
-- 执行时间：450ms

Analysis

分析

  • Full table scan (no index on email)
  • Scanning 1M+ rows
  • 全表扫描（email列无索引）
  • 扫描100万+行数据

Optimization

优化方案

sql
-- 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
sql
-- 添加索引
CREATE INDEX idx_users_email ON users(email);

-- 优化后的查询
SELECT id, email, name FROM users
WHERE email = 'user@example.com';
-- 执行时间：2ms

Impact

优化效果

  • Query time: 450ms → 2ms (99.5% improvement)
  • Index size: ~50MB
undefined
  • 查询时间：450ms → 2ms（提升99.5%）
  • 索引大小：约50MB
undefined

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
  1. 先测量再优化：使用EXPLAIN ANALYZE
  2. 策略性创建索引：并非每个列都需要索引
  3. 持续监控：跟踪慢查询日志
  4. 合理缓存：缓存开销大的查询
  5. 按需反规范化：当读取模式需要时

Reference Files

参考文件

  • references/query_patterns.md
     - Common query optimization patterns, anti-patterns, and caching strategies
  • references/query_patterns.md
     - 常见查询优化模式、反模式及缓存策略

Related Use Cases

相关用例

  • Query optimization
  • Index design
  • N+1 problem resolution
  • Caching implementation
  • Database performance improvement
  • 查询优化
  • 索引设计
  • N+1问题解决
  • 缓存实现
  • 数据库性能提升