高速でわかりやすいクエリ
https://www.amazon.co.jp/SQL%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80%E2%94%80%E2%94%80%E9%AB%98%E9%80%9F%E3%81%A7%E3%82%8F%E3%81%8B%E3%82%8A%E3%82%84%E3%81%99%E3%81%84%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E6%9B%B8%E3%81%8D%E6%96%B9-WEB-DB-PRESS-plus/dp/4774173010
QL実践入門──高速でわかりやすいクエリの書き方を読んだので、備忘としてまとめる。
DBMSのアーキテクチャ
データキャッシュとログバッファ
DBMSがデータを保持するために使用するメモリは、データキャッシュとログバッファが代表的。
二つの割合はトレードオフだが、読み取りが多いシステムではデータキャッシュに多くメモリを割り当てる。
ワーキングメモリ
ソートやハッシュが必要な時に使われる。メモリが足りなくなった時に、ストレージのTemp領域にスワップすることをTemp落ちという。
- Oracle:PGA
- Postgres:work_mem
- SQL Server:Workspace Memory
実行計画
SQLに書かれているデータ（what）をどのようにストレージから取得するか（how）。
オプティマイザが、インデックスの有無、データの分散や偏り度合、DBMSの内部パラメータなどの条件を考慮して、最も低コストな一つの実行計画を立てる。
確認方法
| name | command |
|---|---|
| Oracle | set autotrace traceonly |
| SQL Server | set showplan_text on |
| DB2 | explain all with snapthot for sql文 |
| PostgreSQL | explain sql文 |
| MySQL | explain extended sql文 |
| Oracle, SQL Serverは上記コマンドの後に、SQL文を実行する。 |
実行計画の内容
- 操作対象のオブジェクト
- オブジェクトに対する操作の種類
- 操作の対象となるレコード数
条件分岐
WHERE, HAVINGで条件分岐させるのは素人
WHEREやHAVINGで条件分岐させたSELECT句を複数用意してUNIONでつなげるのは、つなげた分テーブルアクセスが増えて性能劣化につながる。
CASE式で、条件を分岐させて、テーブルアクセス回数を減らすべし。
しかし、マージされるSELECT文同士でテーブルが異なる場合は、UNIONが必要。
また、全条件にINDEXを貼っていればインデックススキャンとなるため、高速な場合もありうる。
集合
単元集合と要素
Group byでカットしたグループに対して、CASE文で集約キー以外の要素をSELECTしようとする際には、MAXかMINで集合を要素化する必要がある。
Group byしたSELECT句には定数か集約関数か指定した集約キーしか書けない。
SELECT id,
MAX(CASE WHEN data_type='A' THEN data_1 ELSE NULL END) AS data_1,
...
...
FROM table,
GROUP BY id;
集約とカット
Group byは内部で、ハッシュかソートを行う
実行計画上は、HashAggreage
十分なワーキングメモリが確保できないとスワップして遅延する。TEMP落ちに注意
カット
Group byは列名だけでなく、Caseによる条件分岐も使える（カット）。
カット基準となるキーを、Group by句とSelect句の両方に書く。
SELECT CASE WHEN age < 20 THEN 'child'
WHEN age BETWEEN 20 AND 69 THEN 'adult'
WHEN age >= 70 THEN 'old'
ELSE NULL END AS age_class,
COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN 'child'
WHEN age BETWEEN 20 AND 69 THEN 'adult'
WHEN age >= 70 THEN 'old'
ELSE NULL END;
ループ
ぐるぐる系とガツン系がある。
ぐるぐる系の欠点
ぐるぐる系は各行をループさせるため、一括処理のガツン系と比べて以下のオーバーヘッドがあり、実行速度が遅い。
- SQL文のネットワーク伝送
- データベースへの接続
- SQL文のパース
- 実行計画生成および評価
- 結果セットのネットワーク伝送
また、ループ一回の処理が単純なためコアでの分散やストレージでの分散処理ができない。
したがって、チューニングポテンシャルが低いのでぐるぐる系は避けるべし。
ぐるぐる系にしないために
ループ回数がわかっている場合
Case式とウィンドウ関数がポイント。
ウィンドウ関数で、ROWS BETWEENオプションを使うことで、カレントレコードの前後の行との比較を行える。
また、サブクエリで集約関数を使うとテーブルスキャンがサブクエリ分はっせいするため、ウィンドウ関数で回避する。
例えば、SELECTでの最小値を求めたい場合は、ウィンドウ関数のORDER BYで昇順にすることで、累計の最小値=全体の最小値となるため、ウィンドウ関数で十分。
ループ回数が不定の場合
隣接リストモデル
隣接リストモデルのような階層構造をSQLで実装するには、再帰共通表式
入れ子集合モデル
各行のデータを集合とみなして、階層構造を集合の入れ子関係で表現する。
例えば、左端と右端の座標を各行で持っており、新規ノード追加時には以下の式で自動的に座標を決める。
new_plft=(plft*2+prgt)/3
new_prgt=(pgrt*2+plfta)/3
結合
用いられる実行計画
- Nested Loops
- Hash
- Sort Merge
※MySQLはNested Loopsとその派生形しか選択肢がない。
Nested Loops
- 駆動表(外部表)各行に対して、内部表をループしてマッチした行を結合していく。
- アクセスされる行数は、駆動表行数×内部表行数
- 他の結合と比べて、メモリ消費しない。
- 駆動表は実行計画上では、同じインデントレベルにある、上に位置するテーブル。
駆動表が小さいほうが性能が良い理由
内部表の結合キーにインデックスがあるのが条件
内部表へのループアクセスが高速化されるため、駆動表が小さいと高速となる。
小さい駆動表×内部表の結合キーにインデックス
ただし結合キーが内部表に対して一意でない場合、結局ループ回数が多くなってしまうので高速でない。
駆動表の検索条件によって、内部表のヒットする件数が変わる場合、検索条件によって性能が変動しやっかい。
その場合は、駆動表を入れ替えて内部表を結合キーで一意に定まるようにして安定させるか、ハッシュ結合を使う。
Hash
- 小さい方のテーブルの結合キーをハッシュ値に変換してハッシュテーブルを作成し、もう一方のテーブルの結合キーがハッシュテーブルに存在するか調べる。
- Nested Loopsに比べるとメモリ消費が大きい。
- したがって、TEMP落ちの可能性がある。
- 等値結合でしか使えない。
有効な場合
- Nested Loopsで、相対的に十分に小さいテーブルが存在しない
- Nested Loopsで、内部表のヒット件数が多い場合
- Nested Loopsで、内部表にインデックスを貼れない場合
ただし、TEMP落ちが怖いため、スループットの高いOLTP(オンライントランザクション処理)には向かない。
夜間バッチなどに使うべし。
また、結合する両方のテーブルをフルスキャンするため、フルスキャンの時間にも気を付ける必要がある。
Sort Merg
- 両方のテーブルをソートする必要があるので、Nested Loopsよりメモリ消費が大きい。
- したがって、TEMP落ちの可能性がある。
- テーブル規模によるが、ハッシュよりメモリ消費が大きい。
- 等値結合だけでなく、不等号の結合もできるが、否定条件の結合はできない。
- SQL Serverは、クラスタ化インデックスの順番にテーブルが元からソートされているので、クラスタ化インデックスを結合キーで指定すれば、ソートをスキップした高速な結合ができる。
- ソートするため、片方のテーブルをすべてスキャンしたところで結合を終了できる。
ソートに時間とリソースを要する可能性があるため、ソートをスキップできるケース以外は、考慮に値しない。
Merge Join Cartesian
複数の結合をする際に、結合条件がないテーブル同士がクロス結合されてしまう場合がある。
実行計画上は、Merge Join Cartesian。
結合条件がないテーブルのサイズが小さいと判断されている場合に起こる。
回避するには、結果に影響しない結合条件を追加する。
サブクエリ
サブクエリと結合をウィンドウ関数で代替することでパフォーマンスを改善できる可能性がある。
サブクエリを使う場合は、結合対象のレコード数を事前に絞り込むことでパフォーマンスを改善できる可能性がある。
シーケンスオブジェクト
連番は、以下理由から、推奨ではない。
- 採番時に排他ロックを使用するため、ロック競合による劣化や、毎回のオーバーヘッドが発生するため
- CACHE、NOORDERオプションを有効にすることで、シーケンスの担保とトレードオフに性能を上げることは可能
- 連番をキーに使うことで、ホットスポットと呼ばれる、ストレージの特定の物理的ブロックだけI/O負荷が高くなることによる性能劣化が発生するため
- 逆キーインデックスや、不要な列を追加したインデックスによって対応は可能だが、SELECTが遅くなったりモデルとしてわかりにくいというデメリットがある
更新
行を項目ごとにSELECTして項目ごとの列にUPDATEするなど、元表の各項目ごとに(列)サブクエリを実行するのはフルスキャンが複数回起こるので、よろしくない。
以下を駆使してテーブルへのアクセスを減らす。
table1
| col_id | col_x | col_y |
|---|---|---|
| 1 | a | 222 |
| 1 | b | 223 |
| 2 | a | 224 |
| 2 | b | 225 |
| 3 | a | 226 |
| 3 | b | 227 |
↓
table2
| col_id | col_a | col_b |
|---|---|---|
| 1 | 222 | 223 |
| 2 | 224 | 225 |
| 3 | 226 | 227 |
行式
UPDATEでSET句に複数列をリスト化して指定する。これにより、リスト全体を一つの操作単位にできる。
2014年現在では、OracleとDB2のみ可能
スカラサブクエリ
行を項目ごとにCASE WHENで指定して値をSELECTする際に、帰ってくる値は、(222,null,null)のようにスカラではないため、MAX関数でスカラにしないと、エラーとなる。
update table2
set (col_a,col_b)
=(select max(case when col_x='a'
then col_y
else null end)
max(case when col_x='b'
then col_y
else null end)
from table1
where table1.id=table2.id);
インデックス
インデックスの分類
- B-treeインデックス
n分木 - ビットマップインデックス
カーディナリティの低い列に効果があるが、更新時のオーバーヘッドが大きいため、あまり頻繁な更新が行われないBI/DWH用途で使用される - ハッシュインデックス
選択率の高い等値検索以外では効果が薄い
インデックスを使うべき行
- カーディナリティ
値のばらつきを表す。最も低いのは、値が1種類しか存在しない - 選択率
絞り込めた行数/テーブル全体
カーディナリティが高く、選択率が低い(5~10%)行はインデックスを使うべき
インデックスが使えない条件
- 中間、後方一致のLIKE
where name LIKE '%名前%'
- インデックス列を演算を行っている
select * from table
wheer col * 1.1>100;
ただし、検索条件の右側で式を用いれば、インデックスが使用される。
wheer col >100/1.1
- IS NULLを使用している
- インデックス列に対して関数を使用している
- 否定形を使用している
インデックスが使えない場合の対処法
外部設計による対処以外なら以下。
データマート
特定のクエリで必要とされるデータだけを保持する、相対的に小さなサイズのテーブル
アクセス対象テーブルサイズを小さくすることでI/O量を減らすことが目的。
列を絞るだけでも、I/Oを減らせる。全列参照の場合などは、Group byで減らしてデータマート化することを考える。
大体、夜間バッチで元のテーブルと同期を取るため、鮮度は1日。
データマートの作成にもバッチウィンドウを圧迫するため、乱立はおすすめでない。
インデックスオンリースキャン
クエリの全列をカバーしているインデックスを作成すれば、フルスキャンの対象をテーブルからインデックスにできる。
上記のようなインデックスをカバリングインデックスと呼ぶ。
疑似的にカラム指向データベースを実現している。
欠点は以下。
- 1つのインデックスに含められる列数に限度がある
- 更新のオーバーヘッドが増える
通常のインデックスよりサイズが大きいため - 定期的なインデックスのリビルドが必要
インデックスのサイズに性能依存するため、定期的なサイズのモニタリングとリビルドを運用に組み込んでおく必要がある - SQLに新たな列が追加されたら使えない