explain()

语法

query.explain( [options] )

类别

SdbQuery

描述

获取查询的访问计划。

参数

参数名参数类型描述是否必填
optionsBSON 对象访问计划执行参数否

options 参数详细说明如下：

参数名参数类型描述默认值
Run布尔型表示是否执行访问计划。
Run 选项为 true 表示执行访问计划，获取数据和时间信息。
Run 选项为 false 表示只获取访问计划的信息，并不执行。		false
Detail布尔型表示是否展示更详细的访问计划，展示涉及访问计划的协调节点、数据节点及相关的上下文信息。
Detail 选项为 true 表示展示详细的访问计划。
Detail 选项为 true 时默认展示一层详细的访问计划，需要使用 Expand 选项展示所有详细的访问计划。
Detail 选项为 false 表示不展示详细的访问计划。		false
Estimate布尔型表示是否展示详细的访问计划中的估算部分。
Estimate 选项为 true 表示展示详细的访问计划中的估算部分。
Estimate 选项为 false 表示不展示详细的访问计划中的估算部分。
如果 Estimate 选项显式设置，Detail 自动设置为 true。		Detail 选项的值
Expand布尔型表示是否展示详细的访问计划中的多层信息。
Expand 选项为 true 表示展示详细的访问计划中的多层信息。
Expand 选项为 false 表示仅展示一层详细的访问计划，该层的子层将不展开。
如果 Expand 选项显式设置，Detail 自动设置为 true。		false
Flatten布尔型表示是否展开每个节点和每个子表的访问计划的输出结果作为一条记录。
Flatten 选项为 true 表示展开输出结果。
Flatten 选项为 false 表示不展开输出结果，并组合成数组挂在上一级节点或者主表上展示。
如果 Flatten 选项显式设置，Detail 选项和 Expand 选项自动设置为 true。		false
Filter字符串 或者 数组表示对估算结果（Estimate 选项）的细节进行过滤。
Filter 选项为可以选择 “None”、”Output”、”Input”、”Filter”、”All”，或者其组合数组。
Filter 选项为 [] 或者 null 表示为 “None”。
1. “None” 表示不输出估算结果的任何细节。
2. “Input” 表示输出估算结果的输入细节。
3. “Filter” 表示输出估算结果的过滤细节。
4. “Output” 表示输出估算结果的输出细节。
5. “All” 表示输出估算结果的全部细节。
如果 Filter 选项显式设置，Detail 选项和 Estimate 选项自动设置为 true。		“All”
LocationBSON 对象表示对访问计划的结果按照数据组进行过滤，使用命令位置参数项。
Location 选项仅支持 “GroupID” 和 “GroupName” 选项。
详细见 命令位置参数。
如果 Location 选项显式设置，Detail 选项自动设置为 true。		空
SubCollections字符串 或者 数组表示对访问计划的结果按照子表进行过滤。
SubCollections 选项只在带有主子表的访问计划时生效。
SubCollections 选项可以选择某个子表名，或者子表名的数组，表示只显示指定子表的访问计划。
SubCollections 选项为 [] 或者 null 表示为不进行过滤。
如果 SubCollections 选项显式设置，Detail 选项自动设置为 true。		空
Search布尔型表示是否查看查询优化器搜索过的访问计划，并查看查询优化器选择的结果。
Search 选项为 true 表示展示查询优化器的选择过程。
Search 选项为 false 表示不展示查询优化器的选择过程。
如果 Search 选项显式设置，Detail 选项和 Expand 选项自动设置为 true。		false
Evaluate布尔型表示是否查看查询优化器搜索过的访问计划的计算过程。
Evaluate 选项为 true 表示展示查询优化器的计算过程。
Evaluate 选项为 false 表示不展示查询优化器的计算过程。
如果 Evaluate 选项显式设置，Detail 选项、Search 选项和 Expand 选项自动设置成 true。		false

普通的访问计划

Detail 选项为 false 时，将会展示普通的访问计划。

普通集合的访问计划信息：

字段名类型描述
NodeName字符串访问计划所在的节点的名称
GroupName字符串访问计划所在的节点属于的复制组的名称
Role字符串访问计划所在的节点的角色
1. “data” 表示数据节点
2. “coord” 表示协调节点
Name字符串访问计划访问的集合的名称
ScanType字符串访问计划的扫描方式
1. “tbscan” 表示全表扫描
2. “ixscan” 表示索引扫描
IndexName字符串访问计划使用的索引的名称
全表扫描时为 “”
UseExtSort布尔型访问计划是否使用非索引排序
QueryBSON 对象访问计划解析后的用户查询条件
IXBoundBSON 对象访问计划使用索引的查找范围表扫描为 null
NeedMatch布尔型访问计划获取记录时是否需要根据匹配符进行过滤
NeedMatch 为 false 的情况有：
1. 没有查询条件
2. 查询条件可以被索引覆盖
ReturnNum长整型访问计划返回记录的个数
ElapsedTime浮点数访问计划查询耗时（单位：秒）
IndexRead长整型访问计划扫描索引记录的个数
DataRead长整型访问计划扫描数据记录的个数
UserCPU浮点数访问计划用户态 CPU 使用时间（单位：秒）
SysCPU浮点数访问计划内核态 CPU 使用时间（单位：秒）

垂直分区中主表的访问计划信息：

字段名类型描述
NodeName字符串访问计划所在的节点的名称
GroupName字符串访问计划所在的节点属于的复制组的名称
Role字符串访问计划所在的节点的角色
1. “data” 表示数据节点
2. “coord” 表示协调节点
Name字符串访问计划访问的集合的名称
SubCollections数组访问计划垂直分区表中各子表的访问计划

垂直分区中子表的访问计划信息：

