Edited at

MongoDB v4 explain 結果をちゃんと理解してクエリ改善 (後半:クエリ改善)

@koshi_life です。

MongoDB v4 explain 結果をちゃんと理解してクエリ改善 (前半:explain結果の見方) の続きです。


今回の記事内容

本記事では、396,416件をフルスキャンしていた以下クエリ(平均実行時間 851.6ms)の速度改善を行います。前提条件やテストデータについては 前回記事を参考。


対象のクエリ

ありがちな1ユーザの最新投稿3件を取得するクエリを改善対象とします。

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3)

{ "title" : "MongoDB Aggregation 100本ノック 前半 (group, sort, limit, project)", "url" : "https://qiita.com/koshilife/items/422070ee6aa636fb75fc", "created_at" : ISODate("2019-04-15T11:52:57Z") }
{ "title" : "Mongoid 特定Collectionにのみインデックスを作る", "url" : "https://qiita.com/koshilife/items/140b5a66d5fa6d1e7ee1", "created_at" : ISODate("2019-04-14T15:06:26Z") }
{ "title" : "Google Sheets API v4「1セル 50000文字までの制限あるよ」", "url" : "https://qiita.com/koshilife/items/5aff56c1bb9e5d31067f", "created_at" : ISODate("2019-04-08T01:31:04Z") }


TL;DR


  • 走査レコードが少ないほど実行時間が短くなる。(傾向あり)

  • 複合フィールドの定義順は大事。走査レコード数が変わるので。1

  • 検証したデータ量では複合フィールドのソート順(昇順、降順)は実行速度に寄与しなかった。2

Btreeを頭に思い浮かべてIndexを貼る感じで上手に貼れるようになる気がしている。

以下記事が参考になった。


インデックス検証パターン


  • v1: user_idのみ単一インデックス {user_id: 1}

  • v2: user_id/created_at(降順)の複合インデックス {user_id: 1, created_at: -1}

  • v3: user_id/created_at(昇順)の複合インデックス {user_id: 1, created_at: 1}

  • v4: created_at(降順)/user_idの複合インデックス {created_at: -1, user_id: 1}

  • v5: 複数インデックスが存在 v1~v4 全て登録済み


検証結果

検証パターン
走査レコード
totalDocsExamined
実行時間(平均)
時間(1)
時間(2)
時間(3)
時間(4)
時間(5)
インデックスサイズ
備考

v1
45
2.2
2
3
5
0
1
2.67 MB

v2
3
0.6
2
0
0
1
0
5.45 MB
v2,v3,v5誤差かと

v3
3
0.2
1
0
0
0
0
5.45 MB
v2,v3,v5誤差かと

v4
4,806
38.8
52
53
20
46
23
8.74 MB

v5
3
0.4
0
0
0
1
1
-
v2のIndexが採択された

インデックスなし
396,416
851.6
951
636
914
893
864
-

※時間の単位はms


参考:各インデックス作成文

// v1

mongo> db.qiita_items.createIndex({user_id: 1}, {unique: false})
// v2
mongo> db.qiita_items.createIndex({user_id: 1, created_at: -1 }, {unique: false})
// v3
mongo> db.qiita_items.createIndex({user_id: 1, created_at: 1}, {unique: false})
// v4
mongo> db.qiita_items.createIndex({created_at: -1, user_id: 1}, {unique: false})


参考:explain結果


v1 {user_id: 1}

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3).explain('executionStats')

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "qiita_api_development.qiita_items",
"indexFilterSet" : false,
"parsedQuery" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"created_at" : -1
},
"limitAmount" : 3,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1
},
"indexName" : "user_id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
]
}
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 26,
"totalKeysExamined" : 45,
"totalDocsExamined" : 45,
"executionStages" : {
"stage" : "PROJECTION",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 51,
"advanced" : 3,
"needTime" : 47,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "SORT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 51,
"advanced" : 3,
"needTime" : 47,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"created_at" : -1
},
"memUsage" : 1041,
"memLimit" : 33554432,
"limitAmount" : 3,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 45,
"executionTimeMillisEstimate" : 0,
"works" : 47,
"advanced" : 45,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 45,
"executionTimeMillisEstimate" : 0,
"works" : 46,
"advanced" : 45,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 45,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 45,
"executionTimeMillisEstimate" : 0,
"works" : 46,
"advanced" : 45,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"user_id" : 1
},
"indexName" : "user_id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
]
},
"keysExamined" : 45,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
}
},
"serverInfo" : {
"host" : "KenjinoMacBook-Air.local",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}


