SQL Training 2021

Transcript

1. SQL 株式会社 AI Shift 三宅 悠太
2. 1. データベース 2. SQL I 3.トランザクション 4. データベース設計 5. インデックス 6. 実行計画 7. SQL II
3. データベース
4. データベースとは “A database is an organized collection of inter-related data that models some aspect of the real-world “ (CMU) データベースとは、実世界のある側面をモデル化した、秩序 だった、相互に関連したデータの集まり
5. DBMS • データベース管理システム（DBMS）は、データベースを管理するソフトウェア ◦ 例：MySQL, Oracle Database, SQLite, MongoDB • DBMSの目的は、アプリケーションが簡単にデータベースにデータを保存したり、保存 されたデータにアクセスしたりできるようにすること • しかし、初期のDBMSは構築・保守する側も、利用する側も苦労した ◦ 最大の原因は、論理層と物理層の密結合 データベース DBMS Application
6. 論理層と物理層の密結合問題の例 1, ‘佐藤’, 20 2, ‘山田’, 19 3, ‘金子’, 18 学生(学生番号、氏名、年齢) ‘写真部’, 1 ‘軽音部‘’, 2 ‘茶道部’, 3 クラブ(クラブ名、学生番号) • 2つのCSVファイルから構成 DBMS Application • レコードの検索/追加/変更時は、都度ファイルをパースしなきゃ... • 物理ファイルの構成が変更されたら、こちらも変更しなきゃ... • データベースの抽象化が上手くできないため、ファイル単位でやり 取り 学生クラブの 部員名簿 モデル化 学生データ 学生が参加するクラブ 1 2 3 4
7. リレーショナルモデル • 1970年にエドガー・コッド博士が考案 • データベースを抽象化し、例に挙げたような問題を回避 ◦ シンプルなデータ表現・データ構造 → リレーション ◦ 高水準な言語によるデータ操作 → SQL ◦ データの独立性 • 彼の論文発表以降、関係モデルに準拠するDBMSの開発・商業化が進み、デ ファクトになっていく ◦ 伝統的に、DBMSといえば関係モデル準拠を指す（正確にはRDBMS） エドガー・コッド博士
8. https://hpi.de/naumann/projects/rdb ms-genealogy.html DBMSの系譜
9. https://hpi.de/naumann/projects/rdb ms-genealogy.html DBMSの系譜
10. データモデル • データモデル ◦ 対象の実世界をデータで表現するための概念の集まり • スキーマ ◦ データモデルで記述された特定のデータの集まり • 代表的なデータモデルの種類 ◦ ネットワークモデル ◦ 階層モデル ◦ リレーショナル ◦ ドキュメント ◦ キーバリュー ◦ グラフベース ◦ カラムファミリー 実世界 データモデル データモデリング スキーマ データベース データベースをデータモデルで モデル化した場合の関係性
11. データモデル • データモデル ◦ 対象の実世界をデータで表現するための概念の集まり • スキーマ ◦ データモデルで記述された特定のデータの集まり • 代表的なデータモデルの種類 ◦ ネットワークモデル ◦ 階層モデル ◦ リレーショナル ◦ ドキュメント ◦ キーバリュー ◦ グラフベース ◦ カラムファミリー 準拠 -> 第1世代DB
12. データモデル • データモデル ◦ 対象の実世界をデータで表現するための概念の集まり • スキーマ ◦ データモデルで記述された特定のデータの集まり • 代表的なデータモデルの種類 ◦ ネットワークモデル ◦ 階層モデル ◦ リレーショナル ◦ ドキュメント ◦ キーバリュー ◦ グラフベース ◦ カラムファミリー 準拠 -> 第2世代DB この研修の中心
13. データモデル • データモデル ◦ 対象の実世界をデータで表現するための概念の集まり • スキーマ ◦ データモデルで記述された特定のデータの集まり • 代表的なデータモデルの種類 ◦ ネットワークモデル ◦ 階層モデル ◦ リレーショナル ◦ ドキュメント ◦ キーバリュー ◦ グラフベース ◦ カラムファミリー -> NoSQL (“Not Only SQL”)
14. SQL I
15. SQL DBMSにおいて、データの操作や定義を行うための高水準な言語 client SQL文 DBMS 結果
16. SQLの歴史 • 1970年、IBMから関係モデルの論文が公表される • 1970年代、IBMが「SEQUEL」を開発 ▪ System R の操作言語 • 1980年代、DBMSの商業化が進む • 1986-87年、ANSI/ISOによる言語の標準化 ◦ Structured Query Language (SQL) ◦ 数年おきに更新 ◦ 最新は2016 標準規格 主要な追加機能 SQL:1999 正規表現、トリガー、ユーザー定義関数 SQL:2003 XML、ウィンドウ関数、シーケンス SQL:2008 TRUNCATE SQL:2011 テンポラルテーブル SQL:2016 JSON、ポリモーフィックテーブル 標準規格一覧（一部抜粋）
17. SQLの特徴 • 実装の多様性 ◦ SQLはDBMS向けの標準規格ではあるが。。 ◦ 準拠の程度や実装はベンダー依存 ◦ 利用するDBMSの製品仕様を読もう • 汎用的・機能豊富 ◦ 長年の機能拡張により • 宣言型 ◦ SQLユーザーはデータをどうやって得るかではなく、何が欲しいかを記述すればOK ▪ データ独立万歳
18. SQLが扱うデータの単位は何か 実世界 リレーショナルモデル (リレーショナル) データベース データモデリング • DBMSはリレーショナルモデル準拠 • リレーションがデータの基本単位 リレーション
19. sid int name text age int major text 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 リレーション student タプル リレーション 属性 例：学生リレーションを二次元表として可視化した図 • リレーション（テーブル） ◦ スキーマ：データの構造や制約の定義（メタデータ） ◦ インスタンス：スキーマを満たす実際のデータ集合 • 属性（列、フィールド） ◦ ドメイン: 有効な値の範囲 • タプル（行、レコード） ◦ 属性値の集まり、何らかの”事実”を表す ◦ インスタンス＝タプルの集合＝”似たような事実”の集まり
20. SQLテーブルとの主な違い • SQLテーブルの特徴 ◦ 行の重複 OK（”事実”が複数保存できてしまう...） ◦ 属性値が不明な状態 (NULL) OK ◦ 集合ではなくタプルの多重集合
21. 用語の整理 リレーショナルモデルの 公式用語 非形式的な日常用語 SQL データの内部表現 関係（リレーション） 表（テーブル） ファイル 組（タプル） 行（ロウ） レコード 属性（アトリビュート） 列（カラム） フィールド 用語の対応表 • 文脈によって使い分けが必要なときがくるかもしれないので、対応 表を頭にいれておくと良いかも
22. SQLのサブ言語 • データ定義言語 (DDL) ◦ CREATE, ALTER, DROP, ... • データ操作言語 (DML) ◦ SELECT, INSERT, UPDATE, DELETE, ... • ...
23. CREATE student cid name budget 1 写真部 300 2 軽音部 450000 3 茶道部 9000000 club cid sid joined_at 1 1 2021/4/15 2 1 2021/4/15 3 3 2021/5/1 club_member sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 • CREATE文は、テーブルの構造（スキーマ）を定義する時に利用
24. CREATE CREATE TABLE student ( sid INTEGER, name CHAR(20), age INTEGER, major CHAR(20); sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 • 基本的に、テーブル名と(属性名、属性のデータ型)のリストを書くだけ
25. CREATE: 主キー sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 CREATE TABLE student ( sid INTEGER, name CHAR(20), age INTEGER, major CHAR(20), PRIMARY KEY (sid)); 主キー (Primary Key) • テーブル内の1行を一意に識別するための”検索キー” • 主キー列では、値の重複が禁止される（主キー制約） • 複数の列からも構成できる（複合主キー） ◦ 例: PRIMARY KEY(name, age)
26. CREATE CREATE TABLE student ( sid INTEGER, name CHAR(20), age INTEGER, major CHAR(20), PRIMARY KEY (sid)); CREATE TABLE club ( cid INTEGER, name CHAR(20), budget INTEGER, PRIMARY KEY (cid)); cid name budget 1 写真部 300 2 軽音部 450,000 3 茶道部 9,000,000 sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid)); sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 Q. なぜ PKはsidだけとか、cidだけ ではだめだったのか
27. CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES student(sid)); sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 CREATE: 外部キー sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 外部キー (Foreign Key) • 特定のテーブルの列を参照するためのキー (“ポインター”) • 2つのテーブルの間に関連（親子関連）をもたらす • 主キーと違い複数設定できる cid name budget 1 写真部 300 2 軽音部 450000 3 茶道部 9000000 参 照 (references) 親テーブル 子テーブル 外部キー
28. CREATE: 外部キー sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 参 照 (references) CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES student(sid), FOREIGN KEY (cid) REFERENCES club(cid)); 子テーブル 親テーブル sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 cid name budget 1 写真部 300 2 軽音部 450000 3 茶道部 9000000 親テーブル 外部キー (Foreign Key) • 特定のテーブルの列を参照するためのキー (“ポインター”) • 2つのテーブルの間に関連（親子関連）をもたらす • 主キーと違い複数設定できる
29. CREATE: 外部キー制約 sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 外部キー制約（参照整合性制約） 1. 子テーブルの外部キーの値は親テーブルの主キーに存在する値 でないといけない 2. 親テーブルの参照されるキーに対してのUPDATE/DELETEを禁止 cid name budget 1 写真部 300 2 軽音部 450000 3 茶道部 9000000 ✕ 1. INSERT (4,...) ✕ 2. DELETE sid=1 親テーブル 子テーブル sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学
30. MySQLの参照アクション アクション ON UPDATE ON DELETE RESTRICT 親テーブルへのUPDATEは拒否される （デフォルト） 親テーブルへのDELETEは拒否される （デフォルト） NO ACTION RESTRICTと同様 RESTRICTと同様 CASCADE 親テーブルへのUPDATEが実施され，子 テーブルの一致する行を自動的に UPDATE 親テーブルへのDELETEが実施され，子テーブルの 一致する行を自動的にDELETE SET NULL 親テーブルへのUPDATEが実施され，子 テーブルの外部キーを NULLに設定 親テーブルへのDELETEが実施され，子テーブルの 外部キーを NULLに設定 https://gihyo.jp/dev/serial/01/mysql- road-construction-news/0063 • 参照アクションは、親テーブルの参照されるキーに対してUPDATE/DELETEした場 合の子テーブルの外部キーに与える影響を指す • UPDATE/DELETE、それぞれ4つのアクションから選べる FOREIGN KEY (sid) REFERENCES student ON UPDATE RESTRICT ON DELETE RESTRICT 記述方法
31. PKとFKは設定しておこう • 基本的に主キーと外部キーは設定しよう！ • 主キーのメリット ◦ 行の重複が防げる ◦ クエリで個別の行を参照できる ◦ 外部キー参照をサポート • 外部キーのメリット ◦ 参照整合性が保たれる ◦ アプリには難しいCASCADEアクション • 上記をApp側でやるのは手間なので、DBに任せよう
32. ここまでの要点 • DBMSはリレーショナルモデルがベース ◦ 構造：テーブル ◦ 操作：SQL ◦ 整合性：主キー制約、外部キー制約 • SQL ◦ データ定義言語 ▪ CREATE TABLEでスキーマを定義 • テーブル名、属性名、属性のデータ型 • 主キー制約、外部キー制約（基本は設定しよう！） ◦ データ操作言語 ▪ 次はSELECT
33. SELECT文 • SELECT文はデータを取得するために利用する • テーブルを引数にテーブルを返す関数みたいなもの ◦ クエリ結果は0行以上のテーブル SELECT文 Shell Command
34. • SELECT句 ◦ クエリ結果として出力する行を形成するための<式のリスト> ◦ 式に書けるのは列名、計算式、定数など(例:「age」,「age + 2」,「1」) • WHERE句 ◦ 検索対象の行を絞り込むための<述語> (検索条件) ◦ 述語＝真偽値を返す関数 (例: age = 20, age >= 19 AND age <= 22 ...) 単純クエリ 単純クエリの構文 SELECT <式のリスト> FROM <テーブル名> [WHERE <述語>];
35. SELECT DISTINCT sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 4 金子 18 経済学 sid name 2 山田 3 金子 SELECT DISTINCT name FROM student WHERE age < 20; • SELECT DISTINCTはクエリ結果（SELECT句の処理結果）から重複 行を除外する修飾子 ◦ 出力を制御する役割 例：20歳未満の学生一覧 (氏名の重複なし)
36. 出力制御: ORDER BY SELECT sid, name FROM student WHERE age < 20 ORDER BY name; • クエリ結果を任意の列で昇順にソート • デフォルトでは昇順 (ASC) だが、降順 (DESC) にもできる • 複数のASCとDESCのミックス SELECT sid, nam FROM student WHERE age < 20 ORDER BY age DESC, name ASC; 例：氏名の辞書順で並び替え 例：年齢順、名前順で並び替え
37. • クエリ結果を最初の <整数> 行に絞る • ORDER BY句と一緒に使おう ◦ クエリ結果の順序が非決定的になるのを避ける 出力制御: LIMIT SELECT sid, name FROM student WHERE age < 20 ORDER BY name; LIMIT 5; 例：学生一覧（名前順）の上位5つだけ返す
38. • SELECT句に書けるもう一つのもの • 複数行の列の値を集計し1つの値を返す関数 • 関数名(<値式>)の形 • AVG, SUM, COUNT, MAX, MINなど 集約関数 SELECT AVG(age) FROM student WHERE major = ‘計算機科学’; AVG(age) 21.9000 sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 .. .. .. .. クエリ結果 集約＝複数行を1行にまとめる 例：計算機科学を専攻する学生の平均年齢
39. 集約関数: COUNT Q. 計算機科学を専攻する学生の数がしりたい SELECT COUNT(*) FROM student WHERE major = ‘計算機科学’; COUNT(*) 30 SELECT COUNT(age) FROM student WHERE major = ‘計算機科学’; COUNT(age) 30 SELECT COUNT(1) FROM student WHERE major = ‘計算機科学’; COUNT(1) 30
40. 集約関数: COUNT SELECT COUNT(*) FROM student WHERE major = ‘計算機科学’; COUNT(*) 30 SELECT COUNT(age) FROM student WHERE major = ‘計算機科学’; COUNT(age) 30 SELECT COUNT(1) FROM student WHERE major = ‘計算機科学’; COUNT(1) 30 • COUNT(*)とCOUNT(定数)は全行数 • COUNT(列名)はNULLを除外した行数 Q. 計算機科学を専攻する学生の数がしりたい
41. 複数の集約関数 SELECT AVG(age), COUNT(*) FROM student WHERE major = ‘計算機科学’; AVG(age) COUNT(*) 21.9000 30
42. DISTINCT 集約関数 SELECT COUNT(DISTINCT name) FROM student; • 重複値を排除した上で集計できる • COUNT、SUM、AVGがDISTINCTをサポート
43. • グループ毎に集計を行いたい時に利用 • GROUP BY <列のリスト(集約キー)> ◦ テーブルを集約キーでグループ分けし、グループ単位で集計 （GROUP BY＝グルーピング＋集約） ◦ クエリ結果の行数 = ユニークなグループの数 GROUP BY SELECT major, AVG(age), COUNT(*) FROM student GROUP BY major; 計算機科学 文化人類学 経済学 AVG(age)=20.8 COUNT(*)=21 AVG(age)=21.9 COUNT(*)=30 AVG(age)=21.3 COUNT(*)=41 major AVG(age) COUNT(*) 計算機科学 21.9 30 経済学 21.3 41 文化人類学 20.8 21 例：学生の専攻毎に集計 集約キーで集合をカットしたイメージ
44. GROUP BY SELECT major, AVG(age), COUNT(*), age FROM student GROUP BY major, age; • GROUP BY句の使用時にSELECT句に書けるもの ◦ GROUP BY句で指定した集約キー ◦ 集約関数 ◦ 定数
45. 個人と集団の属性の違い • GROUP BYを使うということは ◦ 個人の属性（列）は気にしない ▪ 例：Aさんの年齢 ◦ 集団の属性だけ気にする ▪ i.e., 統計的な属性 ▪ 例：チームXの平均年齢 • GROUP BY以降は、頭を集合指向に切り替えよう！
46. • 検索対象のグループを絞り込みたい時に利用 • 条件に含められるもの ◦ GROUP BY句で指定した集約キー、集約関数、 • GROUP BY句と一緒にしか使えない HAVING SELECT major, AVG(age), COUNT(*) FROM student GROUP BY major HAVING COUNT(*) >= 30; 計算機科学 文化人類学 経済学 AVG(age)=20.8 COUNT(*)=21 AVG(age)=21.9 COUNT(*)=30 AVG(age)=21.3 COUNT(*)=41 例：集計対象の専攻を学生数30以上のものに絞る ｘ
47. 全部使ってみる SELECT major, AVG(DISTINCT age) AS avg_age FROM student WHERE age <= 20 GROUP BY major HAVING COUNT(*) >= 10 ORDER BY avg_age DESC LIMIT 1; Q. このクエリが実行されると、どう いった処理がされるか
48. SELECT文の評価順序 結果の表示 クエリの実行 • SELECT文には論理的な評価順序（実行順序）がある ◦ 実際の実行順序はDBMSが決定 ◦ もちろん、製品によって評価順序は違う これから見ていくステップ SELECT major, AVG(DISTINCT age) AS avg_age FROM student WHERE age <= 20 GROUP BY major HAVING COUNT(*) >= 10 ORDER BY avg_age DESC LIMIT 1;
49. 結合 (JOIN) SELECT <値式のリスト> FROM <テーブル名のリスト> WHERE ...; 結合クエリの構文 • 結合は1つのクエリで複数テーブルのデータを扱いたい時に利用 • 結合の種類は3つ ◦ クロス結合 (CROSS JOIN) ◦ 内部結合 (INNER JOIN) ◦ 外部結合 (OUTER JOIN)
50. CROSS JOIN SELECT * FROM student, club_member; sid name age major 1 佐藤 20 計算機科学 2 山田 19 計算機科学 3 金子 18 経済学 sid name age major sid cid joined_at 1 佐藤 20 計算機科学 1 1 2021/4/15 1 佐藤 20 計算機科学 1 2 2021/4/15 1 佐藤 20 計算機科学 2 3 2021/5/1 2 山田 19 計算機科学 1 1 2021/4/15 2 山田 19 計算機科学 1 2 2021/4/15 2 山田 19 計算機科学 2 3 2021/5/1 3 金子 18 経済学 1 1 2021/4/15 3 金子 18 経済学 1 2 2021/4/15 3 金子 18 経済学 2 3 2021/5/1 sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 CROSS JOIN (クロス結合) • クロス結合の結果は、全てのテーブルの行 の全ての組み合わせを網羅したもの ◦ 例：各学生に対して、部活メンバーテーブルの3つ の行との組み合わせが考えられるので、合計9行 クエリ結果
51. INNER JOIN • 内部結合はクロス結合の部分集合 ◦ “内部”とはそういう意味 • 内部結合の結果は、クロス結合から結合条件を 満たす行だけに絞ったものになる • 結合条件の分類 ◦ 等値結合 (=) ◦ 非等値結合 (>, <, <>, !=, ...) sid name age major cid 1 佐藤 20 計算機科学 1 1 佐藤 20 計算機科学 2 2 山田 19 計算機科学 3 クエリ結果 sid name age major sid cid joined_at 1 佐藤 20 計算機科学 1 1 2021/4/15 1 佐藤 20 計算機科学 1 2 2021/4/15 1 佐藤 20 計算機科学 2 3 2021/5/1 2 山田 19 計算機科学 1 1 2021/4/15 2 山田 19 計算機科学 1 2 2021/4/15 2 山田 19 計算機科学 2 3 2021/5/1 3 金子 18 経済学 1 1 2021/4/15 3 金子 18 経済学 1 2 2021/4/15 3 金子 18 経済学 2 3 2021/5/1 SELECT s.*, cm.cid FROM student s, club_member cm WHERE s.sid = cm.sid; 条件マッチ
52. 1. SELECT s.*, cm.cid FROM student s, club_member cm WHERE s.sid = cm.sid; JOIN 構文 2. SELECT s.*, cm.cid FROM student s INNER JOIN club_member cm ON s.sid = cm.sid; 1. SELECT * FROM student, club_member; 2. SELECT * FROM student CROSS JOIN club_member; クロス結合 内部結合
53. SELECT s.*, cm.cid FROM student s LEFT OUTER JOIN club_member cm ON s.sid = cm.sid; LEFT OUTER JOIN • 内部結合と同様に、結合条件を満たす行を全て 返す • さらに、マッチしなかった左側のテーブルの行も残 す ◦ cidはNULLで埋める クエリ結果 sid name age major sid cid joined_at 1 佐藤 20 計算機科学 1 1 2021/4/15 1 佐藤 20 計算機科学 1 2 2021/4/15 1 佐藤 20 計算機科学 2 3 2021/5/1 2 山田 19 計算機科学 1 1 2021/4/15 2 山田 19 計算機科学 1 2 2021/4/15 2 山田 19 計算機科学 2 3 2021/5/1 3 金子 18 経済学 1 1 2021/4/15 3 金子 18 経済学 1 2 2021/4/15 3 金子 18 経済学 2 3 2021/5/1 sid name age major cid 1 佐藤 20 計算機科学 1 1 佐藤 20 計算機科学 2 2 山田 19 計算機科学 3 3 金子 18 経済学 NULL 条件に合うものなし
54. OUTER JOIN • 外部結合の種類 ◦ 左外部結合 (LEFT OUTER JOIN) ◦ 右外部結合 (RIGHT OUTER JOIN) ◦ 完全外部結合 (FULL OUTER JOIN) • 右と左の違いは、どちらをマスタに選ぶか ◦ マスタのデータ（行）はすべて残る • 両方ともマスタなのが、FULL OUTER JOIN
55. JOINの要点 • 結合の種類は3つ ◦ クロス結合 (CROSS JOIN) ◦ 内部結合 (INNER JOIN) ◦ 外部結合 (OUTER JOIN) ▪ 左外部結合 (LEFT OUTER JOIN) ▪ 右外部結合 (RIGHT OUTER JOIN) ▪ 完全外部結合 (FULL OUTER JOIN) • 補足 ◦ クロス結合を実務で使うことはほぼない ◦ 自己結合もできる（同じテーブルに別名をつけて結合するイメージ） • 結合したからと言って、それ以降の処理の流れは変わらない 結果の表示 クエリの実行
56. name kind calorie ロッキーロード THE 31 162 ナッツトゥユー ELEGANT 168 チョップドチョコレー ト CHOCOLATE 175 ... ... ... クエリを書いてみよう Q1. 31アイスクリームのフレーバーの組み合わせ一覧がほしい (“ダブルサイズ”の選択肢をください) ◦ ただし、同じフレーバーはなし ◦ 同じ組み合わせは一覧にいれないでほしい ◦ カロリーの合計もついでにほしい flavor1 flavor2 total_calorie ロッキーロード ナッツトゥユー 340 ナッツトゥユー チョップドチョコ レート 343 チョップドチョコレート ロッキーロード 337 ... ... ... Q2. 上の一覧から最適な組み合わせを一つ選んでほしい ◦ 合計カロリーが350以下 ◦ どちらかのフレーバーの種類は必ずELEGANT ◦ この条件にあうもので一番カロリーが低いもの Q3. 同じ感じで、最適なトリプルの組み合わせもほしいな...
57. トランザクション
58. 背景 • 同じレコードが同時に変更されることがある ◦ 競合状態をどう回避したらいい？ • 口座振り込みの途中でシステム障害が起きることがある ◦ DBをどんな状態に復旧したらいい？（正しい状態とは何か） アプリケーション SQL SQL SQL SQL DBMS アプリケーション アプリケーション SQL SQL SQL SQL SQL SQL SQL SQL
59. 背景 同時実行制御 クラッシュリカバリ • 同じレコードが同時に変更されることがある ◦ 競合状態をどう回避したらいい？ • 口座振り込みの途中でシステム障害が起きることがある ◦ DBをどんな状態に復旧したらいい？（正しい状態とは何か） アプリケーション SQL SQL SQL SQL DBMS アプリケーション アプリケーション SQL SQL SQL SQL SQL SQL SQL SQL
60. トランザクションの2つの機能 • 同時実行制御 ◦ 同時アクセスにより起こりうるデータの不整合を防ぐこと • クラッシュリカバリ ◦ 有事の際に、データベースを正しい状態で復旧すること
61. トランザクション • トランザクション (TX) とは、アプリケーションがDBに対する複数の読み書きを論理的な単位として まとめる方法 • DBMSはTXを変更の単位とみなす ◦ TXの変更処理は、全体として成功するか中断（失敗）するかのどちらか（シンプル！） • これによってアプリケーションはよりシンプルに書ける ◦ エラーが生じたら中断できるし、安全にリトライすることもできる ◦ 潜在的なエラーや並行性の問題を気にしなくて良い 1. Aさんの預金残高を確認 2. Aさんの口座から1万円を差し引く 3. Bさんの口座に1万円を振り込む 例：口座振り込みのトランザクション DBMS アプリケーション 成功 or 中断
62. トランザクション in SQL • 新しいTXは、BEGINで開始でき、COMMIT or ROLLBACK で終了できる ◦ If COMMIT -> DBMSは全ての変更を永続化するか、中断する ◦ If ROLLBACK -> 全ての変更が取り消される（なかったことになる） • 中断は、アプリケーション自らが行うか、DBMSによって起こされるのがどちらか BEGIN; SQL文; SQL文; SQL文; COMMIT; トランザクションのコミット BEGIN; SQL文; SQL文; SQL文; ROLLBACK; トランザクションのアボート
63. ACID特性 • TXが持つ特性、あるいはTXが提供する安全性の保証 ◦ ただし、分離性の意味は曖昧になりがち 原子性 (Atomicity): トランザクションに含まれる操作全てが成功か中断になる 一貫性 (Consistency): トランザクションを実行した前後ではデータの一貫性が損なわれない 分離性 (Isolation): 同時実行している複数のトランザクションは互いに独立している 永続性 (Durability): 一旦コミットが完了したトランザクションによる変更は永続化される
64. 原子性と永続性 • 原子性 ◦ TXが障害のために成功しなかったら、確実に中断されそれまでの操作が破棄 されることを保証 • 永続性 ◦ TXが成功したら、システム障害が起きようと変更は失われないことを保証 ▪ 完全な永続性は存在しない 1. Aさんの預金残高を確認 2. Aさんの口座から1万円を差し引く 3. Bさんの口座に1万円を振り込む 例：口座振り込みのトランザクション 原子性が無いと、この間で障害が起きると1 万円消える 永続性がないと、TX終了後、気づいたら1万 円振り込んでいなかったことになったりす る。。
65. 原子性と永続性 • 代表的な実現方法はロギング ◦ すべての操作ログを出力しておき、 ▪ TX中断後、逆順に操作をやり直す (Undo) ▪ システム障害後、コミット済みだがディスクに未反映の操作を再実行 (Redo)
66. 分離性 • 並行して実行されたTX同士が互いに影響を与えないことを保証 • 分離性の目的は、並行性の問題をアプリケーションから隠すこと 1. Aさんの預金残高を確認 2. Aさんの口座から1万円を差し引く 1. Bさんの口座に1万円を振り込む 口座振り込みのトランザクション 1. Aさんの預金残高を確認 2. Aさんの預金を出力 預金確認のトランザクション 例：ダーティ・リード（並行性の問題） 分離性がないと、1万円差し引かれた値が みえてしまう
67. どう実現するか • 単純に逐次処理していくという方法も考えられるが、現実には性能を考慮する必要 がある • TX間でどの程度の影響を許すか（どういう並行性の問題を許すか)にはいくつかの レベルが考えられていて、実現方法もそのレベルによって異なる
68. 分離性の種類 https://xtech.nikkei.com/it/article/COLUMN/20080123/291846/ 分離レベル (SQL-92)と並行性問題の関係 https://arxiv.org/pdf/cs/0701157.pdf 下の表ではSQL-92では指摘されなかったものも含まれている
69. ファジーリード 1. Aさんの預金残高を確認 2. Aさんの口座から1万円を差し引く 3. Bさんの口座に1万円を振り込む 口座振り込みのトランザクション 1. Aさんの預金残高を確認 2. Aさんの預金を出力 1. Aさんの預金座高を確認 2. Aさんの預金を出力 預金確認のトランザクション 出力値が変わる • ダーティーリードは、他のTXの未コミットな書き込みが読み取れる問題 ◦ ロールバックされると存在しないデータを読み込んだことになる • ファジーリードは、読み出した行が他のTXにより更新/削除され、同じTXで再度同じ行を読み込 んだときに、その行が更新/削除される問題
70. ファントムリード • 他のTXの書き込みによって、同じ検索条件で読んでいるのに，あったはずの行が消えたり， なかった行が現れたりする問題 ◦ ある検索条件を満たす行の集合を読み込んだ後に、他のTXがその検索条件に合うような 行を追加/削除するときに起こる https://en.wikipedia.org/wiki/Isolation_(database_systems)
71. 分離レベル in MySQL • MySQL 5.6 (InnoDB) • サポートされている分離レベル ◦ READ UNCOMMITTED ◦ READ COMMITTED ◦ REPEATABLE READ (デフォルト) ◦ SERIALIZABLE • 実現方法 ◦ MVCCとロック
72. 一貫性 • “TXを実行した前後ではデータの一貫性が損なわれない” • DBの状態遷移から考えると ◦ TX開始前に、DBが一貫性のある状態であれば、終了後は別の一 貫性のある状態へと遷移する ◦ データに変更はあるものの、一貫性が保たれている • 一貫性を損なうような操作は処理されず、中断される（一貫性は保 たれたまま） http://airccse.org/journal/ijdms/pape rs/7115ijdms04.pdf
73. 一貫性 • モチベーションから考える ◦ 多くの場合、データについて常に真でなければならない何らかの 言明（不変性）が存在する ▪ 例：口座振込において、引出額と振込額は同じでなければなら ない (“ビジネスルール”) ◦ 一貫性は、TX前後でその不変性を常に満たすことを保証 http://airccse.org/journal/ijdms/pape rs/7115ijdms04.pdf
74. 一貫性 • ACIDの中で唯一のアプリケーションの特性 ◦ DBMSはどのような不変性があるのかわからないので保証しよう がない ◦ 一貫性を保つようにTXを適切に定義することはアプリケーション の責任 ◦ (ただし、例えば、外部キー制約などの一種の不変性はデータ ベースがチェック可能) http://airccse.org/journal/ijdms/pape rs/7115ijdms04.pdf
75. トランザクションの要点 https://15445.courses.cs.cmu.edu/f all2020/slides/16-concurrencycontr ol.pdf • TXの主な機能は同時実行制御とクラッシュリカバリ • アプリケーションはTXを使うことでよりシンプルになる（DBMSに色々任せられる） • ACID特性はTXが提供する安全性の保証のこと（分離性には種類がある） • ACIDを簡単にまとめると
76. データベース設計
77. • 要求分析 ◦ データベースで管理したいデータやデータの使われ方等を整理 • 概念設計 ◦ 要件定義を元に、DB化の対象となる実世界をモデル化 ◦ 特定のDBMSのデータモデルには依存しない ◦ ERモデルが主流 • 論理設計 ◦ 概念モデルをDBMSのデータモデルでスキーマに変換 ◦ スキーマの改善 • 物理設計 ◦ インデックス、ファイルフォーマットなどの性能チューニング ◦ この後のステップにはセキュリティ設計などが含まれる データベース設計手順 概念設計 論理設計 物理設計 概念モデル 論理モデル 物理モデル 要求分析 要件定義 実世界
78. 概念設計：ERモデル ERモデル • 実体（エンティティ）や関連（リレーションシップ）を使って実 世界を表現するモデル • ERモデルで記述されたものがER図 以下のような質問に答えていく • 何が実体？ • どの実体同士が関連してる？ • 実体や関連はどんな属性が必要？ • 満たすべき整合性制約（ビジネスルール）は何？ 所属 部活 部活 名 部活 ID 学生 学生番 号 氏 名 専 攻 登録 日 実世界 ERモデル ER図 (Chen記法)
79. エンティティ • 実体 (entity) ◦ DBで表現したい実世界の対象物（例：山田、佐藤 ...） ◦ 属性値の集まりから構成される（例：学生番号、氏名 ...） • 実体集合 (entity set) or 実体型 (entity type) ◦ 同一種類の実体の集まり（例：学生、部活） ◦ ERモデルは集合単位で実世界を表現！ ◦ 主キーを1つ持つ (アンダースコア) 学生 学生番号 氏名 専攻
80. キー用語 • キー ◦ 実体集合の個々の実体を一意に識別できる属性もしくは属性 の組合せで極小なもの ▪ 例：学生番号，学生の氏名と住所 ◦ 複数存在しうるので、候補キーとも呼ばれる • 主キー ◦ キーの内、システムが使うのに最も便利なもの ◦ 実体は主キーを1つ持つ（キー制約） 学生 学生番号 氏名 専攻
81. 関連 • 関連（relationship) ◦ 実体間の相互関係 • 関連集合 or 関連型 ◦ 実体集合同様、総体として捉える ▪ 例：「学生は部活に所属する」という具合に ◦ 関連集合にも属性を付与可能 所属 部活 部活 名 部活ID 学生 学生番号 氏名 専攻 登録日 N M
82. 科目番号 複数の関連 • 実体集合は複数の関連集合に参加できる • ER図では、同一の実体集合に対する関連集合も記述可能 ◦ 役割（role）を明記 履修要件 科目 科目名 単位数 対象科目 前提科目 学生 学生番号 氏名 専攻 履修 N M N M
83. カーディナリティ • カーディナリティとは ◦ 関連集合における実体間の量的な対応関係を指す記号(1,N,M) ◦ 関連集合に参加できる実体の最大数を表す ◦ 一種の制約 • 対応関係による関連集合の分類 ◦ 多対多関連集合 (many-to-many) ◦ 1対多関連集合 (1-to-many) ◦ 多対1関連集合 (many-to-1) ◦ 1対1関連集合 (1-to-1) 多対多 N M A B 表記法の例 多対1 N 1 A B Bの実体がこの関連集合に参加できる最大の数
84. 対応関係のイメージ 山田 佐藤 金子 計算幾何学 生物学 文化人類学 経済学 鈴木 学生 専攻分野 専攻 N 1 専攻（多対1）
85. 各対応関係に当てはまる表現 • 多対多 ◦ 例：各学生は複数の部活に所属できて、各部活は複数の学生 を部員として登録できる • 1対多 ◦ 例：各学生は複数の部活の部長を務めることができるが、各 部活の部長は1人まで • 多対1 ◦ 例：各学生は1分野まで専攻分野を選択できるが、1つの分野 は複数の学生が専攻している • 1対1 ◦ 例：各学生が住める部屋は1部屋まで、かつ各部屋に住める 学生の数は1人まで 所属 N M 学生 部活 部長 1 N 学生 部活 専攻 N 1 学生 分野 居住 1 1 学生 部屋
86. カーディナリティとキー制約 • 「最大1人まで」のような表現にはキー制約が隠れている ◦ 例：各部活の部長は1人まで ▪ 部長関連集合に参加する部活を、部活の主キーで一意に特定する ことで実現 • i.e., 1つのキー制約で1対多関連を実現できる ◦ 1対1関連は両サイドからキー制約を課すことで実現できる 部長 1 N 学生 部活
87. 参加制約 • 参加制約(participation constraint) ◦ 関連に参加しない実体を許すかどうか ◦ 制約を課すことで「最低1人」のような表現が可能 • 参加制約の種類 ◦ 全面的 (total)：全ての実体は関連への参加が義務 (mandatory) ◦ 部分的 (partial)：関連へ参加は任意 (optional) • 全面的参加制約の例 ◦ 学生は最低1つの専攻分野を選択しなければならない ◦ 部活には最低1人の部長が必要
88. 参加制約のイメージと表記法 学生 専攻分野 専攻 N 1 • 表記法 ◦ 参加が全面的/義務なら実線 ◦ 参加が部分的/任意なら破線 山田 佐藤 金子 計算幾何学 生物学 文化人類学 経済学 鈴木 専攻（多対1） 全面的 部分的 • 参加制約のイメージ
89. Chen Notation 学生 専攻分野 専攻 (1,1) (0,N) N 1 (関連数の最小値, 関連数の最大値) • 例：専攻集合に個々の学生は最小何回、最大何回まで含まれるか 専攻集合{ (山田、計算機科学), (佐藤、文化人類学), (金子、経済学), (鈴木、文化人類学) } 参加制約 • 対応関係と参加制約の両方を明記する表記法
90. 弱実体集合 • 弱実体集合 (weak entity set) ◦ 別の実体集合（オーナー）に依存した存在 ▪ オーナーなしでは存在できない ◦ オーナーのPK+自分が持つ部分キーで一意に識別できる • 依存関連集合 ◦ オーナーと共に参加必須な1対多関連集合 ▪ 弱実体集合の参加は義務 • 例えば ◦ 物理の実習1は「物理-1」、数学の実習1は「数学-1」のような具合 で一意に特定できる 科目 実習課題 実習 科目番号 科目名 課題番号 課題名 オーナー 部分キー 1 N 依存関連 期限
91. ER図を書いてみよう • チームは一意なチーム名と彼らがプレーする専用のスタジアムを持つ • コーチは氏名を持つ • プレイヤーはプレイヤーID、氏名、スコアを持つ • データベースでは、どのプレイヤーがどのチームのどのポジションでプレーするかに加 えて、誰がチームのキャプテンで、誰がチームのコーチかというデータも管理したい • チームには複数のプレイヤーがいて、その中の1人は必ずキャプテン • 基本的には、プレイヤーは最大1チームでプレーしているが、一時的に無所属になるプ レイヤーもいる • チームにはコーチが必ず必要だが、複数のコーチがいてもかまわない • コーチの識別は、どのチームでコーチングするかで決まる Q. スポーツチームとプレイヤー情報をデータベースで管理したい 以下の要件をもとにER図を書いてみよう! (書き方は自由、drawio 推奨) ER図 チートシート
92. 補足: 様々な表記法
93. 論理設計 手順 • ER図からリレーショナルモデルを使ってスキーマへ変換 • スキーマの正規化
94. 実体集合の変換 学生 学生番号 氏名 専攻 sid name major 1 佐藤 文化人類学 2 山田 計算機科学 3 金子 経済学 CREATE TABLE student( sid INTEGER, name CHAR(20), major CHAR(20), PRIMARY KEY (sid)); • 実体集合からテーブルへの変換
95. 多対多関連集合の変換 • 例えば、学生は複数の部活に登録できるし、部活には複数の学生 が参加できる • 多対多関連集合はテーブルに変換 • テーブルに含めるもの ◦ 関連集合の全ての属性を追加 ◦ 関連集合に参加する各実体集合のキーを外部キーとして設定 ◦ 外部キーのセットを複合主キーとして設定 sid cid joined_at 1 1 2021/4/15 1 2 2021/4/15 2 3 2021/5/1 CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (cid, sid), FOREIGN KEY (sid) REFERENCES student(sid), FOREIGN KEY (cid) REFERENCES club(cid)); 所属 部活 学生 N M 登録日
96. • 例：学生は複数の部長を兼任できるが、各部活の部長は最大1人まで • 2通りのやり方 ◦ 関連集合をテーブルに変換 ▪ 主キーは部活のキーにし、それ以外は多対多と同じように作る ◦ 実体集合のテーブルに関連集合を統合 ▪ 部活テーブルに部長を統合 ▪ 関連の属性の追加＋学生を参照する外部キーを設定 1対多関連集合の変換 部活 学生 部長 N 1 登録日 CREATE TABLE club_director ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (cid), FOREIGN KEY (sid) REFERENCES student, FOREIGN KEY (cid) REFERENCES club); CREATE TABLE club ( cid INTEGER, name CHAR(20), director_id INTEGER, director_since DATE, PRIMARY KEY (cid), FOREIGN KEY (director_id) REFERENCES student(sid)); 部活名 部活ID
97. 全面的参加制約の変換 • 例：各部活には必ず最低1人の部長がいる • NOT NULL制約やCHECK制約を利用 CREATE TABLE club ( cid INTEGER, name CHAR(20), director_id INTEGER NOT NULL, director_since DATE, PRIMARY KEY (cid), FOREIGN KEY (director_id) REFERENCES student); 部活 学生 部長 N 1 登録日 部活名 部活ID 部長と部活を統合するやり方で作ったテーブル
98. 弱実体集合の変換 • 弱実体集合と依存関連集合を単一のテーブルに変換 ◦ 主キーは、オーナーの主キー＋部分キー ◦ オーナーの削除時に、全ての弱実体集合を削除する ために ON DELETE CASCADE CREATE TABLE exercise ( eid INTEGER, name CHAR(20), deadline DATE, PRIMARY KEY (eid, subject_id), FOREIGN KEY (subject_id) REFERENCES subject ON DELETE CASCADE); 科目 実習課題 実習 科目番号 科目名 課題番号 課題名 オーナー 部分キー 1 N 依存関連 期限
99. ここまでの要点 • 概念設計 ◦ 要件定義を元に、管理したい対象の実世界をモデリング • ERモデル ◦ 構造：実体、関連、属性、その他（弱実体） ◦ 制約：キー制約、参加制約 ▪ 外部キー制約は関連の定義に暗黙的に存在 • 論理設計 ◦ 概念モデルをDBのデータモデルでスキーマに変換 ◦ 次は、スキーマの改善（その前に、なぜするのか） 概念設計 論理設計 ER図（概念モデル） リレーショナルスキーマ（論 理モデル） 要求分析 要件定義 実世界 スキーマの改善
100. ERモデルの問題 • ERモデルはシンプルだけど、表現できない制約がまだまだある ◦ 特に、関数従属性など • ER図は主観的 ◦ 与えられたシナリオに対して様々な図が書けてしまう • 次の正規化は、数学的な視点でERモデルが表現しきれなかった制約と向き 合っていくプロセス 概念設計 論理設計 ER図（概念モデル） リレーショナルスキーマ（論 理モデル） 要求分析 要件定義 実世界 スキーマの改善
101. 正規化の目的とは • 目的：リレーションからデータの冗長性を排除すること • 冗長性：必要最低限のものに加えて、事実やデータの重複がある状態 • 冗長性の問題 ◦ スペースの無駄遣い ◦ 更新時の異常 ▪ 異常：データに論理的な不整合がある状態（事実が食い違った状態） • 要は、データの冗長性を排除して、データ不整合（更新時異常）を未然に防ぎたい！
102. 正規形 (Normal Form, NF) • 正規化とは、リレーションからデータの冗長性を排除していく一連のプロセス • 複数の進行段階 ◦ 各段階でその段階特有の冗長性を排除していくようなイメージ ◦ 各ステップ完了後に得られる形式が正規形 (NF) ▪ 例：第1正規化されたリレーションは、第一正規形として定義された条件を満たす ◦ 段階が上がるとデータの”純度”が増す ▪ i.e., 前の段階の条件も満たす ▪ 例：BCNFのリレーションは1NFから3NFまで全て満たす 第1正規形 (1NF) 第2正規形 (2NF) 第3正規形 (3NF) ボイスコッド 正規形 (BCNF) 第4正規形 (4NF) 第5正規形 (5NF) 第1正規化 第2正規化 非正規形 (NFNF) ・・・
103. 第一正規形 • 1NFの条件 ◦ 全ての属性が”アトミック”(単一値)であること ▪ 属性値はそのドメイン中の要素の1つであること ▪ i.e., リレーションであること • NULLや重複があっても正規化はできる • 1NFを満たさない例 学籍番号 氏名 履修科目 1 佐藤 Linux, データベース 2 山田 Linux, Go言語 3 金子 Go言語, kubernetes 学籍番号 氏名 履修科目1 履修科目2 1 佐藤 Linux データベース 2 山田 Go言語 Linux 3 金子 kubernetes Go言語 補足：上記の例は、SQLアンチパターン本で、それぞれジェイウォーク、マルチ カラムアトリビュートとして紹介されている 属性値に複数の値が含まれる 複数の属性が概念的に同じ（同じ事実を表せる）
104. 第一正規形 • 正規化の方法 ◦ ”繰り返し項目”を複数の行に分割 ◦ この例の場合、二通りのやり方が考えられる 学籍番号 氏名 履修科目 1 佐藤 Linux 1 佐藤 データベース 2 山田 Go言語 .. .. .. 学籍番号 履修科目 1 Linux 1 データベース 2 Go言語 .. .. ※ 主キーが変わる 別テーブルに分解 同一テーブル内に展開
105. 第二正規形 • 2NFの条件 ◦ 1NFであること ◦ 全ての非キー属性がキーに完全関数従属していること ▪ i.e., すべての部分関数従属性が排除された状態
106. 関数従属性 • 関数従属性はリレーションに存在しうる一種の制約 • X → Y と記述できる ◦ 意味：Xの値が決まれば、Yの値が一つに定まる ◦ 読み方：”YがXに関数従属している”、”XがYを関数的に決定する”
107. 関数従属性 • Xが複数列の場合 ◦ 例：{A, B} → C ◦ ”Cは{A,B}に関数従属している” ◦ Cの値はAとBの値の組み合わせで、1つに定まる • Yが複数列の場合 ◦ 例：主キー ◦ PK → {A, B, …, } ◦ AはPKに.., BはPKに..., 関数従属している ◦ （すべての属性が主キーに関数従属している）
108. 関数従属性は何が嬉しいのか • 冗長性を発見する手助けをしてくれる • 例：以下のテーブルには二つの関数従属が存在する ◦ {学籍番号, 部活ID} -> 登録日 ◦ 学籍番号 -> 氏名 学籍番号 氏名 部活ID 登録日 Q. この2つの関数従属を見た上で、冗長性の観点 から言えるこのテーブルの問題とは何か
109. 関数従属性は何が嬉しいのか • 冗長性を発見する手助けをしてくれる • 例：以下のテーブルには二つの関数従属が存在する ◦ {学籍番号, 部活ID} -> 登録日 ◦ 学籍番号 -> 氏名 学籍番号 氏名 部活ID 登録日 S1111 佐藤 1 4/12 S1111 佐藤 2 5/12 S2222 山田 3 5/12 学籍番号 氏名 部活ID 登録日 Q. この2つの関数従属を見た上で、冗長性の観点 から言えるこのテーブルの問題とは何か A. 学籍番号と氏名のペアが複数存在できてしまう！学 籍番号と部活IDのペアはキー制約によって一度しか格 納されないので問題なし！
110. テーブルの分解 • この冗長性もテーブルを分解することで解決できる！ 学籍番号 氏名 学籍番号 氏名 部活ID 登録日 学籍番号 部活ID 登録日 分解
111. テーブルの分解 • この冗長性もテーブルを分解することで解決できる！ 実は今排除したのが部分関数従属性！ -> 分解されたテーブル達は2NF 学籍番号 氏名 学籍番号 氏名 部活ID 登録日 学籍番号 部活ID 登録日 部分関数従属 分解 完全関数従属 完全関数従属
112. 関数従属性の種類 完全関数従属性 • X → Yの時 ◦ YはXに完全関数従属している • {A,B} → Yの時 ◦ A→ YもB→ Yも成り立たない時、Yは{A,B}に完全関 数従属している 部分関数従属性 • X → Yの時 ◦ 起こり得ない • {A,B} → Y が与えられた時 ◦ もしA→ CかB→ Cのどちらかが成り立つな ら、Cは{A,B}に部分関数従属している 学籍番号 氏名 学籍番号 部活ID 登録日 学籍番号 氏名 部活ID 登録日 部分関数従属 完全関数従属 完全関数従属 完全関数従属 C A B Y
113. 第二正規形 • 2NFの条件 ◦ 1NFであること ◦ 全ての非キー属性がキーに完全関数従属していること ▪ i.e., すべての部分関数従属性が排除された状態 ▪ （全てのキーが単一ならすでに2NF） • 正規化の方法 ◦ キーの一部によって一意に決まる非キー属性を別表に移す 学籍番号 氏名 学籍番号 氏名 部活ID 登録日 学籍番号 部活ID 登録日 部分関数従属 分解 完全関数従属 完全関数従属
114. 第三正規形 • 3NFの条件 ◦ 2NFであること ◦ 全ての非キー属性がキーに推移関数従属していないこと
115. 推移関数従属性 • やや形式的には ◦ キーXと非キーYが与えられ X → YかつY → Zなら、X → Zが成り立つ時 ZはXに推移関数従属していると表現 学籍番号 氏名 研究室ID 研究室名 推移関数従属 X Y Z 前提：学生が参加できる研究室は最大1つまで
116. 推移関数従属性 • やや形式的には ◦ キーXと非キーYが与えられ X → YかつY → Zなら、X → Zが成り立つ時 ZはXに推移関数従属していると表現 • 要するに3NFでは、非キー属性間の関数従属を排除したい 学籍番号 氏名 研究室ID 研究室名 関数従属 推移関数従属 X Y Z 前提：学生が参加できる研究室は最大1つまで
117. 第三正規形 • 3NFの条件 ◦ 2NFであること ◦ 全ての非キー属性がキーに推移関数従属していないこ と • 正規化の方法 ◦ 非キー属性によって一意に決まる非キー属性を別表に 移す 学籍番号 氏名 研究室ID 研究室名 関数従属 研究室ID 研究室名 分 解 学籍番号 氏名 研究室ID
118. ボイスコッド正規形 • BCNFの条件 ◦ 3NFであること ◦ （自明ではない）関数従属性がすべて取り除かれた状態 • 自明ではないの説明は省略 • つまり、3NFに残る（自明ではない）関数従属性を取り除けば良い
119. 3NFに残る関数従属性 • 今まで排除してきた（自明ではない）関数従属性 ◦ キーの一部→非キー属性 ◦ 非キー属性→非キー属性 学籍番号 氏名 部活ID 登録日 2NFで排除: キーの一部 → 非キー属性 学籍番号 氏名 研究室ID 研究室名 3NFで排除: 非キー属性 → 非キー属性
120. 3NFに残る関数従属性 • 今まで排除してきた（自明ではない）関数従属性 ◦ キーの一部→非キー属性 ◦ 非キー属性→非キー属性 • 残るは ◦ 非キー属性→キーの一部 ◦ キーの一部→キーの一部 学籍番号 氏名 部活ID 登録日 2NFで排除: キーの一部 → 非キー属性 学籍番号 氏名 研究室ID 研究室名 3NFで排除: 非キー属性 → 非キー属性
121. 3NFに残る関数従属性 • 今まで排除してきた（自明ではない）関数従属性 ◦ キーの一部→非キー属性 ◦ 非キー属性→非キー属性 • 残るは ◦ 非キー属性→キーの一部 ◦ キーの一部→キーの一部 学籍番号 氏名 部活ID 登録日 2NFで排除: キーの一部 → 非キー属性 学籍番号 氏名 研究室ID 研究室名 3NFで排除: 非キー属性 → 非キー属性
122. ボイスコッド正規形 • 正規化する方法 ◦ 非キー属性→キーの一部を排除すること ◦ この関数従属関係を別表に移すこと 学籍番号 学科 研究室名 S1111 データベース RDB S1111 言語 Go言語 S2222 データベース NoSQL BNCFで排除: 非キー属性 → キーの一部 分 解 学籍番号 学科 S1111 データベース S1111 言語 S2222 データベース 学科 研究室名 データベース RDB 言語 Go言語 データベース NoSQL 前提：学生は複数の研究室に参加できる
123. ボイスコッド正規形 学籍番号 学科 研究室名 S1111 データベース RDB S1111 言語 Go言語 S2222 データベース NoSQL BNCFで排除: 非キー属性 → キーの一部 分 解 学籍番号 学科 S1111 データベース S1111 言語 S2222 データベース 学科 研究室名 データベース RDB 言語 Go言語 データベース NoSQL • 正規化する方法 ◦ 非キー属性→キーの一部を排除すること ◦ この関数従属関係を別表に移すこと • 注意点 ◦ 3NF -> BCNFへの分割では、関数従属性が保存されな いことがある ▪ 何かしらの情報が欠損する可能性がある 前提：学生は複数の研究室に参加できる
124. ボイスコッド正規形 • 正規化する方法 ◦ 非キー属性→キーの一部を排除すること ◦ この関数従属関係を別表に移すこと • 注意点 ◦ 3NF -> BCNFへの分割では、関数従属性が保存されな いことがある ▪ 何かしらの情報が欠損する可能性がある 学籍番号 学科 研究室名 S1111 データベース RDB S1111 言語 Go言語 S2222 データベース NoSQL BNCFで排除: 非キー属性 → キーの一部 分 解 学籍番号 学科 S1111 データベース S1111 言語 S2222 データベース 学科 研究室名 データベース RDB 言語 Go言語 データベース NoSQL 元々合った情報が欠損している Q. 何が消えただろうか
125. 情報欠損と確認方法 学籍番号 学科 研究室名 S1111 データベース RDB S1111 言語 Go言語 S2222 データベース NoSQL BNCFで排除: 非キー属性 → キーの一部 分 解 学籍番号 学科 S1111 データベース S1111 言語 S2222 データベース 学科 研究室名 データベース RDB 言語 Go言語 データベース NoSQL • この例で消えた情報（関数従属性） ◦ 分解前 ▪ A. {学籍番号, 学科} -> 研究室名 ▪ 研究室名 -> 学科 ◦ 分解後 ▪ 研究室名 -> 学科 • 情報欠損の確認方法 ◦ 分解後のテーブルを結合したときに復元できない関 数従属があるかどうか 元々合った情報が欠損している Q. 何が消えただろうか
126. どうすればよいか • そもそも、3NFだけどBCNFではない状態は、複数の属性からなる キーが複数存在する場合にのみ起きる可能性がある • まずは、別のキーがないか考える 今までのキー：{学籍番号, 学科} → 研究室名 学籍番号 学科 研究室名 分 解 情報欠損 別のキー：{学籍番号, 研究室名} -> 学科 学籍番号 学科 研究室名
127. どうすればよいか • そもそも、3NFだけどBCNFではない状態は、複数の属性からなる キーが複数存在する場合にのみ起きる可能性がある • まずは、別のキーがないか考える 学籍番号 研究室名 研究室名 学科 分 解 情報欠損なし（情報無損失分解） 今までのキー：{学籍番号, 学科} → 研究室名 学籍番号 学科 研究室名 分 解 情報欠損 別のキー：{学籍番号, 研究室名} -> 学科 学籍番号 学科 研究室名 部分関数従属
128. ボイスコッド正規形の要点 • 正規化する方法 ◦ 非キー属性→キーの一部を排除すること ◦ この関数従属関係を別表に移すこと • 注意点 ◦ 3NF -> BCNFへの分割では、関数従属性が保存されないことがある ▪ 何かしらの情報が欠損する可能性がある ◦ なので、別のキーがあるか確認してみよう！
129. ボイスコッド正規形の要点 • 正規化する方法 ◦ 非キー属性→キーの一部を排除すること ◦ この関数従属関係を別表に移すこと • 注意点 ◦ 3NF -> BCNFへの分割では、関数従属性が保存されないことがある ▪ 何かしらの情報が欠損する可能性がある ◦ なので、別のキーがあるか確認してみよう！ ◦ ただし、必ずしも全ての関数従属性を保存できるような（情報無損失分 解できるような）分解方法があるとは限らない.....
130. BCNF以降 • BCNFまで到達すると ◦ 関数従属性を利用した分解はこれ以上できなくなる • 4NF以降は ◦ 結合従属性を排除する戦い ◦ BCNFを満たすと、自動的に5NFを満たす場合が多く、実践的にはBCNFまで理解していれば十分 第1正規形 (1NF) 第2正規形 (2NF) 第3正規形 (3NF) ボイスコッド 正規形 (BCNF) 第4正規形 (4NF) 第5正規形 (5NF) 非正規形 (NFNF)
131. スキーマ改善の要点 • 正規化の目的は、冗長性を排除し、更新時異常を起こりにくくすること • 様々な正規形を見たが、とりあえず、BCNFまでが大事 • とりあえず、BCNFまで分解してみよう！ ◦ ある属性値が関数従属性によって推測できるなら、それはBCNFではない • 3NFまでは必ず情報無損失な分解方法が存在するが、BNCFは違う • 正規化はヒューリスティック! 第1正規形 (1NF) 第2正規形 (2NF) 第3正規形 (3NF) ボイスコッド 正規形 (BCNF) 第4正規形 (4NF) 第5正規形 (5NF) 非正規形 (NFNF) 関数従属性の排除 繰り返しグループの排除
132. 残るは物理設計 概念設計 論理設計 物理設計 概念モデル 論理モデル 物理モデル 要求分析 要件定義 実世界
133. インデックス
134. インデックスの概要 • インデックスとは、検索を高速化するために使われるもの • インデックスの設計は物理設計の一部 ◦ 性能チューニングにおける最も主流な方法 ◦ アプリケーションの変更なしで設計可能 ◦ クエリの結果からも独立
135. データアクセス方法 • データアクセス方法 ◦ DBMSがSQLで指定されたデータにアクセスする方法 ◦ アクセス方法の選択はDBMSが自動で行う • 主に2種類 ◦ シーケンシャルスキャン（フルスキャン） ▪ 対象テーブルの全データにアクセスして、条件を満たす行を1行ずつチェック ◦ インデックススキャン ▪ インデックスを利用したアクセス方法 ▪ 本の索引のように、条件に合う行の場所を特定してから、テーブルにアクセス • 要するに、インデックスの目的はフルスキャンを避けることとも言える
136. インデックスとは • インデックスとは ◦ レコードのフィールド値とそのレコードの格納番地の対応表 ▪ i.e., （フィールド値、格納番地へのポインタ）のタプルの集合 ◦ レコードの探索機能を提供するデータ構造 ◦ 原理的には、バイナリファイル • 一般に、1テーブルに複数のインデックスが定義可能、複合インデックスも貼れる sid name major 1 佐藤 文化人類学 2 山田 計算機科学 3 金子 経済学 フィールド値 格納番地 1 2 3 インデックス データベース 1 2 3 テーブル
137. B+Tree • RDBの代表的なインデックス • 多分岐の平衡木(バランス木) • データは常にソート済み • 二段構成 ◦ データ部：データを格納したリーフノードから構成される ◦ 索引部：ノンリーフノードから構成され、データ部への経路としての役割を担う 索引部 データ部
138. B+Tree 構成 • 索引部 ◦ ルートノードと中間ノードから構成される ◦ 各ノードは（ポインタ、キー値）の配列 • データ部 ◦ リーフノードから構成される ◦ リーフノードは(データポインタ、キー値)の配列 ◦ さらに、隣同士はポインタによって結合 (ポインタ、キー値) 22 データポインタ
139. B+Tree 等価比較 SELECT … WHERE k = 54 54 Binary Search ! Binary Search ! Binary Search ! follow ptr ! follow ptr ! follow ptr !
140. B+Tree 範囲検索 SELECT … WHERE BETWEEN 50 AND 80 Binary Search ! Binary Search ! Binary Search ! follow ptr ! follow ptr !
141. B+Treeで遊んでみよう B+ Tree Visualization
142. 他にもたくさん • 代表的なインデックスの種類 ◦ ハッシュインデックス ◦ ビットマップインデックス ◦ 全文検索インデックス ◦ 空間インデックス • どのインデックスが利用できるかは製品次第
143. インデックスの定義 • 実は、標準SQLにはインデックスについての規定はない • 主キー制約やユニーク制約を定義すると、自動でインデックスが貼られる • CREATE INDEX構文で明示的にも定義もできる CREATE INDEX newIndex ON table (aaa, bbbb);
144. インデックスの設計ポイント • 必要なインデックスだけを貼る ◦ 更新性能、スペース、キャッシュ効率に関わるので • 選択率が低くなるように貼る ◦ 選択率＝検索時に母集合からどれだけ絞り込めるか • カーディナリティの高い列を選ぶ ◦ カーディナリティ＝値のばらつき具合、ユニークな値の数
145. 低いカーディナリティ • カーディナリティが低いと選択率が悪くなる ◦ トラバースのコストも馬鹿にならない • 以下の例だと、半分にも絞り込めていない WHERE k = 1 例：カーディナリティ = 3
146. 例外もある • データの分布に偏りがあれば、検索条件によって効果が大幅に変わる WHERE k = 3
147. カーディナリティ in MySQL SHOW index FROM student; MySQL Workbench
148. それでも効かない時1: 構文チェック • インデックスが使えない検索条件になっていないか確認 ◦ インデックス列を使っていない ◦ 中間一致、後方一致のLIKE述語 ◦ インデックス列で演算 ▪ NG: where age * 10 > 100 ▪ OK: where age > 100/10 ◦ インデックス列に対して関数を使用 ◦ 否定形の利用
149. • インデックスを利用したもう一つのデータアクセス方法 SELECT cid, joined_at FROM club_member 例：検索条件が存在しない (通常はフルスキャン) それでも効かない時2: インデックスオンリースキャン
150. CREATE INDEX coveringIndex ON club_member (cid, joined_at); 以下のように2列をカバーするインデックスを貼ると.. • この2列へのアクセスは、テーブルではなくインデックスをス キャンするだけで済む それでも効かない時2: インデックスオンリースキャン • インデックスを利用したもう一つのデータアクセス方法 SELECT cid, joined_at FROM club_member 例：検索条件が存在しない (通常はフルスキャン)
151. SELECT joined_at FROM club_member • 以下もインデックスオンリースキャンの対象 SELECT cid, joined_at FROM club_member WHERE joined_at >= “2021/5/1” それでも効かない時2: インデックスオンリースキャン
152. それでも効かない時2: インデックスオンリースキャン • 採用時の注意点 ◦ 列数やサイズの上限 ◦ 性能はサイズに比例 ◦ 更新オーバーヘッド ◦ SELECT句に新しい列を加えると使えない
153. • 手順 ◦ 遅いクエリを特定 ◦ 実行計画を確認 ▪ クエリ評価エンジンが作成するデータアクセスプラン ◦ チューニング ▪ クエリ、テーブル、インデックスの見直し それでも効かない時3: SQLチューニング
154. 実行計画
155. クエリ評価エンジンとは • 役割は、クエリを解釈しどのような手順でデータにアクセスす るかの計画を立て、それを実行すること • 実行計画＝データアクセスの手順 • DBMSのアーキテクチャだと上層に位置 https://mura-hiro.com/dbms-architecture/ DBMSのアーキテクチャ
156. DBMSのモジュール • クエリ評価エンジン ◦ これからもう少しみていく • アクセスメソッド ◦ データアクセスの手段(API)を提供するのが仕事 • バッファマネージャ ◦ メモリとディスクの間のデータの往来を管理 • ディスク容量マネージャ ◦ ディスクやファイルシステムを抽象化するレイヤー • 同時実行制御とリカバリマネージャ ◦ 階層を横断する機能 https://mura-hiro.com/dbms-architecture/ DBMSのアーキテクチャ
157. クエリが処理される流れ • パーサ ◦ クエリ（SELECT文）を構文解析 • オプティマイザ ◦ テーブルやインデックスの統計情報を元に複数の実行計画を作 成 ◦ 各プランのコストを算出し、最も低コストなものに絞る • プランエグゼキューター（プラン評価） ◦ 実行計画を手続き型のコードに変換して実際にデータアクセス を実行する https://gihyo.jp/dev/serial/01/db-aca demy/000401 クエリが処理される流れ
158. カタログマネージャー • 役割は、メタデータを集めたテーブル群（カタログ）を管理する こと • カタログには、例えば、データ量、インデックスの有無、データ のカーディナリティなど、様々な統計情報が格納されている https://gihyo.jp/dev/serial/01/db-aca demy/000401 クエリが処理される流れ
159. カタログ (統計情報) in MySQL SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'student'; テーブル統計 SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'student'; インデックス統計
160. 実行計画ってどんなものなのか in MySQL • MySQLでは、EXPLAINをクエリの先頭につければ実行計画が見れる！ EXPLAIN SELECT s.name, s.age, c.name FROM student s INNER JOIN club_member cm ON s.sid = cm.sid INNER JOIN club c ON cm.cid = c.cid;
161. 実行計画ってどんなものなのか in MySQL EXPLAIN SELECT s.name, s.age, c.name FROM student s INNER JOIN club_member cm ON s.sid = cm.sid INNER JOIN club c ON cm.cid = c.cid; • table: アクセス対象のテーブル • type: テーブルへのアクセス方法（インデックス利用有無、スキャン範囲などがわかる） • possible_keys: 利用可能なインデックス • key: 選択されたインデックス • key_len: 読み取ったインデックスのバイト数 • rows: スキャンする見積もり行数 • MySQLでは、EXPLAINをクエリの先頭につければ実行計画が見れる！
162. 例：カバリングインデックスが有効か確認 EXPLAIN SELECT cid, joined_at FROM club_member CREATE INDEX coveringIndex ON club_member (cid, joined_at); EXPLAIN SELECT cid, joined_at FROM club_member
163. 補足：統計情報は常に正しい？ • A. いいえ、正しくありません • 統計の自動再計算は非同期なので最新状態が反映されていないこともある ◦ インデックスが効かないもう一つの原因！ • 特に、テーブルのデータが大きく更新されたら、実質と統計との差分も大きくなる • 簡単な解決策は、明示的に統計の再計算を宣言すること ANALYZE TABLE student;
164. 実行計画を確認しよう Q1. WHERE句の条件列にインデックス Q2. ORDER BY句の条件列にインデックス Q3. GROUP BY句の条件列にインデックス Q4. 複合インデックス • 都度、前のインデックスはDROP 1. CREATE INDEX idx_major on student (major); EXPLAIN SELECT * FROM student WHERE major = "計算幾何学"; 2. CREATE INDEX idx_age on student (age); EXPLAIN SELECT * FROM student WHERE ORDER BY age; 3. CREATE INDEX idx_major on student (major); EXPLAIN SELECT major, COUNT(*) FROM student WHERE age > 20 GROUP BY major; 4. CREATE INDEX idx_major_age on student (major); EXPLAIN SELECT * FROM student WHERE major = "計算幾何学" ORDER BY age; インデックスを貼る前後でクエリの実行計画 にどんな変化があるか確認しよう！
165. 補足: 実行計画のVISUAL EXPLAIN EXPLAIN SELECT s.name, s.age, c.name FROM student s INNER JOIN club_member cm ON s.sid = cm.sid INNER JOIN club c ON cm.cid = c.cid; • MySQL Workbench の VISUAL EXPLAINを使うと直感的なフロー図がみれる
166. SQL II
167. 入れ子型質問（サブクエリ） • 入れ子型質問 (nested query) ◦ クエリを含んだクエリ ◦ 入れ子は何重にもできる ◦ サブクエリの結果もテーブル SELECT name FROM student WHERE sid IN (SELECT sid FROM club_member); サブクエリ 例：クラブに参加している学生一覧
168. 入れ子型質問（サブクエリ） SELECT s.name, s.major FROM student s INNER JOIN (SELECT major FROM student GROUP BY major HAVING COUNT(*) < 30) m ON s.major = m.major; 例：専攻人数が30人未満の分野を専攻している学生一覧 • サブクエリは、ほぼどこにでも書ける ◦ SELECT句, FROM句, WHERE句, HAVING句, ....
169. CASE式 • CASE式は条件分岐を記述するためのもの • WHEN句の評価式が評価され、THEN句の式が返される SELECT name, CASE WHEN age >= 22 THEN "22歳以上" WHEN age >= 20 THEN "20歳以上22歳未満" ELSE "20歳未満" END as age_category FROM student s; 例：学生に年齢カテゴリを付与
170. CASE式 • CASE式は条件分岐を記述するためのもの • WHEN句の評価式が評価され、THEN句の式が返される 書くときのポイント • 各分岐が返すデータ型を統一 • 短絡評価を意識 • ELSE句は必ず書く (ないと暗黙にELSE NULL) SELECT name, CASE WHEN age >= 22 THEN "22歳以上" WHEN age >= 20 THEN "20歳以上22歳未満" ELSE "20歳未満" END as age_category FROM student s; 例：学生に年齢カテゴリを付与
171. CASE式 • こういうこともできる 例：各分野における年齢カテゴリごとの学生数 専攻 22歳以上の数 20歳以上22歳 未満の数 20歳未満の数 文化人類学 2 11 8 計算機科学 11 20 10 経済学 9 12 9
172. CASE式 専攻 22歳以上の数 20歳以上22歳 未満の数 20歳未満の数 文化人類学 2 11 8 計算機科学 11 20 10 経済学 9 12 9 SELECT major, SUM(CASE WHEN age >= 22 THEN 1 ELSE 0 END) as "22歳以上の数", SUM(CASE WHEN age >= 20 AND age < 22 THEN 1 ELSE 0 END) as "20歳以上22歳未満の数", SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) as "20歳未満の数" FROM student GROUP BY major; • こういうこともできる 集約関数の中で使うと、行 を列に変換できる 例：各分野における年齢カテゴリごとの学生数
173. クエリを書いてみよう (cont.) Q1. 31アイスクリームのフレーバーの組み合わせ一覧がほしい (“ダブルサイズ”の 選択肢をください) ◦ ただし、同じフレーバーはなし ◦ 同じ組み合わせは一覧にいれないでほしい ◦ カロリーの合計もついでにほしい Q2. 上の一覧から最適な組み合わせを一つ選んでほしい ◦ 合計カロリーが350以下 ◦ どちらかのフレーバーの種類は必ずELEGANT ◦ この条件にあうもので一番カロリーが低いもの Q3. 同じ感じで、最適なトリプルの組み合わせもほしいな... Q4. 条件にあうフレーバートップ3を順番に出してほしい ◦ 各種類の中で一番カロリーが低いものが対象 ( ▪ 最小のものは1つに決まる前提 ◦ 好きな種類の順番は ELEGANT -> THE 31 -> それ以外 Q5. 大豆を含まないフレーバーの一覧が欲しい ice_cream_flavor(name CHAR, kind CHAR, calorie INTEGER) ice_cream_flavor_allergy(ice_name CHAR, allergy_name CHAR)
174. 最後 • 大事なところ ◦ データ独立性 ◦ データモデル (関係モデル) ◦ 宣言型プログラミング (SQL) ◦ トランザクション (ACID) ◦ データベース設計 (ER, 正規化) ◦ インデックス (B+tree, カーディナリティ) ◦ 実行計画
175. 主な出典 • 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ • 理論から学ぶデータベース実践入門 ―― リレーショナルモデルによる効率的なSQL • SQL実践入門 ──高速でわかりやすいクエリの書き方 • Webエンジニアのための データベース技術[実践]入門 • リレーショナルデータベース入門―データモデル・SQL・管理システム・NoSQL 第3版 • 失敗から学ぶRDBの正しい歩き方 • データ指向アプリケーションデザイン――信頼性、拡張性、保守性の高い分散システム設計の原理 • CS 15-445/645 Database Systems at Carnegie Mellon University • CS186 Introduction to Database Systems at University of California, Berkeley