MongoDB没有使用索引 - 可能的整理问题?

MongoDB not using Index - possible collation issue?

We have a MongoDB Query, fully indexed, that should be scanning at most 4/5 rows. However the query appears to use one only element of the index (the integer) and ignore the string portion.

We are using a case-insensitive collation (strength=2), but it makes no difference if we specify this or not. Documentation: https://docs.mongodb.com/manual/core/index-case-insensitive/

Do collations use indexes? Is there a more efficient way of do we manually need to de-normalise? NOTE: we're not actually using any non-standard characters, the collation is specified purely for case insensitivity.

Version (supports collation):

MongoDB server version: 3.6.13
db.version() => 3.6.13
db.adminCommand( { getParameter: 1, featureCompatibilityVersion: 1 } )
    gives:
    {
            "featureCompatibilityVersion" : {
                    "version" : "3.6"
            },
            "ok" : 1,
            "operationTime" : Timestamp(1565754388, 51),
            "$clusterTime" : {
                    "clusterTime" : Timestamp(1565754388, 51),
                    "signature" : {
                            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                            "keyId" : NumberLong(0)
                    }
            }
    }

DB Structure (summarised) is

{
    "PrimaryID": (int)XXX,
    "aTables": {
        "userExt": [
            {
                "userExtPlatform": (int)XXX,
                "userExtID": (string)XXX,
                "userExtActive": (int 1 | 0)XXX,
            },
            {
                "userExtPlatform": (int)XXX,
                "userExtID": (string)XXX,
                "userExtActive": (int 1 | 0)XXX,
            },
            ...
        ], 
        "userOtherData": [
            {
                "otherDataField1": XXX,
                "otherDataField2": XXX,
            },
            ...
        ], 
        ...
    }
}

