はてなリモートインターンシップ2023 RDBMS ブートキャンプ講義資料

by Hatena

Slide 1

Slide 1 text

RDBMS ブートキャンプ #hatenaintern)*)+

Slide 2

Slide 2 text

はじめにブートキャンプとは • 後半パートで困らないことを⽬的に • 概念からではなく、具体例やテクニック中⼼に • 結構駆け⾜気味なので、後で調べるとっかかりの⼀つにしてもらえると幸いです #hatenaintern)*)+

Slide 3

Slide 3 text

はじめに • はてなでは主に MySQL と PostgreSQL という DBMS をよく使っています • 後半パートの機能開発でこれらを使う際に、0 から調べることがないように基礎的な部分を説明します #hatenaintern)*)+

Slide 4

Slide 4 text

このブートキャンプのゴール MySQL や PostgreSQL をなんとなく扱えるようになる • データを格納出来るようになる • データを保存‧参照‧更新‧削除が出来る • テーブル設計の考え⽅とパフォーマンスがなんとなくわかる #hatenaintern)*)+

Slide 5

Slide 5 text

この講義で扱えないこと • データベースの内部の仕組み • 詳しいテーブル設計や SQL のテクニック • パフォーマンスの詳しい話 • MySQL と PostgreSQL 以外のこと #hatenaintern)*)+

Slide 6

Slide 6 text

⽬次 • RDBMS について • 基本編 • 応⽤編 • 付録 #hatenaintern)*)+

Slide 7

Slide 7 text

RDBMS について #hatenaintern)*)+

Slide 8

Slide 8 text

RDBMS とは RDBMS = Relational Database Management System • Relational Database • 関係データベース • Database Management System • データベース管理システム関係データベースのデータベース管理システム #hatenaintern)*)+

Slide 9

Slide 9 text

関係データベース関係モデルに基づいたデータベース関係モデルとは？ → データを関係として表現し取り扱うデータの表現⽅法関係データベースでは、⾒た⽬としては表（テーブル）の形式でデータを表現する #hatenaintern)*)+

Slide 10

Slide 10 text

関係データベース #hatenaintern)*)+

Slide 11

Slide 11 text

関係データベース関係データベース以外のデータベースシステムもあるが、この講義では扱わない例えばこういうものがあります: #hatenaintern)*)+

Slide 12

Slide 12 text

データベース管理システムデータベース管理システム（DBMS）の役割 • データを抽象化し、効率よく保存 • ⽤途にあわせて最適な構造でデータを保存する • 外部からの指⽰を受け付けてデータを操作する、インターフェースの役割 • データを使う⼈はデータがどのように格納されているか意識しなくてもいい • データを堅牢に扱う仕組みを提供する • 整合性を保つ • 並列アクセスされても齟齬が起きないようにする • データの損失を防ぐ #hatenaintern)*)+

Slide 13

Slide 13 text

基本編 #hatenaintern)*)+

Slide 14

Slide 14 text

⽤語⽤語意味テーブルデータの集合レコードテーブルのデータの組 1 ⾏ 1 ⾏カラムテーブルのデータの属性スキーマテーブルの構造（どんなカラムがあるか、カラムにはどんな値が⼊るか、など） #hatenaintern)*)+

Slide 15

Slide 15 text

⽤語 #hatenaintern)*)+

Slide 16

Slide 16 text

