書籍概要

WEB+DB PRESS plus

SQL実践入門
──高速でわかりやすいクエリの書き方

著者
発売日
更新日

概要

SQLはデータベース操作に特化した言語で，柔軟にデータを操作できます。ただし，独自のロジックに基づいているため，それを正しく理解しなければ，読みづらくパフォーマンスの出ないSQLになってしまいます。本書では，「条件分岐」「集約」「ループ」「結合」「更新」など日常的に実行する処理の良い書き方・悪い書き方を解説します。その際，データベース内部でどう処理が実行されているかを示す実行計画を読み解くことで，「なぜそう書くと効率が良いのか」「可読性や保守性が向上するのか」を実感を持って理解することを目指します。

こんな方におすすめ

  • データベースに触れる機会の多いWebアプリケーションエンジニア
  • データベースエンジニア

本書に関するお知らせ

本書に関連する記事を公開しております。

目次

  • はじめに
  • 謝辞
  • サンプルコードのダウンロード
  • 本書の構成

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

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

  • クエリ評価エンジン
  • バッファマネージャ
  • ディスク容量マネージャ
  • トランザクションマネージャとロックマネージャ
  • リカバリマネージャ

1.2　DBMSとバッファ

  • この世にただ飯はあるか
  • DBMSと記憶装置の関係
    • HDD
    • メモリ
    • バッファの活用による速度向上
  • メモリ上の2つのバッファ
    • データキャッシュ
    • ログバッファ
  • メモリの性質がもたらすトレードオフ
    • 揮発性とは
    • 揮発性の問題点
  • システムの特性によるトレードオフ
    • データキャッシュとログバッファのサイズ
    • 検索と更新，大事なのはどっち
  • もう一つのメモリ領域「ワーキングメモリ」
    • いつ使われるか
    • 不足すると何が起きるのか

1.3　DBMSと実行計画

  • 権限委譲の功罪
  • データへのアクセス方法はどう決まるのか
    • パーサ（parser）
    • オプティマイザ（optimizer）
    • カタログマネージャ（catalog manager）
    • プラン評価（plan evaluation）
  • オプティマイザとうまく付き合う
  • 適切な実行計画が作成されるようにするには

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

  • 実行計画の確認方法
  • テーブルフルスキャンの実行計画
    • 操作対象のオブジェクト
    • オブジェクトに対する操作の種類
    • [Column]実行計画の「実行コスト」と「実行時間」
    • 操作の対象となるレコード数
  • インデックススキャンの実行計画
    • 操作の対象となるレコード数
    • 操作対象のオブジェクトと操作
  • 簡単なテーブル結合の実行計画
    • オブジェクトに対する操作の種類

1.5　実行計画の重要性

第1章のまとめ

演習問題1

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

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

2.1　SELECT文

  • SELECT句とFROM句
  • WHERE句
    • WHERE句のさまざまな条件指定
    • WHERE句は巨大なベン図
    • INでOR条件を簡略化する
    • NULL──何もないとはどういうことか
    • [Column]SELECT文は手続き型言語の関数
  • GROUP BY句
    • グループ分けするメリット
    • ホールケーキを全部1人で食べたい人は？
  • HAVING句
  • ORDER BY句
  • ビューとサブクエリ
    • ビューの作り方
    • 無名のビュー
    • サブクエリを使った便利な条件指定

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

  • SQLと条件分岐
    • CASE式の構文
    • CASE式の動作
  • SQLで集合演算
    • UNIONで和集合を求める
    • INTERSECTで積集合を求める
    • EXCEPTで差集合を求める
  • ウィンドウ関数
  • トランザクションと更新
    • 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

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

4.1　集約

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

4.2　カット

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

第4章のまとめ

演習問題4

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

5.1　ループ依存症

  • Q.「先生，なぜSQLにはループがないのですか？」
  • A.「ループなんてないほうがいいな，と思ったからです」
  • それでもループは回っている

5.2　ぐるぐる系の恐怖

  • ぐるぐる系の欠点
    • SQL実行のオーバーヘッド
    • 並列分散がやりにくい
    • データベースの進化による恩恵を受けられない
  • ぐるぐる系を速くする方法はあるか
    • ぐるぐる系をガツン系に書き換える
    • 個々のSQLを速くする
    • 処理を多重化する
  • ぐるぐる系の利点
    • 実行計画が安定する
    • 処理時間の見積り精度が（相対的には）高い
    • トランザクション制御が容易

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

  • ポイントはCASE式とウィンドウ関数
    • [Column]相関サブクエリによる対象レコードの制限
  • ループ回数の上限が決まっている場合
    • 近似する郵便番号を求める
    • ランキングの問題に読み替え可能
    • ウィンドウ関数でスキャン回数を減らす
    • [Column]インデックスオンリースキャン
  • ループ回数が不定の場合
    • 隣接リストモデルと再帰クエリ
    • 入れ子集合モデル

