時間軸集計にはパーティショニングがオススメ

今やっている案件で、過去ログAと過去ログBを結合して集計をしています。

「過去ログ」というのがポイントで、集計のことを考慮されていないデータ出力*1であったため、結合条件が時間のレンジという泣きそうな状態です。

今回は手元にMySQL5.7.9があるので、それでやってみました。

過去ログA

操作ログをイメージしてもらうと非常に近いです。
しかし、「何を」が抜けていて「誰が」「いつからいつまで」しか記録されていません。

user,start_at,end_at,time
UserA,2015-05-01 10:00:01,2015-05-01 10:01:13,72
UserB,2015-05-01 10:01:01,2015-05-01 10:01:53,52

データ量は550万件ぐらいです。

過去ログB

「何を」に相当するのがこのログです。
あまり書きすぎるとバレてしまうので、皆さんは不自然としか思えないでしょうが、「種別が」「いつからいつまで」が記録されています。

category,start_at,end_at,time
CategoryA,2015-05-01 10:00:00,2015-05-01 10:00:30,30
CategoryB,2015-05-01 10:00:30,2015-05-01 10:01:00,30
CategoryC,2015-05-01 10:01:00,2015-05-01 10:01:30,30

データ量は350万件ぐらいです。
日付は日またぎをすることはありません。（今回に限り）

やりたいこと

この過去ログA, Bを結合し、「誰が」「いつ」「何を」していたかを1テーブルに収めて、その後に集計関数を使って多角的に分析をしていきます。
（データ量が膨大なので、集計関数を使っての部分は別の何かになるかもしれません。）

まずは、１テーブルに収めるために以下のようなものが作りたいです。

user,category,start_at,end_at,time
UserA,CategoryA,2015-05-01 10:00:01,2015-05-01 10:01:13,72
UserA,CategoryB,2015-05-01 10:00:01,2015-05-01 10:01:13,72
UserA,CategoryB,2015-05-01 10:00:01,2015-05-01 10:01:13,72
UserB,CategoryC,2015-05-01 10:01:01,2015-05-01 10:01:53,52

クエリで表せば、こんな感じです。

select
  A.user,
  B.category,
  A.start_at,
  A.end_at,
  A.time
from
  LogA A
  inner join LogB B on (
    (
      A.start_at >= B.start_at and
      A.end_at <= B.start_at
    ) or (
      A.start_at <= B.end_at and
      A.end_at >= B.end_at
    )
  )
;

Aの時間帯に含まれるBを抽出し、BのcategoryをAに追加して出力します。
うーん。伝えいづらいですが、要するに「時間帯」で結合したいわけです。

実際はJavaでグリグリ回した

進捗が見えないので、LogAを1レコードずつ取ってきて、LogBを検索しました。

select
  B.category
from
  LogB B
where
  (
    :start_at >= B.start_at and
    :end_at <= B.start_at
  ) or (
    :start_at <= B.end_at and
    :end_at >= B.end_at
  )

このクエリの問題点

時間「帯」で結合するため、indexを全件舐める必要があります。
350万件というボリュームのためか、私のPCではLogAの1レコードあたり、2秒掛かりました。
そのため、550万件処理すると、「7,640日処理に掛かる」ことになります。

万策尽きたのでケーキ屋さんに転職しそうになりました。

とにかく分割！

今回のログの場合は時間で分割できるため、パーティショニングを利用してみました。

create table LogB (
  ...（中略）
)
PARTITION BY RANGE COLUMNS(start_at)
(
PARTITION p20150501 VALUES LESS THAN ('2015-05-01') ENGINE = InnoDB,
PARTITION p20150502 VALUES LESS THAN ('2015-05-02') ENGINE = InnoDB,
PARTITION p20150503 VALUES LESS THAN ('2015-05-03') ENGINE = InnoDB,
（中略）
PARTITION p20150504 VALUES LESS THAN ('2015-06-01') ENGINE = InnoDB
)

結果、1レコードあたり1.8秒に改善されました・・・あれ？

パーティションを指定してあげないと上手くいかないことがある

オプティマイザが利用するパーティションを正しく判断してくれないと、パーティション分割の意味がありません。
explainで実行計画をとってみると、不要なパーティションが検索対象に入っていました。

そのため、以下のようにパーティションを指定するクエリに修正しました。

select
  B.category
from
  LogB B partition(【対象日のパーティション名】)
where
  (
    :start_at >= B.start_at and
    :end_at <= B.start_at
  ) or (
    :start_at <= B.end_at and
    :end_at >= B.end_at
  )

0.05秒に改善

上記のクエリで1.8秒が0.05秒に改善されました。
これで、およそ3.2日で処理が完了します。

その他にやっていることとか（おまけ）

しかし、別テーブルに保存などを考えるとその他のオーバーヘッドがあり、3.2日でも全然嬉しくありません。
そのため、私の場合はjavaでスレッドを6個立ち上げ、100万件ずつ並行して処理をしています。

ログの特性もあり、550万件→550万件の保存ではなく、550万件→数千万件の保存になるため、
実行から12時間経った今でも処理が終わっていません・・・。
（Core i7 MEM16GB で 4コアフル使用、常時CPU80%超え、現在2000万件保存完了）

スレッド6は50万件の割り当てのため、終了しており、そうなると数時間後には全スレッド終わるものじゃないかと思っています。

[PR] 日本でまともに使えるオープンソースCRMが出ています。

CRM構築はオープンソースのF-RevoCRM －エフレボシーアールエム－

*1:今後の出力ログはAとBを結合したものになるので問題ありません。