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

https://hatena.co.jp/recruit/intern/2024

Transcript

1. RDBMS ϒʔτΩϟϯϓ IBUFOBJOUFSO !
2. ͸͡Ίʹ ブートキャンプとは • 後半パートで困らないことを⽬的に • 概念からではなく、具体例やテクニック中⼼に • 結構駆け⾜気味なので、後で調べるとっかかりの⼀つにしても らえると幸いです IBUFOBJOUFSO !
3. ͸͡Ίʹ • はてなでは主に MySQL と PostgreSQL という DBMS をよく使 っています • 配属チームは MySQL を使っているので、このブートキャン プでは MySQL のみを扱います • 後半パートの機能開発でこれらを使う際に、0 から調べること がないように基礎的な部分を説明します IBUFOBJOUFSO !
4. ͜ͷϒʔτΩϟϯϓͷΰʔϧ MySQL をなんとなく扱えるようになる • データの保存‧参照‧更新‧削除ができる • テーブル設計時に気にすることが分かる • インデックスがなんとなく分かる IBUFOBJOUFSO !
5. ͜ͷߨٛͰѻΘͳ͍͜ͱ • データベースの内部の仕組み • 詳しいテーブル設計や SQL のテクニック • パフォーマンスの詳しい話 • MySQL 以外のこと IBUFOBJOUFSO !
6. ࣭໰ • SQL 知っている⼈ﾉｼ IBUFOBJOUFSO !
7. ࣭໰ • 触り慣れてる⼈ﾉｼ IBUFOBJOUFSO !
8. ໨࣍ • RDBMS について • SQL のおさらい • テーブル設計 • インデックス • 付録 IBUFOBJOUFSO !
9. RDBMS ʹ͍ͭͯ IBUFOBJOUFSO !
10. RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational Database • 関係データベース • Database Management System • データベース管理システム 関係データベースのデータベース管理システム IBUFOBJOUFSO !"
11. ؔ܎σʔλϕʔε 関係モデルに基づいたデータベース 関係モデルとは？ → データを関係として表現し取り扱うデータの表現⽅法 関係データベースでは、 ⾒た⽬としては表（テーブル）の形式でデータを表現する ! リレーショ≒表 です。% 表の間の参照ではなく、表そのもの ! 漢(オトコ)のコンピュータ道: RDBMSに関する典型的な誤解が絶えないという現実 IBUFOBJOUFSO !!
12. ؔ܎σʔλϕʔε IBUFOBJOUFSO !"
13. ؔ܎σʔλϕʔε 関係データベース以外のデータベースシステムもあるが、この講義では扱わない 例えばこういうものがあります: IBUFOBJOUFSO !"
14. σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • 外部からの指⽰を受け付けてデータを操作する、インターフェースの役割 • データを使う⼈はデータがどのように格納されているか意識しなくてもいい • データを抽象化し、効率よく保存、取り出せる • ⽤途にあわせて最適な構造でデータを保存する • データを堅牢に扱う仕組みを提供する • 整合性を保つ • 並列アクセスされても齟齬が起きないようにする • データの損失を防ぐ IBUFOBJOUFSO !"
15. σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • csv として保存するのと何が違うのかを考えると分かりやすい • SQL で扱うことは可能 • CRUD はできる • 効率良く取り出すには不⾜ • 頭から舐める以外のアクセス⽅法が無い • 堅牢性もおそらく不⾜ • 複数⼈で扱うと壊れがち IBUFOBJOUFSO !"
16. SQL ͷ͓͞Β͍ IBUFOBJOUFSO !"
17. ༻ޠ ⽤語 意味 テーブル データの集合 レコード テーブルのデータの組 1 ⾏ 1 ⾏ カラム テーブルのデータの属性 スキーマ テーブルの構造 （どんなカラムがあるか、カラムにはど んな値が⼊るか、など） IBUFOBJOUFSO !"
18. ༻ޠ IBUFOBJOUFSO !"
19. SQL RDBMS に問い合わせ（操作）を⾏うための⾔語 • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など • DCL (Data Control Language) • データへのアクセス制御に関する SQL • この講義では紹介しない SQL で書かれた、データベースへの問い合わせ⽂を、「クエリ」と呼ぶ IBUFOBJOUFSO !"
20. SQL SQL には規格があるが、それにどこまで準拠しているかは DBMS によってまちまち DBMS はそれぞれ独⾃拡張（⽅⾔）を持っていて、互換性がない こともある このブートキャンプでも、「MySQL ではこの書き⽅ができます」 のような話が出てきたら⽅⾔のことだと思ってください IBUFOBJOUFSO !"
21. σʔλఆٛ • DDL (Data Definition Language) ← これ • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"
22. CREATE TABLE จ2 • テーブルを定義する構⽂ • CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ); -- `--` ͸ίϝϯτ -- id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ -- users ͱ͍͏໊લͰఆٛ CREATE TABLE `users` ( `id` bigint NOT NULL, `name` varchar(32) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ); ! MySQL :: MySQL ).+ リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント IBUFOBJOUFSO !!
23. 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 !"
24. CREATE TABLE จ `id` bigint NOT NULL, • `id` の部分: カラムの名前 • BIGINT の部分: カラムのデータ型 • NOT NULL の部分: カラムの制約 IBUFOBJOUFSO !"
25. σʔλܕ IBUFOBJOUFSO !"
26. σʔλܕ • 型によって保存に使われる容量が異なるので⼤事 • 後から変えるのも⼤変なことが多い • よく使うものについて紹介 • 数値型 • ⽂字列型 • ⽇付と時間型 IBUFOBJOUFSO !"
27. ਺஋ܕ • 整数 • TINYINT, INTEGER, BIGINT など • 右に⾏くほど多くの桁を保存できる • MySQL では UNSIGNED 属性を付与すると符号無しで保存できるようになる（BIGINT UNSIGNED） • 属性: カラムの特性。つけられる属性は、データ型によって異なる • 浮動⼩数点 • FLOAT, DOUBLE • 固定⼩数点 • DECIMAL, NUMERIC IBUFOBJOUFSO !"
28. ਺஋ܕ • 桁あふれに気をつける必要がある/ • レコードごとにユニークにつける id などは BIGINT UNSIGNED に しておくと安⼼ • 18446744073709551615（1844京）まで保存できる ! 21億とか42億でエラーが出たら桁あふれを疑う。integer (unsigned) の上限 IBUFOBJOUFSO !"
29. จࣈྻܕ • 多くの場合で VARCHAR(n) を使う • 可変⻑⽂字列で 0 〜 65,535 バイト保存可能 • VARCHAR(255) のように、保存する最⼤⽂字数を指定する • ある程度⻑い⽂字列を保存するときにはテキスト型を利⽤ • MySQL では TEXT, MEDIUMTEXT, LONGTEXT など IBUFOBJOUFSO !"
30. ೔෇ͱ࣌ؒܕ • 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 !"
31. ೔෇ͱ࣌ؒܕ ⽇付と時間の両⽅を含んではいない型もある （登場頻度は少ない） • DATE: ⽇付のみ • TIME: 時間のみ IBUFOBJOUFSO !"
32. ೔෇ͱ࣌ؒܕ 余談: 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<=>- L8<?8 IBUFOBJOUFSO !"
33. ੍໿ IBUFOBJOUFSO !!
34. ੍໿ • カラムに⼊るデータに対する制限 • 意図しないデータが⼊り込まないように • 制約に反するとエラーになる IBUFOBJOUFSO !"
35. ੍໿ 例: 「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 !"
36. ੍໿ͷྫ • NOT NULL制約: カラムは NULL であってはいけない • NULL は、値が⽋損していることを表す • UNIQUE 制約: カラムの値がテーブル内で⼀意でなければならない • ただし NULL はいくつあっても許されるので注意 • PRIMARY KEY 制約: カラムの値がテーブル内でレコードを⼀意に識別できなけれ ばいけない • FOREIGN KEY 制約: 紐づいているテーブル間で整合性が取れていなければいけない IBUFOBJOUFSO !"
37. PRIMARY KEY (ओΩʔ) • テーブル内でレコードを⼀意に識別できるカラム • 1つのテーブルで1つ指定できる • PRIMARY KEY 制約は、UNIQUE 制約と NOT NULL 制約を併せ持って いるといえる • PRIMARY KEYはインデックス（後述）としても使える IBUFOBJOUFSO !"
38. վΊͯ users ςʔϒϧͷఆٛΛݟΔ CREATE TABLE `users` ( `id` bigint NOT NULL, `name` varchar(32) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ); users という名前のテーブルを定義し、以下のカ ラムを持たせる • id • 整数の値が⼊り、この値によってテーブル 内のレコードを⼀意に識別できる • name • 最⼤32⽂字の⽂字列の値が⼊り、NULL でな いかつデータが重複することがない • created_at • ⽇時の値が⼊り、NULL ではない IBUFOBJOUFSO !"
39. σʔλૢ࡞ • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) ← これ • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"
40. CRUD • データ操作する際の基本機能 • それぞれの操作の頭⽂字から • Create (追加) • Read (参照) • Update (更新) • Delete (削除) IBUFOBJOUFSO !"
41. Ҏ߱͸ҎԼͷςʔϒϧ͕͋ΔલఏͰ ユーザーと、ユーザーが所有するブログを管理している状態 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 !"
42. 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 !"
43. 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 !"
44. 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 !!
45. 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 !"
46. 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 !"
47. σʔλͷऔಘํ๏ ⽬的 ⼿段 条件をつけて絞り込み WHERE 句 ソート ORDER BY 句 取得件数の制限 LIMIT 句 ページネーション LIMIT 句と OFFSET 句を組み合わせる グルーピング GROUP BY 句 テーブル同⼠の結合 JOIN 句 IBUFOBJOUFSO !"
48. WHERE ۟ 条件をつけて絞り込む SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; IBUFOBJOUFSO !"
49. ൺֱ 「等しい」は =、「等しくない」は !=（<> とも書かれる） プログラミング⾔語の数値や⽂字列のように >, <, >=, <= で⽐較できる -- 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 !"
50. ൺֱ • 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 !"
51. ൺֱ • ⽇付の⽐較ではタイムゾーンを考える必要がある • 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 !"
52. 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 !"
53. ϖʔδωʔγϣϯ (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 !"
54. ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !"
55. ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !!
56. ϖʔδωʔγϣϯ (LIMIT ۟ / OFFSET ۟) IBUFOBJOUFSO !"
57. ϖʔδωʔγϣϯ ただし、LIMIT/OFFSET を使ったページネーションには以下のようなデメリットがある • パフォーマンス • OFFSET で指定された⾏数まで辿り着くまでに全ての⾏数を数えなければいけない • データがずれる • ページネーションをしている途中に⾏が差し込まれたり削除されたりしたら、ずれてしまう • 例: 1ページ⽬(1〜10件⽬)をクエリ → 1件⽬のデータが削除される → 2ページ⽬(11〜20件 ⽬)をクエリ → もともとの11件⽬がどちらのページにも現れない これらのデメリットを避けるために、次の⽅法がよく使われる IBUFOBJOUFSO !"
58. ϖʔδωʔγϣϯ 前ページの最終⾏の値を、ページの区切りのキーとして使う Keyset Pagination と呼ばれている 例: 1ページ⽬は id = - のレコードが最後だったので、2ページ⽬を取得するときに は id > - のものを取得する SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ メリット • パフォーマンス • インデックス（後述）があれば、現在のページの⾏まで素早く⾶べる • データのずれ • 起こらない デメリット • 任意のページまで⾶べない • 1ページ⽬から10ページ⽬に⾏く、というのは不可能 • 前後ページへのナビゲーションを出そうとすると⼯夫がいる • ページの前後にデータが存在するか確認するクエリも必要になる • 単調増加するカラムが必要になる • 単調増加するカラムを指定するか AND で組み合わせる必要がある IBUFOBJOUFSO !"
59. άϧʔϐϯά (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 !"
60. άϧʔϐϯά (GROUP BY ۟) IBUFOBJOUFSO !"
61. ςʔϒϧಉ࢜ͷ݁߹ (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 !"
62. ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) IBUFOBJOUFSO !"
63. ςʔϒϧಉ࢜ͷ݁߹ (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 !"
64. ςʔϒϧઃܭ IBUFOBJOUFSO !"
65. ςʔϒϧઃܭ ブログサービスを例に考えてみる • ユーザーがいる • ユーザーは名前を持つ • ユーザーはブログを持てる • ブログにはタイトルが付く • ブログには説明⽂が書ける • ユーザーはブログを複数持てる どんなテーブル(表)で表せるか？ IBUFOBJOUFSO !"
66. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 全部⼀つのテーブル(表)で書いてみる IBUFOBJOUFSO !!
67. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά これでも可能ではあるけど... • ユーザー名の情報が重複している • 間違えて⼀部だけ更新してしまったら整合性がなくなってしまう • シンプルなことをしたいだけなのに複雑なクエリを書く場所ができてしまう • 例: ユーザー名⼀覧を取得するためには SELECT DISTINCT user_name FROM blogs;というクエリになる • 扱う情報が増えたらどうする？ • 例: ブログに記事を複数持たせたい • 記事カラムを追加したらこのテーブルでも実現できるが、記事の数だけレコードが作られるので、そ の分ユーザー‧ブログのデータの重複が増える IBUFOBJOUFSO !"
68. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 扱う情報に着⽬してテーブルを分割 • 「ユーザー」と「ブログ」に着⽬して、それぞれをテーブルで 表現する • users テーブルと blogs テーブルに分割する • それぞれのテーブルに、⾏を⼀意に特定できるキーを追加する • そのキーを使って、テーブル同⼠が関連を持てるようにする IBUFOBJOUFSO !"
69. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルを分割する IBUFOBJOUFSO !"
70. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルに⾏を特定できるキーを追加する IBUFOBJOUFSO !"
71. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά そのキーを使ってテーブル同⼠に関連を持たせる IBUFOBJOUFSO !"
72. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά users と blogs は⼀対多の関係 IBUFOBJOUFSO !"
73. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά • 多のテーブルに、⼀のテーブルのレコードを⼀意に特定するカ ラムの値を持たせれば、⼀対多の関係を表せる • 着⽬する情報の関係を図にしてみると捉えやすい • ER図 - Google 検索 IBUFOBJOUFSO !"
74. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 完成形 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 !"
75. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ユーザー名とブログ名は別のテーブルに格納されるようになった ので、それらのデータを⼀気に取得するには 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 !"
76. ςʔϒϧઃܭ - ߪಡػೳ ここで購読機能を追加することを考えてみる • ユーザーはブログを購読できる • 例: ユーザー「はてな太郎」は、「⼆郎の⽇記」「⼆郎の技術 ブログ」のブログを購読できる • SNS でいう「フォロー」機能 • これをテーブル構造で表すには？ IBUFOBJOUFSO !"
77. ςʔϒϧઃܭ - ߪಡػೳ users と購読対象の blogs は多対多の関係 IBUFOBJOUFSO !!
78. ςʔϒϧઃܭ - ߪಡػೳ • blogs に subscribe_user_id（このブログを購読しているユーザ ー ID）のようなカラムを⾜すと？ • blogs.subscribe_user_id は1つしか値を格納できない • → 複数⼈が同じブログを購読している状態を表現できない • 多対多の関係は、どちらかのテーブルにカラムを追加するだけ では表現できない IBUFOBJOUFSO !"
79. ςʔϒϧઃܭ - ߪಡػೳ • users と blogs の購読の関係を表すテーブルを作る • subscriptions テーブル • subscriptions から users と blogs のレコードを特定できるように、user_id と blog_id カラムを持つ • この例に限らず、多対多の関係を表すには、関係を表すテーブルを作ってみるといい IBUFOBJOUFSO !"
80. ςʔϒϧઃܭ - ߪಡػೳ 完成形 CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); IBUFOBJOUFSO !"
81. ςʔϒϧઃܭ - ߪಡػೳ 「はてな太郎」という名前のユーザーが購読しているブログ⼀覧を取得するには、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 !"
82. ςʔϒϧઃܭ まとめ • 扱う情報に着⽬してテーブルを考えてみる • テーブル同⼠の関係は図にしてみるとわかりやすい • 関係⾃体をテーブルで表すとすっきりすることもある IBUFOBJOUFSO !"
83. ςʔϒϧઃܭͷצॴ • ユースケースから名詞と動詞を抽出する • ユーザーはブログを複数持つ • ユーザーはブログを購読する • 名詞はマスタエンティティに • 動詞は名詞化した上でイベントエンティティ に • マスタ • モノを表す • 名前を持つ • users、blogs • イベント • 出来事を表す • 必ず時刻を持つ • 名前を持たない • subscriptions IBUFOBJOUFSO !"
84. ςʔϒϧઃܭͷצॴ • マスタとイベントという分け⽅だが、隠れたリソースを探す⽬を持つと良い • ユーザーがインターンシップに参加する (registrations) • 単なる多対多 (habtm) ではなく、例えばオンライン参加かどうか、何経由で登録し たか、をカラムとして持つことができる • ユーザーフォローは followership というリソース • Web API 設計にも繋がる • CRUD は POST/GET/PUT/DELETE で表現できる • POST /:user_id/follow よりも POST /followerships を好む IBUFOBJOUFSO !"
85. ύϑΥʔϚϯε IBUFOBJOUFSO !"
86. ύϑΥʔϚϯε DB は Web サービスを開発‧運営する上でパフォーマンスの問題に繋がりやすい • サービスが続く限りデータは増加し続ける • 最初は問題なかったクエリが、データ量が増えたことで実⾏に時間がかかるようにな ってしまったり • DB のパフォーマンスの問題はサービスの品質低下に繋がる • クエリの実⾏に時間がかかる • → ユーザに返すレスポンスが遅くなる • → ユーザが離れてしまう IBUFOBJOUFSO !"
87. ΠϯσοΫε IBUFOBJOUFSO !"
88. ΠϯσοΫε • その名の通り、索引のこと • 例えばこの資料の中から「SELECT」が登場す るページを探したい • 41ページ⽬が初出 • インデックスが無かったら、最初から1⽂字ず つ⾒ていくことになる • インデックスがあれば⼆分探索できる IBUFOBJOUFSO !!
89. ΠϯσοΫε • データベースでも同じ • レコードを検索する際、インデックスがなければ、先頭⾏からテーブル全 体を読み取り対象のレコードを⾒つけ出す • 例: SELECT * FROM users WHERE id = 100000; なら、テーブルの先頭か ら1件ずつ⾒ていって id が 100000 に⼀致するものを探す • インデックスはテーブルを検索するために最適なデータ構造で保存される • MySQL で使われているのは B+Tree IBUFOBJOUFSO !"
90. ΠϯσοΫε B+Tree • 定義は⾯倒なので端折る • 平衡が保たれている • 内部ノードは値を持たず、葉ノードが値を持 つ • 葉ノードは次の葉ノードへのポインタを持つ • 範囲検索に強い • 右図は平衡を説明するだけのツリーでB+⽊ ではないよ IBUFOBJOUFSO !"
91. ΠϯσοΫε • 明⽰的に作る⽅法 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 !"
92. ΠϯσοΫε どういうときにインデックスを作るか？ • インデックス作成にはコストがかかる（後述）ため、無闇に作ればいいわけではない • アプリケーションで実⾏するクエリをイメージして、有効に使われそうなインデック スを考える • 例: 「あるユーザーのブログ⼀覧を取得する」 • クエリ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id を元に検索するので user_id にインデックスがあると⾼速に検索できそ う IBUFOBJOUFSO !"
93. ΠϯσοΫε インデックスの注意点 • インデックス作成にはコストがかかる • レコードの作成‧更新‧削除時にインデックスも更新するので、オーバーヘッドがある • 多くのアプリケーションでは ࢀরॲཧ > ߋ৽ॲཧ になるのであまり問題ない • レコードとは別にインデックスを格納するためのディスク領域も別途必要になる • インデックスが使われないケースもある • 例: 全件探索の⽅が早い • 都道府県を表すテーブルだと、47レコードしかないので全件探索した⽅が早い IBUFOBJOUFSO !"
94. ΠϯσοΫε • 複合インデックス • 右図は [c0, c2] で張った複合インデック スのイメージ • c0 が同じ場合に更に c2 で並び替える • c2 単体を⾒るとまったくソートされてい ないことに注意 • c2 のみでの絞り込みや並び替えには 無⼒ • 必要なら [c2] でインデックスを張る id c$ c% & % $ ' % ' ( % ) $ & $ * & & % & ) IBUFOBJOUFSO !"
95. ΠϯσοΫε • よくある失敗 • カーディナリティが低い • 絞り込みがまったく効かない • 例えばみんなを卒業年次でインデックスを作っても2値しか持たない • 複合インデックスと単発インデックスを同時に張る • name と [name, େֶ໊] • 後者で前者が満たせる IBUFOBJOUFSO !"
96. ΠϯσοΫε もっと知りたかったらこの辺。 • MySQL with InnoDB のインデックスの基礎知識とありがちな 間違い - クックパッド開発者ブログ • MySQLのインデックスの貼っていいとき悪いときを原理から理 解したいよ ! IBUFOBJOUFSO !"
97. EXPLAIN10 • クエリの実⾏計画を出⼒する⽅法 • 実⾏計画: クエリにどのようなインデックスを使⽤し、どんな順番でテ ーブルを結合するのか、など • 実⾏計画がわかれば、どの部分がパフォーマンスを悪くしているかを知 ることができる • クエリの先頭に EXPLAIN を付けることで、そのクエリの実⾏計画を得る ことができる !" MySQL :: MySQL *." リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化 IBUFOBJOUFSO !"
98. EXPLAIN ここでは MySQL の例を出しています -- ΠϯσοΫεͷޮ͔ͳ͍ΫΤϦͷEXPLAIN -- type ALL ͸ϑϧςʔϒϧεΩϟϯɺςʔϒϧશମΛಡΈऔ͍ͬͯΔ͜ͱΛࣔ͢ = ΠϯσοΫε͕࢖ΘΕ͍ͯͳ͍ EXPLAIN SELECT COUNT(*) FROM blogs WHERE title LIKE '%೔ه'; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | blogs | NULL | ALL | NULL | NULL | NULL | NULL | 109745 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) IBUFOBJOUFSO !"
99. EXPLAIN -- ΠϯσοΫε͕ޮ͘ΫΤϦͷ EXPLAIN -- type range ͸ΠϯσοΫεΛ࢖༻͠ɺಛఆͷൣғʹ͋ΔߦͷΈ͕औಘ͞Ε͍ͯΔ͜ͱΛࣔ͢ EXPLAIN SELECT COUNT(*) FROM users WHERE id > 2000; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) IBUFOBJOUFSO !!
100. 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 !""
101. ΠϯσοΫεͷ༨ஊ 暗黙の型変換に注意 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 !"!
102. ύϑΥʔϚϯε 「推測するな、計測せよ」という格⾔があります • パフォーマンス対策をするときは勘で対処してはいけない • きちんと計測し、ボトルネックを把握して対処していく必要が ある IBUFOBJOUFSO !"#
103. ऴΘΓʹ IBUFOBJOUFSO !"#
104. ऴΘΓʹ • 1時間お疲れ様でした • 概念‧SQL‧テーブル設計‧パフォーマンスに少しずつ触れましたが、基礎的 なトピックを全て扱えているわけではないです • 知識や理論については、空いた時間やインターンが終わった後に⾒てみてくだ さい • 実サービスのデータ量を扱う経験は、後半パートで体験してもらえると思いま す • 数千万〜億単位のレコードを扱う経験は貴重なので楽しんでいってね IBUFOBJOUFSO !"#
105. ऴΘΓʹ 扱えなかったトピックやキーワードについて列挙しておくので、興味があれば調べてみてください • SQL について • 複雑なクエリや集計 (サブクエリ) • 組み込み関数 • JOIN の種類とアルゴリズム • テーブル設計 • 正規化 • トランザクション • ACID 特性 • ロック • MVCC • プログラミング⾔語から SQL を扱う (付録に参考リンク記載) • N+@ 問題 • セキュリティ • SQL インジェクション IBUFOBJOUFSO !"#
106. ෇࿥ IBUFOBJOUFSO !"#
107. SQL Ξϯνύλʔϯ͔Βز͔ͭ঺հ • EAV • ナイーブツリー • ジェイウォーク IBUFOBJOUFSO !"#
108. SQL Ξϯνύλʔϯ: EAV • EAV (エンティティ‧アトリビュー ト‧バリュー) • 「DB を更新せずにプラグインを導⼊ できる」という状況でよく発⽣する • 本来はカラムで表現すべきものを、 EAV で無理⽮理実現する issue_id attr_name attr_value ./01 product . ./01 version_aff ected ..7 ./01 severity 機能の損失 ./0> product . ./0> sponsor ヒガシマル IBUFOBJOUFSO !"#
109. SQL Ξϯνύλʔϯ: EAV • STI、CCI、CTI で解決する • 詳しくは PoEAA を参照 IBUFOBJOUFSO !"#
110. SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • ナイーブツリー • 親⼦関係があるものをナイーブ (素朴) に作ってしまう comment_id parent_id 発⾔者 コメント 4 NULL Fran このバグの原因は何かな？ D 4 Ollie ヌルポインターのせいじゃないかな？ Q D Fran そうじゃないよ。それは確認済みだ。 \ 4 Kukla 無効な⼊⼒を調べてみたら？ j \ Ollie そうか、バグの原因はそれだな。 l \ Fran よし、じゃあチェック機能を追加して もらえるかな？ z l Kukla 了解。修正したよ。 IBUFOBJOUFSO !!"
111. SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • 以下のいずれかを使うことで解決する • 経路列挙モデル • ⼊れ⼦集合モデル • 閉包テーブル • WITH 句が使えるのでナイーブツリーのままでも良い IBUFOBJOUFSO !!!
112. SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • カンマ区切り等でデータを ⼊れる • VARCHAR で定義しているの で ID の⽂字数によって上限 が変わる • 外部キー制約が効かない user_id blog_ids , ,,. / / IBUFOBJOUFSO !!"
113. SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • 中間テーブルを作ることで解決する • 絶対に検索しないなら使っても良い • が、9割⽅は設計ミスなので避けるべき • カラムに JSON や YAML を⼊れることは割とよくある • JSON 型aaが存在している !! LONGTEXT かつ JSON のパースを保存時に⾏っているので効率的に扱える IBUFOBJOUFSO !!"
114. ෇࿥ お⼿元で⼿軽に SQL のクエリを試してもらえるよう、Docker で データベースを起動してその中に⼊る⽅法を書きました IBUFOBJOUFSO !!"
115. 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 !!"
116. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) 注意点 • root ユーザーでは Docker コンテナ内からでないと⼊れません • → 権限管理の関係 • 公式のイメージだと mysql client から⼊ると⽇本語が⼊⼒できない‧出⼒ されない • OS に⽇本語設定が⼊っていないため • 以下の⽅法で⽇本語⼊⼒できるようになる IBUFOBJOUFSO !!"
117. 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 !!"
118. MySQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; -- MySQL ͔Βग़Δ exit IBUFOBJOUFSO !!"
119. ࢀߟϦϯΫ (1/2) RDBMS 全般 • データベース概論Ⅰ | 筑波⼤学オープンコースウェア｜TSUKUBA OCW • ある程度 DB を扱うことに慣れてきてから⾒てみると、理論と実践が合わさってよいと思う リファレンス • MySQL (.* リファレンスマニュアル • PostgreSQL: Documentation: EF: PostgreSQL EF.G Documentation • リファレンスなので、困ったときに辞書的に使うと良いです • 実際に使うバージョンに合ったマニュアルを参照した⽅が良いです IBUFOBJOUFSO !!"
120. ࢀߟϦϯΫ (2/2) Go ⾔語からデータベースを扱う⽅法 • Accessing databases - The Go Programming Language • プログラミング⾔語から扱う⼀例として Go のドキュメントを紹 介します • このドキュメント⾃体は Go のドキュメントですが、プログラム から SQL を扱うことについてのチュートリアルから⼀般的な話 題、注意点などに触れられます IBUFOBJOUFSO !"#