第1章

テーブルには行の順番がない

テーブルは「行の集合」であって「行のリスト」ではない
指定した順序で並び替える機能が発達している

第2章

「PostgreSQL」と「データベース」の違い

PostgreSQLやMySQLはデータベースを動かすソフトウェアであり、データベースそのものとは違う
1台のマシンで複数のデータベースを作成可能

第6章

SQLではNullPointerExceptionは発生しない

SQLではnullを使った計算をしてもエラーにならず、結果が全てnullになる(例外はある)
SQLがうまく動かない時は、どこかにnullが紛れ込んでいないか確認するとよい

coalesce()関数

値がnullだったら別の値に変換したいときはcoalesce()関数を使う

第8章

SQLでのテーブル結合

すべての組み合わせを生成する
条件に合った組み合わせだけを選ぶ
JOINは句ではなく演算子
主役となるテーブルをfrom句に指定し、補助となるテーブルをJOIN演算子に指定する
- テーブルの役割がわかりやすくなるため、SQLの意図も理解しやすくなる

テーブル結合とサブクエリの比較

テーブル結合を使うと「あるテーブルを検索するときに別のテーブルを利用できる」
サブクエリを使っても「あるテーブルを検索するときに別のテーブルを利用できる」
製品によって得手不得手が違う
- PostgreSQLはサブクエリのほうが得意でMySQLはテーブル結合のほうが得意

using

「on」のかわりに「using」を使う
- usingだと同じ名前の列どうしでしか結合できない
- usingの方が短く書ける

from句には複数テーブル指定できる

全ての組み合わせを表示する

select * from A, B;

テーブル結合の種類

