MySQLで時系列の集計用テーブルを効率よく差分更新するアイデア

レポート用の集計テーブル問題

レポート表示用に別途、集計テーブルを用いることがあります。

大量のレコードがあるテーブルに直接クエリで集計すると時間がかかるので、GROUP BY、COUNT、SUMなどを駆使して必要な粒度にまるめたテーブルを作る手法です。場合によっては圧倒的にパフォーマンスがよくなります。

例えばシンプルに記事(article)ごとのページビューを記録したテーブルがあるとします。

name	type
article	VARCHAR
viewed_at	DATETIME

これを日々の記事ごとのアクセス数に集計します。

name	type
article	VARCHAR
viewed_on	DATE
views	INT

通常であれば、

/* パターン1 */
CREATE TABLE reports (
  SELECT article, CAST(viewed_at AS DATE) AS viewed_on, COUNT(*) AS views
  FROM journals
  GROUP BY article, CAST(viewed_at AS DATE)
)

とか、

/* パターン2 */
INSERT INTO reports (
  article, the_date,  total
) VALUES (
  SELECT article, CAST(viewed_at AS DATE) AS viewed_on, COUNT(*) AS views
  FROM journals
  GROUP BY article, CAST(viewed_at AS DATE)
)

みたいな感じでしょうか。

問題点

これらの方法の問題点は、毎回journalsテーブル全体を集計するので、レコードが増えていくとその分だけ処理時間が長くなっていくことです。

パターン2の方法であれば差分実行できそうですが、すでに存在する日付の集計を事前に削除するなどしないと集計が積み上がっておかしな数値になってしまいます。

SQL1本でうまくできないかと考え、こんなやり方を思いつきました。

差分更新のアイデア

1. 集計テーブルに文字列のIDを用意する
2. GROUP BYする値からグループキーを生成してハッシュ文字列をIDにする
3. INSERTではなく、なければ挿入、あれば更新(UPSERT)する

name	type
id	VARCHAR(42) PIRMARY KEY
article	VARCHAR
viewed_on	DATE
views	INT

INSERT INTO reports (
  id, article, viewed_on, total
) SELECT * FROM (
  SELECT
    SHA1(CONCAT(IFNULL(article, ''), ':', CAST(viewed_at AS DATE))) AS id,
    article, CAST(viewed_at AS DATE) AS viewed_on, COUNT(*) AS views
  FROM journals
  WHERE viewed_at >= '任意の時刻'
  GROUP BY
    article,
    CAST(viewed_at AS DATE)
) AS t
ON DUPLICATE KEY UPDATE
  total = t.total

MySQLではON DUPLICATE KEY UPDATEでUPSERTできます。

この方法の利点は次の通りです。

• 任意の期間について絞り込んで処理できる
• 任意の時点で今日の分も正しい値が得られる
• SQL文ひとつでデータの整合性がとれる

SHA1を使いましたがハッシュ関数は何でもよいです。グループキーが短ければ使わなくてもいいかもしれません。

なんとなくハッシュ関数を使った方がインデックス効きやすいかなーと思います。値の衝突リスクもありますがまあレポートなので…

個人的には気楽で気に入ったのですが、いかがでしょうか。