使用場景 / 基礎用途
針對于MongoDB中的慢查詢，我們通常會使用explain命令進行分析醋虏，其結(jié)果包含MongoDB實際執(zhí)行過程寻咒，但返回往往比較冗長，不夠直觀颈嚼。
最近發(fā)現(xiàn)一款小工具毛秘，能更加便捷快速查看explain分析結(jié)果，和大家分享下阻课。
先看效果叫挟，通過小工具，我們可以拿到如下結(jié)果限煞，
mongo> mongoTuning.executionStats(explainDoc);
1 COLLSCAN ( ms:10427 docs:411121)
2 SORT_KEY_GENERATOR ( ms:10427)
3 SORT ( ms:10427)
4 PROJECTION_SIMPLE ( ms:10428)
Totals: ms: 12016 keys: 0 Docs: 411121
使用方法
1) 下載小工具
https://github.com/gharriso/MongoDBPerformanceTuningBook 中 mongoTuning.js抹恳，
2) 使用Mongo Shell的時候，引入
mongo --shell mongoTuning.js
3) 使用示例
假設我們有一段查詢?nèi)缦拢?/p>
var explainCsr = db.customers.explain()
.find(
{
FirstName: "RUTH",
LastName: "MARTINEZ",
Phone: 496523103
},
{
Address: 1,
dob: 1
})
.sort({ dob: 1 });
a) 首先署驻，var explainDoc = explainCsr.next(); 看下原始結(jié)果 printjson(explainDoc.queryPlanner.winningPlan);, 如下奋献，
mongo> printjson(explainDoc.queryPlanner.winningPlan);
{
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"Address": 1,
"dob": 1
},
"inputStage": {
"stage": "SORT",
"sortPattern": {
"dob": 1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [ <
snip >
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"Phone": 1
},
"indexName": "Phone_1",
"isMultiKey": false,
"multiKeyPaths": {
"Phone": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"Phone": [
"[496523103.0, 496523103.0]"
]
}
}
}
}
}
}
b) 其次健霹，看下分析工具給出結(jié)果 mongoTuning.quickExplain(explainDoc), 如下
Mongo Shell>mongoTuning.quickExplain(explainDoc)
1 IXSCAN Phone_1
2 FETCH
3 SORT_KEY_GENERATOR
4 SORT
5 PROJECTION_SIMPLE
是不是更加的簡潔清晰。 這里 mongoTuning.quickExplain(explainDoc) 在實際的執(zhí)行邏輯中秽荞，其實默認去查explainDoc.queryPlanner.winningPlan
（mongoTuning.quickExplain(explainDoc) =mongoTuning.quickExplain(explainDoc.queryPlanner.winningPlan) ）骤公，
如果我們想看那些 rejectedPlan, 則可以 mongoTuning.quickExplain(explainDoc.queryPlanner.rejectedPlans[1]), 如下:
Mongo> mongoTuning.quickExplain (explainDoc.queryPlanner.rejectedPlans[1])
1 IXSCAN LastNmae_1
2 IXSCAN Phone_1
3 AND_SORTED
4 FETCH
5 SORT_KEY_GENERTOR
6 SORT
7 PROJECTION_SIMPLE
此外，如果想看一些每一步耗時 和 文檔數(shù)量, 可以使用explain('executionStats'),：
var explainObj = db.customers.explain('executionStats')
.find(
{
FirstName: "RUTH",
LastName: "MARTINEZ",
Phone: 496523103
},
{
Address: 1,
dob: 1
}
)
.sort(
{ dob: 1 }
);
原始結(jié)果如下,
mongo> explainDoc.executionStats
{
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 0,
"totalKeysExamined": 1,
"totalDocsExamined": 1,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 1,
"executionTimeMillisEstimate": 0,
"works": 6,
"advanced": 1,
"needTime": 3,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"transformBy": {
"Address": 1,
"inputStage": {
"stage": "SORT",
// Many, many more lines of output
}
}
}
}
帶入到工具中扬跋，
mongo> mongoTuning.executionStats(explainDoc);
1 COLLSCAN ( ms:10427 docs:411121)
2 SORT_KEY_GENERATOR ( ms:10427)
3 SORT ( ms:10427)
4 PROJECTION_SIMPLE ( ms:10428)
Totals: ms: 12016 keys: 0 Docs: 411121
簡單明了阶捆！
原理解析
使用介紹完成，如果感興趣實現(xiàn)钦听，我們看下分析工具是怎么運作的洒试。
以 mongoTuning.quickExplain 舉例
1) 入口代碼
主體調(diào)用函數(shù) prepExplain 和 printInputStage.
mongoTuning.quickExplain = (inputPlan) => {
// Takes as input an explain Plan. Emits a simplified
// version of that plan
const explainPlan = mongoTuning.prepExplain(inputPlan);
let stepNo = 1;
const printSpaces = function (n) {
let s = '';
for (let i = 1; i < n; i++) {
s += ' ';
}
return s;
};
const printInputStage = function (step, depth) {
if ('inputStage' in step) {
printInputStage(step.inputStage, depth + 1);
}
if ('inputStages' in step) {
step.inputStages.forEach((inputStage) => {
printInputStage(inputStage, depth + 1);
});
}
if ('indexName' in step) {
print(stepNo++, printSpaces(depth), step.stage, step.indexName);
} else {
print(stepNo++, printSpaces(depth), step.stage);
}
};
printInputStage(explainPlan, 1);
};
2) prepExplain是為了獲取 explainPlan， 代碼
mongoTuning.prepExplain = (explainInput) => {
// Takes as input explain output in one of the follow formats:
// A fully explain JSON document, in which case emits winningPlan
// An explain() cursor in which case, extracts the winningPlan from the cursor
// A specific plan step in which case just returns that
const keys = Object.keys(explainInput);
// printjson(keys);
if (keys.includes('queryPlanner')) {
// This looks like a top level Explain
return explainInput.queryPlanner.winningPlan;
} else if (keys.includes('hasNext')) {
// This looks like a cursor
if (explainInput.hasNext()) {
return mongoTuning.prepExplain(explainInput.next());
}
return { ok: 0, error: 'No plan found' };
} else if (keys.includes('stage')) {
// This looks like an actual plan
return explainInput;
}
return { ok: 0, error: 'No plan found' };
};
可以看出朴上，如果我們把之前的 explainDoc 傳入, 會默認拿到其 queryPlanner.winningPlan, 就是默認拿到winningPlan
3) printInputStage
在看 printInputStage 前垒棋，先看下 explainDoc.queryPlanner.winningPlan結(jié)構(gòu)，
這里展示MongoDB執(zhí)行過程中stage之間的關系痪宰，
(1) stage "PROJECTION_SIMPLE" 依賴 (2)stage "SORT"叼架，
(2) stage "SORT" inputStage 依賴 (3)stage "SORT_KEY_GENERATOR"，
依次類推衣撬，直到(5)stage IXSCAN不再依賴 inputStage 乖订，則結(jié)束。
所以正常的順序是
Mongo Shell>mongoTuning.quickExplain(explainDoc)
1 IXSCAN Phone_1
2 FETCH
3 SORT_KEY_GENERATOR
4 SORT
5 PROJECTION_SIMPLE
如上則 printInputStage 對應這個遞歸調(diào)用過程具练，代碼如下乍构，有inputStage則繼續(xù)找，直到找到?jīng)]有inputStage的stage扛点，輸出step.stage后返回哥遮。
const printInputStage = function (step, depth) {
if ('inputStage' in step) {
printInputStage(step.inputStage, depth + 1);
}
if ('inputStages' in step) {
step.inputStages.forEach((inputStage) => {
printInputStage(inputStage, depth + 1);
});
}
if ('indexName' in step) {
print(stepNo++, printSpaces(depth), step.stage, step.indexName);
} else {
print(stepNo++, printSpaces(depth), step.stage);
}
};
printInputStage(explainPlan, 1);
總結(jié)和延伸
通過分析工具，能更清晰簡潔地拿到explain結(jié)果陵究。
此外眠饮，由于 mongo --shell mongoTuning.js 支持js語法，我們可以根據(jù)需求定制的Mongo Shell命令畔乙，做出Explain類似的分析工具君仆。
