Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

達人のワザを、達人に学ぼう~「達人に学ぶハイパフォーマンスSQL実践講座」体験レポート

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2016/06/23 14:00

 CodeZine Academy(コードジン・アカデミー)は、開発者のための実装系Webマガジン「CodeZine(コードジン)」編集部が企画・運営する“開発に役立つ実践系セミナー”です。今回初となるSQL関連セミナーは、RDBMS界では知らなきゃモグリだといわれる(?)達人ミックさんによる「ハイパフォーマンスSQL実践講座」です。そのセミナーの模様を本レポートでお伝えします。

 こんな方にオススメ:SQL/RDBの使用経験が1年以上のプログラマ/SE。

  • ひととおりのクエリは書いたり読んだりできるけれども、結合・サブクエリはコピペがメイン
  • パフォーマンスが悪いとお手上げ
  • データ分析にWindow関数を使いたくてマニュアルを読んだけど、ちんぷんかんぷん

 このレベルの人が受講すると、すごく役立つと思います。

 一人一台のノートパソコンにOracle Databaseの実行環境が用意され、演習も実際のデータを用いてできるため、SQL*Plusの簡単な操作さえ行えれば、実際に手を動かして、確認しながらセミナーを聞き進めていくことができます。

午前の部:SQLで自在にデータ操作を行う

 まずは実習で使う環境の確認です。私自身は通常はMySQLを使っており、Oracle DBのCLIであるSQL*Plusをまじめに使っていたのは、もう20年ほど前なのですが(笑)、すでに手元のノートパソコンでOracle DBインスタンスが起動済み、動作の確認もほぼコピペで行えるので、特に行きづまることなく準備OKでした。

 さて、午前の部では、SQLで自在にデータ操作を行う、ということで、JavaやC#など、いわゆる「手続き型言語」では学習の初期に教わる、条件分岐(IF文、CASE文)とループ(FOR文、WHILE文)についてです。これらは手続き型では不可欠なものですが、SQLを勉強する際には「教わりません」。しかし、この2つを使いこなせるようになると、SQLプログラミングの幅が大きく広がるということで。最初は「条件分岐に当たるもの」の解説です。

条件分岐:CASE式

 SQL標準の初期(SQL-92)で定められたCASE式は、今やすべてのRDBMSで利用できます。文ではなく式というのがポイントで、クエリの「式」が書けるところには、どこでも書けます。次のような「一番簡単なCASE式のサンプル」からはじめ、徐々に現場で役立つところまで演習を重ねていきます。

SELECT CASE WHEN 1 = 1 
       THEN '1=1です' 
       ELSE NULL END AS case_col
  FROM DUAL;

ループ:相関サブクエリに替わるもの?

 ループといえば、私などは真っ先に「相関サブクエリ」を思い出すのですが(注1)、モダンなRDBMSではWindow関数でループを扱えるとのこと。Window関数といえば「順位に使えるRANKだけを知っていて、その他はぼんやりとしか分からない。まぁMySQLではそもそも使えないし、いいか」というくらいの温度感だったのですが、ミックさんの「ウィンドウというのは範囲という意味で、GROUP BYのグループに似ているが、集約しないので、GROUP BYのカットの機能だけを残したもの」という説明で、がぜん興味がわいてきました。

 実際、その言葉を元に演習を重ねると、これまでぼんやりとしていたWindow関数の輪郭が徐々にはっきりしてきた気がしましたし、新機能をいろいろ理由付けして未着手にする自分を少し反省しました。

注1

 そもそもSQLでループさせる方法がわからずに、アプリケーション側でループを記述してその中でSQLを実行する人もいますが。

午後の部:SQLの効率的な書き方

 データベースの性能問題を解決するために「実行計画」を知り、それを用いてどのようにクエリを改善できるのか、を考えていく必要があります。実行計画とは、RDBMSが与えられたクエリをどのように実行するか立てる計画で、通常複数の計画が立てられるものです。そのうち、RDBMS側がオプティマイザでどの実行計画がよいかを決定するのですが、RDBMSによってはオプティマイザにヒントを与えて、どの実行計画を利用するかを調整できる場合もあります。

 まずは実行計画をどのように見るか、というところがファーストステップとなります。

実行計画とその見方

 Oracle DBについて、SQL*Plusから以下のように設定すると、実行計画と共に実際にSQLを実行し、それにかかった時間が測定できます。

set autot traceonly
set timing on
set time on

 本番環境では気軽に重いクエリの実行はできないので、set autot traceonlyの後にexpをつけて、SQLを実行しない(いわゆるdry run)も可能です。しかし、ここでは一人一台の実行環境がありますし、すでに実行計画表示用のデータをもったテーブルもありますので、気にせず行いましょう。そして実行計画の見方とかかる時間を確認したら、結合(Join)について、実際にどのように改善できるかどうかトライしてみます。

結合のアルゴリズムと実行計画

 結合(Join)には主な結合アルゴリズムとしてNL(NESTED LOOP)、HASH、SORT MERGEがありますが、今回はNLとHASHについて、その結合アルゴリズム自体の説明と、そのアルゴリズムだからこそ「こうすればはやくなる・遅くなる」の説明が演習とともに示されます。ここでも実際にデータが用意されている、ということが役立ち、例えば前提条件をこう変えると、どのアルゴリズムが利用され、どちらがはやいか、というのを、単なる机上の問いと説明、答えに終わらず、実際に実行して確認や答え合わせができる、というのが、達成感があってよかったです。

最後に

 ミックさんの初心者向けの書籍は、本だけでも非常に分かりやすく役立つものですが、やはり本人と対面で、実際のデータ操作をしながら勉強し、不明点を本人に確認できる場、というのは(受講料がやや高価ですが)なにものにも替えがたい経験でした。

 私の場合、本を読んだときにはCASE式には納得がいっていましたが、Window関数については「へぇ~」って感じで読み飛ばしていたものが、今回の受講でかなり納得して、これが使える商用版の新しめのバージョンや、PostgreSQLなどでは積極的に使ってみようという気になりました(注2)。

 また、便利な手段(CASE式やWindow関数)が無かった時代にはどのような(効率の悪い)クエリで処理を行っていたか、ということも、演習の箸休め的にコラムで触れられていたのも、面白かったです。

 SQLの上っ面だけではなく、その仕組みも理解して、一皮むけたDBエンジニアへの足がかりに、本講座を受講してみてはいかがでしょうか?

注2

 PostgreSQL以外のオープンソースRDBMSでも、Firebirdの最新バージョン3.0や、MySQLからフォークしたMariaDBの次期バージョン10.2からはWindow関数が使えるようになっています。

次回開催(2016/8/30)のお知らせ

 次回は、『達人に学ぶ ビッグデータ時代の最新SQLテクニック講座』と題したセミナーを8月30日(火)に開催します。SQLのテクニック紹介をさらに充実し、大規模データの高速処理に役立つノウハウを紹介します。ご期待ください!

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • 小川 範夫(オガワノリオ)

    ネットワーク型やカード型のデータベースの時代から、データベース界隈を興味の赴くままウン十年さまようデータベースマニア(ミーハー)。ミックさんの本を読んではDB・SQLに思いを馳せ、西内さんの本を読んでは統計学・R言語をやろうと思うものの、日々ビールを飲んで眠ってしまい、積ん読本ばかりが増える日々。基...

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5