2019-01-14

達人に学ぶ SQL徹底指南書第2部前半 13章〜17章

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2018/10/11
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

第2部リレーショナルデータベースの世界

前半
歴史や理論の説明が主であり、SQL文は登場しない（座学）
読み物として、肩肘抜いて
- 「SQL脳」を獲得するのに役立ちそう

ブレイクスルーになったか
- No
SQLの否定から入っている
- 逆説的にSQLの影響力の強さをうかがい知れる
RDBのパフォーマンス問題への対策
- データモデルを単純化し、複雑なデータ操作を制限する
  - →KVS(Key-Value Store)
    - いわゆる連想配列
- シングルボトルネックポイントをなくしてスケールアウト可能にする
  - クラスタ構成
トレードオフ
- NoSQLはRDBと相互補完的関係にある
  - RDBに適さない用途にNoSQLが使えるかもね

14 なぜ"関係"モデルという名前なの？

関係という言葉について説明
- 高階の関係
- 現状のRDBの限界

まとめ

"表"モデルではなくて？
- 表より関係の方が抽象度が高い
定義域
- enumみたいな
関係の関係
- 高階の関係
  - テーブルの中にテーブルがあるみたいな
- 現在対応しているDBMSはない
  - が、その方向に向かって進化している

関係モデルで使用される公式用語と日常的な言葉との対応

形式的な関係モデルの用語	非形式的な日常語
関係 (relation)	表またはテーブル
組 (tuple)	表またはレコード
濃度 (cardinality)	行数
属性 (attribute)	列またはフィールド
次数 (degree)	列数
定義域 (domain)	列の取り得る値の集合

15 関係に始まり関係に終わる

関係モデルからRDBの柔軟性を説明
- 数学的に厳密な基礎付けがあってのこと

まとめ

閉包性 (closure property)
- 演算子の入力と出力が共に関係になる
- 関係の世界が閉じていることを保証する
UNIXのパイプの概念に似ている
- なんでもファイル
SQLは数学的に厳密な基礎付けを持っている
- SQLは体の条件もクリアしている
- エレガントさや機能性を獲得している

代数構造

群 (group)
- 加算と減算（または乗算と除算）について閉じている
環 (ring)
- 加算・減算・乗算について閉じている
体 (field)
- 加算・減算・乗算・除算について閉じている
- つまり、四則演算が自由にできる

16 アドレス、この巨大な怪物

アドレス／ポインタ撲滅への戦い

まとめ

SQLでは基本思想の段階からアドレス／ポインタを排除することに成功した
- 変数を使わない
- 関数型言語との共通性

この記事書いている人の雑感

まだ綺麗事に聞こえる
アドレスを考える必要がなくなったこと、それ自体は良い
代わりに、また別の「変なこと」を考える必要がある
- 実行計画を見て、内部処理を想像／修正する必要がある
- 内部処理を隠蔽しているから／まだオプティマイザが賢くない
いうほど素晴らしいかね…？
- 原理的には最善になるが、今はそうなれていないのがエンジニアを苦しませる

17 順序をめぐる冒険

第2版で新録
ウィンドウ関数の登場はなぜ遅れたか
- SQLの思想から辿る

各勢力の主張

リレーショナル伝統主義保守派
- 行に順序などない（理論的には）
- だからウィンドウ関数など不要
リレーショナル無政府主義左派
- 分析的計算は今時必要なんだから入れようぜ
- 現状の仕様でそれをやろうとすると自己結合が必要になってひどいんだ
  - エレガントでない、パフォーマンスの最適化も困難
この本の著者の立ち位置
- 入れたら便利だったよね

村上春樹…？

以上

2019-01-07

達人に学ぶ SQL徹底指南書 12章 SQLプログラミング作法

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2018/10/11
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

12 SQLプログラミング作法

汚いコードに苦しめられたか…

はじめに
テーブル設計
- 名前と意味
- 属性と列
コーディングの指針
- コメント
- インデント
- スペース
大文字と小文字
- カンマ
- ワイルドカードは使わない
- ORDER BYで列番号は使わない
- 標準語を話そう
  1. 実装依存の関数・演算子を使わない
  2. 結合には標準の構文を使う