SQL RDBMS に問い合わせ（操作）を⾏うための⾔語 • DDL (Data Deﬁnition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など • DCL (Data Control Language) • データへのアクセス制御に関する SQL • この講義では紹介しない SQL で書かれた、データベースへの問い合わせ⽂を、「クエリ」と呼ぶ #hatenaintern)*)+

Slide 17

Slide 17 text

SQL SQL には規格があるが、それにどこまで準拠しているかは DBMS によってまちまち DBMS はそれぞれ独⾃拡張（⽅⾔）を持っていて、互換性がないこともあるこのブートキャンプでも、「MySQL ではこの書き⽅ができます」のような話が出てきたら⽅⾔のことだと思ってください #hatenaintern)*)+

Slide 18

Slide 18 text

データ定義 • DDL (Data Deﬁnition Language) ← これ • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など #hatenaintern)*)+

Slide 19

Slide 19 text

CREATE TABLE ⽂* • テーブルを定義する構⽂ • CREATE TABLE ςʔϒϧ ໊ (ςʔϒϧఆٛ); !" `!"` ͸ίϝϯτ !" id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ !" users ͱ͍͏໊લͰఆٛ CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント PostgreSQL: Documentation: !S: CREATE TABLE #hatenaintern)*)+

Slide 20

Slide 20 text

CREATE TABLE ⽂ id BIGINT NOT NULL, • BIGINT の部分: カラムのデータ型 • NOT NULL の部分: カラムの制約 #hatenaintern)*)+

Slide 21

Slide 21 text

データ型 #hatenaintern)*)+

Slide 22

Slide 22 text

データ型 • 型によって保存に使われる容量が異なるので⼤事 • 後から変えるのも⼤変なことが多い • よく使うものについて紹介 • 数値型 • ⽂字列型 • ⽇付と時間型 #hatenaintern)*)+

Slide 23

Slide 23 text

数値型 • 整数 • TINYINT, INTEGER, BIGINT など • 右に⾏くほど多くの桁を保存できる • MySQL では UNSIGNED 属性を付与すると符号無しで保存できるようになる（BIGINT UNSIGNED） • 属性: カラムの特性。つけられる属性は、データ型によって異なる • 浮動⼩数点 • FLOAT, DOUBLE • 固定⼩数点 • DECIMAL, NUMERIC #hatenaintern)*)+

Slide 24

Slide 24 text

数値型 • 桁あふれに気をつける必要がある • レコードごとにユニークにつける id などは BIGINT UNSIGNED にしておくと安⼼ • 18446744073709551615（1844京）まで保存できる #hatenaintern)*)+

Slide 25

Slide 25 text

⽂字列型 • 多くの場合で VARCHAR(n) を使う • 可変⻑⽂字列で 0 〜 65,535 バイト保存可能 • VARCHAR(32) のように、保存する最⼤⽂字数を指定する • ある程度⻑い⽂字列を保存するときにはテキスト型を利⽤ • MySQL では TEXT, MEDIUMTEXT, LONGTEXT など • PostgreSQL には TEXT だけ⽤意されている #hatenaintern)*)+

Slide 26

Slide 26 text

⽇付と時間型 MySQL の場合 • DATETIME • ⽇付と時間の両⽅を含む • サポートする範囲は 1000-01-01 00:00:00 〜 9999-12-31 23:59:59 • タイムゾーンの概念がない • TIMESTAMP • ⽇付と時間の両⽅を含む • サポートする範囲は 1970-01-01 00:00:01 UTC 〜 2038-01-19 03:14:07 UTC • タイムゾーンの概念がある #hatenaintern)*)+

Slide 27

Slide 27 text

⽇付と時間型 PostgreSQL では、タイムゾーンの有無は ↓ のように区別する • TIMESTAMP WITHOUT TIME ZONE • TIMESTAMP WITH TIME ZONE #hatenaintern)*)+

Slide 28

Slide 28 text

⽇付と時間型⽇付と時間の両⽅を含んではいない型もある（登場頻度は少ない） • DATE: ⽇付のみ • TIME: 時間のみ #hatenaintern)*)+

Slide 29

Slide 29 text

制約 #hatenaintern)*)+

Slide 30

Slide 30 text

制約 • カラムに⼊るデータに対する制限 • 意図しないデータが⼊り込まないように • 制約に反するとエラーになる #hatenaintern)*)+

Slide 31

Slide 31 text

制約例: 「users テーブルの id が⼀意になる」という制約をつけた状態だと...> !" id = 1 ͷ user Λ௥Ճͯ͠ɺ੒ޭ͢Δ INSERT INTO users (id, name, created_at) VALUES (1, 'name1', NOW()); Query OK, 1 row affected (0.01 sec) !" ಉ͡ id ͷ஋ΛೖΕΑ͏ͱ͢ΔͱɺΤϥʔʹͳΔ INSERT INTO users (id, name, created_at) VALUES (1, 'name2', NOW()); ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' ! クエリの下に書いてあるのは RDBMS から返されてきたクエリの結果。ここでは MySQL で実⾏した結果を貼ってある。PostgreSQL でもメッセージは異なるが同じエラーが返ってくる。 #hatenaintern)*)+

Slide 32

Slide 32 text

制約の例 • NOT NULL制約: カラムは NULL であってはいけない • NULL は、値が⽋損していることを表す • UNIQUE 制約: カラムの値がテーブル内で⼀意でなければならない • ただし NULL はいくつあっても許されるので注意 • PRIMARY KEY 制約: カラムの値がテーブル内でレコードを⼀意に識別できなければいけない • FOREIGN KEY 制約: 紐づいているテーブル間で整合性が取れていなければいけない #hatenaintern)*)+

Slide 33

Slide 33 text

PRIMARY KEY (主キー) • テーブル内でレコードを⼀意に識別できるカラム • 1つのテーブルで1つ指定できる • PRIMARY KEY 制約は、UNIQUE 制約と NOT NULL 制約を併せ持っているといえる • PRIMARY KEYはインデックス（後述）としても使える #hatenaintern)*)+

Slide 34

Slide 34 text

改めて users テーブルの定義を⾒る CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); users という名前のテーブルを定義し、以下のカラムを持たせる • id • 整数の値が⼊り、この値によってテーブル内のレコードを⼀意に識別できる • name • 最⼤32⽂字の⽂字列の値が⼊り、NULL でないかつデータが重複することがない • created_at • 時刻の値が⼊り、NULL ではない #hatenaintern)*)+

Slide 35

Slide 35 text

データ操作 • DDL (Data Deﬁnition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) ← これ • データを操作する SQL • 検索、更新など #hatenaintern)*)+

Slide 36

Slide 36 text

CRUD • データ操作する際の基本機能 • それぞれの操作の頭⽂字から • Create (追加) • Read (参照) • Update (更新) • Delete (削除) #hatenaintern)*)+

Slide 37

Slide 37 text

以降は以下のテーブルがある前提でユーザーと、ユーザーが所有するブログを管理している状態 CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL, name VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, !" ϒϩάΛ͍࣋ͬͯΔϢʔβʔͷ id user_id BIGINT NOT NULL, title VARCHAR(50) NOT NULL, description VARCHAR(512), PRIMARY KEY (id) ); #hatenaintern)*)+

Slide 38

Slide 38 text

INSERT ⽂ (Create)0 テーブルにレコードを追加する INSERT ⽂ INSERT INTO users (id, name, created_at) VALUES (1, '͸ͯͳଠ࿠', '2023-08-17 12:00:00'); Query OK, 1 row affected (0.01 sec) MySQL、PostgreSQL でそれぞれ別の書き⽅もある ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.6 INSERT ステートメント PostgreSQL: Documentation: RS: INSERT #hatenaintern)*)+

Slide 39

Slide 39 text

SELECT ⽂ (Read). テーブルからレコードを検索する SELECT ⽂ !" users ςʔϒϧͷϨίʔυΛશ݅ݕࡧʢऔಘʣ SELECT * FROM users; * は「全てのカラム」という意味。* の代わりに個別のカラム名を指定することもできる +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2023-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.10 SELECT ステートメント PostgreSQL: Documentation: P!: SELECT #hatenaintern)*)+

Slide 40

Slide 40 text

SELECT ⽂ (Read) 絞り込みには WHERE 句を付ける（後述） !" id ͕ 1 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2023-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) #hatenaintern)*)+

Slide 41

Slide 41 text

UPDATE ⽂ (Update)0 既に存在するレコードを更新する UPDATE ⽂ !" ߋ৽͍ͨ͠ϨίʔυΛ WHERE ۟Ͱࢦఆ͢Δ UPDATE users SET name = '͸ͯͳೋ࿠' WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 !" ஫ҙ! WHERE ۟Ͱߋ৽ର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅ߋ৽͞ΕΔ UPDATE users SET name = '͸ͯͳೋ࿠'; ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.13 UPDATE ステートメント PostgreSQL: Documentation: PQ: UPDATE #hatenaintern)*)+

Slide 42

Slide 42 text

DELETE ⽂ (Delete), レコードを削除する DELETE FROM users WHERE id = 1; Query OK, 1 row affected (0.01 sec) !" ஫ҙ! WHERE ۟Ͱ࡟আର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅࡟আ͞ΕΔ DELETE FROM users; ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.2 DELETE ステートメント PostgreSQL: Documentation: NO: DELETE #hatenaintern)*)+

Slide 43

Slide 43 text

データの取得⽅法⽬的⼿段条件をつけて絞り込み WHERE 句ソート ORDER BY 句取得件数の制限 LIMIT 句ページネーション LIMIT 句と OFFSET 句を組み合わせるグルーピング GROUP BY 句テーブル同⼠の結合 JOIN 句 #hatenaintern)*)+

Slide 44

Slide 44 text

WHERE 句条件をつけて絞り込む SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; #hatenaintern)*)+

Slide 45

Slide 45 text

⽐較「等しい」は =、「等しくない」は !"（!# とも書かれる）プログラミング⾔語の数値や⽂字列のように >, <, !$, !% で⽐較できる !" id ͕ 1 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; !" id ͕ 1 Ͱ͸ͳ͍ϨίʔυΛݕࡧ SELECT * FROM users WHERE id !# 1; !" created_at ͕ 2023-08-17 12:00:00 ΑΓେ͖͍ʢΑΓޙͷʣϨίʔυΛݕࡧ SELECT * FROM users WHERE created_at > '2023-08-17 12:00:00'; ただし、NULL は値ではないので column_name = NULL と書いても NULL のレコードは得られない。column_name IS NULL と書くこと。 #hatenaintern)*)+

Slide 46

Slide 46 text

⽐較 • AND や OR で複数の条件を組み合わせることができる • 同じカラムに対する OR は、IN でまとめて表現することもできる !" id͕ 1 ͔ 2 ͔ 3 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3; !" ্ͷ SQL Λ IN Λ࢖ͬͯॻ͘ SELECT * FROM users WHERE id IN (1, 2, 3); • LIKE: ⽂字列のパターンマッチング !" name ͕ "͸ͯͳ" ͔Β࢝·ΔϨίʔυΛݕࡧ !" `%` ͸ϫΠϧυΧʔυจࣈͰɺ೚ҙͷ0จࣈҎ্ͷจࣈྻΛද͢ SELECT * FROM users WHERE name LIKE '͸ͯͳ%'; #hatenaintern)*)+

Slide 47

Slide 47 text

ORDER BY 句 • ORDER BY ΧϥϜ໊ [ASC/DESC]: 検索結果のソートが出来る • ASC は昇順、DESC は降順 • WHERE 句と組み合わせるときは、WHERE 句を先に書かないと動かない !" user ςʔϒϧͷϨίʔυΛ created_at ͷ߱ॱͰݕࡧ SELECT * FROM users ORDER BY created_at DESC; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 2 | ͸ͯͳೋ࿠ | 2023-08-17 13:00:00 | | 1 | ͸ͯͳଠ࿠ | 2023-08-17 12:00:00 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) #hatenaintern)*)+

Slide 48

Slide 48 text

ページネーション (LIMIT 句 / OFFSET 句) • LIMIT 句: 検索結果の取得件数を指定できる • OFFSET 句: 指定した値だけ検索結果を読み⾶ばす • この2つを組み合わせてページネーション機能を実現できる !" users ςʔϒϧͷશϨίʔυ͔Β created_at ͷ߱ॱͰݕࡧ͠ɺ3ͷഒ਺෼ͷϨίʔυΛಡΈඈ͹্ͨ͠Ͱ3݅ͷݕࡧ݁ՌΛಘΔ !" 1ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 0; !" 2ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 3; !" 3ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 6; #hatenaintern)*)+

Slide 49

Slide 49 text

ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+

Slide 50

Slide 50 text

ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+

Slide 51

Slide 51 text

ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+

Slide 52

Slide 52 text

ページネーションただし、LIMIT/OFFSET を使ったページネーションには以下のようなデメリットがある • パフォーマンス • OFFSET で指定された⾏数まで辿り着くまでに全ての⾏数を数えなければいけない • データがずれる • ページネーションをしている途中に⾏が差し込まれたり削除されたりしたら、ずれてしまう • 例: 1ページ⽬(1〜10件⽬)をクエリ → 1件⽬のデータが削除される → 2ページ⽬(11〜20件⽬)をクエリ → もともとの11件⽬がどちらのページにも現れないこれらのデメリットを避けるために、次の⽅法がよく使われる #hatenaintern)*)+

Slide 53

Slide 53 text

ページネーション前ページの最終⾏の値を、ページの区切りのキーとして使う Keyset Pagination と呼ばれている例: 1ページ⽬は id = - のレコードが最後だったので、2ページ⽬を取得するときには id > - のものを取得する SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ メリット • パフォーマンス • インデックス（後述）があれば、現在のページの⾏まで素早く⾶べる • データのずれ • 起こらないデメリット • 任意のページまで⾶べない • 1ページ⽬から10ページ⽬に⾏く、というのは不可能 • 前後ページへのナビゲーションを出そうとすると⼯夫がいる • ページの前後にデータが存在するか確認するクエリも必要になる • 単調増加するカラムが必要になる • 単調増加するカラムを指定するか AND で組み合わせる必要がある #hatenaintern)*)+

Slide 54

Slide 54 text

グルーピング (GROUP BY 句) • GROUP BY 句: データをグルーピングする例: ユーザーがブログをいくつ持っているのかを集計する blogs テーブルのレコードを user_id ごとにグルーピングし、それぞれのグループ内の合計数を出す • COUNT(*): 条件に合致するレコードの数を表⽰する • AS: カラムに別名（エイリアス）をつける SELECT user_id, COUNT(*) AS blog_count FROM blogs GROUP BY user_id; +---------+------------+ | user_id | blog_count | +---------+------------+ | 1 | 1 | | 2 | 2 | +---------+------------+ 2 rows in set (0.00 sec) #hatenaintern)*)+

Slide 55

Slide 55 text

グルーピング (GROUP BY 句) #hatenaintern)*)+

Slide 56

Slide 56 text

テーブル同⼠の結合 (JOIN 句) • JOIN 句: 異なるテーブルに保存されたデータを結合できる !" users ςʔϒϧͱ blogs ςʔϒϧΛ݁߹ͯ͠ɺϒϩάͷ࡞ऀͱλΠτϧͷҰཡΛग़͢ SELECT users.name AS user_name, blogs.title AS blog_title FROM blogs JOIN users ON blogs.user_id = users.id; +-----------------+--------------------------+ | user_name | blog_title | +-----------------+--------------------------+ | ͸ͯͳଠ࿠ | ଠ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | +-----------------+--------------------------+ 3 rows in set (0.00 sec) クエリにテーブルが複数登場する場合、ςʔϒϧ໊.ΧϥϜ໊ でカラムを表す #hatenaintern)*)+

Slide 57

Slide 57 text

テーブル同⼠の結合 (JOIN 句) #hatenaintern)*)+

Slide 58

Slide 58 text

テーブル同⼠の結合 (JOIN 句) • blogs JOIN users : blog テーブルに user テーブルを結合させる • ON blogs.user_id = users.id : blogs.user_id と⼀致する値の users.id を持つレコードを結合させる • JOIN には RIGHT JOIN, INNER JOIN, OUTER JOIN などの種類があるが、ここでは触れない #hatenaintern)*)+

Slide 59

Slide 59 text

応⽤編 #hatenaintern)*)+

Slide 60

Slide 60 text

応⽤編これらの話題を少しずつだけ紹介 • テーブル設計 • パフォーマンスの話 #hatenaintern)*)+

Slide 61

Slide 61 text

テーブル設計 #hatenaintern)*)+

Slide 62

Slide 62 text

テーブル設計ブログサービスを例に考えてみる • ユーザーがいる • ユーザーは名前を持つ • ユーザーはブログを持てる • ブログにはタイトルが付く • ブログには説明⽂が書ける • ユーザーはブログを複数持てるどんなテーブル(表)で表せるか？ #hatenaintern)*)+

Slide 63

Slide 63 text

テーブル設計 - ユーザーとブログ全部⼀つのテーブル(表)で書いてみる #hatenaintern)*)+

Slide 64

Slide 64 text

テーブル設計 - ユーザーとブログこれでも可能ではあるけど... • ユーザー名の情報が重複している • 間違えて⼀部だけ更新してしまったら整合性がなくなってしまう • シンプルなことをしたいだけなのに複雑なクエリを書く場所ができてしまう • 例: ユーザー名⼀覧を取得するためには SELECT DISTINCT user_name FROM blogs;というクエリになる • 扱う情報が増えたらどうする？ • 例: ブログに記事を複数持たせたい • 記事カラムを追加したらこのテーブルでも実現できるが、記事の数だけレコードが作られるので、その分ユーザー‧ブログのデータの重複が増える #hatenaintern)*)+

Slide 65

Slide 65 text

テーブル設計 - ユーザーとブログ扱う情報に着⽬してテーブルを分割 • 「ユーザー」と「ブログ」に着⽬して、それぞれをテーブルで表現する • users テーブルと blogs テーブルに分割する • それぞれのテーブルに、⾏を⼀意に特定できるキーを追加する • そのキーを使って、テーブル同⼠が関連を持てるようにする #hatenaintern)*)+

Slide 66

Slide 66 text

テーブル設計 - ユーザーとブログテーブルを分割する #hatenaintern)*)+

Slide 67

Slide 67 text

テーブル設計 - ユーザーとブログテーブルに⾏を特定できるキーを追加する #hatenaintern)*)+

Slide 68

Slide 68 text

テーブル設計 - ユーザーとブログそのキーを使ってテーブル同⼠に関連を持たせる #hatenaintern)*)+

Slide 69

Slide 69 text

テーブル設計 - ユーザーとブログ users と blogs は⼀対多の関係 #hatenaintern)*)+

Slide 70

Slide 70 text

テーブル設計 - ユーザーとブログ • 多のテーブルに、⼀のテーブルのレコードを⼀意に特定するカラムの値を持たせれば、⼀対多の関係を表せる • 着⽬する情報の関係を図にしてみると捉えやすい • ER図 - Google 検索 #hatenaintern)*)+

Slide 71

Slide 71 text

テーブル設計 - ユーザーとブログ完成形 CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, title VARCHAR(254) NOT NULL, description VARCHAR(512) NOT NULL, PRIMARY KEY (id) ); #hatenaintern)*)+

Slide 72

Slide 72 text

テーブル設計 - ユーザーとブログユーザー名とブログ名は別のテーブルに格納されるようになったので、それらのデータを⼀気に取得するには JOIN が必要になる SELECT users.name AS user_name, blogs.title AS blog_title, blogs.description AS blog_description FROM blogs JOIN users ON blogs.user_id = users.id WHERE users.id = 2; +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ | user_name | blog_title | blog_description | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | ٕज़తͳ࿩୊Λॻ͖·͢ | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) #hatenaintern)*)+

Slide 73

Slide 73 text

テーブル設計 - 購読機能ここで購読機能を追加することを考えてみる • ユーザーはブログを購読できる • 例: ユーザー「はてな太郎」は、「⼆郎の⽇記」「⼆郎の技術ブログ」のブログを購読できる • SNS でいう「フォロー」機能 • これをテーブル構造で表すには？ #hatenaintern)*)+

Slide 74

Slide 74 text

テーブル設計 - 購読機能 users と購読対象の blogs は多対多の関係 #hatenaintern)*)+

Slide 75

Slide 75 text

テーブル設計 - 購読機能 • blogs に subscribe_user_id（このブログを購読しているユーザー ID）のようなカラムを⾜すと？ • blogs.subscribe_user_id は1つしか値を格納できない • → 複数⼈が同じブログを購読している状態を表現できない • 多対多の関係は、どちらかのテーブルにカラムを追加するだけでは表現できない #hatenaintern)*)+

Slide 76

Slide 76 text

テーブル設計 - 購読機能 • users と blogs の購読の関係を表すテーブルを作る • subscriptions テーブル • subscriptions から users と blogs のレコードを特定できるように、user_id と blog_id カラムを持つ • この例に限らず、多対多の関係を表すには、関係を表すテーブルを作ってみるといい #hatenaintern)*)+

Slide 77

Slide 77 text

テーブル設計 - 購読機能完成形 CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); #hatenaintern)*)+

Slide 78

Slide 78 text

テーブル設計 - 購読機能「はてな太郎」という名前のユーザーが購読しているブログ⼀覧を取得するには、3つのテーブルの結合が必要になる SELECT blogs.* FROM subscriptions JOIN users ON subscriptions.user_id = users.id JOIN blogs ON subscriptions.blog_id = blogs.id WHERE users.name = '͸ͯͳଠ࿠'; +-----+---------+-----------------+--------------------------------+ | id | user_id | title | description | +-----+---------+-----------------+--------------------------------+ | 102 | 2 | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | +-----+---------+-----------------+--------------------------------+ 1 row in set (0.00 sec) #hatenaintern)*)+

Slide 79

Slide 79 text

テーブル設計まとめ • 扱う情報に着⽬してテーブルを考えてみる • テーブル同⼠の関係は図にしてみるとわかりやすい • 関係⾃体をテーブルで表すとすっきりすることもある #hatenaintern)*)+

Slide 80

Slide 80 text

パフォーマンス #hatenaintern)*)+

Slide 81

Slide 81 text

パフォーマンス DB は Web サービスを開発‧運営する上でパフォーマンスの問題に繋がりやすい • サービスが続く限りデータは増加し続ける • 最初は問題なかったクエリが、データ量が増えたことで実⾏に時間がかかるようになってしまったり • DB のパフォーマンスの問題はサービスの品質低下に繋がる • クエリの実⾏に時間がかかる • → ユーザに返すレスポンスが遅くなる • → ユーザが離れてしまう #hatenaintern)*)+

Slide 82

Slide 82 text

パフォーマンスパフォーマンスに関して、以下の2つのものを紹介します • インデックス • EXPLAIN #hatenaintern)*)+

Slide 83

Slide 83 text

インデックステーブルのレコードを⾼速に検索するための仕組み • レコードを検索する際、インデックスがなければ、先頭⾏からテーブル全体を読み取り対象のレコードを⾒付けだす • 例: SELECT * FROM users WHERE id = 100000; なら、テーブルの先頭から1件ずつ⾒ていって id が 100000 に⼀致するものを探す • インデックスはテーブルを検索するために最適なデータ構造で保存される • よく使われるのは、B-Tree というデータ構造 • 検索対象のカラムがインデックスを持つカラムだと、全てのレコードを調べずに検索ができる • インデックスが定義されていれば、テーブルにデータを挿⼊した際に、⾃動でインデックスにもデータが追加される #hatenaintern)*)+

Slide 84

Slide 84 text

インデックス • 明⽰的に作る⽅法 CREATE INDEX index_name ON table_name (column_name); • 制約により⾃動で作られる • PRIMARY KEY • UNIQUE MySQL では、CREATE TABLE ⽂の中でインデックスを定義することもできる CREATE TABLE table_name ( -- (ུ) INDEX index_name (column_name) ); #hatenaintern)*)+

Slide 85

Slide 85 text

インデックスどういうときにインデックスを作るか？ • インデックス作成にはコストがかかる（後述）ため、無闇に作ればいいわけではない • アプリケーションで実⾏するクエリをイメージして、有効に使われそうなインデックスを考える • 例: 「あるユーザーのブログ⼀覧を取得する」 • クエリ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id を元に検索するので user_id にインデックスがあると⾼速に検索できそう #hatenaintern)*)+

Slide 86

Slide 86 text

インデックスインデックスの注意点 • インデックス作成にはコストがかかる • レコードの作成‧更新‧削除時にインデックスも更新するので、オーバーヘッドがある • 多くのアプリケーションでは ࢀরॲཧ > ߋ৽ॲཧ になるのであまり問題ない • レコードとは別にインデックスを格納するためのディスク領域も別途必要になる • インデックスが使われないケースもある • 例: 全件探索の⽅が早い • 都道府県を表すテーブルだと、47レコードしかないので全件探索した⽅が早い #hatenaintern)*)+

Slide 87

Slide 87 text

EXPLAIN( • クエリの実⾏計画を出⼒する⽅法 • 実⾏計画: クエリにどのようなインデックスを使⽤し、どんな順番でテーブルを結合するのか、など • 実⾏計画がわかれば、どの部分がパフォーマンスを悪くしているかを知ることができる • クエリの先頭に EXPLAIN を付けることで、そのクエリの実⾏計画を得ることができる ! MySQL :: MySQL ).+ リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化 PostgreSQL: Documentation: VW: EXPLAIN #hatenaintern)*)+

Slide 88

Slide 88 text

Slide 89

Slide 89 text

Slide 90

Slide 90 text

EXPLAIN ANALYZE • 実際にクエリを実⾏し、各ステップの実⾏時間を出してくれる • PostgreSQL では古くからサポートされており、MySQL でも 8.0.18 から導⼊され始めている EXPLAIN ANALYZE SELECT COUNT(*) FROM users WHERE id > 2000; +----------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------+ | !" Aggregate: count(0) (cost=0.45 rows=1) (actual time=0.0099!#0.01 rows=1 loops=1) | | !" Filter: (users.id > 2000) (cost=0.35 rows=1) (actual time=0.00858!#0.00858 rows=0 loops=1) | | !" Covering index scan on users using PRIMARY (cost=0.35 rows=1) (actual time=0.00798!#0.00798 rows=0 loops=1) | | | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #hatenaintern)*)+

Slide 91

Slide 91 text

パフォーマンス「推測するな、計測せよ」という格⾔があります • パフォーマンス対策をするときは勘で対処してはいけない • きちんと計測し、ボトルネックを把握して対処していく必要がある #hatenaintern)*)+

Slide 92

Slide 92 text

終わりに #hatenaintern)*)+

Slide 93

Slide 93 text

終わりに • 1時間お疲れ様でした • 概念‧SQL‧テーブル設計‧パフォーマンスに少しずつ触れましたが、基礎的なトピックを全て扱えているわけではないです • 知識や理論については、空いた時間やインターンが終わった後に⾒てみてください • 実サービスのデータ量を扱う経験は、後半パートで体験してもらえると思います #hatenaintern)*)+

Slide 94

Slide 94 text

終わりに扱えなかったトピックやキーワードについて列挙しておくので、興味があれば調べてみてください • SQL について • 複雑なクエリや集計 (サブクエリ) • 組み込み関数 • JOIN の種類とアルゴリズム • テーブル設計 • 正規化 • トランザクション • ACID 特性 • ロック • MVCC • プログラミング⾔語から SQL を扱う (付録に参考リンク記載) • N+@ 問題 • セキュリティ • SQL インジェクション #hatenaintern)*)+

Slide 95

Slide 95 text

付録お⼿元で⼿軽に SQL のクエリを試してもらえるよう、Docker でデータベースを起動してその中に⼊る⽅法を書きました #hatenaintern)*)+

Slide 96

Slide 96 text

Docker を使って MySQL を操作する (1/3) MySQL のコンテナを起動 $ docker pull mysql:8.0 $ docker run !"name rdbms-bootcamp -e MYSQL_ROOT_PASSWORD="root_passwd" \ -e MYSQL_USER='user' \ -e MYSQL_PASSWORD='passwd' \ -e MYSQL_DATABASE="bootcamp" \ -p 13306:3306 mysql:8.0 上記のコンテナの MySQL に⼊る $ docker container exec -it rdbms-bootcamp mysql -uuser -ppasswd bootcamp #hatenaintern)*)+

Slide 97

Slide 97 text

Docker を使って MySQL を操作する (2/3) 注意点 • root ユーザーでは Docker コンテナ内からでないと⼊れません • → 権限管理の関係 • 公式のイメージだと mysql client から⼊ると⽇本語が⼊⼒できない‧出⼒されない • OS に⽇本語設定が⼊っていないため • 以下の⽅法で⽇本語⼊⼒できるようになる #hatenaintern)*)+

Slide 98

Slide 98 text

Docker を使って MySQL を操作する (3/3) ⽇本語の設定⽅法 # ίϯςφ಺ʹೖΔ $ docker container exec -it rdbms-bootcamp /bin/bash # locale ઃఆΛΠϯετʔϧ͠ɺ೔ຊޠ͕࢖͑ΔΑ͏ʹ͢Δ root@xxx:/$ microdnf install glibc-langpack-ja root@xxx:/$ exit # LANG ؀ڥม਺Λ೔ຊޠʹͯ͠ɺmysql ʹϩάΠϯ $ docker container exec -it rdbms-bootcamp env LANG="ja_JP.utf8" mysql -uuser -ppasswd bootcamp #hatenaintern)*)+

Slide 99

Slide 99 text

MySQL でよく使うコマンド !" ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; !" ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; !" MySQL ͔Βग़Δ exit #hatenaintern)*)+

Slide 100

Slide 100 text

Docker を使って PostgreSQL を操作する PostgreSQL のコンテナを起動 $ docker pull postgres:14.5 $ docker run !"name rdbms-bootcamp -e POSTGRES_PASSWORD=passwd postgres:14.5 上記のコンテナの PostgreSQL に⼊る $ docker container exec -it rdbms-bootcamp psql -h localhost -U postgres -d postgres #hatenaintern)*)+

Slide 101

Slide 101 text

PostgreSQL でよく使うコマンド !" ςʔϒϧҰཡΛදࣔ͢Δ \dt !" ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ \d table_name !" PostgreSQL ͔Βग़Δ exit #hatenaintern)*)+

Slide 102

Slide 102 text

参考リンク (1/2) RDBMS 全般 • データベース概論Ⅰ | 筑波⼤学オープンコースウェア｜TSUKUBA OCW • ある程度 DB を扱うことに慣れてきてから⾒てみると、理論と実践が合わさってよいと思うリファレンス • MySQL (.* リファレンスマニュアル • PostgreSQL: Documentation: EF: PostgreSQL EF.G Documentation • リファレンスなので、困ったときに辞書的に使うと良いです • 実際に使うバージョンに合ったマニュアルを参照した⽅が良いです #hatenaintern)*)+

Slide 103

Slide 103 text

参考リンク (2/2) Go ⾔語からデータベースを扱う⽅法 • Accessing databases - The Go Programming Language • プログラミング⾔語から扱う⼀例として Go のドキュメントを紹介します • このドキュメント⾃体は Go のドキュメントですが、プログラムから SQL を扱うことについてのチュートリアルから⼀般的な話題、注意点などに触れられます #hatenaintern)*)+