MongoDB 不懂 ESR 別說你會用 Index !!

建議搭配第二篇 MongoDB 索引的 ESR 規則詳解 一起閱讀


我原本以為對官方來說這是一個很基本的查詢通則,沒想到在 MongoDB Manual 搜尋不到相關的文獻,只有在官方 blog 的 Performance Best Practices: Indexing 這篇文章有介紹到 ESR 規則,讓我有點意外。

ESR(Equality, Sort, Range) 意思是指查詢條件與 Index 必須按照這個順序來安排,才能夠達到最佳的效能。

  • Equality: {$eq}
  • Sort: .sort({ })
  • Range: {$gte:{ }}

什麼情況適用

基本上你有使用 Index 就要這樣做,也就是都要這樣做。

假設你的查詢條件沒有排序,那就可以跳過 sort 階段。

假設你的查詢條件有排序,那 sort 必須緊跟在前一個 equality 階段。

例如有一把 index {a:1, b:1, c:1}

查詢條件就是 .find({a:1}).sort({b:1}),如果變成 .find({a:1}).sort({c:1}) 會看到 explain 的 stage 變成 SORT_KEY_GENERATOR,資料庫會在記憶體裡面進行排序,除了速度會變慢之外,若超過 MongoDB 佔用記憶體,則必須進行硬碟內暫存,速度更是大幅度降低。

因此,無論如何請盡量避免 SORT_KEY_GENERATOR,若非必要則絕對不要開始 allowDiskUse()。特殊的使用情境還是有的,例如定期產生的報表,它不追求效能,不是像使用者查詢報表那樣需要快速看到結果,所以要看需求情境來規劃。


  • IXSCAN: 透過 Index Scan
  • FETCH: 透過 Index Scan 去查詢並讀取整個檔案回傳
  • PROJECTION: 你有透過 PROJECTION 移除一些不必要的欄位再回傳(建議做法)
  • SORT_KEY_GENERATOR(IN-MEMORY-SORT): 使用記憶體進行排序,少資料量可接受
  • SORT_KEY_GENERATOR + allowDiskUse(): 使用記憶體與硬碟寫入排序,大資料量背景報表產生狀況,可接受
  • COLLSCAN: 整表查詢(最差狀況)

實際測試

我們準備建立以下測試資料與一把 index。

1
2
3
4
5
6
7
8
9
10
11
12
db.esr.insertMany([
{a: 1, b: 1, c: 1, d: 'dummy field'},
{a: 2, b: 2, c: 2, d: 'dummy field'},
{a: 3, b: 3, c: 3, d: 'dummy field'},
{a: 1, b: 4, c: 20, d: 'dummy field'},
{a: 2, b: 5, c: 21, d: 'dummy field'},
{a: 1, b: 6, c: 30, d: 'dummy field'},
{a: 1, b: 7, c: 31, d: 'dummy field'}
]);

db.esr.createIndex({ a:1, b:1, c:1 }, { name: "a_b_c"});

以下舉例都有刪除適當欄位


情境 1,查詢欄位先後順序影響

  • 查詢條件 db.esr.find({ a:1 })
    1
    2
    3
    4
    5
    6
    7
    8
    > db.esr.find({ a: 1}).explain('executionStats').executionStats.executionStages;
    {
    "stage" : "FETCH",
    "nReturned" : 4,
    "works" : 5,
    "advanced" : 4,
    "docsExamined" : 4,
    }

可以看到走到正確 Index 上

  • 查詢條件 db.esr.find({ b:1 })
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    > db.esr.find({ b: 1}).explain('executionStats').executionStats.executionStages;
    > db.esr.find({ b: 1}).explain('executionStats').executionStats.executionStages
    {
    "stage" : "COLLSCAN",
    "filter" : {
    "b" : {
    "$eq" : 1
    }
    }
    }

看到 COLLSCAN 就可以直接認定 fail

情境 2,排序的影響

  • 理想狀態 .find({ a: 1}).sort({b:1, c:1})
1
2
3
4
5
6
7
8
9
> db.esr.find({ a: 1}).sort({b:1, c:1}).explain('executionStats').executionStats.executionStages
{
"stage" : "FETCH",
"nReturned" : 4,
"works" : 5,
"advanced" : 4,
"isEOF" : 1,
"docsExamined" : 4,
}

