可変の期間内で一意なユーザーのアクセス数を集計したい

Question

LAMP環境でのシステム開発を行っています。

とあるテーブルに「ユーザーID」と「アクセス時間」のデータを保持しており
このテーブルから、一定の期間内における一意なユーザーのアクセス数を集計したいです。

SELECT count(distinct user_id) FROM table WHERE access_date BETWEEN "FROM" AND "TO";

現状は、ループ処理にて上記クエリのTOの日付を1日づつカウントアップし
1回目：2016/1/1～2016/1/2
2回目：2016/1/1～2016/1/3
3回目：2016/1/1～2016/1/4
という風にして、各期間での集計を行っているのですが
該当テーブルのデータ件数が100万件ほどあり単純なSELECT文でも1秒弱かかっており
1年分集計するのに5分以上もかかってしまいます。

テンポラリテーブルやINDEXを使っても何故か処理時間に変化はありませんでした。
また、集計に必要なデータを、一旦PHP側に保持しPHP側で集計を行おうとも考えましたが
データ件数が多すぎるためメモリの消費量が多く断念いたしました。

例えば、必要な情報を1つのクエリでまとめて取得できたり
何か他の手段にて、もっと早く必要な情報を取得する方法は無いでしょうか？

Comments

[退会済みユーザー]

SHOW CREATE TABLE LIKE 'テーブル名' で得られるテーブル定義をご提示ください。

[KAGURA]

開発環境が手元にないので、テーブル定義の情報そのままを提示できないのですが
user_idはvarchar(20)、access_dateはtimestampでした。
他のカラムについてはユーザーが使用している端末名等が保持されております。

Answer 1

念のため確認させていただきますが、集計のために使用しているSQL文は、質問欄にご提示のままですか？

実は

WHERE DATE(access_date) BETWEEN "FROM" AND "TO";

だったり、他の条件が追加されていたりしませんか？

テンポラリテーブルやINDEXを使っても何故か処理時間に変化はありませんでした。

とのことですが、MySQLでは、カラムに関数や何らかの計算を施していると、インデックスが使用されません。
（根拠となる、信用できる記述が見当たらず、提示できないのが恐縮ですが、、）

また、場合によっては、「他の条件」がインデックスの使用を妨げている可能性も考えられます。

もし、そうであれば、質問欄に以下を提示していただくと、より具体的な回答が可能になります。

• 可能な限り「そのまま」のSQL文
• Kosuke_Shibuya様のご指摘の通り、SHOW CREATE TABLE LIKE 'テーブル名'で得られるテーブル定義
• EXPLAIN 可能な限り「そのまま」のSQL文というSQL文の実行結果

---

あるいは、すでにご提示のSQL文は十分にインデックスが効いている、ということはありませんか？
お使いの環境が不明なので確かなことは言えませんが、

データ件数が100万件ほどあり単純なSELECT文でも1秒弱

というのは、それほど悪くないパフォーマンスだと感じます。

---

で、ようやく以下からが回答となりますが、
やりたいことは「日」単位の期間による集計のようですので、
takasima20様の回答にあるように、あらかじめ「日別のアクセスユーザー」を記録する集計テーブルを作成してやるのが良いと思います。

具体的には、以下の通りです。

CREATE TABLE aggregated (
  access_date DATE NOT NULL,
  user_id varchar(20) NOT NULL,
  
  UNIQUE INDEX (access_date, user_id)
  UNIQUE INDEX (user_id, access_date)
);

INSERT IGNORE INTO aggregated 
SELECT DATE(access_date), user_id FROM table WHERE access_date BETWEEN 【集計したい期間の最小値】 AND 【同 最大値】;

あとは、集計テーブル（aggregatedテーブル）に対して以下のようなSQL文を実行してやれば、望むデータが取得できます。

SELECT COUNT(DISTINCT user_id) FROM aggregated WHERE access_date BETWEEN ...;

これなら、集計テーブルの作成には時間がかかりますが、その後のSELECT文は、おそらく十分なパフォーマンスが期待できます。
なぜなら、aggregatedテーブルに格納されるレコード数は元のテーブルから十分に絞り込まれているのと、まず確実にインデックスが効くからです。

ちなみに、aggregatedテーブルに順番が異なるだけの2つの複合インデックスを張っている理由ですが、
どちらのインデックスが有効かはデータの分布具合によって異なるため、念のため両方のインデックスを作成しています。

