質問: 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つのクエリでまとめて取得できたり 何か他の手段にて、もっと早く必要な情報を取得する方法は無いでしょうか?
回答: 念のため確認させていただきますが、集計のために使用している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`テーブルは非テンポラリテーブルとして作成するのが良いでしょう。 前日以前のアクセスデータが変化することはないので、 日次のバッチなどで前日分のデータを集計・格納してやれば、何度でも再利用できます。