大数据量的查询命不中最优索引

问题来源

在生产环境中智能营销功能中有一个这样的任务：按租户每一个一次性营销，需要按 updatedAt 查最近 2 小时内有更新的客户，判断客户是是否应该进入此营销。

前提条件

• MongoDB 3.2
• 数据量巨大
• 在 member 上创建了几个带有 updatedAt 的索引，而且有较为合适的索引（accountId_isDeleted_updatedAt，accountId_isDeleted__id_updatedAt）

出现的问题

由于用户进行了批量导入客户的操作，所以在一段时间内创建了大量客户，updatedAt 也即更新为最新。

在进行上述任务时，完全未命中可利用的索引，而是一个仅前两个字段有关的索引。

可以看到 "stage": "CACHED_PLAN"，使用了缓存中的索引计划。

问题的调查

首先要说明，这个问题并没有用什么高级的方法去解决，而是用了 updatedId 去替换了索引。不过对于此问题的探究还是要记录一下。

下图为官网中的 Issue SERVER-32452。

image.png

需要说明的是，因为生产环境数据库的限制，所以不曾在 db 上执行过 explain() 函数进行诊断是否会命中更优的查询计划。

上述指明影响版本： 3.2、3.4、3.6、4.0。在 4.1.1 中修复，4.2 的生产环境发部版本可用。

workarounds

其在修复之前提到了三种 workaround 的方法来解决：

• 指定 hint
• 设置一个 query plan cache index filter 覆盖默认的索引
• 检查索引的创建，用更好的索引

解决详情

需要先了解 活动状态（active）和 非活动状态（inactive）的 缓存条目（cache entries）。
缓存条目会首先在 inactive 状态中，并且不会被 计划器 （planner）使用。
他们（inactive 状态的）只被用来跟踪 query 到的期望的 works 数目。（works 数目在 log 上有体现）
当一个计划（A）被执行时，它会与已存在的 inactive 条目（Bs） 进行比较：

• 如果 A 计划的 works 值 小于等于 Bs 的 works 值，A 就会成为 active 状态，而且 works 值会更新
• 如果 A 的 works 值大于 Bs 的值，则 A 计划将不会被缓存。

这个 works 值会被放大两倍（默认）。这个倍数是可以通过 InternalQueryCacheWorksGrowthCoefficient 服务参数来调整的。

---

附：

• 线上慢查询输出 profile 如下
• 读懂 MongoDB 的 explain-results/#executionstats 和 database-profiler

{
  "op": "query",
  "ns": "xxx.member",
  "query": {
    "batchSize": 100,
    "filter": {
      "accountId": {
        "$oid": "5c6122c08606bf4ca50731cc"
      },
      "isDeleted": false,
      "updatedAt": {
        "$gte": {
          "$date": "2020-04-11T07:00:03.811Z"
        },
        "$lt": {
          "$date": "2020-04-11T09:00:03.811Z"
        }
      }
    },
    "find": "member",
    "limit": 100,
    "skip": 0,
    "sort": {
      "updatedAt": 1
    }
  },
  "keysExamined": 4944141,
  "docsExamined": 4944141,
  "cursorExhausted": true,
  "nMatched": 0,
  "nModified": 0,
  "keyUpdates": 0,
  "writeConflicts": 0,
  "numYield": 48114,
  "locks": {
    "Collection": {
      "acquireCount": {
        "r": 48115
      }
    },
    "Database": {
      "acquireCount": {
        "r": 48115
      }
    },
    "Global": {
      "acquireCount": {
        "r": 96230
      }
    }
  },
  "nreturned": 100,
  "responseLength": 209208,
  "protocol": "op_query",
  "millis": 465271,
  "execStats": {
    "advanced": 100,
    "executionTimeMillisEstimate": 459838,
    "inputStage": {
      "advanced": 100,
      "executionTimeMillisEstimate": 458811,
      "inputStage": {
        "advanced": 0,
        "executionTimeMillisEstimate": 458466,
        "inputStage": {
          "advanced": 395,
          "alreadyHasObj": 0,
          "docsExamined": 4944141,
          "executionTimeMillisEstimate": 458397,
          "filter": {
            "$and": [
              {
                "updatedAt": {
                  "$lt": {
                    "$date": "2020-04-11T09:00:03.811Z"
                  }
                }
              },
              {
                "updatedAt": {
                  "$gte": {
                    "$date": "2020-04-11T07:00:03.811Z"
                  }
                }
              }
            ]
          },
          "inputStage": {
            "advanced": 4944141,
            "direction": "forward",
            "dupsDropped": 0,
            "dupsTested": 0,
            "executionTimeMillisEstimate": 12595,
            "indexBounds": {
              "_id": [
                "[MinKey, MaxKey]"
              ],
              "accountId": [
                "[ObjectId('5c6122c08606bf4ca50731cc'), ObjectId('5c6122c08606bf4ca50731cc')]"
              ],
              "createdAt": [
                "[MinKey, MaxKey]"
              ],
              "isDeleted": [
                "[false, false]"
              ],
              "score": [
                "[MinKey, MaxKey]"
              ]
            },
            "indexName": "accountId_1_isDeleted_1_score_1_createdAt_1__id_1",
            "indexVersion": 1,
            "invalidates": 0,
            "isEOF": 1,
            "isMultiKey": false,
            "isPartial": false,
            "isSparse": false,
            "isUnique": false,
            "keyPattern": {
              "_id": 1,
              "accountId": 1,
              "createdAt": 1,
              "isDeleted": 1,
              "score": 1
            },
            "keysExamined": 4944141,
            "nReturned": 4944141,
            "needTime": 0,
            "needYield": 0,
            "restoreState": 48114,
            "saveState": 48114,
            "seenInvalidated": 0,
            "stage": "IXSCAN",
            "works": 4944142
          },
          "invalidates": 0,
          "isEOF": 1,
          "nReturned": 395,
          "needTime": 4943746,
          "needYield": 0,
          "restoreState": 48114,
          "saveState": 48114,
          "stage": "FETCH",
          "works": 4944142
        },
        "invalidates": 0,
        "isEOF": 1,
        "nReturned": 0,
        "needTime": 4943747,
        "needYield": 0,
        "restoreState": 48114,
        "saveState": 48114,
        "stage": "SORT_KEY_GENERATOR",
        "works": 4944143
      },
      "invalidates": 0,
      "isEOF": 1,
      "limitAmount": 100,
      "memLimit": 33554432,
      "memUsage": 209484,
      "nReturned": 100,
      "needTime": 4944143,
      "needYield": 0,
      "restoreState": 48114,
      "saveState": 48114,
      "sortPattern": {
        "updatedAt": 1
      },
      "stage": "SORT",
      "works": 4944243
    },
    "invalidates": 0,
    "isEOF": 1,
    "nReturned": 100,
    "needTime": 0,
    "needYield": 0,
    "restoreState": 48114,
    "saveState": 48114,
    "stage": "CACHED_PLAN",
    "works": 100
  },
  "ts": {
    "$date": "2020-04-11T09:07:49.085Z"
  },
  "client": "192.168.13.210",
  "allUsers": [
    {
      "db": "xxx-xxx",
      "user": "xxx"
    }
  ],
  "user": "xxx@xxx"
}