1. 慢查与表详情
1.1 慢查日志
1）查询中命中了表 orderPrinter.order_153551 的索引
IXSCAN { deptCode: 1, createTime: 1, isCPrinted: 1 }
mongodb日志分析：{ "replanReason": "cached plan was less efficient than expected: expected trial execution to take 57 works but it took at least 570 works", "op": "query", "ns": "orderPrinter.order_153551", "planSummary": "IXSCAN { deptCode: 1, createTime: 1, isCPrinted: 1 }", "nreturned": 0, "responseLength": 239, "storage": { }, "locks": { "FeatureCompatibilityVersion": { "acquireCount": { "r": { "$numberLong": "19" } } }, "Database": { "acquireCount": { "r": { "$numberLong": "18" } } }, "Collection": { "acquireCount": { "r": { "$numberLong": "18" } } }, "Mutex": { "acquireCount": { "r": { "$numberLong": "1" } } }, "ReplicationStateTransition": { "acquireCount": { "w": { "$numberLong": "19" } } }, "Global": { "acquireCount": { "r": { "$numberLong": "19" } } } }, "flowControl": { }, "command": { "filter": { "isCPrinted": 0, "createTime": { "$gte": { "$date": "2025-07-22T08:20:00.195+08:00" } }, "stallNumber": null, "dealDeliveryType": { "$ne": -1 }, "delStatus": null, "requiredDeliveryTime": { "$lte": { "$date": "2025-08-05T23:59:59.999+08:00" } }, "deptCode": "10019" }, "lsid": { "id": { "$binary": "oVA3fU/pTmqOO1mkuIvseg==", "$type": "04" } }, "$db": "orderPrinter", "$clusterTime": { "clusterTime": { "$timestamp": { "t": 1754353200, "i": 37 } }, "signature": { "keyId": { "$numberLong": "7485993259508957185" }, "hash": { "$binary": "DQXPPvYqOy7Bg5X3ZXCQt4chDJU=", "$type": "00" } } }, "find": "order_153551", "limit": 10, "sort": { "createTime": -1 } }, "replanned": true, "queryHash": "FE251C8C", "protocol": "op_msg", "keysExamined": 4001, "planCacheKey": "79D2F979", "numYield": 17, "replRole": { "stateStr": "PRIMARY", "_id": 2 }, "docsExamined": 0, "cursorExhausted": true, "millis": 151, "fromMultiPlanner": true }
1.2 表数据量与索引
mgset-67375689:PRIMARY> db.order_153551.count() 356052 mgset-67375689:PRIMARY> db.order_153551.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }, { "v" : 2, "unique" : true, "key" : { "orderNum" : 1 }, "name" : "orderNum_1" }, { "v" : 2, "key" : { "deptCode" : 1 }, "name" : "deptCode_1" }, { "v" : 2, "key" : { "deptCode" : 1, "createTime" : 1, "isCPrinted" : 1 }, "name" : "deptCode_1_createTime_1_isCPrinted_1" }, { "v" : 2, "key" : { "deptCode" : 1, "createTime" : 1, "requiredDeliveryTime" : 1, "isCPrinted" : 1, "dealDeliveryType" : 1 }, "name" : "idx_deptCode_createTime_requiredDeliveryTime", "background" : true }, { "v" : 2, "key" : { "createTime" : 1 }, "name" : "createTime_1", "background" : true }, { "v" : 2, "key" : { "isCPrinted" : 1, "createTime" : -1, "deptCode" : 1 }, "name" : "isCPrinted_1_createTime_-1_deptCode_1", "background" : true } ] mgset-67375689:PRIMARY>
2. 慢SQL查询计划分析
3. 计算表的字符数据唯一性
3.1 两种计算方法
1）javascrite方式
算出字段唯一值，除以总数据量获得百分比
var isCPrinted=db.order_153551.distinct("isCPrinted") var uniqueCount =isCPrinted.length print("字段唯一值数量：", uniqueCount);
2）聚合方式
算出字段唯一值，除以总数据量获得百分比
db.order_153551.aggregate([ { $group: { _id: "$deptCode" } }, { $count: "deptCode 唯一值数量" } ])
4. 计算个字段数据量
4.1 计算
4.2 分析计算结果
从计算情况分析可知，此慢查数据分布不均衡，因此索引字段顺序可以按照 "isCPrinted":1,"createTime":-1,"deptCode":1 来创建
db.order_153551.createIndex({"isCPrinted":1,"createTime":-1,"deptCode":1},{"background" : true})
5. 新执行计划分析
5.1 新执行计划分析
1）执行计划走了新建的索引：
isCPrinted_1_createTime_-1_deptCode_1
2）"executionStats" 详细列出了新执行计划
"executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 3, "totalKeysExamined" : 228, "totalDocsExamined" : 0, "executionStages" : { "stage" : "FETCH", "filter" : { "$and" : [ { "dealDeliveryType" : { "$not" : { "$eq" : -1 } } }, { "delStatus" : { "$eq" : null } }, { "requiredDeliveryTime" : { "$lte" : ISODate("2025-08-05T15:59:59.999Z") } }, { "stallNumber" : { "$eq" : null } } ] }, "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 229, "advanced" : 0, "needTime" : 227, "needYield" : 0, "saveState" : 1, "restoreState" : 1, "isEOF" : 1, "docsExamined" : 0, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 228, "advanced" : 0, "needTime" : 227, "needYield" : 0, "saveState" : 1, "restoreState" : 1, "isEOF" : 1, "keyPattern" : { "isCPrinted" : 1, "createTime" : -1, "deptCode" : 1 }, "indexName" : "isCPrinted_1_createTime_-1_deptCode_1", "isMultiKey" : false, "multiKeyPaths" : { "isCPrinted" : [ ], "createTime" : [ ], "deptCode" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "isCPrinted" : [ "[0.0, 0.0]" ], "createTime" : [ "[new Date(9223372036854775807), new Date(1753143600195)]" ], "deptCode" : [ "[\"10019\", \"10019\"]" ] }, "keysExamined" : 228, "seeks" : 228, "dupsTested" : 0, "dupsDropped" : 0
5.2 新旧执行计划对比
6. 强制走索引hint 提示
6.1 语法
// 方式1：通过索引名称指定 db.集合名.find(查询条件).sort(排序条件).hint("索引名称"); // 方式2：通过索引键模式指定（与创建索引时的键模式一致） db.集合名.find(查询条件).sort(排序条件).hint({ 索引键模式 });
6.2 例句
db.order_153551.explain("executionStats").find({
isCPrinted: 0,
createTime: {
$gte: ISODate("2025-07-22T08:20:00.195+08:00")
},
stallNumber: null,
dealDeliveryType: {
$ne: -1
},
delStatus: null,
requiredDeliveryTime: {
$lte: ISODate("2025-08-05T23:59:59.999+08:00")
},
deptCode: "10019"
}).sort({
createTime: -1
}).hint("idx_deptCode_createTime_requiredDeliveryTime");
posted on
浙公网安备 33010602011771号