「SequoiaDB巨杉数据库」explain()(二)

示例

  • 普通表的访问计划
{
  "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
            }
          ]
        }
      }
    ]
  }
}

相关文章请点击阅读

「SequoiaDB巨杉数据库」explain()(一) 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值