Index is set up as follows (note - collation is specified as {locale:en, strength: 2}:

{
    "v" : 2,
    "key" : {
        "aTables.userExt.userExtPlatform" : 1,
        "aTables.userExt.userExtID" : 1
    },
    "name" : "extPlatform",
    "background" : false,
    "ns" : "archive.users",
    "collation" : {
        "locale" : "en",
        "caseLevel" : false,
        "caseFirst" : "off",
        "strength" : 2,
        "numericOrdering" : false,
        "alternate" : "non-ignorable",
        "maxVariable" : "punct",
        "normalization" : false,
        "backwards" : false,
        "version" : "57.1"
    }
}

The query (trying with/without specifying collation)

use archive;

db.users.find(
    {
        "aTables.userExt.userExtPlatform": 4,
        "aTables.userExt.userExtID": "AStringValue",
        "aTables.userExt.userExtActive": 1,
        "deleted": { "$exists": false }
    }
)

db.users.find(
    {
        "aTables.userExt.userExtPlatform": 4,
        "aTables.userExt.userExtID": "AStringValue",
        "aTables.userExt.userExtActive": 1,
        "deleted": { "$exists": false }
    }
).collation( { locale: "en", strength: 2 } )

Note: Removing the 'deleted' clause makes no difference to the speed / results / explain.

Here is the explain, and it shows a vast number of keys and documents queried.

{
    "queryPlanner" : {
            "plannerVersion" : 1,
            "namespace" : "archive.users",
            "indexFilterSet" : false,
            "parsedQuery" : {
                    "$and" : [
                            {
                                    "aTables.userExt.userExtActive" : {
                                            "$eq" : 1
                                    }
                            },
                            {
                                    "aTables.userExt.userExtID" : {
                                            "$eq" : "PrivateStringRemoved"
                                    }
                            },
                            {
                                    "aTables.userExt.userExtPlatform" : {
                                            "$eq" : 4
                                    }
                            },
                            {
                                    "$nor" : [
                                            {
                                                    "deleted" : {
                                                            "$exists" : true
                                                    }
                                            }
                                    ]
                            }
                    ]
            },
            "collation" : {
                    "locale" : "en",
                    "caseLevel" : false,
                    "caseFirst" : "off",
                    "strength" : 2,
                    "numericOrdering" : false,
                    "alternate" : "non-ignorable",
                    "maxVariable" : "punct",
                    "normalization" : false,
                    "backwards" : false,
                    "version" : "57.1"
            },
            "winningPlan" : {
                    "stage" : "FETCH",
                    "filter" : {
                            "$and" : [
                                    {
                                            "aTables.userExt.userExtActive" : {
                                                    "$eq" : 1
                                            }
                                    },
                                    {
                                            "aTables.userExt.userExtID" : {
                                                    "$eq" : "PrivateStringRemoved"
                                            }
                                    },
                                    {
                                            "$nor" : [
                                                    {
                                                            "deleted" : {
                                                                    "$exists" : true
                                                            }
                                                    }
                                            ]
                                    }
                            ]
                    },
                    "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                    "aTables.userExt.userExtPlatform" : 1,
                                    "aTables.userExt.userExtID" : 1
                            },
                            "indexName" : "extPlatform",
                            "collation" : {
                                    "locale" : "en",
                                    "caseLevel" : false,
                                    "caseFirst" : "off",
                                    "strength" : 2,
                                    "numericOrdering" : false,
                                    "alternate" : "non-ignorable",
                                    "maxVariable" : "punct",
                                    "normalization" : false,
                                    "backwards" : false,
                                    "version" : "57.1"
                            },
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                    "aTables.userExt.userExtPlatform" : [
                                            "aTables.userExt"
                                    ],
                                    "aTables.userExt.userExtID" : [
                                            "aTables.userExt"
                                    ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                    "aTables.userExt.userExtPlatform" : [
                                            "[4.0, 4.0]"
                                    ],
                                    "aTables.userExt.userExtID" : [
                                            "[MinKey, MaxKey]"
                                    ]
                            }
                    }
            },
            "rejectedPlans" : [ ]
    },
    "executionStats" : {
            "executionSuccess" : true,
            "nReturned" : 0,
            "executionTimeMillis" : 1304,
            "totalKeysExamined" : 290114,
            "totalDocsExamined" : 290114,
            "executionStages" : {
                    "stage" : "FETCH",
                    "filter" : {
                            "$and" : [
                                    {
                                            "aTables.userExt.userExtActive" : {
                                                    "$eq" : 1
                                            }
                                    },
                                    {
                                            "aTables.userExt.userExtID" : {
                                                    "$eq" : "PrivateStringRemoved"
                                            }
                                    },
                                    {
                                            "$nor" : [
                                                    {
                                                            "deleted" : {
                                                                    "$exists" : true
                                                            }
                                                    }
                                            ]
                                    }
                            ]
                    },
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 1245,
                    "works" : 290115,
                    "advanced" : 0,
                    "needTime" : 290114,
                    "needYield" : 0,
                    "saveState" : 2267,
                    "restoreState" : 2267,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "docsExamined" : 290114,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 290114,
                            "executionTimeMillisEstimate" : 270,
                            "works" : 290115,
                            "advanced" : 290114,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 2267,
                            "restoreState" : 2267,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                    "aTables.userExt.userExtPlatform" : 1,
                                    "aTables.userExt.userExtID" : 1
                            },
                            "indexName" : "extPlatform",
                            "collation" : {
                                    "locale" : "en",
                                    "caseLevel" : false,
                                    "caseFirst" : "off",
                                    "strength" : 2,
                                    "numericOrdering" : false,
                                    "alternate" : "non-ignorable",
                                    "maxVariable" : "punct",
                                    "normalization" : false,
                                    "backwards" : false,
                                    "version" : "57.1"
                            },
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                    "aTables.userExt.userExtPlatform" : [
                                            "aTables.userExt"
                                    ],
                                    "aTables.userExt.userExtID" : [
                                            "aTables.userExt"
                                    ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                    "aTables.userExt.userExtPlatform" : [
                                            "[4.0, 4.0]"
                                    ],
                                    "aTables.userExt.userExtID" : [
                                            "[MinKey, MaxKey]"
                                    ]
                            },
                            "keysExamined" : 290114,
                            "seeks" : 1,
                            "dupsTested" : 290114,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                    }
            }
    },
    "serverInfo" : {
            "host" : "api-mdb-archive-03",
            "port" : 27017,
            "version" : "3.6.13",
            "gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1565753056, 9),
    "$clusterTime" : {
            "clusterTime" : Timestamp(1565753056, 9),
            "signature" : {
                    "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                    "keyId" : NumberLong(0)
            }
    }
}

