SQLでJoin句をつなげるとQueryに10分くらいかかってしまう。

Question

困りごと

下表のようにbaseball.pitchテーブルにbaseball.playerの情報をjoinしようと思っています。joinにあたっては、投手などのIDだけでは一意には決まらず、年度とIDとの組み合わせでjoinの組み合わせ相手が一意に決まる形式になっています。

以下のようにjoin区を二つつなげて実行したのですが、実行に10分程度かかってしまいます。一方joinを1つだけにした場合、1,2秒で終了します。

解決したいこと

本例のように、joinを複数実施する時に遅くしないようにどうしたらいいでしょうか？
また、Primary Key設定はしていません(こういった場合に設定したら早くなるのかがわからないため)。

テーブル概要

baseball.pitch (70万レコード)

年度	投手ID	打者ID	投げた球の種類	時刻	アウトカウント
2017	100	102	ストレート	19:00	0
2017	100	102	ストレート	19:01	0
2017	100	102	スライダー	19:01	0
2017	101	100	カーブ	19:03	1
、、、	、、、	、、、	、、、	、、、
2018	100	102	カーブ	20:00	0
2018	100	102	カーブ	20:00	0
2018	100	102	カーブ	20:02	0
、、、	、、、	、、、	、、、	、、、	、、、

baseball.player (3000レコード)

年度	選手ID	身長	年俸	氏名	生年月日
2017	100	180	20000	A	1990/1/1
2017	101	175	10000	B	1989/3/1
2017	102	170	8000	C	1991/5/1
2018	100	180	15000	A	1982/1/1
、、、	、、、	、、、	、、、	、、、	、、、

クエリー１（baseball.pitchに集約）

explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
from baseball.pitch as p
         join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
              on p.年度 = 投手テーブル.年度
              and p.投手ID =  投手テーブル.選手ID
         join (select 年度, 選手ID, 身長, 年俸 from baseball.player) as 打者テーブル
              on p.年度 = 打者テーブル.年度
              and p.打者ID = 打者テーブル.選手ID
limit 10000;

Explainで実行計画結果（クエリー１）。

Indexを貼らない場合

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | player | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   2704 |   100.00 | NULL                                               |
|  1 | SIMPLE      | player | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   2704 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | p      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 771848 |     0.10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Indexを貼った場合

+----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref                                   | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
|  1 | SIMPLE      | p      | NULL       | ALL  | id_year_index | NULL          | NULL    | NULL                                  | 771848 |   100.00 | Using where |
|  1 | SIMPLE      | player | NULL       | ref  | id_year_index | id_year_index | 10      | baseball.p.年度,baseball.p.投手ID     |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | player | NULL       | ref  | id_year_index | id_year_index | 10      | baseball.p.年度,baseball.p.打者ID     |      1 |   100.00 | NULL        |
+----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

