Upgrade to Pro — share decks privately, control downloads, hide ads and more …

実践RDBMS〜PostgreSQL編〜

progrhyme
August 10, 2018

 実践RDBMS〜PostgreSQL編〜

progrhyme

August 10, 2018
Tweet

More Decks by progrhyme

Other Decks in Technology

Transcript

  1. 実践RDBMS〜PostgreSQL編〜
    2018/5 IKEDA Kiyoshi
    1

    View Slide

  2. About Me
    ➢ 2009〜2017 ... 渋谷の某IT企業でソシャゲ開発やイ
    ンフラ運用に勤しむ
    ○ ほぼMySQL
    ○ 高負荷や大量データとの戦いの日々を経験
    ➢ 2017〜 ... M3
    ○ クラウド電子カルテ ... PostgreSQL
    ○ AIチーム ... MySQL, PostgreSQL
    ➢ PostgreSQL勉強中
    ➢ Oracle未着手
    2

    View Slide

  3. はじめに
    ➢ 今日の目的
    ○ RDBを用いたアプリケーションの開発に必要な基礎知識を身に着け
    る。
    ➢ 前提
    ○ SQLを書いたことがある
    ○ RDBを使ったアプリケーション開発の経験が浅い
    ○ 一般的な知識を復習したい
    ➢ 今日やらないこと:
    ○ 理論的なことには深入りしない
    ○ Oracle, MySQLについてはほぼ触れない
    ○ ハンズオンの時間はありません
    ■ 演習問題をいくつか用意しています←後ほど各自でどうぞ
    ■ サンプルリポジトリ有り(後掲)
    3

    View Slide

  4. 今日伝えたい大事なこと
    ➢ クソクエリを本番DBに流さない
    ➢ ORMを使っていても、発行されるSQLを意識しよう
    ➢ 実行計画を見よう
    ➢ 負荷試験をしよう
    ➢ 正しいDB設計でデータの整合性を守り、パフォーマ
    ンスを担保する
    ➢ DBMSの公式ドキュメントを読もう
    4

    View Slide

  5. お品書き
    ➢ RDB, RDBMSとは
    ➢ DB設計
    ○ ER図, 正規化
    ○ 制約
    ➢ SQL
    ○ SQLの基礎
    ○ リレーション演算, JOIN
    ➢ INDEXと実行計画
    ➢ ロックとトランザクション
    5

    View Slide

  6. RDB, RDBMSとは
    6

    View Slide

  7. RDBとは
    ➢ Relational Database = 関係(relation)DB
    ➢ 事前定義された、関連があるデータ項目の集合体
    ○ https://aws.amazon.com/jp/relational-database/
    ○ データ項目: 行と列を持つテーブルのセット
    7
    顧客ID 店舗ID 氏名 Email 入会日
    1 101 Adam ***@example.com 2015/01/01
    2 101 Bluce ***@example.com 2016/07/15
    3 102 Chris ***@example.com 2011/09/23
    4 103 Dave ***@example.com 2014/02/03
    表:例 - 顧客テーブル

    View Slide

  8. RDBの理論
    ➢ リレーショナルモデル ... データを「リレーション」という概
    念で表現する
    ➢ リレーション ... テーブルに相当
    ➢ 属性 ... テーブルの「列」に相当
    ➢ データを集合として扱う
    ○ 集合操作(和、差、積など)をサポート
    ○ よく使うのは「射影」「選択(制限)」「結合」(※後述)
    8

    View Slide

  9. RDBMS
    ➢ RDB Management System
    ○ RDBを管理するためのソフトウェア
    ➢ PostgreSQL, Oracle, MySQL, etc.
    ➢ 機能
    ○ SQL
    ○ トランザクション
    ○ バックアップ
    ○ リストア
    ○ クラッシュリカバリ
    ○ レプリケーション
    ○ 権限管理
    ➢ ACID特性を持つ
    9

    View Slide

  10. ACID特性について
    ➢ トランザクション処理を持つシステムの備えるべき4つ
    の性質
    ➢ Atomicity(原子性)
    ○ 一連の処理は全て実行されるか、全く実行されないかのど
    ちらか
    ➢ Consistency(一貫性)
    ○ 処理の前後でデータの整合性が保たれる
    ➢ Isolation(独立性)
    ○ 処理中の更新は確定するまで他の処理から不可視
    ➢ Durability(永続性)
    ○ 確定した結果が失われない
    10

    View Slide

  11. DB設計
    11

    View Slide

  12. サンプルDB
    12
    ➢ https://github.com/progrhyme/postgres-tutorial
    ➢ ER図: http://www.postgresqltutorial.com/postgresql-sample-database/
    ➢ 以降の説明の一部でこのdvdrentalデータベースを使
    う。

    View Slide

  13. RDBにおけるDB設計
    ➢ 例: 「DVDレンタルショップのシステムを作ろう」
    ➢ 関連するデータ属性の組をリレーション(=テーブル)
    にまとめていく。
    ○ store ... 店舗
    ○ staff ... 店員
    ○ film ... 映画
    ○ actor ... 俳優
    13

    View Slide

  14. ER図
    14
    store
    store_id
    manager_staff_id
    address
    staff
    staff_id
    store_id
    name
    email
    active
    film
    film_id
    title
    description
    release_date
    length
    actor
    actor_id
    name
    birth_date
    film_actor
    film_id
    actor_id
    1:Nの関係
    交差テーブルによりM:N
    の関係を実現

    View Slide

  15. ER図について
    ➢ テーブル間のデータのカーディナリティ(多重度)を視覚化でき

    ➢ あると引き継ぎ時など便利
    ➢ テーブル数が20を超えるぐらいから可視化が難しくなる
    ○ 主要部分について、または、機能単位などで作ると良い
    ➢ ツールは色々あるが、ERMasterやMySQL Workbenchが使
    いやすかった記憶
    ➢ DB上のテーブル定義からER図を生成できるものや、ER図か
    らDDLを生成できるものもある
    15

    View Slide

  16. リレーション(テーブル)の正規化
    ➢ 正規化 ... データの重複や矛盾を防ぐための作業
    ➢ 正規形(NF) ... 正規化の段階を示すもの
    ○ 第1〜第6正規形まである
    ○ BCNF(ボイスコッド正規形) ... 3NFと4NFの間
    ○ 第3正規形までは覚えよう
    ○ 第n正規形(n >= 2)はより下位の正規形を満たす
    ■ BCNFは1〜3NFを満たす
    ➢ 第1正規形
    ○ 重複がない(主キー or ユニークキーがある)
    ○ スカラ値のみを持つ
    ■ JSONとかNG
    ○ NULL値を取らない
    16

    View Slide

  17. 1NF → 2NF
    17
    作品 レビュワー 監督 出身 レート
    A Bob Tom アリゾナ ★★★
    A Alice Tom アリゾナ ★
    B Bob Eva ミネソタ ★★
    C Alice Tom アリゾナ ★★★
    表:「レビュー」リレーション
    (作品, レビュワー)を
    候補キーという
    作品 レビュワー レート
    A Bob ★★★
    A Alice ★
    B Bob ★★
    C Alice ★★★
    作品 監督 出身
    A Tom アリゾナ
    B Eva ミネソタ
    C Tom アリゾナ
    「候補キー → 非キー属性」へ
    の関数従属性を除く

    View Slide

  18. 2NF → 3NF
    18
    作品 監督 出身
    A Tom アリゾナ
    B Eva ミネソタ
    C Tom アリゾナ
    監督 出身
    Tom アリゾナ
    Eva ミネソタ
    作品 監督
    A Tom
    B Eva
    C Tom
    「非キー属性 → 非キー属性」
    への関数従属性を除く

    View Slide

  19. 正規化しない / あえて非正規化する例
    ➢ 日付のNULL値をフラグ的に扱うやり方
    ○ 例) deleted_atがNULLならば削除されてないと見なす
    ➢ optionalな属性値をまとめて1つのカラムにJSON形
    式で格納
    ○ RDB的にはアンチパターン
    ■ インデックス効かない(例外: PostgreSQLのjsonb型)
    ■ アプリケーションにロジックが寄る→データ不整合を生みやすい
    ○ メリット: ALTERを避けられる
    ○ NoSQLを使うという手もある
    ➢ JOINによってパフォーマンス劣化することがある vs
    正しくクエリを書けば劣化しないはず
    19

    View Slide

  20. 制約 - 主キー、一意性制約、NOT NULL制約
    ➢ 主キー ... レコードを一意に特定するための列(の組)。基本、
    作る。
    ○ 複合主キー ... 複数列から成る主キー
    ○ ナチュラルキー ... 意味のあるキー。ユーザ名, 氏名, メールアドレ
    スなど
    ○ サロゲートキー ... システムが発行する意味のないキー。連番のID
    など
    ➢ 一意性制約 = ユニークキー
    ○ 主キー以外にも設けられる
    ➢ NOT NULL制約 ... 適宜付ける
    20
    昔のMySQLやPostgreSQLはNULLだとINDEXが効かない問題があった
    が、最近はINDEXが効く。PostgreSQLなら部分INDEX(後述)も使える。//
    正規化の観点ではNULLは避けるべき

    View Slide

  21. 制約 - 外部キー制約①
    ➢ 複数テーブル間でデータの整合性をとるために使う
    ➢ `REFERENCES` を指定する
    21
    CREATE TABLE city (
    city_id integer NOT NULL,
    city character varying(50) NOT NULL,
    country_id smallint NOT NULL REFERENCES country,
    last_update timestamp NOT NULL DEFAULT now(),
    PRIMARY KEY (city_id)
    );
    参照テーブル
    被参照テーブル

    View Slide

  22. 制約 - 外部キー制約②
    22
    dvdrental=# INSERT INTO city (city,country_id) VALUES ('Tokyo',
    9999);
    ERROR: insert or update on table "city" violates foreign key
    constraint "fk_city"
    DETAIL: Key (country_id)=(9999) is not present in table
    "country".
    被参照テーブルに存在しない値でINSERTしようとしてエラー

    View Slide

  23. 制約 - 外部キー制約③
    23
    CREATE TABLE inventory (
    inventory_id integer NOT NULL,
    film_id smallint NOT NULL REFERENCES film
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    store_id smallint NOT NULL,
    last_update timestamp NOT NULL DEFAULT now(),
    PRIMARY KEY (inventory_id)
    );
    被参照列の更新後の値が参照行にコピー
    される。
    被参照行の削除を防ぐ。
    ON UPDATE RESTRICT ... 被参照行の更新を防ぐ
    ON DELETE CASCADE ... 被参照行の削除時、参照行も削除する

    View Slide

  24. 制約 - 外部キー制約の注意点
    ➢ 外部キーには暗黙的なインデックスが設定されない
    ➢ 先に挙げた2例だと city.country_id や
    inventory.film_id にインデックスがないと、countryや
    filmの行削除/更新時にフルスキャンが走る。
    ➢ 参考: PostgreSQLアンチパターン:外部キー制約の
    更新コストを見くびる - Qiita
    24

    View Slide

  25. DB設計 - 要点まとめ
    ➢ 第3正規形まではできるようになろう
    ➢ 主キーは付ける
    ○ ナチュラルキー、サロゲートキーは適宜使い分けるべし(異
    論は有るかも)
    ➢ 外部キー制約はデータの整合性担保のために有用
    だが、パフォーマンス上は要注意
    25

    View Slide

  26. (演習) DB設計
    ➢ ERMasterなどを使って、案件で利用しているDBの
    ER図を作ってみよう
    ➢ 正規化されていないテーブルがあれば、理由を考え
    てみよう。また、正規化するとしたらどうするか考えて
    みよう
    ➢ dvdrentalデータベースのfilmテーブルのデータを更
    新・削除を試し、外部キー制約の挙動を確認しよう
    26

    View Slide

  27. SQL
    27

    View Slide

  28. SQLとは
    28
    ➢ RDBの操作を行うための言語
    ➢ ISOによって国際標準のSQL仕様が定められている
    ➢ どの程度標準に準拠しているかは、各RDBMSによって異なる
    ○ 「方言」が多いといわれる←なるべく標準SQLを書こう
    ○ 参考: SQLの観点から「Oracle Database」「PostgreSQL」「MySQL」の特徴
    を整理しよう! | アシスト
    ➢ DDL ... データ定義言語。TABLEやINDEXのCREATE,
    DROP, ALTERなど
    ➢ DML ... データ操作言語。レコードのCRUD (INSERT,
    SELECT, UPDATE, DELETE)

    View Slide

  29. SQLの基礎 - テーブルの作成、削除
    29
    -- テーブルの作成
    CREATE TABLE language (
    language_id integer NOT NULL,
    name character(20) NOT NULL,
    last_update timestamp NOT NULL DEFAULT now(),
    PRIMARY KEY (language_id)
    );
    -- テーブルの削除
    DROP TABLE language;
    language_id name last_update
    1 English 2006-02-15 10:02:19
    2 Italian 2006-02-15 10:02:19
    3 Japanese 2006-02-15 10:02:19
    4 Mandarin 2006-02-15 10:02:19
    5 French 2006-02-15 10:02:19
    6 German 2006-02-15 10:02:19
    ※一部簡略化しています。

    View Slide

  30. SQLの基礎 - レコードの作成
    30
    -- レコードの挿入
    INSERT INTO language VALUES (7, 'Chinese', NOW());
    INSERT INTO language (name, language_id) VALUES ('Korean', 10);
    -- 複数行の挿入(= BULK INSERT)
    INSERT INTO language VALUES
    (8, 'Russian', NOW()), (9, 'Greese', NOW());
    -- サブクエリを利用した BULK INSERT
    INSERT INTO language (name, language_id) SELECT name, language_id
    + 10 FROM language;

    View Slide

  31. SQLの基礎 - レコードの更新・削除
    31
    -- レコードの更新
    UPDATE language SET name = 'Swidish' WHERE language_id = 6;
    -- レコードの削除
    DELETE FROM language WHERE language_id > 10;
    WHERE句のないUPDATE, DELETEは
    全件が対象となり、非常に危険!!

    View Slide

  32. SQLの基礎 - レコードの参照
    32
    -- 全件の全列を取得
    SELECT * FROM language;
    -- 列を指定
    SELECT name,last_update FROM language;
    -- 更に、行の条件を指定
    SELECT name,last_update FROM language WHERE language_id IN (1,2);
    -- ソート
    SELECT * FROM language ORDER BY last_update DESC;
    -- ソートして結果の件数を制限
    SELECT * FROM language ORDER BY last_update LIMIT 3;

    View Slide

  33. SQLの基礎 - レコードの参照 + 集約クエリ
    33
    -- 結果のN件目からM件を取得
    SELECT * FROM language LIMIT 3 OFFSET 5;
    -- 結果から重複行を除く
    SELECT DISTINCT last_update FROM language;
    -- 集約
    SELECT last_update, count(*) FROM language GROUP BY last_update;
    SELECT last_update, count(*) FROM language GROUP BY last_update
    HAVING count(*) > 3;
    LIMIT OFFSET句は簡易なページングシステムなどでよく使われるが、OFFSET値
    が大きくなるとDB負荷が大きくなるため、OFFSET上限が定まらない限りは安易に
    使うべきではない

    View Slide

  34. SQLによるリレーション演算
    ➢ 射影 ... テーブルの中から一部の列だけを取り出す
    ○ 例) SELECT 顧客ID, 氏名 FROM 顧客;
    ➢ 選択(制限) ... 条件に合う行だけを取り出す
    ○ 例) SELECT * FROM 顧客 WHERE 店舗ID = 101;
    ➢ 結合 ... 2つのテーブルを結びつけて1つの仮想的なテーブルを作る
    ○ 例) SELECT * FROM 顧客 INNER JOIN 店舗 ON (顧客.店舗ID =
    店舗.店舗ID);
    34
    顧客ID 店舗ID 氏名 Email 入会日
    1 101 Adam ***@example.com 2015/01/01
    2 101 Bluce ***@example.com 2016/07/15
    3 102 Chris ***@example.com 2011/09/23
    4 104 Dave ***@example.com 2014/02/03
    店舗ID 管理者 住所 営業開始日
    101 White アリゾナ 2013/05/01
    102 River フロリダ 2011/04/20
    103 Mountain ミシシッピ 2016/02/17
    表:顧客テーブル
    表:店舗テーブル

    View Slide

  35. 結合の種類
    ➢ 条件による結合
    ○ A INNER JOIN B ... A及びBで値が一致する行について
    結合
    ○ A LEFT OUTER JOIN B ... Aの各行は最低1回は現れ、
    Bで値が存在しないものはNULLになる
    ○ A RIGHT OUTER JOIN B ... ↑の逆
    ○ A FULL OUTER JOIN B ... A, Bとも最低1回は現れ、他
    方に値が存在しないものはNULLになる
    ➢ クロス結合
    ○ A CROSS JOIN B ... AとBの直積(デカルト積)。すべての
    可能な行同士の組み合わせ。結果はn * m行になる。
    35

    View Slide

  36. SQL補遺
    ➢ 公式チュートリアル:
    ○ https://www.postgresql.jp/document/10/html/tutorial-sql.html
    ➢ その他の集約関数 ... avg, sum, maxなど
    ○ 一覧:
    https://www.postgresql.jp/document/10/html/functions-aggregate.html
    ➢ 集合操作:
    ○ UNION ... たまに使う。
    ○ EXCEPT, INTERSECT ... ほとんど使わない。
    ➢ ※(特にWeb)アプリケーションから複雑なSQLを実行するのは
    推奨しません。
    36
    不慣れな人は一通りどうぞ

    View Slide

  37. (演習) SQL
    ➢ dvdrental DBについて
    ○ ①film, film_category, categoryの3テーブルを内部結合し
    て、category_id = 2 (Animation) のtitleとnameを10件抽
    出しましょう。
    ○ ②payment, customer, addressの3テーブルのデータか
    ら、2007年2月の売上を顧客の住所ごとに合計して上位
    10件を抽出して下さい。サブクエリを使うと良いでしょう。
    37

    View Slide

  38. 閑話:データ型とデータサイズ(PostgreSQL)
    ➢ https://www.postgresql.jp/document/10/html/datatype.html
    ➢ 数値データ型
    ○ integer(4B) … 約20億までなので
    ○ bigint(8B) … ↑に収まらないときはこちら
    ➢ 文字型
    ○ char(n) … n文字分。固定長
    ○ varchar(n) … 最大n文字分。可変長
    ○ 1文字のデータサイズは文字セット(エンコーディング)による
    ■ https://www.postgresql.jp/document/10/html/multibyte.html
    ■ UTF8 … 1-4B
    ➢ バイナリ型
    ➢ 日付/時刻データ型
    38

    View Slide

  39. 閑話:データ型とデータサイズ
    ➢ 1行のサイズ
    ○ 〜各列のデータサイズの合計
    ○ インデックスサイズ
    ■ 〜キー列のデータサイズの合計
    ➢ データ型は必要十分なサイズを選ぼう
    ➢ NG例:
    ○ 数値しか入らないのに文字型を使う
    39

    View Slide

  40. INDEXと実行計画
    40

    View Slide

  41. B-treeインデックス
    41
    ➢ RDBのインデックスといえばまずこれ。
    ➢ PostgreSQLでは他にもいくつか選べるが、デフォルトは
    B-tree.
    ➢ Balanced Treeである。(binary treeではない)
    ○ leafの高さを一定に保つアルゴリズム
    ➢ 計算量はO(log N)
    ➢ PKやユニークキーを作るとB-treeインデックスが作られる
    ➢ 制約のないインデックスを作ることもできる
    ➢ 参考:
    ○ B-treeインデックス入門 - Qiita
    ○ B木 - Wikipedia
    ○ 11.6. 一意インデックス - PostgreSQL 10.3文書

    View Slide

  42. どうインデックスを作るべきか?
    ➢ 検索やソートの要件に応じて、必要なイ
    ンデックスを作る
    ➢ 例) paymentテーブル(右)
    ○ 要件:
    ■ 特定のpayment_dateの期間
    ■ staff_id + customer_idによる絞り込み
    ■ rental_idで絞り込んでpayment_dateでソート
    ○ INDEX:
    ■ (payment_date)
    ■ (staff_id, customer_id)
    ■ (rental_id, payment_date)
    ➢ ※UPDATEやDELETEの絞り込み条
    件でもインデックスが用いられる
    42
    payment
    payment_id
    customer_id
    staff_id
    rental_id
    amount
    payment_date

    View Slide

  43. 複合インデックスについて
    ➢ 複数列から成るインデックス
    ➢ 検索木は最初の列値で作られる
    ○ => 2番目以降の列値で絞り込みやソートをしてもインデックスは使わ
    れない
    ○ 先の例だと、(rental_id, payment_date)は(payment_date)の代替に
    ならない
    ➢ 複合PKでも同じ話
    ➢ カバリングインデックス
    ○ インデックスに含まれる列値のみをSELECT対象とする問合せで、イ
    ンデックスオンリースキャンを可能にする
    ■ 実データ部にアクセスしないことでI/Oを抑えられる
    ○ この目的であえて検索条件として不要な列値をインデックスに加えるこ
    ともある
    43

    View Slide

  44. 初心者がやりがちなインデックス効かない事例
    ➢ 中間一致
    ○ SELECT * FROM t WHERE a LIKE ‘%foo%’;
    ➢ 後方一致
    ○ SELECT * FROM t WHERE a LIKE ‘%foo’;
    ➢ 否定形
    ○ SELECT * FROM t WHERE a <> 1;
    ➢ 参考:
    ○ インデックスが使えない検索条件 - Qiita
    ※NULLはインデックス効きます
    44

    View Slide

  45. インデックスのトレードオフ、他
    ➢ インデックスが多いほど、更新処理が重くなる
    ➢ インデックスのデータ量も増える
    ➢ 無駄なインデックスは省こう:
    ○ 例) (a, b, c) というインデックスがあるなら (a) や (a, b) は
    無駄。
    ➢ 巨大なテーブルに後からインデックスを追加するのは
    高コスト
    ○ 使いそうなインデックスは予め作っておく
    45

    View Slide

  46. 閑話:部分インデックス(PostgreSQL)
    ➢ https://www.postgresql.jp/document/10/html/index
    es-partial.html
    ➢ CREATE INDEX … WHERE 〜 で対象となるレ
    コードを限定する。
    ➢ 頻出値(デフォルト値など)を除外するときに有効
    ➢ パフォーマンス向上する場合がある
    ○ インデックスサイズが小さく
    ○ 検索・更新が速く
    46

    View Slide

  47. 閑話:関数インデックス(PostgreSQL, Oracle)
    ➢ https://www.postgresql.jp/document/10/html/indexes-expr
    essional.html
    ➢ 列値そのものでなく、1つ以上の列から計算した結果
    の値でインデックスを作る
    ➢ 例)
    ○ メールアドレスに対してlower関数(小文字化)をかけてユ
    ニーク制約を付ける
    47

    View Slide

  48. PostgreSQLのクエリ処理と実行計画
    参考:
    ➢ https://www.postgresql.jp/document/9.3/html/overview.html
    ➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の鉄則』4.3 問い合わせ
    の実行
    48
    図は割愛

    View Slide

  49. 実行計画を見よう(PostgreSQL)
    ➢ 実行計画 … DBMSがどのようにクエリを実行するか、とい
    う内部情報。チューニングの重要な情報源
    ➢ EXPLAIN 〜 ... 実行計画を表示
    ➢ EXPLAIN ANALYZE 〜 ... 実際にクエリを実行し、
    かかった時間と合わせて実行計画を表示
    ○ ※更新系SQLを食わせると危険!
    49

    View Slide

  50. PostgreSQL:実行計画の例と見方
    dvdrental=# EXPLAIN SELECT f.title, l.name FROM film f, language l WHERE f.language_id
    = l.language_id AND f.film_id IN (237,407);
    QUERY PLAN
    --------------------------------------------------------------------------------------
    Nested Loop (cost=0.28..13.85 rows=2 width=99)
    Join Filter: (f.language_id = l.language_id)
    -> Seq Scan on language l (cost=0.00..1.06 rows=6 width=88)
    -> Materialize (cost=0.28..12.61 rows=2 width=17)
    -> Index Scan using film_pkey on film f (cost=0.28..12.60 rows=2 width=17)
    Index Cond: (film_id = ANY ('{237,407}'::integer[]))
    (6 rows)
    50
    filmとlanguageをJOINして問合せ

    View Slide

  51. PostgreSQL:実行計画の例と見方
    dvdrental=# EXPLAIN SELECT f.title, l.name FROM film f, language l WHERE f.language_id
    = l.language_id AND f.film_id IN (237,407);
    QUERY PLAN
    --------------------------------------------------------------------------------------
    Nested Loop (cost=0.28..13.85 rows=2 width=99)
    Join Filter: (f.language_id = l.language_id)
    -> Seq Scan on language l (cost=0.00..1.06 rows=6 width=88)
    -> Materialize (cost=0.28..12.61 rows=2 width=17)
    -> Index Scan using film_pkey on film f (cost=0.28..12.60 rows=2 width=17)
    Index Cond: (film_id = ANY ('{237,407}'::integer[]))
    (6 rows)
    51
    filmとlanguageをJOINして問合せ
    入れ子ループ
    結合
    インデックス
    が使われる
    シーケンシャルス
    キャン。インデック
    スが使われない
    最初の1件を返すまでのコ
    スト .. 全件を返すまでのコ
    スト

    View Slide

  52. PostgreSQL:実行計画の見方の補足
    ➢ 実行計画は木構造
    ○ 下位ノードから実行される
    ○ 総実行コストは下位ノードの実行コストの総和
    ➢ コスト値は基準値(seq_page_cost)を1とする相対評価
    ➢ スキャン系ノード
    ○ Seq Scan, Index Scan, Index Only Scan他
    ➢ 結合ノード
    ○ Nested Loop, Hash Join, Merge Join
    ➢ その他ノード
    ○ Sort ... work_memが小さいと外部ソートが発生し、遅くなる
    ○ and more
    52

    View Slide

  53. 大事なこと
    ➢ 大量のフルスキャンを発生させない
    ○ 適切にインデックスを張る
    ○ インデックスが想定通り使われるか、実行計画を見て確認
    ➢ リリース当初は良くても、データが溜まって性能劣化
    することがある
    ○ データが蓄積した状態の負荷試験をしましょう
    53

    View Slide

  54. ロックとトランザクション
    54

    View Slide

  55. ロック
    55
    ➢ 一方が処理している間、他方は処理できないようにブ
    ロックする。排他ロック
    ➢ 例)
    ○ AとBがほぼ同時に1つしかないDVD『ジュラシックパーク』を借りようと
    する
    ○ ロック機構がないと、2人とも借りることに成功してしまう
    ➢ PostgreSQLのロックは細かくて難しい
    ○ テーブルレベルロック
    ■ SELECTでもゆるいロックを獲得する
    ■ 同一テーブルに競合するロックモードのトランザクションを同時に実行で
    きない
    ○ 行レベルロック
    ■ 2つのトランザクションは同じ行に対して競合するロックを保持できない

    View Slide

  56. PostgreSQLのテーブルレベルロックモード①
    56
    ロックモード 獲得タイミング
    ACCESS SHARE SELECT
    ROW SHARE SELECT FOR UPDATE, SELECT FOR SHARE
    ROW EXCLUSIVE UPDATE, DELETE, INSERT
    SHARE UPDATE EXCLUSIVE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY,
    CREATE STATISTICS, ALTER TABLE VALIDATE及び他の
    ALTER TABLEの亜種
    SHARE CREATE INDEX
    SHARE ROW EXCLUSIVE CREATE COLLATION, CREATE TRIGGER, ALTER TABLE
    EXCLUSIVE REFRESH MATERIALIZED VIEW CONCURRENTLY
    ACCESS EXCLUSIVE DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM
    FULL, REFRESH MATERIALIZED VIEW, ALTER TABLE,
    LOCK TABLE


    View Slide

  57. PostgreSQLのテーブルレベルロックモード②
    表: https://www.postgresql.jp/document/10/html/explicit-locking.html より
    57

    View Slide

  58. PostgreSQLのテーブルレベルロックまとめ
    ➢ SELECTは基本ロック競合しない
    ➢ SELECT FOR UPDATEの他、UPDATEなど行を更
    新するクエリは行ロックする(後述)
    ➢ 大抵のDDLは更新操作と競合する
    ○ DDLそのものとも競合する
    ➢ DROP TABLE, 一部のALTER TABLE, VACUUM
    FULLなどはSELECTとも競合する
    58

    View Slide

  59. PostgreSQLの行レベルロックモード
    59
    ロックモード 獲得タイミング
    FOR KEY SHARE SELECT FOR KEY SHARE
    FOR SHARE SELECT FOR SHARE
    FOR NO KEY UPDATE ユニークキーを含む列値に対する UPDATE, SELECT FOR NO
    KEY UPDATE
    FOR UPDATE UPDATE, DELETE, SELECT FOR UPDATE


    https://www.postgresql.jp/document/10/html/explicit-locking.html より

    View Slide

  60. デッドロック
    ➢ 異なる2者がそれぞれA→B, B→Aとロックを取り合
    い、どちらも処理できなくなること
    ➢ PostgreSQLのデッドロック検出機構:
    ○ deadlock_timeout(デフォルト1秒)ごとにロック獲得待ちの
    トランザクションをチェック
    ○ デッドロックしていたらどちらかをabort
    ○ 参考: PostgreSQLは雰囲気でデッドロックを殺す - そーだ
    いなるらくがき帳
    ➢ 行ロックの場合、更新順序をソートするのがよくやる
    解決策
    60

    View Slide

  61. トランザクション
    ➢ トランザクション
    ○ 互いに関連する一連の処理を1まとめにしたもの
    ○ ACID特性を満たす
    ■ Atomicity ... 一連の処理は全て実行されるか、全く実行されないか
    ● ロールバックが可能
    ■ Consistency ... 処理の前後でデータの整合性が保たれる
    ■ Isolation ... 処理中の更新は確定するまで他の処理から不可視
    ■ Durability ... 確定した結果が失われない
    ➢ ロックとトランザクション
    ○ ロックは獲得された後、トランザクションの終わりまで保持
    される
    61

    View Slide

  62. トランザクションを使う
    ➢ psqlクライアント
    ○ 開始 ... `BEGIN` or `START TRANSACTION`
    ○ キャンセル ... `ROLLBACK`
    ○ 確定 ... `COMMIT`
    ○ ※トランザクションを使わないと、更新SQLを実行する度に
    ただちに確定される
    ➢ ライブラリから接続するとき、多くの場合、トランザク
    ションを有効にして使う
    ➢ PostgreSQLはDDLでもトランザクションを使える
    62

    View Slide

  63. トランザクション分離レベルについて
    https://www.postgresql.jp/document/10/html/transaction-iso.html
    63
    分離レベル ダーティーリード 反復不能読み取り ファントムリード 直列化異常
    READ UNCOMMITTED OK※ NG NG NG
    READ COMMITTED OK NG NG NG
    REPEATABLE READ OK OK OK※ NG
    SERIALIZABLE OK OK OK OK
    OK※ ... 標準SQLでは許容されるが、PostgreSQLでは発生しない
    表:分離レベルと発生し得る問題
    PostgreSQLのデフォルトはREAD COMMITTED. M3でもこれを使って
    いる。

    View Slide

  64. 分離レベルが低いことで起こる問題
    ➢ ダーティリード
    ○ 他のトランザクション(Tx)による未コミットのデータを読み込んでしまう
    ➢ 反復不能読み取り
    ○ 他のTxによるコミット済みのデータを読み込むことで、Tx内の同一行
    に対するSELECTの結果が異なる
    ➢ ファントムリード
    ○ ある範囲内への他のTxによるレコード挿入 + コミットによって、Tx内の
    該当の範囲検索の結果が異なる
    ➢ 直列化異常
    ○ 複数のTxを正常にコミットした結果が、それぞれをあらゆる可能な順
    序で実行した結果と一貫性がない
    64

    View Slide

  65. 分離レベルのトレードオフ
    ➢ 分離レベルが高いほどデータの信頼性は高くなる
    ➢ 引き換えに、性能が低くなりやすい
    ○ トランザクション並列度が下がる
    ○ ロック競合やデッドロックが発生しやすくなる
    ➢ REPEATABLE READ以上では、直列化失敗による
    Txの再実行を考慮する必要がある
    ○ `ERROR: could not serialize access due to concurrent update`
    ○ ↑Tx中に他のTxによって同一行の更新が行われた
    ■ ロールバックされる
    65

    View Slide

  66. ロックとトランザクションで大事なこと
    ➢ 適切にロックを取り、データ不整合を防ごう
    ○ 反復不能読み取りが許容できない箇所では `SELECT
    FOR UPDATE` を使うといった対策が考えられる
    ➢ ソーシャル機能など、複数の更新主体があるとロック
    問題を起こしやすい
    ○ 誰がデータを更新し得るか(=どういうロックを取るか)を網
    羅しよう
    ➢ ロック・トランザクションの粒度はなるべく小さくする
    ○ ロック競合や性能の低下を防ぐ
    66

    View Slide

  67. (演習) ロックとトランザクションを体験しよう
    1. psqlでローカルのPostgreSQLにトランザクション処
    理を実行
    2. ターミナルを2つ開き、それぞれpsqlで接続
    a. 更新Tx処理を行い、未コミットのデータが他方から見えないことを確認
    b. 互いにロック競合するTx処理をしてみよう
    i. テーブルロック、行ロックそれぞれ試してみる
    ii. ロック競合しないパターンについても動作確認してみる
    3. デッドロックの実験
    a. ↑と同じ要領で。
    (参考) 複数プロセスが同時に操作しても問題ないことを確認する
    には、スクリプトを書くなどしてテストする
    67

    View Slide

  68. まとめ:大事なこと(再掲)
    ➢ クソクエリを本番DBに流さない
    ➢ ORMを使っていても、発行されるSQLを意識しよう
    ➢ 実行計画を見よう
    ➢ 負荷試験をしよう
    ➢ 正しいDB設計でデータの整合性を守り、パフォーマ
    ンスを担保する
    ➢ DBMSの公式ドキュメントを読もう
    68

    View Slide

  69. その他のトピック
    ➢ RDBMS共通
    ○ ストアドプロシージャ(PostgreSQLだとFUNCTION)
    ○ トリガー
    ○ VIEW
    ○ シーケンス
    ○ レプリケーション
    ○ PARTITIONING
    ➢ PostgreSQL特有
    ○ プロセスの種類と役割
    ○ VACUUM ← 追記型アーキテクチャ
    ➢ 「何それ?」というものがあれば調べてみて下さい
    69

    View Slide

  70. 参考文献等
    ➢ 『理論から学ぶデータベース実践入門』 奥野幹也 /
    2015年
    ➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の
    鉄則』 勝俣智成,佐伯昌樹,原田登志 / 2014年
    ➢ PostgreSQL公式ドキュメント(日本語)
    ○ 第14章 性能に関するヒント … チューニングTips
    70

    View Slide

  71. お疲れ様でした。
    71

    View Slide

  72. おまけ:1テーブル1インデックス
    ➢ 一度に1テーブルで1インデックスしか使われないとい
    う原則の話。
    ➢ 参考: インデックスの結合 : ひとつのテーブルへのア
    クセスに複数のインデックスを使用します。
    ➢ PostgreSQLではBitmapスキャンがあるので、カー
    ディナリティ(データのばらつき)の低い複数の列にそ
    れぞれ単列のインデックスを張っておくことが有効な
    こともある
    ○ 参考: 複合インデックス非推奨: つれづれネット散歩
    72

    View Slide