SQL Joinサンプル集 Joinで遅いSQLの原因を調べる方法
  • facebookページ
  • twitterページ
  • 2019.08.18

    SQL Joinサンプル集 Joinで遅いSQLの原因を調べる方法

    SQLのjoinのサンプルコードを集めてみました。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    SQLのjoinの基本

    別テーブルの項目を、キーを使って引っ張って来たい、というときに使うのがjoin。

    具体的な例だと、employeesテーブルのfist_name(名前)、last_name(姓)と、salary(年収)をemp_no(社員番号)で紐付けて抽出したい…といったときに使います。

     

    salariesには、各社員の過去の給与情報も含まれているため、最新の給与を抽出するために「salaries.to_date = “9999-01-01″」という条件を追加しています。

    結果は、こうなります。

    結果

    今回の例では、外部結合(アウタージョイン)のLEFT JOINを使用しました。

    続いて、内部結合・外部結合の違いと、LEFT JOIN、RIGHT JOIN、INNER JOINの違いを見て行きましょう。

    SQLの内部結合と外部結合の違い

    参考)SQL Join Type Examples – TrueCodes

    SQLのjoinのパターンは、よくベン図で説明されています。

    テーブルAとテーブルBの結合を、ベン図で表すと全部で8パターン。

    しかし、実際にSQLで使用するのは内部結合(インナージョイン)と、外部結合(アウタージョイン)のLEFTジョインの2つだけです。

    内部結合(インナージョイン)

    指定したキーの値が、テーブルAとテーブルB両方に存在するレコードのみを抽出します。

    もしもsalariesテーブルに給与情報がない社員が居た場合、データが抽出されません。

    外部結合(アウタージョイン)のLEFTジョイン

    LEFT ジョインは、上記のSQLの「inner join」が、「left join」に変わります。

    インナージョインの指定

    LEFTジョインの指定

    LEFT JOINの「LEFT」とは、このベン図を見た時の左側(LEFT)を主体にデータを取得するという意味だったんですね。

    ポテパンダの一言メモ

    LEFT JOINのLEFTは、ベン図を書いたときの左側のテーブルを主体にする、という意味

    この場合、テーブルAを主体にして、テーブルB上にデータがなくても「NULL」としてデータを結合します。

    試しに「employees」テーブルに「YAMADA TARO」という社員データを追加し、salariesテーブルはそのままにします。

    employesテーブルにはデータがあるのに、salariesテーブルにはデータがない状態になります。

    わかりやすくするため、条件に「first_nameがTAROのレコードのみ」という条件を追加しました。

    結果はこうなります。

    結果

    なお、RIGHT JOINは、記述の順番を変えることでLEFT JOINに書き換えることが可能です。

    人によっては、SQLの可読性をあげるためにRIGHT JOINは使わずに、全てLEFT JOINで記述するという人もいるくらいです。

    SQLのJOINが遅い場合の、簡易的なSQLチューニング方法

    「Joinしたテーブルが遅い」という場合、例えばorder byの並び替え項目指定の違いで、速度が大きく変わることがあります。

    結果

    約33万件のクエリの実行にかかった時間は、0.0005秒。

    331603 total, Query took 0.0005 seconds

    order byの指定を以下のように変えて実行してみます。

    331603 total, Query took 0.8373 seconds.

    全く同じ実行結果ですが、かかる時間は0.8373秒になりました。

    比較すると、1674倍の時間がかかっています。

    ポテパンダの一言メモ

    joinしたテーブルに対して、order byの指定の仕方が異なるだけでパフォーマンスが1,000倍以上変わることがある。

    なぜ、ここまで速度が変わるんでしょうか?

    速度低下の原因は、SQLが内部的にどのような処理をおこなっているかを表示させる「explain」で見ることができます。

    explainで見るべき項目

    単純にSQLの最初に「explain」を追加することで、SQLの解析結果を確認できます。

    実行すると、SQL内で使っている3テーブルに対して、どのようなアクセスが発生するのかを解析した結果が表示されます。

    参考)MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

    見るべき項目は6つで、その中でもtype、key、rows、Extraの4項目に注目。

    • employeesテーブルのtype=ALL → テーブルフルスキャンという重い処理が実行されている
    • key=NULL → アクセス時にインデックスが使用されていない
    • rows=299247, filterd=100.00  → 対象データ件数は、299247件×100.00%で、約30万件
    • Extra=Using tempporary、Using filesort  →  一時テーブルが使用されている、ファイルソートが使用されている。

    並び替え指定を「 order by employees.emp_no 」に変更すると、Explainの内容はこのように変わります。

    • employeesテーブルのtype=index → インデックスを使って高速アクセスされている
    • key=PRIMARY → 主キー(PRIMARYキー)インデックスを使用している
    • Extra=NULL → 一時テーブルもファイルソートもおこなっていない

    結果は同じでも、内部的な処理はまったく違っているんですね。

    joinを使ったSQLが遅い場合には、特に件数(rows × filterd )の多いテーブルで、type=ALL(テーブルフルスキャン)のような重い処理が実行されていないかどうかをチェックしてみましょう。

    ポテパンダの一言メモ
    • SQLが遅い場合は、explainである程度遅い原因を確認できる
    • 件数が多いテーブルで重い処理が実行されているときは要注意
    • なるべくインデックスを使って処理されるよう、order byなどの指定を変えてみる


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE