SQL のデータベースオプティマイザによる書き換え (DB2 for IBM i)

データベース製品はいろんな手段を使って SQL を速く実行させてユーザーにできるだけすぐに結果を伝えようとするわけですが、
そんないろいろな手段の中に SQL の書き換えというものがあります。

もともと SQL というのは、得たいデータ集合の条件の記述、なわけですから、その構成句を順番に実行する必要などない仕様になっているのですが、
えてして SQL の解析(パース)を行った順番に拘束されて非効率なアクセスプランが実行されてしまうことがままあります。

そんな非効率な SQL に対して、一般的には人間さまが手動で書き直すことが大概なのですが、
本来そんなことはシステムがやってくれてもいいはずですよね?!

iSeries / System i /IBM i のデータベースはそれをやってくれます。人間がわざわざ機械に気を使う必要がないわけなんですね。


データベースの世界では、そういったデータベースエンジンによる SQL の書き換えを "Query Rewite" といいます。

iSeries Network の記事「Query Optimization in DB2 for i5/OS」に、Query Rewrite について次のように書いてありました。

DB2 for i5/OS can optimize and implement SQL queries in many ways. One of the most interesting and smart techniques that the query optimizer employs is query rewrite. The optimizer can rewrite the query internally, allowing more streamlined processing and more efficient execution. For example, reading and processing data only to throw it away later makes no sense. The query optimizer tries to eliminate rows as early as possible to reduce unnecessary processing
A simple example of this idea is a grouping query that specifies the HAVING clause, which applies selection to the groups. The groups must be formed before the selection. Grouping and aggregating is often a costly process. Handling and then eliminating a large set of rows is unnecessary if the query can be rewritten to avoid processing the superfluous data. With this query
SELECT  CITY,
SUM(REVENUE)
FROM           SALES
GROUP BY       CITY
HAVING  CITY = 'Rochester';
the HAVING clause predicate (CITY = 'Rochester') is "pulled up," and the optimizer rewrites the query to specify a WHERE clause predicate. This lets the database engine eliminate any irrelevant rows before aggregation. Because only one group (i.e., CITY) is returned, the grouping logic can also be effectively eliminated. The resulting query is much more efficient and should be faster:
SELECT  CITY,
SUM(REVENUE)
FROM           SALES
WHERE   CITY = 'Rochester';

そこで確かめてみようと手持ちのテーブルを使って似たような SQL を書いて実行してみたところ、確かにそのとおりに書き直しているのが確認できました。


こちらが実行してみた SQL です。

記事にあるとおり、GROUP BY 〜 HAVING 〜 が WHERE での指定に書き換えられているのが確認できます。

この SQL をそのまま額面どおりに受け取ると、GROUP BY でグルーピングしてから、その条件にあうグループを取り出す、ということになりますが、
それだと、わざわざ条件以外のものをただ捨てるためだけにグループ化する処理を行うことになって、いかにももったいないですね。

そこでまず最初に選択を行ってグループ化する対象の行をまず減らしてしまおう、という戦略をとるわけです。
この書き換え自体は、数学的に(代数操作)等価な書き換えです。ヴェン図などで確かめてみればわかりますが、結果が変わってくるようなことはありません。

テーブル・スキャンを行い、約10000件(索引が存在せず、WHEREで=が指定されている場合、10%が行数見積もり値としてコスト計算されます)のデータから「集約」処理を行うプランが生成されています。

「アクション」-「アドバイザー」を選択してみましょう。
↓のような画面が表示されるので、索引が推奨されていることがわかります。
(↑の画面でも、「索引の作成の推奨」が「はい」になっていることから推奨索引があることがわかります)

アドバイスされたとおりの索引を作成した後の結果が↓です。

同様の書き換えがされていますが、対象がインデックスに変わっています。(これもまた”書き換え”ですね?! 当り前すぎて気づかないかもしれませんが)

「選択された行カウントの合計」を見ると、61 とより正確な値になっていることがわかります。
また、「実行される計画についての見積もり」を見ると「CPU制約」になっており、「使用されたメモリー」も65536 とあまり多くないことがわかります。

「CPU制約」でなければ「入出力制約」になるのですが、コンピュータの処理の中で時間がかかる代表的な処理は入出力処理です。
つまり、なるべく入出力をなくして CPU を積極的に使って速く SQL の処理結果を出してもらう、というストラテジーを持っている、ということになりますね。

では、それぞれの結果を比較してみましょう。

インデックス作成前のものです。
「合計時間、マイクロ秒」は104288です。