v2 {user_id: 1, created_at: -1}

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3).explain('executionStats')

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "qiita_api_development.qiita_items",
"indexFilterSet" : false,
"parsedQuery" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1,
"created_at" : -1
},
"indexName" : "user_id_1_created_at_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 1,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"user_id" : 1,
"created_at" : -1
},
"indexName" : "user_id_1_created_at_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "KenjinoMacBook-Air.local",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}


v3 {user_id: 1, created_at: 1}

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3).explain('executionStats')

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "qiita_api_development.qiita_items",
"indexFilterSet" : false,
"parsedQuery" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1,
"created_at" : 1
},
"indexName" : "user_id_1_created_at_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 1,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"user_id" : 1,
"created_at" : 1
},
"indexName" : "user_id_1_created_at_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "KenjinoMacBook-Air.local",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}


v4 {created_at: -1, user_id: 1}

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3).explain('executionStats')

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "qiita_api_development.qiita_items",
"indexFilterSet" : false,
"parsedQuery" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"created_at" : -1,
"user_id" : 1
},
"indexName" : "created_at_-1_user_id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created_at" : [ ],
"user_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"created_at" : [
"[MaxKey, MinKey]"
],
"user_id" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 21,
"totalKeysExamined" : 4806,
"totalDocsExamined" : 4806,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 10,
"works" : 4807,
"advanced" : 3,
"needTime" : 4803,
"needYield" : 0,
"saveState" : 37,
"restoreState" : 37,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"nReturned" : 3,
"executionTimeMillisEstimate" : 10,
"works" : 4806,
"advanced" : 3,
"needTime" : 4803,
"needYield" : 0,
"saveState" : 37,
"restoreState" : 37,
"isEOF" : 0,
"invalidates" : 0,
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"nReturned" : 3,
"executionTimeMillisEstimate" : 10,
"works" : 4806,
"advanced" : 3,
"needTime" : 4803,
"needYield" : 0,
"saveState" : 37,
"restoreState" : 37,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 4806,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4806,
"executionTimeMillisEstimate" : 10,
"works" : 4806,
"advanced" : 4806,
"needTime" : 0,
"needYield" : 0,
"saveState" : 37,
"restoreState" : 37,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"created_at" : -1,
"user_id" : 1
},
"indexName" : "created_at_-1_user_id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created_at" : [ ],
"user_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"created_at" : [
"[MaxKey, MinKey]"
],
"user_id" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 4806,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "KenjinoMacBook-Air.local",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}


v5 v1~v4 全て登録済み

rejectedPlans が初めて登場し、最良のIndexを判断している様子がわかる。

mongo> db.qiita_items.find({'user_id':'koshilife'},{'_id':0,'created_at':1,'title':1,'url':1}).sort({'created_at':-1}).limit(3).explain('executionStats')

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "qiita_api_development.qiita_items",
"indexFilterSet" : false,
"parsedQuery" : {
"user_id" : {
"$eq" : "koshilife"
}
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1,
"created_at" : -1
},
"indexName" : "user_id_1_created_at_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"created_at" : -1
},
"limitAmount" : 3,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1
},
"indexName" : "user_id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
]
}
}
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user_id" : 1,
"created_at" : 1
},
"indexName" : "user_id_1_created_at_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
}
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 3,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 3,
"inputStage" : {
"stage" : "PROJECTION",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"transformBy" : {
"_id" : 0,
"created_at" : 1,
"title" : 1,
"url" : 1
},
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"user_id" : 1,
"created_at" : -1
},
"indexName" : "user_id_1_created_at_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"user_id" : [ ],
"created_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"user_id" : [
"[\"koshilife\", \"koshilife\"]"
],
"created_at" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "KenjinoMacBook-Air.local",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}





  1. 複合フィールドの定義順は大事と公式にも記載あり。Doc 



  2. ソート順 単一フィールドは関係ないが複合フィールドのインデックスは関係あるらしいDoc