Каталог и индексы MongoDB
mongodb116-index-compound-screen
Это строки реального запуска на синтетических документах. Своя коллекция после проверки удалена.
Наблюдения этого запуска
Планы и время ниже — диагностика конкретного стенда, не норматив скорости. Случайные имена учебных объектов заменены подписями.
Создание индекса
{
"numIndexesBefore": 1,
"numIndexesAfter": 2,
"createdCollectionAutomatically": false,
"ok": 1
}Обычный выбор оптимизатора
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "<учебная база>.<учебная коллекция>",
"parsedQuery": {
"$and": [
{
"status": {
"$eq": "active"
}
},
{
"tenant": {
"$eq": "A"
}
},
{
"price": {
"$gte": 200
}
}
]
},
"indexFilterSet": false,
"queryHash": "894FA80F",
"planCacheShapeHash": "894FA80F",
"planCacheKey": "40CD4244",
"optimizationTimeMillis": 0,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"stage": "LIMIT",
"limitAmount": 4,
"inputStage": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"_id": 1,
"title": 1,
"price": 1
},
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"tenant": 1,
"status": 1,
"price": 1,
"_id": 1
},
"indexName": "catalog_screen",
"isMultiKey": false,
"multiKeyPaths": {
"tenant": [],
"status": [],
"price": [],
"_id": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"tenant": [
"[\"A\", \"A\"]"
],
"status": [
"[\"active\", \"active\"]"
],
"price": [
"[200, inf.0]"
],
"_id": [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 4,
"executionTimeMillis": 0,
"totalKeysExamined": 4,
"totalDocsExamined": 4,
"executionStages": {
"isCached": false,
"stage": "LIMIT",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 5,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"limitAmount": 4,
"inputStage": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"transformBy": {
"_id": 1,
"title": 1,
"price": 1
},
"inputStage": {
"stage": "FETCH",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"docsExamined": 4,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"keyPattern": {
"tenant": 1,
"status": 1,
"price": 1,
"_id": 1
},
"indexName": "catalog_screen",
"isMultiKey": false,
"multiKeyPaths": {
"tenant": [],
"status": [],
"price": [],
"_id": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"tenant": [
"[\"A\", \"A\"]"
],
"status": [
"[\"active\", \"active\"]"
],
"price": [
"[200, inf.0]"
],
"_id": [
"[MinKey, MaxKey]"
]
},
"keysExamined": 4,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
}
},
"queryShapeHash": "E0E9082FA78EE5920ADC5080ED8D3832E277C4532A9ADF353B6285B6E6827F70",
"command": {
"find": "<учебная коллекция>",
"filter": {
"tenant": "A",
"status": "active",
"price": {
"$gte": 200
}
},
"projection": {
"_id": 1,
"title": 1,
"price": 1
},
"sort": {
"price": 1,
"_id": 1
},
"limit": 4,
"maxTimeMS": 1000,
"$db": "<учебная база>"
},
"ok": 1
}Проверка заданного индекса через hint
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "<учебная база>.<учебная коллекция>",
"parsedQuery": {
"$and": [
{
"status": {
"$eq": "active"
}
},
{
"tenant": {
"$eq": "A"
}
},
{
"price": {
"$gte": 200
}
}
]
},
"indexFilterSet": false,
"queryHash": "894FA80F",
"planCacheShapeHash": "894FA80F",
"planCacheKey": "40CD4244",
"optimizationTimeMillis": 0,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"stage": "LIMIT",
"limitAmount": 4,
"inputStage": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"_id": 1,
"title": 1,
"price": 1
},
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"tenant": 1,
"status": 1,
"price": 1,
"_id": 1
},
"indexName": "catalog_screen",
"isMultiKey": false,
"multiKeyPaths": {
"tenant": [],
"status": [],
"price": [],
"_id": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"tenant": [
"[\"A\", \"A\"]"
],
"status": [
"[\"active\", \"active\"]"
],
"price": [
"[200, inf.0]"
],
"_id": [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 4,
"executionTimeMillis": 0,
"totalKeysExamined": 4,
"totalDocsExamined": 4,
"executionStages": {
"isCached": false,
"stage": "LIMIT",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 5,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"limitAmount": 4,
"inputStage": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"transformBy": {
"_id": 1,
"title": 1,
"price": 1
},
"inputStage": {
"stage": "FETCH",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"docsExamined": 4,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 4,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 4,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"keyPattern": {
"tenant": 1,
"status": 1,
"price": 1,
"_id": 1
},
"indexName": "catalog_screen",
"isMultiKey": false,
"multiKeyPaths": {
"tenant": [],
"status": [],
"price": [],
"_id": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"tenant": [
"[\"A\", \"A\"]"
],
"status": [
"[\"active\", \"active\"]"
],
"price": [
"[200, inf.0]"
],
"_id": [
"[MinKey, MaxKey]"
]
},
"keysExamined": 4,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
}
},
"queryShapeHash": "E0E9082FA78EE5920ADC5080ED8D3832E277C4532A9ADF353B6285B6E6827F70",
"command": {
"find": "<учебная коллекция>",
"filter": {
"tenant": "A",
"status": "active",
"price": {
"$gte": 200
}
},
"projection": {
"_id": 1,
"title": 1,
"price": 1
},
"sort": {
"price": 1,
"_id": 1
},
"limit": 4,
"maxTimeMS": 1000,
"hint": "catalog_screen",
"$db": "<учебная база>"
},
"ok": 1
}Что измерено
{
"fixture_documents": 120,
"performance_claim": false,
"screen_limit": 4
}PHP 8.5.11; mongodb 2.5.5; MongoDB 8.0.34.