12テーブルをINNER JOINしているSQL文(コードあり)の速度改善をしたいです。LEFT OUTER JOINに変えたりしておりますが、改善につながりません。

解決済

回答 6

投稿

  • 評価
  • クリップ 0
  • VIEW 892

qaz3330

score 111

次のようなSQL文があります。
こちらのSQLでは、INNER JOINしているテーブル及び、WHERE文で対象としているテーブルは、2つしかありません。
これでしたら10秒ほどで、集計が完了します。

しかし、今回の要件上、INNER JOINするテーブル数は、12個あり、
同様にINNER JOINとWHERE文を追加していき実行すると、1時間以上かかってしまいます。

SELECT artists.user_id, artists.staff_id,
Sum(events.sum),\
Sum(goods.sum),\
FROM artists\
INNER JOIN `events` ON `artists`.`id` = `events`.`artist_id`\
INNER JOIN `goods` ON `artists`.`id` = `goods`.`artist_id`\ 
WHERE (`events`.`created_at` BETWEEN '2017-01-01' AND '2017-07-30')\
AND (`goods`.`created_at` BETWEEN '2017-01-01' AND '2017-07-30')\
GROUP BY artists.user_id, artists.staff_id;

SQLの速度改善をしたいのですが、これ以上絞り込む条件がないため、
SQL文の見直しにより、改善できる方法を探しております。

尚、バッチ処理にするという案は今回の要件上NGでした。

生のSQL文で記述しておりますが、実際はRuby on Railsのアプリケーション上で実行します。
その為、非同期処理というのも模索しておりますが、今回、集計した結果をDBにINSERTするため、
非同期処理にすると、本来データが入ってほしいレコードにデータが入らないという恐れを懸念しております。

INNER JOIN を LEFT OUTER JOIN にしたり等、不勉強ながら試行錯誤しておりますが、なかなか改善につながりません。

理想は1~2分で完了してほしいです。

また、この集計処理は、期間を変えて実行することはあっても、同じSQL文を何度も使うということはないので、
クエリキャッシュをするというのもあまり意味がないと思っております。

また、外部キー周りにindexキーはつけております。

宜しくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • miyahan

    2017/07/29 21:16

    実際に遅くなっているクエリと各テーブルのスキーマがわからないとと答えようがないです。

    キャンセル

回答 6

+2

まずやるべきことは、EXPLAINです。

Railsのコンソールから(リレーション).explainでも出ますが、ときどき必要な情報が取れないこともあって、(リレーション).to_sqlとして生成したSQL文を、EXPLAIN (SQL文)のようにしてSQLコンソールから実行しましょう。

多くの場合、

  • インデックスで絞れないテーブルスキャンの発生
  • 結合条件がうまく効かず、爆発的な行数の一時テーブルを生成している

などの問題が見つかるかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

0

最近、似たような形のSQLチューニングの仕事しました。
(同じ人が作ったSQLですか?と思ってしまうくらい似てます。
元々1時間以上かかる処理で、その方も理想は1~2分での完了が希望で、
ダメならバッチ処理を検討してました。)

その仕事では、意図する抽出結果のSQLを作成して、
1秒以内に終わる処理にチューニングしたので、
ちゃんとチューニングしてあげれば、
恐らく数秒程度で終わるSQLになります。

あと、記載されているSQLだと、抽出結果が意図しない結果になる可能性が非常に高いです。
artistsテーブルの1行に対して、
events.sumとgoods.sumがそれぞれ複数存在する場合を考慮していないSQLです。

explainをすると、おそらく「Block Nested Loop」あたりが出てくると思いますが、
まずは、チューニングの前に、意図した抽出結果になるSQLを作ることをお勧めします。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

というか
artists eventsの内部結合
artists goodsの内部結合
の2回クエリを実行すべきで
artists, events, goods, を全部内部結合するのはちょっと違うような?

たぶん別々で集計したほうがいいですよ。

シングルスレッドで順次実行したらどれくらいかかりますか。
また マルチスレッドで集計するのも全然有りです。
全部のスレッドが完了するまで待機する というマルチスレッドの作法に則り
全スレッドが完了したら取得した集計データをInsertすればよいです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

処理時間以前にartist別に発生するeventsやgoodsなどの12のアイテムが必ず発生するとは思えず、innerではなくouterで結合しないとよろしくないのではないでしょうか。

この前提をもとに組み替えてみました

select artists.*, events.item_sum, goods.item_sum
from 
    (select user_id, staff_id from artists group by user_id, staff_id) artists
    left join (
     select user_id, staff_id, sum(item.sum) item_sum
     from artists inner join events as item on artists.id=item.artist_id
     where created_at between '2017-01-01' and '2017-07-30'
     group by user_id, staff_id
    ) events
    on artists.user_id=events.user_id and artists.staff_id=events.staff_id
    left join (
     select user_id, staff_id, sum(item.sum) item_sum
     from artists inner join goods as item on artists.id=item.artist_id
     where created_at between '2017-01-01' and '2017-07-30'
     group by user_id, staff_id
    ) goods
    on artists.user_id=goods.user_id and artists.staff_id=goods.staff_id


キー構成やインデックスの状況が分からないので何とも言えませんが、
少なくともアイテムごとに独立しているので、アイテムの数と処理時間は比例したものになるのではないかと思います。

ここでは以下のインデックスを想定しています。
artists(user_id, staff_id, artist_id)
events や goodsなど(created_at, artist_id)

何れにせよ、sql文だけではなく、キー構成とインデックスおよび実行計画の結果も提示して、どこにネックがあるかを質問した方が的確なアドバイスが付くと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

artistsテーブルのuser_idやstuff_idとidの整合性がとれてないのでは?
すべてinner joinしてから集計しないでも個々に集計してから
くっつけたほうが速いかもしれませんね
具体的なサンプルを提示されないと具体的な高速化については言及できないと思います

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

-1

BETWEEN で指定している created_at にindex設定すればいいのでは?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.21%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • トップ
  • MySQLに関する質問
  • 12テーブルをINNER JOINしているSQL文(コードあり)の速度改善をしたいです。LEFT OUTER JOINに変えたりしておりますが、改善につながりません。