示例
- 普通表的访问计划
{
"Name": "sample.employee",
"ScanType": "ixscan",
"IndexName": "$shard",
"UseExtSort": false,
"Query": {
"$and": [
{
"a": {
"$gt": 1
}
}
]
},
"IXBound": {
"a": [
[
1,
{
"$maxElement": 1
}
]
]
},
"NeedMatch": false,
"NodeName": "hostname:11830",
"GroupName": "group",
"Role": "data",
"ReturnNum": 0,
"ElapsedTime": 0.000107,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0
}
Copy
- 垂直分区的访问计划
{
"NodeName": "hostname:11830",
"GroupName": "group",
"Role": "data",
"Name": "maincs.maincl",
"SubCollections": [
{
"Name": "subcs.subcl1",
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false,
"Query": {
"$and": []
},
"IXBound": null,
"NeedMatch": false,
"ReturnNum": 0,
"ElapsedTime": 0.000088,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0
},
{
"Name": "subcs.subcl2",
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false,
"Query": {
"$and": []
},
"IXBound": null,
"NeedMatch": false,
"ReturnNum": 0,
"ElapsedTime": 0.000089,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0
}
]
}
Copy
- 协调节点上的虚拟访问计划,即匹配符不能命中任何分区
{
"NodeName": "hostname:11810",
"GroupName": "SYSCoord",
"Role": "coord",
"Collection": "maincs.maincl",
"Query": {
"a": 10000000
},
}
Copy
- 查看查询的普通访问计划,并使用 Run 选项执行查询
> db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Run : true } )
{
"NodeName": "hostname:11810",
"GroupName": "group1",
"Role": "data",
"Name": "sample.employee",
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false,
"Query": {
"$and": [
{
"a": {
"$gt": 100
}
}
]
},
"IXBound": null,
"NeedMatch": true,
"ReturnNum": 49892,
"ElapsedTime": 0.323423,
"IndexRead": 0,
"DataRead": 49945,
"UserCPU": 0.1399999999999999,
"SysCPU": 0
}
{
"NodeName": "hostname:11820",
"GroupName": "group2",
"Role": "data",
"Name": "sample.employee",
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false,
"Query": {
"$and": [
{
"a": {
"$gt": 100
}
}
]
},
"IXBound": null,
"NeedMatch": true,
"ReturnNum": 50007,
"ElapsedTime": 0.41887,
"IndexRead": 0,
"DataRead": 50055,
"UserCPU": 0.1400000000000006,
"SysCPU": 0.009999999999999787
}
Copy
- 使用 Detail 选项查看查询的详细访问计划
> db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true } )
{
"NodeName": "hostname:11800",
"GroupName": "SYSCoord",
"Role": "coord",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 0,
"ReturnNum": 0,
"ElapsedTime": 0.00123,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"PlanPath": {
"Operator": "COORD-MERGE",
"Sort": {},
"NeedReorder": false,
"DataNodeNum": 2,
"DataNodeList": [
{
"Name": "hostname:11810",
"EstTotalCost": 1.484
},
{
"Name": "hostname:11820",
"EstTotalCost": 0.7418349999999999
}
],
"Selector": {},
"Skip": 0,
"Return": -1,
"Estimate": {
"StartCost": 0,
"RunCost": 1.5214865,
"TotalCost": 1.5214865,
"Output": {
"Records": 74973,
"RecordSize": 29,
"Sorted": false
}
},
"ChildOperators": [
{
"NodeName": "hostname:11810",
"GroupName": "group1",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 0,
"ElapsedTime": 0.000078,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"CacheStatus": "HitCache",
"MainCLPlan": false,
"CacheLevel": "OPT_PLAN_PARAMETERIZED",
"Parameters": [
100
],
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": true,
"FuzzyOptr": false
}
},
{
"NodeName": "hostname:11820",
"GroupName": "group2",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 0,
"ElapsedTime": 0.000081,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"CacheStatus": "HitCache",
"MainCLPlan": false,
"CacheLevel": "OPT_PLAN_PARAMETERIZED",
"Parameters": [
100
],
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": true,
"FuzzyOptr": false
}
}
]
}
}
Copy
- 使用 Detail 选项查看查询的详细访问计划,并使用 Run 选项执行查询
> db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true, Run : true } )
{
"NodeName": "hostname:11800",
"GroupName": "SYSCoord",
"Role": "coord",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 0,
"ReturnNum": 99899,
"ElapsedTime": 0.82863,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0.01999999999999999,
"SysCPU": 0.009999999999999995,
"PlanPath": {
"Operator": "COORD-MERGE",
"Sort": {},
"NeedReorder": false,
"DataNodeNum": 2,
"DataNodeList": [
{
"Name": "hostname:11820",
"EstTotalCost": 0.7418349999999999,
"QueryTimeSpent": 0.733299,
"WaitTimeSpent": 0.013556
},
{
"Name": "hostname:11810",
"EstTotalCost": 1.484,
"QueryTimeSpent": 0.82677,
"WaitTimeSpent": 0.084652
}
],
"Selector": {},
"Skip": 0,
"Return": -1,
"Estimate": {
"StartCost": 0,
"RunCost": 1.5214865,
"TotalCost": 1.5214865,
"Output": {
"Records": 74973,
"RecordSize": 29,
"Sorted": false
}
},
"Run": {
"ContextID": 29314,
"StartTimestamp": "2017-12-14-15.24.51.254623",
"QueryTimeSpent": 0.821182,
"GetMores": 112,
"ReturnNum": 99899,
"WaitTimeSpent": 0.075
},
"ChildOperators": [
{
"NodeName": "hostname:11820",
"GroupName": "group2",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 49892,
"ElapsedTime": 0.733493,
"IndexRead": 0,
"DataRead": 49945,
"UserCPU": 0.14,
"SysCPU": 0.01000000000000001,
"CacheStatus": "HitCache",
"MainCLPlan": false,
"CacheLevel": "OPT_PLAN_PARAMETERIZED",
"Parameters": [
100
],
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": true,
"FuzzyOptr": false
}
},
{
"NodeName": "hostname:11810",
"GroupName": "group1",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 50007,
"ElapsedTime": 0.82666,
"IndexRead": 0,
"DataRead": 50055,
"UserCPU": 0.1499999999999986,
"SysCPU": 0.01000000000000023,
"CacheStatus": "HitCache",
"MainCLPlan": false,
"CacheLevel": "OPT_PLAN_PARAMETERIZED",
"Parameters": [
100
],
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": true,
"FuzzyOptr": false
}
}
]
}
}
Copy
- 使用 Detail 选项查看查询的详细访问计划,并且使用 Search 选项查看查询优化器的搜索过程
> db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true, Search : true } )
{
"NodeName": "hostname:11800",
"GroupName": "SYSCoord",
"Role": "coord",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 0,
"ReturnNum": 0,
"ElapsedTime": 0.037223,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"PlanPath": {
"Operator": "COORD-MERGE",
"Sort": {},
"NeedReorder": false,
"DataNodeNum": 2,
"DataNodeList": [
{
"Name": "hostname:11820",
"EstTotalCost": 0.7418349999999999
},
{
"Name": "hostname:11810",
"EstTotalCost": 1.334165
}
],
"Selector": {},
"Skip": 0,
"Return": -1,
"Estimate": {
"StartCost": 0,
"RunCost": 1.3591655,
"TotalCost": 1.3591655,
"Output": {
"Records": 50001,
"RecordSize": 29,
"Sorted": false
}
},
"ChildOperators": [
{
"NodeName": "hostname:11820",
"GroupName": "group2",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 0,
"ElapsedTime": 0.000048,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"CacheStatus": "NoCache",
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": false,
"FuzzyOptr": false
},
"PlanPath": {
"Operator": "TBSCAN",
"Collection": "sample.employee",
"Query": {
"$and": [
{
"a": {
"$gt": 100
}
}
]
},
"Selector": {},
"Skip": 0,
"Return": -1,
"Estimate": {
"StartCost": 0,
"RunCost": 0.7418349999999999,
"TotalCost": 0.7418349999999999,
"CLEstFromStat": false,
"Input": {
"Pages": 37,
"Records": 49945,
"RecordSize": 29
},
"Filter": {
"MthSelectivity": 0.4999994999999995
},
"Output": {
"Records": 24973,
"RecordSize": 29,
"Sorted": false
}
}
},
"Search": {
"Options": {
"sortbuf": 256,
"optcostthreshold": 20
},
"SearchPaths": [
{
"IsUsed": false,
"IsCandidate": false,
"Score": 1,
"ScanType": "ixscan",
"IndexName": "$id",
"UseExtSort": false,
"Direction": 1,
"IXBound": {
"_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
},
"NeedMatch": true,
"IXEstFromStat": false
},
{
"IsUsed": false,
"IsCandidate": false,
"Score": 0.4999994999999995,
"ScanType": "ixscan",
"IndexName": "$shard",
"UseExtSort": false,
"Direction": 1,
"IXBound": {
"a": [
[
100,
{
"$decimal": "MAX"
}
]
]
},
"NeedMatch": false,
"IXEstFromStat": false
},
{
"IsUsed": true,
"IsCandidate": true,
"Score": 0.4999994999999995,
"TotalCost": 1483670,
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false
}
]
}
},
{
"NodeName": "hostname:11810",
"GroupName": "group1",
"Role": "data",
"Collection": "sample.employee",
"Query": {
"a": {
"$gt": 100
}
},
"Sort": {},
"Selector": {},
"Hint": {},
"Skip": 0,
"Return": -1,
"Flag": 2048,
"ReturnNum": 0,
"ElapsedTime": 0.000064,
"IndexRead": 0,
"DataRead": 0,
"UserCPU": 0,
"SysCPU": 0,
"CacheStatus": "NoCache",
"MatchConfig": {
"EnableMixCmp": false,
"Parameterized": false,
"FuzzyOptr": false
},
"PlanPath": {
"Operator": "TBSCAN",
"Collection": "sample.employee",
"Query": {
"$and": [
{
"a": {
"$gt": 100
}
}
]
},
"Selector": {},
"Skip": 0,
"Return": -1,
"Estimate": {
"StartCost": 0,
"RunCost": 1.334165,
"TotalCost": 1.334165,
"CLEstFromStat": false,
"Input": {
"Pages": 74,
"Records": 50055,
"RecordSize": 29
},
"Filter": {
"MthSelectivity": 0.4999994999999995
},
"Output": {
"Records": 25028,
"RecordSize": 29,
"Sorted": false
}
}
},
"Search": {
"Options": {
"sortbuf": 256,
"optcostthreshold": 20
},
"SearchPaths": [
{
"IsUsed": false,
"IsCandidate": false,
"Score": 1,
"ScanType": "ixscan",
"IndexName": "$id",
"UseExtSort": false,
"Direction": 1,
"IXBound": {
"_id": [
[
{
"$minElement": 1
},
{
"$maxElement": 1
}
]
]
},
"NeedMatch": true,
"IXEstFromStat": false
},
{
"IsUsed": false,
"IsCandidate": false,
"Score": 0.4999994999999995,
"ScanType": "ixscan",
"IndexName": "$shard",
"UseExtSort": false,
"Direction": 1,
"IXBound": {
"a": [
[
100,
{
"$decimal": "MAX"
}
]
]
},
"NeedMatch": false,
"IXEstFromStat": false
},
{
"IsUsed": true,
"IsCandidate": true,
"Score": 0.4999994999999995,
"TotalCost": 2668330,
"ScanType": "tbscan",
"IndexName": "",
"UseExtSort": false
}
]
}
}
]
}
}
相关文章请点击阅读