ところで、この集計作業が定例のものであれば、aggregatedテーブルは非テンポラリテーブルとして作成するのが良いでしょう。
前日以前のアクセスデータが変化することはないので、
日次のバッチなどで前日分のデータを集計・格納してやれば、何度でも再利用できます。

Comments

[KAGURA]

ご回答ありがとうございます！

集計に使用しているSQLについてですが、記載した通りのクエリを使用しております。

また、他の詳細なDB回りの情報についても、お伝えしたいのですが
手元に開発環境がなく、かつ開発環境を見れる場所ではこういった場にアクセス出来ないので
強引に突破しようとすると何かしらコンプライアンスに抵触する恐れがあり
問題にならなさそうな範囲で情報公開し、有益な情報を得ようとしております。。。

クエリのチューニングについては詳しくないので、自分で調べながら
実行計画を取得してみましたが、indexは全く使われておらず
100万件のレコードに対するフル検索が行われているようでした。
試しにuser_idやaccess_dateにINDEXを張ってみたものの、そのINDEXが使われている様子もなく頭を抱えていた状態でございます。

該当の処理は、集計画面を開こうとした時に動くものなのですが
集計処理に時間がかかりすぎて、画面を開くことができないため対策を行っております。
定例処理ではありませんが、他の処理にも使えそうですのでこの集計テーブルは作っておいた方が良さそうだと感じました。
現状、こういった集計用テーブルは存在していなかった気がします。。。

Answer 2

もう試されているかもしれませんが…

SELECT user_id,access_date FROM table
WHERE access_date BETWEEN "初日" AND "末日"
GROUP BY user_id,access_date;

の結果をテンポラリに作ってそれに対して select かけるのはどうでしょう。
access_date は年月日の形式にするのがいいのかな?

Comments

[KAGURA]

ご回答ありがとうございます！

確かにdate(access_date)のような形にして、このクエリからテンポラリを作れば
集計対象のテーブル件数自体はだいぶ減らせそうな気がします！

あとは、件数がどれだけ減るか・・・と
それによってどの程度パフォーマンスが改善するかですね。

まだ、このような形でテンポラリを作ることは試してなかったので
挑戦してみます！

Answer 3

FROMからTOまでのdistinctなuser_idの累計ですので

下記のSelect文の結果を元にして、
PHPで集計してはどうでしょう？

select user_id,min(access_date)
from table
where access_date between "FROM" and "TO"
group by user_id
order by min(access_date)

Answer 4

こんばんは、この時間は回答者が少ないと思うので勝手に答えますｗ

DBには、詳しくありませんので期待しないでください。
他の素晴らしい回答者が現れるまでのつなぎ程度です。

えと、まずSQLのDistinctは、コストが高いと思われます。

適当に検索してみたらDISTINCTの代りにEXISTSを使う なんて記事が見つかったので、調べてみてはいかがですか。

それと

1回目：2016/1/1～2016/1/2

2回目：2016/1/1～2016/1/3

と期間が重なって集計しているようですが、差分のみ取得して計算することはできないのかなと思いました。
もしくはFrom〜Toの型が分かりませんが、日時型じゃなくて日付型だったとしたら日付でDistinctoするのもよいのかもと思いました。

あとは、システムが既に動いているとかであれば厳しいかも知れませんが、ちょっと考え方を変えて登録の際にユニークなユーザなのか調べていなければ登録しておくという風にコストを分散しておけば、
集計は一瞬で出来るのではないかとか勝手に思いました。
机上の空論かも知れません^_^；

Comments

[KAGURA]

ご回答ありがとうございます！

確かにデータを保持する時点でゴニョゴニョできれば楽ですね。
現実はなかなか。。。

日毎の集計であれば、1度のクエリで「その日の一意なアクセス数」を取得できたのですが
その日ごとのアクセス数を合算してしまうと、同じユーザーが日をまたいでアクセスした情報が重複して合算されるため数がズレてしまうのです。。。
（一度、これを試して数字が合わずハマりました。。。）

例えばAさん、Bさんが1/1と1/2にそれぞれ数回アクセスしたとして
■日毎の集計
1/1-2件
1/2-2件

■欲しい情報
1/1～1/2 - 2件（この2日間でAさんとBさんの2人しかアクセスしていないため）

説明が下手で申し訳ありません。。。

[Mr_Roboto]

なるほど、いろいろと条件があるようですね。

ちょっとカウントの仕方が理解できませんが、改めて質問を編集してもう少し情報を出せば、的確な回答が得られると思います ^^