5.4　バイアスの功罪

第5章のまとめ

演習問題5

第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

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

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

  • サブクエリの問題点
    • サブクエリの計算コストが上乗せされる
    • データのI/Oコストがかかる
    • 最適化を受けられない
  • サブクエリ・パラノイア
    • サブクエリを使った場合
    • 相関サブクエリは解にならない
    • ウィンドウ関数で結合をなくせ！
  • 長期的な視野でのリスクマネジメント
    • アルゴリズムの変動リスク
    • 環境起因の遅延リスク
  • サブクエリ・パラノイア──応用版
    • サブクエリ・パラノイア再び
    • 行間比較でも結合は必要ない
  • 困難は分割するな

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

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

第7章のまとめ

演習問題7

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

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

  • 主キーが1列の場合
    • ウィンドウ関数を利用する
    • 相関サブクエリを利用する
  • 主キーが複数列から構成される場合
    • ウィンドウ関数を利用する
    • 相関サブクエリを利用する
  • グループごとに連番を振る場合
    • ウィンドウ関数を利用する
    • 相関サブクエリを利用する
  • ナンバリングによる更新
    • ウィンドウ関数を利用する
    • 相関サブクエリを利用する

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

  • 中央値を求める
    • 集合指向的な解
    • 手続き型の解（1）──世界の中心を目指せ
    • 手続き型の解（2）──2マイナス1は1
  • ナンバリングによりテーブルを分割する
    • 断絶区間を求める
    • 集合指向的な解──集合の境界線
    • 手続き型の解──「1行あと」との比較
  • テーブルに存在するシーケンスを求める
    • 集合指向的な解──再び，集合の境界線
    • 手続き型の解──再び，「1行あと」との比較

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

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

第8章のまとめ

演習問題8

第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

第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

Appendix A：PostgreSQLのインストールと起動

Appendix B：演習問題の解答

サポート

ダウンロード

本書で使用するサンプルコードはダウンロードできます。圧縮ファイルをダウンロードしていただき，適宜解凍してご利用ください。

2018年3月22日まで公開していたサンプルコードに全角のスペースが入っていましたので，そちらを半角スペースに修正しました。

（2018年3月23日更新）

ダウンロード
サンプルコード

正誤表

本書の以下の部分に誤りがありました。ここに訂正するとともに，ご迷惑をおかけしたことを深くお詫び申し上げます。

（2016年9月5日更新）

初版第1刷をお持ちの方（第2刷で修正済み）

P.229　リスト8.7

誤 
studdent_id
正 
student_id

P.283　リスト9.20 10行目

