mongodbのsparse indexと `{$ne: null}` 的なquery
疎なfield(ほとんどがnullでごく少数のdocumentだけ値が入る)があったとして。 これに対するindexはsparse indexで十分じゃないかなと思っていたのだけれど。
試してみたらだめなqueryだったという話。
explainの見方
その前にexplainの見方をメモ
db.<collection>.find(q).explain("executionStats")
- winningPlanが採用されたもの(rejectedPlanが非採用)
- 使われているindexはinpuStageがIXSCANの時のindexName(IXSCAN=index scan, COLLSCAN=collection scan)
- totalDocsExaminedはindexで絞り込まれたdocument数
- nReturnedは返されたdocument数
要はIXSCANになっていればOK。
sparse index
部分関数みたいないもの。sparse indexとdense indexで対応している。通常のindexがdense index。 値が入っていない部分に関してはindexを作成しないというもの。indexのサイズを小さくできることが利点(逆に言うとsparse indexにしたからといって早くなることはない。メモリーに乗り切らなくなるみたいなすごいきわどい状況の話でなければ)。
例えばKというfieldがありこれがnull等の場合に貼るindexを考えてみたときに以下の様な形になる。
value | dense index | sparse index |
---|---|---|
{K: 1} | true | true |
{K: null} | true | true |
{} | true | false |
fieldが存在しない場合にはindexは生成されないけれど、nullの場合は作成される。 (ちなみにunique:trueを付けたときにsparse:trueを付けると、該当のfieldを持たないdocumentはunique制約の対象外にできるみたいな話もあったりする)
{$ne: null}
もしくは {$exists: true}
sparse index
ある値が存在するのならばというqueryは {$exists: true}
という形になるし。これはindexがあるかで判定ができる。
一方で {$exists: false}
はindexの対象外の値を見ているのでindexだけをみて判断できない。
sparse indexが賢いなら、全部nullのfieldになっていた場合に、 {$ne: null}
でもなんか良い感じにindex使われないかなと思ったけれど。そんなことはなかった。
考えてみたら当たり前で null以外は値を持たないも含んでしまうのでsparse indexではまかないきれない範囲になっている。
代わりに {$exists: true}
とのandを取ってあげるとindexを使ってくれる。
dense index
一方dense indexというか通常のindexは全部の情報を持っているので {$ne: null}
だけでindexが効く。
考えてみれば当たり前の話ではあるけれど。
実験
entriesというidとnameとmarkedAtだけで作られたdocumentを格納するcollectionを作成。 markedAtは1つだけに値を入れる(それ以外はnullかunset)。 markedAtにindexを貼ってみて上手くindexが使われるか調べる。
db.createCollection("entries"); db.entries.insert([ {"_id": ObjectId(), "name": "foo"}, {"_id": ObjectId(), "name": "bar"}, {"_id": ObjectId(), "name": "boo"}, ]); // create sparse index db.entries.createIndex({"markedAt": 1}, {sparse: true}); // markedAt 1 and other items don't have markedAt db.entries.updateOne({}, {$set: {"markedAt": ISODate()}}) db.entries.find({markedAt: {$exists: true}}).explain("executionStats") // IXSCAN // markedAt 1 and other items markedAt are null db.entries.updateMany({}, {$set: {"markedAt": null}}); db.entries.updateOne({}, {$set: {"markedAt": ISODate()}}) db.entries.find({markedAt: {$ne: null}}).explain("executionStats") // COLLSCAN db.entries.find({$and: [{markedAt: {$ne: null}}, {markedAt: {$exists: true}}]}).explain("executionStats") // IXSCAN // create dense index db.entries.dropIndex("markedAt_1"); db.entries.createIndex({"markedAt": 1}); // markedAt 1 and other items markedAt are null db.entries.find({markedAt: {$ne: null}}).explain("executionStats") // IXSCAN // markedAt 1 and other items don't have markedAt db.entries.updateMany({}, {$unset: {"markedAt": 1}}); db.entries.updateOne({}, {$set: {"markedAt": ISODate()}}) db.entries.find({markedAt: {$exists: false}}).explain("executionStats") // IXSCAN
sparse indexにする時には、$exists: true
を忘れずにという話