mongodb-query-and-index-optimize by romiluz13/mongodb-agent-skills
npx skills add https://github.com/romiluz13/mongodb-agent-skills --skill mongodb-query-and-index-optimize由 MongoDB 维护的查询模式与索引策略。包含 5 大类共 46 条规则,按影响程度排序。涵盖 MongoDB 8.0 特性:bulkWrite 命令、$queryStats(在 MongoDB 6.0.7 中引入,并在 8.1/8.2 中增强)、查询设置以及 updateOne 排序选项。索引是提升查询性能的主要工具——大多数慢查询都缺少合适的索引。
在以下情况时参考本指南:
$text 搜索当请求涉及 Atlas Search、$search、$searchMeta、分析器、同义词、自动补全或对 Atlas 托管数据进行特定于搜索的相关性调优时,请使用 mongodb-search。
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
| 优先级 | 类别 | 影响 | 前缀 | 规则数量 |
|---|---|---|---|---|
| 1 | 索引基础 | 关键 | index- | 9 |
| 2 | 专用索引 | 高 | index- | 11 |
| 3 | 查询模式 | 高 | query- | 10 |
| 4 | 聚合优化 | 高 | agg- | 8 |
| 5 | 性能诊断 | 中 | perf- | 8 |
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 - 将索引保留在 RAM 中以获得最佳性能index-unique - 为标识符和约束强制执行唯一性index-partial - 索引文档子集以减少大小index-sparse - 跳过缺少索引字段的文档index-ttl - 为会话/日志自动设置文档过期index-text-search - 内置的 $text 搜索,支持词干提取和相关性排序index-wildcard - 为多态模式动态索引字段index-multikey - 数组字段索引(每个元素一个条目)index-geospatial - 用于位置查询的 2dsphere 索引index-hashed - 为等值查找或分片键提供均匀分布index-clustered - 使用聚集集合进行有序存储index-hidden - 在生产环境中安全地测试索引移除query-use-projection - 仅获取所需字段query-avoid-ne-nin - 使用 $in 代替否定运算符query-or-index - 所有 $or 子句都必须有索引才能使用索引query-anchored-regex - 正则表达式以 ^ 开头才能使用索引query-batch-operations - 避免 N+1 模式,使用 $in 或 $lookupquery-pagination - 使用基于范围的翻页,而非 skipquery-exists-with-sparse - 理解 $exists 与稀疏索引的行为query-sort-collation - 使排序顺序和排序规则与索引匹配query-bulkwrite-command - MongoDB 8.0 跨集合原子批量操作query-updateone-sort - MongoDB 8.0 通过排序选项实现确定性更新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 - 不要对大型数组使用 $unwindagg-allowdiskuse - 处理超过 100MB 的大型聚合agg-group-memory-limit - 控制 $group 的内存使用和溢出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 - 使用 $queryStats 进行基于工作负载的查询分析(在 MongoDB 6.0.7 中引入,并在 8.1/8.2 中增强)perf-query-settings - MongoDB 8.0 通过 setQuerySettings 实现持久化索引提示"如果没有索引,那就是全集合扫描。"
每个没有支持索引的查询都会扫描整个集合。一个在 10,000 个文档上耗时 10ms 的查询,在 1 千万个文档上会变成 10 秒的查询。
关于复合索引字段顺序的最重要规则:
// 查询: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 | 在已排序的数据中扫描 |
ERS 例外:当范围谓词具有高选择性时,将范围字段放在排序字段之前可以减少排序输入。请使用 explain() 验证。
阅读单独的规则文件以获取详细解释和代码示例:
rules/index-compound-field-order.md
rules/perf-explain-interpretation.md
rules/_sections.md
每个规则文件包含:
此技能中的每条规则都提供:
我可以分析代码模式,但在没有连接的情况下无法看到您的实际数据库。这意味着我可能会建议:
在实施前务必进行验证。 每条规则都包含验证命令。
要进行自动验证,请连接 MongoDB MCP Server:
选项 1:连接字符串
{
"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
{
"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 验证数据模式未经您明确批准,我绝不会执行写操作。
| 操作类型 | MCP 工具 | 操作 |
|---|---|---|
| 读取(安全) | find, aggregate, explain, collection-indexes, $indexStats | 我可能会自动运行以进行验证 |
| 写入(需要批准) | create-index, drop-index, update-many, delete-many | 我将显示命令并等待您的"是" |
| 破坏性操作(需要批准) | drop-collection, drop-database | 我将警告您并要求明确确认 |
当我建议创建索引或进行更改时:
您的数据库,您来决定。 我在这里提供建议,而不是单方面采取行动。
如果您对某个建议不确定:
我们是一个团队——让我们一起把它做好。
如需包含所有规则详情的完整指南,请查看:AGENTS.md
每周安装量
220
代码仓库
GitHub 星标数
17
首次出现
Jan 29, 2026
安全审计
已安装于
claude-code182
codex107
opencode101
cursor101
github-copilot101
gemini-cli100
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 (introduced in MongoDB 6.0.7, with 8.1/8.2 enhancements), Query Settings, and updateOne sort option. Indexes are the primary tool for query performance—most slow queries are missing an appropriate index.
Reference these guidelines when:
$text search with text indexesUse mongodb-search instead when the request is about Atlas Search, $search, $searchMeta, analyzers, synonyms, autocomplete, or search-specific relevance tuning on Atlas-hosted data.
| 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- |
index-compound-field-order - Equality first, sort second, range last (ESR rule)index-compound-multi-field - Use compound indexes for multi-field queriesindex-ensure-usage - Avoid COLLSCAN, verify with explain()index-remove-unused - Audit indexes with $indexStatsindex-high-cardinality-first - Put selective fields at index startindex-covered-queries - Include projected fields to avoid document fetchindex-prefix-principle - Compound indexes serve prefix queriesindex-creation-background - Build indexes without blocking operationsindex-size-considerations - Keep indexes in RAM for optimal performanceindex-unique - Enforce uniqueness for identifiers and constraintsindex-partial - Index subset of documents to reduce sizeindex-sparse - Skip documents missing the indexed fieldindex-ttl - Automatic document expiration for sessions/logsindex-text-search - Built-in $text search with stemming and relevanceindex-wildcard - Dynamic field indexing for polymorphic schemasindex-multikey - Array field indexing (one entry per element)index-geospatial - 2dsphere indexes for location queriesquery-use-projection - Fetch only needed fieldsquery-avoid-ne-nin - Use $in instead of negation operatorsquery-or-index - All $or clauses must have indexes for index usagequery-anchored-regex - Start regex with ^ for index usagequery-batch-operations - Avoid N+1 patterns, use $in or $lookupquery-pagination - Use range-based pagination, not skipquery-exists-with-sparse - Understand $exists behavior with sparse indexesquery-sort-collation - Match sort order and collation to indexesquery-bulkwrite-command - MongoDB 8.0 cross-collection atomic batch operationsagg-match-early - Filter with $match at pipeline startagg-project-early - Reduce document size with $projectagg-sort-limit - Combine $sort with $limit for top-Nagg-lookup-index - Ensure $lookup foreign field is indexedagg-graphlookup - Use $graphLookup for recursive graph traversalagg-avoid-large-unwind - Don't $unwind massive arraysagg-allowdiskuse - Handle large aggregations exceeding 100MBagg-group-memory-limit - Control $group memory and spillsperf-explain-interpretation - Read explain() output like a properf-slow-query-log - Use profiler to find slow operationsperf-index-stats - Find unused indexes with $indexStatsperf-query-plan-cache - Understand and manage query plan cacheperf-use-hint - Force a known-good index when the optimizer errsperf-atlas-performance-advisor - Use Atlas suggestions for missing indexesperf-query-stats - Workload-based query analysis with $queryStats (introduced in MongoDB 6.0.7, with 8.1/8.2 enhancements)perf-query-settings - MongoDB 8.0 persistent index hints with setQuerySettings"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.
The most important rule for compound index field order:
// 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 |
ERS Exception: When range predicate is highly selective, placing Range before Sort reduces sort input. Verify with explain().
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:
Every rule in this skill provides:
I analyze code patterns, but I can't see your actual database without a connection. This means I might suggest:
Always verify before implementing. Each rule includes verification commands.
For automatic verification, connect the MongoDB MCP Server:
Option 1: Connection String
{
"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
{
"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:
mcp__mongodb__collection-indexesmcp__mongodb__explainmcp__mongodb__aggregateI 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, |
When I recommend creating an index or making changes:
Your database, your decision. I'm here to advise, not to act unilaterally.
If you're not sure about a recommendation:
We're a team—let's get this right together.
For the complete guide with all rules expanded: AGENTS.md
Weekly Installs
220
Repository
GitHub Stars
17
First Seen
Jan 29, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
claude-code182
codex107
opencode101
cursor101
github-copilot101
gemini-cli100
Azure 升级评估与自动化工具 - 轻松迁移 Functions 计划、托管层级和 SKU
79,900 周安装
| 8 |
| 5 | Performance Diagnostics | MEDIUM | perf- | 8 |
index-hashed - Uniform distribution for equality lookups or shard keysindex-clustered - Ordered storage with clustered collectionsindex-hidden - Safely test index removals in productionquery-updateone-sort - MongoDB 8.0 deterministic updates with sort optiondelete-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 |