ぐるぐる系 VS ガツン系と結合の備忘録
こちらの記事の続きです。書籍の内容が重すぎて、まだまだまとめることが多そうです。
ぐるぐる系 VS ガツン系
SQL単体はFor文の機能がないため、For文(ぐるぐる系)を用いようとするとストアドプロシジャーを利用することになる(またはサーバーサイドプログラミングでfor文を利用するなど)。しかし、パフォーマンスを上げるために、できるだけ、ストアドプロシジャーを用いないこと(ガツン系)。
ぐるぐる系の処理のパフォーマンスが悪い理由は以下の二つの理由である。
- 一つ一つの処理にオーバーヘッドが必要
- 並列分散処理ができない
また、処理自体が単純なため、RDBMSの進化の恩恵を受けられない(チューニングポテンシャルがほとんどない)ことから、求められる性能がでないときは下記のような対策が取られるが、コストが非常に高いので、初めからぐるぐる系の処理を用いることを最小限にとどめることが求められる。ぐるぐるの処理回数が決まっていて、処理内容が多くない場合、ぐるぐる系の処理のほうがパフォーマンスがでることもあるので、例外的にぐるぐる系の処理でもよい。
- ぐるぐる系処理をガツン系に書き換える
- CPUなどの物理的に性能の良いものに変える
相関サブクエリ
サブクエリ内に外側のクエリとの結合条件を記述することでテーブルをその結合キーでカットした部分集合に対して操作を行うことを可能にする技術。ウィンドウ関数のPARTITION BY句とORDER BY句と同じ機能をもつ。
結合
クロス結合
直積(非常に演算コストが高い)
SELECT * FROM Employees CROSS JOIN Departments;
内部結合
直積の部分集合、相関サブクエリと書き換え可能なことが多い
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
FROM Employees E INNER JOIN Departments D
ON E.dept_id = D.dept_id;
外部結合
マスタ側のテーブルだけに存在するキーがあっても、キーを削除せず、結果に保存。値のない部分はNULL。クロス結合の部分集合でない要素もあることがあるので、外部結合という。
SELECT E.emp_id, E.emp_name, E.dept_id, dept_name
FROM Departments D LEFT OUTER JOIN Employees E
ON D.dept_id = E.dept_id;
※左のテーブルがマスタ
結合アルゴリズムとパフォーマンス
オプティマイザが選択可能な結合アルゴリズムは主に以下の三つ。RDBMSによってサポートしていないものもある。
- Nested Loops
- Hash
- Sort Merge
1.Nested Loops
結合対象となるテーブルを１行ずつループしながらスキャンする。このテーブルを駆動上または外部表と呼ぶ。もう一方のテーブルは内部表と呼ぶ。駆動表の一行について内部表を一行ずつスキャンして、結合条件に一致すれば返却する。この動作をすべての駆動表の行に対して繰り返す。
- Nested Loopsの実行時間は行数の直積数に比例
- １ステップで処理する行数が少ないため、あまりメモリを消費しない
- どのDBMSでもサポートしている
内部表の結合キーにインデックスが張られていてる場合(ほとんどの場合)、駆動表が小さいほうが処理速度は速い
2.Hash
小さいほうのテーブルをスキャンし、結合キーに対してハッシュ関数を適用することでハッシュ値に変換する。その次にもう一方の大きいほうのテーブルをスキャンし、結合キーがそのハッシュ値に存在するか調べる。
- ハッシュテーブルのためにNested Loopより多くのメモリを消費
- よって、TEMP落ちになる可能性がある
- ハッシュ値は入力値の順序性を保存しないため、等値結合でしか利用できない
つまり、Nested Loopがあまり効かない場合、Hashを利用するとよい。
3.Sort Merge
結合対象のテーブルをそれぞれ結合キーでソートを行い、一致する結合キーを見つけたらそれを結果にセットする。Nested Loopがあまり効かない場合、こちらも検討。