可以看到理想狀態的排序就和 index 設計上一樣。

  • 排序倒序了 .find({ a: 1}).sort({b:-1, c:-1})
1
2
3
4
5
6
7
8
> db.esr.find({ a: 1}).sort({b:-1, c:-1}).explain('executionStats').executionStats.executionStages
{
"stage" : "FETCH",
"nReturned" : 4,
"works" : 5,
"isEOF" : 1,
"docsExamined" : 4,
}

即使排序使用了倒序,也不會有問題,但實際應用上不會這麼單純,請務必跑過 explain 查看 docsExaminednReturned 比例。

  • 排序倒序了 .find().sort({a:-1, b:-1, c:-1})
1
2
3
4
5
6
7
8
> db.esr.find().sort({a:-1, b:-1, c:-1}).explain('executionStats').executionStats.executionStages
{
"stage" : "FETCH",
"nReturned" : 4,
"works" : 5,
"isEOF" : 1,
"docsExamined" : 4,
}

即使排序全部使用了倒序,也不會有問題。

  • 部分沒按照 index 排序,就會出問題 .sort({a: 1, b:-1, c:-1})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
> db.esr.find().sort({a: 1, b:-1, c:-1}).explain('executionStats').executionStats.executionStages
{
"stage" : "SORT",
"nReturned" : 7,
"works" : 18,
"advanced" : 7,
"needTime" : 10,
"isEOF" : 1,
"sortPattern" : {
"a" : 1,
"b" : -1,
"c" : -1
},
"memUsage" : 574,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 7,

這邊我們就能很明確看到整個執行階段進入了 SORT 階段,下面有 SORT_KEY_GENERATOR,也就是剛剛提到的記憶體排序,若追求極限速度或資料量龐大,請盡量避免。

情境 3,欄位順序的影響(ESR)

  • 理想 ESR.find({a:1, c:{$gt:1}}).sort({b:1})
1
2
3
4
5
6
7
> db.esr.find({a:1, c:{$gt:1}}).sort({b:1}).explain('executionStats').executionStats.executionStages
{
"stage" : "FETCH",
"nReturned" : 3,
"works" : 5,
"isEOF" : 1,
"docsExamined" : 3,
  • 未按照 ESR 順序使用 .find({a:1, b:{$gt:1}}).sort({c:1})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
> db.esr.find({a:1, b:{$gt:1}}).sort({c:1}).explain('executionStats').executionStats.executionStages
{
"stage" : "SORT",
"nReturned" : 3,
"works" : 9,
"advanced" : 3,
"isEOF" : 1,
"sortPattern" : {
"c" : 1
},
"memUsage" : 246,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",

可以看到我們先使用了 b 欄位進行 RANGE 查詢,再使用 c 欄位進行排序,這會導致 IN-MEMORY-SORY,請避免。當然 index 也可以改建為 {a:1, c:1, b:1}..

總結

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
.find({a:1})                    => Perfect
.find({a:1, b:1}) => Perfect
.find({a:1}).sort({b:1}) => Perfect
.find({a:1}).sort({c:1}) => In-memory-sort
.find({a:1}).sort({b:1, c:1}) => Perfect
.find({a:1}).sort({b:1, c:-1}) => In-memory-sort
.find({a:1}).sort({b:-1, c:1}) => In-memory-sort


.find({a:1}).sort({b:1, c:1}) => Perfect
.find({a:1}).sort({b:-1, c:-1}) => Perfect
.find({a:1}).sort({b:-1, c:1}) => In-memory-sort
.find({a:1}).sort({b:1, c:-1}) => In-memory-sort

.sort({a:1, b:1, c:1}) => Perfect
.sort({a:-1, b:-1, c:-1}) => Perfect
.sort({a:1, b:1, c:-1}) => In-memory-sort
.sort({a:1, b:-1, c:1}) => In-memory-sort
.sort({a:1, b:-1, c:-1}) => In-memory-sort

實務上來說當然是沒這麼單純,特別是加上 aggregation 狀態後,因此每個 Collection 的 Indexes 都需要花特別多時間琢磨。一但有新的需求與新的查詢條件,都要仔細審視過一次全部查詢。