- 左派と右派
- 相関サブクエリを追放せよ
- FROM句から書く
まとめ
- 参考文献
  1. Joe Celko『Joe Celko's SQL Programming Style』
  2. Brian W.Kernighan, P.J.Plauger『プログラム書法第2版』
  3. Steve McConnell『CODE COMPLETE 第2版完全なプログラミングを目指して』
- 参考文献
  1. Brian W.Kernighan, Rob Pike『プログラミング作法』
  2. Bill Karwin『SQL アンチパターン』
  3. ミック『達人に学ぶDB設計徹底指南書』

はじめに

誰が見ても読みやすく間違いの少ないコードを書くにはどうすればいいか
SQLコーディングスタイル

テーブル設計

名前と意味

名が体を表す具体的な名前を付ける

属性と列

1つの列に複数の意味を持たせない

コーディングの指針

なるべくコメントはあった方がよい
- 短いコードに多くの処理が凝縮されているので、コードを見て処理がわかりにくいため
- 段階的な実行デバッグができないため
コメントの種類
- 1行コメント（--）
- 複数行コメント（/**/）

インデント

こんなときに1段下げる
- サブクエリ
- SELECT句やGROUP BY句において列を複数指定するとき
句の先頭合わせと末尾合わせ

-- 先頭合わせ
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

-- 末尾合わせ
SELECT
  FROM
 WHERE
 GROUP BY
HAVING
 ORDER BY

スペース

-- ○良い見本
SELECT col_1
  FROM tbl_A A INNER JOIN tbl_B B
    ON A.col_3 = B.col_3
 WHERE ( A.col_1 >= 100 OR A.col_2 IN ( 'a', 'b' ) );

-- ×悪い見本
SELECT col_1
  FROM tbl_A A INNER JOIN tbl_B B
    ON A.col_3=B.col_3
 WHERE (A.col_1>=100 OR A.col_2 IN ('a','b'));

SELECT
    col_1
FROM
    tbl_A A INNER JOIN tbl_B B
    ON A.col_3 = B.col_3
WHERE
    (A.col_1 >= 100 OR A.col_2 IN ('a', 'b'));

大文字と小文字

予約語は大文字
列名やテーブル名は小文字

-- ○メリハリがあって読みやすい
SELECT col_1, col_2, col_3, COUNT(*)
  FROM tbl_A
 WHERE col_1 = 'a'
   AND col_2 = ( SELECT MAX(col_2)
                   FROM tbl_B
                  WHERE col_3 = 100 )
 GROUP BY col_1, col_2, col_3;

-- ×平たんで読みにくい：オール小文字
select col_1, col_2, col_3, count(*)
  from tbl_a
 where col_1 = 'a'
   and col_2 = ( select max(col_2)
                   from tbl_b
                  where col_3 = 100 )
 group by col_1, col_2, col_3;

-- ×平たんで読みにくい：オール大文字
SELECT COL_1, COL_2, COL_3, COUNT(*)
  FROM TBL_A
 WHERE COL_1 = 'A'
   AND COL_2 = ( SELECT MAX(COL_2)
                   FROM TBL_B
                  WHERE COL_3 = 100 )
 GROUP BY COL_1, COL_2, COL_3;

カンマ

前カンマ

SELECT col_1
      ,col_2
      ,col_3
      ,col_4
  FROM tbl_A;

- 前カンマの利点
    1. 最後の要素を行ごと削除してもエラーにならない
    2. 矩形選択機能を適用しやすい
        - カンマがどの行でも同じ列位置に来るため

ワイルドカードは使わない

コードの可読性が下がる
仕様変更に弱くなる

× SELECT * FROM SomeTable;
○ SELECT col_1, col_2, col_3, ... FROM SomeTable;

ORDER BYで列番号は使わない

コードの可読性が下がる
仕様変更に弱くなる
保守性が下がる
- 「将来削除されるべき機能」のため

× SELECT col_1, col_2 FROM SomeTable ORDER BY 1, 2;
○ SELECT col_1, col_2 FROM SomeTable ORDER BY col_1, col_2;

標準語を話そう

実装依存の関数・演算子を使わない
- DECODE（Oracle）
- IF（MySQL）
- NVL（Oracle）
- STUFF（SQL Server）
結合には標準の構文を使う
- INNERやCROSSといったキーワードで結合の種類を示す
- 結合条件をON句に分離して書く

