Mongo为何未选择最优查询计划？正则查询索引疑问

我有一个Mongo集合，包含以下两个索引：

{"token" : 1,"TradeName" : -1 }
{"token" : 1,"Name" : 1 }

token、Name、TradeName均为字符串类型。当执行查询{ token : "SOMETHING" , Name : /SOMETHINGELSE/}时，Mongo却选用了{"token" : 1,"TradeName" : -1 }索引，而非{"token" : 1,"Name" : 1 }索引。请问这是什么原因？是否因为正则查询导致索引无法发挥作用？

被选中的token_1_TradeName_-1索引执行计划： #

{
    "nReturned" : 30,
    "executionTimeMillisEstimate" : 0,
    "totalKeysExamined" : 730,
    "totalDocsExamined" : 730,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "Name" : /JOSE/
        },
        "nReturned" : 30,
        "executionTimeMillisEstimate" : 0,
        "works" : 731,
        "advanced" : 30,
        "needTime" : 700,
        "needYield" : 0,
        "saveState" : 18,
        "restoreState" : 18,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 730,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 730,
            "executionTimeMillisEstimate" : 0,
            "works" : 731,
            "advanced" : 730,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 18,
            "restoreState" : 18,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "token" : 1,
                "TradeName" : -1
            },
            "indexName" : "token_1_TradeName_-1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "token" : [],
                "TradeName" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "token" : [
                    "[\"IRENO\", \"IRENO\"]"
                ],
                "TradeName" : [
                    "[MaxKey, MinKey]"
                ]
            },
            "keysExamined" : 730,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }
    }
}

token_1_Name_1索引的执行计划： #

{
    "nReturned" : 30,
    "executionTimeMillisEstimate" : 10,
    "totalKeysExamined" : 731,
    "totalDocsExamined" : 30,
    "executionStages" : {
        "stage" : "FETCH",
        "nReturned" : 30,
        "executionTimeMillisEstimate" : 10,
        "works" : 731,
        "advanced" : 30,
        "needTime" : 700,
        "needYield" : 0,
        "saveState" : 19,
        "restoreState" : 19,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 30,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "filter" : {
                "Name" : /JOSE/
            },
            "nReturned" : 30,
            "executionTimeMillisEstimate" : 10,
            "works" : 731,
            "advanced" : 30,
            "needTime" : 700,
            "needYield" : 0,
            "saveState" : 19,
            "restoreState" : 19,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "token" : 1,
                "Name" : 1
            },
            "indexName" : "token_1_Name_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "token" : [],
                "Name" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "token" : [
                    "[\"IRENO\", \"IRENO\"]"
                ],
                "Name" : [
                    "[\"\", {})",
                    "[/JOSE/, /JOSE/]"
                ]
            },
            "keysExamined" : 731,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }
    }
}

原因分析 #

1. 正则并非完全无法利用索引：从token_1_Name_1的执行计划可以看到，索引确实被使用了——在IXSCAN阶段就对Name字段做了正则过滤，只返回30个符合条件的索引键，后续只需要读取30个文档。
2. 查询优化器的成本估算偏差：Mongo的查询优化器会根据集合统计信息估算不同索引的执行成本。这里它认为token_1_TradeName_-1的预估执行时间（0ms）比token_1_Name_1（10ms）更低，但这个预估忽略了实际的文档IO开销：前者需要读取730个文档再过滤，后者只需要读取30个文档，在数据量更大的场景下后者实际性能会更优。
3. 非前缀正则的限制：你使用的/JOSE/是非前缀匹配正则，Mongo无法利用token_1_Name_1索引的有序性做范围扫描，只能逐个检查索引中的Name值是否匹配正则，这会增加索引扫描的耗时，导致优化器误判这种方式的成本更高，转而选择只利用token字段定位文档、再全量过滤的方案。

解决建议 #

• 改用前缀正则：如果你的查询需求是匹配以JOSE开头的Name，将正则改为/^JOSE/。这种情况下Mongo可以利用索引的有序性快速定位范围，大幅减少索引扫描量，优化器也会优先选择token_1_Name_1索引。
• 强制指定索引：使用db.collection.find({ token : "SOMETHING" , Name : /SOMETHINGELSE/}).hint("token_1_Name_1")强制查询使用目标索引，绕过优化器的误判。
• 更新统计信息：执行db.collection.stats()更新集合的统计数据，让查询优化器能更准确地估算不同索引的执行成本。

内容的提问来源于stack exchange，提问作者Carlos Siestrup