mongodb-query-and-index-optimize

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MongoDB Query and Index Optimization

MongoDB 查询与索引优化

Query patterns and indexing strategies for MongoDB, maintained by MongoDB. Contains 46 rules across 5 categories, prioritized by impact. Includes MongoDB 8.0 features:

bulkWrite

command,

$queryStats

, Query Settings, and

updateOne

sort option. Indexes are the primary tool for query performance—most slow queries are missing an appropriate index.

由MongoDB官方维护的MongoDB查询模式与索引策略指南。包含5个类别共46条规则，按影响优先级排序。涵盖MongoDB 8.0新特性：

bulkWrite

命令、

$queryStats

、Query Settings（查询设置）以及

updateOne

排序选项。索引是提升查询性能的核心工具——大多数慢查询问题都是因为缺少合适的索引。

When to Apply

适用场景

Reference these guidelines when:

Writing new MongoDB queries or aggregations
Creating or reviewing indexes for collections
Debugging slow queries (COLLSCAN, high execution time)
Reviewing explain() output
Seeing Performance Advisor suggestions
Optimizing aggregation pipelines
Implementing full-text search
Adding geospatial queries
Setting up TTL (time-to-live) for data expiration
Analyzing index usage with $indexStats
Profiling slow operations

在以下场景中参考本指南：

编写新的MongoDB查询或聚合语句
为集合创建或审核索引
调试慢查询（如COLLSCAN、执行时间过长等问题）
分析explain()输出结果
查看Performance Advisor（性能顾问）的建议
优化聚合管道
实现全文搜索
添加地理空间查询
为数据过期设置TTL（生存时间）
使用$indexStats分析索引使用情况
对慢操作进行性能分析

Rule Categories by Priority

按优先级划分的规则类别


index-
index-
query-
agg-
perf-

Priority	Category	Impact	Prefix	Rules
1	Index Essentials	CRITICAL	`index-`	9
2	Specialized Indexes	HIGH	`index-`	11
3	Query Patterns	HIGH	`query-`	10
4	Aggregation Optimization	HIGH	`agg-`	8
5	Performance Diagnostics	MEDIUM	`perf-`	8


index-
index-
query-
agg-
perf-

优先级	类别	影响程度	前缀	规则数量
1	索引基础	关键	`index-`	9
2	专用索引	高	`index-`	11
3	查询模式	高	`query-`	10
4	聚合优化	高	`agg-`	8
5	性能诊断	中	`perf-`	8

Quick Reference

快速参考

1. Index Essentials (CRITICAL) - 9 rules

1. 索引基础（关键）- 9条规则

```
index-compound-field-order
```
- Equality first, sort second, range last (ESR rule)
```
index-compound-multi-field
```
- Use compound indexes for multi-field queries
```
index-ensure-usage
```
- Avoid COLLSCAN, verify with explain()
```
index-remove-unused
```
- Audit indexes with $indexStats
```
index-high-cardinality-first
```
- Put selective fields at index start
```
index-covered-queries
```
- Include projected fields to avoid document fetch
```
index-prefix-principle
```
- Compound indexes serve prefix queries
```
index-creation-background
```
- Build indexes without blocking operations
```
index-size-considerations
```
- Keep indexes in RAM for optimal performance

```
index-compound-field-order
```
- 先等值条件，再排序，最后范围查询（ESR规则）
```
index-compound-multi-field
```
- 为多字段查询使用复合索引
```
index-ensure-usage
```
- 避免COLLSCAN，使用explain()验证
```
index-remove-unused
```
- 使用$indexStats审计未使用的索引
```
index-high-cardinality-first
```
- 将高选择性字段放在索引开头
```
index-covered-queries
```
- 包含投影字段以避免文档读取
```
index-prefix-principle
```
- 复合索引支持前缀查询
```
index-creation-background
```
- 以后台方式创建索引，避免阻塞业务操作
```
index-size-considerations
```
- 将索引保留在内存中以获得最佳性能

2. Specialized Indexes (HIGH) - 11 rules

2. 专用索引（高）- 11条规则

