mongodb-query-optimizer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MongoDB Query Optimizer

MongoDB 查询优化器

When this skill is invoked

此技能的调用场景

Invoke only when the user wants:

Query/index optimization or performance help
Why a query is slow or how to speed it up
Slow queries on their cluster and/or how to optimize them

Do not invoke for routine query authoring unless the user has requested help with optimization, slow queries, or indexing.

仅在用户有以下需求时调用：

查询/索引优化或性能相关帮助
想知道查询为什么慢，或者如何提速
想了解集群上的慢查询和/或如何优化它们

不要为常规查询编写需求调用此技能，除非用户明确要求优化、慢查询或索引相关帮助。

High Level Workflow

整体工作流程

General Performance Help

通用性能帮助

If the user wants to examine slow queries, or is looking for general performance suggestions (not regarding any particular query):

Use MongoDB MCP server atlas-get-performance-advisor tool to fetch slow query logs and performance advisor output
Make suggestions based on this information

If Atlas MCP Server for Atlas is not configured or you don’t have enough information to run atlas-get-performance-advisor against the correct cluster, tell the user that general performance analysis requires Atlas MCP Server configuration with API credentials, and suggest they configure it or ask about a specific query instead.

如果用户想要排查慢查询，或者寻求通用性能建议（不针对特定查询）：

使用MongoDB MCP服务的atlas-get-performance-advisor工具拉取慢查询日志和性能顾问输出
基于这些信息给出建议

如果尚未配置Atlas MCP服务，或者你没有足够信息针对正确集群运行atlas-get-performance-advisor，请告知用户通用性能分析需要配置带有API凭证的Atlas MCP服务，建议用户完成配置，或者转而询问特定查询的相关问题。

Help with a Specific Query

特定查询帮助

If the user is asking about a particular query:

Use collection-indexes, explain, and find MCP tools to get existing indexes on the collection, explain() output for the query, and a sample document from the collection
Use atlas-get-performance-advisor MCP tool to fetch slow query logs and performance advisor output

Then make an optimization suggestion based on collected information and MongoDB best practices and examples from reference files. Prefer creating an index that fully covers the query if possible. If you cannot use MongoDB MCP Server then still try to make a suggestion.

如果用户询问的是某个具体查询：

使用collection-indexes、explain和find MCP工具获取集合现有索引、查询的explain()输出，以及集合的样本文档
使用atlas-get-performance-advisor MCP工具拉取慢查询日志和性能顾问输出

之后基于收集到的信息、MongoDB最佳实践和参考文件中的示例给出优化建议。如果可能，优先创建可以完全覆盖查询的索引。即使你无法使用MongoDB MCP服务，也请尽量给出建议。

MCP: available tools

MCP可用工具

How to invoke. Call the MongoDB MCP server with the exact tool name as

toolName

and a single arguments object as

arguments

. Do not pass the tool name as an option, query param, or nested key; pass it as the MCP tool name and the parameters as the arguments object. Full MCP Server tool reference: MongoDB MCP Server Tools.

Database tools (when the MCP cluster connection works):

Tool name (exact) Arguments object

Tool name (exact)	Arguments object
`collection-indexes`	`{ "database": "<db>", "collection": "<coll>" }` — both required strings.
`explain`	`{ "database": "<db>", "collection": "<coll>", "method": [ { "name": "find", "arguments": { "filter": {...}, "sort": {...}, "limit": N } } ], "verbosity": "executionStats" }` . `method` is an array of one object: `name` is `"find"` , `"aggregate"` , or `"count"` ; `arguments` holds that method's params (e.g. find: `filter` , `sort` , `limit` ; aggregate: `pipeline` ; count: `query` ). Optional `verbosity` : `"queryPlanner"` (default), `"executionStats"` , `"queryPlannerExtended"` , `"allPlansExecution"` .
`find`	`{ "database": "<db>", "collection": "<coll>", "filter": {...}, "projection": {...}, "sort": {...}, "limit": N }` — `database` , `collection` , and `filter` are required. Optional: `projection` , `sort` , `limit` .

collection-indexes

{ "database": "<db>", "collection": "<coll>" }

— both required strings.

explain

