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

by Hatena

Slide 1

Slide 1 text

RDBMS ϒʔτΩϟϯϓ IBUFOBJOUFSO !

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

͸͡Ίʹ • はてなでは主に MySQL と PostgreSQL という DBMS をよく使っています • 配属チームは MySQL を使っているので、このブートキャンプでは MySQL のみを扱います • 後半パートの機能開発でこれらを使う際に、0 から調べることがないように基礎的な部分を説明します IBUFOBJOUFSO !

Slide 4

Slide 4 text

͜ͷϒʔτΩϟϯϓͷΰʔϧ MySQL をなんとなく扱えるようになる • データの保存‧参照‧更新‧削除ができる • テーブル設計時に気にすることが分かる • インデックスがなんとなく分かる IBUFOBJOUFSO !

Slide 5

Slide 5 text

͜ͷߨٛͰѻΘͳ͍͜ͱ • データベースの内部の仕組み • 詳しいテーブル設計や SQL のテクニック • パフォーマンスの詳しい話 • MySQL 以外のこと IBUFOBJOUFSO !

Slide 6

Slide 6 text

࣭໰ • SQL 知っている⼈ﾉｼ IBUFOBJOUFSO !

Slide 7

Slide 7 text

࣭໰ • 触り慣れてる⼈ﾉｼ IBUFOBJOUFSO !

Slide 8

Slide 8 text

໨࣍ • RDBMS について • SQL のおさらい • テーブル設計 • インデックス • 付録 IBUFOBJOUFSO !

Slide 9

Slide 9 text

RDBMS ʹ͍ͭͯ IBUFOBJOUFSO !

Slide 10

Slide 10 text

RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational Database • 関係データベース • Database Management System • データベース管理システム関係データベースのデータベース管理システム IBUFOBJOUFSO !"

Slide 11

Slide 11 text

ؔ܎σʔλϕʔε 関係モデルに基づいたデータベース関係モデルとは？ → データを関係として表現し取り扱うデータの表現⽅法関係データベースでは、⾒た⽬としては表（テーブル）の形式でデータを表現する ! リレーショ≒表です。% 表の間の参照ではなく、表そのもの ! 漢(オトコ)のコンピュータ道: RDBMSに関する典型的な誤解が絶えないという現実 IBUFOBJOUFSO !!

Slide 12

Slide 12 text

ؔ܎σʔλϕʔε IBUFOBJOUFSO !"

Slide 13

Slide 13 text

ؔ܎σʔλϕʔε 関係データベース以外のデータベースシステムもあるが、この講義では扱わない例えばこういうものがあります: IBUFOBJOUFSO !"

Slide 14

Slide 14 text

σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • 外部からの指⽰を受け付けてデータを操作する、インターフェースの役割 • データを使う⼈はデータがどのように格納されているか意識しなくてもいい • データを抽象化し、効率よく保存、取り出せる • ⽤途にあわせて最適な構造でデータを保存する • データを堅牢に扱う仕組みを提供する • 整合性を保つ • 並列アクセスされても齟齬が起きないようにする • データの損失を防ぐ IBUFOBJOUFSO !"

Slide 15

Slide 15 text

σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • csv として保存するのと何が違うのかを考えると分かりやすい • SQL で扱うことは可能 • CRUD はできる • 効率良く取り出すには不⾜ • 頭から舐める以外のアクセス⽅法が無い • 堅牢性もおそらく不⾜ • 複数⼈で扱うと壊れがち IBUFOBJOUFSO !"

Slide 16

Slide 16 text

SQL ͷ͓͞Β͍ IBUFOBJOUFSO !"

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

༻ޠ IBUFOBJOUFSO !"

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

σʔλఆٛ • DDL (Data Deﬁnition Language) ← これ • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"

Slide 22

Slide 22 text