左派と右派

左外部結合の方がわかりやすい

相関サブクエリを追放せよ

可読性が下がる
パフォーマンスが下がる
デバッグが難しい
ウィンドウ関数で代替できるかも

FROM句から書く

SQLの実行順序に沿って書こう
SQLの実行順序
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- （ORDER BY）
結合後の列名などわからないので、FROMから考えるしかないと思うのだが

まとめ

読みにくいコードは何物も、誰も解決してくれない
パフォーマンスチューニングに負けず劣らず重要
- プログラミングはコミュニケーションの一種
参考文献（章末）
1. Joe Celko『Joe Celko's SQL Programming Style』
2. Brian W.Kernighan, P.J.Plauger『プログラム書法第2版』
3. Steve McConnell『CODE COMPLETE 第2版完全なプログラミングを目指して』
参考文献（本文中）
1. Brian W.Kernighan, Rob Pike『プログラミング作法』
2. Bill Karwin『SQL アンチパターン』
3. ミック『達人に学ぶDB設計徹底指南書』

以上

2019-01-07

達人に学ぶ SQL徹底指南書 11章 SQLを速くするぞ

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2018/10/11
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

11 SQLを速くするぞ

この記事書いている人の雑感

本章はあくまでSQLのみの最適化
テーブル構成やインデックスの検討、統計情報、設定（メモリやジャーナルのサイズ等）、クエリログ等気にする項目は山ほどある
本章は今までの内容の集大成（読み解くのにそれほど苦労しないと思う）
- SQL中級者の入り口に立ったことが肌感覚として伝わってくるのでは

はじめに
効率の良い検索を利用する
- サブクエリを引数に取る場合、INよりもEXISTSを取る
- サブクエリを引数に取る場合、INよりも結合を使う
ソートを回避する
- 集合演算子のALLをうまく使う
- DISTINCTをEXISTSで代用する
極値関数（MAX/MIN）でインデックスを使う
WHERE句でかける条件はHAVING句には書かない
- GROUP BY句とORDER BY句でインデックスを使う
そのインデックス、本当に使われてますか？
- 索引列に加工を行っている
- インデックス列にNULLが存在する
- 否定形を使っている
- ORを使っている
- 複合索引の場合に、列の順番を間違えている
- 後方一致、または中間一致のLIKE述語を用いている
- 暗黙の型変換を行っている
中間テーブルを減らせ
- HAVING句を活用しよう
- IN述語で複数のキーを利用する場合は、一箇所にまとめる
- 集約よりも結合を先に行なう
- ビューのご利用は計画的に
まとめ
1. INにサブクエリを取る場合は、EXISTSまたは結合に書き換える。
2. インデックスを利用するときは「左辺は裸」が基本。
3. SQLは明示的にソートを記述することはないが、暗黙のソートを行う演算が多くあるので注意が必要。
4. 余計な中間テーブルをなるべく減らそう。
5. レコード数を絞れる条件は早い段階で記述する。負債は早く返さないと、あとでツケを払うことになる。

はじめに

本格的なパフォーマンスチューニングを行なうには、使用しているハードウェアやDBMSが持つ機能や特徴についての知識が不可欠
本章では、なるべく実装非依存で、SQLを見直すだけで手軽にできる方法を集めた

CREATE TABLE Class_A (id INT, name VARCHAR(16),
  PRIMARY KEY(id));
CREATE TABLE Class_B (id INT, name VARCHAR(16),
  PRIMARY KEY(id));

効率の良い検索を利用する

効率よいアクセスをオプティマイザに指示できる書き方を知る

サブクエリを引数に取る場合、INよりもEXISTSを取る

IN
- パフォーマンス×
- 可読性○
EXISTS
- パフォーマンス○
- 可読性×

-- 遅い
SELECT *
  FROM Class_A
 WHERE id IN (SELECT id FROM Class_B);

-- 速い
SELECT *
  FROM Class_A A
 WHERE EXISTS
      (SELECT *
         FROM Class_B B
        WHERE A.id = B.id);

EXISTSのほうが速いと期待できる理由は以下の2つです。
1. もし結合キー（この場合はid）にインデックスが張られていれば、Class_Bテーブルの実表は見に行かず、インデックスを参照するのみで済む。
2. EXISTSは1行でも条件に合致する行を見つけたらそこで検索を打ち切るので、INのように全表検索の必要がない。これはNOT EXISTSの場合でも同様。
最近のDBMSはINでも速度改善が行われている

