查詢規劃器

規劃器 2.0 版新增了對否定運算子的索引支援$ne，包括 $nin、$not {eq}、 和 $not {in}，以及 $type和 $elemMatch。

Sample Document: { "x": 10, "y": [1, 2, 3] }

db.foo.createIndex({ "x": 1, "y": 1 })
db.foo.find({ "x": {$nin: [20, 30] }})
db.foo.find({"x":{ $type: "string" }})

db.foo.createIndex({"x.y": 1})
db.foo.find({"x":{$elemMatch:{"y":{$elemMatch:{"$gt": 3 }}}}})

即使查詢表達式中$exists不存在 ，規劃器 2.0 版仍會利用稀疏或部分索引。

Sample Document: {"name": "Bob", "email": "example@fake.com" }

Using Planner Version 1.0, you can specify the command as shown below:
db.foo.find({email: "example@fake.com", email: {$exists: true}})

Using Planner Version 2.0, you can specify command without $exists:
db.foo.find({ email: "example@fake.com" })

即使查詢條件未完全符合部分索引篩選條件表達式，規劃器 2.0 版仍會利用部分索引。

Sample Document: {"name": "Bob", "age": 34}
db.foo.createIndex({"age":1},{partialFilterExpression:{"age":{$lt:50}}})

With Planner Version 1.0, index is used only when the query condition meets the partial
 index filter criterion:
     db.foo.find({"age":{$lt:50}})

With Planner Version 2.0, index is used even when the query condition doesn’t meet the index
criterion:
db.foo.find({"age":{$lt:30}})

規劃器 2.0 版使用部分索引掃描搭配 $elemMatch 查詢。

Sample Document: {"name": "Bob", "age": [34,35,36]}
db.foo.createIndex({"age":1},{partialFilterExpression:{"age":{$lt:50,$gt:20}}})
db.foo.find({age:{$elemMatch:{$lt:50,$gt:20}}})

規劃器 2.0 版包含 的索引掃描支援$regex，而不需要在應用程式程式碼$hint中提供 。 僅$regex支援字首搜尋的索引。

Sample Document: { "x": [1, 2, 3], "y": "apple" }
db.foo.createIndex({ "x": 1, "y": 1 })
db.foo.find({"y":{ $regex: "^a" }})

規劃器 2.0 版可改善涉及多金鑰索引的查詢效能，以及多金鑰欄位的相等條件。

Sample Document: {"x": [1, 2, 3],
"y": 5}
db.foo.createIndex({"x": 1, "y":1})
db.foo.find({"x": 2,
"y": {$gt: 1}}).limit(1)

規劃器 2.0 版可改善涉及多個篩選條件的查詢效能，尤其是文件大於 8 KB 的集合。

Sample Document: {"x": 2,
"y": 4,
"z": 9,
"t": 99}
db.foo.find({$and: [{"x": {$gt : 1}, "y": {$gt : 3}, "z": {$lt : 10},
"t":{$lt : 100}}]})

規劃器 2.0 版透過消除排序階段，在將$in運算子與複合索引搭配使用時改善查詢的效能。

Sample Document: {"x": 2,
"y": 4,
"z": 9,
"t": 99}
db.foo.createIndex({"x":1, "y":1})
db.foo.find({"x":2,
"y":$in:[1,2,3,4]}).sort({x:1,y:1})

它也可以改善使用多索引鍵索引搭配 $in元素的查詢效能。

Sample Document: {"x": [1, 2, 3]}
db.foo.createIndex({"x": 1})
db.foo.find("x":{$in:[>100 elements]})

規劃器 2.0 版可改善涉及多個篩選條件的查詢效能，尤其是文件大於 8 KB 的集合。

Sample Document: {"x": 2,
"y": 4,
"z": 9,
"t": 99}
db.foo.find({$and: [{"x": {$gt : 1}, "y": {$gt : 3}, "z": {$lt : 10},
"t":{$lt : 100}}]})

規劃器 2.0 版透過消除排序階段，在將$in運算子與複合索引搭配使用時改善查詢的效能。

Sample Document: {"x": 2,
"y": 4,
"z": 9,
"t": 99}
db.foo.createIndex({"x":1, "y":1})
db.foo.find({"x":2,
"y":$in:[1,2,3,4]}).sort({x:1,y:1})

它也可以改善使用多索引鍵索引搭配 $in元素的查詢效能。

