sidebarちょっと硬派なコンピュータフリークのBlogです。Workbenchの次期バージョンである6.0のベータ版が公開された。例によってMySQLのダウンロードサイトで公開されているので、新機能が気になる人はゲットして試してみて頂きたい。見た目が若干今流行りのフラットデザインっぽくなってシャレオツ(笑)な感じに仕上がってる。ベータ版が公開されたのを記念して、Workbenchに搭載されているナイスな機能について紹介したい。Explainとは読んで字のごとく、SQLの実行計画を視覚的に表現したものだ。SQLが複雑になると、その実行計画は理解し辛いものとなる。Explain基本的な使い方と、それがどのように見えるかを紹介しようと思う。Explainを使用するには、対象のMySQLのバージョンが5.6以上であり、なおかつWorkbenchのバージョンが5.2.45以上または6.0.2以上でなければならない。7.3でもオッケーだ。5.6で拡張されたEXPLAINの新機能である、JSONフォーマットによる出力を利用するからである。ちなみにJSONフォーマットでEXPLAINを出力するには次のように実行すると良い。(以下略);JSONフォーマットになっただけでも十分役立つのだが、これが視覚化されることで威力は倍増する!「今までMySQLのEXPLAINは見辛かった。」ないしは「理解するのに苦労した。」という人にはまさに朗報だ。Explainの機能に若干の違いがある。本エントリはバージョン6.0をベースに解説するので、そこのところは了解いただきたい。例によってMySQLのサンプルデータベースであるworldデータベースを用いて解説しよう。worldデータベースはMySQLのドキュメントサイトからダウンロード可能だ。(worldデータベースはInnoDB版もあるのでお好みでどうぞ!)まずは最もシンプルな実行計画であるテーブルスキャンがどう表示されるかを見てみよう。CountryWHERE句すらない純粋なテーブルスキャンである。この通り、ボックスがひとつ表示されるだけである。箱の上の数字は、このテーブルから何行フェッチされるかという見積もりであるる。EXPLAINの実行結果のrowsフィールドに相当する。ここで、このボックスの色が赤であるという点に注意しておいてほしい。このクエリに対してWHERE句をつけてみよう。'J%'Codeカラムはこのテーブルの主キーなのでインデックスが貼られてある。このクエリは範囲検索である。なんとボックスの色が緑に変わった!ボックスの色はテーブルへのアクセスタイプによって変化する。これは直感的で分かりやすい。JOINの例も見てみよう。LANGUAGE実はこのJOINにはヒューマンエラーが潜んでいる。Explainの表示をみてみよう。それぞれのテーブルに対するボックスの色は赤、青、オレンジである。青はeq_refなので最も効率的な(主キーやユニークキーによる)JOINである。問題は赤とオレンジだ。LoopになってJOINバッファが割り当てられている。「あ、JOINの条件を付け忘れてる!」というふうに間違いに気づくことができる(笑)正しいクエリはこう↓であるはずだ。Explainの色も・・・オレンジから緑に変わった。Explainのミソである。複雑なクエリの例も紹介しよう。次のクエリはCountryテーブルから異なる(関連性のない)条件に該当する行の数をカウントするものである。LocalName4つのSELECTがUNION(ALL)で合体しているので、都合4回のクエリを実行することになる。実行計画を見てみよう。なんと4つのSELECTのうち、3つが「赤」だ!それぞれの検索条件にマッチするインデックスがないからだ。(このテーブルには主キー以外のインデックスは存在しないのだが。)Countryテーブルはサイズが小さいので、例えテーブルスキャンでもパフォーマンスの問題になることはないと考えられるが、現実にはこのような小さなテーブルだけを扱っていれば良いというシチュエーションは少ない。ここはひとつ、「テーブルのサイズは大きくなるかも知れないのでスキャンはなるべく控えたい」という前提でチューニングをしてみよう。条件にマッチするインデックスがなければどうしてもテーブルスキャンは避けられない。あれば「1回のスキャンで必要なデータを全部収集してしまおう」というのがセオリーだ。複雑であるが、CASE式とpivotテーブルを使うことで次のように書き換えができる。(この例ではpivotテーブルはUNIONで作っている。)COUNT()ではなくSUM()を使っているところがミソである。Explainは次のようになる。赤いボックスはひとつになった。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テーブルを使って列から行へと変換するテクニックは便利なので覚えておくといいだろう。書き換えで高速化できるとはいえ、このようなクエリはクソクエリの部類であるので、頻繁に実行するのは避けるべきである。そもそも集約関数は非常に多くの行にアクセスしなければならないため、コンピュータリソースを非常に多く消費してしまい、データベースがスローダウンする原因になりやすい。このようなクエリをトランザクションの途中で実行したり、ウェブページを表示するごとに実行するといったことは、絶対に避けるべきである。(避けるべきだが非常に使用例が多くて困るので、皆さん気をつけてください、マジメな話。Explainを使うことで、直感的に効率的かどうかがある程度理解できるというわけだ。Explainについて説明した。使える機能なのでぜひ試してみて欲しい。6.0の正式版のリリースも、ぜひ楽しみにして頂きたい。Okuno私は所謂ギークです。普段はMySQL関係の仕事をしています。今の時代、フリーソフトウェア(自由なソフトウェア)こそギークの歩く道だと信じています。コンピュータに手を染めてはや二十年。コンピュータ道はいくら進んでも終わりが見えない果てしない世界。コンピュータにはロマンがある。男に生まれたからにはロマンを追わずして居られようか!詳細プロフィールを表示リンクはどうぞご自由に。7.3登場!!これで勝つる。プログラミングを教育する前に必要なことはてなのデザイン変更はイノベーションか。本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。