実践RDBMS〜PostgreSQL編〜

Transcript

1. 実践RDBMS〜PostgreSQL編〜 2018/5 IKEDA Kiyoshi 1
2. About Me ➢ 2009〜2017 ... 渋谷の某IT企業でソシャゲ開発やイ ンフラ運用に勤しむ ◦ ほぼMySQL ◦ 高負荷や大量データとの戦いの日々を経験 ➢ 2017〜 ... M3 ◦ クラウド電子カルテ ... PostgreSQL ◦ AIチーム ... MySQL, PostgreSQL ➢ PostgreSQL勉強中 ➢ Oracle未着手 2
3. はじめに ➢ 今日の目的 ◦ RDBを用いたアプリケーションの開発に必要な基礎知識を身に着け る。 ➢ 前提 ◦ SQLを書いたことがある ◦ RDBを使ったアプリケーション開発の経験が浅い ◦ 一般的な知識を復習したい ➢ 今日やらないこと: ◦ 理論的なことには深入りしない ◦ Oracle, MySQLについてはほぼ触れない ◦ ハンズオンの時間はありません ▪ 演習問題をいくつか用意しています←後ほど各自でどうぞ ▪ サンプルリポジトリ有り(後掲) 3
4. 今日伝えたい大事なこと ➢ クソクエリを本番DBに流さない ➢ ORMを使っていても、発行されるSQLを意識しよう ➢ 実行計画を見よう ➢ 負荷試験をしよう ➢ 正しいDB設計でデータの整合性を守り、パフォーマ ンスを担保する ➢ DBMSの公式ドキュメントを読もう 4
5. お品書き ➢ RDB, RDBMSとは ➢ DB設計 ◦ ER図, 正規化 ◦ 制約 ➢ SQL ◦ SQLの基礎 ◦ リレーション演算, JOIN ➢ INDEXと実行計画 ➢ ロックとトランザクション 5
6. RDB, RDBMSとは 6
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 表：例 - 顧客テーブル
8. RDBの理論 ➢ リレーショナルモデル ... データを「リレーション」という概 念で表現する ➢ リレーション ... テーブルに相当 ➢ 属性 ... テーブルの「列」に相当 ➢ データを集合として扱う ◦ 集合操作(和、差、積など)をサポート ◦ よく使うのは「射影」「選択(制限)」「結合」(※後述) 8
9. RDBMS ➢ RDB Management System ◦ RDBを管理するためのソフトウェア ➢ PostgreSQL, Oracle, MySQL, etc. ➢ 機能 ◦ SQL ◦ トランザクション ◦ バックアップ ◦ リストア ◦ クラッシュリカバリ ◦ レプリケーション ◦ 権限管理 ➢ ACID特性を持つ 9
10. ACID特性について ➢ トランザクション処理を持つシステムの備えるべき4つ の性質 ➢ Atomicity(原子性) ◦ 一連の処理は全て実行されるか、全く実行されないかのど ちらか ➢ Consistency(一貫性) ◦ 処理の前後でデータの整合性が保たれる ➢ Isolation(独立性) ◦ 処理中の更新は確定するまで他の処理から不可視 ➢ Durability(永続性) ◦ 確定した結果が失われない 10
11. DB設計 11
12. サンプルDB 12 ➢ https://github.com/progrhyme/postgres-tutorial ➢ ER図: http://www.postgresqltutorial.com/postgresql-sample-database/ ➢ 以降の説明の一部でこのdvdrentalデータベースを使 う。
13. RDBにおけるDB設計 ➢ 例: 「DVDレンタルショップのシステムを作ろう」 ➢ 関連するデータ属性の組をリレーション(=テーブル) にまとめていく。 ◦ store ... 店舗 ◦ staff ... 店員 ◦ film ... 映画 ◦ actor ... 俳優 13
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 の関係を実現
15. ER図について ➢ テーブル間のデータのカーディナリティ(多重度)を視覚化でき る ➢ あると引き継ぎ時など便利 ➢ テーブル数が20を超えるぐらいから可視化が難しくなる ◦ 主要部分について、または、機能単位などで作ると良い ➢ ツールは色々あるが、ERMasterやMySQL Workbenchが使 いやすかった記憶 ➢ DB上のテーブル定義からER図を生成できるものや、ER図か らDDLを生成できるものもある 15
16. リレーション(テーブル)の正規化 ➢ 正規化 ... データの重複や矛盾を防ぐための作業 ➢ 正規形(NF) ... 正規化の段階を示すもの ◦ 第1〜第6正規形まである ◦ BCNF(ボイスコッド正規形) ... 3NFと4NFの間 ◦ 第3正規形までは覚えよう ◦ 第n正規形(n >= 2)はより下位の正規形を満たす ▪ BCNFは1〜3NFを満たす ➢ 第1正規形 ◦ 重複がない(主キー or ユニークキーがある) ◦ スカラ値のみを持つ ▪ JSONとかNG ◦ NULL値を取らない 16
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 アリゾナ 「候補キー → 非キー属性」へ の関数従属性を除く
18. 2NF → 3NF 18 作品 監督 出身 A Tom アリゾナ B Eva ミネソタ C Tom アリゾナ 監督 出身 Tom アリゾナ Eva ミネソタ 作品 監督 A Tom B Eva C Tom 「非キー属性 → 非キー属性」 への関数従属性を除く
19. 正規化しない / あえて非正規化する例 ➢ 日付のNULL値をフラグ的に扱うやり方 ◦ 例) deleted_atがNULLならば削除されてないと見なす ➢ optionalな属性値をまとめて1つのカラムにJSON形 式で格納 ◦ RDB的にはアンチパターン ▪ インデックス効かない(例外: PostgreSQLのjsonb型) ▪ アプリケーションにロジックが寄る→データ不整合を生みやすい ◦ メリット: ALTERを避けられる ◦ NoSQLを使うという手もある ➢ JOINによってパフォーマンス劣化することがある vs 正しくクエリを書けば劣化しないはず 19
20. 制約 - 主キー、一意性制約、NOT NULL制約 ➢ 主キー ... レコードを一意に特定するための列(の組)。基本、 作る。 ◦ 複合主キー ... 複数列から成る主キー ◦ ナチュラルキー ... 意味のあるキー。ユーザ名, 氏名, メールアドレ スなど ◦ サロゲートキー ... システムが発行する意味のないキー。連番のID など ➢ 一意性制約 = ユニークキー ◦ 主キー以外にも設けられる ➢ NOT NULL制約 ... 適宜付ける 20 昔のMySQLやPostgreSQLはNULLだとINDEXが効かない問題があった が、最近はINDEXが効く。PostgreSQLなら部分INDEX(後述)も使える。// 正規化の観点ではNULLは避けるべき
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) ); 参照テーブル 被参照テーブル
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しようとしてエラー
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 ... 被参照行の削除時、参照行も削除する
24. 制約 - 外部キー制約の注意点 ➢ 外部キーには暗黙的なインデックスが設定されない ➢ 先に挙げた2例だと city.country_id や inventory.film_id にインデックスがないと、countryや filmの行削除/更新時にフルスキャンが走る。 ➢ 参考: PostgreSQLアンチパターン：外部キー制約の 更新コストを見くびる - Qiita 24
25. DB設計 - 要点まとめ ➢ 第3正規形まではできるようになろう ➢ 主キーは付ける ◦ ナチュラルキー、サロゲートキーは適宜使い分けるべし(異 論は有るかも) ➢ 外部キー制約はデータの整合性担保のために有用 だが、パフォーマンス上は要注意 25
26. (演習) DB設計 ➢ ERMasterなどを使って、案件で利用しているDBの ER図を作ってみよう ➢ 正規化されていないテーブルがあれば、理由を考え てみよう。また、正規化するとしたらどうするか考えて みよう ➢ dvdrentalデータベースのfilmテーブルのデータを更 新・削除を試し、外部キー制約の挙動を確認しよう 26
27. SQL 27
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)
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 ※一部簡略化しています。
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;
31. SQLの基礎 - レコードの更新・削除 31 -- レコードの更新 UPDATE language SET name = 'Swidish' WHERE language_id = 6; -- レコードの削除 DELETE FROM language WHERE language_id > 10; WHERE句のないUPDATE, DELETEは 全件が対象となり、非常に危険！！
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;
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上限が定まらない限りは安易に 使うべきではない
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 表:顧客テーブル 表:店舗テーブル
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
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 不慣れな人は一通りどうぞ
37. (演習) SQL ➢ dvdrental DBについて ◦ ①film, film_category, categoryの3テーブルを内部結合し て、category_id = 2 (Animation) のtitleとnameを10件抽 出しましょう。 ◦ ②payment, customer, addressの3テーブルのデータか ら、2007年2月の売上を顧客の住所ごとに合計して上位 10件を抽出して下さい。サブクエリを使うと良いでしょう。 37
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
39. 閑話:データ型とデータサイズ ➢ 1行のサイズ ◦ 〜各列のデータサイズの合計 ◦ インデックスサイズ ▪ 〜キー列のデータサイズの合計 ➢ データ型は必要十分なサイズを選ぼう ➢ NG例: ◦ 数値しか入らないのに文字型を使う 39
40. INDEXと実行計画 40
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文書
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
43. 複合インデックスについて ➢ 複数列から成るインデックス ➢ 検索木は最初の列値で作られる ◦ => 2番目以降の列値で絞り込みやソートをしてもインデックスは使わ れない ◦ 先の例だと、(rental_id, payment_date)は(payment_date)の代替に ならない ➢ 複合PKでも同じ話 ➢ カバリングインデックス ◦ インデックスに含まれる列値のみをSELECT対象とする問合せで、イ ンデックスオンリースキャンを可能にする ▪ 実データ部にアクセスしないことでI/Oを抑えられる ◦ この目的であえて検索条件として不要な列値をインデックスに加えるこ ともある 43
44. 初心者がやりがちなインデックス効かない事例 ➢ 中間一致 ◦ SELECT * FROM t WHERE a LIKE ‘%foo%’; ➢ 後方一致 ◦ SELECT * FROM t WHERE a LIKE ‘%foo’; ➢ 否定形 ◦ SELECT * FROM t WHERE a <> 1; ➢ 参考: ◦ インデックスが使えない検索条件 - Qiita ※NULLはインデックス効きます 44
45. インデックスのトレードオフ、他 ➢ インデックスが多いほど、更新処理が重くなる ➢ インデックスのデータ量も増える ➢ 無駄なインデックスは省こう: ◦ 例) (a, b, c) というインデックスがあるなら (a) や (a, b) は 無駄。 ➢ 巨大なテーブルに後からインデックスを追加するのは 高コスト ◦ 使いそうなインデックスは予め作っておく 45
46. 閑話：部分インデックス(PostgreSQL) ➢ https://www.postgresql.jp/document/10/html/index es-partial.html ➢ CREATE INDEX … WHERE 〜 で対象となるレ コードを限定する。 ➢ 頻出値(デフォルト値など)を除外するときに有効 ➢ パフォーマンス向上する場合がある ◦ インデックスサイズが小さく ◦ 検索・更新が速く 46
47. 閑話：関数インデックス(PostgreSQL, Oracle) ➢ https://www.postgresql.jp/document/10/html/indexes-expr essional.html ➢ 列値そのものでなく、1つ以上の列から計算した結果 の値でインデックスを作る ➢ 例) ◦ メールアドレスに対してlower関数(小文字化)をかけてユ ニーク制約を付ける 47
48. PostgreSQLのクエリ処理と実行計画 参考: ➢ https://www.postgresql.jp/document/9.3/html/overview.html ➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の鉄則』4.3 問い合わせ の実行 48 図は割愛
49. 実行計画を見よう(PostgreSQL) ➢ 実行計画 … DBMSがどのようにクエリを実行するか、とい う内部情報。チューニングの重要な情報源 ➢ EXPLAIN 〜 ... 実行計画を表示 ➢ EXPLAIN ANALYZE 〜 ... 実際にクエリを実行し、 かかった時間と合わせて実行計画を表示 ◦ ※更新系SQLを食わせると危険！ 49
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して問合せ
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件を返すまでのコ スト .. 全件を返すまでのコ スト
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
53. 大事なこと ➢ 大量のフルスキャンを発生させない ◦ 適切にインデックスを張る ◦ インデックスが想定通り使われるか、実行計画を見て確認 ➢ リリース当初は良くても、データが溜まって性能劣化 することがある ◦ データが蓄積した状態の負荷試験をしましょう 53
54. ロックとトランザクション 54
55. ロック 55 ➢ 一方が処理している間、他方は処理できないようにブ ロックする。排他ロック ➢ 例) ◦ AとBがほぼ同時に1つしかないDVD『ジュラシックパーク』を借りようと する ◦ ロック機構がないと、2人とも借りることに成功してしまう ➢ PostgreSQLのロックは細かくて難しい ◦ テーブルレベルロック ▪ SELECTでもゆるいロックを獲得する ▪ 同一テーブルに競合するロックモードのトランザクションを同時に実行で きない ◦ 行レベルロック ▪ 2つのトランザクションは同じ行に対して競合するロックを保持できない
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 弱 強
57. PostgreSQLのテーブルレベルロックモード② 表: https://www.postgresql.jp/document/10/html/explicit-locking.html より 57
58. PostgreSQLのテーブルレベルロックまとめ ➢ SELECTは基本ロック競合しない ➢ SELECT FOR UPDATEの他、UPDATEなど行を更 新するクエリは行ロックする(後述) ➢ 大抵のDDLは更新操作と競合する ◦ DDLそのものとも競合する ➢ DROP TABLE, 一部のALTER TABLE, VACUUM FULLなどはSELECTとも競合する 58
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 より
60. デッドロック ➢ 異なる2者がそれぞれA→B, B→Aとロックを取り合 い、どちらも処理できなくなること ➢ PostgreSQLのデッドロック検出機構: ◦ deadlock_timeout(デフォルト1秒)ごとにロック獲得待ちの トランザクションをチェック ◦ デッドロックしていたらどちらかをabort ◦ 参考: PostgreSQLは雰囲気でデッドロックを殺す - そーだ いなるらくがき帳 ➢ 行ロックの場合、更新順序をソートするのがよくやる 解決策 60
61. トランザクション ➢ トランザクション ◦ 互いに関連する一連の処理を1まとめにしたもの ◦ ACID特性を満たす ▪ Atomicity ... 一連の処理は全て実行されるか、全く実行されないか • ロールバックが可能 ▪ Consistency ... 処理の前後でデータの整合性が保たれる ▪ Isolation ... 処理中の更新は確定するまで他の処理から不可視 ▪ Durability ... 確定した結果が失われない ➢ ロックとトランザクション ◦ ロックは獲得された後、トランザクションの終わりまで保持 される 61
62. トランザクションを使う ➢ psqlクライアント ◦ 開始 ... `BEGIN` or `START TRANSACTION` ◦ キャンセル ... `ROLLBACK` ◦ 確定 ... `COMMIT` ◦ ※トランザクションを使わないと、更新SQLを実行する度に ただちに確定される ➢ ライブラリから接続するとき、多くの場合、トランザク ションを有効にして使う ➢ PostgreSQLはDDLでもトランザクションを使える 62
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でもこれを使って いる。
64. 分離レベルが低いことで起こる問題 ➢ ダーティリード ◦ 他のトランザクション(Tx)による未コミットのデータを読み込んでしまう ➢ 反復不能読み取り ◦ 他のTxによるコミット済みのデータを読み込むことで、Tx内の同一行 に対するSELECTの結果が異なる ➢ ファントムリード ◦ ある範囲内への他のTxによるレコード挿入 + コミットによって、Tx内の 該当の範囲検索の結果が異なる ➢ 直列化異常 ◦ 複数のTxを正常にコミットした結果が、それぞれをあらゆる可能な順 序で実行した結果と一貫性がない 64
65. 分離レベルのトレードオフ ➢ 分離レベルが高いほどデータの信頼性は高くなる ➢ 引き換えに、性能が低くなりやすい ◦ トランザクション並列度が下がる ◦ ロック競合やデッドロックが発生しやすくなる ➢ REPEATABLE READ以上では、直列化失敗による Txの再実行を考慮する必要がある ◦ `ERROR: could not serialize access due to concurrent update` ◦ ↑Tx中に他のTxによって同一行の更新が行われた ▪ ロールバックされる 65
66. ロックとトランザクションで大事なこと ➢ 適切にロックを取り、データ不整合を防ごう ◦ 反復不能読み取りが許容できない箇所では `SELECT FOR UPDATE` を使うといった対策が考えられる ➢ ソーシャル機能など、複数の更新主体があるとロック 問題を起こしやすい ◦ 誰がデータを更新し得るか(=どういうロックを取るか)を網 羅しよう ➢ ロック・トランザクションの粒度はなるべく小さくする ◦ ロック競合や性能の低下を防ぐ 66
67. (演習) ロックとトランザクションを体験しよう 1. psqlでローカルのPostgreSQLにトランザクション処 理を実行 2. ターミナルを2つ開き、それぞれpsqlで接続 a. 更新Tx処理を行い、未コミットのデータが他方から見えないことを確認 b. 互いにロック競合するTx処理をしてみよう i. テーブルロック、行ロックそれぞれ試してみる ii. ロック競合しないパターンについても動作確認してみる 3. デッドロックの実験 a. ↑と同じ要領で。 (参考) 複数プロセスが同時に操作しても問題ないことを確認する には、スクリプトを書くなどしてテストする 67
68. まとめ：大事なこと(再掲) ➢ クソクエリを本番DBに流さない ➢ ORMを使っていても、発行されるSQLを意識しよう ➢ 実行計画を見よう ➢ 負荷試験をしよう ➢ 正しいDB設計でデータの整合性を守り、パフォーマ ンスを担保する ➢ DBMSの公式ドキュメントを読もう 68
69. その他のトピック ➢ RDBMS共通 ◦ ストアドプロシージャ(PostgreSQLだとFUNCTION) ◦ トリガー ◦ VIEW ◦ シーケンス ◦ レプリケーション ◦ PARTITIONING ➢ PostgreSQL特有 ◦ プロセスの種類と役割 ◦ VACUUM ← 追記型アーキテクチャ ➢ 「何それ？」というものがあれば調べてみて下さい 69
70. 参考文献等 ➢ 『理論から学ぶデータベース実践入門』 奥野幹也 / 2015年 ➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の 鉄則』 勝俣智成，佐伯昌樹，原田登志 / 2014年 ➢ PostgreSQL公式ドキュメント(日本語) ◦ 第14章 性能に関するヒント … チューニングTips 70
71. お疲れ様でした。 71
72. おまけ：1テーブル1インデックス ➢ 一度に1テーブルで1インデックスしか使われないとい う原則の話。 ➢ 参考: インデックスの結合 : ひとつのテーブルへのア クセスに複数のインデックスを使用します。 ➢ PostgreSQLではBitmapスキャンがあるので、カー ディナリティ(データのばらつき)の低い複数の列にそ れぞれ単列のインデックスを張っておくことが有効な こともある ◦ 参考: 複合インデックス非推奨: つれづれネット散歩 72