Mongoid WinningPlan不使用复合索引 [英] Mongoid winningPlan does not use compound index
问题描述
我的复合索引如下.
index({ account_id: 1, is_private: 1, visible_in_list: 1, sent_at: -1, user_id: 1, status: 1, type: 1, 'tracking.last_opened_at' => -1 }, {name: 'email_page_index'})
然后我有一个包含这些确切字段的查询，
Then I have a query with these exact fields,
selector:
{"account_id"=>BSON::ObjectId('id'), "is_private"=>false, "visible_in_list"=>{:$in=>[true, false]}, "status"=>{:$in=>["ok", "queued", "processing", "failed"]}, "sent_at"=>{"$lte"=>2021-03-22 15:29:18 UTC}, "tracking.last_opened_at"=>{"$gt"=>1921-03-22 15:29:18 UTC}, "user_id"=>BSON::ObjectId('id')}
options: {:sort=>{"tracking.last_opened_at"=>-1}}
winningPlan如下
The winningPlan is the following
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"account_id": {
"$eq": {
"$oid": "objectid"
}
}
},
{
"is_private": {
"$eq": false
}
},
{
"sent_at": {
"$lte": "2021-03-22T14:06:10.000Z"
}
},
{
"tracking.last_opened_at": {
"$gt": "1921-03-22T14:06:10.716Z"
}
},
{
"status": {
"$in": [
"failed",
"ok",
"processing",
"queued"
]
}
},
{
"visible_in_list": {
"$in": [
false,
true
]
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"user_id": 1
},
"indexName": "user_id_1",
"isMultiKey": false,
"multiKeyPaths": {
"user_id": []
},.....
拒绝的计划具有复合索引，其格式如下
And the rejected plan has the compound index and forms as follows
"rejectedPlans": [
{
"stage": "FETCH",
"inputStage": {
"stage": "SORT",
"sortPattern": {
"tracking.last_opened_at": -1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"account_id": 1,
"is_private": 1,
"visible_in_list": 1,
"sent_at": -1,
"user_id": 1,
"status": 1,
"type": 1,
"tracking.last_opened_at": -1
},
"indexName": "email_page_index",
"isMultiKey": false,
"multiKeyPaths": {
"account_id": [],
"is_private": [],
"visible_in_list": [],
"sent_at": [],
"user_id": [],
"status": [],
"type": [],
"tracking.last_opened_at": []
},
"isUnique": false,
问题在于winningPlan速度慢，如果蒙古人选择复合指数会更好吗?有办法强制吗?另外，如何查看每个单独STAGE的执行时间?
The problem is that the winningPlan is slow, wouldn't be better if mongoid choose the compound index? Is there a way to force it? Also, how can I see the execution time for each separate STAGE?
推荐答案
我正在发布一些信息，这些信息可以帮助解决性能问题并使用适当的索引.请注意，这可能不是解决方案(问题尚待讨论).
I am posting some information that can help resolve the issue of performance and use an appropriate index. Please note this may not be the solution (and the issue is open to discussion).
...此外，如何查看每个单独STAGE的执行时间?
...Also, how can I see the execution time for each separate STAGE?
为此，请使用使用 问题是winningPlan速度慢，如果蒙古族选择复合指数吗?有办法强制吗?
The problem is that the winningPlan is slow, wouldn't be better if
mongoid choose the compound index? Is there a way to force it? 发布时，该计划显示一个 As posted the plans show a 单个复合索引可用于带有filter + sort操作的查询.那将是有效的索引和查询.但是，它要求以某种方式定义复合索引-需要遵循一些规则.请参阅 A single compound index can be used for a query with a filter+sort operations. That would be an efficient index and query. But, it requires that the compound index be defined in a certain way - some rules need to be followed. See this topic on Sort and Non-prefix Subset of an Index - as is the case in this post. I quote the example from the documentation for illustration: 假设有一个复合索引: Suppose there is a compound index: 请注意，查询过滤器具有三个具有等于条件的字段(不存在 Note the query filter has three fields with equality condition (there are no 在您的情况下，此功能无法从发布的查询中应用.因此，要寻求解决方案，您可能必须定义一个新索引，以便利用规则 索引的排序和非前缀子集 . In your case, this cannot be applied from the posted query. So, to work towards a solution you may have to define a new index so as to take advantage of the rule Sort and Non-prefix Subset of an Index. 有可能吗?这取决于您的应用程序和用例.我有这样的想法，可能会有所帮助.创建一个如下所示的复合索引，并查看其工作原理: Is it possible? It depends upon your application and the use case. I have an idea like this and it may help. Create a compound index like the follows and see how it works: 我认为查询中有条件 I think having a condition 还包括一些细节，例如MongoDB服务器的版本，集合的大小和一些平台细节.通常，查询性能取决于许多因素，包括索引，RAM内存，数据的大小和类型以及对数据的操作类型. Also, include some details like the version of the MongoDB server, the size of collection and some platform details. In general, query performance depends upon many factors, including, indexes, RAM memory, size and type of data, and the kind of operations on the data. ESR规则:当对具有多个过滤条件和排序的查询使用复合索引时，有时平等排序范围规则对于优化查询很有用.请参阅以下有关这种情况的文章: MongoDB-在对范围查询进行排序和限制时未使用索引 The ESR Rule:
When using compound index for a query with multiple filter conditions and sort, sometimes the Equality Sort Range rule is useful for optimizing the query. See the following post with such a scenario: MongoDB - Index not being used when sorting and limiting on ranged query 这篇关于Mongoid WinningPlan不使用复合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ executionStats
阶段":"SORT_KEY_GENERATOR" ，这表明正在内存中执行 sort 操作(即未使用排序的索引).这将是性能下降的原因之一(或主要原因).那么，如何使查询和排序使用索引?"stage": "SORT_KEY_GENERATOR", implying that the sort operation is being performed in the memory (that is not using an index for the sort). That would be one (or main) of the reasons for the slow performance. So, how to make the query and the sort use the index? {a:1，b:1，c:1，d:1} 并且，所有字段都在带有filter + sort的查询中使用.理想的查询是具有如下的filter + sort:{ a: 1, b: 1, c: 1, d: 1 }
And, all the fields are used in a query with filter+sort. The ideal query is, to have a filter+sort as follows:db.test.find( { a: "val1", b: "val2", c: 1949 } ).sort( { d: 1 })
$ gt ， $ lt 等).然后，查询的排序具有索引的最后一个字段 d .这是将索引用于查询的过滤器以及排序操作的理想情况.$gt, $lt, etc.). Then the query's sort has the last field d of the index. This is the ideal situation where the index will be used for the query''s filter as well as sort operations.account_id: 1,
is_private: 1
visible_in_list: 1,
status: 1,
user_id: 1,
'tracking.last_opened_at': -1
"tracking.last_opened_at" => {"$ gt" => 1921-03-22 15:29:18 UTC} 的过滤器可能无法帮助使用索引."tracking.last_opened_at"=>{"$gt"=>1921-03-22 15:29:18 UTC}, in the query''s filter may not help for the usage of the index.
