メモ

はじめに

第1章　DBMSのアーキテクチャ──この世にただ飯はあるか

1.1　DBMSのアーキテクチャ概要

• クエリ評価エンジン
• 実行計画を立てる
• パフォーマンスでめっちゃ大事

• バッファマネージャ
• メモリ領域の管理をしている

• ディスク容量マネージャ
• IO周りの制御

• トランザクションマネージャとロックマネージャ

• リカバリマネージャ
• バッグアップ取得などリカバリ関連の機能を管理

1.2　DBMSとバッファ

• この世にただ飯はあるか
• HDDは安くて大量のデータ保存ができるけど、アクセス速度が遅い
• メモリは逆で、トレードオフになっている

• DBMSと記憶装置の関係
• HDD
• DBMSではほぼこれ
• 二次記憶装置に分類される
• メモリを合わせて使うことも多い
• 現在はSSDが主流だけど、メモリよりは遅い？
• ファイルソートが以前ほど遅くない、みたいな差分がある
• AWSでデフォルト設定ではSSDになっている
• メモリ
• コストが高いため1台に乗っている量は多くない
• 全てのデータをメモリに載せることは通常できない
• バッファの活用による速度向上
• 頻繁にアクセスするデータがメモリにあると早い
• 実行時間の多くはストレージIOがほとんど
• パフォーマンス向上のためのメモリをバッファとかキャッシュと呼ぶ

• メモリ上の2つのバッファ
• データキャッシュ
• ディスクにあるデータの一部を保持するためのメモリ領域
• ログバッファ
• 更新処理の実行に関係
• 実際の更新処理は、一度ログバッファに貯めてから、あとでまとめてディスクに反映させる
• これはパフォーマンスのための仕組み

• メモリの性質がもたらすトレードオフ
• 揮発性とは
• ハードウェアの電源を落とすとメモリ上のデータは消える
• 揮発性の問題点
• ログバッファのデータが消えると、反映前の更新情報が消えることになる
• DBMSではログバッファからコミットするタイミングでログファイルを永続ストレージに書き込むことで整合性を担保している
• コミットのタイミングでは同期アクセスが必要になる
• 同期的にすると整合性は保たれるが、ディスクアクセスを待つ必要がありパフォーマンスが下がる
• ここにもトレードオフがある

• システムの特性によるトレードオフ
• データキャッシュとログバッファのサイズ
• 多くのデータベースはログバッファのサイズは小さく、データキャッシュのサイズが大きい
• 検索に重きを置いているため
• 検索と更新，大事なのはどっち
• システムによって何を優先するか考える
• 最近は割り当てを自動でやってくれる仕組みもあるが、限界もある

• もう一つのメモリ領域「ワーキングメモリ」
• いつ使われるか
• ソートとか、テーブルの結合でハッシュ結合が使われる際に使われる
• メモリ上で処理しきれないとディスクストレージを使う
• これが発生することを「TEMP落ち」という
• 不足すると何が起きるのか
• 極端に処理が遅くなる
• 逆に遅くなるだけで、処理は継続される
• 単体では発生しないが、複数のSQLを使った時だけ発生したりもする

1.3　DBMSと実行計画

• 権限委譲の功罪
• SQLを通じてしかアクセスできないことで、どのようにデータを探すのかに関与しない仕組みになっている
• 隠蔽することで生産性が上がった功績はあるが、実際にはHowを考慮してSQLを書く場面も多い

• データへのアクセス方法はどう決まるのか



• パーサ（parser）
• 構文チェックと、後続処理の効率化のためのステップ
• オプティマイザ（optimizer）
• 選択可能な実行計画を作成して、それぞれのコストを計算、低コストなプランに絞り込む
• 一般的に人間よりもコンピューターに任せた方が高速
• カタログマネージャ（catalog manager）
• 統計情報とも呼ぶ
• テーブルやインデックスの情報が格納されており、オプティマイザに提供する
• プラン評価（plan evaluation）
• オプティマイザの実行計画を選択する

• オプティマイザとうまく付き合う
• 基本的に中身を意識することはない
• 失敗するパターンがいくつかある
• カタログが適切に更新されていない場合など

• 適切な実行計画が作成されるようにするには
• カタログの更新にパフォーマンスにおいて重要
• 重い処理なので実行タイミングは工夫する
• PostgreSQLは
• MySQLは

1.4　実行計画がSQL文のパフォーマンスを決める

• 実行計画の確認方法
• 複雑なSQLでは最適なアクセスパスを生成できないことがある
• 実行計画を確認するコマンドが用意されている
• PostgreSQLは
• MySQLは

• テーブルフルスキャンの実行計画
• 操作対象のオブジェクト
• オブジェクトに対する操作の種類
• [Column]実行計画の「実行コスト」と「実行時間」
• 操作の対象となるレコード数
• カタログの値を参照している

