MongoDB aggregation の検証
だいぶ空いてしまったが、久々の更新!
Aggregate周りを色々検証したので載せておく。
基本的なTAG構造。
TAGを扱う上でオーソドックスなクエリーと性能を調査。
性能は、people 2000万件、hobies 3200万件、完全ランダムデータで計測。
月数千円で手に入るコンピューティングリソースを使ってます。
Model
Mike
:性別:男
:趣味: 映画、テレビ
Cate
:性別: 女
:趣味: テニス
Bob
:性別: 男
:趣味: 映画、テレビ、テニス
Data
RDBMSではこんな感じの正規化をする。
people
_id | name | sex |
---|---|---|
1 | Mike | male |
2 | Cate | female |
hobbies
pid | name |
---|---|
1 | TV |
1 | cinema |
2 | tenis |
3 | TV |
3 | tenis |
BSON
一方MongoDBではこんな感じで持つだろう
{ _id: 1, "name": "Mike", "sex": "male", "hobbies": [ "cinema", "TV" ] } { _id: 2, "name": "Cate", "sex": "female", "hobbies": [ "tenis" ] } { _id: 3, "name": "Bob", "sex": "male", "hobbies": [ "TV", "tenis", "cinema" ] }
問題1
趣味TVの男性の数を抽出
得たい結果
count(people._id) | |
---|---|
2 |
sql
説明不要・・・
SELECT COUNT(people._id) FROM people INNER JOIN hobbies ON people._id = hobbies.pid WHERE people.sex = ‘male' AND hobbies.name = 'TV';
mongodb
なんの変哲も無いクエリーだが・・・
db.people.find( { sex: ‘male’, hobbies: ‘TV’ } ).count();
性能
Mongo爆速!!
countを舐める処理が賢いのが要因。
mysql5.6 | MongoDB 2.6.3 |
---|---|
94sec | 1sec |
問題2
趣味毎の男性人数を集計
得たい結果
hobbies.name | count(people._id) |
---|---|
tenis | 1 |
cinema | 2 |
TV | 2 |
sql
普通のgroup by
SELECT hobbies.name, COUNT(people._id) FROM people INNER JOIN hobbies ON people._id = hobbies.pid WHERE people.sex = 'male' GROUP BY hobbies.name ORDER BY COUNT(people._id);
mongodb
hobbiesを一旦$unwindでバラす。
ちょっと独特なので下で解説
db.people.aggregate([ { $match: { sex: 'male' }}, { $project: { hobbies: 1, count:{ $literal: 1 }}}, { $unwind: "$hobbies" }, { $group: {_id: "$hobbies", count: { $sum: "$count" }}}, { $sort: { count: 1 } }, ]).
解説:パイプラインの各段階でのデータ
FIRST
{ _id: 1, "name": "Mike", "sex": "male", "hobbies": [ "cinema", "TV" ] } { _id: 2, "name": "Cate", "sex": "female","hobbies": [ "tenis" ] } { _id: 3, "name": "Bob", "sex": "male", "hobbies": [ "TV", "tenis", "cinema" ] }
$match: { sex: 'male' }
最初に絞る。
{ _id: 1, "name": "Mike", "sex": "male", "hobbies": [ "cinema", "TV" ] } { _id: 3, "name": "Bob", "sex": "male", "hobbies": [ "TV", "tenis", "cinema" ] }
$project: { hobbies: 1, count:{ $literal: 1 }}
フィールドも絞る。$literalは2.6系から。SQLとは逆の発想。
{ "hobbies" : [ "cinema", "TV" ], "count" : 1 } { "hobbies" : [ "TV", "tenis", "cinema" ], "count" : 1 }
$unwind: "$hobbies"
$unwindで配列を展開してフラットなドキュメントリストにする
{ "hobbies" : "cinema", "count" : 1 } { "hobbies" : "TV", "count" : 1 } { "hobbies" : "TV", "count" : 1 } { "hobbies" : "tenis", "count" : 1 } { "hobbies" : "cinema", "count" : 1 }
$group: {_id: "$hobbies", count: { $sum: "$count" }}
あとは普通に$groupする
{ "_id" : "cinema", "count" : 2 } { "_id" : "TV", "count" : 2 } { "_id" : "tenis", "count" : 1 }
性能
やはりMongo(Aggregate)速い!
一見非効率なpipelineだが速度は早い。
mysql5.6 | MongoDB 2.6.3 |
---|---|
145sec | 30sec |
問題3
男性が同時に嗜んでいる趣味(趣味の関係性)
得たい結果
h1.name | h2.name | count |
---|---|---|
TV | cinema | 2 |
TV | tenis | 1 |
cinema | TV | 2 |
cinema | tenis | 1 |
tenis | TV | 1 |
tenis | cinema | 1 |
sql
Self Join が常套手段
SELECT h1.name, h2.name, COUNT(people._id) FROM people INNER JOIN hobbies h1 ON people._id = h1.pid INNER JOIN hobbies h2 ON people._id = h2.pid WHERE people.sex = 'male' AND h1.name != h2.name GROUP BY h1.name, h2.name ORDER BY COUNT(people._id) DESC;
mongo
hobbies配列を2個持たせて2重に$unwindする。
db.people.aggregate([ { $match: { sex: 'male' }}, { $project: { h1: "$hobbies", h2: "$hobbies", count:{ $literal: 1 }}}, { $unwind: "$h1" }, { $unwind: "$h2" }, { $redact: { $cond: { if: { $eq: ["$h1", "$h2"]}, then: "$$PRUNE", else: "$$KEEP"} } }, { $group: { _id: { h1: "$h1", h2: "$h2"}, count: { $sum: "$count" }}}, { $sort: { count: -1 } }, ])
性能
Mongo(Aggregate)早すぎる!
$unwindを2重にして一時的にドキュメント数が配列数の2乗倍の数になっているのだが性能にはあまり影響しないようだ。
パイプラインの段数も多くなってるが同じく性能にあまり影響しない。
カジュアルに繋げて大丈夫!!
mysql5.6 | MongoDB 2.6.3 |
---|---|
195sec | 36sec |
実際、業務レベルでも4回$unwindする様な事をしても、充分実用に耐えているな。
問題4
男性の趣味数の分布
得たい結果
hobby_count | count(pid) |
---|---|
2 | 1 |
3 | 1 |
sql
サブクエリーの結果を更にgroup by。辛い感じのSQL
SELECT hobby_count, COUNT(pid) FROM ( SELECT people._id AS pid, COUNT(name) AS hobby_count FROM people INNER JOIN hobbies ON people._id = hobbies.pid WHERE people.sex = 'male' GROUP BY hobbies.pid ) hobby_count_per_person GROUP BY hobby_count ORDER BY COUNT(pid);
mongo
パイプラインだと素直。
db.people.aggregate([ { $match: { sex: 'male' }}, { $project: { count: {$literal: 1}, numhobbies: { $size: "$hobbies" } } }, { $group: { _id: "$numhobbies", count: { $sum: "$count" } } }, { $sort: { count: 1 } }, ])
性能
やはりMongoが早いが、$unwindでひたすらコンテキストを増やすタイプのクエリーより遅い。
これは全然わからないな。
mysql5.6 | MongoDB 2.6.3 |
---|---|
186sec | 53sec |
総括
集計に関しては明らかにMongoDBのAggregationFWが優秀だ。
MongoDB
正規化する代わりにembedするので更新が有った時に全更新が辛い。(不可能な時もある)
この辺に目を瞑れるならMongoDBは充分有用だ。
特にAggregationFW はまさに集計用途に設計されており非常に早い。
またパイプライン処理はプログラマに近いかな。(イメージはシェルスクリプトと一緒)
しかしAggregateFWのPIPELINEの処理は中が全く想像できず
何をやれば性能がどうなるのか?想像しにくい面が有る。
AggregationFW特徴など色々
- 複雑なPipelineを多段組んでも大丈夫そう。
- Sharding環境でも効率的に動く。
- $matchにshard keyが含まれていれば
- 無関係なShardには処理を投げない。
- バラバラに処理できる部分(map)は
- 各Shardで処理を進め、集計が必要な部分(reduce)はPrimary shardが行う。