SQLのexplainについてまとめています。
SQLのexplainは、実行計画を表示するSQLコード
explainは、SQLの実行計画を表示します。SQL文の先頭に「explain」をつけて実行します。
mysql> explain select * from employees; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299246 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
確認すべき箇所は、大きく2箇所、typeとrowsです。type=ALLの箇所は、全件検索をおこなっていることになります。rowsは影響するデータ件数です。
type=ALLで、rowsの値が大きい箇所を対策することで、効率よくSQLの効率化が可能です。
例えば、上記の例では、299246件の検索をインデックスなしで実行していることになります。このSQLの場合は、どう工夫しても全件検索（select *）となるため、実行速度をすることはできません。
【関連記事】
▶MySQLのインデックス作成方法 効いてないと思ったらexplainで確認する
サブクエリをexplainした例
サブクエリを使った複雑なSQLをexplainしてみましょう。
下記SQLは、titles（肩書テーブル）に存在する全ての肩書の社員が揃っている部署を取得します。
mysql> explain SELECT dept_name,
-> count(dept_name)
-> FROM (SELECT dept_name,
-> title
-> FROM titles
-> LEFT JOIN employees
-> ON titles.emp_no = employees.emp_no
-> LEFT JOIN dept_emp
-> ON employees.emp_no = dept_emp.emp_no
-> LEFT JOIN departments
-> ON dept_emp.dept_no = departments.dept_no
-> GROUP BY dept_emp.dept_no,
-> title) tbl
-> GROUP BY dept_name
-> HAVING count(*) = (SELECT count(DISTINCT title)
-> FROM titles);
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filte
red | Extra |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 488846 | 100
.00 | Using temporary |
| 3 | SUBQUERY | titles | NULL | index | PRIMARY | PRIMARY | 209 | NULL | 442605 | 100
.00 | Using index |
| 2 | DERIVED | titles | NULL | index | NULL | PRIMARY | 209 | NULL | 442605 | 100
.00 | Using index; Using temporary |
| 2 | DERIVED | employees | NULL | eq_ref | PRIMARY | PRIMARY | 4 | employees.titles.emp_no | 1 | 100
.00 | Using index |
| 2 | DERIVED | dept_emp | NULL | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | 100
.00 | Using index |
| 2 | DERIVED | departments | NULL | eq_ref | PRIMARY | PRIMARY | 16 | employees.dept_emp.dept_no | 1 | 100
.00 | NULL |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+------------------------------+
【関連記事】
▶SQL having 集約関数の絞り込み whereよりもレスポンスが遅い理由とは？
対策すべきところは、id=1の行（type=ALL、rows=488846）です。tableがderived2と出ているのは、実テーブルではなくサブクエリの2番めであることを示しています。
具体的にはhavingの部分です。havingはインデックスを利用しないため、SQLが遅くなる原因とされています。可能であればjoinに置き換えることで速度改善が可能なケースがあります。
今回の場合は、SQLの最適化でこれ以上レスポンスを上げることはできません。テーブル構造を変更するなど、SQL以外の部分での対応が必要になってきます。
まとめ
ポテパンダの一言メモ
- explainは、SQLの実行計画を確認するSQL
- 表示結果のtype=ALL、rowsの値が多い箇所を重点的に対策することで、速度改善を効率的におこなう。
- SQLだけの最適化では、速度改善がおこなえないケースがある。
