mongodb - 如何在 MongoDB 聚合查询中使用 $hint？

Question

我在 ubuntu 机器上使用 mongo v3.0.1。我有 3 亿行的集合。我根据我的查询偏好创建了两个索引。

当我尝试使用解释运行聚合时，它占用了低效的索引，这就是为什么它需要多花 20-25 秒的时间。有什么方法可以放 $hint，以便我的聚合查询使用适当的索引。

$match 处于我的第一个管道阶段。我有两个索引:

1. “Host_-1_SiteType_-1”

2. “访问时间_-1_AccountId_-1_Host_-1_SiteType_-1_Extension_-1_LifeTime_-1”

我的 $match 管道就像:

{ "$match" : {
    "AccountId": accID, 
    "VisitTime": { "$lte" : today, "$gte" : last365Days },
    "$or": [
        { "$and": [
            { "Extension":{ "$in": ["chrome_0","firefox_0"] }},
            { "LifeTime": 0 }
        ]},
        {  "LifeTime": { "$gt": 1000 }}
    ],
    "Host": { "$ne": "localhost" },
    "SiteType" : { "$exists": true },
}

它使用第一个索引，而不是第二个索引。以及第一个索引在 50 秒内花费的时间，而仅使用第二个索引只需要 18 秒。

这是我的一份文件样本:

{ 
    "_id" : "2bc1143c-07e4-4c37-a020-a7485b2802a3", 
    "CreatedDate" : ISODate("2015-07-22T04:05:06.802+0000"), 
    "UpdatedDate" : ISODate("2015-07-22T05:28:26.469+0000"), 
    "AccountId" : accID, 
    "Url" : "http://www.test.com/test.html", 
    "Host" : "test.com", 
    "VisitTime" : ISODate("2014-08-12T18:08:25.813+0000"), 
    "LifeTime" : 789546.01, 
    "Status" : "closed", 
    "LocalTime" : ISODate("2014-08-12T18:08:25.813+0000"), 
    "DeviceId" : "123456789", 
    "Extension" : "firefox_0", 
    "SubSiteType" : "TestSubSite", 
    "SiteType" : "TestSite", 
    "Flag" : "1"
}

这是我的聚合解释:

{
    "stages" : [
        {
            "$cursor" : {
                "query" : {
                    "AccountId" : "accID",
                    "VisitTime" : {
                        "$lte" : "2015-07-25T18:30:00Z",
                        "$gte" : "2014-07-25T18:30:00Z"
                    },
                    "Host" : {
                        "$ne" : "localhost"
                    },
                    "SiteType" : {
                        "$exists" : true
                    },
                    "$or" : [
                        {
                            "$and" : [
                                {
                                    "Extension" : {
                                        "$in" : [
                                            "chrome_0",
                                            "firefox_0"
                                        ]
                                    }
                                },
                                {
                                    "LifeTime" : 0
                                }
                            ]
                        },
                        {
                            "LifeTime" : {
                                "$gt" : 1000
                            }
                        }
                    ]
                },
                "fields" : {
                    "Host" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "Test",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [
                            {
                                "$or" : [
                                    {
                                        "$and" : [
                                            {
                                                "LifeTime" : {
                                                    "$eq" : 0
                                                }
                                            },
                                            {
                                                "Extension" : {
                                                    "$in" : [
                                                        "chrome_0",
                                                        "firefox_0"
                                                    ]
                                                }
                                            }
                                        ]
                                    },
                                    {
                                        "LifeTime" : {
                                            "$gt" : 1000
                                        }
                                    }
                                ]
                            },
                            {
                                "$not" : {
                                    "Host" : {
                                        "$eq" : "localhost"
                                    }
                                }
                            },
                            {
                                "VisitTime" : {
                                    "$lte" : "2015-07-25T18:30:00Z"
                                }
                            },
                            {
                                "AccountId" : {
                                    "$eq" : "accID"
                                }
                            },
                            {
                                "VisitTime" :"2014-07-25T18:30:00Z"

                            },
                            {
                                "SiteType" : {
                                    "$exists" : true
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [
                                {
                                    "SiteType" : {
                                        "$exists" : true
                                    }
                                },
                                {
                                    "$or" : [
                                        {
                                            "$and" : [
                                                {
                                                    "LifeTime" : {
                                                        "$eq" : 0
                                                    }
                                                },
                                                {
                                                    "Extension" : {
                                                        "$in" : [
                                                            "chrome_0",
                                                            "firefox_0"
                                                        ]
                                                    }
                                                }
                                            ]
                                        },
                                        {
                                            "LifeTime" : {
                                                "$gt" : 1000
                                            }
                                        }
                                    ]
                                },
                                {
                                    "VisitTime" : {
                                        "$lte" : "2015-07-25T18:30:00Z"
                                    }
                                },
                                {
                                    "AccountId" : {
                                        "$eq" : "accID"
                                    }
                                },
                                {
                                    "VisitTime" : {
                                        "$gte" : "2014-07-25T18:30:00Z"
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "Host" : -1,
                                "SiteType" : -1
                            },
                            "indexName" : "Host_-1_SiteType_-1",
                            "isMultiKey" : false,
                            "direction" : "forward",
                            "indexBounds" : {
                                "Host" : [
                                    "[MaxKey, \"localhost\")",
                                    "(\"localhost\", MinKey]"
                                ],
                                "SiteType" : [
                                    "[MaxKey, MinKey]"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : [
                        {
                            "stage" : "FETCH",
                            "filter" : {
                                "$and" : [
                                    {
                                        "SiteType" : {
                                            "$exists" : true
                                        }
                                    },
                                    {
                                        "$or" : [
                                            {
                                                "$and" : [
                                                    {
                                                        "LifeTime" : {
                                                            "$eq" : 0
                                                        }
                                                    },
                                                    {
                                                        "Extension" : {
                                                            "$in" : [
                                                                "chrome_0",
                                                                "firefox_0"
                                                            ]
                                                        }
                                                    }
                                                ]
                                            },
                                            {
                                                "LifeTime" : {
                                                    "$gt" : 1000
                                                }
                                            }
                                        ]
                                    }
                                ]
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "VisitTime" : -1,
                                    "AccountId" : -1,
                                    "Host" : -1,
                                    "SiteType" : -1,
                                    "Extension" : -1,
                                    "LifeTime" : -1
                                },
                                "indexName" : "VisitTime_-1_AccountId_-1_Host_-1_SiteType_-1_Extension_-1_LifeTime_-1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "VisitTime" : [
                                        "[new Date(1437849000000), new Date(1406313000000)]"
                                    ],
                                    "AccountId" : [
                                        "[\"accID\", \"accID\"]"
                                    ],
                                    "Host" : [
                                        "[MaxKey, \"localhost\")",
                                        "(\"localhost\", MinKey]"
                                    ],
                                    "SiteType" : [
                                        "[MaxKey, MinKey]"
                                    ],
                                    "Extension" : [
                                        "[MaxKey, MinKey]"
                                    ],
                                    "LifeTime" : [
                                        "[MaxKey, MinKey]"
                                    ]
                                }
                            }
                        }
                    ]
                }
            }
        },
        {
            "$group" : {
                "_id" : "$Host",
                "Count" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            }
        },
        {
            "$sort" : {
                "sortKey" : {
                    "Count" : -1
                },
                "limit" : 5
            }
        },
        {
            "$project" : {
                "_id" : false,
                "Host" : "$_id",
                "TotalVisit" : "$Count"
            }
        }
    ],
    "ok" : 1
}

Answer 1

2019 年答案

从 MongoDB 3.6 版开始

来自documentation ，您可以通过以下方式添加聚合提示:

db.collection.aggregate(pipeline, {hint: "index_name"})

如果您想查看说明，只需添加说明，就像没有 hint

一样