分析并优化MongoDB慢查询
结合执行计划定位性能瓶颈,提出索引与查询改写方案,说明写入成本及优化效果的验证指标。
完整提示词
<context>
The explain output tells you almost everything: COLLSCAN means no usable index; a large gap between totalDocsExamined and nReturned means the index is not selective for this filter; an in-memory SORT stage means the index does not provide the order, and on big results it spills or fails at the memory limit. Compound indexes work best in equality, sort, range order (ESR): equality fields first, then the sort keys, then range filters, because a range field placed before the sort key forces an in-memory sort. Covered queries (projection only of indexed fields, excluding _id if it is not indexed) skip fetching documents entirely. In aggregations, $match and $sort must come first to use an index; $lookup per document is the MongoDB N+1, and $unwind on large arrays multiplies documents. Some slowness is the schema: unbounded arrays, documents near the size limit, or data that is always read together but stored apart. Every new index costs write time, memory and disk.
</context>
<task>
Slow query: {{query}}
{{#explain}}
Explain or profiler output:
{{explain}}
{{/explain}}
{{#indexes}}
Indexes and collection facts:
{{indexes}}
{{/indexes}}
1. Read the winning plan: stage types, index used, keysExamined, docsExamined, nReturned, in-memory sort, execution time. If no explain output is given, give the exact explain command to run and reason from the query shape, marking conclusions as unverified.
2. Name the cause in one or two sentences: no index, wrong field order, unselective index, in-memory sort, fetching whole documents, early $lookup or $unwind, or a schema problem.
3. Propose the index in ESR order and explain the order field by field. Check whether an existing index can be extended instead of adding a new one, and flag existing indexes that become redundant (a prefix of the new one).
4. Rewrite the query or pipeline if it helps: move $match and $sort first, add a projection, use a covered query, replace skip-based paging with range paging on an indexed key, filter before $lookup and give the joined collection an index on the foreign field.
5. Note schema changes only when the query cannot be fixed otherwise (embedding, bucketing, a precomputed field), with their write-side cost.
6. Give the verification: the explain fields that should change and the expected ratio of keys examined to documents returned.
</task>
<constraints>
- Do not claim a speed-up you have not seen; state the explain numbers that would confirm it.
- State the write and memory cost of every index you propose; do not suggest indexing every field.
- Build large indexes in a way that does not block the application; say to check the server version's index build behaviour first.
- Keep query results and ordering identical unless the user agrees to change them.
{{> guardrails/investigate-before-answering}}
</constraints>
<output_format>
## Diagnosis
The key explain numbers and the cause.
## Index change
The createIndex command, the reason for the field order, and indexes made redundant.
## Query or pipeline rewrite
The new query or pipeline, or "No change needed".
## Schema notes
Only if relevant, otherwise "None".
## How to verify
The explain fields to check and their expected values.
</output_format>可替换内容
- {{query}}
- {{#explain}}
- {{explain}}
- {{/explain}}
- {{#indexes}}
- {{indexes}}
- {{/indexes}}
- {{> guardrails/investigate-before-answering}}
分类与标签
编程开发、MongoDB、慢查询优化、执行计划、复合索引、聚合管道