字段名类型描述
Name字符串访问计划访问的集合的名称
ScanType字符串访问计划的扫描方式
1. “tbscan” 表示全表扫描
2. “ixscan” 表示索引扫描
IndexName字符串访问计划使用的索引的名称
全表扫描时为 “”
UseExtSort布尔型访问计划是否使用非索引排序
QueryBSON 对象访问计划解析后的用户查询条件
IXBoundBSON 对象访问计划使用索引的查找范围表扫描为 null
NeedMatch布尔型访问计划获取记录时是否需要根据匹配符进行过滤
NeedMatch 为 false 的情况有：
1. 没有查询条件
2. 查询条件可以被索引覆盖
ReturnNum长整型访问计划返回记录的个数
ElapsedTime浮点数访问计划查询耗时（单位：秒）
IndexRead长整型访问计划扫描索引记录的个数
DataRead长整型访问计划扫描数据记录的个数
UserCPU浮点数访问计划用户态 CPU 使用时间（单位：秒）
SysCPU浮点数访问计划内核态 CPU 使用时间（单位：秒）

Note:

  1. 如果集合经过 split 分布在多个复制组，访问计划会按照一组一记录的方式返回。
  2. 如果查询的匹配符不能命中垂直分区的任何一个分区时，查询将不会下发到数据节点上执行，此时的访问计划将返回一个带有协调节点的虚拟访问计划。

详细的访问计划

Detail 选项为 true 时，将会展示详细的访问计划。在协调节点和数据节点上展示的详细访问计划略有不同。

访问计划的搜索过程

Search 选项为 true 时，将会展示查询优化器搜索过的访问计划，并查看查询优化器选择的结果。

返回值

返回访问计划的游标，类型为 object 。

错误

如果出错则抛异常，并输出错误信息，可以通过getLastErrMsg()获取错误信息或通过getLastError()获取错误码。 关于错误处理可以参考常见错误处理指南。

常见错误可参考错误码。