{ "database": "<db>", "collection": "<coll>", "method": [ { "name": "find", "arguments": { "filter": {...}, "sort": {...}, "limit": N } } ], "verbosity": "executionStats" }

method

is an array of one object:

name

"find"

"aggregate"

, or

"count"

;

arguments

holds that method's params (e.g. find:

filter

sort

limit

; aggregate:

pipeline

; count:

query

). Optional

verbosity

"queryPlanner"

(default),

"executionStats"

"queryPlannerExtended"

"allPlansExecution"

find

{ "database": "<db>", "collection": "<coll>", "filter": {...}, "projection": {...}, "sort": {...}, "limit": N }

—

database

collection

, and

filter

are required. Optional:

projection

sort

limit

Atlas tools (when Atlas API credentials are configured):

Tool name (exact) Arguments object

Tool name (exact)	Arguments object
`atlas-list-projects`	`{}` or `{ "orgId": "<24-char hex>" }` . Returns projects with their IDs; use to get `projectId` for Performance Advisor.
`atlas-get-performance-advisor`	Required: `"projectId"` (24-character hex string), `"clusterName"` (string, 1–64 chars, alphanumeric/underscore/dash). Optional: `"operations"` — array of strings from `"suggestedIndexes"` , `"dropIndexSuggestions"` , `"slowQueryLogs"` , `"schemaSuggestions"` (request only what you need); for slowQueryLogs only: `"since"` (ISO 8601 date-time), `"namespaces"` (array of `"db.coll"` strings).

atlas-list-projects

{}

{ "orgId": "<24-char hex>" }

. Returns projects with their IDs; use to get

projectId

for Performance Advisor.

atlas-get-performance-advisor

Required:

"projectId"

(24-character hex string),

"clusterName"

(string, 1–64 chars, alphanumeric/underscore/dash). Optional:

"operations"

— array of strings from

"suggestedIndexes"

"dropIndexSuggestions"

"slowQueryLogs"

"schemaSuggestions"

(request only what you need); for slowQueryLogs only:

"since"

(ISO 8601 date-time),

"namespaces"

(array of

"db.coll"

strings).

For a user question, try to fetch information from both the connection string and Atlas API related to the query you are optimizing.

调用方式：调用MongoDB MCP服务时，将准确的工具名作为

toolName

，单个参数对象作为

arguments

。不要将工具名作为选项、查询参数或嵌套键传递；请将其作为MCP工具名，参数作为参数对象传递。完整MCP服务工具参考：MongoDB MCP Server Tools。

数据库工具（MCP集群连接可用时）：

准确工具名参数对象

准确工具名	参数对象
`collection-indexes`	`{ "database": "<db>", "collection": "<coll>" }` — 两个都是必填字符串。
`explain`	`{ "database": "<db>", "collection": "<coll>", "method": [ { "name": "find", "arguments": { "filter": {...}, "sort": {...}, "limit": N } } ], "verbosity": "executionStats" }` 。 `method` 是仅包含一个对象的数组： `name` 可以是 `"find"` 、 `"aggregate"` 或 `"count"` ； `arguments` 存放对应方法的参数（例如find的参数： `filter` 、 `sort` 、 `limit` ；aggregate的参数： `pipeline` ；count的参数： `query` ）。可选参数 `verbosity` 可选值： `"queryPlanner"` （默认）、 `"executionStats"` 、 `"queryPlannerExtended"` 、 `"allPlansExecution"` 。
`find`	`{ "database": "<db>", "collection": "<coll>", "filter": {...}, "projection": {...}, "sort": {...}, "limit": N }` — `database` 、 `collection` 和 `filter` 为必填项。可选参数： `projection` 、 `sort` 、 `limit` 。

collection-indexes

{ "database": "<db>", "collection": "<coll>" }

— 两个都是必填字符串。

explain

{ "database": "<db>", "collection": "<coll>", "method": [ { "name": "find", "arguments": { "filter": {...}, "sort": {...}, "limit": N } } ], "verbosity": "executionStats" }

。

method

是仅包含一个对象的数组：

name

可以是

"find"

、

"aggregate"

或

"count"

；

arguments

存放对应方法的参数（例如find的参数：

filter

、

sort

、

limit

