SQLの実行計画を取得してみよう！基本的な考え方を解説！

SQLでSELECT文やCREATE文など、基本的な操作は出来るようになったけれども、実行計画についてはイマイチ理解出来ていないという方も多いようです。

SQLのパフォーマンスを上げるためには、実行計画の理解は欠かせません。

今回は、SQLの基本が理解出来た初心者の方向けに、パフォーマンス・チューニングに欠かせない実行計画について基本的な情報と使い方をご紹介していきたいと思います。

ポテパンダの一言メモ

本記事の内容はMySQLデータベースを利用して確認を行っています。
データベース毎に利用可能な機能や特性が異なりますので、ご利用になられるデータベースの仕様を確認した上で実践してみてください。

SQLの実行計画とは

SQLの実行計画は名前の通り、SQLを実行するための計画つまり手順書のようなものです。

データベースが判断したパフォーマンスの高いSQLの実行方法を具体的に確認することが可能です。

SQLではどのデータを取得するか決めることが出来ますが、どのようにデータを取得するか最終的な組み立てはデータベースのオプティマイザという機能が決定します。

SQLの実行計画を確認する意味とは

データベースが判断したSQLの実行計画が、必ずしも最適な実行方法とは限りません。

エンジニアが実行計画を確認し、自分自身が意図した通りの実行パフォーマンスを出せるように、SQLをチューニングする目的で確認されます。

例えば、テーブルの全行にアクセスして該当するデータが存在するのかを確認する「フルスキャン」、テーブルに定義されたインデックスを利用した「インデックス検索」のどちらが利用されているかを確認する際に利用出来ます。

SQLの実行計画を取得する

SQLの実行計画を取得するには「EXPLAIN」ステートメントを利用することで確認することが可能です。

EXPLANの記述方法

EXPLAINを記述して実行計画を確認するには、SELECT文の最初にEXPLAINと追記するだけです。

EXPLAIN
SELECT カラム名[, カラム名, ...]
FROM テーブル名
[WHERE 条件式];

サンプル

では実際にサンプルのSQLを実行してどのような表示となるのかを確認してみましょう。

サンプルテーブル作成

まず「explain_sample」という名前の「id」と「name」2カラムだけが存在するシンプルなテーブルを作成します。

create table explain_sample(id int not null auto_increment, name varchar(30), primary key(id));

作成したサンプルテーブルにダミーデータを約600件投入しました。

mysql> select count(*) from explain_sample;
+----------+
| count(*) |
+----------+
|      625 |
+----------+
1 row in set (0.00 sec)

実行計画取得

では上記で作成したテーブルに対して実行計画を取得してみましょう。

mysql> explain select count(*) from explain_sample;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | explain_sample | NULL       | index | NULL          | PRIMARY | 4       | NULL |  625 |   100.00 | Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

SQLの実行計画で確認するポイント

上記サンプルのようにSQLで実行計画を取得すると複数項目表示されるため、どの情報を見ればよいのか慣れていない方には難しい問題です。

今回は最初に着目しておきたい重要項目をいくつかピックアップしてご紹介していきます。

type

実行計画においてまず最初に確認すべき重要な項目が「type」です。

type項目は、テーブルに対する行検索の方法が表示される項目です。

特に「ALL」と「index」が表示されることが多いのですが、indexをテーブルに設定しているにも関わらず、ALLが表示されている場合には、indexが活用出来ていないことになりますので、SQLを見直す必要があります。

また「index」と表示されている場合でも、サンプルのようなカバリングインデックス(Extra項目にUsing indexと表示)なのか、その他のインデックス検索なのかによっても検索方法が異なります。

あくまで一例としてご紹介していますが、SQLのスピードを改善するためには開発者が想定する通りの検索方法となるようなSQLチューニングが必要です。

ポテパンダの一言メモ

データベースのオプティマイザは非常に優秀ですので、全ての発行SQLに対して実行計画を取得する必要は基本的にありません。
スピードが遅いなと感じる処理があれば、実行計画を取得し検索方法を見直すことでパフォーマンスチューニングを行います。

possible_keys

possible_keysの項目は、利用可能なインデックス候補が表示されます。

上記サンプルでは、インデックスの追加を行っていないため、「NULL」と表示されていますが、テーブルにインデックスを設定しておけば、この項目に表示されることになります。

possible_keysの項目に、テーブルに設定したインデックスが表示されていない場合には、実行計画に反映されることはありませんので、テーブル定義やSQLの見直しが必要です。

Extra

Extra項目は、「その他」項目として利用されますが、意外と大事な情報が記述されている項目でもあります。

上述したように、「index」項目と「Extra」項目の情報を合わせて、検索方法の詳細が確認出来るなど、実行計画を取得する上では、必ずチェックすべき項目と言えます。

各種実行計画の項目で狙い通りの値が設定されているのに、思ったようなパフォーマンスが出ない場合には、Extra項目の情報と合わせて詳細内容を確認するようにしましょう。

さいごに： SQLの実行計画はパフォーマンス・チューニングに必須

本記事では、SQLの実行計画取得方法について、MySQLデータベースをサンプルとしながらご紹介してきました。

今回ご紹介した内容は基礎的な部分のみで、実際に実行計画からパフォーマンス・チューニングを行うにはある程度の慣れが必要となってきます。

テーブルから正しいデータさえ取得出来ていれば、最低限はOKといえますが、ある程度の規模のシステムや開発者としてステップアップするためには、SQLのパフォーマンス改善が出来ることは必須です。

少しずつSQLの実行計画を見ながら、ご自身の作成したSQLのパフォーマンスが問題ないか確認出来るようにチャレンジしていきましょう。