はじめに
『SQL 実践入門 高速でわかりやすいクエリの書き方』を読んだので、学んだことの整理・備忘のために個人的に「これもっと早く知っておきたかった」と思ったことをまとめました。1年前の私のような人の目に止まって、SQL/DBMS について学ぶきっかけにもなれば幸いです。
1. 統計情報は大事
- SQL において、どこにあるデータをどのように探すかという手続き (データアクセスの方法) は一切現れない
-
クエリ評価エンジン (の中のオプティマイザ) が、以下のような 統計情報 をもとに、最適なデータアクセスの方法(実行計画)を決める
- 各テーブルのレコード数
- 各テーブルの列数と列のサイズ
- 列内の一意な値の個数(カーディナリティ)
- 列内の値のヒストグラム
- 列内にある
NULLの数 - インデックス情報
- この実行計画が、SQL のパフォーマンスを決める
Note:
実行計画は、実際のテーブルの内容ではなく、統計情報にもとづいて作成 される。
よって、テーブルに対してデータの挿入/更新/削除がおこなわれたのに、統計情報が更新されていないと、オプティマイザは古い情報をもとに実行計画を作ろうとしてしまう。
適切な実行計画が作成されるようにするには？
テーブルのデータが大きく更新されたら、統計情報もセットで更新する。
2. 結合のアルゴリズム
オプティマイザが選択可能な結合アルゴリズムは大きく以下の 3 つ。 (順に頻繁に選択される)
- Nested Loops
- 利点
- 「小さな駆動表」 + 「内部表のインデックス」の条件下で高速
- メモリやディスクの消費が少なく、OLTP （Online Transaction Processing） に適している
- 非等値結合でも使用できる
- 欠点
- 大規模テーブル同士の結合に不向き
- 内部表のインデックスが使えなかったり、内部表の選択率が高かったりすると低速
- 利点
- Hash
- 利点
- 大規模テーブル同士の結合に適している
- 欠点
- メモリ消費が大きく、 OLTP に不向き
- メモリ不足の場合は TEMP 落ちが発生する
- 等値結合のみ で使用できる
- 利点
- Sort Merge
- 利点
- 大規模テーブル同士の結合に適している
- 非等値結合でも使用できる
- 欠点
- メモリ消費が大きく、 OLTP に不向き
- メモリ不足の場合は TEMP 落ちが発生する
- データが ソート済でなければ あまり効率的でない
- 利点
結合アルゴリズムの選択方針
- 小テーブル - 小テーブル: 何を選んでも大差ない
- 小テーブル - 大テーブル: Nested Loops
- 小テーブルを駆動表
- 大テーブル (内部表) にインデックス
- 内部表の対象行が多い場合は、駆動表とひっくり返すか Hash を検討する
- 大テーブル - 大テーブル: まずは Hash 。 結合キーがソート済なら Sort Merge
結合アルゴリズム (実行計画) の制御方法
そもそも結合アルゴリズムの選択はオプティマイザがおこなうのでは？
→ ヒント句 でユーザが制御できる (どこまで制御できるかは DBMS による)
実行計画をユーザが制御することによるリスク
データ量やカーディナリティは運用を続けていく中で変化するため、ある時点において最適な実行計画が、別の時点においてはそうでなくなることがある。
3. メモリ領域の種類
DBMS は、パフォーマンス向上のため (ディスクへの I/O を回避するため)、データの一部をメモリに乗せている。
データキャッシュ
ディスクにあるデータの一部を保持するためのメモリ領域。もし、 SELECT したいデータがすべてこの中に収まっていれば非常に高速なレスポンスを期待できる。
ログバッファ
SQL の実行と実際のディスクへの更新処理とを非同期におこなうために、更新情報を溜めておく領域。
ワーキングメモリ
ソートやハッシュなどの処理に利用する作業領域。ワーキングメモリが溢れると、ディスク上の一時領域が使用されることになり、レスポンスは悪くなる。
Note:
「データがメモリ内に収まっているときは非常に高速なのに、メモリから溢れた瞬間に一気に遅くなる」という極端な劣化が突然起きてしまうところが厄介。
4. 「困難の分割」は必ずしも正義ではない
- サブクエリは困難を分割できる便利な道具だが、パフォーマンスを悪化させることもある
- サブクエリ + 結合 → ウインドウ関数で代替
そもそもテーブル/ビュー/サブクエリの違いとは
- テーブル: 永続的かつデータを保持する
- ビュー: 永続的だがデータは保持しないため、アクセスのたびに SELECT 文が実行される
- サブクエリ: 非永続的なのでスコープが SQL の実行中に限られる
サブクエリの問題点
サブクエリの問題点は、サブクエリが実体的なデータを持っていないことに起因する。
- サブクエリの計算コストが上乗せされる (テーブルと比べて)
- データの I/O コストがかかる
- 計算したデータがメモリ上に収まればこのオーバーヘッドは小さい
- メモリに収まらなければ、ファイルに書き出す (TEMP 落ち)
- 最適化を受けられない
- 制約インデックスなどのメタ情報が一切ないため、オプティマイザがクエリを解析するために必要な情報が、サブクエリからは得られない
(例題)サブクエリ + 結合をウインドウ関数で代替
- 顧客の購入明細を記録する、以下のような購入明細テーブルがあるとする
-
seq列は、顧客の古い購入ほど小さな値が振られている - 顧客ごとに最小の
seqの金額を求める (= 一番古い購入履歴を見つける)
| customer_id | seq | price |
|---|---|---|
| A | 1 | 500 |
| A | 2 | 1000 |
| A | 3 | 700 |
| B | 5 | 100 |
| B | 6 | 5000 |
| B | 7 | 300 |
| B | 9 | 200 |
| B | 12 | 1000 |
| C | 10 | 600 |
| C | 20 | 100 |
| C | 45 | 200 |
| C | 70 | 50 |
| D | 3 | 2000 |
サブクエリを使った場合
顧客ごとに最小の seq の値を保持するサブクエリ (R2) を作り、それと本体とを結合する。
SELECT R1.customer_id, R1.seq, R1.price
FROM Receipts R1
INNER JOIN (
SELECT customer_id, MIN(seq) AS min_seq
FROM Receipts
GROUP BY customer_id
) R2
ON R1.customer_id = R2.customer_id
AND R1.seq = R2.min_seq
パフォーマンス上の欠点
- サブクエリは多くの場合、(メモリにせよディスクにせよ)一時的な領域に確保されるため、オーバーヘッドが生じる
- サブクエリはインデックスや制約の情報を持っていないので、最適化が受けられない
- 結合を必要とするためコストが高く、かつ実行計画変動のリスクが発生する
- Receipts テーブルへのスキャンが 2 回必要となる
ウインドウ関数を使った場合
ROW_NUMBER() で行に通番を振り、常に最小値を 1 にすることで、 seq 列の最小値が不確定という問題に対処。
SELECT customer_id, seq, price
FROM (SELECT customer_id, seq, price,
ROW_NUMBER()
OVER (PARTITION BY customer_id
ORDER BY seq) AS row_seq
FROM Receipts) WORK
WHERE WORK.row_seq = 1
5. SQL の主役は「文」ではなく「式」
SELECT, FROM, WHERE, GROUP BY, HAVING, ODER BY,... に記載するのは、すべて 式。
列名だけ：たまたま演算子がない式、定数だけ：たまたま変数も演算子もない式
たとえば、 WHERE 句に CASE 式 を与えることもできる。
SELECT ...
FROM ...
WHERE CASE WHEN a = 1 THEN value1
WHEN a = 2 THEN value2
WHEN a = 3 THEN value3
ELSE NULL = 'VALUE'
6. スカラサブクエリ
- 戻り値が単一の値になるクエリのこと
- 戻り値が単一なので、 SELECT 句や CASE 式の引数、 UPDATE 文の SET の右辺などに記述できる
SELECT E.emp_id, E.emp_name, E.dept_id,
(SELECT D.dept_name
FROM Departments D
WHERE E.dept_id = D.dept_id) AS dept_name
FROM Employees E