CREATE TABLE จ2 • テーブルを定義する構⽂ • CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ); -- `--` ͸ίϝϯτ -- id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ -- users ͱ͍͏໊લͰఆٛ CREATE TABLE ùsers` ( ìd` bigint NOT NULL, `name` varchar(32) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (ìd`), UNIQUE KEY `name` (`name`) ); ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント IBUFOBJOUFSO !!

Slide 23

Slide 23 text

CREATE TABLE จ (PostgreSQL)3 • MySQL/PosgreSQL で⽅⾔が違う例 • ` -> " • DATETIME -> TIMESTAMP • UNIQUE の書き⽅が違う CREATE TABLE "blogs" ( "id" bigint NOT NULL, "name" varchar(32) NOT NULL, "created_at" timestamp NOT NULL, PRIMARY KEY ("id"), UNIQUE ("name") ); ! PostgreSQL: Documentation: 56: CREATE TABLE IBUFOBJOUFSO !"

Slide 24

Slide 24 text

CREATE TABLE จ `id` bigint NOT NULL, • `id` の部分: カラムの名前 • BIGINT の部分: カラムのデータ型 • NOT NULL の部分: カラムの制約 IBUFOBJOUFSO !"

Slide 25

Slide 25 text

σʔλܕ IBUFOBJOUFSO !"

Slide 26

Slide 26 text

σʔλܕ • 型によって保存に使われる容量が異なるので⼤事 • 後から変えるのも⼤変なことが多い • よく使うものについて紹介 • 数値型 • ⽂字列型 • ⽇付と時間型 IBUFOBJOUFSO !"

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

਺஋ܕ • 桁あふれに気をつける必要がある/ • レコードごとにユニークにつける id などは BIGINT UNSIGNED にしておくと安⼼ • 18446744073709551615（1844京）まで保存できる ! 21億とか42億でエラーが出たら桁あふれを疑う。integer (unsigned) の上限 IBUFOBJOUFSO !"

Slide 29

Slide 29 text

จࣈྻܕ • 多くの場合で VARCHAR(n) を使う • 可変⻑⽂字列で 0 〜 65,535 バイト保存可能 • VARCHAR(255) のように、保存する最⼤⽂字数を指定する • ある程度⻑い⽂字列を保存するときにはテキスト型を利⽤ • MySQL では TEXT, MEDIUMTEXT, LONGTEXT など IBUFOBJOUFSO !"

Slide 30

Slide 30 text

೔෇ͱ࣌ؒܕ • 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 • タイムゾーンの概念がある IBUFOBJOUFSO !"

Slide 31

Slide 31 text

೔෇ͱ࣌ؒܕ ⽇付と時間の両⽅を含んではいない型もある（登場頻度は少ない） • DATE: ⽇付のみ • TIME: 時間のみ IBUFOBJOUFSO !"

Slide 32

Slide 32 text

೔෇ͱ࣌ؒܕ 余談: MySQL の DateTime 型が 8 byte を綺麗に使っているので⾒てくれ | Bits | Field | Value | | ----: | :---- | :---- | | 1 | sign |(used when on disk) | | 17 | year*13+month |(year 0-9999, month 0-12) | | 5 | day |(0-31)| | 5 | hour |(0-23)| | 6 | minute |(0-59)| | 6 | second |(0-59)| | 24 | microseconds |(0-999999)| https://github.com/mysql/mysql-server/blob/mysql-6.7.8/mysys/my_time.cc#L8<=>- L88 IBUFOBJOUFSO !"

Slide 33

Slide 33 text

੍໿ IBUFOBJOUFSO !!

Slide 34

Slide 34 text

੍໿ • カラムに⼊るデータに対する制限 • 意図しないデータが⼊り込まないように • 制約に反するとエラーになる IBUFOBJOUFSO !"

Slide 35

Slide 35 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 から返されてきたクエリの結果。 IBUFOBJOUFSO !"

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

վΊͯ users ςʔϒϧͷఆٛΛݟΔ CREATE TABLE ùsers` ( ìd` bigint NOT NULL, `name` varchar(32) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (ìd`), UNIQUE KEY `name` (`name`) ); users という名前のテーブルを定義し、以下のカラムを持たせる • id • 整数の値が⼊り、この値によってテーブル内のレコードを⼀意に識別できる • name • 最⼤32⽂字の⽂字列の値が⼊り、NULL でないかつデータが重複することがない • created_at • ⽇時の値が⼊り、NULL ではない IBUFOBJOUFSO !"

Slide 39

Slide 39 text

σʔλૢ࡞ • DDL (Data Deﬁnition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) ← これ • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"

Slide 40

Slide 40 text

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

Slide 41

Slide 41 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) ); IBUFOBJOUFSO !"

Slide 42

Slide 42 text

INSERT จ (Create)6 テーブルにレコードを追加する INSERT ⽂ INSERT INTO users (id, name, created_at) VALUES (1, '͸ͯͳଠ࿠', '2024-08-19 16:30:00'); Query OK, 1 row affected (0.01 sec) INSERT ... SET 形式のような別の書き⽅もある ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.6 INSERT ステートメント IBUFOBJOUFSO !"

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

UPDATE จ (Update)8 既に存在するレコードを更新する 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 = '͸ͯͳೋ࿠'; WHERE 句から書き始めるのがエンジニアしぐさ。⼿が勝⼿に Enter を押しても安全 ! MySQL :: MySQL !.* リファレンスマニュアル :: 13.2.13 UPDATE ステートメント IBUFOBJOUFSO !"

Slide 46

Slide 46 text

DELETE จ (Delete)9 レコードを削除する DELETE FROM users WHERE id = 1; Query OK, 1 row affected (0.01 sec) -- ஫ҙ! WHERE ۟Ͱ࡟আର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅࡟আ͞ΕΔ DELETE FROM users; SELECT ⽂として書いて、対象データを確認してから SELECT -> DELETE に書き換えるのがエンジニアしぐさ ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.2.2 DELETE ステートメント IBUFOBJOUFSO !"

Slide 47

Slide 47 text

σʔλͷऔಘํ๏ ⽬的⼿段条件をつけて絞り込み WHERE 句ソート ORDER BY 句取得件数の制限 LIMIT 句ページネーション LIMIT 句と OFFSET 句を組み合わせるグルーピング GROUP BY 句テーブル同⼠の結合 JOIN 句 IBUFOBJOUFSO !"

Slide 48

Slide 48 text

WHERE ۟ 条件をつけて絞り込む SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; IBUFOBJOUFSO !"

Slide 49

Slide 49 text

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

Slide 50

Slide 50 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 '͸ͯͳ%'; IBUFOBJOUFSO !"

Slide 51

Slide 51 text

ൺֱ • ⽇付の⽐較ではタイムゾーンを考える必要がある • DATETIME 型はタイムゾーンを持たない • DB には UTC で保存してあることが多い -- ࠓ೔ొ࿥͞ΕͨϢʔβ SELECT * FROM users WHERE created_at > '2024-08-18 15:00:00'; -- ΋͘͠͸ SELECT * FROM users WHERE created_at > DATE_ADD('2024-08-19 00:00:00', INTERVAL -9 HOUR); IBUFOBJOUFSO !"

Slide 52

Slide 52 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 | ͸ͯͳೋ࿠ | 2024-08-19 17:30:00 | | 1 | ͸ͯͳଠ࿠ | 2024-08-19 16:30:00 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) IBUFOBJOUFSO !"

Slide 53

Slide 53 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; IBUFOBJOUFSO !"

Slide 54

Slide 54 text

ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !"

Slide 55

Slide 55 text

ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !!

Slide 56

Slide 56 text

ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !"

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 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) IBUFOBJOUFSO !"

Slide 60

Slide 60 text

άϧʔϐϯά (GROUP BY ۟) IBUFOBJOUFSO !"

Slide 61

Slide 61 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) クエリにテーブルが複数登場する場合、ςʔϒϧ໊.ΧϥϜ໊ でカラムを表す IBUFOBJOUFSO !"

Slide 62

Slide 62 text

ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) IBUFOBJOUFSO !"

Slide 63

Slide 63 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 などの種類があるが、ここでは触れない IBUFOBJOUFSO !"

Slide 64

Slide 64 text

ςʔϒϧઃܭ IBUFOBJOUFSO !"

Slide 65

Slide 65 text

ςʔϒϧઃܭ ブログサービスを例に考えてみる • ユーザーがいる • ユーザーは名前を持つ • ユーザーはブログを持てる • ブログにはタイトルが付く • ブログには説明⽂が書ける • ユーザーはブログを複数持てるどんなテーブル(表)で表せるか？ IBUFOBJOUFSO !"

Slide 66

Slide 66 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 全部⼀つのテーブル(表)で書いてみる IBUFOBJOUFSO !!

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 扱う情報に着⽬してテーブルを分割 • 「ユーザー」と「ブログ」に着⽬して、それぞれをテーブルで表現する • users テーブルと blogs テーブルに分割する • それぞれのテーブルに、⾏を⼀意に特定できるキーを追加する • そのキーを使って、テーブル同⼠が関連を持てるようにする IBUFOBJOUFSO !"

Slide 69

Slide 69 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルを分割する IBUFOBJOUFSO !"

Slide 70

Slide 70 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルに⾏を特定できるキーを追加する IBUFOBJOUFSO !"

Slide 71

Slide 71 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά そのキーを使ってテーブル同⼠に関連を持たせる IBUFOBJOUFSO !"

Slide 72

Slide 72 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά users と blogs は⼀対多の関係 IBUFOBJOUFSO !"

Slide 73

Slide 73 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά • 多のテーブルに、⼀のテーブルのレコードを⼀意に特定するカラムの値を持たせれば、⼀対多の関係を表せる • 着⽬する情報の関係を図にしてみると捉えやすい • ER図 - Google 検索 IBUFOBJOUFSO !"

Slide 74

Slide 74 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) ); IBUFOBJOUFSO !"

Slide 75

Slide 75 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) IBUFOBJOUFSO !"

Slide 76

Slide 76 text

ςʔϒϧઃܭ - ߪಡػೳ ここで購読機能を追加することを考えてみる • ユーザーはブログを購読できる • 例: ユーザー「はてな太郎」は、「⼆郎の⽇記」「⼆郎の技術ブログ」のブログを購読できる • SNS でいう「フォロー」機能 • これをテーブル構造で表すには？ IBUFOBJOUFSO !"

Slide 77

Slide 77 text

ςʔϒϧઃܭ - ߪಡػೳ users と購読対象の blogs は多対多の関係 IBUFOBJOUFSO !!

Slide 78

Slide 78 text

ςʔϒϧઃܭ - ߪಡػೳ • blogs に subscribe_user_id（このブログを購読しているユーザー ID）のようなカラムを⾜すと？ • blogs.subscribe_user_id は1つしか値を格納できない • → 複数⼈が同じブログを購読している状態を表現できない • 多対多の関係は、どちらかのテーブルにカラムを追加するだけでは表現できない IBUFOBJOUFSO !"

Slide 79

Slide 79 text

ςʔϒϧઃܭ - ߪಡػೳ • users と blogs の購読の関係を表すテーブルを作る • subscriptions テーブル • subscriptions から users と blogs のレコードを特定できるように、user_id と blog_id カラムを持つ • この例に限らず、多対多の関係を表すには、関係を表すテーブルを作ってみるといい IBUFOBJOUFSO !"

Slide 80

Slide 80 text

ςʔϒϧઃܭ - ߪಡػೳ 完成形 CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); IBUFOBJOUFSO !"

Slide 81

Slide 81 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) IBUFOBJOUFSO !"

Slide 82

Slide 82 text

ςʔϒϧઃܭ まとめ • 扱う情報に着⽬してテーブルを考えてみる • テーブル同⼠の関係は図にしてみるとわかりやすい • 関係⾃体をテーブルで表すとすっきりすることもある IBUFOBJOUFSO !"

Slide 83

Slide 83 text

ςʔϒϧઃܭͷצॴ • ユースケースから名詞と動詞を抽出する • ユーザーはブログを複数持つ • ユーザーはブログを購読する • 名詞はマスタエンティティに • 動詞は名詞化した上でイベントエンティティに • マスタ • モノを表す • 名前を持つ • users、blogs • イベント • 出来事を表す • 必ず時刻を持つ • 名前を持たない • subscriptions IBUFOBJOUFSO !"

Slide 84

Slide 84 text

ςʔϒϧઃܭͷצॴ • マスタとイベントという分け⽅だが、隠れたリソースを探す⽬を持つと良い • ユーザーがインターンシップに参加する (registrations) • 単なる多対多 (habtm) ではなく、例えばオンライン参加かどうか、何経由で登録したか、をカラムとして持つことができる • ユーザーフォローは followership というリソース • Web API 設計にも繋がる • CRUD は POST/GET/PUT/DELETE で表現できる • POST /:user_id/follow よりも POST /followerships を好む IBUFOBJOUFSO !"

Slide 85

Slide 85 text

ύϑΥʔϚϯε IBUFOBJOUFSO !"

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

ΠϯσοΫε IBUFOBJOUFSO !"

Slide 88

Slide 88 text

ΠϯσοΫε • その名の通り、索引のこと • 例えばこの資料の中から「SELECT」が登場するページを探したい • 41ページ⽬が初出 • インデックスが無かったら、最初から1⽂字ずつ⾒ていくことになる • インデックスがあれば⼆分探索できる IBUFOBJOUFSO !!

Slide 89

Slide 89 text

ΠϯσοΫε • データベースでも同じ • レコードを検索する際、インデックスがなければ、先頭⾏からテーブル全体を読み取り対象のレコードを⾒つけ出す • 例: SELECT * FROM users WHERE id = 100000; なら、テーブルの先頭から1件ずつ⾒ていって id が 100000 に⼀致するものを探す • インデックスはテーブルを検索するために最適なデータ構造で保存される • MySQL で使われているのは B+Tree IBUFOBJOUFSO !"

Slide 90

Slide 90 text

ΠϯσοΫε B+Tree • 定義は⾯倒なので端折る • 平衡が保たれている • 内部ノードは値を持たず、葉ノードが値を持つ • 葉ノードは次の葉ノードへのポインタを持つ • 範囲検索に強い • 右図は平衡を説明するだけのツリーでB+⽊ではないよ IBUFOBJOUFSO !"

Slide 91

Slide 91 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) ); IBUFOBJOUFSO !"

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

ΠϯσοΫε • 複合インデックス • 右図は [c0, c2] で張った複合インデックスのイメージ • c0 が同じ場合に更に c2 で並び替える • c2 単体を⾒るとまったくソートされていないことに注意 • c2 のみでの絞り込みや並び替えには無⼒ • 必要なら [c2] でインデックスを張る id c$ c% & % $ ' % ' ( % ) $ & $ * & & % & ) IBUFOBJOUFSO !"

Slide 95

Slide 95 text

ΠϯσοΫε • よくある失敗 • カーディナリティが低い • 絞り込みがまったく効かない • 例えばみんなを卒業年次でインデックスを作っても2値しか持たない • 複合インデックスと単発インデックスを同時に張る • name と [name, େֶ໊] • 後者で前者が満たせる IBUFOBJOUFSO !"

Slide 96

Slide 96 text

ΠϯσοΫε もっと知りたかったらこの辺。 • MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ • MySQLのインデックスの貼っていいとき悪いときを原理から理解したいよ ! IBUFOBJOUFSO !"

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

Slide 99

Slide 99 text

Slide 100

Slide 100 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) IBUFOBJOUFSO !""

Slide 101

Slide 101 text

ΠϯσοΫεͷ༨ஊ 暗黙の型変換に注意 WITH dummy AS ( SELECT '1' AS id UNION ALL SELECT '01' UNION ALL SELECT ' 1' UNION ALL SELECT '1a' ) SELECT * FROM dummy WHERE id = 1; +----+ | id | +----+ | 1 | | 01 | | 1 | | 1a | +----+ 4 rows in set, 1 warning (0.00 sec) • 01 が引っかかっているということは • インデックスは効かない • foo_id を VARCHAR で定義すると…… IBUFOBJOUFSO !"!

Slide 102

Slide 102 text

ύϑΥʔϚϯε 「推測するな、計測せよ」という格⾔があります • パフォーマンス対策をするときは勘で対処してはいけない • きちんと計測し、ボトルネックを把握して対処していく必要がある IBUFOBJOUFSO !"#

Slide 103

Slide 103 text

ऴΘΓʹ IBUFOBJOUFSO !"#

Slide 104

Slide 104 text

ऴΘΓʹ • 1時間お疲れ様でした • 概念‧SQL‧テーブル設計‧パフォーマンスに少しずつ触れましたが、基礎的なトピックを全て扱えているわけではないです • 知識や理論については、空いた時間やインターンが終わった後に⾒てみてください • 実サービスのデータ量を扱う経験は、後半パートで体験してもらえると思います • 数千万〜億単位のレコードを扱う経験は貴重なので楽しんでいってね IBUFOBJOUFSO !"#

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

෇࿥ IBUFOBJOUFSO !"#

Slide 107

Slide 107 text

SQL Ξϯνύλʔϯ͔Βز͔ͭ঺հ • EAV • ナイーブツリー • ジェイウォーク IBUFOBJOUFSO !"#

Slide 108

Slide 108 text

SQL Ξϯνύλʔϯ: EAV • EAV (エンティティ‧アトリビュート‧バリュー) • 「DB を更新せずにプラグインを導⼊できる」という状況でよく発⽣する • 本来はカラムで表現すべきものを、 EAV で無理⽮理実現する issue_id attr_name attr_value ./01 product . ./01 version_aﬀ ected ..7 ./01 severity 機能の損失 ./0> product . ./0> sponsor ヒガシマル IBUFOBJOUFSO !"#

Slide 109

Slide 109 text

SQL Ξϯνύλʔϯ: EAV • STI、CCI、CTI で解決する • 詳しくは PoEAA を参照 IBUFOBJOUFSO !"#

Slide 110

Slide 110 text

SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • ナイーブツリー • 親⼦関係があるものをナイーブ (素朴) に作ってしまう comment_id parent_id 発⾔者コメント 4 NULL Fran このバグの原因は何かな？ D 4 Ollie ヌルポインターのせいじゃないかな？ Q D Fran そうじゃないよ。それは確認済みだ。 \ 4 Kukla 無効な⼊⼒を調べてみたら？ j \ Ollie そうか、バグの原因はそれだな。 l \ Fran よし、じゃあチェック機能を追加してもらえるかな？ z l Kukla 了解。修正したよ。 IBUFOBJOUFSO !!"

Slide 111

Slide 111 text

SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • 以下のいずれかを使うことで解決する • 経路列挙モデル • ⼊れ⼦集合モデル • 閉包テーブル • WITH 句が使えるのでナイーブツリーのままでも良い IBUFOBJOUFSO !!!

Slide 112

Slide 112 text

SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • カンマ区切り等でデータを⼊れる • VARCHAR で定義しているので ID の⽂字数によって上限が変わる • 外部キー制約が効かない user_id blog_ids , ,,. / / IBUFOBJOUFSO !!"

Slide 113

Slide 113 text

SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • 中間テーブルを作ることで解決する • 絶対に検索しないなら使っても良い • が、9割⽅は設計ミスなので避けるべき • カラムに JSON や YAML を⼊れることは割とよくある • JSON 型aaが存在している !! LONGTEXT かつ JSON のパースを保存時に⾏っているので効率的に扱える IBUFOBJOUFSO !!"

Slide 114

Slide 114 text

෇࿥ お⼿元で⼿軽に SQL のクエリを試してもらえるよう、Docker でデータベースを起動してその中に⼊る⽅法を書きました IBUFOBJOUFSO !!"

Slide 115

Slide 115 text

Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (1/3) MySQL のコンテナを起動 $ docker pull mysql:9.0 $ docker run --rm --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:9.0 上記のコンテナの MySQL に⼊る $ docker container exec -it rdbms-bootcamp mysql -uuser -ppasswd bootcamp IBUFOBJOUFSO !!"

Slide 116

Slide 116 text

Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) 注意点 • root ユーザーでは Docker コンテナ内からでないと⼊れません • → 権限管理の関係 • 公式のイメージだと mysql client から⼊ると⽇本語が⼊⼒できない‧出⼒されない • OS に⽇本語設定が⼊っていないため • 以下の⽅法で⽇本語⼊⼒できるようになる IBUFOBJOUFSO !!"

Slide 117

Slide 117 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 IBUFOBJOUFSO !!"

Slide 118

Slide 118 text

MySQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; -- MySQL ͔Βग़Δ exit IBUFOBJOUFSO !!"

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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