• インデックススキャンの実行計画
• 操作の対象となるレコード数
• さっきと違ってRowsが1になった
• 操作対象のオブジェクトと操作
• フルスキャンよりインデックススキャンの方が一般に早い

• 簡単なテーブル結合の実行計画
• SQLが遅いのは大抵結合が絡むパターン
• 結合アルゴリズムは3種類ある
• Nested loops
• Sort Merge
• Hash
• オブジェクトに対する操作の種類
• 実行計画は階層になっている
• ネストが深い方が先に実行される

1.5　実行計画の重要性

• DBMS的にはユーザーが実行計画なんて見なくても使える形が理想だが、現実にはそうはいかない

第1章のまとめ

演習問題1

• [Column]いろいろなキャッシュ

第2章　SQLの基礎──母国語を話すがごとく

2.1　SELECT文

• SELECT句とFROM句
• 検索に使う
• みたいにテーブルからデータを取り出さない場合はFROMが不要

• WHERE句
• 条件指定に使う
• WHERE句のさまざまな条件指定
• 等しいとか、等しくないとか、以上とかがある
• WHERE句は巨大なベン図
• ANDとかORで表現できる
• INでOR条件を簡略化する
• 同じキーに対する指定には IN が使える
• NULL──何もないとはどういうことか
• とか で指定する必要がある
• NULL はデータの値ではないためらしい
• [Column]SELECT文は手続き型言語の関数
• 二次元表の入力に対して二次元表の出力を返す

• GROUP BY句
• グループ分けするメリット
• 集計計算ができる（数とか合計とか）
• ホールケーキを全部1人で食べたい人は？
• 分割しない場合は と書けるが、句自体を省略することも可能

• HAVING句
• 集約した結果に対しての絞り込みができる

• ORDER BY句
• デフォルトで並び順は不定
• 順番を指定しても、同じ値のレコード同士の並び順は不定

• ビューとサブクエリ
• ビューの作り方
• SELECT文をビューとして保存しておける
• ビューを他のテーブルと同じように FROM で参照できる
• 無名のビュー
• ビューはテーブルのようにふるまうが、実際はSELECTが入れ子として展開されている
• ビュー自体はデータを持たない
• サブクエリを使った便利な条件指定
• FROMに指定するSELECT文をサブクエリと呼ぶ
• INの中にSELECT文を書ける
• データで指定できるので、ハードコーディングにならない

2.2　条件分岐，集合演算，ウィンドウ関数，更新

• SQLと条件分岐
• CASE式の構文
• 単純CASE式と検索CASE式がある
• という構文で書ける
• CASE式の動作
• 式だから SELECTとか WHEREとか色々なところに書ける

• SQLで集合演算
• UNIONで和集合を求める
• 全ての列で全く同じ値は重複として削除される
• みたいに書けば削除されない
• INTERSECTで積集合を求める
• EXCEPTで差集合を求める
• UNION、INTERSECTと違って記述順で結果が変わることに注意

• ウィンドウ関数
• GROUP BY はカットと集約の二つの機能を持つ
• ウィンドウ関数はカットだけ
• 集約関数の後ろにOVER句を続けて記述する
• カットするキーを指定： PARTITION BY句
• ソートキーを指定：ORDER BY句
• みたいな
• ウィンドウ関数専用の RANK関数や ROW_NUMBER関数がある

• トランザクションと更新
• SQLは検索が主なので、更新機能はシンプル
• INSERTでデータを挿入する
• キーと値の順番は対応している
• 文字列はシングルクオートで囲む
• DELETEでデータを削除する
• 削除対象は行
• UPDATEでデータを更新する

第2章のまとめ

演習問題2

第3章　SQLにおける条件分岐──文から式へ

3.1　UNIONを使った冗長な表現

• UNIONによる条件分岐の簡単なサンプル
• UNIONを使うと実行計画が冗長になる
• UNIONを安易に使うべからず

• WHERE句で条件分岐させるのは素人

• SELECT句で条件分岐させると実行計画もすっきり

3.2　集計における条件分岐

• 集計対象に対する条件分岐
• UNIONによる解
• UNIONの実行計画
• 集計における条件分岐もやはりCASE式
• CASE式の実行計画

• 集約の結果に対する条件分岐
• UNIONで条件分岐させるのは簡単だが……
• UNIONの実行計画
• CASE式による条件分岐
• CASE式による条件分岐の実行計画

3.3　それでもUNIONが必要なのです

• UNIONを使わなければ解けないケース

• UNIONを使ったほうがパフォーマンスが良いケース
• UNIONによる解
• ORを使った解
• INを使った解

3.4　手続き型と宣言型

• 文ベースと式ベース

• 宣言型の世界へ跳躍しよう