```
index-unique
```
- Enforce uniqueness for identifiers and constraints
```
index-partial
```
- Index subset of documents to reduce size
```
index-sparse
```
- Skip documents missing the indexed field
```
index-ttl
```
- Automatic document expiration for sessions/logs
```
index-text-search
```
- Full-text search with stemming and relevance
```
index-wildcard
```
- Dynamic field indexing for polymorphic schemas
```
index-multikey
```
- Array field indexing (one entry per element)
```
index-geospatial
```
- 2dsphere indexes for location queries
```
index-hashed
```
- Uniform distribution for equality lookups or shard keys
```
index-clustered
```
- Ordered storage with clustered collections
```
index-hidden
```
- Safely test index removals in production

```
index-unique
```
- 为标识符和约束条件创建唯一索引
```
index-partial
```
- 仅为文档子集创建索引以减小索引大小
```
index-sparse
```
- 跳过缺少索引字段的文档
```
index-ttl
```
- 为会话/日志等数据设置自动过期
```
index-text-search
```
- 使用支持词干提取和相关性排序的全文搜索索引
```
index-wildcard
```
- 为多态模式的动态字段创建通配符索引
```
index-multikey
```
- 为数组字段创建多键索引（每个元素对应一个索引条目）
```
index-geospatial
```
- 为位置查询使用2dsphere索引
```
index-hashed
```
- 为等值查询或分片键使用哈希索引实现均匀分布
```
index-clustered
```
- 使用聚集集合实现有序存储
```
index-hidden
```
- 在生产环境中安全测试索引删除操作

3. Query Patterns (HIGH) - 10 rules

3. 查询模式（高）- 10条规则

```
query-use-projection
```
- Fetch only needed fields
```
query-avoid-ne-nin
```
- Use $in instead of negation operators
```
query-or-index
```
- All $or clauses must have indexes for index usage
```
query-anchored-regex
```
- Start regex with ^ for index usage
```
query-batch-operations
```
- Avoid N+1 patterns, use $in or $lookup
```
query-pagination
```
- Use range-based pagination, not skip
```
query-exists-with-sparse
```
- Understand $exists behavior with sparse indexes
```
query-sort-collation
```
- Match sort order and collation to indexes
```
query-bulkwrite-command
```
- MongoDB 8.0 cross-collection atomic batch operations
```
query-updateone-sort
```
- MongoDB 8.0 deterministic updates with sort option

```
query-use-projection
```
- 仅获取所需字段
```
query-avoid-ne-nin
```
- 使用$in替代否定运算符
```
query-or-index
```
- 所有$or子句都必须有对应索引才能利用索引查询
```
query-anchored-regex
```
- 正则表达式以^开头才能使用索引
```
query-batch-operations
```
- 避免N+1查询模式，使用$in或$lookup
```
query-pagination
```
- 使用基于范围的分页，而非skip
```
query-exists-with-sparse
```
- 了解$exists与稀疏索引的交互行为
```
query-sort-collation
```
- 确保排序顺序和排序规则与索引匹配
```
query-bulkwrite-command
```
- MongoDB 8.0跨集合原子批量操作
```
query-updateone-sort
```
- MongoDB 8.0通过排序选项实现确定性更新

4. Aggregation Optimization (HIGH) - 8 rules

4. 聚合优化（高）- 8条规则

```
agg-match-early
```
- Filter with $match at pipeline start
```
agg-project-early
```
- Reduce document size with $project
```
agg-sort-limit
```
- Combine $sort with $limit for top-N
```
agg-lookup-index
```
- Ensure $lookup foreign field is indexed
```
agg-graphlookup
```
- Use $graphLookup for recursive graph traversal
```
agg-avoid-large-unwind
```
- Don't $unwind massive arrays
```
agg-allowdiskuse
```
- Handle large aggregations exceeding 100MB
```
agg-group-memory-limit
```
- Control $group memory and spills

```
agg-match-early
```
- 在聚合管道开头使用$match过滤数据
```
agg-project-early
```
- 使用$project减小文档大小
```
agg-sort-limit
```
- 将$sort与$limit结合实现Top-N查询
```
agg-lookup-index
```
- 确保$lookup关联的外部字段有索引
```
agg-graphlookup
```
- 使用$graphLookup实现递归图遍历
```
agg-avoid-large-unwind
```
- 不要对超大数组执行$unwind操作
```
agg-allowdiskuse
```
- 处理超过100MB的大型聚合时启用allowDiskUse
```
agg-group-memory-limit
```
- 控制$group阶段的内存使用和磁盘溢出

5. Performance Diagnostics (MEDIUM) - 8 rules

5. 性能诊断（中）- 8条规则

