データベースのチューニングをきちんと行うことで、データベースの処理速度を格段に向上させることが出来ます。
このチューニングでもっとも簡単な方法は”EXPLAIN”を使う方法だと言えます。
そこで今回はこの”EXPLAIN”について解説をしてみたいと思います。
データベースチューニングの鉄則
これは簡単に言ってしまえば「インデックス」を貼ることが挙げられます。
但し必要以上にインデックスを貼ると返って処理性能が落ちてしまいますので、適切なカラムを見極める必要が有ります。
ではどうやって見極めれば良いのか？
これはもう慣れないうちは何度も何度も試行錯誤を繰り返すしかありませんが、その労力を圧倒的に減らしてくれるクエリこそが”EXPLAIN”です。
EXPLAINステータス
EXPLAINは、あくまでも現状の実行計画がどのようになっているかを教えてくれるだけの機能です。
ですから、その読み解き方をわかっていないと何も始まりません。
この項目では各ステータスについて解説していきたいと思います。
- select_type
- クエリの種類を表す
-
- SIMPLE
- 単純なSELECT (UNIONやサブクエリを使用しない)
-
- SUBQUERY
- サブクエリ内の第一SELECT
-
- DERIVED
- 派生テーブルSELECT （FROM節内のサブクエリ)
-
- PRIMARY
- 最外部のSELECT
-
- UNION
- 内の第２およびそれ以降の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で最適化する
まずは下の例を見てください。
SHOW COLUMNS FROM sample_table_a; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | hashA | varchar(10) | NO | | NULL | | | type | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+
このテーブルには次の様なデータが1万件ほど入っています。
+----+------------+------------+ | id | hashA | type | +----+------------+------------+ | 1 | 288e6e38cf | fish | | 2 | a4cb792b7a | fish | | 3 | cf2167b2e6 | fish | | 4 | b428d7094b | fish | | 5 | ba8d7acd50 | fish | | 6 | 92bcabf019 | vegetables | | 7 | 0138fa000f | vegetables | | 8 | 3bb91370f7 | vegetables | | 9 | 598d1cb0dc | meat | ・・・ ・・・ ・・・
また”show columns from sample_table_a”で表示されている部分を見てわかるように、まだインデックスは貼っていません。
まずはこの状態での実行計画を見てみましょう。
“EXPLAIN”の使い方は、クエリの先頭につけるだけです。
EXPLAIN SELECT * FROM sample_table_a WHERE type = "fish"; +------+-------------+----------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | sample_table_a | ALL | NULL | NULL | NULL | NULL | 10176 | Using where | +------+-------------+----------------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.000 sec)
クエリを実行すると実行計画が表示されましたが、この中で最も注視すべきは「type」と「possible_keys」です。
上の結果では「type」がALLで「possible_keys」NULLになっています。
これは登録されたレコードを全て検索しているという意味なので、非常に効率が落ちていることを示しています。
では次に、先ほどのテーブル構成にインデックスを付けて再度EXPLAINを行ってみましょう。
EXPLAIN SELECT * FROM sample_table_a WHERE type = "fish"; +------+-------------+----------------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | sample_table_a | ref | type | type | 32 | const | 3364 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.000 sec)
今度は”type”カラムにインデックスを貼って同じようにEXPLAINを実行してみました。
“type”や”possible_keys”の他にも”key”、”key_len”、”ref”など色々変わった部分が増えましたね。
“Exrta”の項目でもきちんとインデックスを使って検索されたことが明記されています。
検索数も10176行→3364行に減っていますね。
1万件程度のデータでは大差ないかもしれませんが、これが10万件、100万件といったようなビッグデータになってくると顕著に差が出てきます。
EXPLAINについてはもっと具体的な最適化方法や見方がありますが、初心者の方も最低限、”type”と”possible_keys”、もう少し欲張るなら”Extra”は見るようにしましょう。
まとめ
いかがでしたか？
今回はEXPLAINの必要性と見方について解説を行いました。
データベースチューニングの方法をあまり理解していなくてもEXPLAINでの解釈さえ分かっていれば、ある程度は最適化できます。
初心者から抜け出す第一歩として、この使い方をマスターできるよう色々試してみましょう。
