为此查询创建索引的策略是什么？ [英] What's the strategy for creating index for this query?

问题描述

我有一个每个帖子的评论模型，

I have a comment model for each thread,

const CommentSchema = new mongoose.Schema({
    author: { type: ObjectID, required: true, ref: 'User' },
    thread: { type: ObjectID, required: true, ref: 'Thread' },
    parent: { type: ObjectID, required: true, ref: 'Comment' },
    text: { type: String, required: true },
}, {
    timestamps: true,
});

除了通过 _id 的单一查询外，我想通过这种方式查询数据库：

Besides a single query via _id, I want to query the database via this way:

范围查询

const query = {
    thread: req.query.threadID,
    _id: { $gt: req.query.startFrom }
};

CommentModel.find(query).limit(req.query.limit);

我的目的是找到与线程相关的注释，然后获取结果的一部分。看来这个查询按预期工作。我的问题是：

My intention here is to find comments which related to a thread then get part of the result. It seems this query works as expected. My questions are:

1. 这是满足我要求的正确方法吗？

1. Is this the right way to fulfill my requirement?

如何正确索引字段？这是一个复合索引还是我需要分别索引每个字段？我检查了 explain（）的结果，只要其中一个查询字段包含索引， inputStage.stage 总是有 IXSCAN 而不是 COLLSCAN ？这是检查查询性能的关键信息吗？

How to proper index the fields? Is this a compound index or I need to separate indexing each field? I checked the result of explain(), it seems as long as one of the query fields contains an index, the inputStage.stage will always have IXSCAN rather than COLLSCAN? Is this the key information to check the performace of the query?

这是否意味着每次我需要根据一个字段查找时，我需要制作这些字段的索引？假设我想搜索作者发布给特定帖子的所有评论。

Does it mean that every time I need to find based on one field, I need to make an index for these fields? Let's say that I want to search all the comments that are posted by an author to a specific thread.

代码如：这个：

const query = {
    thread: req.query.threadID,
    author: req.query.authorID,
};

我是否需要为此要求创建复合索引？

Do I need to create a compound index for this requirement?

推荐答案

如果你想通过多个字段进行查询，那么你必须创建复合索引。

If you want to query by multiple fields then you have to create compound index.

例如

const query = {
    thread: req.query.threadID,
    author: req.query.authorID,
};

如果你想使用这个查询，你必须创建复合索引，如：

if you want to use this query then you have to create compound index like :

db.comments.createIndex( { "thread": 1, "author": 1 } );

然后，索引支持对项目字段以及项目和库存字段的查询：

Then that is, the index supports queries on the item field as well as both item and stock fields:

db.comments.find( { thread: "threadName" } )
db.comments.find( { thread: "threadName", author: "authorName" } 

但不支持此

db.comments.find( { author: "authorName", thread: "threadName" } 

如果你为创建索引{thread：1，author：1，parent：1} 然后是下面的命令查询支持索引

say if you create index for { "thread": 1, "author": 1, "parent": 1 } then for bellow ordered query support index

线程字段，

主题字段和作者字段，

主题字段和作者字段和 parent 字段。

但不支持以下命令

autho r 字段，

父字段，或

作者和父字段。

如需更多阅读本文

这篇关于为此查询创建索引的策略是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！