MySqlの実行計画についてまとめてみる
実行計画について
今回、仕事でクエリのチューニングを任されたので実行計画について、最低限の知識を振り返りながらまとめてみる。
まずこういった仕事を任される際に私が最初に確認することは、
- SQL文
- テーブル構造
- クエリの実行時間
- クエリを実行しているソース
- 実行計画
の５点である。
１点目から４点目については詳細は省略するが、まぁ考えてみれば当然のことである。
クエリのチューニングを任される場合のほとんどは、負荷をあげているか時間がかかりすぎている点にある。
そのため、実際にはSQL文だけでなく、テーブルは正規化されているかどうか、実行時間は実際どのくらいなのか、分割するなどして実行の仕方を見直せないか、などなど検討する必要がある。
それにプラスしてDBにあまり明るくない人でも、簡単にそのクエリがどのように実行されているのか確認することができる「実行計画」を確認する。
実行計画の概要
文を実行するためにOracle Databaseが使用するステップの組合せが実行計画です。各処理では、データベースからデータ行を物理的に検索するか、文を発行したユーザーのためにデータ行を準備します。実行計画には、文がアクセスする各表へのアクセス・パスと、適切な結合方法に基づく表の順序(結合順序)が含まれています。 Oracle® Database SQLチューニング・ガイド 引用
つまりは、実行計画とはそのクエリがどのような順序で何をキーを使ってどのように実行されるのかを確認することができる機能である。
もしこれがなければ一つ一つのテーブルのインデックスなどを確認しながら、SQL文と見比べてどのように実行されるのかひもとかなくてはいけないが、これがあれば一発でわかるということである。
MySqlの場合で説明するが、基本的にはどのDBも確認の仕方は大体同じ。
実行計画の確認方法
方法はいたってシンプルで、クエリの先頭にEXPLAINを付け加えるだけである。
mysql> SELECT name FROM user WHERE id="regreg2020regpon"; +----------+ | name | +----------+ | regpon | +----------+ mysql> EXPLAIN SELECT name FROM user WHERE id="regreg2020regpon"; +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | idx_id_only | idx_id_only | 107 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
このように表示される。
それぞれについて重要なところを簡単に説明すると、
| 項目 | 説明 |
|---|---|
| id | 検索に用いるidのid。何をいってるかというと複数のテーブルをまたがって検索する場合、idが複数存在する場合がある。そのidに対して種類ごとに通し番号を振っている。 |
| select_type | SELECT文の種類。詳細は省くがSIMPLEは全て同じidで検索されていることを示す。MySql6.0より前の場合、JOINはNLJ(一つずつ順に処理する方式)しかないためSIMPLEになる。SIMPLEがいいという訳ではないのがミソ |
| table | 検索対象のテーブル |
| partition | クエリで照合されるパーティションで、参照するテーブルについてパーティション化されていない場合はNULLになる。 |
| type | かなり重要なポイント。このテーブルに対してどのように検索するか表示される。実行速度が大体ここでわかる。パターンとしてはconst > eq_ref > ref > range > index > ALLの順で実行が早い。別表にそれぞれをまとめたのでそちらを参照してほしい。(※type) |
| possible_keys | オプティマイザと呼ばれるよしなにクエリを最適化してくれる機能が、テーブルへのアクセスの際に利用可能なインデックスの候補として挙げたキーの一覧 |
| key | possible_keysのうち、実際に用いられたキー |
| key_len | 上記のキーの長さ |
| ref | 検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。前述のSQLで言う所の"regreg2020regpon"に当たる。JOINする場合などは何でJOINしているかがわかる |
| rows | 実際にそのテーブルからフェッチされる行数の見積もり。これは帰ってくる行数ではないことに注意。この行数に対してWHERE句で指定した条件で絞り込みをかけるため、インデックスを使わないクエリの場合はここがテーブルの全レコード数(全件走査)になり、処理が膨大になる。 |
| Extra | 実行計画の生成時に得られる情報で、要はオプティマイザの戦略がここで提言されている。お疲れ、オプティマイザ。 |
※type
| type | 説明 |
|---|---|
| const | PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセスで最速。 |
| eq_ref | JOINにおいてPRIARY KEYまたはUNIQUE KEYを利用したアクセス。上記とはJOINで用いられるところが違う。 |
| ref | インデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセス。 |
| range | インデックスを用いた範囲検索(WHERE id IN (1, 2, 3, 4, 5) )。許容範囲はここまで。 |
| index | フルインデックススキャン。インデックス全体をスキャンするため遅い。 |
| ALL | フルスキャン。インデックスを使用しない検索。発見次第駆逐せよ。 |
今回の場合しっかりとインデックスなどを設定してるので、特に問題のない実行計画となっているが、ぱっと見でやばい実行計画の場合、こうなる。
mysql> EXPLAIN SELECT name FROM user WHERE id="regreg2020regpon"; +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | null | null | 107 | const | 100 | 100.00 | Using Where | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
これの何がやばいかというと、possible_keys, keyがnullでtypeがALLになっていることである。
つまりどういうことかというと、このクエリについて、結果を手に入れる際に使われるインデックスもなければキーもなければ、全件走査され、処理見積もりの行数が１００行もあるのである。
同じクエリでもこのように実行のされ方が変わると結果を得られるまでにかかる時間やDBへの負荷が異なる。
このクエリは簡単なクエリだが、JOINしていたり、サブクエリがある場合などは各テーブルへのクエリの実行方法や実行順序を得られる。
mysql> EXPLAIN SELECT user.nick, user.id, user.status, user.life, user.grade, profile.img_flag, admin_user.is_admin FROM user LEFT OUTER JOIN profile ON user.nick = profile.nick LEFT OUTER JOIN admin_user ON user.nick = admin_user.nick WHERE user.id IN("regreg1","regreg2","regreg3","regreg4");
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | idx_id_only | idx_id_only | 107 | NULL | 4 | 100.00 | Using index condition |
| 1 | SIMPLE | profile | NULL | eq_ref | PRIMARY | PRIMARY | 8 | userdb.user.nick| 1 | 100.00 | NULL |
| 1 | SIMPLE | admin_user | NULL | eq_ref | PRIMARY | PRIMARY | 8 | userdb.user.nick| 1 | 100.00 | NULL |
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-----------------------+
このようにJOINを2回行ってみた。
すると全てselect_typeはSIMPLEでidも一つになっているため、これはJOINの処理(NLJ)になっていることがわかる。
そして、typeをみて見るとuserテーブルに対してはrangeになっていることがわかる。これはWHERE句のuser.id IN("regreg1","regreg2","regreg3","regreg4")のため、インデックスに対して範囲検索が行われている。
他のテーブルはtypeがeq_refなのでインデックスを用いた等価検索がされている。
検索順序はSIMPLEの場合は上から順になるので、userテーブル、profileテーブル、admin_userテーブルとなる。
userデーブルでidを用いて4行に絞り、絞られたレコードについてprofileテーブルのnickをuser.nickをキーに結合し、同様にadmin_userテーブルのnickとuser.nickをキーに結合している。
検索方法については問題がないため、rowsを確認すると、userテーブルは4行(IN句で指定したidの数)、他のテーブルは1行(JOINする際のキーに対しての検索範囲が1行という見積もりとなっている)となっており、最小工数になっていることがわかる。
以上によりこのクエリは問題ないことがわかった。もし、このクエリが遅くなるとしたら、IN句に大量の引数を渡したことによって、rowsが爆増してしまうことくらいにあまり考えられない。
もし、IN句に大量の引数を渡している場合、並列実行などで分割することを検討すると良い。それはSQLというより、プログラム側の修正になる。
以上のようにざっくりと実行計画についてまとめてみたが、これはかなり重要かつ初歩的なことなので、今後ももっと深入りしてチューニングを楽しみたいと思う。
