SQLの高速化について。

Question

SQLの高速化について質問です。
とあるselect文でメモリが足りません。。

実現したい事は

最終ポイント獲得日が180日以前だったユーザーを抽出です。
最終的な形としては

Array
(
    [0] => Array
        (
            [id] => 2
            [user_id] => 2
            [point] => 387
            [max_date] => 1535727600
        )

    [1] => Array
        (
            [id] => 5
            [user_id] => 4
            [point] => 1261
            [max_date] => 1535727600
        )

)

このような感じで、配列で取り出せれば理想です。

現在の環境

テーブルは以下の様な感じです。

CREATE TABLE `test_point_history` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `point` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `expire_date` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_point_history` (`id`, `user_id`, `point`, `date`, `expire_date`) VALUES
(1, 1, 1320, 1519830000, NULL),
(2, 2, 387, 1522508400, NULL),
(3, 2, 1716, 1525100400, NULL),
(4, 1, 1717, 1527778800, NULL),
(5, 4, 1261, 1530370800, NULL),
(6, 1, 1304, 1533049200, NULL),
(7, 2, 1031, 1535727600, NULL),
(8, 3, 420, 1538319600, NULL),
(9, 4, 168, 1535727600, NULL),
(10, 1, 1859, 1540998000, NULL),
(11, 1, 1637, 1543590000, NULL),
(12, 1, 1125, 1551366000, NULL),
(13, 3, 219, 1551366000, NULL);

このテーブルには獲得したポイントを履歴に残すテーブルです。
現在やりたい事は実現できているのですが、実データーは1000万件を超えるので、メモリーエラーになってしまいます。
メモリーの上限値を変更する前にSQLを見直ししたいと思います。

現在実行しているSQL

SELECT `id`, `user_id`, `point`, MAX(date) as max_date
FROM `test_point_history`
WHERE `expire_date` IS NULL
GROUP BY `user_id`
HAVING `max_date` <= (UNIX_TIMESTAMP() - 15552000)

以上になります。ご教授宜しくお願いします！

2019/05/21　13：30　追記

配列の時点でメモリーエラーでは？とご意見いただきましたので、SQL文を発行している箇所を追記します。

$this->db = $this->load->database('db_slave', true);

        $this->db->select('user_id,MAX(date) as max_date')->from('point_history_copy');
        $this->db->where("expire_date <=", 0);
        $this->db->group_by('user_id');
        $this->db->having('max_date <= (' . $this->time . ' - 15552000)');
        $array = $this->db->get()->result_array();

        return $array;
//↑returnされる前にメモリーが足りなくてエラーが出ます。

そして現在
php.iniのmemory_limmitは256MBでした。

Comments

[yambejp]

日付をintで持つのはマストなのでしょうか？

[hihijiji]

どこでどのようにメモリー不足になってますか？

[awazon78]

dateの方が速度が速いのですか？

[tanat]

エラーメッセージを追記してください

[ttyp03]

恐らくレコード数が大きすぎて配列化するときにメモリーが足りないのでしょう。SQLよりもそれを呼び出しているコードを提示したほうがよいと思います。タグにその言語も追加してください。

[awazon78]

エラーメッセージは
allowed memory size of　256MB~~ですね。

[awazon78]

php部分追加しました。フレームワークはcodeigniterです

[namda]

DBの種類をタグに追加してください。MySQLなのかOracleDBなのか、PostgreSQLかSQL_ServerなのかSQLiteなのかAccessなのか。

Answer 1

実データが1,000万件でも質問のSQLでメモリーが不足するように思えません。
コンソールでそのSQLを実行してエラーになるでしょうか？
どちらかと言うと、その結果を配列に変換する際に件数が多くて発生しているように思います。

件数に制限を持たせるか、分割して処理するようにすれば良いのではないでしょうか。

Comments

[awazon78]

配列の時点で。。確かにそうかもしれないです！

[sazi]

SQLの高速化というよりエラーを解決したいのですよね？

[sazi]

質問に追記された内容を見ると、配列に格納しようとしてのメモリ不足の様ですね。

Answer 2

user_idでgorup byするのにidやpointは拾えません
mysqlの場合エラーにならないだけで、いつも同じ値が正しく得られるわけではありません。

とりあえずuser_idとdateにまたがった複合インデックスをつけるところから

triggerのsample

CREATE TABLE `test_point_history` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `point` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `expire_date` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `last_point_get`(
`user_id` int primary key,
`date` int
);

drop trigger if exists trg_aft_insert;
delimiter //
create trigger trg_aft_insert_test_point_history after insert on test_point_history
for each row begin 
insert last_point_get(`user_id`,`date`) values
(new.user_id,new.date) on duplicate key update `date`=values(`date`);
end
//
delimiter ;

• データ投入

INSERT INTO `test_point_history` (`id`, `user_id`, `point`, `date`, `expire_date`) VALUES
(1, 1, 1320, 1519830000, NULL),
(2, 2, 387, 1522508400, NULL),
(3, 2, 1716, 1525100400, NULL),
(4, 1, 1717, 1527778800, NULL),
(5, 4, 1261, 1530370800, NULL),
(6, 1, 1304, 1533049200, NULL),
(7, 2, 1031, 1535727600, NULL),
(8, 3, 420, 1538319600, NULL),
(9, 4, 168, 1535727600, NULL),
(10, 1, 1859, 1540998000, NULL),
(11, 1, 1637, 1543590000, NULL),
(12, 1, 1125, 1551366000, NULL),
(13, 3, 219, 1551366000, NULL);

• 結果

SELECT * FROM last_point_get;

user_id	date
1	1551366000
2	1535727600
3	1551366000
4	1535727600

Comments

[awazon78]

この段階では正直pointとidは必要ないのでselect文に入れる必要はありません。言葉不足ですいません。user_idだけ取得できればOKです。

[yambejp]

運用方法を変えるという選択肢があれば相当楽になりますけどありでしょうか？
具体的には、最終ポイント獲得テーブルをつくっておき、
ユーザーがポイントを獲得するたびにtriggerで最新日に更新をしていくことです

[awazon78]

稼働してるアプリになるので、なかなか運用方法までは。。ちょっと難しそうです

[yambejp]

現行のテーブルには影響がない処理なのですでにアプリが可動していても
問題ないはずですが？

この手の抜本的な対策をしない限り都度都度maxを拾っていると
集計に時間がかかるのは諦めるしかないですね

そもそも私が最初に回答した
「user_idとdateにまたがった複合インデックス」
が効いていればそこそこのパフォーマンスだと思うのですが、
なにかおかしいのかもしれないのでexplainで確認して見たほうがよいかもしれません。

Answer 3

CodeIgniter って、たしかログでメモリ結構食ってしまってたはず。
バージョンで違うかもしれないけど、確認してみては？
CodeIgniterが食っていたメモリ

Comments

[awazon78]

なるほど、、参考になりました！ありがとうございます！結構メモリ食うみたいですね

Answer 4

result_array()を実行すると結果を全てPHP配列に変換してしまうので、
結果レコード数が多いSQLに対してresult_array()を呼ぶとメモリを大量に使います。

代わりにunbuffered_row()メソッドを使うやり方があります。
こちらを使えば1行ずつデータ処理できるので、書き方によってはメモリ節約可能です。
(データを全部PHP配列に追加したら同じ事はおきますが、PHP配列には入れずに出力してしまうなど)

https://codeigniter.jp/user_guide/3/database/results.html#id3

ただ、htmlに出力するのであればデータ量として多すぎるので、Freedom1abさんが回答されているようにLIMIT / OFFSETを使ったほうが良いと思います。

Comments

[awazon78]

unbuffered_row()は知らなかったです！勉強になりました！ありがとうございます！

Answer 5

下記のような「LIMIT」句が使えるdbですか？
SELECT col_name1 [, col_name2 ...] FROM table_name
LIMIT [offset,] row_count

LIMIT句が使えて、「その結果を配列に変換する際に件数が多くて発生」が原因なら
LIMIT句を使って回避できると思いますが・・・ページ区切りでLIMIT句を使った
経験しかないので、参考になれば幸いです。

Comments

[awazon78]

使えます!やはりlimitですかね!

[Freedom1ab]

ログをページ毎に表示する時に利用しましたが、
Offsetで飛ばす行数を指定したり、１ページの表示する行数をrow_countで指定して、ページング処理をロジックで書く手間の殆どをLIMIT句で書けて重宝しましたよ。

Answer 6

GROUP BY `user_id`
          ↓
GROUP BY `id`, `user_id`, `point`

では？

Answer 7

test_point_historyのCREATE TABLEを見ると主キーもユニークもインデックスもつけられていないように見受けられますが、まずはそこからではないでしょうか。
expire_dateがnullになっていますが、もしnullばかり（たまにしか入らない）のであればテーブルを分割するなどの手段を考えたほうがいいかもしれません。

• SQLの効率化
• SQLを少しでも高速化するためのチューニング
• 無駄をなくしてSQLを高速化する方法
• なぜ、SQLは重たくなるのか？──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

Comments

[awazon78]

すいません、こちらはテストのテーブルで件数も少ないのでインデックスなど貼り忘れてましたが、実際には貼ってあります。

[m.ts10806]

分かるように追記しておいてください。回答者には出されたものがすべてです。
今回のように質問が丁寧に書いてあってテストデータまで提示されていたら尚更ですね。

[awazon78]

失礼いたしました

[m.ts10806]

いえ、怒ってるわけでもなんでもないですよ。回答の方向性も決まるので追記してくださいとお願いしているだけです