内部結合 (inner join) ...左右のテーブルから、対応する組み合わせだけを取り出す
左外部結合 (left outer join) ...対応する組み合わせに加えて、対応しない行を左側のテーブルから取り出す
右外部結合 (right outer join) ...対応する組み合わせに加えて、対応しない行を右側のテーブルから取り出す
完全外部結合 (full outer join) ...対応する組み合わせに加えて、対応しない行を左右のテーブルから取り出す
- ※ すべての組み合わせの通り数ではないことに注意
クロス結合 (cross join) ...結合条件がない(つまり対応する行の有無を気にしない

左外部結合と右外部結合と完全外部結合をまとめて、「外部結合 (outer join)」という。

「内部」や「外部」という用語の由来

「テーブルの結合」を「集合の重なり」と考える
ベン図を使う
- 集合の関係を表現する図
- 重なった領域(内側の領域)を取り出すのが内部結合
- 重なっていない領域(外側の領域)も含んで取り出すのが外部結合

省略形

「LEFT OUTER JOIN」演算子の省略形は「LEFT JOIN」

第10章

外部キー

他テーブルの主キーを参照している列

外部キー制約

参照先のテーブルにない値が外部キーに入るのを禁止する制約

第12章

インデックス

どの行がどの場所にあるかを保持したデータ

インデックスがない場合

データベースエンジンはテーブルを先頭行から順番に読み取って調べる。このような調べ方をシーケンシャルスキャン(Sequential scan)という。

インデックスがある場合

走査対象がテーブルではなくインデックスになる。
シーケンシャルスキャンとインデックススキャンのうち、どちらの走査を使うかはデータベースが自動的に判断する。

インデックスの作成と削除

create index members_name_idx on members(name);

drop index members_name_idx;

インデックスが効いているかを確認

explain

explain文を使うと、SQLがどのように実行されるかという内部情報が見れる。これを使うと、SQL実行時に使われるインデックスがわかる。

explain select * from members where name = 'テスト';

カーディナリティ

カーディナリティ(Cardinality)とは、ある列に入っている値の種類の多さを表す。
例えば、IDや名前の列に値の重複がないとするなら、行の数だけ値の種類があるので「カーディナリティが高い」と言う。
これに対して性別の列は値が2種類しかないとして、行の数に比べて値の数が少ないので「カーディナリティが低い」と言う。

インデックスを作成して効果が高いのはどちらか？

カーディナリティが高い方であり、値によって行を大きく絞り込めるから。
反対にカーディナリティが低い方にインデックスを作成しても効果はない。

自動的に作成されるインデックス

カーディナリティが一番高くなるのは、列の値に重複がない場合である。
そのため「主キー」の列と一意制約が設定された列には、自動的にインデックスが作成される。
なので主キーや一意制約付きの列を使って行を絞り込むと、インデックスが効いて高速になる。

インデックスが効かない理由

インデックスを作成しても思ったように使われないことがある。その理由はいくつかある。

テーブルの行数が少ない

テーブルに十分な行数がない場合は、インデックスが使われない。なぜならテーブルサイズが小さいなら、全て読み込んだ方が速いから。おおざっぱにはテーブルのサイズが数KBだとインデックスが使われない。

8KBのブロック

PostgreSQLでは、テーブルのデータは「8KB」ごとのブロックに分かれており、その中に行が格納される。またインデックスが参照する先は行ではなく、実は行が格納されたブロックを参照している。
テーブルの行数が少ないと、すべての行が1つのブロックに収まる。つまりインデックスが参照する先はどれも同じブロックになるため、インデックスを使う意味がない。

カーディナリティが低い

比較式に関数や演算子を使っている

「where lower(name) = ’サシャ’」のようにname列に関数を使ってしまうとインデックスが使われない。
ただしインデックス作成時時に、列名である「name」のかわりに「lower(name)」という式を指定していれば、インデックスが使われる。このように列名ではなく式を使ったインデックスを式インデックス(Expression index)という。

データ型が一致していない

データ型が一致していないと(あるいは相互変換ができるデータ型でないと)、インデックスが使われない。

id 列が integer 型でかつインデックスが作られているとして、「where id = round(10.5)」ではインデックスが使わない。なぜなら四捨五入を行う関数「round()」が numeric 型を返しているため、integer 型である id 列とはデータ型が一致しないから。

これがやっかいなのは、「round()」が一見すると整数を返しているように見る (けど実は整数を返していない)こと。PostgreSQL では「pg_typeof()」でデータ型を調べられるので、気になったらデータ型を調べてみること。

複合インデックス

複合インデックス (Composite index)とは、複数の列から構成されるインデックスのこと。
複合インデックスを作るには、create index 文において複数の列名を指定するだけ。
####　複合インデックスが使われる場合と使われない場合
以下のようなインデックスを作成するとする。

create index pocketbook_date_amount_idx on pocketbook(date, amount);

date 列と amount 列の両方を使うと、インデックスが使われる
- 例:「where date = ’2019-09-22’ and amount > 10000」
date 列だけでも、インデックスが使われる
- 例:「where date = ’2019-09-22’」
amount 列だけだと、インデックスは使われません。
- 例:「where amount > 10000」 ← インデックスは使わない

インデックスの注意点

行の挿入や更新が少し遅くなる

大量のデータ挿入時はインデックスを外す

インデックス作成はテーブルをロックする

インデックスを作成する create index 文は、テーブルをロックする。つまりそのテーブルへの挿入や変更ができなくなる。

これは行数が非常に多いテーブルで問題になる。行数が多ければ多いほどインデックス作成に時間がかかるので、テーブルへアクセスができなくなる時間も長くなってしまう。

そのため行数が多いテーブルでは、ceate index 文に「concurrently」オプションをつけてインデックス作成をバックグラウンドで行うことで、テーブルがロックされるのを回避する。
• create index concurrently インデックス名 on テーブル名 (列名)
通常の create index 文よりも時間がかかるが、テーブルをロックしないので安全。

式インデックス

例.

create index user_registrations_email_idx
  on user_registrations(lower(email));

一意制約でも式が使えるといいが、あいにくそうはなっていないので。一意制約に式を使いたい場合は、一意制約のかわりに一意なインデックスを作成する。

create table user_registrations (
  id     integer  primary key
, name text not null
, email text not null unique ← これだと式を指定できない , unique (lower(email)) ← 式を指定できてもよさそうだができない );
-- 一意なインデックスを作成
create unique index user_reigstartions_email_key
on user_registrations(lower(email)); ← 式を指定できる

部分インデックス

条件を指定したインデックスを部分インデックス (Partial index) という。
例.

create index app_users_deleted_at_idx on app_users(deleted_at) where deleted_at is not null;

第15章

case式

case式において「else」とその値を省略するのは止める。
nullにするのが意図通りだとしても「else null」と明示的に書く。

nullとの比較はis nullを使う

PostgreSQLとMySQLではselect句でつけた別名（Alias）をgroup by句に指定できるように拡張されている

#　第16章

相関サブクエリ

外側のテーブルや列を参照している内側のサブクエリのこと。

第17章

テーブル結合(応用編)

自己結合

あるテーブルとそのテーブル自身を結合すること。
自己結合を使う例としては以下

前日の値と比較する
階層構造における親を表示する

非等値結合

第18章

ウィンドウ関数

パーティション

ウィンドウ関数は直後に「over (...)」というover句がつく。
「sum()」などの集約関数も、直後に over 句がつくとウィンドウ関数として使える。
over 句の丸カッコ内には、主に次の2つを指定する。

「partition by ...」... テーブルをパーティションに分割する。
「order by ...」... パーティションごとに行を並び替える。

第21章

デッドロック

デッドロックの回避方法

更新用ロックを掛ける順番を同じにする
- 主キーでソートするなど

第23章

総合問題:製造販売

23.1商品価格

自分の回答

問題1:導出テーブル

select
    t.item_id,
    si.name,
    t.start_date,
    sp.price
from
    (
        select
            item_id,
            MAX(start_date) start_date
        from
            sales_prices
        group by
            item_id
    ) t
    join sales_prices sp using(item_id, start_date)
    join sales_items si on sp.item_id = si.id
order by
    si.id;

問題2:in演算子

select
    sp.item_id,
    si.name,
    sp.start_date,
    sp.price
from
    sales_prices sp
    inner join sales_items si on sp.item_id = si.id
where
    (item_id, start_date) in (
        select
            item_id,
            MAX(start_date)
        from
            sales_prices
        group by
            item_id
    )
order by
    sp.item_id;

[備忘録]わかりみSQL

第1章