database-optimization

数据库优化

针对 PostgreSQL、MySQL、SQL Server 等 SQL 数据库，以及 MongoDB、Cassandra、Redis 等 NoSQL 数据库，优化数据库性能、架构设计、索引和查询性能。此技能提供全面的数据库优化策略和工具。

何时使用

遇到以下情况时，请使用此技能：

• 数据库性能下降，查询速度变慢
• 需要优化数据库架构和索引策略
• 想要分析并优化查询性能
• 需要调整数据库配置参数
• 正在迁移数据库，需要优化方面的指导
• 想要实施数据库监控和告警
• 需要针对特定工作负载（OLTP、OLAP、混合型）优化数据库
• 想要通过优化降低数据库成本

我能做什么

• 查询性能分析：分析并优化 SQL 和 NoSQL 查询
• 索引优化：分析并优化数据库索引
• 架构优化：优化数据库架构设计和规范化
• 配置调优：调整数据库配置参数，以获得最佳性能
• 连接池优化：优化数据库连接管理
• 锁与并发优化：优化锁定策略和并发控制
• 存储优化：优化数据库存储和分区策略
• 复制与分片优化：优化复制和分片策略
• 备份与恢复优化：优化备份和恢复策略
• 数据库监控：实施数据库性能监控和告警

示例

# Analyze query performance
./scripts/analyze-database-optimization.sh --query-analysis --database postgresql

# Optimize database indexes
./scripts/analyze-database-optimization.sh --index-optimization --database mysql

# Tune database configuration
./scripts/analyze-database-optimization.sh --configuration-tuning --database mongodb

# Generate optimization report
./scripts/analyze-database-optimization.sh --report --output optimization-report.json

# Monitor database performance
./scripts/analyze-database-optimization.sh --performance-monitoring --interval 60

输出格式

Database Optimization Analysis
─────────────────────────────────────
Analysis Date: 2025-01-15T10:30:00Z
Database System: PostgreSQL 14.8
Database Size: 245 GB
Analysis Duration: 15 minutes

PERFORMANCE METRICS:
────────────────────
Current Performance Score: 72/100
Query Response Time: 85th percentile: 450ms (Target: < 200ms)
Transactions per Second: 125 (Target: > 200)
Connection Pool Utilization: 92% (Target: < 80%)
Cache Hit Ratio: 78% (Target: > 90%)

QUERY PERFORMANCE ANALYSIS:
───────────────────────────
Slow Queries Identified: 42
Total Query Execution Time: 85% spent on 5 queries

Top 5 Slowest Queries:
1. Query: SELECT * FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 100
   Average Execution Time: 1,250ms
   Execution Count: 12,850/day
   Issue: Missing composite index on (customer_id, status, created_at)
   Optimization: Add index: CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC)

2. Query: SELECT p.*, c.name FROM products p JOIN categories c ON p.category_id = c.id WHERE p.price > ? AND p.stock > 0
   Average Execution Time: 890ms
   Execution Count: 8,420/day
   Issue: Sequential scan on products table (245,000 rows)
   Optimization: Add index: CREATE INDEX idx_products_price_stock ON products(price) WHERE stock > 0

3. Query: UPDATE inventory SET quantity = quantity - ? WHERE product_id = ? AND warehouse_id = ?
   Average Execution Time: 650ms
   Execution Count: 15,230/day
   Issue: Row-level locking contention
   Optimization: Implement optimistic locking or batch updates

4. Query: SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at > NOW() - INTERVAL '30 days' GROUP BY user_id HAVING COUNT(*) > 5
   Average Execution Time: 1,850ms
   Execution Count: 1,250/day
   Issue: Full table scan with aggregation
   Optimization: Create summary table or materialized view

5. Query: DELETE FROM sessions WHERE expires_at < NOW()
   Average Execution Time: 2,150ms
   Execution Count: 850/day
   Issue: Table bloat and vacuum overhead
   Optimization: Implement batch deletion with index on expires_at

INDEX OPTIMIZATION ANALYSIS:
─────────────────────────────
Current Indexes: 48
Duplicate Indexes: 7
Unused Indexes: 12
Missing Indexes: 9

Index Issues:
• Duplicate: idx_orders_customer (customer_id) and idx_orders_customer_status (customer_id, status)
• Unused: idx_products_supplier (supplier_id) - 0 uses in 30 days
• Missing: idx_orders_created_status (created_at, status) - would benefit 3 frequent queries

Index Recommendations:
1. Drop 7 duplicate indexes: Free 2.8GB storage
2. Drop 12 unused indexes: Free 4.2GB storage
3. Add 9 missing indexes: Improve query performance 35-85%

