The creation and optimization of indexes

Index Creation

In Mongoose, indexes are key tools for improving query performance. By specifying indexes during model definition, data retrieval speed can be significantly enhanced. Mongoose supports various types of indexes, including single-field indexes, compound indexes, unique indexes, and more.

The most basic way to create a single-field index is as follows:

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    index: true  // Create a single-field index
  },
  email: String,
  createdAt: Date
});

// Alternatively, use the schema.index() method
userSchema.index({ createdAt: 1 });  // 1 for ascending order, -1 for descending order

Compound indexes are suitable for multi-condition query scenarios:

userSchema.index({ username: 1, createdAt: -1 });

Unique indexes ensure field values are not duplicated:

const productSchema = new mongoose.Schema({
  sku: {
    type: String,
    unique: true  // Create a unique index
  },
  name: String
});

Detailed Explanation of Index Types

Mongoose supports all MongoDB index types, each suited for different scenarios:

1. Single-Field Index: The simplest index type, ideal for queries on a single field.

userSchema.index({ email: 1 });

2. Compound Index: Combines multiple fields, most efficient when query conditions include these fields.

userSchema.index({ lastName: 1, firstName: 1 });

3. Multikey Index: Used for array fields, creating index entries for each element in the array.

const blogSchema = new mongoose.Schema({
  tags: [String]
});
blogSchema.index({ tags: 1 });

4. Text Index: Supports full-text search.

const articleSchema = new mongoose.Schema({
  title: String,
  content: String
});
articleSchema.index({ content: 'text' });

5. Geospatial Index: Used for geolocation queries.

const placeSchema = new mongoose.Schema({
  location: {
    type: { type: String },
    coordinates: [Number]
  }
});
placeSchema.index({ location: '2dsphere' });

Index Optimization Strategies

Proper index strategies can significantly improve query performance. Key optimization points include:

1. Prioritize High-Selectivity Fields: Choose fields with high distinctiveness for indexing.

// Username is more suitable for indexing than gender
userSchema.index({ username: 1 });  // High selectivity
// userSchema.index({ gender: 1 });  // Low selectivity

2. Covered Query Optimization: Ensure the index includes all fields required by the query.

// If only username and email are frequently needed
userSchema.index({ username: 1, email: 1 });

3. Query Pattern Matching: The order of index fields should match the query condition order.

// Good index design
userSchema.index({ status: 1, createdAt: -1 });
// Matching query
User.find({ status: 'active' }).sort('-createdAt');

4. Index Intersection Optimization: MongoDB can use the intersection of multiple indexes.

userSchema.index({ age: 1 });
userSchema.index({ country: 1 });
// The query will use the intersection of both indexes
User.find({ age: { $gt: 18 }, country: 'CN' });

Index Performance Analysis

Understanding index usage is crucial for optimization:

1. Explain Query Plans:

const explanation = await User.find({ age: { $gt: 25 } })
  .explain('executionStats');
console.log(explanation.executionStats);

2. Index Usage Monitoring:

// Get index usage statistics for a collection
const stats = await User.collection.stats();
console.log(stats.indexDetails);

3. Slow Query Log Analysis:

// Enable slow query logging
mongoose.set('debug', function(collectionName, method, query, doc) {
  if (query.executionTime > 100) {  // Queries over 100ms are considered slow
    console.log(`Slow query on ${collectionName}.${method}`, query);
  }
});

Common Index Issues and Solutions

1. Too Many Indexes Degrading Write Performance:

// Every insert/update must maintain all indexes
// Solution: Regularly evaluate and remove unused indexes
await User.collection.dropIndex('username_1');

2. Index Not Being Used:

// Possible cause: Query condition type mismatch
// Bad example: Using a number to query a string field
User.find({ phone: 123456 });  // Won't use the {phone:1} index
// Correct approach
User.find({ phone: '123456' });

3. Insufficient Memory Causing Index Inefficiency:

// Solution: Use projection to limit returned fields
User.find({ status: 'active' }, 'username email');

4. Index Fragmentation:

// Regularly rebuild indexes
await User.collection.reIndex();

Advanced Index Techniques

1. Partial Indexes: Create indexes only for documents that meet specific conditions.

userSchema.index({ username: 1 }, { 
  partialFilterExpression: { 
    status: { $eq: 'active' } 
  }
});

2. Sparse Indexes: Create indexes only for documents where the field exists.

userSchema.index({ referralCode: 1 }, { sparse: true });

3. TTL Indexes: Automatically expire documents.

const sessionSchema = new mongoose.Schema({
  _id: String,
  data: Object,
  expiresAt: Date
});
sessionSchema.index({ expiresAt: 1 }, { expireAfterSeconds: 0 });

4. Hidden Indexes: Test the impact of removing an index.

await User.collection.hideIndex('username_1');
// Test query performance
await User.collection.unhideIndex('username_1');

Index Maintenance Best Practices

1. Regular Index Reviews:

// Get index usage statistics
const indexStats = await User.collection.aggregate([
  { $indexStats: {} }
]).toArray();

2. Production Environment Index Change Process:

// 1. Validate new indexes in a test environment
// 2. Create indexes during off-peak hours (background build)
userSchema.index({ email: 1 }, { background: true });
// 3. Monitor performance changes

3. Index Naming Conventions:

userSchema.index({ department: 1, role: 1 }, { name: 'dept_role_idx' });

4. Sharded Cluster Index Strategies:

// Shard keys must be included in unique indexes
userSchema.index({ tenantId: 1, email: 1 }, { unique: true });