Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

RDB, RDBMSとは 6

Slide 7

Slide 7 text

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 表：例 - 顧客テーブル

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

DB設計 11

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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 の関係を実現

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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 アリゾナ 「候補キー → 非キー属性」へ の関数従属性を除く

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

制約 - 外部キー制約① ➢ 複数テーブル間でデータの整合性をとるために使う ➢ `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) ); 参照テーブル 被参照テーブル

Slide 22

Slide 22 text

制約 - 外部キー制約② 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しようとしてエラー

Slide 23

Slide 23 text

制約 - 外部キー制約③ 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 ... 被参照行の削除時、参照行も削除する

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

SQL 27

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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 ※一部簡略化しています。

Slide 30

Slide 30 text

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;

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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;

Slide 33

Slide 33 text

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上限が定まらない限りは安易に 使うべきではない

Slide 34

Slide 34 text

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 表:顧客テーブル 表:店舗テーブル

Slide 35

Slide 35 text

結合の種類 ➢ 条件による結合 ○ 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

Slide 36

Slide 36 text

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 不慣れな人は一通りどうぞ

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

閑話:データ型とデータサイズ(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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

INDEXと実行計画 40

Slide 41

Slide 41 text

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文書

Slide 42

Slide 42 text

どうインデックスを作るべきか？ ➢ 検索やソートの要件に応じて、必要なイ ンデックスを作る ➢ 例) 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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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して問合せ

Slide 51

Slide 51 text

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件を返すまでのコ スト .. 全件を返すまでのコ スト

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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 弱 強

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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 より

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

トランザクション分離レベルについて 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でもこれを使って いる。

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

お疲れ様でした。 71

Slide 72

Slide 72 text

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