【SQL・初心者向け】SQLの実行計画とは

どうも、ブログ更新サボり続けてしまっていました。
久しぶりの更新。

今回はSQLの実行計画について調べたことを書きます。

調べようと思った経緯

友人より、実行計画について
わかりやすく書いてあるページを教えてもらいました。

実は実行計画について、僕はざっくりベースでしかわかっていなくて、
「実行スピードとSQLの構文の実行順がわかりやすく、結果として見せてくれる」
というくらいしかわかっていませんでした。

そんな時に友人からの連絡。
たくさん感謝です。

実行計画とは

上記サイトでは

どのようにテーブルに対して検索を行なうかが書かれた、SQLの実行手順書のようなものです。そのため、SQLのパフォーマンスは実行計画によって大きく左右されます。

と書かれています。
SQLの実行手順書を見て、どのように実行されているかを分析して
SQL構文の改善に役立てるというところでしょうか。

実際に実行して、内容を見てみました。

実行時の環境

Mac 10.13.3
MySQL 5.7.21-1
SequelPro

テーブルの構成

・userInfoテーブル

CREATE TABLE `userInfo` (
  `id` int(11) NOT NULL,
  `pref` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `graderId` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `graderId` (`graderId`),
  CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`graderId`) REFERENCES `grader` (`graderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

・内容

id pref address1 address2 name graderId
1 東京都 渋谷区 テスト1 大泉陽 l1
2 山形県 鶴岡市 テスト3 南城希 l3
3 青森県 八戸市 テスト3 綾智絵里 l3
4 福島県 郡山市 テスト2 矢沢笑顔 l3
5 青森県 八戸市 テスト2 東野真姫 l1
6 千葉県 八千代市 テスト4 夜空林 l1
7 埼玉県 朝霞市 テスト3 低路穂乃果 l2
8 埼玉県 朝霞市 テスト2 園そら l2
9 沖縄県 那覇市 テスト1 西田鴻 l2

・graderテーブル

CREATE TABLE `grader` (
  `graderId` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `graderName` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`graderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

・内容

graderId graderName
l1 1年生
l2 2年生
l3 3年生

学年の情報をgraderテーブルに持たせて
userInfoテーブルには個人の情報と、学年のIDを持たせています。
ちなみに、外部キーとしてuserInfoのgraderIdがgraderテーブルのgraderIdと紐づくように
持っています。

実行計画を取得

SELECTの構文の前に「EXPLAIN」をつければ良い模様。
というわけで、SELECTの前につけて以下のSQLを実行しました。

EXPLAIN select
    *
from
    grader gr
left outer join
    userInfo ui
ON
    gr.`graderId` = ui.`graderId`

実行結果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gr NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE ui NULL ALL graderId NULL NULL NULL 9 100.00 Using where; Using join buffer (Block Nested Loop)

よくわからない項目が出てきたので
一つずつ解説

項目名 内容
id 実行順番。複数レコードで数字が同じ場合、同時に実行されたSQL
select_type select内容のタイプ、意味については後述
table どのテーブルを使用したか。別名をつけた場合は、別名として表示される。
partitions partitionを使ったかどうか。使っていない場合はNULL
type テーブルのレコードに対して、どのようにアクセスされているかみている。INDEXが使われているかもここを見れば良いみたい
possible_keys オプティマイザが、テーブルのアクセスに利用可能なINDEXの候補として挙げたキーの一覧。
オプティマイザが自動で、INDEXの候補を選んでくれるみたいです。
key オプティマイザが自動で選んだKEY
key_len 選択されたKEYの長さ。短いほど高速。
NULLの場合は爆速なのかな?要調査だと思ってます。
ref 検索条件でKEYと比較されている値やカラムの種類。
検索条件をつけない場合はNULLになるのかな?
rows テーブルからfetchされる行数。
ただし、表示される数値はおおよその行数。
graderテーブルは3行あるのに、2行しか返されない
filtered テーブルの条件によってフィルタリングされた件数
Extra オプティマイザがどんな計画を立てて実行したかの説明

ざっくりとこんな感じの説明になります。
説明した内容は以下のページを参考にさせてもらいました。

qiitaさんにはいつも助けられています・・・!ありがとうございます。

まとめ

今回は実行計画の入り口を調べて、少し実行しましたが、
次はもう少し複雑なクエリを作って、実行してみようかなと思います。

qiitaさん、そして実行計画について良いページを教えてくれた友人へ
本当にありがとうございます!

今回は以上!

スポンサーリンク

SQL

Posted by タツノコ