mongodb多键索引似乎在排序时未使用 [英] mongodb multikey index seems like not being used when sort

问题描述

让我们假设我有tx_collection，它具有如下3个文档

Let's assume that I have tx_collection which has 3 documents like below

{
    "block_number": 1,
    "value": 122
    "transfers": [
        {
            "from": "foo1", 
            "to": "bar1", 
            "amount": 111
        },
        {
            "from": "foo3", 
            "to": "bar3", 
            "amount": 11
        },
    ]
},
{
    "block_number": 2,
    "value": 88
    "transfers": [
        {
            "from": "foo11", 
            "to": "bar11", 
            "amount": 33
        },
        {
            "from": "foo22", 
            "to": "bar22", 
            "amount": 55
        },
    ]
},
{
    "block_number": 3,
    "value": 233
    "transfers": [
        {
            "from": "foo1", 
            "to": "bar1", 
            "amount": 33
        },
        {
            "from": "foo3", 
            "to": "bar3", 
            "amount": 200
        },
    ]
}

对于性能问题，我在transfers.amount

当我按transfers.amount排序时，

db.getCollection('tx_transaction').find({}).sort({"transfers.amount":-1})

我希望文档的顺序是按子字段transfers.amount的最大值排序的，例如

what I expected order of documents is sorted by max value of subfield transfers.amount like

{
    "block_number": 3,
    "value": 233
    "transfers": [
        {
            "from": "foo1", 
            "to": "bar1", 
            "amount": 33
        },
        {
            "from": "foo3", 
            "to": "bar3", 
            "amount": 200
        },
    ]
},
{
    "block_number": 1,
    "value": 122
    "transfers": [
        {
            "from": "foo1", 
            "to": "bar1", 
            "amount": 111
        },
        {
            "from": "foo3", 
            "to": "bar3", 
            "amount": 11
        },
    ]
},
{
    "block_number": 2,
    "value": 88
    "transfers": [
        {
            "from": "foo11", 
            "to": "bar11", 
            "amount": 33
        },
        {
            "from": "foo22", 
            "to": "bar22", 
            "amount": 55
        },
    ]
}

由于只有3个文档，因此排序效果很好.排序顺序是我期望的3号块-> 1号块-> 2号块

The sort works well since there are only 3 documents. Sorted order is block number 3 -> block number 1 -> block_number 2 which I expected

按摩就像

"errmsg" : "Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.",

似乎在排序时不使用多键索引.

您知道为什么会引发此错误消息吗?

It seems that multikey index is not used when sort.

do you have any idea why this error message is thrown?

JFYI.

• 我的mongodb版本是3.6.3
• tx_collection被分片

推荐答案

从MongoDB 3.6及更高版本开始，我认为这是可以预期的，如

As of MongoDB 3.6 and newer, I think this is to be expected as mentioned in Use Indexes to Sort Query Results where it stated:

由于MongoDB 3.6中对数组字段的排序行为发生了变化，当对使用多键索引建立索引的数组进行排序时，查询计划包括一个阻塞的SORT阶段.新的排序行为可能会对性能产生负面影响.

As a result of changes to sorting behavior on array fields in MongoDB 3.6, when sorting on an array indexed with a multikey index the query plan includes a blocking SORT stage. The new sorting behavior may negatively impact performance.

在阻塞式SORT中，所有输入必须先由排序步骤使用，然后才能产生输出.在非阻塞或索引排序中，排序步骤扫描索引以按请求的顺序产生结果.

In a blocking SORT, all input must be consumed by the sort step before it can produce output. In a non-blocking, or indexed sort, the sort step scans the index to produce results in the requested order.

换句话说，阻塞排序"是指表示SORT_KEY_GENERATOR阶段的存在，该阶段表示内存中排序.由于 SERVER-19402 的存在，该版本与MongoDB 3.6之前的版本有所不同.对数组字段进行排序.

In other words, "blocking sort" means the presence of the SORT_KEY_GENERATOR stage, the stage that means in-memory sort. This was changed from pre-3.6 MongoDB due to SERVER-19402 to address the inconsistencies around sorting an array field.

有一张可以改善这种情况的票证: SERVER-31898 .不幸的是，目前还没有针对此行为的解决方法.

There is a ticket to improve this situation: SERVER-31898. Unfortunately there is no workaround for this behaviour just yet.

这篇关于mongodb多键索引似乎在排序时未使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！