MySQLのスロークエリを早くしたい

Question

環境

DB: Amazon Aurora（MySQL 5.6互換）
対象テーブルには1億レコード以上あります。

やりたいこと

以下のクエリで月毎の「レコード増加数」と「レコード数の累計」を算出しています。
実行結果は変えず、現状4分半→理想1分以内で結果が返ってくるように改善したいです。
SQLが初めてなので、どこがボトルネックになっているのかも解説していただけると助かります。
よろしくお願いいたします。

SELECT
	t1.accum_date,
	t1.count,
	SUM(t2.count) AS accum
FROM (
	SELECT
		DATE_FORMAT(created, '%Y%m') AS accum_date,
		COUNT(*) AS count
	FROM
		table
	GROUP BY accum_date
	ORDER BY accum_date
) AS t1
JOIN (
	SELECT
		DATE_FORMAT(created, '%Y%m') AS accum_date,
		COUNT(*) AS count
	FROM
		table
	GROUP BY accum_date
	ORDER BY accum_date
) AS t2
ON
	t1.accum_date >= t2.accum_date
GROUP BY 1
ORDER BY 1
;

+------------+----------+-----------+
| accum_date | count    | accum     |
+------------+----------+-----------+
...(中略)...
| 201808     | 10000000 |  85000000 |
| 201809     | 15000000 | 100000000 |
| 201810     | 20000000 | 120000000 |
+------------+----------+-----------+
50 rows in set (4 min 37.47 sec)

追記

EXPLAINの結果です。

+----+-------------+------------+------+---------------+------+---------+------+-----------+----------------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows      | Extra                                              |
+----+-------------+------------+------+---------------+------+---------+------+-----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 120000000 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 120000000 | Using where; Using join buffer (Block Nested Loop) |
|  3 | DERIVED     | table      | ALL  | NULL          | NULL | NULL    | NULL | 120000000 | Using temporary; Using filesort                    |
|  2 | DERIVED     | table      | ALL  | NULL          | NULL | NULL    | NULL | 120000000 | Using temporary; Using filesort                    |
+----+-------------+------------+------+---------------+------+---------+------+-----------+----------------------------------------------------+

Comments

[maisumakun]

EXPLAINの結果も確認できますか？

[hang]

追記しました。よろしくお願いいたします。

Answer 1

まず集計単位が%Y%mだとわかっているならテーブルに専用カラムを用意して
埋め込んでおくとよいでしょう。（適当なインデックスも貼っておく）

また１億件のデータを毎回集計するのはむだなので
日次処理などで過去データは予め集計しておき、
既存の集計済みデータと当日データの集計結果を加算して処理するようにすれば
おそらく数百倍は早くなると思います。

Comments

[hang]

回答ありがとうございます。
カラム変更する権限がないため、今回はこのクエリを早くする方向でお願いいたします><

[yambejp]

テーブルの設計がダメなら何をやっても無駄ですよ
別のサマリーテーブルが作れないならスピードについては諦めるしかないですね。

Answer 2

相関サブクエリーでの累計は試されましたか？

SELECT
    accum_date,
    count,
    (select count(*) from table where DATE_FORMAT(created, '%Y%m') <= t1.accum_date) as accum
FROM (
    SELECT
        DATE_FORMAT(created, '%Y%m') AS accum_date,
        COUNT(*) AS count
    FROM table
    GROUP BY DATE_FORMAT(created, '%Y%m')
) AS t1
ORDER BY 1
;

MySQL5.7ならファンクションインデックスもどきで多少は改善されそうですけどね。

改善策としては前月までのデータは集計済みとして保持しておく事が考えられます。
その際には、createdのインデックスがあるのが吉。

Comments

[hang]

> 相関サブクエリーでの累計は試されましたか？
試していますが、10分以上結果が返ってこないので相関サブクエリーの方が遅いと判断して良さそうです。

> 改善策としては前月までのデータは集計済みとして保持しておく事が考えられます。
> その際には、createdのインデックスがあるのが吉。
なるほどです。
そういう権限がないため、今回はこのクエリを早くする方向でお願いいたします><
（提案してみます！）

[sazi]

分析関数もWITHも使えないし、SQLで何とかするのは無理ぽい気がします。
後は、@変数使う位でしょうけど。

Answer 3

質問にCREATE TABLE, CREATE INDEX と実行計画SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう を提示されては？
実行計画の見かた MySQLのEXPLAINを徹底解説!!

Comments

[hang]

追記しました。よろしくお願いいたします。

[Orlofsky]

CREATE TABLE, CREATE INDEX は載せる気がないですか？

Answer 4

経験に乏しいので参考程度の回答ですが。

どこがボトルネックか調べる方法ですが、(クエリに寄りますが)例えばサブクエリだけ走らせることで、どの時点で一番時間がかかっているかにあたりを付けることができるかと。

あと、Orderbyは最後に一度やればいいのでは？時間かかりそうですし。

また、データ件数が多いので、テーブルそのものの設定(インデックス設定したり)を見直すべきかどうかもわかればベターな気がします。

Comments

[hang]

回答ありがとうございます。
サブクエリは2分強だったので、2本のサブクエリが処理のほどんどを占めているようです。

[退会済みユーザー]

最適化されたりなんだりあると思うので、実際、最終的なクエリにおいてどうかはわかりませんが、参考にはなるのでいいかなと思います。サブクエリのOrderBy外してみたらどうでしょうか。

[hang]

> サブクエリのOrderBy外してみたらどうでしょうか。

ほとんど変わりませんでした（誤差の範囲内かと）。
ありがとうございます。参考になります！

[退会済みユーザー]

あらら、そうでしたか。GroupByなどを外した単純なSelectでやってみて、それぞれ2分かかるなら、絞り込むか(要件にないので無理だとは思いますが)、インデックス貼るかぐらいですね（私が思いつく限界）。単純なSelectで早いようなら、GroupByをJoin後、最後にやるというのも手かもしれません。