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

3月 12, 20186月 9, 2020

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

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

1. 調べようと思った経緯
2. 実行計画とは
3. 実行時の環境
4. テーブルの構成
5. 実行計画を取得
6. まとめ

調べようと思った経緯

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

実行計画？？統計情報？？って人へ #SQL - Qiita

はじめに新人研修でSQLがカリキュラムに組み込まれている場合、ほとんどがSQLの構文のみをスコープとし ...

https://qiita.com/NagaokaKenichi/items/5b6eb9887f88046a594d

実は実行計画について、僕はざっくりベースでしかわかっていなくて、
「実行スピードと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	東京都	渋谷区	テスト１	大泉陽	l1
2	山形県	鶴岡市	テスト３	南城希	l3
3	青森県	八戸市	テスト３	綾智絵里	l3
4	福島県	郡山市	テスト２	矢沢笑顔	l3
5	青森県	八戸市	テスト２	東野真姫	l1
6	千葉県	八千代市	テスト４	夜空林	l1
7	埼玉県	朝霞市	テスト３	低路穂乃果	l2
8	埼玉県	朝霞市	テスト２	園そら	l2
9	沖縄県	那覇市	テスト１	西田鴻	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	オプティマイザがどんな計画を立てて実行したかの説明