DB設計について（その3）
以前DB設計について書いた記事の続きになります。
実行計画を見よう
テーブル設計時にどんなクエリを発行するかを考えて設計しようという話を書いたと思いますが、実際に想定しているクエリ自体のパフォーマンスが悪ければそもそもの設計を見直す必要があります。
プログラミングと同様に、SQLも同じ結果を得る方法は無数にあるのでできる限りパフォーマンスの良いクエリを使うことが肝要です。
実行計画を確認することでおおよその良し悪しは判断できると思います。
やり方はSQLの前にEXPLAINを付けるだけ。
実行すると様々な項目が表示されますが、その中でも特にチェックすべき項目を以下にまとめたいと思います。
type
typeは対象のテーブルへのアクセス方法を示しています。
| 値 | 説明 |
|---|---|
| const | PRIMAARY KEYやUNIQUE KEYを使用したアクセス・最速 |
| eq_ref | JOINの場合のconst・最速 |
| ref | インデックスを使用したアクセス |
| range | インデックスを使用した範囲検索 |
| index | インデックスをフルスキャン・重い・要改善 |
| ALL | テーブルフルスキャン・重い・要改善 |
簡単にまとめると↑のようになります。
ALLだと遅いのはすぐわかると思います。インデックスも使われず、テーブルをフルスキャンするので遅くなるのは容易にイメージできるはずです。
注意が必要なのはindexです。名前からするとインデックスを使用しているので速いのではと思いがちですがインデックス全体をフルスキャンしているので遅いです。
この2つが出てきたら基本的にはクエリ自体の見直しかインデックスを貼るカラムの再考が必要でしょう。
key
使用したインデックスを示しています。
possible_keysは使用できるインデックスの候補なので実際に使用されたものはkeyの方を見ます。
possible_keysに値があるのにkeyがnullだとインデックスをきちんと使えていないことになります。
こちらもクエリの修正が必要になります。
extra
名前の通り「その他」の情報が入るわけですが、ここに入っている情報の中にはパフォーマンスへの影響が大きいものもあります。
Using filesort
MySQL はソート順で行を取得する方法を見つけるために、追加のパスを実行する必要があります。ソートは、結合型に従ってすべての行を進み、ソートキーと WHERE 句に一致するすべての行について行へのポインタを格納して実行されます。次にキーがソートされ、ソート順で行が取得されます。セクション8.2.1.15「ORDER BY の最適化」を参照してください。
MySQLの場合、ORDER BYしようとするとまずはインデックスを使用できるかどうか試みます。
それができない場合、内部的にはクイックソートが行われます。これが遅くなる原因です。
なのでORDER BYを行う際には必ずそのカラムにインデックスを貼ることが必要です。
ちなみにGROUP BYでも同じです。
デフォルトで、MySQL はすべての GROUP BY col1, col2, ... クエリーを、ORDER BY col1, col2, ... とクエリーに指定したかのように、ソートします。
引用元: MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.15 ORDER BY の最適化
Using temporary
クエリーを解決するために、MySQL は結果を保持する一時テーブルを作成する必要があります。これは一般に、クエリーに、カラムを異なって一覧表示する GROUP BY 句と ORDER BY 句が含まれる場合に発生します。
引用元: MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット
記載されている通り、GROUP BYとORDER BYで異なるカラムを指定する場合に出ることが多いです。
その他にも以下のような場合に一時テーブルが作成される可能性があります。
- UNION クエリーが一時テーブルを使用します。
- TEMPTABLE アルゴリズムを使用して評価されるものや、UNION またはアグリゲーションを使用するものなど、一部のビューで一時テーブルを必要とします。
- ORDER BY 句と別の GROUP BY 句がある場合、または、ORDER BY または GROUP BY に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合は、一時テーブルが作成されます。
- DISTINCT と ORDER BY の組み合わせで、一時テーブルが必要になることがあります。
- SQL_SMALL_RESULT オプションを使用すると、MySQL では、クエリーにディスク上ストレージを必要とする要素 (後述) も含まれていないかぎり、インメモリー一時テーブルが使用されます。
- 複数テーブル UPDATE ステートメント。
- GROUP_CONCAT() または COUNT(DISTINCT) 評価。
- 派生テーブル (FROM 句内のサブクエリー)。
- サブクエリーまたは準結合実体化のために作成されるテーブル。
引用元: MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み
まとめ
以上実行計画についてまとめてみました。
実行計画を見る癖を付けていると明らかに遅いクエリを事前に防ぐことができるので良いと思います。
特にフレームワークやORMを使っていると実際にどんなSQLが発行されるか意識せずともできてしまうので実装前に自分でしっかり想定・確認することが大事だと思います。
