AgentSkillsCN

database-indexing

掌握数据库索引策略与查询优化技术。当用户提出“优化查询”“创建索引”“提升数据库性能”“分析查询”“解释执行计划”“选择索引”“处理慢查询”“调优数据库”“优化表结构”，或提及数据库性能与查询优化时使用。

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

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

Index Selection

  1. •Look for WHERE clause columns
  2. •Consider JOIN conditions
  3. •Evaluate sorting/grouping columns
  4. •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

  1. •Analyze queries - Use EXPLAIN
  2. •Identify bottlenecks - Query profiling
  3. •Test thoroughly - Before/after metrics
  4. •Monitor regularly - Track performance changes
  5. •Denormalize carefully - Balance read vs write
  6. •Archive old data - Keep active data small
  7. •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:

sql
CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;
ANALYZE;

MySQL:

sql
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)