Database Manual / CRUD Operations / CRUD Concepts

Query Plans查询计划

For any given query, the MongoDB query planner chooses and caches the most efficient query plan given the available indexes. To evaluate the efficiency of query plans, the query planner runs all candidate plans during a trial period. In general, the winning plan is the query plan that produces the most results during the trial period while performing the least amount of work.对于任何给定的查询，MongoDB查询计划器在给定可用索引的情况下选择并缓存最有效的查询计划。为了评估查询计划的效率，查询计划器在试用期内运行所有候选计划。一般来说，获胜计划是在试用期内产生最多结果同时执行最少工作量的查询计划。

The associated plan cache entry is used for subsequent queries with the same plan cache query shape.关联的计划缓存条目用于具有相同计划缓存查询形状的后续查询。

The following diagram illustrates the query planner logic:下图说明了查询计划器逻辑：

A diagram of MongoDB's query planner logic.

Note

Using explain ignores all existing plan cache entries and prevents the MongoDB query planner from creating a new plan cache entry.使用explain会忽略所有现有的计划缓存条目，并阻止MongoDB查询计划器创建新的计划缓存条目的操作。

Plan Cache Entry State计划缓存条目状态

Each plan cache query shape is associated with one of three states in the cache:每个计划缓存查询形状都与缓存中的三个状态之一相关联：

State状态Description描述
Missing

No entry for this shape exists in the cache.缓存中不存在此形状的条目。

For a query, if the cache entry state for a plan cache query shape is Missing:对于查询，如果计划缓存查询形状的缓存条目状态为“缺少”：

  1. Candidate plans are evaluated and a winning plan is selected.评估候选计划并选择获胜计划。
  2. The cache creates an entry for the plan cache query shape in state Inactive with a value that quantifies the amount of work required by the plan.缓存为处于“非活动”状态的计划缓存查询形状创建一个条目，该条目的值量化了计划所需的工作量。
Inactive

The entry in the cache is a placeholder entry for this shape. That is, the planner has seen the shape, calculated a value that quantifies the amount of work required by the plan and stored the shape placeholder entry but the plan cache query shape is not used to generate query plans.缓存中的条目是此形状的占位符条目。也就是说，计划器已经看到了形状，计算了一个量化计划所需工作量的值，并存储了形状占位符条目，但计划缓存查询形状不用于生成查询计划。

For a query, if the cache entry state for a shape is Inactive:对于查询，如果形状的缓存条目状态为“非活动”：

  1. Candidate plans are evaluated and a winning plan is selected.评估候选计划并选择获胜计划。

  2. The selected plan's value that quantifies the amount of work required by the plan is compared to the Inactive entry's. If the selected plan's value is:将所选计划的值（量化计划所需的工作量）与“未活动”条目进行比较。如果所选计划值为：

    • Less than or equal to the Inactive entry's:小于或等于非活动条目：

      The selected plan replaces the placeholder Inactive entry and has an Active state.所选计划将替换占位符“非活动”条目，并具有“活动”状态。

      If before the replacement happens, the Inactive entry becomes Active (for example, due to another query operation), the newly active entry will only be replaced if its value that quantifies the amount of work required by the plan is greater than the selected plan.如果在替换发生之前，非活动条目变为活动条目（例如，由于另一个查询操作），则只有当新活动条目的量化计划所需工作量的值大于所选计划时，才会替换新活动条目。

    • Greater than the Inactive entry's:大于非活动条目：
      The Inactive entry remains but its value that quantifies the amount of work required by the plan is incremented.非活动条目仍然存在，但其量化计划所需工作量的值将递增。
Active

The entry in the cache is for the winning plan. The planner can use this entry to generate query plans.缓存中的条目用于获胜计划。计划员可以使用此条目生成查询计划。

For a query, if the cache entry state for a shape is Active:对于查询，如果形状的缓存条目状态为“活动”：

The active entry is used to generate query plans.活动条目用于生成查询计划。

