【MySQL】実行計画を見直して検索速度を上げる。EXPLAINの使い方について解説。
  • facebookページ
  • twitterページ
  • 2019.12.31

    【MySQL】実行計画を見直して検索速度を上げる。EXPLAINの使い方について解説。

    データベースのチューニングをきちんと行うことで、データベースの処理速度を格段に向上させることが出来ます。

    このチューニングでもっとも簡単な方法は”EXPLAIN”を使う方法だと言えます。

    そこで今回はこの”EXPLAIN”について解説をしてみたいと思います。

    データベースチューニングの鉄則

    これは簡単に言ってしまえば「インデックス」を貼ることが挙げられます。

    但し必要以上にインデックスを貼ると返って処理性能が落ちてしまいますので、適切なカラムを見極める必要が有ります。

    ではどうやって見極めれば良いのか?

    これはもう慣れないうちは何度も何度も試行錯誤を繰り返すしかありませんが、その労力を圧倒的に減らしてくれるクエリこそが”EXPLAIN”です。

    EXPLAINステータス

    EXPLAINは、あくまでも現状の実行計画がどのようになっているかを教えてくれるだけの機能です。

    ですから、その読み解き方をわかっていないと何も始まりません。

    この項目では各ステータスについて解説していきたいと思います。

    EXPLAINのステータス
    select_type
    クエリの種類を表す

    • SIMPLE
      単純なSELECT (UNIONやサブクエリを使用しない)
    • SUBQUERY
      サブクエリ内の第一SELECT
    • DERIVED
      派生テーブルSELECT (FROM節内のサブクエリ)
    • PRIMARY
      最外部のSELECT
    • UNION
      内の第2およびそれ以降のSELECTステートメント
    • UNION_RESULT
      UNIONの結果
    table
    結果を得るために参照するテーブル
    type
    結合型 – 各結合型を最適なものから順に紹介する

    • ALL
      フルテーブルスキャン
    • index
      インデックスツリーのみがスキャンされる
    • range
      インデックスを利用した範囲検索
    • ref
      UNIQUEや PRIMARY KEYではないインデックスを利用した等価検索
    • eq_ref
      インデックスが UNIQUEまたは PRIMARY KEYである場合に使用される
    • const
      PRIMARY KEY/UNIQUEキーを定数と比較する場合に使用される
    possible_keys
    利用可能なインデックスの候補
    key
    MySQL が実際に使用を決定したキー
    key_len
    MySQL が実際に使用を決定したキーの長さ
    ref
    keyとともに使用されるカラムまたは定数
    rows
    クエリの実行に際して調べる必要があると MySQL によって判定されたレコードの数
    Extra
    上記以外の特記事項

    EXPLAINで最適化する

    まずは下の例を見てください。

    このテーブルには次の様なデータが1万件ほど入っています。

    また”show columns from sample_table_a”で表示されている部分を見てわかるように、まだインデックスは貼っていません。

    まずはこの状態での実行計画を見てみましょう。

    “EXPLAIN”の使い方は、クエリの先頭につけるだけです。

    クエリを実行すると実行計画が表示されましたが、この中で最も注視すべきは「type」と「possible_keys」です。

    上の結果では「type」がALLで「possible_keys」NULLになっています。

    これは登録されたレコードを全て検索しているという意味なので、非常に効率が落ちていることを示しています。

    では次に、先ほどのテーブル構成にインデックスを付けて再度EXPLAINを行ってみましょう。

    今度は”type”カラムにインデックスを貼って同じようにEXPLAINを実行してみました。

    “type”や”possible_keys”の他にも”key”、”key_len”、”ref”など色々変わった部分が増えましたね。

    “Exrta”の項目でもきちんとインデックスを使って検索されたことが明記されています。

    検索数も10176行→3364行に減っていますね。

    1万件程度のデータでは大差ないかもしれませんが、これが10万件、100万件といったようなビッグデータになってくると顕著に差が出てきます。

    EXPLAINについてはもっと具体的な最適化方法や見方がありますが、初心者の方も最低限、”type”と”possible_keys”、もう少し欲張るなら”Extra”は見るようにしましょう。

    まとめ

    いかがでしたか?

    今回はEXPLAINの必要性と見方について解説を行いました。

    データベースチューニングの方法をあまり理解していなくてもEXPLAINでの解釈さえ分かっていれば、ある程度は最適化できます。

    初心者から抜け出す第一歩として、この使い方をマスターできるよう色々試してみましょう。



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

    この記事をシェア

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









    ABOUT US

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

    READ MORE