如何提高mongodb $in的性能?

Question

当我使用mongodb查询与$in，当项目长度小，它是快速的。但是当我查询大项目时，它很慢。

如何提高性能?

例如:

我有两个集合:

书

for(var i = 0 ; i < 1000000; i ++) {
db.books.insert({name: "book" + i})
}

用户。假设，user1只能检查bucket中_id的图书，所以当我们找到user1的图书列表时，我们将像这样查询:

// a. find all books ids that user1 can see,
books: db.user_access.findOne({uid: "user1"},{books: 1}); 
// books: ["id1","id2","id3" ... "idN"]
// b. query with the  access. 
db.books.find({$in: {_id: books}}).limit(10).skip(0) // pagination. 

我们从一个复杂条件的大集合中查询10文档。我们如何从设计或查询方面提高性能?

Answer 1

我已经有了工作代码，我会给你一些解释:

const { MongoClient } = require('mongodb')
const uri = 'mongodb://127.0.0.1:27017/';
const dbName = 'stackoverflow_q69548755';
const client = new MongoClient(uri);
async function run(callback) {
try {
await client.connect();
console.log('Connected successfully to server');
await callback();
} finally {
await client.close();
console.log('Connection closed')
}
}
async function runOperation() {
const db = client.db(dbName);
// users and user_books (from a cart) should go in different collections
// ! collections can be accessed later via api like: db.user_access..., db.books...
const usersCollection = db.collection('user_access'); // user details with books assigned
const booksCollection = db.collection('books');
const books = [];
const userBooks = [];
const totalBooks = 1000000;
const totalUserBooks = 10;
// * create user
const userDocument = await usersCollection.insertOne({ uid: 'user1' });
console.log(`User ${userDocument.insertedId} created`)
// * create books
for (let i = 0 ; i < totalBooks; i++) {
books.push({ name: "book" + i });
}
const bookDocuments = await booksCollection.insertMany(books);
console.log(`${books.length} books created`)
// * assign a few books to the user
const bookDocumentIds = Object.values(bookDocuments.insertedIds);
for (let i = 0; i < totalUserBooks; i++) {
userBooks.push(bookDocumentIds[pickRandomBook(totalBooks)]);
}
await usersCollection.updateOne(
{ _id: userDocument.insertedId },
{ $set: { books: userBooks }}
);
console.log(`User took ${totalUserBooks} books randomly from ${totalBooks} in the collection:nt${userBooks.join(', ')}`)
// * find user books from books collection
// we know which books are from the user!
const bookFilteredDocuments = await booksCollection.find(
{ _id: { $in: userBooks } }
).skip(0).limit(totalUserBooks).toArray();
const bookFilteredDocumentIds = bookFilteredDocuments.map(document => document._id);
console.warn(`Books filtered: nt${bookFilteredDocumentIds.join(', ')}`)
console.log('Done');
}
function pickRandomBook(maxBooks) {
// expected outputs are [0, 1, ..., maxBooks - 1]
return Math.floor(Math.random() * maxBooks);
}
run(runOperation).catch(console.dir);

我想你正在使用MongoClient来访问你的集合。我在代码中留下了一些注释，但也会在这里写下一些东西:

• 当使用find()过滤器时，filter参数需要属性名，然后操作符，如{_id: {$in: books}}而不是{$in: {_id: books}}

• 使用分页时，操作顺序为:

  2. 跳过

  您交换了跳过和限制操作的顺序。

不知道为什么你网站上的查询这么慢，但是我在我的机器上用本地数据库运行它，我在一两秒钟内得到了结果。我建议你分析一下我的代码。我认为主要问题在我指出的第一条评论中。如果你的查询太慢，比如超过一两秒你能更具体一点吗?-如果我的解决方案对你有帮助，写下到底是什么帮助了你。

Answer 2

• 在向数据库发送查询之前，在应用程序中限制和跳过图书列表。首先只发送book1~book10，然后发送book11~book12，等等

• 使用键集分页，告诉最后获取的项是什么，并获得该项之后的下一页