MongoDB查询效率疑问：用户集合越大，按公司查用户是否越低效？

Great question! Your core intuition here is mostly correct, but there are critical nuances tied to indexing, data structure choices, and access patterns that determine exactly how each query scales. Let’s break this down:

1. Why your initial guess holds weight (most of the time) #

Your assumption that querying the users collection directly gets slower as the collection grows is rooted in how MongoDB handles unindexed queries:

• Without an index on the companyId (or whatever field links users to their company), MongoDB performs a full collection scan to find matching users. As users grows into millions or billions of documents, this scan will take exponentially longer—each new user adds more data to sift through.
• Even with an index, there’s a scaling limit: if the index on companyId can’t fit entirely in MongoDB’s working set (memory), the database will have to read parts of the index from disk, which slows down query performance. That said, indexed queries scale far better than full scans (O(log n) vs O(n) complexity).

Here’s what the direct users query looks like with and without an index:

// Unindexed query (slow for large users collections)
db.users.find({ companyId: ObjectId("your-company-id") })

// With an index (far more scalable)
db.users.createIndex({ companyId: 1 })
db.users.find({ companyId: ObjectId("your-company-id") })

2. The second approach: Querying companies first #

This method’s efficiency depends entirely on how you’ve structured the company-user relationship:

• Embedded user arrays: If you embed user documents directly in the companies collection, querying the company is fast (since you’re fetching a single document), but this breaks down if a company has hundreds/thousands of users. MongoDB’s BSON document size limit (16MB) will cap how many users you can embed, and updating a user’s data requires finding and modifying the parent company document—slow and unwieldy for large datasets.
• User ID references: If you store an array of user _ids in the companies document, you’ll first fetch the company, then query users with $in on those IDs. Since _id has a default unique index, this second query is extremely fast (even for large users collections), because it leverages the efficient _id index. The catch? If a company has tens of thousands of users, passing a huge $in array can add overhead, but this is still often faster than an unindexed scan on users.

Example of the reference-based approach with aggregation:

db.companies.aggregate([
  { $match: { _id: ObjectId("your-company-id") } },
  { $lookup: {
      from: "users",
      localField: "userIds",
      foreignField: "_id",
      as: "company_users"
    }
  }
])

3. When your guess might not hold #

If you’ve added a proper index on users.companyId, the direct query won’t degrade nearly as quickly as you might expect. For most real-world scenarios, an indexed users query will stay fast even as the collection grows, especially if the index fits in memory. The second approach only becomes clearly better if:

• You regularly need to fetch company metadata alongside its users (so you avoid two separate queries).
• The users collection is massive, and your working set can’t hold the companyId index (forcing disk reads for the index).

Final Verdict #

Your core推测 (guess) is correct in the unindexed scenario—direct users queries get drastically slower as the collection grows. With proper indexing, however, the performance degradation is much more gradual. The second approach shines when company user counts are small or you need company data alongside users, but it has its own scaling limits for large user groups.

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