第3章のまとめ

演習問題3

nkjzmメモ

• UNIONは複数のSELECT文を結合できて便利だけど、パフォーマンスが悪いがち（テーブルフルスキャンが発生するから）

• 大抵の場合はCASEで書けるしその方がパフォーマンスがいい

• SQL初心者は手続き的な「文」の方が取っつきやすいけど、SQLのスキームは宣言的な「式」なので、がんばって覚えよう

第4章　集約とカット──集合の世界

4.1　集約

• 複数行を1行にまとめる
• CASE式とGROUP BYの応用
• 集約・ハッシュ・ソート

• 合わせ技1本

4.2　カット

• あなたは肥り過ぎ？ 痩せ過ぎ？ ──カットとパーティション
• パーティション
• BMIによるカット

• PARTITION BY句を使ったカット

第4章のまとめ

演習問題4

nkjzmメモ

• SQLでは処理を行単位でなく集合単位で記述することがあるが、プログラミングとは発想の転換が必要

• GROUP BYとかすると結果が行で返ってくるので、CASEで条件以外をNULLにして該当データを1つだけにして、MAXとかで適当に取り出すといいよ

• GROUP BYでカットした部分集合をパーティションと呼ぶ

第5章　ループ──手続き型の呪縛

5.1　ループ依存症

• Q.「先生，なぜSQLにはループがないのですか？」

• A.「ループなんてないほうがいいな，と思ったからです」

• それでもループは回っている

5.2　ぐるぐる系の恐怖

• ぐるぐる系の欠点
• SQL実行のオーバーヘッド
• 並列分散がやりにくい
• データベースの進化による恩恵を受けられない

• ぐるぐる系を速くする方法はあるか
• ぐるぐる系をガツン系に書き換える
• 個々のSQLを速くする
• 処理を多重化する

• ぐるぐる系の利点
• 実行計画が安定する
• 処理時間の見積り精度が（相対的には）高い
• トランザクション制御が容易

5.3　SQLではループをどう表現するか

• ポイントはCASE式とウィンドウ関数
• [Column]相関サブクエリによる対象レコードの制限

• ループ回数の上限が決まっている場合
• 近似する郵便番号を求める
• ランキングの問題に読み替え可能
• ウィンドウ関数でスキャン回数を減らす
• [Column]インデックスオンリースキャン

• ループ回数が不定の場合
• 隣接リストモデルと再帰クエリ
• 入れ子集合モデル

5.4　バイアスの功罪

第5章のまとめ

演習問題5

nkjzmメモ

• ぐるぐる系はパフォーマンスが悪く、ガツン系に変更するには大規模改修になる

• CASEがifに、ウィンドウ関数（SUMとかMAX）がループにあたる

• ROWS BETWEENで対象レコードを限定できる

• 共通項式EXPLOSIONや入れ子集合で代替する選択肢もある

第6章　結合──結合を制する者はSQLを制す

6.1　機能から見た結合の種類

• クロス結合──すべての結合の母体
• [Column]自然結合の構文
• クロス結合の動作
• クロス結合が実務で使われない理由
• うっかりクロス結合

• 内部結合──何の「内部」なのか
• 内部結合の動作
• 内部結合と同値の相関サブクエリ

• 外部結合──何の「外部」なのか
• 外部結合の動作

• 外部結合と内部結合の違い

• 自己結合──自己とは誰のことか
• 自己結合の動作
• 自己結合の考え方

6.2　結合のアルゴリズムとパフォーマンス

• Nested Loops
• Nested Loopsの動作
• 駆動表の重要性
• Nested Loopsの落とし穴

• Hash
• Hashの動作
• Hashの特徴
• Hashが有効なケース

• Sort Merge
• Sort Mergeの動作
• Sort Mergeの特徴
• Sort Mergeが有効なケース

• 意図せぬクロス結合
• Nested Loopsが選択される場合
• クロス結合が選択される場合
• 意図せぬクロス結合を回避するには

6.3　結合が遅いなと感じたら

• ケース別の最適な結合アルゴリズム

• そもそも実行計画の制御は可能なのか？
• DBMSごとの実行計画制御の状況
• 実行計画をユーザが制御することによるリスク

• 揺れるよ揺れる，実行計画は揺れるよ

第6章のまとめ

演習問題6

nkjzmメモ

• クロス結合、内部結合、外部結合などがある

• 基本はNested Loopsで、次点でHashなどが使われる

• Nested Loopsは駆動表（結合元）が小さい方がパフォーマンスがいい
• 内部表が大きい方がインデックスによるループスキップの効果が大きい

• 結合アルゴリズムによって実行計画に変化が起きやすいので、結合を回避することは重要な戦略

第7章　サブクエリ──困難は分割するべきか

7.1　サブクエリが引き起こす弊害