；aggregate的参数：

pipeline

；count的参数：

query

）。可选参数

verbosity

可选值：

"queryPlanner"

（默认）、

"executionStats"

、

"queryPlannerExtended"

、

"allPlansExecution"

。

find

{ "database": "<db>", "collection": "<coll>", "filter": {...}, "projection": {...}, "sort": {...}, "limit": N }

—

database

、

collection

和

filter

为必填项。可选参数：

projection

、

sort

、

limit

。

Atlas工具（Atlas API凭证已配置时）：

准确工具名参数对象

准确工具名	参数对象
`atlas-list-projects`	`{}` 或 `{ "orgId": "<24位十六进制字符串>" }` 。返回项目及其ID；用于获取性能顾问所需的 `projectId` 。
`atlas-get-performance-advisor`	必填项： `"projectId"` （24位十六进制字符串）、 `"clusterName"` （字符串，1-64个字符，支持字母/数字/下划线/短横线）。可选项： `"operations"` — 字符串数组，可选值为 `"suggestedIndexes"` 、 `"dropIndexSuggestions"` 、 `"slowQueryLogs"` 、 `"schemaSuggestions"` （仅请求你需要的内容）；仅针对slowQueryLogs的可选参数： `"since"` （ISO 8601格式日期时间）、 `"namespaces"` （ `"db.coll"` 格式的字符串数组）。

atlas-list-projects

{}

或

{ "orgId": "<24位十六进制字符串>" }

。返回项目及其ID；用于获取性能顾问所需的

projectId

。

atlas-get-performance-advisor

必填项：

"projectId"

（24位十六进制字符串）、

"clusterName"

（字符串，1-64个字符，支持字母/数字/下划线/短横线）。可选项：

"operations"

— 字符串数组，可选值为

"suggestedIndexes"

、

"dropIndexSuggestions"

、

"slowQueryLogs"

、

"schemaSuggestions"

（仅请求你需要的内容）；仅针对slowQueryLogs的可选参数：

"since"

（ISO 8601格式日期时间）、

"namespaces"

（

"db.coll"

格式的字符串数组）。

对于用户的问题，尽量从连接字符串和Atlas API两个渠道拉取与你正在优化的查询相关的信息。

1. DB connection string works for MongoDB MCP

1. MongoDB MCP的DB连接字符串可用

Typical flow: call

collection-indexes

→

explain

→

find

(sample doc).

collection-indexes
— Use the result's
```
classicIndexes
```
(each has
```
name
```
,
```
key
```
) to see if the query can already use an existing index.
explain
— Run in
```
"queryPlanner"
```
mode first to check for COLLSCAN. If the query uses an index or the collection is very small, run again with
```
"executionStats"
```
(10-second timeout) to get docs scanned vs. returned.

典型流程：调用

collection-indexes

→

explain

→

find

（获取样本文档）。

collection-indexes
— 用返回结果中的
```
classicIndexes
```
（每个包含
```
name
```
、
```
key
```
）判断查询是否已经可以使用现有索引。
explain
— 首先以
```
"queryPlanner"
```
模式运行，检查是否有COLLSCAN（全表扫描）。如果查询使用了索引或者集合非常小，再以
```
"executionStats"
```
模式运行（超时时间10秒），获取扫描的文档数和返回的文档数对比。

2. Atlas API access works for MongoDB MCP

2. MongoDB MCP的Atlas API访问可用

If you need a project ID, call

atlas-list-projects

first. Then call

atlas-get-performance-advisor

with only the

operations

you need:

Operation value	Use when
`slowQueryLogs`	Fetching slow queries—prioritize by slowest and most frequent. Optional: `namespaces` to scope to a collection; `since` for a time window.
`suggestedIndexes`	Fetching cluster index recommendations
`dropIndexSuggestions`	User asks what to remove or reduce index overhead
`schemaSuggestions`	User asks for schema/query-structure advice alongside indexes

Do not pass the MCP tool name as an

operations

value—

operations

is a separate argument listing what data to fetch.

如果你需要项目ID，先调用

atlas-list-projects

。然后调用

atlas-get-performance-advisor

，仅传入你需要的

operations

：

