您好,欢迎来到[编程问答]网站首页   源码下载   电子书籍   软件下载   专题
当前位置:首页 >> 编程问答 >> 其他齐发国际登录 >> mongodb走了索引,依旧是慢查询,请看执行计划

齐发国际登录

来源:网络整理     时间:2018/1/23 1:36:24     关键词:

关于网友提出的“ mongodb走了索引,依旧是慢查询,请看执行计划”问题疑问,本网通过在网上对“ mongodb走了索引,依旧是慢查询,请看执行计划”有关的相关答案进行了整理,供用户进行参考,详细问题解答如下:

问题: mongodb走了索引,依旧是慢查询,请看执行计划
描述:

根据索引in查询, 扫描索引3条 keysExamined 3, 返回文档数1条 nReturned 1, 耗时 millis 452毫秒, execStats中executionTimeMillisEstimate都是0.
问题1: executionTimeMillisEstimate都是0. 为啥整个查询却消耗452毫秒.
问题2: 走了索引,只扫描索引3条,返回文档数1条,消耗452毫秒,怎么优化或者怎么调整它.
问题3: 在其它的执行计划中我看到inputStage.stage.FETCH操作耗时也很高,在200~1000毫秒.FETCH根据索引取下文档为啥还要这么多时间呢.
求助 @Mongoing中文社区 @bguo

{
  "op": "query",
  "ns": "webDevice",
  "query": {
    "find": "webDevice",
    "filter": {
      "lid": {
        "$in": [
          "40CnwyHkVmnA9kbScMLNLneaxuS4Tcj",
          "140CnwyHkVmnA9kbScMLNLneaxuS4Tcj"
        ]
      }
    },
    "projection": {
      "$sortKey": {
        "$meta": "sortKey"
      }
    },
    "sort": {
      "createTime": -1
    },
    "limit": 1,
    "shardVersion": [
      {
        "$timestamp": {
          "t": 106,
          "i": 0
        }
      },
      {
        "$oid": "59b0039e9b5e66530435be05"
      }
    ]
  },
  "keysExamined": 3,
  "docsExamined": 1,
  "hasSortStage": true,
  "cursorExhausted": true,
  "keyUpdates": 0,
  "writeConflicts": 0,
  "numYield": 0,
  "locks": {
    "Global": {
      "acquireCount": {
        "r": 2
      }
    },
    "Database": {
      "acquireCount": {
        "r": 1
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 1
      }
    }
  },
  "nreturned": 1,
  "responseLength": 1267,
  "protocol": "op_command",
  "millis": 452,
  "execStats": {
    "stage": "CACHED_PLAN",
    "nReturned": 1,
    "executionTimeMillisEstimate": 0,
    "works": 2,
    "advanced": 1,
    "needTime": 0,
    "needYield": 0,
    "saveState": 0,
    "restoreState": 0,
    "isEOF": 1,
    "invalidates": 0,
    "inputStage": {
      "stage": "PROJECTION",
      "nReturned": 1,
      "executionTimeMillisEstimate": 0,
      "works": 5,
      "advanced": 1,
      "needTime": 4,
      "needYield": 0,
      "saveState": 0,
      "restoreState": 0,
      "isEOF": 1,
      "invalidates": 0,
      "transformBy": {
        "$sortKey": {
          "$meta": "sortKey"
        }
      },
      "inputStage": {
        "stage": "SORT",
        "nReturned": 1,
        "executionTimeMillisEstimate": 0,
        "works": 5,
        "advanced": 1,
        "needTime": 4,
        "needYield": 0,
        "saveState": 0,
        "restoreState": 0,
        "isEOF": 1,
        "invalidates": 0,
        "sortPattern": {
          "createTime": -1
        },
        "memUsage": 1031,
        "memLimit": 33554432,
        "limitAmount": 1,
        "inputStage": {
          "stage": "SORT_KEY_GENERATOR",
          "nReturned": 0,
          "executionTimeMillisEstimate": 0,
          "works": 4,
          "advanced": 0,
          "needTime": 2,
          "needYield": 0,
          "saveState": 0,
          "restoreState": 0,
          "isEOF": 1,
          "invalidates": 0,
          "inputStage": {
            "stage": "SHARDING_FILTER",
            "nReturned": 1,
            "executionTimeMillisEstimate": 0,
            "works": 3,
            "advanced": 1,
            "needTime": 1,
            "needYield": 0,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 1,
            "invalidates": 0,
            "chunkSkips": 0,
            "inputStage": {
              "stage": "FETCH",
              "nReturned": 1,
              "executionTimeMillisEstimate": 0,
              "works": 3,
              "advanced": 1,
              "needTime": 1,
              "needYield": 0,
              "saveState": 0,
              "restoreState": 0,
              "isEOF": 1,
              "invalidates": 0,
              "docsExamined": 1,
              "alreadyHasObj": 0,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 1,
                "executionTimeMillisEstimate": 0,
                "works": 3,
                "advanced": 1,
                "needTime": 1,
                "needYield": 0,
                "saveState": 0,
                "restoreState": 0,
                "isEOF": 1,
                "invalidates": 0,
                "keyPattern": {
                  "lid": -1
                },
                "indexName": "lid_-1",
                "isMultiKey": false,
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 1,
                "direction": "forward",
                "indexBounds": {
                  "lid": [
                    "[\"40CnwyHkVmnA9kbScMLNLneaxuS4Tcj\", \"40CnwyHkVmnA9kbScMLNLneaxuS4Tcj\"]",
                    "[\"140CnwyHkVmnA9kbScMLNLneaxuS4Tcj\", \"140CnwyHkVmnA9kbScMLNLneaxuS4Tcj\"]"
                  ]
                },
                "keysExamined": 3,
                "dupsTested": 0,
                "dupsDropped": 0,
                "seenInvalidated": 0
              }
            }
          }
        }
      }
    }
  },
  "ts": {
    "$date": 1514285478923
  },
  "client": "10.105.122.126",
  "allUsers": [
    {
      "user": "__system",
      "db": "local"
    }
  ],
  "user": "__system@local",
  "_id": "c044e94198e245f3e61b39d230feb393-20171226105118923-200109374"
}

补充说明

我补充一下我的环境: 机器是8核16内存. 机器上部署有5个mongodb实例(在docker容器里面),1个mongos,1个config,3个shard(1个主,1个从,1个arbiter).
以下是docker的内存使用情况.

cpu使用情况内存使用情况
arbiter实例1.83%80.18MiB / 15.51GiB
shard2从3.09%5.306GiB / 15.51GiB
config1.81%1.449GiB / 15.51GiB
shard3主2.56%5.025GiB / 15.51GiB
mongos0.37%188.3MiB / 15.51GiB

其中config,shard主,shard从.3个实例都设置了CacheSizeGB为3.
目前从资源使用情况来看,CPU使用率都很低,内存config虽然限制了3GB,但整个docker容器只用了1.5GB的内存.

相关图片

相关文章