示例

  • 普通表的访问计划
  1. {
  2.   "Name": "sample.employee",
  3.   "ScanType": "ixscan",
  4.   "IndexName": "$shard",
  5.   "UseExtSort": false,
  6.   "Query": {
  7.     "$and": [
  8.       {
  9.         "a": {
  10.           "$gt": 1
  11.         }
  12.       }
  13.     ]
  14.   },
  15.   "IXBound": {
  16.     "a": [
  17.       [
  18.         1,
  19.         {
  20.           "$maxElement": 1
  21.         }
  22.       ]
  23.     ]
  24.   },
  25.   "NeedMatch": false,
  26.   "NodeName": "hostname:11830",
  27.   "GroupName": "group",
  28.   "Role": "data",
  29.   "ReturnNum": 0,
  30.   "ElapsedTime": 0.000107,
  31.   "IndexRead": 0,
  32.   "DataRead": 0,
  33.   "UserCPU": 0,
  34.   "SysCPU": 0
  35. }
  • 垂直分区的访问计划
  1. {
  2.   "NodeName": "hostname:11830",
  3.   "GroupName": "group",
  4.   "Role": "data",
  5.   "Name": "maincs.maincl",
  6.   "SubCollections": [
  7.     {
  8.       "Name": "subcs.subcl1",
  9.       "ScanType": "tbscan",
  10.       "IndexName": "",
  11.       "UseExtSort": false,
  12.       "Query": {
  13.         "$and": []
  14.       },
  15.       "IXBound": null,
  16.       "NeedMatch": false,
  17.       "ReturnNum": 0,
  18.       "ElapsedTime": 0.000088,
  19.       "IndexRead": 0,
  20.       "DataRead": 0,
  21.       "UserCPU": 0,
  22.       "SysCPU": 0
  23.     },
  24.     {
  25.       "Name": "subcs.subcl2",
  26.       "ScanType": "tbscan",
  27.       "IndexName": "",
  28.       "UseExtSort": false,
  29.       "Query": {
  30.         "$and": []
  31.       },
  32.       "IXBound": null,
  33.       "NeedMatch": false,
  34.       "ReturnNum": 0,
  35.       "ElapsedTime": 0.000089,
  36.       "IndexRead": 0,
  37.       "DataRead": 0,
  38.       "UserCPU": 0,
  39.       "SysCPU": 0
  40.     }
  41.   ]
  42. }
  • 协调节点上的虚拟访问计划，即匹配符不能命中任何分区
  1. {
  2.   "NodeName": "hostname:11810",
  3.   "GroupName": "SYSCoord",
  4.   "Role": "coord",
  5.   "Collection": "maincs.maincl",
  6.   "Query": {
  7.     "a": 10000000
  8.   },
  9. }
  • 查看查询的普通访问计划，并使用 Run 选项执行查询
  1. > db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Run : true } )
  2. {
  3.   "NodeName": "hostname:11810",
  4.   "GroupName": "group1",
  5.   "Role": "data",
  6.   "Name": "sample.employee",
  7.   "ScanType": "tbscan",
  8.   "IndexName": "",
  9.   "UseExtSort": false,
  10.   "Query": {
  11.     "$and": [
  12.       {
  13.         "a": {
  14.           "$gt": 100
  15.         }
  16.       }
  17.     ]
  18.   },
  19.   "IXBound": null,
  20.   "NeedMatch": true,
  21.   "ReturnNum": 49892,
  22.   "ElapsedTime": 0.323423,
  23.   "IndexRead": 0,
  24.   "DataRead": 49945,
  25.   "UserCPU": 0.1399999999999999,
  26.   "SysCPU": 0
  27. }
  28. {
  29.   "NodeName": "hostname:11820",
  30.   "GroupName": "group2",
  31.   "Role": "data",
  32.   "Name": "sample.employee",
  33.   "ScanType": "tbscan",
  34.   "IndexName": "",
  35.   "UseExtSort": false,
  36.   "Query": {
  37.     "$and": [
  38.       {
  39.         "a": {
  40.           "$gt": 100
  41.         }
  42.       }
  43.     ]
  44.   },
  45.   "IXBound": null,
  46.   "NeedMatch": true,
  47.   "ReturnNum": 50007,
  48.   "ElapsedTime": 0.41887,
  49.   "IndexRead": 0,
  50.   "DataRead": 50055,
  51.   "UserCPU": 0.1400000000000006,
  52.   "SysCPU": 0.009999999999999787
  53. }
  • 使用 Detail 选项查看查询的详细访问计划
  1. > db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true } )
  2. {
  3.   "NodeName": "hostname:11800",
  4.   "GroupName": "SYSCoord",
  5.   "Role": "coord",
  6.   "Collection": "sample.employee",
  7.   "Query": {
  8.     "a": {
  9.       "$gt": 100
  10.     }
  11.   },
  12.   "Sort": {},
  13.   "Selector": {},
  14.   "Hint": {},
  15.   "Skip": 0,
  16.   "Return": -1,
  17.   "Flag": 0,
  18.   "ReturnNum": 0,
  19.   "ElapsedTime": 0.00123,
  20.   "IndexRead": 0,
  21.   "DataRead": 0,
  22.   "UserCPU": 0,
  23.   "SysCPU": 0,
  24.   "PlanPath": {
  25.     "Operator": "COORD-MERGE",
  26.     "Sort": {},
  27.     "NeedReorder": false,
  28.     "DataNodeNum": 2,
  29.     "DataNodeList": [
  30.       {
  31.         "Name": "hostname:11810",
  32.         "EstTotalCost": 1.484
  33.       },
  34.       {
  35.         "Name": "hostname:11820",
  36.         "EstTotalCost": 0.7418349999999999
  37.       }
  38.     ],
  39.     "Selector": {},
  40.     "Skip": 0,
  41.     "Return": -1,
  42.     "Estimate": {
  43.       "StartCost": 0,
  44.       "RunCost": 1.5214865,
  45.       "TotalCost": 1.5214865,
  46.       "Output": {
  47.         "Records": 74973,
  48.         "RecordSize": 29,
  49.         "Sorted": false
  50.       }
  51.     },
  52.     "ChildOperators": [
  53.       {
  54.         "NodeName": "hostname:11810",
  55.         "GroupName": "group1",
  56.         "Role": "data",
  57.         "Collection": "sample.employee",
  58.         "Query": {
  59.           "a": {
  60.             "$gt": 100
  61.           }
  62.         },
  63.         "Sort": {},
  64.         "Selector": {},
  65.         "Hint": {},
  66.         "Skip": 0,
  67.         "Return": -1,
  68.         "Flag": 2048,
  69.         "ReturnNum": 0,
  70.         "ElapsedTime": 0.000078,
  71.         "IndexRead": 0,
  72.         "DataRead": 0,
  73.         "UserCPU": 0,
  74.         "SysCPU": 0,
  75.         "CacheStatus": "HitCache",
  76.         "MainCLPlan": false,
  77.         "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  78.         "Parameters": [
  79.           100
  80.         ],
  81.         "MatchConfig": {
  82.           "EnableMixCmp": false,
  83.           "Parameterized": true,
  84.           "FuzzyOptr": false
  85.         }
  86.       },
  87.       {
  88.         "NodeName": "hostname:11820",
  89.         "GroupName": "group2",
  90.         "Role": "data",
  91.         "Collection": "sample.employee",
  92.         "Query": {
  93.           "a": {
  94.             "$gt": 100
  95.           }
  96.         },
  97.         "Sort": {},
  98.         "Selector": {},
  99.         "Hint": {},
  100.         "Skip": 0,
  101.         "Return": -1,
  102.         "Flag": 2048,
  103.         "ReturnNum": 0,
  104.         "ElapsedTime": 0.000081,
  105.         "IndexRead": 0,
  106.         "DataRead": 0,
  107.         "UserCPU": 0,
  108.         "SysCPU": 0,
  109.         "CacheStatus": "HitCache",
  110.         "MainCLPlan": false,
  111.         "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  112.         "Parameters": [
  113.           100
  114.         ],
  115.         "MatchConfig": {
  116.           "EnableMixCmp": false,
  117.           "Parameterized": true,
  118.           "FuzzyOptr": false
  119.         }
  120.       }
  121.     ]
  122.   }
  123. }
  • 使用 Detail 选项查看查询的详细访问计划，并使用 Run 选项执行查询
  1. > db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true, Run : true } )
  2. {
  3.   "NodeName": "hostname:11800",
  4.   "GroupName": "SYSCoord",
  5.   "Role": "coord",
  6.   "Collection": "sample.employee",
  7.   "Query": {
  8.     "a": {
  9.       "$gt": 100
  10.     }
  11.   },
  12.   "Sort": {},
  13.   "Selector": {},
  14.   "Hint": {},
  15.   "Skip": 0,
  16.   "Return": -1,
  17.   "Flag": 0,
  18.   "ReturnNum": 99899,
  19.   "ElapsedTime": 0.82863,
  20.   "IndexRead": 0,
  21.   "DataRead": 0,
  22.   "UserCPU": 0.01999999999999999,
  23.   "SysCPU": 0.009999999999999995,
  24.   "PlanPath": {
  25.     "Operator": "COORD-MERGE",
  26.     "Sort": {},
  27.     "NeedReorder": false,
  28.     "DataNodeNum": 2,
  29.     "DataNodeList": [
  30.       {
  31.         "Name": "hostname:11820",
  32.         "EstTotalCost": 0.7418349999999999,
  33.         "QueryTimeSpent": 0.733299,
  34.         "WaitTimeSpent": 0.013556
  35.       },
  36.       {
  37.         "Name": "hostname:11810",
  38.         "EstTotalCost": 1.484,
  39.         "QueryTimeSpent": 0.82677,
  40.         "WaitTimeSpent": 0.084652
  41.       }
  42.     ],
  43.     "Selector": {},
  44.     "Skip": 0,
  45.     "Return": -1,
  46.     "Estimate": {
  47.       "StartCost": 0,
  48.       "RunCost": 1.5214865,
  49.       "TotalCost": 1.5214865,
  50.       "Output": {
  51.         "Records": 74973,
  52.         "RecordSize": 29,
  53.         "Sorted": false
  54.       }
  55.     },
  56.     "Run": {
  57.       "ContextID": 29314,
  58.       "StartTimestamp": "2017-12-14-15.24.51.254623",
  59.       "QueryTimeSpent": 0.821182,
  60.       "GetMores": 112,
  61.       "ReturnNum": 99899,
  62.       "WaitTimeSpent": 0.075
  63.     },
  64.     "ChildOperators": [
  65.       {
  66.         "NodeName": "hostname:11820",
  67.         "GroupName": "group2",
  68.         "Role": "data",
  69.         "Collection": "sample.employee",
  70.         "Query": {
  71.           "a": {
  72.             "$gt": 100
  73.           }
  74.         },
  75.         "Sort": {},
  76.         "Selector": {},
  77.         "Hint": {},
  78.         "Skip": 0,
  79.         "Return": -1,
  80.         "Flag": 2048,
  81.         "ReturnNum": 49892,
  82.         "ElapsedTime": 0.733493,
  83.         "IndexRead": 0,
  84.         "DataRead": 49945,
  85.         "UserCPU": 0.14,
  86.         "SysCPU": 0.01000000000000001,
  87.         "CacheStatus": "HitCache",
  88.         "MainCLPlan": false,
  89.         "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  90.         "Parameters": [
  91.           100
  92.         ],
  93.         "MatchConfig": {
  94.           "EnableMixCmp": false,
  95.           "Parameterized": true,
  96.           "FuzzyOptr": false
  97.         }
  98.       },
  99.       {
  100.         "NodeName": "hostname:11810",
  101.         "GroupName": "group1",
  102.         "Role": "data",
  103.         "Collection": "sample.employee",
  104.         "Query": {
  105.           "a": {
  106.             "$gt": 100
  107.           }
  108.         },
  109.         "Sort": {},
  110.         "Selector": {},
  111.         "Hint": {},
  112.         "Skip": 0,
  113.         "Return": -1,
  114.         "Flag": 2048,
  115.         "ReturnNum": 50007,
  116.         "ElapsedTime": 0.82666,
  117.         "IndexRead": 0,
  118.         "DataRead": 50055,
  119.         "UserCPU": 0.1499999999999986,
  120.         "SysCPU": 0.01000000000000023,
  121.         "CacheStatus": "HitCache",
  122.         "MainCLPlan": false,
  123.         "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  124.         "Parameters": [
  125.           100
  126.         ],
  127.         "MatchConfig": {
  128.           "EnableMixCmp": false,
  129.           "Parameterized": true,
  130.           "FuzzyOptr": false
  131.         }
  132.       }
  133.     ]
  134.   }
  135. }
  • 使用 Detail 选项查看查询的详细访问计划，并且使用 Search 选项查看查询优化器的搜索过程
  1. > db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true, Search : true } )
  2. {
  3.   "NodeName": "hostname:11800",
  4.   "GroupName": "SYSCoord",
  5.   "Role": "coord",
  6.   "Collection": "sample.employee",
  7.   "Query": {
  8.     "a": {
  9.       "$gt": 100
  10.     }
  11.   },
  12.   "Sort": {},
  13.   "Selector": {},
  14.   "Hint": {},
  15.   "Skip": 0,
  16.   "Return": -1,
  17.   "Flag": 0,
  18.   "ReturnNum": 0,
  19.   "ElapsedTime": 0.037223,
  20.   "IndexRead": 0,
  21.   "DataRead": 0,
  22.   "UserCPU": 0,
  23.   "SysCPU": 0,
  24.   "PlanPath": {
  25.     "Operator": "COORD-MERGE",
  26.     "Sort": {},
  27.     "NeedReorder": false,
  28.     "DataNodeNum": 2,
  29.     "DataNodeList": [
  30.       {
  31.         "Name": "hostname:11820",
  32.         "EstTotalCost": 0.7418349999999999
  33.       },
  34.       {
  35.         "Name": "hostname:11810",
  36.         "EstTotalCost": 1.334165
  37.       }
  38.     ],
  39.     "Selector": {},
  40.     "Skip": 0,
  41.     "Return": -1,
  42.     "Estimate": {
  43.       "StartCost": 0,
  44.       "RunCost": 1.3591655,
  45.       "TotalCost": 1.3591655,
  46.       "Output": {
  47.         "Records": 50001,
  48.         "RecordSize": 29,
  49.         "Sorted": false
  50.       }
  51.     },
  52.     "ChildOperators": [
  53.       {
  54.         "NodeName": "hostname:11820",
  55.         "GroupName": "group2",
  56.         "Role": "data",
  57.         "Collection": "sample.employee",
  58.         "Query": {
  59.           "a": {
  60.             "$gt": 100
  61.           }
  62.         },
  63.         "Sort": {},
  64.         "Selector": {},
  65.         "Hint": {},
  66.         "Skip": 0,
  67.         "Return": -1,
  68.         "Flag": 2048,
  69.         "ReturnNum": 0,
  70.         "ElapsedTime": 0.000048,
  71.         "IndexRead": 0,
  72.         "DataRead": 0,
  73.         "UserCPU": 0,
  74.         "SysCPU": 0,
  75.         "CacheStatus": "NoCache",
  76.         "MatchConfig": {
  77.           "EnableMixCmp": false,
  78.           "Parameterized": false,
  79.           "FuzzyOptr": false
  80.         },
  81.         "PlanPath": {
  82.           "Operator": "TBSCAN",
  83.           "Collection": "sample.employee",
  84.           "Query": {
  85.             "$and": [
  86.               {
  87.                 "a": {
  88.                   "$gt": 100
  89.                 }
  90.               }
  91.             ]
  92.           },
  93.           "Selector": {},
  94.           "Skip": 0,
  95.           "Return": -1,
  96.           "Estimate": {
  97.             "StartCost": 0,
  98.             "RunCost": 0.7418349999999999,
  99.             "TotalCost": 0.7418349999999999,
  100.             "CLEstFromStat": false,
  101.             "Input": {
  102.               "Pages": 37,
  103.               "Records": 49945,
  104.               "RecordSize": 29
  105.             },
  106.             "Filter": {
  107.               "MthSelectivity": 0.4999994999999995
  108.             },
  109.             "Output": {
  110.               "Records": 24973,
  111.               "RecordSize": 29,
  112.               "Sorted": false
  113.             }
  114.           }
  115.         },
  116.         "Search": {
  117.           "Options": {
  118.             "sortbuf": 256,
  119.             "optcostthreshold": 20
  120.           },
  121.           "SearchPaths": [
  122.             {
  123.               "IsUsed": false,
  124.               "IsCandidate": false,
  125.               "Score": 1,
  126.               "ScanType": "ixscan",
  127.               "IndexName": "$id",
  128.               "UseExtSort": false,
  129.               "Direction": 1,
  130.               "IXBound": {
  131.                 "_id": [
  132.                   [
  133.                     {
  134.                       "$minElement": 1
  135.                     },
  136.                     {
  137.                       "$maxElement": 1
  138.                     }
  139.                   ]
  140.                 ]
  141.               },
  142.               "NeedMatch": true,
  143.               "IXEstFromStat": false
  144.             },
  145.             {
  146.               "IsUsed": false,
  147.               "IsCandidate": false,
  148.               "Score": 0.4999994999999995,
  149.               "ScanType": "ixscan",
  150.               "IndexName": "$shard",
  151.               "UseExtSort": false,
  152.               "Direction": 1,
  153.               "IXBound": {
  154.                 "a": [
  155.                   [
  156.                     100,
  157.                     {
  158.                       "$decimal": "MAX"
  159.                     }
  160.                   ]
  161.                 ]
  162.               },
  163.               "NeedMatch": false,
  164.               "IXEstFromStat": false
  165.             },
  166.             {
  167.               "IsUsed": true,
  168.               "IsCandidate": true,
  169.               "Score": 0.4999994999999995,
  170.               "TotalCost": 1483670,
  171.               "ScanType": "tbscan",
  172.               "IndexName": "",
  173.               "UseExtSort": false
  174.             }
  175.           ]
  176.         }
  177.       },
  178.       {
  179.         "NodeName": "hostname:11810",
  180.         "GroupName": "group1",
  181.         "Role": "data",
  182.         "Collection": "sample.employee",
  183.         "Query": {
  184.           "a": {
  185.             "$gt": 100
  186.           }
  187.         },
  188.         "Sort": {},
  189.         "Selector": {},
  190.         "Hint": {},
  191.         "Skip": 0,
  192.         "Return": -1,
  193.         "Flag": 2048,
  194.         "ReturnNum": 0,
  195.         "ElapsedTime": 0.000064,
  196.         "IndexRead": 0,
  197.         "DataRead": 0,
  198.         "UserCPU": 0,
  199.         "SysCPU": 0,
  200.         "CacheStatus": "NoCache",
  201.         "MatchConfig": {
  202.           "EnableMixCmp": false,
  203.           "Parameterized": false,
  204.           "FuzzyOptr": false
  205.         },
  206.         "PlanPath": {
  207.           "Operator": "TBSCAN",
  208.           "Collection": "sample.employee",
  209.           "Query": {
  210.             "$and": [
  211.               {
  212.                 "a": {
  213.                   "$gt": 100
  214.                 }
  215.               }
  216.             ]
  217.           },
  218.           "Selector": {},
  219.           "Skip": 0,
  220.           "Return": -1,
  221.           "Estimate": {
  222.             "StartCost": 0,
  223.             "RunCost": 1.334165,
  224.             "TotalCost": 1.334165,
  225.             "CLEstFromStat": false,
  226.             "Input": {
  227.               "Pages": 74,
  228.               "Records": 50055,
  229.               "RecordSize": 29
  230.             },
  231.             "Filter": {
  232.               "MthSelectivity": 0.4999994999999995
  233.             },
  234.             "Output": {
  235.               "Records": 25028,
  236.               "RecordSize": 29,
  237.               "Sorted": false
  238.             }
  239.           }
  240.         },
  241.         "Search": {
  242.           "Options": {
  243.             "sortbuf": 256,
  244.             "optcostthreshold": 20
  245.           },
  246.           "SearchPaths": [
  247.             {
  248.               "IsUsed": false,
  249.               "IsCandidate": false,
  250.               "Score": 1,
  251.               "ScanType": "ixscan",
  252.               "IndexName": "$id",
  253.               "UseExtSort": false,
  254.               "Direction": 1,
  255.               "IXBound": {
  256.                 "_id": [
  257.                   [
  258.                     {
  259.                       "$minElement": 1
  260.                     },
  261.                     {
  262.                       "$maxElement": 1
  263.                     }
  264.                   ]
  265.                 ]
  266.               },
  267.               "NeedMatch": true,
  268.               "IXEstFromStat": false
  269.             },
  270.             {
  271.               "IsUsed": false,
  272.               "IsCandidate": false,
  273.               "Score": 0.4999994999999995,
  274.               "ScanType": "ixscan",
  275.               "IndexName": "$shard",
  276.               "UseExtSort": false,
  277.               "Direction": 1,
  278.               "IXBound": {
  279.                 "a": [
  280.                   [
  281.                     100,
  282.                     {
  283.                       "$decimal": "MAX"
  284.                     }
  285.                   ]
  286.                 ]
  287.               },
  288.               "NeedMatch": false,
  289.               "IXEstFromStat": false
  290.             },
  291.             {
  292.               "IsUsed": true,
  293.               "IsCandidate": true,
  294.               "Score": 0.4999994999999995,
  295.               "TotalCost": 2668330,
  296.               "ScanType": "tbscan",
  297.               "IndexName": "",
  298.               "UseExtSort": false
  299.             }
  300.           ]
  301.         }
  302.       }
  303.     ]
  304.   }
  305. }
  • 使用 Detail 选项查看查询的详细访问计划，并且使用 Location 选项查看查询在 group1 上的访问计划
  1. > db.sample.employee.find( { a : { $gt : 100 } } ).explain( { Detail : true, Location : { GroupName : 'group1' } } )
  2. {
  3.   "NodeName": "hostname:11800",
  4.   "GroupName": "SYSCoord",
  5.   "Role": "coord",
  6.   "Collection": "sample.employee",
  7.   "Query": {
  8.     "a": {
  9.       "$gt": 100
  10.     }
  11.   },
  12.   "Sort": {},
  13.   "Selector": {},
  14.   "Hint": {},
  15.   "Skip": 0,
  16.   "Return": -1,
  17.   "Flag": 0,
  18.   "ReturnNum": 0,
  19.   "ElapsedTime": 0.011374,
  20.   "IndexRead": 0,
  21.   "DataRead": 0,
  22.   "UserCPU": 0,
  23.   "SysCPU": 0,
  24.   "PlanPath": {
  25.     "Operator": "COORD-MERGE",
  26.     "Sort": {},
  27.     "NeedReorder": false,
  28.     "DataNodeNum": 2,
  29.     "DataNodeList": [
  30.       {
  31.         "Name": "hostname:11810",
  32.         "EstTotalCost": 1.484
  33.       },
  34.       {
  35.         "Name": "hostname:11820",
  36.         "EstTotalCost": 0.7418349999999999
  37.       }
  38.     ],
  39.     "Selector": {},
  40.     "Skip": 0,
  41.     "Return": -1,
  42.     "Estimate": {
  43.       "StartCost": 0,
  44.       "RunCost": 1.5214865,
  45.       "TotalCost": 1.5214865,
  46.       "Output": {
  47.         "Records": 74973,
  48.         "RecordSize": 29,
  49.         "Sorted": false
  50.       }
  51.     },
  52.     "ChildOperators": [
  53.       {
  54.         "NodeName": "hostname:11810",
  55.         "GroupName": "group1",
  56.         "Role": "data",
  57.         "Collection": "sample.employee",
  58.         "Query": {
  59.           "a": {
  60.             "$gt": 100
  61.           }
  62.         },
  63.         "Sort": {},
  64.         "Selector": {},
  65.         "Hint": {},
  66.         "Skip": 0,
  67.         "Return": -1,
  68.         "Flag": 2048,
  69.         "ReturnNum": 0,
  70.         "ElapsedTime": 0.000088,
  71.         "IndexRead": 0,
  72.         "DataRead": 0,
  73.         "UserCPU": 0,
  74.         "SysCPU": 0,
  75.         "CacheStatus": "HitCache",
  76.         "MainCLPlan": false,
  77.         "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  78.         "Parameters": [
  79.           100
  80.         ],
  81.         "MatchConfig": {
  82.           "EnableMixCmp": false,
  83.           "Parameterized": true,
  84.           "FuzzyOptr": false
  85.         }
  86.       }
  87.     ]
  88.   }
  89. }
  • 使用 Detail 选项查看垂直分区的详细访问计划，并使用 Expand 选项展开所有细节
  1. > db.maincs.maincl.find( { a : { $gt : 100 } } ).explain( { Detail : true, Expand : true } )
  2. {
  3.   "NodeName": "hostname:11800",
  4.   "GroupName": "SYSCoord",
  5.   "Role": "coord",
  6.   "Collection": "maincs.maincl",
  7.   "Query": {
  8.     "a": {
  9.       "$gt": 100
  10.     }
  11.   },
  12.   "Sort": {},
  13.   "Selector": {},
  14.   "Hint": {},
  15.   "Skip": 0,
  16.   "Return": -1,
  17.   "Flag": 0,
  18.   "ReturnNum": 0,
  19.   "ElapsedTime": 0.002748,
  20.   "IndexRead": 0,
  21.   "DataRead": 0,
  22.   "UserCPU": 0,
  23.   "SysCPU": 0,
  24.   "PlanPath": {
  25.     "Operator": "COORD-MERGE",
  26.     "Sort": {},
  27.     "NeedReorder": false,
  28.     "DataNodeNum": 2,
  29.     "DataNodeList": [
  30.       {
  31.         "Name": "hostname:11810",
  32.         "EstTotalCost": 0.9624999999999999
  33.       },
  34.       {
  35.         "Name": "hostname:11820",
  36.         "EstTotalCost": 0.9624999999999999
  37.       }
  38.     ],
  39.     "Selector": {},
  40.     "Skip": 0,
  41.     "Return": -1,
  42.     "Estimate": {
  43.       "StartCost": 0,
  44.       "RunCost": 0.9874999999999999,
  45.       "TotalCost": 0.9874999999999999,
  46.       "Output": {
  47.         "Records": 50000,
  48.         "RecordSize": 43,
  49.         "Sorted": false
  50.       }
  51.     },
  52.     "ChildOperators": [
  53.       {
  54.         "NodeName": "hostname:11810",
  55.         "GroupName": "group1",
  56.         "Role": "data",
  57.         "Collection": "maincs.maincl",
  58.         "Query": {
  59.           "a": {
  60.             "$gt": 100
  61.           }
  62.         },
  63.         "Sort": {},
  64.         "Selector": {},
  65.         "Hint": {},
  66.         "Skip": 0,
  67.         "Return": -1,
  68.         "Flag": 2048,
  69.         "ReturnNum": 0,
  70.         "ElapsedTime": 0.00062,
  71.         "IndexRead": 0,
  72.         "DataRead": 0,
  73.         "UserCPU": 0,
  74.         "SysCPU": 0,
  75.         "PlanPath": {
  76.           "Operator": "MERGE",
  77.           "Sort": {},
  78.           "NeedReorder": false,
  79.           "SubCollectionNum": 2,
  80.           "SubCollectionList": [
  81.             {
  82.               "Name": "subcs.subcl2",
  83.               "EstTotalCost": 0.475
  84.             },
  85.             {
  86.               "Name": "subcs.subcl1",
  87.               "EstTotalCost": 0.475
  88.             }
  89.           ],
  90.           "Selector": {},
  91.           "Skip": 0,
  92.           "Return": -1,
  93.           "Estimate": {
  94.             "StartCost": 0,
  95.             "RunCost": 0.9624999999999999,
  96.             "TotalCost": 0.9624999999999999,
  97.             "Output": {
  98.               "Records": 25000,
  99.               "RecordSize": 43,
  100.               "Sorted": false
  101.             }
  102.           },
  103.           "SubCollections": [
  104.             {
  105.               "Collection": "subcs.subcl2",
  106.               "Query": {
  107.                 "a": {
  108.                   "$gt": 100
  109.                 }
  110.               },
  111.               "Sort": {},
  112.               "Selector": {},
  113.               "Hint": {},
  114.               "Skip": 0,
  115.               "Return": -1,
  116.               "Flag": 2048,
  117.               "ReturnNum": 0,
  118.               "ElapsedTime": 0.000042,
  119.               "IndexRead": 0,
  120.               "DataRead": 0,
  121.               "UserCPU": 0,
  122.               "SysCPU": 0,
  123.               "CacheStatus": "HitCache",
  124.               "MainCLPlan": true,
  125.               "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  126.               "Parameters": [
  127.                 100
  128.               ],
  129.               "MatchConfig": {
  130.                 "EnableMixCmp": false,
  131.                 "Parameterized": true,
  132.                 "FuzzyOptr": false
  133.               },
  134.               "PlanPath": {
  135.                 "Operator": "TBSCAN",
  136.                 "Collection": "subcs.subcl2",
  137.                 "Query": {
  138.                   "$and": [
  139.                     {
  140.                       "a": {
  141.                         "$gt": 100
  142.                       }
  143.                     }
  144.                   ]
  145.                 },
  146.                 "Selector": {},
  147.                 "Skip": 0,
  148.                 "Return": -1,
  149.                 "Estimate": {
  150.                   "StartCost": 0,
  151.                   "RunCost": 0.475,
  152.                   "TotalCost": 0.475,
  153.                   "CLEstFromStat": false,
  154.                   "Input": {
  155.                     "Pages": 25,
  156.                     "Records": 25000,
  157.                     "RecordSize": 43
  158.                   },
  159.                   "Filter": {
  160.                     "MthSelectivity": 0.4999994999999995
  161.                   },
  162.                   "Output": {
  163.                     "Records": 12500,
  164.                     "RecordSize": 43,
  165.                     "Sorted": false
  166.                   }
  167.                 }
  168.               }
  169.             },
  170.             {
  171.               "Collection": "subcs.subcl1",
  172.               "Query": {
  173.                 "a": {
  174.                   "$gt": 100
  175.                 }
  176.               },
  177.               "Sort": {},
  178.               "Selector": {},
  179.               "Hint": {},
  180.               "Skip": 0,
  181.               "Return": -1,
  182.               "Flag": 2048,
  183.               "ReturnNum": 0,
  184.               "ElapsedTime": 0.000049,
  185.               "IndexRead": 0,
  186.               "DataRead": 0,
  187.               "UserCPU": 0,
  188.               "SysCPU": 0,
  189.               "CacheStatus": "HitCache",
  190.               "MainCLPlan": true,
  191.               "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  192.               "Parameters": [
  193.                 100
  194.               ],
  195.               "MatchConfig": {
  196.                 "EnableMixCmp": false,
  197.                 "Parameterized": true,
  198.                 "FuzzyOptr": false
  199.               },
  200.               "PlanPath": {
  201.                 "Operator": "TBSCAN",
  202.                 "Collection": "subcs.subcl1",
  203.                 "Query": {
  204.                   "$and": [
  205.                     {
  206.                       "a": {
  207.                         "$gt": 100
  208.                       }
  209.                     }
  210.                   ]
  211.                 },
  212.                 "Selector": {},
  213.                 "Skip": 0,
  214.                 "Return": -1,
  215.                 "Estimate": {
  216.                   "StartCost": 0,
  217.                   "RunCost": 0.475,
  218.                   "TotalCost": 0.475,
  219.                   "CLEstFromStat": false,
  220.                   "Input": {
  221.                     "Pages": 25,
  222.                     "Records": 25000,
  223.                     "RecordSize": 43
  224.                   },
  225.                   "Filter": {
  226.                     "MthSelectivity": 0.4999994999999995
  227.                   },
  228.                   "Output": {
  229.                     "Records": 12500,
  230.                     "RecordSize": 43,
  231.                     "Sorted": false
  232.                   }
  233.                 }
  234.               }
  235.             }
  236.           ]
  237.         }
  238.       },
  239.       {
  240.         "NodeName": "hostname:11820",
  241.         "GroupName": "group2",
  242.         "Role": "data",
  243.         "Collection": "maincs.maincl",
  244.         "Query": {
  245.           "a": {
  246.             "$gt": 100
  247.           }
  248.         },
  249.         "Sort": {},
  250.         "Selector": {},
  251.         "Hint": {},
  252.         "Skip": 0,
  253.         "Return": -1,
  254.         "Flag": 2048,
  255.         "ReturnNum": 0,
  256.         "ElapsedTime": 0.00067,
  257.         "IndexRead": 0,
  258.         "DataRead": 0,
  259.         "UserCPU": 0,
  260.         "SysCPU": 0,
  261.         "PlanPath": {
  262.           "Operator": "MERGE",
  263.           "Sort": {},
  264.           "NeedReorder": false,
  265.           "SubCollectionNum": 2,
  266.           "SubCollectionList": [
  267.             {
  268.               "Name": "subcs.subcl2",
  269.               "EstTotalCost": 0.475
  270.             },
  271.             {
  272.               "Name": "subcs.subcl1",
  273.               "EstTotalCost": 0.475
  274.             }
  275.           ],
  276.           "Selector": {},
  277.           "Skip": 0,
  278.           "Return": -1,
  279.           "Estimate": {
  280.             "StartCost": 0,
  281.             "RunCost": 0.9624999999999999,
  282.             "TotalCost": 0.9624999999999999,
  283.             "Output": {
  284.               "Records": 25000,
  285.               "RecordSize": 43,
  286.               "Sorted": false
  287.             }
  288.           },
  289.           "SubCollections": [
  290.             {
  291.               "Collection": "subcs.subcl2",
  292.               "Query": {
  293.                 "a": {
  294.                   "$gt": 100
  295.                 }
  296.               },
  297.               "Sort": {},
  298.               "Selector": {},
  299.               "Hint": {},
  300.               "Skip": 0,
  301.               "Return": -1,
  302.               "Flag": 2048,
  303.               "ReturnNum": 0,
  304.               "ElapsedTime": 0.000034,
  305.               "IndexRead": 0,
  306.               "DataRead": 0,
  307.               "UserCPU": 0,
  308.               "SysCPU": 0,
  309.               "CacheStatus": "HitCache",
  310.               "MainCLPlan": true,
  311.               "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  312.               "Parameters": [
  313.                 100
  314.               ],
  315.               "MatchConfig": {
  316.                 "EnableMixCmp": false,
  317.                 "Parameterized": true,
  318.                 "FuzzyOptr": false
  319.               },
  320.               "PlanPath": {
  321.                 "Operator": "TBSCAN",
  322.                 "Collection": "subcs.subcl2",
  323.                 "Query": {
  324.                   "$and": [
  325.                     {
  326.                       "a": {
  327.                         "$gt": 100
  328.                       }
  329.                     }
  330.                   ]
  331.                 },
  332.                 "Selector": {},
  333.                 "Skip": 0,
  334.                 "Return": -1,
  335.                 "Estimate": {
  336.                   "StartCost": 0,
  337.                   "RunCost": 0.475,
  338.                   "TotalCost": 0.475,
  339.                   "CLEstFromStat": false,
  340.                   "Input": {
  341.                     "Pages": 25,
  342.                     "Records": 25000,
  343.                     "RecordSize": 43
  344.                   },
  345.                   "Filter": {
  346.                     "MthSelectivity": 0.4999994999999995
  347.                   },
  348.                   "Output": {
  349.                     "Records": 12500,
  350.                     "RecordSize": 43,
  351.                     "Sorted": false
  352.                   }
  353.                 }
  354.               }
  355.             },
  356.             {
  357.               "Collection": "subcs.subcl1",
  358.               "Query": {
  359.                 "a": {
  360.                   "$gt": 100
  361.                 }
  362.               },
  363.               "Sort": {},
  364.               "Selector": {},
  365.               "Hint": {},
  366.               "Skip": 0,
  367.               "Return": -1,
  368.               "Flag": 2048,
  369.               "ReturnNum": 0,
  370.               "ElapsedTime": 0.000048,
  371.               "IndexRead": 0,
  372.               "DataRead": 0,
  373.               "UserCPU": 0,
  374.               "SysCPU": 0,
  375.               "CacheStatus": "HitCache",
  376.               "MainCLPlan": true,
  377.               "CacheLevel": "OPT_PLAN_PARAMETERIZED",
  378.               "Parameters": [
  379.                 100
  380.               ],
  381.               "MatchConfig": {
  382.                 "EnableMixCmp": false,
  383.                 "Parameterized": true,
  384.                 "FuzzyOptr": false
  385.               },
  386.               "PlanPath": {
  387.                 "Operator": "TBSCAN",
  388.                 "Collection": "subcs.subcl1",
  389.                 "Query": {
  390.                   "$and": [
  391.                     {
  392.                       "a": {
  393.                         "$gt": 100
  394.                       }
  395.                     }
  396.                   ]
  397.                 },
  398.                 "Selector": {},
  399.                 "Skip": 0,
  400.                 "Return": -1,
  401.                 "Estimate": {
  402.                   "StartCost": 0,
  403.                   "RunCost": 0.475,
  404.                   "TotalCost": 0.475,
  405.                   "CLEstFromStat": false,
  406.                   "Input": {
  407.                     "Pages": 25,
  408.                     "Records": 25000,
  409.                     "RecordSize": 43
  410.                   },
  411.                   "Filter": {
  412.                     "MthSelectivity": 0.4999994999999995
  413.                   },
  414.                   "Output": {
  415.                     "Records": 12500,
  416.                     "RecordSize": 43,
  417.                     "Sorted": false
  418.                   }
  419.                 }
  420.               }
  421.             }
  422.           ]
  423.         }
  424.       }
  425.     ]
  426.   }
  427. }