MongoDB批量更新优化求助：合并两集合邮箱数据

Hey there! Let's fix that sluggish bulk update you're dealing with. The core issue with your current code is that you're pulling all 390k documents from coll1 into your application's memory at once, then running 390k individual find-and-update operations (even wrapped in bulk, the per-operation find is expensive if you don't have proper indexing). Here are a few way better approaches:

If you're using MongoDB 4.2+, $merge is the absolute best tool for this job. It lets you handle the entire sync directly on the database server, so you don't have to transfer hundreds of thousands of documents between your app and MongoDB. This will cut down execution time drastically and eliminate the CPU/memory bloat on your app server.

Example with Mongoose: #

const options = { socketTimeoutMS: 0, keepAlive: true, reconnectTries: 30 };
mongoose.connect(`mongodb://localhost:27017/your-db-name`, options);

// Run the aggregation directly on coll1 to merge updates into coll2
Coll1Model.aggregate([
  // Filter only documents with non-empty email
  { $match: { email: { $ne: '' } } },
  // Project only the fields we need for the update
  { $project: { id: 1, email: 1, _id: 0 } },
  // Merge into coll2: match on 'id', update the email field when there's a match
  {
    $merge: {
      into: 'coll2', // Target collection name
      on: 'id', // Field to match documents
      whenMatched: { $set: { email: '$email' } }, // Update email when match found
      whenNotMatched: 'discard' // Do nothing if no match in coll2
    }
  }
])
.then(() => {
  console.log('Update completed successfully!', new Date());
})
.catch(err => {
  console.error('Error during merge:', err);
});

All processing happens inside MongoDB, no data is pulled into your app. It's optimized for large datasets and will run way faster than your current approach.

If you're stuck on an older MongoDB version that doesn't support $merge, we can still fix your approach by streaming data instead of loading everything into memory, and ensuring fast lookups with an index.

First, make sure you have an index on coll2.id! Without this, every find({ id: ... }) is a full collection scan, which is a huge reason your current code is taking forever. Create the index if you haven't:

Coll2Model.collection.createIndex({ id: 1 }); // Run this once

Optimized Batch Update Code: #

const options = { socketTimeoutMS: 0, keepAlive: true, reconnectTries: 30 };
mongoose.connect(`mongodb://localhost:27017/your-db-name`, options);

const Coll1Model = mongoose.model('coll1', collSchema);
const Coll2Model = mongoose.model('coll2', collSchema);

// Use a cursor to stream documents instead of loading all into memory
const cursor = Coll1Model.find({ email: { $ne: '' } })
  .select({ id: 1, email: 1, _id: 0 })
  .cursor();

const batchSize = 1000; // Adjust based on your server's memory capacity
let bulkOps = [];

cursor.on('data', async (doc) => {
  bulkOps.push({
    updateOne: {
      filter: { id: doc.id },
      update: { $set: { email: doc.email } }
    }
  });

  // Execute batch when we reach the batch size
  if (bulkOps.length >= batchSize) {
    await Coll2Model.bulkWrite(bulkOps);
    bulkOps = [];
    console.log(`Processed ${batchSize} documents...`);
  }
});

cursor.on('end', async () => {
  // Execute remaining operations
  if (bulkOps.length > 0) {
    await Coll2Model.bulkWrite(bulkOps);
  }
  console.log('All updates completed!', new Date());
});

cursor.on('error', (err) => {
  console.error('Cursor error:', err);
});

Using a cursor streams documents one (or a few) at a time, so you don't load 390k docs into memory. Batching the bulk writes reduces the number of round trips to MongoDB, and the index on coll2.id makes each filter query instant.

Quick Recap of Key Fixes: #

• Add an index on coll2.id: This is non-negotiable for fast lookups.
• Avoid loading all data into memory: Use cursors or server-side operations like $merge.
• Leverage server-side processing: $merge is the most efficient option if your MongoDB version supports it.

内容的提问来源于stack exchange，提问作者Arcades