SQL中級者になる！（その11/12） - 「大人の教養・知識・気付き」を伸ばすブログ

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ

作者:ミック
翔泳社

Amazon

を参考に、更なるSQLの知識を拡充していこう。

前回

power-of-awareness.com

初めに
前回
11.　SQLを速くするぞ
次回

11.　SQLを速くするぞ

　 $\mathrm{SQL}$ を高速化し可能な限り少ないリソースで実行するためのパフォーマンスチューニング技術を考える。
　 $\mathrm{SQL}$ のレスポンスが遅いとしても、 $\mathrm{SQL}$ 以外の要因、たとえばメモリの配分やストレージ構成、システムの物理的な設計に起因することもある。ここでは $\mathrm{SQL}$ のみの問題を考える。

　**11.1　効率の良い検索を利用する
　　パフォーマンスを追求したい場合、効率の良いアクセスをオプティマイザに指示できる書き方を知る。

サブクエリを引数に取る場合、 $\mathrm{IN}$ よりも $\mathrm{EXISTS}$ を用いる。

- 遅い
SELECT *
FROM Class_A
WHERE id IN (SELECT id FROM Class_B)

- 速い
SELECT *
FROM Class_A A
WHERE EXISTS
           (SELECT *
               FROM Class_B B
             WHERE A.id = B.id)
　 $\mathrm{EXISTS}$ が速いのは以下の2つの理由からである：

もし結合キーにインデックスが張られていれば、インデックスを参照するのみである。

$\mathrm{EXISTS}$ は1行でも条件に合致する行を見つけたらそこで検索を打ち切る一方で、 $\mathrm{IN}$ は全表検索を行う。

サブクエリを引数を取る場合、 $\mathrm{IN}$ よりも結合を用いる

　結合を用いた方が $\mathrm{IN}$ よりも速くなる。
SELECT A.id, A.name
FROM Class_A A
INNER JOIN Class_B B
ON A.id = B.id
少なくとも一方のテーブルの $\mathrm{id}$ 列のインデックスが利用でき、サブクエリがなくなるために中間テーブルが作られない。

11.2　ソートを回避する

　 $\mathrm{DBMS}$ 内では頻繁にソートが暗黙裡に行われる。そのためソートを暗黙的に行う演算を理解しておくのが望ましい。

$\mathrm{GROUP\ BY}$ 句

$\mathrm{ORDER\ BY}$ 句

集約関数( $\mathrm{SUM},$ $\mathrm{COUNT},$ $\mathrm{AVG},$ $\mathrm{MAX},$ $\mathrm{MIN}$ )

$\mathrm{DISTINCT}$

集合演算子( $\mathrm{UNION},$ $\mathrm{INTERSECT},$ $\mathrm{EXCEPT}$ )

ウィンドウ関数( $\mathrm{RANK},$ $\mathrm{ROW}$ _ $\mathrm{NUMBER}$ など)

集合演算子の $\mathrm{ALL}$ オプションを上手く使う：
　 $\mathrm{SQL}$ は $\mathrm{UNION},$ $\mathrm{INTERSECT},$ $\mathrm{EXCEPT}$ を用いると、重複排除のためのソートを行なう。重複を気にしなくても良い場合、 $\mathrm{UNION\ ALL}$ を用いた方がパフォーマンスが良い。
$\mathrm{DISTINCT}$ を $\mathrm{EXISTS}で代用する$ ：
　 $\mathrm{DISTINCT}$ は重複を排除するためのソートを行なう。そのため $\mathrm{EXISTS}$ を代用した方がパフォーマンス上は望ましい。

SELECT item_no
FROM Items I
WHERE EXISTS (SELECT *
                          FROM SalesHistory SH
                        WHERE I.item_no = SH.item_no)