「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

SQL中級者になる!(その11/12)

初めに

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

power-of-awareness.com

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

を参考に、更なる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)

11.3 極値関数でインデックスを用いる

 \mathrm{SQL}\mathrm{MAX}\mathrm{MIN}という2つの極値関数を持っており、これらはいずれもソートを行なう。しかし引数の列にインデックスが存在すれば、そのインデックスのスキャンのみで済ませることができる。

11.4 WHERE句に書けることはHAVING句に書かない

 \mathrm{WHERE}句の方が\mathrm{HAVING}句に同じ制約を書くよりも効率的に動作する。

11.5 そのインデックス、本当に使われていますか?

 以下の場合、インデックスが有効に利用されない:

  • 索引列に加工(計算)を行なっている
  • インデックス列に\mathrm{NULL}が用いられている
  • 否定形(<>,!=,\mathrm{NOT\ IN})を用いている
  • \mathrm{OR}を用いている
  • 複合検索のときに列の順番を間違えている
  • 後方一致または中間一致の\mathrm{LIKE}述語を用いている
  • 暗黙の型変換を行なっている

11.6 中間テーブルを減らせ

 サブクエリといった中間テーブルを可能な限り避けるとパフォーマンス向上につながる。

  • \mathrm{IN}述語で複数のキーを用いる場合は論理演算子で結合するなど一か所にまとめる
  • 集約よりも結合を先に行う
  • ビューの利用は控える

11.7 まとめ

  • \mathrm{SQL}のチューニングでは、ボトルネックを見つけ、そこを重点的に解消すること。
  • \mathrm{IN}にサブクエリを取る場合、\mathrm{EXISTS}または結合に書き換える。
  • インデックスを利用するとき、「左辺は裸」とすることが基本である。
  • \mathrm{SQL}は暗黙のソートを行なうことが度々あるため注意する。
  • 余計な中間テーブルは可能な限り減らす。
  • レコード数を絞れる条件は早期段階で記述する。
プライバシーポリシー お問い合わせ