• サブクエリの問題点
• サブクエリの計算コストが上乗せされる
• データのI/Oコストがかかる
• 最適化を受けられない

• サブクエリ・パラノイア
• サブクエリを使った場合
• 相関サブクエリは解にならない
• ウィンドウ関数で結合をなくせ！

• 長期的な視野でのリスクマネジメント
• アルゴリズムの変動リスク
• 環境起因の遅延リスク

• サブクエリ・パラノイア──応用版
• サブクエリ・パラノイア再び
• 行間比較でも結合は必要ない

• 困難は分割するな

7.2　サブクエリの積極的意味

• 結合と集約の順序
• 2つの解
• 結合の対象行数

第7章のまとめ

演習問題7

nkjzmメモ

第8章　SQLにおける順序──甦る手続き型

8.1　行に対するナンバリング

• 主キーが1列の場合
• ウィンドウ関数を利用する
• 相関サブクエリを利用する

• 主キーが複数列から構成される場合
• ウィンドウ関数を利用する
• 相関サブクエリを利用する

• グループごとに連番を振る場合
• ウィンドウ関数を利用する
• 相関サブクエリを利用する

• ナンバリングによる更新
• ウィンドウ関数を利用する
• 相関サブクエリを利用する

8.2　行に対するナンバリングの応用

• 中央値を求める
• 集合指向的な解
• 手続き型の解（1）──世界の中心を目指せ
• 手続き型の解（2）──2マイナス1は1

• ナンバリングによりテーブルを分割する
• 断絶区間を求める
• 集合指向的な解──集合の境界線
• 手続き型の解──「1行あと」との比較

• テーブルに存在するシーケンスを求める
• 集合指向的な解──再び，集合の境界線
• 手続き型の解──再び，「1行あと」との比較

8.3　シーケンスオブジェクト・IDENTITY列・採番テーブル

• シーケンスオブジェクト
• シーケンスオブジェクトの問題点
• シーケンスオブジェクトそのものに起因する性能問題
• シーケンスオブジェクトそのものに起因する性能問題への対策
• 連番をキーに使うことに起因する性能問題
• 連番をキーに使うことに起因する性能問題への対策

• IDENTITY列

• 採番テーブル

第8章のまとめ

演習問題8

nkjzmメモ

第9章　更新とデータモデル──盲目のスーパーソルジャー

9.1　更新は効率的に

• NULLの埋め立てを行う

• 逆にNULLを作成する

9.2　行から列への更新

• 1列ずつ更新する

• 行式で複数列更新する

• NOT NULL制約がついている場合
• UPDATE文を利用する
• MERGE文を利用する

9.3　列から行への更新

9.4　同じテーブルの異なる行からの更新

• 相関サブクエリを利用する

• ウィンドウ関数を利用する

• INSERTとUPDATEはどちらが良いのか

9.5　更新のもたらすトレードオフ

• SQLで解く方法

• SQLに頼らずに解く方法

9.6　モデル変更の注意点

• 更新コストが高まる

• 更新までのタイムラグが発生する

• モデル変更のコストが発生する

9.7　スーパーソルジャー病：類題

• 再び，SQLで解くなら

• 再び，モデル変更で解くなら

• 初級者よりも中級者がご用心

9.8　データモデルを制す者はシステムを制す

第9章のまとめ

演習問題9

nkjzmメモ

第10章　インデックスを使いこなす──秀才の弱点

10.1　インデックスと言えばB-tree

• 万能型のB-tree

• その他のインデックス

10.2　インデックスを有効活用するには

• カーディナリティと選択率
• [Column]クラスタリングファクタ

• インデックスの利用が有効かを判断するには

10.3　インデックスによる性能向上が難しいケース

• 絞り込み条件が存在しない

• ほとんどレコードを絞り込めない
• 入力パラメータによって選択率が変動する（1）
• 入力パラメータによって選択率が変動する（2）

• インデックスが使えない検索条件
• 中間一致，後方一致のLIKE述語
• 索引列で演算を行っている
• IS NULL述語を使っている
• 否定形を用いている

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

• 外部設計による対処──深くて暗い川を渡れ
• UI設計による対処

• 外部設計による対処の注意点

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

• データマートを採用するときの注意点
• データ鮮度
• データマートのサイズ
• データマートの数
• バッチウィンドウ

• インデックスオンリースキャンによる対処
• [Column]インデックスオンリースキャンとカラム指向データベース

• インデックスオンリースキャンを採用するときの注意点
• DBMSによっては使えないこともある
• 1つのインデックスに含められる列数には限度がある
• 更新のオーバーヘッドを増やす
• 定期的なインデックスのリビルドが必要
• SQL文に新たな列が追加されたら使えない

第10章のまとめ

演習問題10

nkjzmメモ