The planner also evaluates the entry's performance and if its value that quantifies the amount of work required by the plan no longer meets the selection criterion, it will transition to Inactive state.计划器还会评估条目的性能，如果其量化计划所需工作量的值不再符合选择标准，则将转换为“非活动”状态。

See Plan Cache Flushes for additional scenarios that trigger changes to the plan cache.有关触发计划缓存更改的其他场景，请参阅计划缓存刷新。

Query Plan and Cache Information查询计划和缓存信息

To view the query plan information for a given query, you can use db.collection.explain() or the cursor.explain() .要查看给定查询的查询计划信息，可以使用db.collection.explain()或cursor.explain()。

To view plan cache information for a collection, you can use the $planCacheStats aggregation stage.要查看集合的计划缓存信息，可以使用$planCacheStats聚合阶段。

Plan Cache Flushes计划缓存刷新

The query plan cache does not persist if a mongod restarts or shuts down. In addition:如果mongod重新启动或关闭，查询计划缓存将不会持久。此外：

  • Catalog operations like index or collection drops clear the plan cache.索引或集合删除等目录操作会清除计划缓存。
  • Least recently used (LRU) cache replacement mechanism clears the least recently accessed cache entry, regardless of state.最近最少使用（LRU）缓存替换机制会清除最近最少访问的缓存条目，而不管其状态如何。

Users can also:用户还可以：

Tip

planCacheShapeHash and 和planCacheKey

Plan Cache Debug Info Size Limit计划缓存调试信息大小限制

Starting in MongoDB 5.0, the plan cache will save full plan cache entries only if the cumulative size of the plan caches for all collections is lower than 0.5 GB. 从MongoDB 5.0开始，只有当所有集合的计划缓存的累积大小低于0.5 GB时，计划缓存才会保存完整的计划缓存条目。When the cumulative size of the plan caches for all collections exceeds this threshold, additional plan cache entries are stored without the following debug information:当所有集合的计划缓存的累积大小超过此阈值时，将存储其他计划缓存条目，但不包含以下调试信息：

The estimated size in bytes of a plan cache entry is available in the output of $planCacheStats.$planCacheStats的输出中提供了计划缓存条目的估计大小（以字节为单位）。

planCacheShapeHash and 和planCacheKey

planCacheShapeHash

To help identify slow queries with the same plan cache query shape, each plan cache query shape is associated with a query hash. The plan cache query shape hash is a hexadecimal string that represents a hash of the query shape and is dependent only on the query shape.为了帮助识别具有相同计划缓存查询形状的慢速查询，每个计划缓存查询形状都与一个查询哈希相关联。计划缓存查询形状哈希是一个十六进制字符串，表示查询形状的哈希，仅依赖于查询形状。

Note

As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions between different query shapes is unlikely.与任何哈希函数一样，两个不同的查询形状可能会产生相同的哈希值。然而，不同查询形状之间不太可能发生哈希冲突。

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。If you're using an earlier MongoDB version, you'll only see the queryHash field. 如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

planCacheKey

To provide more insight into the query plan cache, MongoDB offers the planCacheKey.为了更深入地了解查询计划缓存，MongoDB提供了planCacheKey。

planCacheKey is a hash of the plan cache query shape, further distinguished by any available indexes for the shape.是计划缓存查询形状的哈希值，通过形状的任何可用索引进一步区分。

Note

Unlike planCacheShapeHash, planCacheKey is a function of both the query shape and the currently available indexes for the shape. 与planCacheShapeHash不同，planCacheKey是查询形状和形状当前可用索引的函数。That is, if indexes that can support the query shape are added/dropped, the planCacheKey value may change whereas the planCacheShapeHash value would not change.也就是说，如果添加/删除可以支持查询形状的索引，则planCacheKey值可能会更改，而planCacheShapeHash值不会更改。

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。If you're using an earlier MongoDB version, you'll only see the queryHash field. 如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