Warning内容

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `baseball`.`p`.`年度` AS `年度`,`baseball`.`player`.`年俸` AS `投手年俸`,`baseball`.`player`.`身長` AS `打者身長` from `baseball`.`pitch` `p` join `baseball`.aseball`.`player` where ((`baseball`.`player`.`選手ID` = `baseball`.`p`.`投手ID`) and (`baseball`.`player`.`選手ID` = `baseball`.`p`.`打者ID`) and (`baseball`.`player`.`年度` = `baseball`.`p`.`年度eball`.`player`.`年度` = `baseball`.`p`.`年度`)) limit 10000                                 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

クエリー2（baseball.playerに集約）

explain select * from baseball.player as 選手
    left join baseball.pitch as 投手
        on 選手.年度 = 投手.年度
        and 選手.選手ID = 投手.投手ID
    left join baseball.pitch as 打者
        on 選手.年度 = 打者.年度
        and 選手.選手ID = 打者.打者ID;

Explainで実行計画結果（クエリー２）。

Indexを貼った場合

+----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys                     | key                 | key_len | ref                                             | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
|  1 | SIMPLE      | 選手   | NULL       | ALL  | NULL                              | NULL                | NULL    | NULL                                            | 2704 |   100.00 | NULL  |
|  1 | SIMPLE      | 投手   | NULL       | ref  | id_year_index,id_year_index_toshu | id_year_index_toshu | 10      | baseball.選手.年度,baseball.選手.選手ID         | 1002 |   100.00 | NULL  |
|  1 | SIMPLE      | 打者   | NULL       | ref  | id_year_index,id_year_index_toshu | id_year_index       | 10      | baseball.選手.年度,baseball.選手.選手ID         |  734 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

Indexを貼らない場合

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | 選手   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   2704 |   100.00 | NULL                                               |
|  1 | SIMPLE      | 投手   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 771848 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | 打者   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 771848 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Comments

[sazi]

インデックスが無い状態ならインデックスを適切に作成すれば早くはなります。
だけど、その為には、正確なテーブルの構造と、実際に投入しているSQLが無いとアドバイスできません。
pitch テーブルの役割は何ですか？
投手と打者を識別するだけには思えないのですが？
SQLも実際とは違うように見えます

[MagMag]

データの概要と目的を述べると、baseball.pitchには野球試合で投げたボールの全球種（ストレートなど）と、関する情報（時刻、相互の得点、投手/打者/捕手などのプレイヤーID、何アウト何ストライクの時のボールか等）が入っています。最終的には、これらの情報から、次に投手が何の球種を投げるか、という予測を行っています。

プレイヤー情報はIDで紐付けされているので投手や打者の年俸など、予測に寄与しそうな変数をSQLを使って取ってこようと思っています。

なお、データの定義は以下のリンクあります（train_player.csvがbaseball.playerに、train_pitch.csvがbaseball.pitchに対応しています）。
https://signate.jp/competitions/275/data

ただ、ここらへんを詳しく書くと、情報が多いと思い、単純化して質問させていただいた次第です。かえって混乱させてしまったのですが、、、。

[sazi]

会員登録しないと駄目な場所を参照先にしないで下さい。

playerの組み合わせがpitchだと思いますが、どのような組み合わせなのかが分かりません。
pitchを参照するのはその組み合わせの情報を取得しようとしてだと思いますが、そこが分かればもっと簡潔に出来るのではないかと思います。

[MagMag]

あ、申し訳ありません。気が付きませんでした。本文を編集します。

[MagMag]

実際はpitchには試合で投げたボールの種類が入っており、その時の打者や投手などがプレイヤIDに正規化されて格納されています。

[sazi]

実行計画だけでは駄目ですよ。その際のSQLもセットです。

[MagMag]

ありがとうございます。表の上の方にクエリを記載していたのですが、Explainの上に移動しました。

[sazi]

私へのベストアンサーはどういう意味でしょう？
てっきりSQLを変更したのだと思ってましたが。
状況を整理して下さい。

Answer 1

MySQL SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう の結果を質問に追記しては？

         join (select 年度, 年俸, 選手ID from baseball.player) as 投
手テーブル

とすることでインラインビューで無駄に全件読み込んでからその下の on の条件で絞り込みます。
ですから、

select データ内連番, p.年度, 年俸 as 投手年俸, 身長 as 打者身長
from baseball.pitch as p
INNER join baseball.player as 投手テーブル
on p.年度 = 投手テーブル.年度
and p.投手ID =  投手テーブル.選手ID
INNER join from baseball.player as 打者テーブル
on p.年度 = 打者テーブル.年度
and p.打者ID = 打者テーブル.選手ID
limit 10000;

baseball.pitch に
年度, 投手ID
年度, 打者ID
の2つのインデックスが欲しいです。

baseball.player に
年度, 選手ID
のインデックスが欲しいです。

質問にCREATE TABLE, CREATE INDEX も提示できた方が適切なコメントが付き易いです。

Comments

[sazi]

低評価されちゃったみたいなので、コメントしておきます。
※低評価する人はホント、理由書いてほしい

先ず、回答したSQLでは、pitch のインデックスは、使用されたとしても一つだけです。
次に、質問でのインラインビューですけど、条件がありませんので、インラインビューが理由で全表を読み込むことになるとは考えにくいです。

というところが低評価の理由として推測されます。

[Orlofsky]

質問者が実行計画を載せてもらった後になりますが、pitch テーブルを投手用と打者用に分ける方法もあります。Oracleのようにヒント文で使用するインデックスを指定できると良いのですが。
バッチをチューニングして処理時間を半分以下に改善するとユーザーには必ず感謝されます。10案件に1件くらい、そのシステムの担当者がチューニング方法の説明を受ける事を拒否して嫌がらせをしてくることがありあす。
この掲示板に参加する目的が技術の向上ではなく、嫌がらせになっている人がいるようです。

[sazi]

低評価が嫌がらせなのかどうか分かりませんが、少なくとも、回答のSQLに対して提案された、pitch テーブルのインデックスは適切ではありませんよ。
それはoracleでも同じだと思いますけど。

[sousuke]

「INNER join baseball.player」でいいのはほぼ間違いないんだけど
そもそもこのSQLで何を得てるのかわからない…。質問文のSQLでも思うんだけど「年俸」って両方にあるからSQL実行できなさそうなんだけどMySQLってイケるの？
※sazi氏「INNER join from baseball.player」ってところあるから…低評価それが原因？わかるから私はまったく気にしないけど揚げ足取り的なのもあるかもね笑

[sazi]

@sousukeさん
成程、そんなところもありましたか。
> このSQLで何を得てるのかわからない
「追記・修正依頼」でコメントしているところです。
多分、インラインビューは実際とは違い、簡略化の際に誤った記述になっていると推測していますけど、pitch の目的がさっぱり分からない。

[Orlofsky]

質問者の環境でどの程度改善されたか情報が欲しいところです。

[MagMag]

みなさま、回答ありがとうございます。
playerテーブルの(年度, 選手ID)にインデックスを貼ったところ、実行時間が半分になりました。

ただ、自分でも理由がわからないのが、もともと8〜10分くらいかかっていたクエリーを再度実行したところ（インデックスを貼らずに）、数秒で済んだことです。前回のSQL実行時には別のSQLプロセスは走っていないですし、データベースやクエリーは前回から変えていません（PyCharmのDatabase機能を使って実行しています）。また、CPU負荷を意図的に高くしても時間は変わりませんでした（だんだん最適化されるものなのでしょうか？）。それでも、インデックスを貼ることで4→2秒と削減されましたので、今後はインデックスを貼って対応をしたいと思います。

なお、打者年俸と投手年俸と、同じplayerテーブルから抽出することも可能でした。

[sazi]

＞もともと8〜10分くらいかかっていたクエリーを再度実行したところ（インデックスを貼らずに）、数秒で済んだことです。

同じ問合せならキャッシュが使用されるので、殆ど時間は掛かりません。

[MagMag]

ありがとうございます。改善時間をここで報告するためにRESET QUERY CACHEを試してみましたが、Errorが返ってきました。調べるとMySQL8.0からはクエリーキャッシュが廃止されたとのことです（認識が違ったらすみません）。

[MagMag]

なぜかクエリーが遅くなったので、クエリーを発行してみました。
インデックスなしで11分から、インデックスあり61msに削減されました。
（インデックスのおかげで早くなっているのか、別の原因なのかの切り分けがはっきりしませんが、、、）

[Orlofsky]

インデックスを追加する前と後の両方のEXPLAIN を
https://teratail.com/help/question-tips#questionTips3-7 の [コード]
追加してください。

[MagMag]

追加してみました。

[Orlofsky]

もう一度書きます。
https://teratail.com/help/question-tips#questionTips3-7 の [コード]
追加してください。
今の[表]で載せられても見にくくてダメです。

[MagMag]

失礼しました。修正しました。

最初、コードでの記述は試したのですが、 今のIDEでクエリ結果をエクスポートするとMarkdownの表形式がデフォルトになっており、コードモードでペーストしたら見れたものじゃないので、Markdownの表で貼り付けた次第です。Terminalで実行し、その結果をコードで貼るべき、という理解に到るのに時間がかかりました、、、。初心者なので、指摘いただき助かります。

[MagMag]

相変わらず実行時間は早い時も遅い時もあります。前述のとおり、他にSQLは動かしていませんし、CPUやメモリに高い負荷はかけていません（それなりにスペックのよいPCを使っています）。PyCharmのDatabaseコンソールを使用してクエリを叩いているので、もしかしたらそのせいかもしれません。

[sazi]

select の結果をcount(*)にしてみてください。
select count(*) from ～
こうすれば、DBツールなどの結果の描画による影響を押さえる事ができます。

尚、後から追記された実行計画の結果を見ると、インデックスも使用されていますし、アクセスする行も適切ですので、特に問題は無いと思います。

[MagMag]

ありがとうございます。
count(*)にしたところ、クエリー2でインデックスなしで20分以上経っても結果が出ず、インデックスを貼ったら4秒で済みました。なぜ時間が変動するのかはわからなかったですが、インデックスやExplainの使い方に多少慣れました。助かりました。

Answer 2

join区を二つつなげて実行したのですが、実行に10分程度かかってしまいます。一方joinを1つだけにした場合、1,2秒で終了します。

一方のみのjoin で1,2秒が担保できるのなら、一方のみでjoinしたものをさらにjoinすると、倍程度の時間では済むかもしれません。

ただ、適切なインデックスと適切なSQLなら、秒は掛からないと思いますけどね。

追記

駆動表が逆ですね。

select *
from  baseball.player as 選手
      left join baseball.pitch as 投手
      on    選手.年度   = 投手.年度
        and 選手.選手ID = 投手.投手ID
      left join baseball.pitch as 打者
      on    選手.年度   = 打者.年度
        and 選手.選手ID = 打者.打者ID

上記であれば、以下のインデックスを準備すると改善されると思います。
player：(年度,選手ID)
pitch ：(年度,投手ID), (年度,打者ID)

select項目は、caseや**coalesce()**を活用すれば簡潔になるでしょう。