操作值	使用场景
`slowQueryLogs`	拉取慢查询 — 按最慢和出现频率最高排序优先处理。可选参数： `namespaces` 限定查询范围到某个集合； `since` 指定时间窗口。
`suggestedIndexes`	拉取集群索引建议
`dropIndexSuggestions`	用户询问要删除哪些索引或者降低索引开销时
`schemaSuggestions`	用户除了索引之外还需要 schema/查询结构建议时

不要将MCP工具名作为

operations

的值传递 —

operations

是单独的参数，用于指定要拉取的数据。

Example workflow 1 (help with specific query)

示例流程1（特定查询帮助）

User: "Why is this query slow?

db.orders.find({status: 'shipped', region: 'US'}).sort({date: -1})

If MCP db connection is configured and the database + collection names are known, run steps 1–3. Otherwise skip to step 4.

Check existing collection indexes:
- Call
```
collection-indexes
```
  with database=
```
store
```
  , collection=
```
orders
```
- Result shows:
```
{_id: 1}
```
  ,
```
{status: 1}
```
  ,
```
{date: -1}
```

Run explain:

Call

explain

with method=

find

, filter=

{status: 'shipped', region: 'US'}

, sort=

{date: -1}

, verbosity=

queryPlanner

and

executionStats

Result: Uses

{status: 1}

index, then in-memory SORT,

totalKeysExamined: 50000

nReturned: 100

Run find:
- Call
```
find
```
  with limit=1 to fetch a sample document to impute the schema.

If MCP Atlas connection is configured, run step 4. Otherwise skip to step 5.

Run atlas-get-performance-advisor:
- Try to get the cluster name from the MCP connection string, or ask the user for projectId/clusterName
- Use slowQueryLogs to fetch slow query logs from database=
```
store
```
  , collection=
```
orders
```
  in the past 24 hours
- Use suggestedIndexes to check for index suggestions for the query
Diagnose: Based on explain output and slow query logs, this query targets 100 docs but scans 50K index entries (poor selectivity: 0.002). In-memory sort adds overhead. Index doesn't support both filter fields or sort.
Recommend: Create compound index
```
{status: 1, region: 1, date: -1}
```
following ESR (two equality fields, then sort). This eliminates in-memory sort and improves selectivity by filtering on both status and region.

If the MongoDB MCP server is not set up, follow best indexing practices.

用户： "为什么这个查询这么慢？

db.orders.find({status: 'shipped', region: 'US'}).sort({date: -1})

如果已经配置MCP数据库连接，且已知数据库和集合名称，执行步骤1-3，否则跳转到步骤4。

检查集合现有索引：
- 调用
```
collection-indexes
```
  ，参数database=
```
store
```
  、collection=
```
orders
```
- 返回结果：
```
{_id: 1}
```
  、
```
{status: 1}
```
  、
```
{date: -1}
```
运行explain：
- 调用
```
explain
```
  ，参数method=
```
find
```
  、filter=
```
{status: 'shipped', region: 'US'}
```
  、sort=
```
{date: -1}
```
  、verbosity=
```
queryPlanner
```
  和
```
executionStats
```
- 返回结果：使用了
```
{status: 1}
```
  索引，之后进行内存排序，
```
totalKeysExamined: 50000
```
  、
```
nReturned: 100
```
运行find：
- 调用
```
find
```
  ，limit=1拉取样本文档以推断schema。

如果已经配置MCP Atlas连接，执行步骤4，否则跳转到步骤5。

运行atlas-get-performance-advisor：
- 尝试从MCP连接字符串获取集群名称，或者询问用户获取projectId/clusterName
- 使用slowQueryLogs拉取过去24小时内database=
```
store
```
  、collection=
```
orders
```
  的慢查询日志
- 使用suggestedIndexes查看该查询的索引建议
诊断： 基于explain输出和慢查询日志，该查询目标返回100条文档，但扫描了5万条索引条目（选择性差：0.002）。内存排序增加了开销。索引无法同时支持过滤字段和排序。
建议： 遵循ESR规则（两个等值字段在前，排序在后）创建复合索引
```
{status: 1, region: 1, date: -1}
```
。这可以消除内存排序，同时通过同时过滤status和region提升索引选择性。