For example, consider a collection foo with the following indexes:例如，考虑一个具有以下索引的集合foo：

db.foo.createIndex( { x: 1 } )
db.foo.createIndex( { x: 1, y: 1 } )
db.foo.createIndex( { x: 1, z: 1 }, { partialFilterExpression: { x: { $gt: 10 } } } )

The following queries on the collection have the same shape:对集合的以下查询具有相同的形状：

db.foo.explain().find( { x: { $gt: 5 } } )  // Query Operation 1
db.foo.explain().find( { x: { $gt: 20 } } ) // Query Operation 2

Given these queries, the index with the partial filter expression can support query operation 2 but not support query operation 1. 给定这些查询，具有部分筛选表达式的索引可以支持查询操作2，但不支持查询操作1。Since the indexes available to support query operation 1 differs from query operation 2, the two queries have different planCacheKey.由于可用于支持查询操作1的索引不同于查询操作2，因此这两个查询具有不同的planCacheKey。

If one of the indexes were dropped, or if a new index { x: 1, a: 1 } were added, the planCacheKey for both query operations will change.如果其中一个索引被删除，或者添加了新的索引{ x: 1, a: 1 }，则两个查询操作的planCacheKey都将更改。

Availability可用性

The planCacheShapeHash and planCacheKey are available in:planCacheShapeHash和planCacheKey可在以下位置获得：

Index Filters索引筛选器

Index filters are set with the planCacheSetFilter command and determine which indexes the planner evaluates for a query shape. 使用planCacheSetFilter命令设置索引筛选器，并确定计划器为查询形状计算哪些索引。A plan cache query shape consists of a combination of query, sort, and projection specifications. If an index filter exists for a given query shape, the planner only considers those indexes specified in the filter.计划缓存查询形状由查询、排序和投影规范的组合组成。如果给定的查询形状存在索引筛选器，则计划器只考虑筛选器中指定的索引。

When an index filter exists for the plan cache query shape, MongoDB ignores the hint(). 当计划缓存查询形状存在索引筛选器时，MongoDB会忽略hint()。To see whether MongoDB applied an index filter for a query shape, check the indexFilterSet field of either the db.collection.explain() or the cursor.explain() method.要查看MongoDB是否为查询形状应用了索引筛选器，请检查db.collection.explain()或cursor.explain()方法的indexFilterSet字段。

Index filters only affect which indexes the planner evaluates; the planner may still select the collection scan as the winning plan for a given plan cache query shape.索引筛选器只影响计划器评估哪些索引；规划者仍然可以选择集合扫描作为给定计划缓存查询形状的获胜计划。

Index filters exist for the duration of the server process and do not persist after shutdown. MongoDB also provides a command to manually remove filters.索引筛选器在服务器进程期间存在，并且在关闭后不会持续存在。MongoDB还提供了一个手动删除筛选器的命令。

Because index filters override the expected behavior of the planner as well as the hint() method, use index filters sparingly.因为索引筛选器覆盖了规划器以及hint()方法的预期行为，所以要谨慎使用索引筛选器。

Starting in MongoDB 6.0, an index filter uses the collation previously set using the planCacheSetFilter command.从MongoDB 6.0开始，索引筛选器使用之前使用planCacheSetFilter命令设置的排序规则。

Starting in MongoDB 8.0, use query settings instead of adding index filters. Index filters are deprecated starting in MongoDB 8.0.从MongoDB 8.0开始，使用查询设置，而不是添加索引筛选器。索引筛选器从MongoDB 8.0开始就被弃用。

Query settings have more functionality than index filters. Also, index filters aren't persistent and you cannot easily create index filters for all cluster nodes. 查询设置比索引筛选器具有更多功能。此外，索引筛选器不是持久的，您无法轻松为所有集群节点创建索引筛选器。To add query settings and explore examples, see setQuerySettings.要添加查询设置并探索示例，请参阅setQuerySettings。

Tip