The log output also confirms that it's checking a huge number of documents, and that collation comes through.

2019-08-14T03:23:53.912+0000 I COMMAND  [conn20679] command archive.users appName: "MongoDB Shell" command: find { find: "users", filter: { aTables.userExt.userExtPlatform: 4.0, aTables.userExt.userExtID: "PrivateStringRemoved", aTables.userExt.userExtActive: 1.0, deleted: { $exists: false } }, collation: { locale: "en", strength: 2.0 }, lsid: { id: UUID("3178aa31-5ee9-4a79-9848-f01c1842f542") }, $clusterTime: { clusterTime: Timestamp(1565753015, 41), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, $db: "archive" } planSummary: IXSCAN { aTables.userExt.userExtPlatform: 1, aTables.userExt.userExtID: 1 } keysExamined:289966 docsExamined:289966 cursorExhausted:1 numYields:2267 nreturned:0 reslen:228 locks:{ Global: { acquireCount: { r: 4536 } }, Database: { acquireCount: { r: 2268 } }, Collection: { acquireCount: { r: 2268 } } } protocol:op_msg 1546ms
2019-08-14T03:24:16.864+0000 I COMMAND  [conn20679] command archive.users appName: "MongoDB Shell" command: explain { explain: { find: "users", filter: { aTables.userExt.userExtPlatform: 4.0, aTables.userExt.userExtID: "PrivateStringRemoved", aTables.userExt.userExtActive: 1.0, deleted: { $exists: false } }, collation: { locale: "en", strength: 2.0 } }, verbosity: "executionStats", lsid: { id: UUID("3178aa31-5ee9-4a79-9848-f01c1842f542") }, $clusterTime: { clusterTime: Timestamp(1565753033, 128), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, $db: "archive" } numYields:2267 reslen:3578 locks:{ Global: { acquireCount: { r: 4536 } }, Database: { acquireCount: { r: 2268 } }, Collection: { acquireCount: { r: 2268 } } } protocol:op_msg 1341ms

For completeness, this is driven by PHP, but verified in Mongo CMD as above. Here is the PHP:

$aParams = [
    'aTables.userExt.userExtID' => 4,
    'aTables.userExt.userExtPlatform' => 'PrivateStringRemoved',
    'aTables.userExt.userExtActive' => 1,
    'deleted': [
        '$exists' => false
    ]
];
$aOptions = [
    'readPreference' => new \MongoDB\Driver\ReadPreference(\MongoDB\Driver\ReadPreference::RP_NEAREST),
    'skip' => $start,
    'limit' => $limit,
    'typeMap' => [
        'root' => 'array',
        'document' => 'array',
        'array' => 'array'
    ],
    'collation' => [
        'locale' => 'en',
        'strength' => 2
    ],
];
try {
    $aResults = $collectionArchive->find($aParams, $aOptions);
} catch (\Exception $exception) {
    throw new ArchiverException('Mongo Error', ArchiverRequest::ERROR_MONGO, $exception->getMessage());
}
doumeng9188
doumeng9188 是的,这是真的。它似乎过滤了“userExtPlatform”,但后来没有使用索引的userExtID部分-因此IDXScan在[MinKey,MaxKey]上。一些谷歌搜索表明我们需要“$elemMatch”,但我不知道为什么上述不起作用。
大约一年之前 回复
dongxkbjiaofea1163
dongxkbjiaofea1163 解释只显示2268行,这意味着由于索引会发生一些过滤
大约一年之前 回复
dongmouhao7438
dongmouhao7438 实际字符串类似于“g12345678901234567890”(字母g,后跟数字)。所以我们不应该需要1级匹配。事实上,对于这个指数,我们甚至不需要2级,但我们会为其他人。表格的大小目前约为2/3百万行-我中止填写它直到它被解决但它将达到20+百万-这对于扫描来说将非常慢。
大约一年之前 回复
drmgg4411
drmgg4411 你可以用整理级别1来试试这个吗?可能的不同字符类型未匹配。此外,桌子的总大小是多少?
大约一年之前 回复

1个回答

Posting here, for reassurance if anyone searches. (Based on other answers)

Having played around, the following syntax is the correct one. You need to group the final elements into $elemMatch as below.

db.users.find(
    {
        "aTables.userExt" : { 
            "$elemMatch" : {
                "userExtPlatform": 4,
                "userExtID": "AStringValue",
                "userExtActive": 1
            }
        }
    }
).collation( { locale: "en", strength: 2 } ).explain("executionStats")

As requested: here is the explain:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "archive.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "aTables.userExt" : {
                "$elemMatch" : {
                    "$and" : [
                        {
                            "userExtActive" : {
                                "$eq" : 1
                            }
                        },
                        {
                            "userExtID" : {
                                "$eq" : "AStringValue"
                            }
                        },
                        {
                            "userExtPlatform" : {
                                "$eq" : 4
                            }
                        }
                    ]
                }
            }
        },
        "collation" : {
            "locale" : "en",
            "caseLevel" : false,
            "caseFirst" : "off",
            "strength" : 2,
            "numericOrdering" : false,
            "alternate" : "non-ignorable",
            "maxVariable" : "punct",
            "normalization" : false,
            "backwards" : false,
            "version" : "57.1"
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "aTables.userExt" : {
                    "$elemMatch" : {
                        "$and" : [
                            {
                                "userExtPlatform" : {
                                    "$eq" : 4
                                }
                            },
                            {
                                "userExtID" : {
                                    "$eq" : "AStringValue"
                                }
                            },
                            {
                                "userExtActive" : {
                                    "$eq" : 1
                                }
                            }
                        ]
                    }
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "aTables.userExt.userExtPlatform" : 1,
                    "aTables.userExt.userExtID" : 1
                },
                "indexName" : "extPlatform",
                "collation" : {
                    "locale" : "en",
                    "caseLevel" : false,
                    "caseFirst" : "off",
                    "strength" : 2,
                    "numericOrdering" : false,
                    "alternate" : "non-ignorable",
                    "maxVariable" : "punct",
                    "normalization" : false,
                    "backwards" : false,
                    "version" : "57.1"
                },
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "aTables.userExt.userExtPlatform" : [
                        "aTables.userExt"
                    ],
                    "aTables.userExt.userExtID" : [
                        "aTables.userExt"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "aTables.userExt.userExtPlatform" : [
                        "[4.0, 4.0]"
                    ],
                    "aTables.userExt.userExtID" : [
                        "[\")MOK9C5S)?Q1\u0001\u0010\", \")MOK9C5S)?Q1\u0001\u0010\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 4,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "aTables.userExt" : {
                    "$elemMatch" : {
                        "$and" : [
                            {
                                "userExtPlatform" : {
                                    "$eq" : 4
                                }
                            },
                            {
                                "userExtID" : {
                                    "$eq" : "AStringValue"
                                }
                            },
                            {
                                "userExtActive" : {
                                    "$eq" : 1
                                }
                            }
                        ]
                    }
                }
            },
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 0,
            "works" : 1,
            "advanced" : 0,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 0,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 0,
                "works" : 1,
                "advanced" : 0,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "aTables.userExt.userExtPlatform" : 1,
                    "aTables.userExt.userExtID" : 1
                },
                "indexName" : "extPlatform",
                "collation" : {
                    "locale" : "en",
                    "caseLevel" : false,
                    "caseFirst" : "off",
                    "strength" : 2,
                    "numericOrdering" : false,
                    "alternate" : "non-ignorable",
                    "maxVariable" : "punct",
                    "normalization" : false,
                    "backwards" : false,
                    "version" : "57.1"
                },
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "aTables.userExt.userExtPlatform" : [
                        "aTables.userExt"
                    ],
                    "aTables.userExt.userExtID" : [
                        "aTables.userExt"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "aTables.userExt.userExtPlatform" : [
                        "[4.0, 4.0]"
                    ],
                    "aTables.userExt.userExtID" : [
                        "[\")MOK9C5S)?Q1\u0001\u0010\", \")MOK9C5S)?Q1\u0001\u0010\"]"
                    ]
                },
                "keysExamined" : 0,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "api-mdb-archive-03",
        "port" : 27017,
        "version" : "3.6.13",
        "gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1565870195, 8),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1565870195, 8),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}
dsrw29618
dsrw29618 当然 - 看编辑。 谢谢你的建议。
大约一年之前 回复
dousikuai5417
dousikuai5417 你能在这里分享解释的结果吗?
大约一年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问