```
perf-explain-interpretation
```
- Read explain() output like a pro
```
perf-slow-query-log
```
- Use profiler to find slow operations
```
perf-index-stats
```
- Find unused indexes with $indexStats
```
perf-query-plan-cache
```
- Understand and manage query plan cache
```
perf-use-hint
```
- Force a known-good index when the optimizer errs
```
perf-atlas-performance-advisor
```
- Use Atlas suggestions for missing indexes
```
perf-query-stats
```
- MongoDB 8.0 workload-based query analysis with $queryStats
```
perf-query-settings
```
- MongoDB 8.0 persistent index hints with setQuerySettings

```
perf-explain-interpretation
```
- 专业解读explain()输出结果
```
perf-slow-query-log
```
- 使用性能分析器定位慢操作
```
perf-index-stats
```
- 使用$indexStats找出未使用的索引
```
perf-query-plan-cache
```
- 理解并管理查询计划缓存
```
perf-use-hint
```
- 当优化器选择错误时，强制使用已知高效的索引
```
perf-atlas-performance-advisor
```
- 使用Atlas性能顾问的建议添加缺失索引
```
perf-query-stats
```
- MongoDB 8.0基于工作负载的查询分析工具$queryStats
```
perf-query-settings
```
- MongoDB 8.0使用setQuerySettings设置持久化索引提示

Key Principle

核心原则

"If there's no index, it's a collection scan."

Every query without a supporting index scans the entire collection. A 10ms query on 10,000 documents becomes a 10-second query on 10 million documents.

“没有索引，就会执行全集合扫描。”

任何没有索引支持的查询都会扫描整个集合。在10,000条数据上耗时10ms的查询，在1000万条数据上会变成耗时10秒的查询。

ESR Rule (Equality-Sort-Range)

ESR规则（等值-排序-范围）

The most important rule for compound index field order:

javascript

// Query: status = "active" AND createdAt > lastWeek ORDER BY priority
// ESR: Equality (status) → Sort (priority) → Range (createdAt)
db.tasks.createIndex({ status: 1, priority: 1, createdAt: 1 })

Position	Type	Example	Why
First	Equality	`status: "active"`	Narrows to exact matches
Second	Sort	`ORDER BY priority`	Avoids in-memory sort
Third	Range	`createdAt > date`	Scans within sorted data

复合索引字段顺序的最重要规则：

javascript

// 查询：status = "active" AND createdAt > lastWeek ORDER BY priority
// ESR：等值条件（status）→ 排序（priority）→ 范围（createdAt）
db.tasks.createIndex({ status: 1, priority: 1, createdAt: 1 })

位置	类型	示例	原因
第一位	等值条件	`status: "active"`	快速缩小查询范围到精确匹配的文档
第二位	排序	`ORDER BY priority`	避免内存排序
第三位	范围	`createdAt > date`	在已排序的数据范围内扫描

How to Use

使用方法

Read individual rule files for detailed explanations and code examples:

rules/index-compound-field-order.md
rules/perf-explain-interpretation.md
rules/_sections.md

Each rule file contains:

Brief explanation of why it matters
Incorrect code example with explanation
Correct code example with explanation
"When NOT to use" exceptions
How to verify with explain()
Performance impact and metrics

阅读单个规则文件获取详细说明和代码示例：

rules/index-compound-field-order.md
rules/perf-explain-interpretation.md
rules/_sections.md

每个规则文件包含：

规则重要性的简要说明
错误代码示例及问题分析
正确代码示例及解释
“不适用场景”例外情况
如何使用explain()验证
性能影响及指标

How These Rules Work

规则工作机制

Recommendations with Verification

带验证的建议

Every rule in this skill provides:

A recommendation based on best practices
A verification checklist of things that should be confirmed
Commands to verify so you can check before implementing
MCP integration for automatic verification when connected

本指南中的每条规则都提供：

最佳实践建议
验证检查清单
验证命令，便于实施前检查
MCP集成，连接后可自动验证

Why Verification Matters

验证的重要性

I analyze code patterns, but I can't see your actual database without a connection. This means I might suggest:

Creating an index that already exists
Optimizing a query that's already using an efficient index
Adding a compound index when a prefix already covers the query

Always verify before implementing. Each rule includes verification commands.

我可以分析代码模式，但无法直接访问你的实际数据库。这意味着我可能会建议：

