根據(jù)索引in查詢, 掃描索引3條 keysExamined 3, 返回文檔數(shù)1條 nReturned 1, 耗時 millis 452毫秒, execStats中executionTimeMillisEstimate都是0.
問題1: executionTimeMillisEstimate都是0. 為啥整個查詢卻消耗452毫秒.
問題2: 走了索引,只掃描索引3條,返回文檔數(shù)1條,消耗452毫秒,怎么優(yōu)化或者怎么調(diào)整它.
問題3: 在其它的執(zhí)行計劃中我看到inputStage.stage.FETCH操作耗時也很高,在200~1000毫秒.FETCH根據(jù)索引取下文檔為啥還要這么多時間呢.
求助 @Mongoing中文社區(qū) @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"
}

補充說明

我補充一下我的環(huán)境: 機器是8核16內(nèi)存. 機器上部署有5個mongodb實例(在docker容器里面),1個mongos,1個config,3個shard(1個主,1個從,1個arbiter).
以下是docker的內(nèi)存使用情況.

其中config,shard主,shard從.3個實例都設(shè)置了CacheSizeGB為3.
目前從資源使用情況來看,CPU使用率都很低,內(nèi)存config雖然限制了3GB,但整個docker容器只用了1.5GB的內(nèi)存.

1. 疑問1: 按照你說的方式調(diào)小cacheSizeGB,應(yīng)該怎么調(diào)整比較合理
2. 疑問2: config只用了1.5GB內(nèi)存(限制了CacheSizeGB3GB).可以說明索引都加載到了內(nèi)存中嗎?
3. 疑問3: mongodb remove數(shù)據(jù)后,查詢卻越來越慢是什么情況?