Sample Document: {"x": [1, 2, 3]}
db.foo.createIndex({"x": 1})
db.foo.find("x":{$in:[>100 elements]})

在某些情況下，MongoDB 中的 $(update)和 $(projection)運算子的行為可能與 Amazon DocumentDB 的規劃器 1.0 版不同。以下是一些範例：

db.students_list.insertMany( [ { _id: 5, student_ids: [ 100, 200 ], grades: [ 95, 100 ], grad_year: [ 2024, 2023 ] } ] )

db.students_list.updateOne({ student_ids: 100, grades: 100, grad_year: 2024 },
{ $set: { “grad_year.$”: 2025 } }

db.col.insert({x:[1,2,3]})
db.col.update({$and:[{x:1},{x:3}]},{$set:{"x.$":500}})

db.col.insert({x:[1,2,3]})
db.col.find()

db.col.insert({x:100})
db.col.update({x:100},{x:100})

當$(update)運算子與 搭配使用時$setOnInsert，規劃器 1.0 版和 MongoDB 會擲回錯誤，但規劃器 2.0 版不會。

重新命名不存在的欄位以在規劃器 2.0 版中$field擲回錯誤，而 不會在規劃器 1.0 版和 MongoDB 中產生更新。

當 套用不適合的索引$hint時，規劃器 2.0 版會擲回錯誤，而規劃器 1.0 版和 MongoDB 則不會。

// Insert
db.col.insert({x:1})
db.col.insert({x:2})
db.col.insert({x:3})

// Create index on x with partialFilter Expression {x:{$gt:2}}
db.col.createIndex({x:1},{partialFilterExpression:{x:{$gt:2}}})

// Mongodb allows hint on the following queries
db.col.find({x:1}).hint("x_1")
// result is no documents returned because {x:1} is not indexed by the partial index
// Without $hint mongo should return {x:1}, thus the difference in result between COLSCAN and IXSCAN

DocumentDB will error out when $hint is applied on such cases.
db.col.find({x:1}).hint("x_1")
Error: error: {
    "ok" : 0,
    "operationTime" : Timestamp(1746473021, 1),
    "code" : 2,
    "errmsg" : "Cannot use Hint for this Query. Index is multi key index , partial index or sparse index and query is not optimized to use this index."
}

rs0:PRIMARY> db.runCommand({"planCacheSetFilter": "col", "query": { location: {$nearSphere: {$geometry: {type: "Point", coordinates: [1, 1]}}}}, "indexes": ["name_1"]})
{
    "ok" : 0,
    "operationTime" : Timestamp(1750815778, 1),
    "code" : 303,
    "errmsg" : "Unsupported query shape for index filter $nearSphere"
}

$near 無法在規劃器 2.0 版$hint({“$natural”:1})中使用 。

// indexes present are index on x and geo index

rs0:PRIMARY> db.usarestaurants.getIndexes()
[
    {
        "v" : 4,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.usarestaurants"
    },
    {
        "v" : 4,
        "key" : {
            "location" : "2dsphere"
        },
        "name" : "location_2dsphere",
        "ns" : "test.usarestaurants",
        "2dsphereIndexVersion" : 1
    }
]

// Planner Version 2.0 will throw an error when $hint is applied with index "x_1"
rs0:PRIMARY> db.usarestaurants.find({    "location":{       "$nearSphere":{          "$geometry":{             "type":"Point",             "coordinates":[                -122.3516,                47.6156             ]          },          "$minDistance":1,          "$maxDistance":2000       }    } }, {    "name":1 }).hint({"$natural": 1})
Error: error: {
    "ok" : 0,
    "operationTime" : Timestamp(1746475524, 1),
    "code" : 291,
    "errmsg" : "unable to find index for $geoNear query"
}

// Planner Version 1.0 and MongoDB will not throw an error
 db.usarestaurants.find({    "location":{       "$nearSphere":{          "$geometry":{             "type":"Point",             "coordinates":[                -122.3516,                47.6156             ]          },          "$minDistance":1,          "$maxDistance":2000       }    } }, {    "name":1 }).hint({"$natural": 1})
{ "_id" : ObjectId("681918e087dadfd99b7f0172"), "name" : "Noodle House" }

雖然 MongoDB 支援完整的 regex 索引掃描，但規劃器 2.0 版僅支援字首欄位的 regex 索引掃描。

// index on x
db.col.createIndex({x:1})

// index scan is used only for prefix regexes
rs0:PRIMARY> db.col.find({x: /^x/}).explain()
{
    "queryPlanner" : {
        "plannerVersion" : 2,
        "namespace" : "test.col",
        "winningPlan" : {
            "stage" : "IXSCAN",
            "indexName" : "x_1",
            "direction" : "forward",
            "indexCond" : {
                "$and" : [
                    {
                        "x" : {
                            "$regex" : /^x/
                        }
                    }
                ]
            },
            "filter" : {
                "x" : {
                    "$regex" : /^x/
                }
            }
        }
    },
    "indexFilterSet" : false,
    "indexFilterApplied" : false,
    "ok" : 1,
    "operationTime" : Timestamp(1746474527, 1)
}

// COLSCAN is used for non-prefix regexes
rs0:PRIMARY> db.col.find({x: /x$/}).explain()
{
    "queryPlanner" : {
        "plannerVersion" : 2,
        "namespace" : "test.col",
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "x" : {
                    "$regex" : /x$/
                }
            }
        }
    },
    "indexFilterSet" : false,
    "indexFilterApplied" : false,
    "ok" : 1,
    "operationTime" : Timestamp(1746474575, 1)