インデックス作成後には「合計時間、マイクロ秒」が 21013、と 1/5近くになっていることがわかりますね。

インデックス作成後の「実行される計画についての見積もり」では「CPU制約」でしたね。これがそのインデックス作成前のものでは「入出力制約」になっていたのが確認できます。

「使用されたメモリー」については、インデックス作成後に最適化された値は 65536 でしたが、インデックス作成前のものでは 4541324.467 になっています。(この「使用されたメモリー」は実際には「使用可能なメモリー」の10%をとっただけのようですね。インデックスが存在しないので、とりあえずの"推定"をしているわけです)

「処理時間」なども比較してみてください。(実際には、この「テーブルのスキャン」の情報は、インデックス作成後のもので言えば「索引プローブ」と「テーブル・プローブ」を足したものになります。それを足してみても格段に速くなっていることが確認できますね。そもそも単位が違います)

個別の部分も見ていきましょう。

インデックス作成後の「索引プローブ」の部分です。インデックス作成前での「テーブルのスキャン」にあたる部分です。

「ランダムIO」の部分です。

「テーブル・プローブ」の部分です。
ここは「入出力制約」になっていますね。

インデックス作成前とインデックス作成後に共通に存在する「集約」部分ですが、まずインデックス作成前の「集約」ノードの内容を見てみましょう。

インデックス作成後のプランの「集約」部分です。
「実行された計画」についてはほとんど変わりませんが、入力されるデータの数が異なるため「処理時間」が少なくなっていることが確認できます。ここにたどり着く前にデータを絞っているので、処理が軽くなっているわけです。

最後にそれぞれの最終結果の情報です。
ついでですが、「ステートメント・テキスト」を見ると実行したSQL文のHAVINGの指定がパラメータ・マーカーに変換されていることがわかりますね。これも SQL の再利用のための重要な機能です。再利用されれば、それだけ最適化のために使用される CPU 時間が助かる、というわけですね。

↓が、インデックス作成前のものです。

インデックス作成後のものは↓です。

プランの詳細に立ち入ったため、ちょっと焦点がぼけてしまったかもしれませんので、最後にちょっとまとめておきます。

DB2/400 は SQL クエリ(最近はこちらの言い方の方がメジャーなかんじなので?!)の最適化をいろんな方法で行います。
S/38、AS/400 以来の、ユーザーにはアプリを作ること/より良く動かすことに専念してもらいたい、つまり、できるだけ余計なことをしないように済むように、という一貫した哲学に見事に沿った形で、現代のリレーショナル・データベース・エンジンとして実装がなされています。

よく SQL の書き方ガイドみたいなものに、"同じ意味なので/同じ結果が返ってくるので、こう書き換えるべき"な SQL の例が載っていますが、
もともとデータベースへの照会(クエリ)は、通常のプログラムのような「どういう操作でデータを抽出してほしい」という手続きの記述ではなく、「どういう条件のデータを抽出してほしい」という指示であり、必要なデータの条件の記述です。
照会されるデータの内容は、コッド博士の考えたように、集合理論に基づく代数演算で一意的に決まるものです。

本来、集合理論に基づく代数演算としては同一のものなのに、SQL 上の表現としてはいろんな書き方ができてしまう、ということが起こります。

あるデータがほしい時に、まずグループ化して、その中でこれとこれのグループは必要ない、というのが GROUP BY 〜 HAVING 〜 と記述した時の発想ですね。
これを、「先に選択で除外しておいた方が結果が速い!」なんていうのはコンピュータ/システムの都合であって、そこをわざわざユーザーが歩み寄って"よりシステムにわかりやすい"クエリを書いてあげる、なんてことをするのはまさしく「余計なこと」なわけです。

それを汲んで DB2/400 では、そういう "SQL ではいろんな書き方がされているがリレーショナル代数上は同一の表現" について、システム側で書き換えを行う、というのが今回確認したことのポイント/焦点です。

SQL でいろんな書き方ができる、というのは、アプリケーション開発者にとっては自然な発想どおりに SQL を書くことができるわけで、それだけ理解しやすい SQL につながるわけです。
それを、わざわざシステムの都合にあわせたクエリに書き直してわかりにくくする、というのは本末転倒な話でもありますよね。

そのようにしてシステムに書き換えてもらって速くなったものも、さらにインデックスなどを使ってもっと速くできることがある、ということが後半のお話でした。
第二段階のチューニングも簡単に行える、ということです。

[Top Pageに戻る]

Ads by TOK2