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。

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 })

> 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 })

> 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})

> 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})

> 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 查看 docsExamined 與 nReturned 比例。

排序倒序了 .find().sort({a:-1, b:-1, c:-1})

> 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})

> 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})

> 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})

> 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}..

總結

.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 都需要花特別多時間琢磨。一但有新的需求與新的查詢條件，都要仔細審視過一次全部查詢。