MYSQL 50万件データが集計できない

Question

MYSQLで年間データを集計するSQLを作っています。

現在、
入庫テーブル：tbl_nyuko 125458件
販売テーブル：tbl_hanbai 365570件

入っているデータが入っているのですが、下記SQLを発行すると２時間たっても結果が表示されません、、
何か遅くなる原因や対策方法など教授して頂ければうれしいです

やりたいのは大分類ごと、得意先ごとの販売金額を集計したいです

tbl_nyukoのインデックス：nyukoday、originalno
tbl_hanbaiのインデックス：sday、serialno、tokuisakino

SELECT t.daino,h.tokuisakino,h.tokuisakiname,sum(h.hanbaisu * n.tanka),h.hday FROM tbl_hanbai h 
 LEFT OUTER JOIN 
 tbl_nyuko n 
 
 ON h.serialno = n.originalno 
 and DATE(h.sday) = DATE(n.nyukoday)
 LEFT OUTER JOIN 
 m_tokuisaki t ON h.tokuisakino = t.tokuisakino 
 
 where h.hday 
 BETWEEN '2021/04/17 00:00:00' 
 AND '2022/03/31 23:59:59' 
 AND t.daino = 0 
 GROUP by t.tokuisakino order by t.daino,t.tokuisakino

EXPLAINを実行してみました。
回答いただいた、DATE(h.sday) = DATE(n.nyukoday)も試しましたが、１時間ほど返ってこなかったので諦めました、、

Comments

[yorozuya]

h.hday にはインデックスはないのですか？

[nukasa]

当該SQLにexplainをつけて、実行計画を表示してみてください。それを質問文に追記してください。

[Ruri5280]

explaiを初めて知りました。
追記しましたので見ていただけるとありがたいです

[nukasa]

LEFT OUTER JOIN なので、2番目に検索して欲しい m_tokuisaki が最初に検索されていますね。索引も使われていない。
ここは、INNER JOIN にするわけにはいかないのですか。LEFT OUTER JOIN で m_tokuisaki に該当データが存在しなかった場合、GROUP by t.tokuisakino で、tokuisakino が NULL のものが集計されるだけですよね。

[takanaweb5]

サンプルデータとそのデータによって得られる予想結果を提示されてはいかがでしょうか？
ご提示の説明文とSQLから第三者が質問者が実現したいことを読み取ることは困難です。

[Orlofsky]

SQLのパフォーマンスの問い合せは質問にSQLで使っているテーブルのCREATE TABLE, CREATE INDEX, EXPLAIN を https://teratail.com/help/question-tips#questionTips37 の [コード] を使って提示してください。画面のキャプチャではなく、エディタで検索するので文字で情報が必要です。

Answer 1

日付で切り捨てしたもの同士で結合しているようですが、その用途用のインデックスが無いから、インデックスを使用した実行計画にはなりません。

用途用のインデックスを作成して下さい。
例えば、`DATE(h.sday) = DATE(n.nyukoday))という条件にするなら、

tbl_nyukoのインデックス:Date(nyukoday)、originalno
tbl_hanbaiのインデックス：DATE(sday)、serialno、tokuisakino

というような所謂関数インデックスが必要です。
生成列を作成して、その項目を含んだインデックスにするのが良いでしょう。
MySQL 5.7で生成カラムを使って関数INDEXを作成する

Comments

[Ruri5280]

ありがとうございます！
試してみます！

Answer 2

• 基本的にleft joinはやめる
• 適切なインデックスを貼る
• 販売単価は販売テーブル側に保持する
• group by処理をした場合、そのキーと集計データ以外の参照はおこなわない

まっとうに設定していれば0.1秒かからない処理じゃないかなとは思いますが・・・

Comments

[Ruri5280]

ありがとうございます！
0.1秒目指します！！！

[sazi]

> 基本的にleft joinはやめる
チューニング目的にこの記事にたどり着く人もいるでしょうから、これはさすがに言葉足らずで不適切。

Answer 3

DATE_FORMAT(h.sday, '%Y%m%d') = DATE_FORMAT(n.nyukoday, '%Y%m%d')

ですが、この処理は、時刻部分を無視して、同じ日付のものを連結したいということですよね？
でしたら、以下にすれば、ある程度改善するかと。

DATE(h.sday) = DATE(n.nyukoday)

Answer 4

tbl_hanbai.hday と m_tokuisaki.daino それぞれにインデックスを付けましょう。

さもなくば、

SELECT t.daino, t.tokuisakino, t.tokuisakiname, SUM(h.hanbaisu * n.tanka), h.hday
 FROM m_tokuisaki t
 LEFT OUTER JOIN tbl_hanbai h ON h.tokuisakino = t.tokuisakino
 LEFT OUTER JOIN tbl_nyuko  n ON h.serialno = n.originalno 
 AND DATE(h.sday) = DATE(n.nyukoday)
 WHERE h.hday BETWEEN '2021/04/17 00:00:00' AND '2022/03/31 23:59:59' 
 AND t.daino = 0 
 GROUP by t.tokuisakino
 ORDER by t.tokuisakino

Answer 5

NESTED LOOP結合によりSQLを改善し３秒実現できました！
ありがとうございました！