问题：

MongoDB不使用索引-可能的排序问题？

元叶秋

2023-03-14

我们有一个完全索引的MongoDB查询，最多可以扫描4/5行。然而，查询似乎只使用索引的一个元素（整数），而忽略了字符串部分。

我们使用的是不区分大小写的排序规则（强度=2），但指定与否没有任何区别。文档：https://docs.mongodb.com/manual/core/index-case-insensitive/

排序规则是否使用索引？有没有更有效的方法可以让我们手动去归一化？注意：我们实际上没有使用任何非标准字符，排序规则纯粹是为区分大小写而指定的。

版本（支持排序）：

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结构（总结）为

{
    "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,
            },
            ...
        ], 
        ...
    }
}

索引设置如下（注意-排序规则指定为{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"
    }
}

查询（尝试使用/不指定排序规则）

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 } )

注意：删除“deleted”子句对速度/结果/解释没有影响。

这是解释，它显示了查询的大量密钥和文档。

{
    "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)
            }
    }
}

日志输出还确认它正在检查大量文档，并进行了排序。

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

为了完整性起见，这是由PHP驱动的，但在Mongo CMD中进行了验证，如上所述。以下是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());
}

共有1个答案

宰父存

2023-03-14

在这里发帖，如果有人搜索，请放心。（基于其他答案）

玩转之后，以下语法是正确的。您需要将最终元素分组到$elemMatch中，如下所示。

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

根据要求：以下是解释：

{
    "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)
        }
    }
}

类似资料：

MongoDB不使用多键索引

我有一个MongoDB版本3.0.3集合，其中包含具有2个字段的文档：数字长id 我在数组上构建了一个多键索引，并通过使用方法确认该索引存在并且是多键的。但是，当我查询数组中特定字段的2个范围的交集时，Mongo不使用此索引，尽管集合中有1,000,000个文档。我可以从方法的输出中看到这一点。更奇怪的是，当我使用指定索引时，Mongo遍历了所有1,000,000个文档和60,000,000个索
MongoDB不使用复合索引_id

我在MongoDB有一个收藏，里面有以下文档。现在，我尝试使用以下查询查询一些文档。根据我的理解，由于\u id是一个复合字段，并且Mongo总是在\u id上维护一个索引，因此要回答上述查询，Mongo应该在“\u id”上使用索引。然而，上述问题的答案如下：可以观察到，MongoDB正在对DB进行一次完整的扫描，以查找少量文档。我不知道这里到底怎么了。我试图改变查询的顺序，但结果相同。
SQL 排序索引

本文向大家介绍SQL 排序索引，包括了SQL 排序索引的使用技巧和注意事项，需要的朋友参考一下示例如果您使用按检索方式排序的索引，则该SELECT语句在检索时不会进行其他排序。执行查询时数据库系统不会执行其他排序，因为它可以按该顺序进行索引查找。
MongoDB的索引

本文向大家介绍MongoDB的索引，包括了MongoDB的索引的使用技巧和注意事项，需要的朋友参考一下 1、简介它就像是一本书的目录，如果没有它，我们就需要对整个书籍进行查找来获取需要的结果，即所说的全盘扫描；而有了目录（索引）之后就可以通过它帮我们定位到目标所在的位置，快速的获取我们想要的结果。 2、演示第一步，向用户集合users中插入100W条数据 LZ的渣渣I3和4G内存总共耗时了4
mysql是否使用索引进行排序？''

问题内容：如果排序列上有索引，排序是否使用mysql索引？索引还用于其他什么用途？它对列的组合索引和单独索引有什么区别？问题答案：是的，当按排序列排序时，MySQL使用索引对信息进行排序。另外，如果在添加到 SELECT 子句的所有列中都有索引，则MySQL不会从表本身加载数据，而是从索引加载数据（更快）。合并和单独的索引之间的区别是，MySQL不能使用超过一个每次查询索引，因此，
MongoDB索引

主要内容：createIndex() 方法,dropIndex() 方法,dropIndexes() 方法,getIndexes() 方法索引是特殊的数据结构，存储在一个方便遍历和读取的数据集合中。索引在任何数据库中都非常重要，通过使用索引，可以大大提高查询语句的执行效率。举个简单的例子，假如您有一个集合，其中包含了数千甚至上万个没有索引的文档，当我们在集合中查找某个文档时，MongoDB 需要扫描整个集合来寻找所需的文档，这种扫描效率极低，特别是在处理大量的数据时，可能需要花费几十秒甚至几分

MongoDB不使用索引-可能的排序问题？

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档