explainで確認するポイント（SQL速度改善）

はじめに

Mysql（MariaDB）を使っていてSQLの実行が遅い時など、SQLのチューニングが必要になってきた場合にSQLの実行計画をexplainで確認しましょう。

> select * from menu;

というSQLの実行計画を確認する場合、先頭に「explain」を付けます。

> explain select * from menu;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | menu  | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.000 sec)

explain 出力フォーマット

explainが出力できたら読み方を確認しましょう。

カラム	意味
id	SELECT 識別子
select_type	SELECT 型
table	出力行のテーブル
partitions	一致するパーティション
type	結合型
possible_keys	選択可能なインデックス
key	実際に選択されたインデックス
key_len	選択されたキーの長さ
ref	インデックスと比較されるカラム
rows	調査される行の見積もり
filtered	テーブル条件によってフィルタ処理される行の割合
Extra	追加情報
参照：mysql8.0リファレンス

見ておきたいポイント

explainを実行したら結果の中で特に確認しておきたいポイントとして「type」「key」「Extra」の3カラムに注目して見ていきます。

「type」結合型

typeカラムにテーブルの結合方法が出力されます。
注意したい出力は「ALL」です。「ALL」が出力された場合は、そのテーブルの全行が読みだされています。（フルテーブルスキャンが行われています）

意図的に全行を読み出しているのであれば問題ありませんが、WHERE句などでレコードの抽出を行っているのに「type」が「ALL」となった場合、フルテーブルスキャンが行われるコストの高い検索となっている可能性があるため見直しが推奨されます。

「key」実際に選択されたインデックス

インデックスが効いてない場合は「NULL」になります。
インデックスが使われていなかったらインデックスを追加するか、インデックスが使われるようなSQLに変更する必要があります。インデックスが使われていても意図するインデックスが使われていなかったりもする可能性もあるので確認しておきましょう。

インデックスが追加できない（インデックスの追加が不適な構造）などの場合、SQL側で処理をしないでプログラム側に処理を移す事も検討すると良いと思います。

「Extra」追加情報

SQLを改善するヒントが追加情報に含まれますので参考にしましょう。

最後に

最近は SQL ではなくプログラム に処理が移譲される傾向があり、あまり複雑なSQLにする事は多くないと思いますが、紙に印刷するとA4が3枚くらいになる長文のSQLというのもレガシーなシステムに存在したりするので、切れるカードの１つとしてexplainがある事を頭の片隅に入れておくと良いかもしれません。

「explainを確認するポイント（SQL速度改善）」でした。いかがでしたでしょうか。プログラムとSQLの両方からプログラムを改善していきたいと思います。