Mongo查询需要很长时间.如何使其更快? [英] Mongo query take a long time. How make it more fast?

问题描述

我在node js中使用mongoose驱动程序.我的架构:

let sendResultSchema = mongoose.Schema({
  emailId: String,      email: String,              
  letterId: String,     sendedFrom: String,
  resultMsg: String,    owner: String,              
  created: Date,        result: Boolean,
  tag: String,          tryNum: Number,
  clickHash: String,    links: [String]
})
sendResultSchema.index({emailId: 1, letterId: 1, result: 1, owner: 1, tag: 1, clickHash: 1})
let sendResultModel = mongoose.model('sendresult', sendResultSchema)

sendresult集合具有641000个文档.

此查询执行〜0.5秒.

 db.sendresults.find({"tag" : "tagValue", "letterId" : "5ad630b5949bb02ea07d15d1"}).sort({emailId: -1}).limit(1)

我认为它必须执行得更快.您可以在此处

如何使此查询更快?

解决方案

索引将需要覆盖查询的所有部分(相等部分，排序部分和范围部分).这是因为在典型的find()查询中，MongoDB仅使用一个索引.例如，通常不对相等部分使用一个索引，对排序部分使用另一个索引.

通常，索引中的字段顺序需要遵循等于->排序->范围的模式.

优化MongoDB复合索引中对此进行了详细说明.. >

对于您的查询，相等部分为tag:..., letterId:...，排序部分为emailId:-1.您的查询中没有范围部分.

使用此模式，您需要的复合索引为:

db.test.createIndex({tag:1, letterId:1, emailId:-1})

让我们尝试确认使用该索引可以带来多少性能提升.

测试数据

为了确认索引的适用性，我使用 mgeneratejs 将一百万条记录插入了测试数据库，这是使用模板创建随机文档的工具.

根据您的示例，我正在使用的mgeneratejs模板是:

$ cat template.json
{
  "emailId": "$hash",
  "email": "$email",
  "letterId": "$hash",
  "sendedFrom": "$email",
  "resultMsg": "$word",
  "owner": "$name",
  "created": "$date",
  "result": "$bool",
  "tag": "$word",
  "tryNum": {"$integer": {"min": 0, "max": 1e3}},
  "clickHash": "$word",
  "links": {"$array": {"of": "$url", "number": {"$integer": {"min": 1, "max": 5}}}}
}

并将100万个随机文档导入MongoDB:

$ mgeneratejs template.json -n 1000000 | mongoimport -d test -c test

测试1:非最佳索引

然后，我创建您拥有的索引，并尝试查找不存在的文档，并收集了10个运行查询，并且该查询只包含该索引:

> db.test.createIndex({emailId: 1, letterId: 1, result: 1, owner: 1, tag: 1, clickHash: 1})

> db.test.find({"tag" : "xyz", "letterId" : "abc"}).sort({emailId: -1}).limit(1)
Fetched 0 record(s) in 3069ms
Fetched 0 record(s) in 2924ms
Fetched 0 record(s) in 2923ms
Fetched 0 record(s) in 3013ms
Fetched 0 record(s) in 2917ms
Fetched 0 record(s) in 2961ms
Fetched 0 record(s) in 2882ms
Fetched 0 record(s) in 2870ms
Fetched 0 record(s) in 2969ms
Fetched 0 record(s) in 2863ms

因此使用该索引，查询的响应时间并不长，大多数执行接近3秒.

测试2:相等->排序->范围索引

通过添加最佳的平等->排序->范围索引:

> db.test.createIndex({tag:1, letterId:1, emailId:-1})

> db.test.find({"tag" : "xyz", "letterId" : "abc"}).sort({emailId: -1}).limit(1)
Fetched 0 record(s) in 2ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 3ms

相比之下，使用最佳索引可以显着提高性能.没有超过3ms的时间返回查询，绝大多数时间是1ms.

I use mongoose driver in node js. My schema:

let sendResultSchema = mongoose.Schema({
  emailId: String,      email: String,              
  letterId: String,     sendedFrom: String,
  resultMsg: String,    owner: String,              
  created: Date,        result: Boolean,
  tag: String,          tryNum: Number,
  clickHash: String,    links: [String]
})
sendResultSchema.index({emailId: 1, letterId: 1, result: 1, owner: 1, tag: 1, clickHash: 1})
let sendResultModel = mongoose.model('sendresult', sendResultSchema)

sendresult collection have a 641000 documents.

this query executing ~0.5 seconds.

 db.sendresults.find({"tag" : "tagValue", "letterId" : "5ad630b5949bb02ea07d15d1"}).sort({emailId: -1}).limit(1)

I think it must execute more fast. You can see explain of this query here

How make this query more fast?

解决方案

The index would need to cover all part of the query (equality part, sort part, and range part). This is because in a typical find() query, MongoDB only uses one index. E.g., it generally doesn't use one index for the equality part, and another index for the sort part.

In general, the sequence of fields in the index needs to follow the pattern of equality -> sort -> range.

This is described in detail in Optimizing MongoDB Compound Indexes.

For your query, the equality part is tag:..., letterId:... and the sort part is emailId:-1. There is no range part in your query.

Using this pattern, the compound index you need is:

db.test.createIndex({tag:1, letterId:1, emailId:-1})

Let's try to confirm how much performance improvement we can get using this index.

Test data

To confirm the suitability of the index, I inserted 1 million records into a test database using mgeneratejs, which is a tool to create a random document using a template.

Based on your example, the mgeneratejs template I'm using is:

$ cat template.json
{
  "emailId": "$hash",
  "email": "$email",
  "letterId": "$hash",
  "sendedFrom": "$email",
  "resultMsg": "$word",
  "owner": "$name",
  "created": "$date",
  "result": "$bool",
  "tag": "$word",
  "tryNum": {"$integer": {"min": 0, "max": 1e3}},
  "clickHash": "$word",
  "links": {"$array": {"of": "$url", "number": {"$integer": {"min": 1, "max": 5}}}}
}

and imported 1 million random documents into MongoDB:

$ mgeneratejs template.json -n 1000000 | mongoimport -d test -c test

Test 1: non-optimal index

Then I create the index you have, and tried to find a non-existent document and gathered 10 runs of the query with the collection containing only this index:

> db.test.createIndex({emailId: 1, letterId: 1, result: 1, owner: 1, tag: 1, clickHash: 1})

> db.test.find({"tag" : "xyz", "letterId" : "abc"}).sort({emailId: -1}).limit(1)
Fetched 0 record(s) in 3069ms
Fetched 0 record(s) in 2924ms
Fetched 0 record(s) in 2923ms
Fetched 0 record(s) in 3013ms
Fetched 0 record(s) in 2917ms
Fetched 0 record(s) in 2961ms
Fetched 0 record(s) in 2882ms
Fetched 0 record(s) in 2870ms
Fetched 0 record(s) in 2969ms
Fetched 0 record(s) in 2863ms

so using that index, the response times of the query are not great, with most execution close to 3 seconds.

Test 2: equality -> sort -> range index

By adding the optimal equality -> sort -> range index:

> db.test.createIndex({tag:1, letterId:1, emailId:-1})

> db.test.find({"tag" : "xyz", "letterId" : "abc"}).sort({emailId: -1}).limit(1)
Fetched 0 record(s) in 2ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 1ms
Fetched 0 record(s) in 3ms

In contrast, using the optimal index, performance was markedly improved. No query returned in more than 3ms, with the great majority of time it returns in 1ms.

这篇关于Mongo查询需要很长时间.如何使其更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！