サブクエリを引数に取る場合、INよりも結合を使う

インデックスがない場合は、恐らくEXISTSに軍配があがる

-- INを結合で代用
SELECT A.id, A.name
  FROM Class_A A INNER JOIN Class_B B
    ON A.id = B.id;

ソートを回避する

ソートは頻繁に「暗黙裡」に行われている
ソートが発生する代表的な演算
- GROUP BY句
- ORDER BY句
- 集約関数（SUM, COUNT, AVG, MAX, MIN）
- DISTINCT
- 集合演算子（UNION, INTERSECT, EXCEPT）
- ウィンドウ関数（RANK, ROW_NUMBER等）
ストレージを使ったソートが行われると、パフォーマンスが大きく低下
- Using filesort

集合演算子のALLをうまく使う

UNION, INTERSECT, EXCEPT
普通に使うと重複排除のためのソートを行う
ALLを使うとソートを避けられる
- UNION ALL, INTERSECT ALL, EXCEPT ALL

-- 商品マスタ
CREATE TABLE Items (item_no INT, item VARCHAR(16),
  PRIMARY KEY(item_no));
-- 売り上げ履歴
CREATE TABLE SalesHistory (sale_date DATETIME, item_no INT, quantity INT,
  PRIMARY KEY(sale_date, item_no));

DISTINCTをEXISTSで代用する

結果を一意にするためにDISTINCTを使っている場合

-- DISTINCT版
SELECT DISTINCT I.item_no
  FROM Items I INNER JOIN SalesHistory SH
    ON I.item_no = SH.item_no;

-- EXISTS版
SELECT item_no
  FROM Items I
 WHERE EXISTS (SELECT *
                 FROM SalesHIstory SH
                WHERE I.item_no = SH.item_no);

極値関数（MAX/MIN）でインデックスを使う

-- これは全表検索が必要
SELECT MAX(item)
  FROM Items;

-- これはインデックスを利用できる
SELECT MAX(item_no)
  FROM Items;

WHERE句で書ける条件はHAVING句には書かない