相較於 MongoDB，搭配規劃器 2.0 版使用計劃快取篩選條件有一些固有差異。雖然規劃器 2.0 版不支援使用計劃快取篩選條件指定「投影」和「定序」，但 MongoDB 會這麼做。不過，MongoDB 索引篩選條件僅限記憶體內，重新啟動後會遺失。規劃器 2.0 版透過重新啟動和修補程式保留索引篩選條件。

使用規劃器 2.0 版時的 DML 稽核日誌格式與規劃器 1.0 版略有不同。

command -  db.col.find({x:1})

************** Audit logs generated ******************

// v1 format for dml audit logs
{"atype":"authCheck","ts":1746473479983,"timestamp_utc":"2025-05-05 19:31:19.983","remote_ip":"127.0.0.1:47022","users":[{"user":"serviceadmin","db":"test"}],"param":{"command":"find","ns":"test.col","args":{"batchSize":101,"filter":{"x":1},"find":"col","limit":18446744073709551615,"lsid":{"id":{"$binary":"P6RCGz9ZS4iWBSSHWXW15A==","$type":"4"},"uid":{"$binary":"6Jo8PisnEi3dte03+pJFjdCyn/5cGQL8V2KqaoWsnk8=","$type":"0"}},"maxScan":18446744073709551615,"singleBatch":false,"skip":0,"startTransaction":false},"result":0}}

// v2 formal for dml audit logs
{"atype":"authCheck","ts":1746473583711,"timestamp_utc":"2025-05-05 19:33:03.711","remote_ip":"127.0.0.1:37754","users":[{"user":"serviceadmin","db":"test"}],"param":{"command":"find","ns":"test.col","args":{"find":"col","filter":{"x":1},"lsid":{"id":{"$binary":"nJ88TGCSSd+BeD2+ZtrhQg==","$type":"4"}},"$db":"test"},"result":0}}

做為解釋計畫一部分的索引條件：

rs0:PRIMARY> db.col.createIndex({index1:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1,
	"operationTime" : Timestamp(1761149251, 1)
}

規劃器 2.0 版說明顯示索引條件和篩選條件的計畫輸出：

rs0:PRIMARY> db.col.find({$and:[{price:{$eq:300}},{item:{$eq:"apples"}}]}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 2,
		"namespace" : "test.col",
		"winningPlan" : {
			"stage" : "IXSCAN",
			"indexName" : "price_1",
			"direction" : "forward",
			"indexCond" : {
				"$and" : [
					{
						"price" : {
							"$eq" : 300
						}
					}
				]
			},
			"filter" : {
				"$and" : [
					{
						"item" : {
							"$eq" : "apples"
						}
					}
				]
			}
		}
	},
	"indexFilterSet" : false,
	"indexFilterApplied" : false,
	"ok" : 1,
	"operationTime" : Timestamp(1761149497, 1)
}

規劃器 1.0 版說明計畫輸出：

rs0:PRIMARY> db.col.find({$and:[{price:{$eq:300}},{item:{$eq:"apples"}}]}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.col",
		"winningPlan" : {
			"stage" : "IXSCAN",
			"indexName" : "price_1",
			"direction" : "forward"
		}
	},
	"ok" : 1,
	"operationTime" : Timestamp(1761149533, 1)
}