为聚合框架创建覆盖索引 [英] creating covered index for aggregation framework

问题描述

我在为查询创建索引时遇到问题，无法在网络上找到任何类似的解决方案，因此也许有些人会帮助我。

I have a problem with creating index for my query and can't find any similar solution on the web, so maybe some of you will help me.

要简化问题，假设我们的手机具有某些属性，

To simplify problem let's say we have Phones with some attributes,

{
  "type":"Samsung",
  "model":"S3",
  "attributes":[{
     "value":"100",
     "name":"BatteryLife"
   },{
     "value":"200$",
     "name":"Price"
   }
}

索引为：{ type：1， attributes.value：1}

With index: {"type":1, "attributes.value":1}

我们有数百万个每种类型的电话，我想查找具有给定属性的给定类型的电话，我的查询如下：

We have millions of phones for every type and i want to find phones for given type that have given attributes, my query looks like:

db.Phone.aggregate([ 
{ "$match" : { "type" : "Samsung"}} , 
{ "$match" : { "attributes" : { "$all" : [ 
    { "value" : "100", "name" : "BatteryLife" } , 
    { "value" : "200$", "name" : "Price"}
                              ]}
             }
 }
])

它有效！
问题是此查询效率很低，因为它仅使用索引的第一部分，即类型（并且我有数百万种电话，每种类型），并且不使用 attributes.value '部分（类型+ attribute.value几乎是唯一的，因此它将大大降低复杂性）。

And it works ! The problem is that this query is highly inefficient, beacuse it use only first part of my index, that is "type"(and i have millions of phones of every type), and doesn't use 'attributes.value' part (type + attributes.value is almost unique, so it would reduce complexity significantly).

@Edit
感谢Neil Lunn，我知道这是因为索引仅在我的第一个比赛中使用，所以我必须更改查询。

@Edit Thanks to Neil Lunn i know it's because index is used only in my first match, so i have to change my query.

@ Edit2
我想我找到了解决方法：

@Edit2 I think i found solution:

db.Phone.aggregate([
{$match: {
    $and: [ 
        {type: "Samsung"}, 
        {attributes: {
           $all: [
                { "value":"100", "type" : "BatteryLife" },
                { "value":"200$", "type" : "Price" }
           ] 
        }}
    ]}
}])

+ db.Phone.ensureIndex（{type：1，attribute：1}），似乎有效。我想我们现在可以关闭。感谢您提供有关$ match的技巧。

+db.Phone.ensureIndex({type:1, attributes:1}), seems to work. I think we can close now. Thanks for tip about $match.

推荐答案

要充分利用索引，您需要尽早进行$ match使用索引中所有字段的管道。并避免使用 $ and 运算符，因为它是不必要的，并且在当前（2.4）版本中会导致索引无法充分利用（幸运的是，在即将发布的2.6中已修复）。

To get the most out of the index you need to have a $match early enough in the pipeline that uses all the fields in the index. And avoid using $and operator since it's unnecessary and in the current (2.4) version can cause an index not to be fully utilized (luckily fixed for the upcoming 2.6).

但是，查询不是很正确，因为您需要使用 $ elemMatch 来确保使用相同的元素满足名称和值字段。

However, the query is not quite correct as you need to use $elemMatch to make sure the same element is used to satisfy the name and value fields.

您的查询应为：

db.Phone.aggregate([
{$match: {  type: "Samsung", 
           attributes: { $all: [
                {$elemMatch: {"value":"100", "type" : "BatteryLife" }},
                {$elemMatch: {"value":"200$", "type" : "Price" }}
           ] }
        }
}]);

现在，不是将成为覆盖查询，因为属性值和名称是嵌入的，更不用说名称不在索引中了。

Now, it's not going to be a covered query, since the attributes.value and name are embedded, not to mention the fact that name is not in the index.

您需要索引为 { type ：1， attributes.value：1， attributes.name：1} 以获得最佳性能，尽管仍然无法涵盖，但它将比现在更具选择性。

You need the index to be {"type":1, "attributes.value":1, "attributes.name":1} for best performance, though it still won't be covered, it'll be much more selective than now.

这篇关于为聚合框架创建覆盖索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！