MongoDB 索引分析记录


MongoDB 索引分析记录

最近使用 MongoDB 比较多,这个篇文章会记录 MongoDB 索引方面多问题。

有无索引的区别

  • 记录数量
db.vip_accrued_log.count()
1783345
  • 有索引前

  • 添加索引
    `
    db.getCollection(“vip_accrued_log”).dropIndex(“order_id_1”)

db.getCollection(“vip_accrued_log”).createIndex({
“order_id”: “hashed”
}, {
name: “idx_order_id_hashed”,
background: true
})

db.vip_accrued_log.createIndex({‘order_id’: 1}, {‘name’:’idx_’, ‘background’: true})

{
    "v" : 2,
    "unique" : true,
    "key" : {
        "user_id" : 1,
        "order_id" : 1,
        "type" : 1
    },
    "name" : "unique_user_id_order_id_type",
    "ns" : "billing.vip_accrued_log",
    "background" : true
}


- explain 无索引

{
“queryPlanner”: {
“plannerVersion”: 1,
“namespace”: “billing.vip_accrued_log”,
“indexFilterSet”: false,
“parsedQuery”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“winningPlan”: {
“stage”: “COLLSCAN”,
“filter”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“direction”: “forward”
},
“rejectedPlans”: [ ]
},
“executionStats”: {
“executionSuccess”: true,
“nReturned”: 1,
“executionTimeMillis”: 2124,
“totalKeysExamined”: 0,
“totalDocsExamined”: 1783345,
“executionStages”: {
“stage”: “COLLSCAN”,
“filter”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“nReturned”: 1,
“executionTimeMillisEstimate”: 1850,
“works”: 1783347,
“advanced”: 1,
“needTime”: 1783345,
“needYield”: 0,
“saveState”: 13954,
“restoreState”: 13954,
“isEOF”: 1,
“invalidates”: 0,
“direction”: “forward”,
“docsExamined”: 1783345
},
“allPlansExecution”: [ ]
},
“ok”: 1,
“operationTime”: {
“$timestamp”: {
“t”: 1590548094,
“i”: 2
}
},
“$clusterTime”: {
“clusterTime”: {
“$timestamp”: {
“t”: 1590548094,
“i”: 2
}
},
“signature”: {
“hash”: {
“$binary”: {
“base64”: “fGC8LiBtU3bJF1h0lOnEBZdZnjE=”,
“subType”: “00”
}
},
“keyId”: {
“$numberLong”: “6788427056662183937”
}
}
}
}

- 加索引后

{
“queryPlanner”: {
“plannerVersion”: 1,
“namespace”: “billing.vip_accrued_log”,
“indexFilterSet”: false,
“parsedQuery”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“winningPlan”: {
“stage”: “FETCH”,
“filter”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“inputStage”: {
“stage”: “IXSCAN”,
“keyPattern”: {
“order_id”: “hashed”
},
“indexName”: “idx_order_id_hashed”,
“isMultiKey”: false,
“isUnique”: false,
“isSparse”: false,
“isPartial”: false,
“indexVersion”: 2,
“direction”: “forward”,
“indexBounds”: {
“order_id”: [
“[7132502507681949272, 7132502507681949272]”
]
}
}
},
“rejectedPlans”: [ ]
},
“executionStats”: {
“executionSuccess”: true,
“nReturned”: 1,
“executionTimeMillis”: 0,
“totalKeysExamined”: 1,
“totalDocsExamined”: 1,
“executionStages”: {
“stage”: “FETCH”,
“filter”: {
“order_id”: {
“$eq”: “5e8a28c1fcf1ee03d388d07b”
}
},
“nReturned”: 1,
“executionTimeMillisEstimate”: 0,
“works”: 2,
“advanced”: 1,
“needTime”: 0,
“needYield”: 0,
“saveState”: 0,
“restoreState”: 0,
“isEOF”: 1,
“invalidates”: 0,
“docsExamined”: 1,
“alreadyHasObj”: 0,
“inputStage”: {
“stage”: “IXSCAN”,
“nReturned”: 1,
“executionTimeMillisEstimate”: 0,
“works”: 2,
“advanced”: 1,
“needTime”: 0,
“needYield”: 0,
“saveState”: 0,
“restoreState”: 0,
“isEOF”: 1,
“invalidates”: 0,
“keyPattern”: {
“order_id”: “hashed”
},
“indexName”: “idx_order_id_hashed”,
“isMultiKey”: false,
“isUnique”: false,
“isSparse”: false,
“isPartial”: false,
“indexVersion”: 2,
“direction”: “forward”,
“indexBounds”: {
“order_id”: [
“[7132502507681949272, 7132502507681949272]”
]
},
“keysExamined”: 1,
“seeks”: 1,
“dupsTested”: 0,
“dupsDropped”: 0,
“seenInvalidated”: 0
}
},
“allPlansExecution”: [ ]
},
“ok”: 1,
“operationTime”: {
“$timestamp”: {
“t”: 1590548549,
“i”: 1
}
},
“$clusterTime”: {
“clusterTime”: {
“$timestamp”: {
“t”: 1590548549,
“i”: 1
}
},
“signature”: {
“hash”: {
“$binary”: {
“base64”: “TovgNbCAhIV0hRfmucotagfGB/I=”,
“subType”: “00”
}
},
“keyId”: {
“$numberLong”: “6788427056662183937”
}
}
}
}
`


Author: Kyle Liu
Reprint policy: All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source Kyle Liu !
  TOC