-- 集約した後にHAVING句でフィルタリング
SELECT sale_date, SUM(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING sale_date = '2007-10-01';

-- 集約する前にWHERE句でフィルタリング
SELECT sale_date, SUM(quantity)
  FROM SalesHistory
 WHERE sale_date = '2007-10-01'
 GROUP BY sale_date;

GROUP BY句とORDER BY句でインデックスを使う

読んで字のごとく

そのインデックス、本当に使われてますか？

最もポピュラーなBツリーインデックス→二分探索

索引列に加工を行っている

インデックスを利用するときは、列は裸
左辺で計算を行いたい場合は、関数索引という方法もある

-- NG
SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

-- OK
SELECT * FROM SomeTable WHERE col_1 > 100 / 1.1;

-- NG
SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

-- OK
SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

インデックス列にNULLが存在する

インデックスが使用されないかも
- IS NULLやIS NOT NULLを使用する
- NULLが多い列

SELECT * FROM SomeTable WHERE col_1 IS NULL;

IS NOT NULLの条件でインデックスをどうしても使いたい場合
- すべての範囲を満たす条件を与える
- わかりにくいので使用には注意

-- IS NOT NULLの代用案
SELECT * FROM SomeTable WHERE col_1 > 0;

否定形を使っている

インデックスを使用できない
- <>
- !=
- NOT IN

SELECT * FROM SomeTable WHERE col_1 <> 100;

ORを使っている

ORにはインデックスを使用できないか、できても限定的
ORに向いたビットマップインデックス
- 更新コストが高い
- 更新処理が少ないBI/DWH（Business Intelligence/Data Ware House）向け

SELECT * FROM SomeTable WHERE col_1 > 100 OR col_2 = 'abc';

複合索引の場合に、列の順番を間違えている

列の順番と同じ順番で条件を書く

-- (col_1, col_2, col_3)の複合インデックスが張られている場合
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;

後方一致、または中間一致のLIKE述語を用いている

LIKE述語を使うときは、前方一致検索のみ索引が使用される

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

暗黙の型変換を行っている

-- もし文字列型なら…
× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

中間テーブルを減らせ

中間テーブルの問題点
1. データを展開するためにメモリやストレージを消費すること
2. 元テーブルに存在したインデックスを使うのが難しくなる（特に集約した場合）

HAVING句を活用しよう

集約した結果に対する条件について
- HAVINGをまっとうに利用する

-- NG
SELECT *
  FROM (SELECT sale_date, MAX(quantity) AS max_qty
          FROM SalesHistory
         GROUP BY sale_date) TMP -- 無駄な中間テーブル
 WHERE max_qty >= 10;

-- OK
SELECT sale_date, MAX(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING MAX(quantity) >= 10;

IN述語で複数のキーを利用する場合は、一箇所にまとめる

キーを結合して上手に検索できる場合がある

-- NG
SELECT id, state, city
  FROM Addresses1 A1
 WHERE state IN (SELECT state
                   FROM Addresses2 A2
                  WHERE A1.id = A2.id)
   AND city IN (SELECT city
                  FROM Addresses2 A2
                 WHERE A1.id = A2.id);

-- OK
SELECT *
  FROM Addresses1 A1
 WHERE id || state || city IN (SELECT id || state || city
                                 FROM Addresses2 A2);

-- OK（行比較をサポートしているDB）
SELECT *
  FROM Addresses1 A1
 WHERE (id, state, city) IN (SELECT id, state, city
                               FROM Addresses2 A2);

集約よりも結合を先に行なう

「普通の設計」が前提

ビューのご利用は計画的に

以下要注意
- 集約関数（AVG, COUNT, SUM, MIN, MAX）
- 集合演算子（UNION, INTERSECT, EXCEPT等）

まとめ

低速ストレージへのアクセスを減らす
本章の要点
1. INにサブクエリを取る場合は、EXISTSまたは結合に書き換える。
2. インデックスを利用するときは「左辺は裸」が基本。
3. SQLは明示的にソートを記述することはないが、暗黙のソートを行う演算が多くあるので注意が必要。
4. 余計な中間テーブルをなるべく減らそう。
5. レコード数を絞れる条件は早い段階で記述する。負債は早く返さないと、あとでツケを払うことになる。
参考文献（本文中）
1. ミック『SQL実践入門』
  - 実行計画の判断

以上

第2部 リレーショナルデータベースの世界

目次

13 RDB近現代史

まとめ

関係モデルの処遇について（この記事書いている人の雑感）

関係モデルの課題

NoSQL

14 なぜ"関係"モデルという名前なの？

まとめ

関係モデルで使用される公式用語と日常的な言葉との対応

15 関係に始まり関係に終わる

まとめ

代数構造

16 アドレス、この巨大な怪物

まとめ

この記事書いている人の雑感

17 順序をめぐる冒険

各勢力の主張

12 SQLプログラミング作法

目次

はじめに

テーブル設計

名前と意味

属性と列

コーディングの指針

コメント

インデント

スペース

大文字と小文字

カンマ

ワイルドカードは使わない

ORDER BYで列番号は使わない

標準語を話そう

左派と右派

相関サブクエリを追放せよ

FROM句から書く

まとめ

11 SQLを速くするぞ

この記事書いている人の雑感

目次

はじめに

効率の良い検索を利用する

サブクエリを引数に取る場合、INよりもEXISTSを取る

サブクエリを引数に取る場合、INよりも結合を使う

ソートを回避する

集合演算子のALLをうまく使う

DISTINCTをEXISTSで代用する

極値関数（MAX/MIN）でインデックスを使う

WHERE句で書ける条件はHAVING句には書かない

GROUP BY句とORDER BY句でインデックスを使う

そのインデックス、本当に使われてますか？

索引列に加工を行っている

インデックス列にNULLが存在する

否定形を使っている

ORを使っている

複合索引の場合に、列の順番を間違えている

後方一致、または中間一致のLIKE述語を用いている

暗黙の型変換を行っている

中間テーブルを減らせ

HAVING句を活用しよう

IN述語で複数のキーを利用する場合は、一箇所にまとめる

集約よりも結合を先に行なう

ビューのご利用は計画的に

まとめ

第2部リレーショナルデータベースの世界

13 RDB 近現代史