Database Manual / Reference / Database Commands / Administration

setQuerySettings (database command数据库命令)

Definition定义

setQuerySettings

New in version 8.0.在版本8.0中新增。

You can use query settings to add index hints, define operation rejection filters, and set other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown.您可以使用查询设置添加索引提示、定义操作拒绝筛选器和设置其他字段。这些设置适用于整个集群上的查询形状。集群在关闭后保留设置。

For example, query settings allow you to use an index for all executions of a query shape in a cluster.例如，查询设置允许您对集群中查询形状的所有执行使用索引。

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query.查询优化器在查询规划期间使用查询设置作为额外输入，这会影响为运行查询而选择的计划。

setQuerySettings defines query settings used by the find, distinct, and aggregate commands.定义find、distinct和aggregate命令使用的查询设置。

Starting in MongoDB 8.0, use query settings instead of 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.查询设置比索引筛选器具有更多功能。此外，索引筛选器不是持久的，您无法轻松为所有集群节点创建索引筛选器。

Note

To remove query settings, use removeQuerySettings. To obtain the query settings, use a $querySettings stage in an aggregation pipeline.要删除查询设置，请使用removeQuerySettings。要获取查询设置，请在聚合管道中使用$querySettings阶段。

Syntax语法

You can add or update query settings using either of the two syntax specifications shown in this section.您可以使用本节中显示的两种语法规范中的任何一种添加或更新查询设置。

Set Query Settings by Passing in a Query通过传入查询设置查询设置

In the following syntax, you provide:在以下语法中，您提供：

  • The same fields as a find, distinct, or aggregate command. See the syntax sections on the pages for those commands for the fields you can include in setQuerySettings.与find、distinct或aggregate命令相同的字段。有关可以包含在setQuerySettings中的字段的命令，请参阅页面上的语法部分。
  • A $db field to specify the database for the query settings.$db字段，用于指定查询设置的数据库。
  • A settings document with indexHints and other fields.包含indexHints和其他字段的settings文档。
db.adminCommand( {
   setQuerySettings: {
      <fields>,  // Provide fields for
                 // find, distinct, or aggregate command
      $db: <string>  // Provide a database name
   },
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>,
      comment: <BSON type>
   }
} )

Set Query Settings by Passing in a Query Shape Hash通过传入查询形状哈希来设置查询设置

You can provide an existing query shape hash string in setQuerySettings and an updated settings document with indexHints and other fields:您可以在setQuerySettings中提供现有的查询形状哈希字符串，以及带有indexHints和其他字段的更新settings文档：

db.adminCommand( {
   setQuerySettings: <string>,  // Provide an existing query shape hash string
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>,
      comment: <BSON type>
   }
} )

A query shape hash is a string that uniquely identifies the query shape. An example query shape hash is "F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C".查询形状哈希是唯一标识查询形状的字符串。一个示例查询形状哈希是"F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"。

To obtain the query shape hash string, do any of these:要获取查询形状哈希字符串，请执行以下任一操作：

If you set the query settings using a hash string, then you won't have the representativeQuery field in the $querySettings aggregation stage output.如果使用哈希字符串设置查询设置，则$querySettings聚合阶段输出中不会有representativeQuery字段。

Tip

In both syntax variations, you can provide an array of indexHints documents. You can omit the array brackets if you provide only one indexHints document.在这两种语法变体中，您都可以提供一系列indexHints文档。如果只提供一个indexHints文档，则可以省略数组括号。

Command Fields命令字段

The command takes these fields:该命令包含以下字段：

Field字段Field Type字段类型Necessity必要性Description描述
setQuerySettingsdocument or string文档或字符串Required必需