SCHEMA OPTIMIZATION:
────────────────────
Normalization Issues:
• products table has redundant category_name field (denormalized)
• orders table missing foreign key constraint on customer_id
• users table has JSONB field with frequently queried data (should be separate columns)

Schema Recommendations:
1. Remove redundant category_name from products table
2. Add foreign key constraint: ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
3. Extract frequently queried JSONB fields to separate columns
4. Consider partitioning orders table by created_at month

CONFIGURATION TUNING:
──────────────────────
Current Configuration Issues:
• shared_buffers: 128MB (Recommended: 4-8GB for 32GB RAM)
• work_mem: 4MB (Recommended: 64MB for complex queries)
• maintenance_work_mem: 64MB (Recommended: 1-2GB)
• effective_cache_size: 4GB (Recommended: 24GB)
• random_page_cost: 4.0 (Recommended: 1.1 for SSDs)

Configuration Recommendations:
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB
random_page_cost = 1.1

CONNECTION POOLING ANALYSIS:
────────────────────────────
Current: 200 connections, 184 active (92% utilization)
Issues: Connection pool exhaustion during peak hours
Recommendations:
• Increase connection pool to 300
• Implement connection pool with pgbouncer
• Set idle connection timeout to 300 seconds

LOCKING AND CONCURRENCY:
─────────────────────────
Lock Wait Events: 12,850/day
Deadlocks: 8/day
Issues: High row-level locking on inventory table
Recommendations:
• Implement optimistic locking for inventory updates
• Use SKIP LOCKED for batch processing
• Reduce transaction isolation level where appropriate

STORAGE OPTIMIZATION:
──────────────────────
Table Sizes:
• orders: 85GB (35% of database)
• products: 42GB (17% of database)
• users: 28GB (11% of database)

Storage Issues:
• orders table has high bloat (32% dead tuples)
• No partitioning on time-series data
• Uncompressed JSONB columns

Storage Recommendations:
1. Vacuum aggressive on orders table
2. Implement partitioning on orders by created_at (monthly)
3. Enable compression for historical data
4. Archive old orders to cold storage

REPLICATION AND SHARDING:
─────────────────────────
Current: Single primary with 2 read replicas
Issues: Replication lag up to 45 seconds during peak
Recommendations:
• Add 1 more read replica
• Implement connection routing (primary for writes, replicas for reads)
• Consider sharding by customer_id for orders table

BACKUP AND RECOVERY:
─────────────────────
Current: Daily full backup, 7-day retention
Issues: Backup takes 4 hours, affects performance
Recommendations:
• Implement incremental backups with WAL archiving
• Increase retention to 30 days
• Test recovery procedure monthly

PERFORMANCE MONITORING:
────────────────────────
Current Monitoring: Basic (CPU, memory, disk)
Missing: Query performance, index usage, lock monitoring
Recommendations:
• Implement pg_stat_statements for query monitoring
• Set up alerts for slow queries (> 500ms)
• Monitor index usage and bloat weekly

COST OPTIMIZATION:
──────────────────
Current Monthly Cost: $1,850 (AWS RDS)
Optimization Opportunities:
• Right-size instance: Save $450/month
• Reserved instance: Save $650/month (3-year)
• Storage optimization: Save $125/month
• Archive old data: Save $85/month

Total Potential Savings: $1,310/month (71%)

IMPLEMENTATION ROADMAP:
────────────────────────
Phase 1: Immediate (1-2 days):
• Add 5 missing indexes for slowest queries
• Drop 7 duplicate indexes
• Tune critical configuration parameters

Phase 2: Short-term (1-2 weeks):
• Implement connection pooling
• Add query performance monitoring
• Optimize backup strategy

Phase 3: Medium-term (3-4 weeks):
• Implement table partitioning
• Add read replica
• Optimize schema (remove redundancy, add constraints)

Phase 4: Long-term (2-3 months):
• Implement sharding strategy
• Archive historical data
• Comprehensive performance testing

EXPECTED RESULTS:
─────────────────
• Query performance improvement: 45-85%
• Storage reduction: 15-25%
• Cost reduction: 50-70%
• Availability improvement: 99.9% → 99.95%
• Maintenance overhead reduction: 40-60%

注意事项

• 数据库优化是一个迭代过程；请在变更前后进行测量
• 不同的数据库系统需要采用不同的优化方法
• 优化时应考虑工作负载模式（OLTP 与 OLAP）
• 在生产环境中应用优化变更前，先在预发布环境中进行测试
• 监控优化变更对应用性能的影响
• 定期维护（vacuum、analyze、reindex）对于保持性能至关重要
• 优化时应同时考虑读写性能
• 在规范化与性能要求之间取得平衡
• 实施全面的监控，以检测性能回退