SQL実践入門を読みながらメモ

# はじめに
この記事は、[SQL実践入門](https://gihyo.jp/book/2015/978-4-7741-7301-6)を読んで要点を押さえつつ、後で見返す用のメモです。
あくまで主観的な要点整理となります。

# 実行計画
DBMSがどのようにデータへアクセスするかの計画。
SQLの遅延が起きた場合は、まず実行計画を確認。
各DBで実行計画の表示形式は異なるが以下の点は共通している。
- 操作対象のオブジェクト
テーブル以外もある。SQLで操作できるもの。
- オブジェクトに対する操作の種類

- 操作対象になるレコード数(Rows)
この数値は、実行時の値ではなく統計情報から取得している。

## オブジェクトに対する操作の種類

### シーケンススキャン
データにシーケンシャルにアクセスして全データを読み出す。
計算量は、O(N)。

### インデックススキャン
事前に作成したインデックスを利用したデータアクセス。
計算量は、O(logN)。

よって、データ量が少ない場合、検索速度は

**シーケンススキャン < インデックススキャン** となるが、

データ量が多くなるにつれて

**シーケンススキャン > インデックススキャン** となる。

# UNION
## 条件分岐はUNIONを使用せず、CASE式を使用する
同じテーブルに対する操作で、UNIONで条件分岐したSQLを実行した場合、シーケンススキャンをUNIONで結合した文の数だけ実行するため、読み取りコストがかかる。
SELECT句でCASE式を使用したの場合は、シーケンススキャン1回で済む。
SQLで条件分岐をしたい場合は、まずCASE式をSELECT句で使用することを考える。
UNIONは条件分岐のための構文ではない。

## UNIONを使うシーン
UNIONでマージするテーブルが異なるテーブルのとき。

# ループ処理

## 単純なSQLをループで回すデメリット

- SQLを実行する度にオーバーヘッドが発生する
- 並列分散が難しい
- DBの進化の恩恵を受けられない
- ループ処理で遅くなった場合、SQLのチューニングで早くできない

## ループ処理を早くするには

- 一括処理に書き換える
- 個々のSQLをチューニングする
今以上に早くするのが難しい場合が多い。

- 処理を多重化する
多重化を前提としたものなら望みあり。

## 単純なSQLをループで回すメリット
- 実行計画が安定する
- 処理時間の見積もり制度が高い
処理時間 = 一回あたりの実行時間 * 実行回数

- トランザクション制御が容易

SQLでループを表現する方法については必要になったときに調べる。

# 結合のアルゴリズム

## Nested Loops

```sql
SELECT * FROM driving_table INNER JOIN inner_table ON driving_table.id = inner_table.id
```
駆動表(`driving_table`)1行に対して、内部表(`inner_table`)を1行ずつスキャンし一致するものを返却する。
二重ループのイメージ。
実行時間は、`駆動表の行数 * 内部行の行数` に比例する。

### 性能を改善するには

- 駆動表に小さなテーブルを選ぶ
- 内部表の結合キーの列にインデックスが存在する

ことで性能を改善できる。
内部表のループをいかに短くできるかがポイント。

### 性能改善の要件を満たしているのに性能が良くならないケース

- 内部表のヒット件数が多い
検索条件によってヒット数が変わり、性能が変化してしまう。
対応策としては、駆動表にあえて大きいテーブルを指定することである。
または、Hashアルゴリズムを使用する。

## Hash

小さいテーブルをスキャンし、ハッシュテーブルを作成し、大きなテーブルをスキャンし、ハッシュテーブルと一致する値を検索する。

### 考慮する点
- 結合テーブルからハッシュテーブルを作成するため、メモリをNested Loopsより使用する
- ハッシュテーブルがメモリ内に収まらない場合、ストレージを使用するため速度が落ちる
同時実効性の高いOLTP処理には向かない

- 出力となるハッシュ値は順序を保証しないため、等値結合でのみ使用できる

### Hashが有効なケース

- Nested Loopsで適切な駆動表が存在しない場合（両テーブルの大きさがほとんど同じ）
- Nested Loopsで内部表のヒット数が多い場合
- Nested Loopsの内部表にインデックスがない（追加できない）場合


## Sort Merge

結合する両テーブルを結合キーでソートし、条件に一致する値を検索する。

### 考慮する点

- 両テーブルをソートするため、Nested Loopsよりメモリ消費量は多い
- Hashとは異なり、<>以外の不等号(<,>,<=,>=)を使用した結合で利用できる

- テーブルが結合キーで既にソートされていれば、ソートは省略できる
原理的には可能だが、DBMSの実装依存となる。

## 意図しないクロス結合

3つのテーブルを結合する場合、結合キーの条件がないテーブル同士を先に結合することで発生する。

```SQL
SELECT A.column1, B.column2, C.column3 FROM A
    INNER JOIN B ON A.id = B.id
    INNER JOIN C ON A.id = C.id
```
テーブルB,CがAより小さい場合に先にB,Cをクロス結合する場合がある。
また、検索条件によってヒットする件数が変わる場合にも発生することがある。
これを防止する方法として、 B,C間の結合条件を追加する方法がある。
ただし、**結合条件を追加しても結果が変わらない場合のみ**できる方法である。

```SQL
SELECT A.column1, B.column2, C.column3 FROM A
    INNER JOIN B ON A.id = B.id
    INNER JOIN C ON A.id = C.id AND C.id = B.id
```

## どのアルゴリズムが良いか

結合テーブルの大きさが
- 小 - 小
Nested Loops

- 小 - 大
Nested Loops
小が駆動表、大はインデックスのある内部表。

- 大 - 大
Hash。結合キーがソート済みであればSort Merge。

を使用する。

DBMSによっては、ヒント句によって使用するアルゴリズムの制御が可能。

# サブクエリ

## テーブル、ビュー、サブクエリの違い

- テーブル
永続的でデータを保存する。

- ビュー
永続的だがデータを保存しないため、アクセスのたびにSELECT文が発行される。

- サブクエリ
非永続的なため、生存期間はSQL実行中のみ。


## サブクエリの問題点

### 計算コストが増える

サブクエリにアクセスするたびにSELECT文を実行するため、その分の計算コストが増える。
サブクエリが複雑であるほど影響は大きい。

### I/Oコストがかかる

サブクエリの結果をメモリ上に保持するため、メモリが枯渇し、ストレージ領域を使用してしまうと速度が遅くなる。

### 最適化されない

サブクエリは、制約やインデックスなどの情報を持たないため、最適化されない。


---

同一テーブルで結合や相関サブクエリを使用している場合は、ウィンドウ関数の使用を検討する。
上記の場合、テーブルのスキャン回数が複数回になるため。

## サブクエリが有効な場面

結合と集約が絡むSQLに対して、集約をサブクエリで行い結合対象の行数を減らした状態で結合する方がパフォーマンスは良い。

# 順序を扱いたいとき

順序や連番を持たないテーブルに対して順序や連番を振る方法。
方法として、ウィンドウ関数、相関サブクエリを使用する方法があるが、パフォーマンス的にはウィンドウ関数の方が良い。
テーブルのスキャン回数が少ないため。

応用編については省略。

## シーケンスオブジェクト、IDENTITIY列、採番テーブル

基本的には、どの機能も使用しない方が良い。
IDENTITIY列よりはシーケンスオブジェクトを使用する。

### シーケンスオブジェクトの問題点

- 構文がDBMS依存で移植性がない
- システムで自動的に払い出す値のため、実際のエンティティの属性ではない
- **パフォーマンス問題を引き起こす**

#### パフォーマンス問題を起こす理由①

シーケンスオブジェクトが払い出す連番には以下の特性がある。
- 一意性
- 連続性
- 順序性

この特性を満たすため、排他制御が必要になる。
そのため、ユーザーが`NEXT VALUE`を取得するときのイメージは以下のようになる。
1. シーケンスオブジェクトを排他ロックする
1. `NEXT VALUE`を取得する。
1. `CURRENT VALUE`が更新される。
1. シーケンスオブジェクトの排他ロックを解除する。

上記の間、他のユーザーはシーケンスオブジェクトにアクセスできないため、同時実行時にパフォーマンスが低下する。
チューニング方法として、`CHACE`,`NOORDER`オプションを使用する方法くらいしかない。

#### パフォーマンス問題を起こす理由②

連番のような値の近いデータを連続してINSERTすると、物理的にも同じ領域に格納され、特定の物理ブロックのみI/O負荷があがり、パフォーマンスが低下する。
連続した値を扱う場合に発生し、シーケンスオブジェクトに限ったものではないが、シーケンスオブジェクトを使用した場合に顕著に見られる。

チューニング方法として、連続した値を追加する場合でも分散して格納するような仕組みを使用する方法があるが、範囲検索でのI/Oが増加し、SELECTでのパフォーマンスが低下する可能性がある。

### IDENTITIY列

性能的・機能的にシーケンスオブジェクトの劣化版。
シーケンスオブジェクトがテーブルとは独立しているのに対し、IDENTITIY列は特定のテーブルに結びついている。
`CHACE`,`NOORDER`といったオプションがない。

### 採番テーブル

連番を払い出すための専用のテーブル。
シーケンスオブジェクトやIDENTITIY列がなかった頃の自動採番手段。
シーケンスオブジェクトのロックメカニズムをテーブルで表現したもののため、`CHACE`,`NOORDER`オプションといったチューニング手段がない。
使用しているSQLも単純なため、チューニング余地もない。

# 更新

例に対しての解決策の提示のため、省略。
必要になったら読み返す。

更新の効率化は、行式、サブクエリ、CASE式、MERGE文を使用することを検討する。
SQLでの解決ではなく、モデルを変更した方が問題を解決しやすいこともある（モデルの変更ができるなら）。

# インデックス

## インデックスが有効かの判断基準

- カーディナリティが高い

- 選択率が低い（5〜10%前後）
少ない行に絞り込めること。

上記を満たす列に対しては、インデックスを作る価値はあるかもしれない。

### カーディナリティとは

値のばらつき具合を表す概念。
最も高いのは、すべての行に対して値が異なる一意キー。
最も低いのは、値が１種類しかない列。

### 選択率とは

特定の列の値を選択したとき、行をテーブルの全行からどの程度絞り込めるかを表す概念。
一意キーの中の人つを指定した場合、全行が100行なら選択率は1/100 = 1%

## インデックスによる性能改善ができないパターン

- 絞り込み条件のないSQL

- 絞り込み条件はあるが、ほとんど絞り込めないSQL
  - 条件のキーに該当するレコードが多い
  - 入力値が条件の時のみ該当するレコードが多い


- インデックスが使えない検索条件
  - 中間一致、後方一致の`LIKE`述語
  前方一致のみインデックスが使用できる（`'検索条件%'`）

  - 索引列で計算をしている
  - `IS NULL`述語を使用している
  - 索引列に関数を使用している
  - 否定形（`<>`,`!=`,`NOT IN`）を用いている

## インデックスが使用できない場合どうするか

### 外部設計による対処

UI的にインデックスの使用できないクエリが発行されないようなUIにする。
検索画面がインデックスが使用できない条件を設定できないように設計する。

### データマートによる対処

特定のクエリ郡で使用されるデータを保持する元のテーブルより小さいテーブルを用意する。

#### データマートの注意点

- データ鮮度
オリジナルテーブルとデータを同期する必要があるため、データの鮮度が重要な要件では使用できない。

- データマートのサイズ
小さいテーブルによってI/Oを減らすのが目的のため、オリジナルとテーブルの大きさが変わらない場合、効果がない。
`GROUP BY`句であらかじめ集約したものをデータマートにするのはサイズや計算処理が省略できるため良い。

- データマートの数
データマートが増えすぎるとストレージ容量の圧迫や管理しきれなくなるため注意。

- バッチ関連への考慮
テーブルの同期処理や同期処理の統計情報の収集も考慮に入れる必要がある。

### インデックスオンリースキャンによる対処

#### インデックスオンリースキャンとは

絞り込み条件のないSQLの検索列を対象としたインデックス（カバリングインデックス）を作成し、検索時にそのインデックスだけをスキャン対象とする検索。
カバリングインデックスにより、テーブルへのアクセスをスキップできる。
データマートの同期問題を解消している。

### インデックスオンリースキャンの注意点

- DBMSによっては使用できないものもある
- 1つのインデックスに含められる列数に上限がある
- 更新のオーバーヘッドが増える
インデックスのサイズが大きくなる傾向があるため、検索の高速化はできるが、更新が遅くなる。

- 定期的なインデックスのリビルドが必要
インデックスのサイズに検索性能が比例するため。

- SQL文に新たな列が追加されると使えない

便利な点もあるが、注意しなければいけない点も多いためご利用は計画的に。


# 終わりに
応用編や特殊なケースに対しては、必要になった時に見返せば良さそう。
一番身近な結合に関しては、コーディング時に意識しながらできると良さそう。
クエリの遅延が起こったときの引き出しもある程度できたのでよかった。