ベータ版が公開されたのを記念して、Workbenchに搭載されているナイスな機能について紹介したい。そう、Visual Explainだ。Visual Explainとは読んで字のごとく、SQLの実行計画を視覚的に表現したものだ。SQLが複雑になると、その実行計画は理解し辛いものとなる。
今日はVisual Explain基本的な使い方と、それがどのように見えるかを紹介しようと思う。
Visual Explainを使用するには、対象のMySQLのバージョンが5.6以上であり、なおかつWorkbenchのバージョンが5.2.45以上または6.0.2以上でなければならない。（MySQL Cluster 7.3のSQLノードはMySQL 5.6ベースなので、MySQL Cluster 7.3でもオッケーだ。）MySQLのバージョンが5.6でなければならないは、MySQL 5.6で拡張されたEXPLAINの新機能である、JSONフォーマットによる出力を利用するからである。ちなみにJSONフォーマットでEXPLAINを出力するには次のように実行すると良い。
mysql> EXPLAIN FORMAT=JSON SELECT (以下略);
JSONフォーマットになっただけでも十分役立つのだが、これが視覚化されることで威力は倍増する！「今までMySQLのEXPLAINは見辛かった。」ないしは「理解するのに苦労した。」という人にはまさに朗報だ。
なお、Workbench 5.2と6.0では、Visual Explainの機能に若干の違いがある。本エントリはバージョン6.0をベースに解説するので、そこのところは了解いただきたい。
サンプルクエリ＆画像
例によってMySQLのサンプルデータベースであるworldデータベースを用いて解説しよう。worldデータベースはMySQLのドキュメントサイトからダウンロード可能だ。（worldデータベースはInnoDB版もあるのでお好みでどうぞ！）まずは最もシンプルな実行計画であるテーブルスキャンがどう表示されるかを見てみよう。
SELECT * FROM Country
WHERE句すらない純粋なテーブルスキャンである。
この通り、ボックスがひとつ表示されるだけである。箱の上の数字は、このテーブルから何行フェッチされるかという見積もりであるる。EXPLAINの実行結果のrowsフィールドに相当する。ここで、このボックスの色が赤であるという点に注意しておいてほしい。
このクエリに対してWHERE句をつけてみよう。
SELECT * FROM Country WHERE Code LIKE 'J%'
Codeカラムはこのテーブルの主キーなのでインデックスが貼られてある。つまりこのクエリは範囲検索である。
なんとボックスの色が緑に変わった！ボックスの色はテーブルへのアクセスタイプによって変化する。これは直感的で分かりやすい。
JOINの例も見てみよう。
SELECT
Language,
COUNT(1)
FROM
CountryLanguage
JOIN Country
WHERE
Capital IN (SELECT Id FROM City)
GROUP BY
LANGUAGE
実はこのJOINにはヒューマンエラーが潜んでいる。お気づきだろうか？ではVisual Explainの表示をみてみよう。
それぞれのテーブルに対するボックスの色は赤、青、オレンジである。青はeq_refなので最も効率的な（主キーやユニークキーによる）JOINである。問題は赤とオレンジだ。主キーでJOINされているものの、Block Nested LoopになってJOINバッファが割り当てられている。何かがおかしい。「あ、JOINの条件を付け忘れてる！」というふうに間違いに気づくことができる（笑）
正しいクエリはこう↓であるはずだ。
SELECT
Language,
COUNT(1)
FROM
CountryLanguage
JOIN Country
ON CountryLanguage.CountryCode = Country.Code
WHERE
Capital IN (SELECT Id FROM City)
GROUP BY
LANGUAGE
するとVisual Explainの色も・・・
オレンジから緑に変わった。緑ならとりあえずオッケーだ！といってしまうとかなりアバウトな感じがするが、直感的な判断で大まかには問題がないのがVisual Explainのミソである。
せっかくなのでもう少し複雑なクエリの例も紹介しよう。次のクエリはCountryテーブルから異なる（関連性のない）条件に該当する行の数をカウントするものである。
SELECT
'cond1' AS LABEL,
COUNT(1) AS COUNT
FROM
Country
WHERE
Code LIKE 'J%'
UNION SELECT
'cond2',
COUNT(1)
FROM
Country
WHERE
IndepYear > 1900
UNION SELECT
'cond3',
COUNT(1)
FROM
Country
WHERE
Continent = 'Africa'
UNION SELECT
'cond4',
count(1)
FROM
Country
WHERE
Name = LocalName
4つのSELECTがUNION（ALL）で合体しているので、都合4回のクエリを実行することになる。では実行計画を見てみよう。
なんと4つのSELECTのうち、3つが「赤」だ！それぞれの検索条件にマッチするインデックスがないからだ。（このテーブルには主キー以外のインデックスは存在しないのだが。）
Countryテーブルはサイズが小さいので、例えテーブルスキャンでもパフォーマンスの問題になることはないと考えられるが、現実にはこのような小さなテーブルだけを扱っていれば良いというシチュエーションは少ない。ここはひとつ、「テーブルのサイズは大きくなるかも知れないのでスキャンはなるべく控えたい」という前提でチューニングをしてみよう。条件にマッチするインデックスがなければどうしてもテーブルスキャンは避けられない。であれば「1回のスキャンで必要なデータを全部収集してしまおう」というのがセオリーだ。
少し複雑であるが、CASE式とpivotテーブルを使うことで次のように書き換えができる。（この例ではpivotテーブルはUNIONで作っている。）COUNT()ではなくSUM()を使っているところがミソである。
SELECT
CASE
WHEN
id = 1
THEN
'cond1'
WHEN
id = 2
THEN
'cond2'
WHEN
id = 3
THEN
'cond3'
WHEN
id = 4
THEN
'cond4'
ELSE
NULL
END AS LABEL,
CASE
WHEN
id = 1
THEN
c1
WHEN
id = 2
THEN
c2
WHEN
id = 3
THEN
c3
WHEN
id = 4
THEN
c4
ELSE
NULL
END AS COUNT
FROM
(
SELECT
id,
c1,
c2,
c3,
c4
FROM
(
SELECT
SUM(
CASE
WHEN
Code LIKE 'J%'
THEN
1
ELSE
0
END
) AS c1,
SUM(
CASE
WHEN
IndepYear > 1900
THEN
1
ELSE
0
END
) AS c2,
SUM(
CASE
WHEN
Continent = 'Africa'
THEN
1
ELSE
0
END
) AS c3,
SUM(
CASE
WHEN
Name = LocalName
THEN
1
ELSE
0
END
) AS c4
FROM
Country
) AS t1
JOIN
(
SELECT 1 AS id
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
) AS pivot
) AS t2
このクエリに対するVisual Explainは次のようになる。
赤いボックスはひとつになった。青いボックスはテーブルを使用していない（FROM DUAL）クエリで、pivotテーブルを生成している部分である。
このクエリを理解するには行数に着目して欲しい。
t1（サブクエリ）は239行で、これは元のテーブルと同じ行数である。この部分はテーブルスキャンであり非効率なクエリであると言えるが、今回のクエリではそれがひとつになった。t1はグループの指定なしの集約関数の実行結果なので、その実行結果は1行となる。ただしt1には列が4つあり、それぞれが先ほどのUNIONを用いたクエリの結果の行に相当している。
さっきのクエリでは実行結果が4行だったが、それが4列になってしまうのは都合が悪い。値は同じなのでアプリ側を書き換えれば良い話だが、出来ればアプリを書き換えずに済ませたいという場合も多いだろう。そこで使えるのがpivotテーブルである。
pivotテーブルは4行でt1は1行。t2（サブクエリ）は1行と4行のテーブルをJOINしたものなので、4行となる。列はというと、pivotテーブル内のidカラムと、t2の結果4列（どの行も値が同じ）で、合計5列となっている。仕上げにCASE式を使い、idの値に応じて列の値を変化させることで、4行2列のテーブルに変換している。このようにCASE式とpivotテーブルを使って列から行へと変換するテクニックは便利なので覚えておくといいだろう。
ただし、いかに書き換えで高速化できるとはいえ、このようなクエリはクソクエリの部類であるので、頻繁に実行するのは避けるべきである。そもそも集約関数は非常に多くの行にアクセスしなければならないため、コンピュータリソースを非常に多く消費してしまい、データベースがスローダウンする原因になりやすい。このようなクエリをトランザクションの途中で実行したり、ウェブページを表示するごとに実行するといったことは、絶対に避けるべきである。（避けるべきだが非常に使用例が多くて困るので、皆さん気をつけてください、マジメな話。）
クエリの構造の解説で話が逸れてしまったが、このような複雑な構造をもったクエリでもVisual Explainを使うことで、直感的に効率的かどうかがある程度理解できるというわけだ。
まとめ
今回はMySQL WorkbenchのVisual Explainについて説明した。なかなか使える機能なのでぜひ試してみて欲しい。現在ベータ版であるMySQL Workbench 6.0の正式版のリリースも、ぜひ楽しみにして頂きたい。Enjoy!
0 コメント:
コメントを投稿