中年engineerの独り言 - crumbjp

LinuxとApacheの憂鬱

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が優秀だ。

MySQL

そもそもRDBMSは集計用途では無い。正規化してJOINする事こそが本領だ。
クエリーも複雑になりがちだし速度も出ない。
集計の性能は直感的で想像しやすい。

MongoDB

正規化する代わりにembedするので更新が有った時に全更新が辛い。(不可能な時もある)
この辺に目を瞑れるならMongoDBは充分有用だ。

特にAggregationFW はまさに集計用途に設計されており非常に早い。
またパイプライン処理はプログラマに近いかな。(イメージはシェルスクリプトと一緒)

しかしAggregateFWのPIPELINEの処理は中が全く想像できず
何をやれば性能がどうなるのか?想像しにくい面が有る。

AggregationFW特徴など色々
  • 複雑なPipelineを多段組んでも大丈夫そう。
  • Sharding環境でも効率的に動く。
    • $matchにshard keyが含まれていれば
    • 無関係なShardには処理を投げない。
    • バラバラに処理できる部分(map)は
    • 各Shardで処理を進め、集計が必要な部分(reduce)はPrimary shardが行う。