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 を忘れずにという話