如何加速mongo查询 [英] How To speed up mongo query

问题描述

我是 mongo 数据库的新手，目前正在开发使用它的网络应用

db.element.aggregate([{$匹配":{versions.branch":分支}}, {$匹配":{$或":[{文档类型":10921"}, {文档类型":10926"}, {文档类型":10943"}, {文档类型":10945"}, {文档类型":13162"}, {文档类型":1521"}, {文档类型":15281"}, {文档类型":15282"}, {文档类型":15283"}, {文档类型":15284"}, {文档类型":15291"}, {文档类型":15372"}, {文档类型":15431"}, {文档类型":1545"}, {文档类型":1550"}, {文档类型":1551"}, {文档类型":1555"}, {文档类型":15514"}, {文档类型":15516"}, {文档类型":15517"}, {文档类型":15518"}, {文档类型":15519"}, {文档类型":1553"}, {文档类型":15542"}, {文档类型":17402"}, {文档类型":19009"}, {文档类型":19055"}, {文档类型":19073"}, {文档类型":19082"}, {文档类型":19083"}, {文档类型":19812"}, {文档类型":2134"}, {文档类型":2400"}, {文档类型":2401"}]}}, {$匹配":{字首": {"$ne": "500"}}}, {$排序":{身份证":1}}, {"$unwind": "$versions"}, {$匹配":{versions.branch":分支}}, {"$unwind": "$versions.labels"}, {$组":{_ID": {"$toUpper": "$_id"},ID": {"$addToSet": "$product_name"},最大限度": {"$max": "$versions.name"},作者": {"$addToSet": "$versions.author"},标签": {"$addToSet": "$versions.labels"},分支": {"$addToSet": "$versions.branch"},等级": {"$addToSet": "$level"},vob":{"$addToSet": "$vob"},名称": {"$addToSet": "$_id"},is_product":{"$addToSet": "$is_product"},字首": {"$addToSet": "$prefix"},解密":{"$addToSet": "$doctype"},产品编号": {"$addToSet": "$versions.product_number"},地位": {"$addToSet": "$versions.status"},修订": {"$addToSet": "$versions.revision"},变体":{"$addToSet": "$versions.variant"}}}, ])

web 应用程序很慢，想要加快速度，你能建议我怎么做，创建哪些索引，查询顺序好吗?或任何其他建议.我创建了索引

db.element.ensureIndex({product_name: 1, doctype: 1, prefix: 1})

我应该使用复合索引还是其他索引?同样，当我尝试创建索引时

db.element.ensureIndex({versions.branch: 1, doctype: 1, prefix: 1})

<块引用>

Thu Jun 20 09:45:54.983 JavaScript 执行失败:SyntaxError: Unexpected token .

解决方案

您可以在此处执行的一些操作:

首先，使用 $in 而不是 $or.

其次，MongoDB 可以只为您的第一个匹配使用索引，因此您必须决定(通过尝试)两个匹配中哪一个更好.目标是尽可能快地进行查询，并减少通过管道的文档.为此，您需要执行以下操作:

首先创建三个索引:

db.element.ensureIndex( { 'versions.branch' : 1 } );db.element.ensureIndex( { 'doctype' : 1 } );db.element.ensureIndex( { 'prefix' : 1 } );

然后运行以下三个查询并注意cursor"、n"、nScanned"和ms"字段:

branch = "nameofbranch";//在这里猜测db.element.find("versions.branch": 分支).explain();db.element.find( "doctype" { $in: [ "15281", "15282" .... ] } ).explain();db.element.find( "prefix": { $ne: "500" } ).explain();

对于最后一个查询，您会注意到cursor"是BasicCursor"，因为 $ne 查询不能使用索引.

另外两个将显示ms"、n"和nScanned"的各种值.ms"是运行查询所用的时间.如果这大致相同，则查看n"和nScanned"值之间的差异.我将期望并猜测versions.branch"查询的差异为 0.对于doctype"查询，它可能不同.如果ms"不大致相同，则将最快的 $match 作为聚合管道中的 $match 子句放在首位.

如果速度(ms")相同，请检查n"值.如果前缀"查询的n"是5"，versions.branch"查询的n"是500"，那么这意味着前缀"查询的结果更好，因为更少文件被退回.在这种情况下，把它作为你的第一个 $match 子句.如果versions.branch"少得多，请使用它作为第一个 $match 子句.

I'm newbie in mongo databases, curenttly crating web app that use this

db.element.aggregate([{
  "$match": {
    "versions.branch": branch
  }
}, {
  "$match": {
    "$or": [{
      "doctype": "10921"
    }, {
      "doctype": "10926"
    }, {
      "doctype": "10943"
    }, {
      "doctype": "10945"
    }, {
      "doctype": "13162"
    }, {
      "doctype": "1521"
    }, {
      "doctype": "15281"
    }, {
      "doctype": "15282"
    }, {
      "doctype": "15283"
    }, {
      "doctype": "15284"
    }, {
      "doctype": "15291"
    }, {
      "doctype": "15372"
    }, {
      "doctype": "15431"
    }, {
      "doctype": "1545"
    }, {
      "doctype": "1550"
    }, {
      "doctype": "1551"
    }, {
      "doctype": "1555"
    }, {
      "doctype": "15514"
    }, {
      "doctype": "15516"
    }, {
      "doctype": "15517"
    }, {
      "doctype": "15518"
    }, {
      "doctype": "15519"
    }, {
      "doctype": "1553"
    }, {
      "doctype": "15542"
    }, {
      "doctype": "17402"
    }, {
      "doctype": "19009"
    }, {
      "doctype": "19055"
    }, {
      "doctype": "19073"
    }, {
      "doctype": "19082"
    }, {
      "doctype": "19083"
    }, {
      "doctype": "19812"
    }, {
      "doctype": "2134"
    }, {
      "doctype": "2400"
    }, {
      "doctype": "2401"
    }]
  }
}, {
  "$match": {
    "prefix": {
      "$ne": "500"
    }
  }
}, {
  "$sort": {
    "id": 1
  }
}, {
  "$unwind": "$versions"
}, {
  "$match": {
    "versions.branch": branch
  }
}, {
  "$unwind": "$versions.labels"
}, {
  "$group": {
    "_id": {
      "$toUpper": "$_id"
    },
    "id": {
      "$addToSet": "$product_name"
    },
    "max": {
      "$max": "$versions.name"
    },
    "author": {
      "$addToSet": "$versions.author"
    },
    "labels": {
      "$addToSet": "$versions.labels"
    },
    "branch": {
      "$addToSet": "$versions.branch"
    },
    "level": {
      "$addToSet": "$level"
    },
    "vob": {
      "$addToSet": "$vob"
    },
    "Name": {
      "$addToSet": "$_id"
    },
    "is_product": {
      "$addToSet": "$is_product"
    },
    "prefix": {
      "$addToSet": "$prefix"
    },
    "decclass": {
      "$addToSet": "$doctype"
    },
    "product_number": {
      "$addToSet": "$versions.product_number"
    },
    "status": {
      "$addToSet": "$versions.status"
    },
    "revision": {
      "$addToSet": "$versions.revision"
    },
    "variant": {
      "$addToSet": "$versions.variant"
    }
  }
}, ])

web app is slow, and want to speed up , can you suggest me what to do, Which indexes to create, is order of query good ? or any other sugesttion . I created index

db.element.ensureIndex({product_name: 1, doctype: 1, prefix: 1})

shoud I use compund index or other? Also when I tried to create index

db.element.ensureIndex({versions.branch: 1, doctype: 1, prefix: 1})

Thu Jun 20 09:45:54.983 JavaScript execution failed: SyntaxError: Unexpected token .

解决方案

A few things that you can do here:

First of all, use an $in instead of an $or.

Secondly, MongoDB can only use an index for your first match, so you will have to decide (by trying it out) which of the two matches is better. The goal is to have as fast a query, and as fewer documents going through your pipeline. For that you do the following things:

First, create the three indexes:

db.element.ensureIndex( { 'versions.branch' : 1 } );
db.element.ensureIndex( { 'doctype' : 1 } );
db.element.ensureIndex( { 'prefix' : 1 } );

Then run the following three queries and notice the "cursor", "n", "nScanned" and "ms" fields:

branch = "nameofbranch"; // guessing here
db.element.find( "versions.branch": branch ).explain();
db.element.find( "doctype" { $in: [ "15281", "15282" .... ] } ).explain();
db.element.find( "prefix": { $ne: "500" } ).explain();

For the last query, you will note that "cursor" is "BasicCursor", because a $ne query can not use the index.

The other two will show you various values for "ms", "n" and "nScanned". "ms" is the time it took to run the query. If this is approximately the same, then look at the difference between the "n" and "nScanned" values. I am going to expect and guess that the difference for the "versions.branch" query is 0. For the "doctype" query it might be different. If the "ms" is not approximately the same put the $match which was the fastest first as $match clause in your aggregation pipeline.

If the speed ("ms") is both the same, check the "n" values. If the "n" for the "prefix" query is say "5" and the "n" for the "versions.branch" query is "500" then that means that the result of the "prefix" query is better, as fewer documents are returned. In that case, put that as your first $match clause in aggregate. If the "versions.branch" is a lot less, use that one as first $match clause.

这篇关于如何加速mongo查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！