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

Question

次のようなSQL文があります。
こちらのSQLでは、INNER JOINしているテーブル及び、WHERE文で対象としているテーブルは、２つしかありません。
これでしたら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キーはつけております。

宜しくお願い致します。

Comments

[miyahan]

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

Answer 1

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

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

多くの場合、

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

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

Answer 2

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

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

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

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

Answer 3

処理時間以前に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文だけではなく、キー構成とインデックスおよび実行計画の結果も提示して、どこにネックがあるかを質問した方が的確なアドバイスが付くと思います。

Answer 4

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

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

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

Answer 5

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

Answer 6

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