创建已存在的索引
优化已经在使用高效索引的查询
当已有前缀索引覆盖查询时，仍建议添加复合索引

实施前务必验证。每条规则都包含验证命令。

MongoDB MCP Integration

MongoDB MCP集成

For automatic verification, connect the MongoDB MCP Server:

Option 1: Connection String

json

{
  "mcpServers": {
    "mongodb": {
      "command": "npx",
      "args": ["-y", "mongodb-mcp-server", "--readOnly"],
      "env": {
        "MDB_MCP_CONNECTION_STRING": "mongodb+srv://user:pass@cluster.mongodb.net/mydb"
      }
    }
  }
}

Option 2: Local MongoDB

json

{
  "mcpServers": {
    "mongodb": {
      "command": "npx",
      "args": ["-y", "mongodb-mcp-server", "--readOnly"],
      "env": {
        "MDB_MCP_CONNECTION_STRING": "mongodb://localhost:27017/mydb"
      }
    }
  }
}

⚠️ Security: Use

--readOnly

for safety. Remove only if you need write operations.

When connected, I can automatically:

Check existing indexes via
```
mcp__mongodb__collection-indexes
```
Analyze query performance via
```
mcp__mongodb__explain
```
Verify data patterns via
```
mcp__mongodb__aggregate
```

如需自动验证，请连接MongoDB MCP Server：

选项1：连接字符串

json

{
  "mcpServers": {
    "mongodb": {
      "command": "npx",
      "args": ["-y", "mongodb-mcp-server", "--readOnly"],
      "env": {
        "MDB_MCP_CONNECTION_STRING": "mongodb+srv://user:pass@cluster.mongodb.net/mydb"
      }
    }
  }
}

选项2：本地MongoDB

json

{
  "mcpServers": {
    "mongodb": {
      "command": "npx",
      "args": ["-y", "mongodb-mcp-server", "--readOnly"],
      "env": {
        "MDB_MCP_CONNECTION_STRING": "mongodb://localhost:27017/mydb"
      }
    }
  }
}

⚠️ 安全提示：为安全起见，使用

--readOnly

模式。仅当需要写操作时才移除该参数。

连接后，我可以自动：

通过
```
mcp__mongodb__collection-indexes
```
检查现有索引
通过
```
mcp__mongodb__explain
```
分析查询性能
通过
```
mcp__mongodb__aggregate
```
验证数据模式

⚠️ Action Policy

⚠️ 操作策略

I will NEVER execute write operations without your explicit approval.

Operation Type	MCP Tools	Action
Read (Safe)	`find` , `aggregate` , `explain` , `collection-indexes` , `$indexStats`	I may run automatically to verify
Write (Requires Approval)	`create-index` , `drop-index` , `update-many` , `delete-many`	I will show the command and wait for your "yes"
Destructive (Requires Approval)	`drop-collection` , `drop-database`	I will warn you and require explicit confirmation

When I recommend creating an index or making changes:

I'll explain what I want to do and why
I'll show you the exact command
I'll wait for your approval before executing
If you say "go ahead" or "yes", only then will I run it

Your database, your decision. I'm here to advise, not to act unilaterally.

未经你明确批准，我绝不会执行任何写操作。

操作类型	MCP工具	操作方式
只读（安全）	`find` , `aggregate` , `explain` , `collection-indexes` , `$indexStats`	我可能会自动执行以验证信息
写操作（需要批准）	`create-index` , `drop-index` , `update-many` , `delete-many`	我会展示命令并等待你确认“同意”
破坏性操作（需要批准）	`drop-collection` , `drop-database`	我会发出警告并要求你明确确认

当我建议创建索引或进行修改时：

我会说明要做什么以及为什么要做
我会展示精确的命令
等待你的批准后才执行
只有当你回复“执行”或“同意”时，我才会运行该命令

你的数据库，你做主。我仅提供建议，不会擅自操作。

Working Together

协作方式

If you're not sure about a recommendation:

Run the verification commands I provide
Share the output with me
I'll adjust my recommendation based on your actual data

We're a team—let's get this right together.

如果你对某个建议有疑问：

运行我提供的验证命令
将输出结果分享给我
我会根据你的实际数据调整建议

我们是团队协作——一起把事情做对。

Full Compiled Document

完整编译文档

For the complete guide with all rules expanded:

AGENTS.md

如需查看所有规则的完整扩展指南，请参考：

AGENTS.md