You can provide either:您可以提供：

  • The same fields as those in a find, distinct, or aggregate command, and a $db field with the database associated with the original command.与find、distinct或aggregate命令中的字段相同的字段，以及与原始命令关联的数据库的$db字段。
  • An existing query shape hash string that uniquely identifies the query shape. An example query shape hash is "F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"`.唯一标识查询形状的现有查询形状哈希字符串。一个示例查询形状哈希是"F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"。
indexHints.nsdocument文档Optional可选

Namespace for index hints. Only required when optional index hints are specified.索引提示的命名空间。仅当指定了可选索引提示时才需要。

dbstring字符串Required必需Name of the database for index hints.索引提示数据库的名称。
collstring字符串Required必需Name of the collection for index hints.索引提示集合的名称。
indexHints.allowedIndexesarray数组Optional可选

Array of indexes for index hints. An index hint can be one of these:索引提示的索引数组。索引提示可以是以下之一：

  • Index name索引名称
  • index key pattern索引键模式
  • $natural hint提示

For more details, see Indexes and hint().有关更多详细信息，请参阅索引和hint()。

queryFrameworkstring字符串Optional可选

Query framework查询框架 string can be set to:string可以设置为：

rejectboolean布尔值Optional可选

If true:如果为true：

  • New queries with the matching query shape are rejected and the query response states the query is rejected.具有匹配查询形状的新查询将被拒绝，查询响应将声明该查询被拒绝。
  • Any queries currently in progress aren't rejected.当前正在进行的任何查询都不会被拒绝。

Default is false.默认值为false。

To enable a query shape, run setQuerySettings again for the query shape and set reject to false. If you set reject to true and then back to false using setQuerySettings then:要启用查询形状，请再次对查询形状运行setQuerySettings，并将reject设置为false。如果你使用setQuerySettings将reject设置为true，然后再设置为false，那么：

  • If your settings document isn't empty, then setQuerySettings enables the query shape.如果settings文档不是空的，那么setQuerySettings将启用查询形状。
  • If your settings document only contains reject: false, then setQuerySettings returns an error. Instead, use the removeQuerySettings command to remove the settings and then use setQuerySettings to add query settings.如果settings文档只包含reject:false，则setQuerySettings将返回错误。相反，使用removeQuerySettings命令删除设置，然后使用setQuerySettings添加查询设置。
commentBSON typeOptional可选

A comment can be any valid BSON type. For example: string, object, and so on.注释可以是任何有效的BSON类型。例如：字符串、对象等。

You can use a comment to provide additional information about the query settings. For example, to add a string that indicates why you added the query settings, use comment: "Index hint for orderDate_1 index to improve query performance".您可以使用注释提供有关查询设置的其他信息。例如，要添加一个字符串来指示添加查询设置的原因，请使用comment: "Index hint for orderDate_1 index to improve query performance"。

To update a comment, run setQuerySettings again and use comment: { body: { msg: "Updated comment" } }.要更新注释，请再次运行setQuerySettings并使用comment: { body: { msg: "Updated comment" } }。

You cannot remove a comment, but you can set it to a string with a space character. You can remove the query settings using removeQuerySettings.您不能删除注释，但可以将其设置为带有空格字符的字符串。您可以使用removeQuerySettings删除查询设置。

Comments appear in the $querySettings aggregation pipeline stage output, the explain() command output, and the slow query logs.注释出现在$querySettings聚合管道阶段输出、explain()命令输出和慢速查询日志中。

Available starting in MongoDB 8.1 (and 8.0.4).从MongoDB 8.1（和8.0.4）开始可用。

Examples示例

The following examples create a collection and add query settings for different commands. The examples use one index for all executions of a query shape run in the cluster.以下示例创建了一个集合，并为不同的命令添加了查询设置。这些示例对集群中运行的查询形状的所有执行使用一个索引。

1

Create the example collection and indexes创建示例集合和索引

Run:运行：

 // Create pizzaOrders collection
 db.pizzaOrders.insertMany( [
   { _id: 0, type: "pepperoni", size: "small", price: 19,
     totalNumber: 10, orderDate: ISODate( "2023-03-13T08:14:30Z" ) },
   { _id: 1, type: "pepperoni", size: "medium", price: 20,
     totalNumber: 20, orderDate: ISODate( "2023-03-13T09:13:24Z" ) },
   { _id: 2, type: "pepperoni", size: "large", price: 21,
     totalNumber: 30, orderDate: ISODate( "2023-03-17T09:22:12Z" ) },
   { _id: 3, type: "cheese", size: "small", price: 12,
     totalNumber: 15, orderDate: ISODate( "2023-03-13T11:21:39.736Z" ) },
   { _id: 4, type: "cheese", size: "medium", price: 13,
     totalNumber: 50, orderDate: ISODate( "2024-01-12T21:23:13.331Z" ) },
   { _id: 5, type: "cheese", size: "large", price: 14,
     totalNumber: 10, orderDate: ISODate( "2024-01-12T05:08:13Z" ) },
   { _id: 6, type: "vegan", size: "small", price: 17,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:08:13Z" ) },
   { _id: 7, type: "vegan", size: "medium", price: 18,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:10:13Z" ) }
] )

 // Create ascending index on orderDate field
 db.pizzaOrders.createIndex( { orderDate: 1 } )

 // Create ascending index on totalNumber field
 db.pizzaOrders.createIndex( { totalNumber: 1 } )

The indexes have the default names orderDate_1 and totalNumber_1.索引的默认名称为orderDate_1和totalNumber_1。

2

Add query settings for a find command为查找命令添加查询设置

The following example adds query settings for a find command. The example provides fields in setQuerySettings for the find command, and includes the orderDate_1 index in allowedIndexes.以下示例添加find命令的查询设置。该示例在setQuerySettings中为find命令提供了字段，并在allowdIndexes中包含了orderDate_1索引。

db.adminCommand( {
   setQuerySettings: {
      find: "pizzaOrders",
      filter: {
         orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) }
      },
      sort: {
         totalNumber: 1
      },
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "orderDate_1" ]
      },
      queryFramework: "classic",
      comment: "Index hint for orderDate_1 index to improve query performance"
   }
} )
3

(Optional) Verify the query settings（可选）验证查询设置

Run this explain command:运行以下explain命令：

db.pizzaOrders.explain().find( { orderDate: { $gt: ISODate(
"2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } )

The following truncated output shows the query settings are set:以下截断的输出显示了已设置的查询设置：

queryPlanner: {
   winningPlan: {
     stage: 'SINGLE_SHARD',
     shards: [
       {
         explainVersion: '1',
         ...
         namespace: 'test.pizzaOrders',
         indexFilterSet: false,
         parsedQuery: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
         querySettings: {
           indexHints: {
             ns: { db: 'test', coll: 'pizzaOrders' },
             allowedIndexes: [ 'orderDate_1' ]
           },
           queryFramework: 'classic',
           comment: 'Index hint for orderDate_1 index to improve query performance'
         },
         ...
       }
     ...
     ]
   }
}
4

(Optional) Run the query（可选）运行查询

The following example runs the query:以下示例运行查询：

db.pizzaOrders.find(
   { orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 }
)

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query.查询优化器在查询规划期间使用查询设置作为额外输入，这会影响为运行查询而选择的计划。

Query output:查询输出：

[
   {
      _id: 0,
      type: 'pepperoni',
      size: 'small',
      price: 19,
      totalNumber: 10,
      orderDate: ISODate('2023-03-13T08:14:30.000Z')
   },
   {
      _id: 5,
      type: 'cheese',
      size: 'large',
      price: 14,
      totalNumber: 10,
      orderDate: ISODate('2024-01-12T05:08:13.000Z')
   },
   {
      _id: 3,
      type: 'cheese',
      size: 'small',
      price: 12,
      totalNumber: 15,
      orderDate: ISODate('2023-03-13T11:21:39.736Z')
   },
   {
      _id: 1,
      type: 'pepperoni',
      size: 'medium',
      price: 20,
      totalNumber: 20,
      orderDate: ISODate('2023-03-13T09:13:24.000Z')
   },
   {
      _id: 2,
      type: 'pepperoni',
      size: 'large',
      price: 21,
      totalNumber: 30,
      orderDate: ISODate('2023-03-17T09:22:12.000Z')
   },
   {
      _id: 4,
      type: 'cheese',
      size: 'medium',
      price: 13,
      totalNumber: 50,
      orderDate: ISODate('2024-01-12T21:23:13.331Z')
   }
]
5

(Optional) Obtain the query settings（可选）获取查询设置

The following example uses a $querySettings stage in an aggregation pipeline to obtain the query settings:以下示例使用聚合管道中的$querySettings阶段来获取查询设置：

db.aggregate( [
   { $querySettings: {} }
] )

Truncated output, which includes the queryShapeHash field:截断输出，其中包括queryShapeHash字段：

[
   {
      queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1',
      settings: {
         indexHints: [
            {
               ns: { db: 'test', coll: 'pizzaOrders' },
               allowedIndexes: [ 'orderDate_1' ]
            }
         ],
         queryFramework: 'classic',
         comment: 'Index hint for orderDate_1 index to improve query performance'
      },
      representativeQuery: {
         find: 'pizzaOrders',
         filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
         sort: { totalNumber: 1 },
         '$db': 'test'
      }
   }
]
6

Add query settings for a distinct command为不同的命令添加查询设置

The following example adds query settings for a distinct command:以下示例添加了distinct命令的查询设置：

db.adminCommand( {
   setQuerySettings: {
      distinct: "pizzaOrders",
      key: "totalNumber",
      query: { type: "pepperoni"} ,
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "orderDate_1" ]
      },
      queryFramework: "classic",
      comment: "Index hint for orderDate_1 index to improve query performance"
   }
} )
7

Add query settings for an aggregate command为聚合命令添加查询设置

The following example adds query settings for an aggregate command:以下示例为aggregate命令添加查询设置：

db.adminCommand( {
   setQuerySettings: {
      aggregate: "pizzaOrders",
      pipeline: [
         { $match: { size: "medium" } },
         { $group: {
            _id: "$type",
            totalMediumPizzaOrdersGroupedByType: { $sum: "$totalNumber" }
         } }
      ],
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "totalNumber_1" ]
      },
      queryFramework: "classic",
      comment: "Index hint for totalNumber_1 index to improve query performance"
   }
} )

Learn More了解更多