https://flic.kr/p/qakxAH
為了增進資料庫的查詢效率，我們會建立索引。但是要如何比較前後的變化，以便得知改善了多少呢？MongoDB 提供了一種途徑，可以產生執行計劃，讓我們得知資料庫在執行時經過了哪些階段，以及各階段的詳情。
本文將示範執行計劃的產生方式，並解說其內容。接著會延續前一篇的「索引的用途與操作方式」，驗證索引帶來的效率提升。
一、執行計劃是什麼
執行計劃紀錄了資料庫在執行操作時做了哪些事情。以查詢操作為例，除了在 collection 掃描 document 外，可能還有讀取索引、排序、分頁與擷取特定欄位等情況。
由於存取資料庫屬於 I/O 操作，對於資料量多、請求量高或查詢頻繁的系統而言，查詢速度會相當地影響系統的反應快慢。對此，開發者可能會做出一些調整，例如建立索引或調整欄位等。接著分析查詢計劃，確認索引有被使用以及花費時間有減少。
那麼要如何查看 MongoDB 的執行計劃呢？只要在操作的語法加上 explain 方法即可，本文是以 find 方法為例：
db.book.find({ "title": "Book 1" }).explain()
以下是得到的執行計劃之部份內容：
{
"queryPlanner": {
"parsedQuery": {
"title": {
"$eq": "Book 1"
}
},
"winningPlan": {
"stage": "COLLSCAN",
"filter": {
"title": {
"$eq": "Book 1"
}
},
"direction": "forward"
},
"rejectedPlans": []
}
}
其中 winningPlan 的意義是資料庫採用的查詢方式。「COLLSCAN」代表掃描整個 collection，通常是效率最差的方式。在接下來的小節，我們會建立一些索引，從執行計劃中看出效率的改變。
二、範例資料介紹
以下介紹本文使用的範例資料。假設 collection 名稱叫做「book」，並且有以下 document（略過 _id 欄位）。
| title | category | price |
|---|---|---|
| Book 1 | Computer | 1 |
| Book 2 | Computer | 2 |
| ... | ... | ... |
| Book 20000 | Computer | 20000 |
| Book 1 | Economic | 20001 |
| ... | ... | ... |
| Book 20000 | Economic | 40000 |
| Book 1 | Accounting | 40001 |
| ... | ... | ... |
| Book 20000 | Accounting | 60000 |
欄位包含 title（書名）、category（類別）與 price（價格）。其中 category 又分為 Computer、Economic 與 Accounting 三種，各有 2 萬筆 document。
以下是產生範例資料的程式碼，讀者可用於初始化資料：
由於 collection 中有相當多筆資料，因此對於不同的執行計劃，效率的差距也會放大。
三、產生執行計劃
從這一節開始，讓我們實際查詢範例資料，並產生執行計劃，後續小節再進行解讀。首先建立一個索引，它是以 category 欄位遞增排序：
db.book.createIndex({ "category": 1 })
（一）產生方式
接著請讀者進行以下的操作：
db.book.find({ "category": "Computer" })
.sort({ "price": 1 })
.skip(30)
.limit(10)
.project({ "_id": 0, "title": 1, "price": 1 })
.explain("executionStats")
這段語法是查詢價格第 31 至 40 低的電腦類書籍，依價格遞增排序，並只留下書名與價格欄位。最後使用 explain 方法，產生執行計劃。
若讀者是在 NoSQLBooster 這款 GUI 工具進行，會看到視覺化（Visual Tree）的結果，如下圖：
也可點選右上方的下拉式選單，切換到 JSON 格式。
（二）Verbosity 模式
前面使用 explain 方法時，傳入了一個叫做 verbosity 的參數。它是用來控制執行計劃內容的詳細程度。可傳入的值如下。
- queryPlanner：包含選出的執行方式。使用 explain 方法但未傳入參數時，將使用此預設值。
- executionStats：除了 queryPlanner 的內容，也包含捨棄的執行方式。且被選出的方式將會實際進行評估，因此也可獲得相關統計訊息。
- allPlansExecution：除了 executionStats 的內容，也會對選出與捨棄的執行方式做評估。其方式是以競速（race）的方式進行，內容並不會與 executionStats 的統計訊息相同。
無論傳入何種參數，MongoDB 在產生執行計劃時，一律不會對資料庫進行寫入。讀者不必擔心 document 的內容被修改或刪除。
四、執行計劃解讀
接續第三節的查詢語法，其執行計劃的 JSON 內容會出現「executionStats」欄位。以下是它的部份內容：
該內容的欄位層次有點多，首先我們來認識第一層欄位的定義。
- nReturned：查詢結果的 document 數量。
- executionTimeMillis：執行時間，單位為毫秒。其中包含產生與選擇執行計劃，及實際執行的時間。
- totalKeysExamined：掃描的索引條目數量。
- totalDocsExamined：比對的 document 數量。
- executionStages：資料庫為了完成操作，所經過的執行階段。
在 executionStages 欄位中，會紀錄一至多個執行階段（stage）的詳情。特別的地方是，這些階段是以 embedded document，也就是巢狀欄位的形式來呈現，內部的欄位名為 inputStage。而最一開始的階段位於最內層，接下來讓我們由內而外解讀這些 stage。
（一）IXSCAN 階段
此階段的目的，是根據查詢條件掃描索引中的條目，找出可能符合條件的 document 的地址。本階段的使用的索引名為「category_1」。回傳了 2 萬筆資料。
從第二節的範例資料，我們已知 collection 中有 2 萬筆 document 符合條件。而現在在索引中也找出相同的數量，也就是 keysExamined 欄位值。
為什麼筆者前面說「可能符合條件」呢？假設查詢條件還包含其他欄位（例如 price 小於某個值），但仍命中這個索引，則這 2 萬筆索引條目所指向的 document 就未必都符合條件了。由此可知，IXSCAN 階段算是縮小範圍的過程。
（二）FETCH 階段
此階段的目的，是利用 IXSCAN 階段所獲得的地址，找到 document 並比對內容，篩選出確實符合查詢條件的資料。本階段回傳了 2 萬筆資料。
由於前一階段回傳了 2 萬筆資料，因此執行時也就得比對相同的數量，也就是 docsExamined 欄位值。
（三）SORT_KEY_GENERATOR 階段
此階段的目的，是獲取用來排序的欄位。
（四）SORT 階段
此階段的目的，是根據指定的欄位值來排序 document。
另外為了處理後續的分頁，因此排序後，只留下前 40 筆資料（skip 30 筆 + limit 10 筆）。
由於是在記憶體中排序，尚可找到 memLimit 與 memUsage 欄位，分別代表記憶體的限制量與使用量。
（五）SKIP 階段
此階段的目的，是跳過指定數量的資料。進入這階段的有 40 筆資料，跳過 30 筆後，回傳了剩下 10 筆資料。
（六）PROJECTION 階段
此階段的目的，是挑選要輸出的 document 欄位。最後回傳了 10 筆資料。
以上就是該查詢語法所經過的執行階段。MongoDB 並不是只有這些階段而已，不同的操作可能會不同種類的階段。
執行計劃中的 inputStage 都具有 executionTimeMillisEstimate 欄位。其為預估值，且每次都不太一樣。茲將本例各階段的預估執行時間整理如下。
| 階段 | 執行時間（毫秒） | 累計執行時間（毫秒） |
|---|---|---|
| IXSCAN | 67 | 67 |
| FETCH | 11 | 78 |
| SORT_KEY_GENERATOR | 43 | 121 |
| SORT | 22 | 143 |
| SKIP | 0 | 143 |
| PROJECTION | 0 | 143 |
五、驗證複合索引的效率
當查詢條件不只有一個欄位時，可考慮建立複合索引，達到比單欄位索引更快的查詢速度。在本節，筆者使用 category 與 title 欄位作為查詢條件：
db.book.find(
{
"category": "Computer",
"title": "Book 12345"
}
)
.explain("executionStats")
（一）單欄位索引的效率
當命中第三節建立的「category_1」索引時，以下是筆者某次執行計劃中的 execution stats 部份內容。
{
"executionStats": {
"nReturned": 1,
"executionTimeMillis": 62,
"totalKeysExamined": 20000,
"totalDocsExamined": 20000,
"executionStages": {
"stage": "FETCH",
"executionTimeMillisEstimate": 55,
"docsExamined": 20000,
"inputStage": {
"stage": "IXSCAN",
"executionTimeMillisEstimate": 22,
"keysExamined": 20000
}
}
}
}
掃瞄了 2 萬筆索引條目，再比對 2 萬筆 document，花了 62 毫秒。
（二）複合索引的效率
接著我們建立一個複合索引，使用 title 與 category 欄位排序。
db.book.createIndex(
{
"title": 1,
"category": 1
}
)
當命中「title_1_category_1」索引時，以下是執行計劃中的 execution stats 部份內容。
{
"executionStats": {
"nReturned": 1,
"totalKeysExamined": 1,
"totalDocsExamined": 1,
"executionStages": {
"stage": "FETCH",
"executionTimeMillisEstimate": 0,
"docsExamined": 1,
"inputStage": {
"stage": "IXSCAN",
"executionTimeMillisEstimate": 0,
"keysExamined": 1
}
}
}
}
讀者可以看到，資料庫只掃描 1 筆索引條目，並比對 1 筆 document 而已。而 IXSCAN 與 FETCH 階段的預估執行時間皆小於 1 毫秒，快上許多。
六、驗證覆蓋索引的效率
在查詢時，若只需要回傳 document 的部份欄位，可考慮利用覆蓋索引的特性來加快速度。請建立一個複合索引，先後使用 category 與 price 欄位排序：
db.book.createIndex(
{
"category": 1,
"price": 1
}
)
（一）回傳所有欄位
以下是查詢 category 欄位為「Computer」的語法，預設會回傳 document 的所有欄位。
db.book.find({ "category": "Computer" }).explain("executionStats")
當命中「category_1_price_1」索引時，以下是執行計劃中的部份內容：
{
"executionStats": {
"nReturned": 20000,
"totalKeysExamined": 20000,
"totalDocsExamined": 20000,
"executionStages": {
"stage": "FETCH",
"executionTimeMillisEstimate": 63,
"docsExamined": 20000,
"inputStage": {
"stage": "IXSCAN",
"executionTimeMillisEstimate": 44,
"keysExamined": 20000
}
}
}
}
讀者可以試著先執行幾次，確認查詢時間大概是多久。以筆者為例，某次的預估查詢時間為 63 毫秒。
由於 document 的 _id 與 title 欄位並沒有收錄在該索引中，因此資料庫仍需找出 document，取得所有欄位的值。讀者也能看到 totalDocsExamined 欄位值為 2 萬。
（二）回傳指定欄位
接著使用相同的查詢條件，但指定回傳的欄位只有 price，則語法如下：
db.book.find(
{
"category": "Computer"
},
{
"_id": 0,
"price": 1
}
)
.explain("executionStats")
當命中「category_1_price_1」索引時，以下是執行計劃中的部份內容：
{
"executionStats": {
"nReturned": 20000,
"totalKeysExamined": 20000,
"totalDocsExamined": 0,
"executionStages": {
"stage": "PROJECTION",
"executionTimeMillisEstimate": 36,
"inputStage": {
"stage": "IXSCAN",
"executionTimeMillisEstimate": 36,
"keysExamined": 20000
}
}
}
}
以筆者為例，某次的預估查詢時間為 36 毫秒。
由於 price 欄位有收錄在索引中，因此資料庫可直接從索引中獲取該值，不必另外找出 document。讀者也能看到執行計劃中沒有 FETCH 階段，而 totalDocsExamined 欄位值也為 0。
七、使用索引幫助排序
在第四節的範例中，我們使用 price 欄位來排序。然而從各階段執行時間的表格可看出，資料庫花了不少時間在排序。若能利用索引先將資料排序好，便能節省許多時間。
本節筆者同樣以 category 欄位做為條件，並根據 price 欄位排序。此處用了「hint」方法，指定使用「category_1」索引。
db.book.find({ "category": "Computer" })
.sort({ "price": 1 })
.hint("category_1")
.explain("executionStats")
讀者可以試著先執行幾次，確認預估執行時間大概是多久，以下是筆者某次的數據。
| 執行階段 | 執行時間（毫秒） | 累計執行時間（毫秒） |
|---|---|---|
| IXSCAN | 40 | 40 |
| FETCH | 65 | 105 |
| SORT_KEY_GENERATOR | 33 | 138 |
| SORT | 61 | 199 |
接著指定使用第六節建立的「category_1_price_1」索引，並再執行一次。
db.book.find({ "category": "Computer" })
.sort({ "price": 1 })
.hint("category_1_price_1")
.explain("executionStats")
以下是筆者某次的數據。
| 執行階段 | 執行時間（毫秒） | 累計執行時間（毫秒） |
|---|---|---|
| IXSCAN | 25 | 25 |
| FETCH | 32 | 57 |
由於不需要在記憶體中排序，我們可發現少了「SORT_KEY_GENERATOR」與「SORT」兩個階段。預估的執行時間也變快許多。
該索引雖然以 price 欄位遞增排序，但查詢時以遞減來排序也是可以的，資料庫會自行決定掃瞄的方向。總體來說，查詢時不論用幾個欄位來排序，只要與索引的排序方向「完全相同」或「完全相反」，即可免於在記憶體中排序。
八、使用索引反而變慢
索引是加快查詢的好幫手，但當查詢結果的 document 數量很多時，可能不用索引反而還比較快。
本節筆者同樣以 category 欄位做為查詢條件。由於前面小節已經有建立關於 category 的索引，因此查詢前暫時將它們隱藏，藉此引發 COLLSCAN。附帶一提，MongoDB 4.4 版以上才有隱藏索引的功能，若讀者版本不夠，亦可暫時刪除索引。
db.book.hideIndex("category_1")
db.book.hideIndex("category_1_price_1")
db.book.find({ "category": "Computer" }).explain("executionStats")
讀者可以試著先執行幾次，確認執行時間大概是多久。以筆者為例，執行計劃中的 executionTimeMillis 欄位值落在 38 到 44 毫秒之間。
接著將剛剛的索引取消隱藏，隨後繼續執行相同的查詢。
db.book.unhideIndex("category_1")
db.book.unhideIndex("category_1_price_1")
db.book.find({ "category": "Computer" }).explain("executionStats")
讀者會發現命中索引後，花費的時間變多了。以筆者為例，executionTimeMillis 落在 53 到 67 毫秒之間。
為什麼查詢結果太多時，執行時間會變慢呢？這是因為 MongoDB 會先產生執行計劃，挑選出要使用的索引再開始查詢。所以有一部份時間是花在這裡。
除此之外，資料庫會根據索引條目中紀錄的 document 地址，從磁碟中找出資料。當結果的 document 越多，對磁碟的 I/O 存取也就越多。以此例來說，查詢結果有 2 萬筆 document，佔了 collection 的三分之一，導致花費時間已經大於 COLLSCAN（全表掃瞄）了。
留言
張貼留言