誤 
INSERT INTO OrderReceipts VALUES (10000, 3, '牛肉',  2011/8/26');
正 
INSERT INTO OrderReceipts VALUES (10000, 3, '牛肉', '2011/8/26');

P.316　リスト10.18

誤 
CREATE INDEX CoveringIndex_2 ON Orders (receive_shop, order_id, receive_date);
正 
CREATE INDEX CoveringIndex_2 ON Orders (shop_name, order_id, receive_date);

P.318　図b

誤 物理的には行単位でひとまとまり
正 物理的には列単位でひとまとまり

P.338　リストB.2

誤 
INSERT INTO Sales2
SELECT company,
        year,
        sale,
        CASE SIGN(sale - (SELECT MAX(sale)  --  直前のレコードと売り上げを比較
                            FROM Sales SL2
                           WHERE SL1.company = SL2.company
                             AND SL1.year > SL2.year ))
        WHEN 0  THEN '='
        WHEN 1  THEN '+'
        WHEN -1 THEN '-'
        ELSE NULL END AS var
   FROM Sales SL1;
正 
INSERT INTO Sales2
SELECT company,
        year,
        sale,
        CASE SIGN(sale - (SELECT sale -- 直近の年の売り上げを選択
                            FROM Sales SL2
                           WHERE SL1.company = SL2.company
                             AND SL2.year =
                               (SELECT MAX(year)  -- 直近の年を選択
                                  FROM Sales SL3
                                 WHERE SL1.company = SL3.company
                                   AND SL1.year > SL3.year )))
        WHEN 0  THEN '='
        WHEN 1  THEN '+'
        WHEN -1 THEN '-'
        ELSE NULL END AS var
   FROM Sales SL1;

P.338　図B.8

誤 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    12 |   108 |3   (0)     | 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SALES       |     1 |     9 |2   (0)     | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004248 |     1 |       |1   (0)     | 00:00:01 |
|   4 |  TABLE ACCESS FULL           | SALES       |    12 |   108 |3   (0)     | 00:00:01 |
--------------------------------------------------------------------------------------------
正 
--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost  (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |    12 |   108 |2   (0)      | 00:00:01 |
|   1 |  SORT AGGREGATE                 |          |     1 |     9 |             |          |
|   2 |   TABLE ACCESS BY INDEX ROWID   | SALES    |     1 |     9 |1   (0)      | 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN            | PK_SALES |     1 |       |0   (0)      | 00:00:01 |
|   4 |     SORT AGGREGATE              |          |     1 |     6 |             |          |
|   5 |      FIRST ROW                  |          |     1 |     6 |1   (0)      | 00:00:01 |
|*  6 |       INDEX RANGE SCAN (MIN/MAX)| PK_SALES |     1 |     6 |1   (0)      | 00:00:01 |
|   7 |  TABLE ACCESS FULL              | SALES    |    12 |   108 |2   (0)      | 00:00:01 |
--------------------------------------------------------------------------------------------

P.338-339　「演習問題5の解答」3段落目

誤

相関サブクエリではSL1.company = SL2.companyという条件によって「同じ会社ならば」という条件を実現できるため、CASE式による条件分岐が1つ減ります。こちらは、典型的なSQLの集合指向の考え方です。相関サブクエリの中で非等値結合を使うことによってカレントレコードを起点とした集合を作るのは、ウィンドウ関数が導入される前のSQLでは定石の技術でした。

今、相関サブクエリの中では、SL3テーブルからMAX(year)を選択することであるレコードの直近の前年を取得し、これと同じ年のsale列をSL2から選択しています。これがすなわち「直近の年の売り上げ」になります。その条件の中心となるのが、SL1.year > SL2.yearという不等式です。カレントレコードはSL1.yearのほうですから、「それより小さい（＝昔の）年」という意味になります。

SL1.yearと、SL1.year > SL2.yearの条件に合致するレコード集合の対応をマッピングすると図B.9のようになります（太字の年は、集合の中の最大値を示します）。

正

相関サブクエリではSL1.company = SL2.company（およびSL1.company = SL3.company）という条件によって「同じ会社ならば」という条件を表現します。こちらは、典型的なSQLの集合指向の考え方です。相関サブクエリの中で非等値結合を使うことによってカレントレコードを起点とした集合を作るのは、ウィンドウ関数が導入される前のSQLでは定石の技術でした。

今、相関サブクエリの中では、同じ会社で、カレント行の年より前で直近の年MAX(year)をSL3テーブルから選択しています。この年における売り上げが、すなわちカレント行に対する「直近の年の売り上げ」になります。その条件の中心となるのが、SL1.year > SL3.yearという不等式です。カレントレコードはSL1.yearのほうですから、「それより小さい（＝昔の）年」という意味になります。

SL1.yearと、SL1.year > SL3.yearの条件に合致するレコード集合の対応をマッピングすると図B.9のようになります（太字の年は、集合の中の最大値を示します）。

P.339　図B.9

誤 
SL1.year > SL2.yearの条件に該当するレコード集合
正 
SL1.year > SL3.yearの条件に該当するレコード集合

初版第1～2刷をお持ちの方
P.87

誤 図3.11　UNIONの実行計画（PostgreSQL）
正 図3.11　CASE式の実行計画（PostgreSQL）

P.93　「UNIONを使ったほうがパフォーマンスが良いケース」（P.92）の4段落3行目

誤 図3.12のサンプルデータ
正 図3.16のサンプルデータ

P.94　注8

誤 このサンプルにように
正 このサンプルのように

初版第1～2刷をお持ちの方

P.218　リスト7.9

誤 
SELECT C.co_cd, C.district,
正 
SELECT C.co_cd, MAX(C.district),

補足情報

第8章のサンプルコードについて

（2018年3月5日更新）

MySQL8.0.2からウィンドウ関数がサポートされたことで，本書のサンプルコードはすべてMySQLでも動作するようになりました。

商品一覧