如果没有配置MongoDB MCP服务，请遵循最佳索引实践给出建议。

Example workflow 2 (general database performance help)

示例流程2（通用数据库性能帮助）

User: "Can you help with optimizing slow queries on my cluster?”

Run atlas-get-performance-advisor:
- Try to get the cluster name from the connection string and deduce the project name you need in atlas-list-projects; if you are not sure, then ask the user for cluster name and project id.
- Use slowQueryLogs to fetch slow query logs from the past 24 hours
- Use suggestedIndexes
- Use dropIndexSuggestions
- Use schemaSuggestions
Diagnose and Recommend: Based on slow query logs and performance advisor advice, you can create the compound index
```
{status: 1, region: 1, date: -1}
```
on the
```
db.orders
```
collection to optimize queries such as
```
find({status: 'shipped', region: 'US'}).sort({date: -1})
```

Examine all performance advisor output as well as slow query logs. Provide information on what is being improved and why, and focus on suggestions that have the potential for greatest impact (e.g., indexes that affect the most queries, or queries that have the worst performance).

用户： "Can you help with optimizing slow queries on my cluster?”

运行atlas-get-performance-advisor：
- 尝试从连接字符串获取集群名称，通过atlas-list-projects推断所需的项目名称；如果不确定，询问用户获取集群名称和项目ID。
- 使用slowQueryLogs拉取过去24小时的慢查询日志
- 使用suggestedIndexes
- 使用dropIndexSuggestions
- 使用schemaSuggestions
诊断和建议： 基于慢查询日志和性能顾问建议，你可以在
```
db.orders
```
集合上创建复合索引
```
{status: 1, region: 1, date: -1}
```
来优化类似
```
find({status: 'shipped', region: 'US'}).sort({date: -1})
```
的查询。

检查所有性能顾问输出和慢查询日志。说明优化的内容和原因，优先给出潜在影响最大的建议（例如：影响最多查询的索引，或者性能最差的查询的优化方案）。

Load references

加载参考文件

Before beginning diagnosis and recommendation, load reference files.

Always load:

```
references/core-indexing-principles.md
```
```
references/antipattern-examples.md
```

Conditionally load these files:

If diagnosing aggregation pipelines →
```
references/aggregation-optimization.md
```
If diagnosing queries that change docs such as replaceOne, findOneAndUpdate, etc. →
```
references/update-query-examples.md
```
for oplog-efficient updates and common update anti-patterns

在开始诊断和给出建议之前，加载参考文件。

始终加载：

```
references/core-indexing-principles.md
```
```
references/antipattern-examples.md
```

按需加载以下文件：

如果诊断聚合管道 →
```
references/aggregation-optimization.md
```
如果诊断会修改文档的查询，例如replaceOne、findOneAndUpdate等 →
```
references/update-query-examples.md
```
，了解oplog高效更新和常见更新反模式

Output

输出

Keep answers short and clear: a few sentences on index and optimization suggestions, and reasoning behind them (e.g. general indexing principles, observing slow query logs in the cluster, or seeing advice in Performance Advisor)
Focus on highest impact indexes or optimizations - if you've omitted some optimizations let the user know and present them if asked.
Do not use strong language, such as saying “You should create these indexes and they will definitely improve application performance” - Explain they are suggestions for certain queries, and give the reasoning behind them.
Consider how many indexes already exist on the collection (if known) - there shouldn’t generally be more than 20
Suggest removing indexes only if the suggestion comes from Atlas Performance Advisor
Do not create indexes directly via MCP unless the user gives approval

回答简洁清晰：用几句话说明索引和优化建议，以及背后的原因（例如：通用索引原则、观察到的集群慢查询日志、性能顾问给出的建议）
优先给出影响最大的索引或优化方案 — 如果你省略了部分优化方案，请告知用户，用户询问时再提供
不要使用绝对化表述，例如“你应该创建这些索引，它们一定会提升应用性能” — 说明这些是针对特定查询的建议，并给出背后的原因
考虑集合上已有的索引数量（如果已知） — 一般来说索引数量不应超过20个
仅当Atlas性能顾问给出建议时，才推荐删除索引
除非用户批准，否则不要通过MCP直接创建索引