Agent Skills
› NeverSight/learn-skills.dev
› database-indexing
database-indexing
GitHub提供数据库索引策略与查询优化指南，涵盖B-Tree、Hash等索引类型选择，EXPLAIN计划分析，避免常见错误及性能调优技巧，辅助提升数据库执行效率。
Trigger Scenarios
optimize queries
create indexes
database performance
query analysis
explain plans
index selection
slow queries
database tuning
schema optimization
Install
npx skills add NeverSight/learn-skills.dev --skill database-indexing -g -y
SKILL.md
Frontmatter
{
"name": "database-indexing",
"description": "Database indexing strategies and query optimization. Use when user asks to \"optimize queries\", \"create indexes\", \"database performance\", \"query analysis\", \"explain plans\", \"index selection\", \"slow queries\", \"database tuning\", \"schema optimization\", or mentions database performance and query optimization."
}
Database Indexing & Query Optimization
Strategies for optimizing database queries through proper indexing and schema design.
Index Types
B-Tree Index
- Default for most databases (MySQL, PostgreSQL)
- Balanced tree structure
- Good for range queries and sorting
Hash Index
- O(1) lookup for equality
- Not suitable for range queries
- Fast point lookups
Full-Text Index
- Optimized for text search
- Language-specific analysis
- Used with text search queries
Spatial Index
- R-tree, Quadtree for geographic data
- Optimized for spatial queries
Composite Index
- Multiple columns in one index
- Column order matters (leftmost prefix)
Query Optimization Techniques
EXPLAIN Plans
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
Index Selection
- Look for WHERE clause columns
- Consider JOIN conditions
- Evaluate sorting/grouping columns
- Check cardinality (selectivity)
Avoid Common Mistakes
- Creating indexes on low-cardinality columns
- Creating unused indexes
- Over-indexing (write performance impact)
- Not analyzing index usage
Performance Tuning
- Analyze queries - Use EXPLAIN
- Identify bottlenecks - Query profiling
- Test thoroughly - Before/after metrics
- Monitor regularly - Track performance changes
- Denormalize carefully - Balance read vs write
- Archive old data - Keep active data small
- Partition tables - Handle large datasets
Schema Design
- Normalization - Reduce redundancy
- Appropriate data types - Use INT not VARCHAR for IDs
- Foreign keys - Maintain referential integrity
- Constraints - Enforce data quality
Tools & Commands
PostgreSQL:
CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;
ANALYZE;
MySQL:
EXPLAIN analyzer SELECT * FROM users WHERE email = 'test@example.com';
CREATE INDEX idx_email ON users(email);
References
- PostgreSQL Index Documentation
- MySQL Performance Tuning
- Database Query Optimization Principles
- Use the Index, Luke! (Free online book)
Version History
- e0220ca Current 2026-07-05 20:42
