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

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

Transcript

1. RDBMS ブートキャンプ #hatenaintern)*)+
2. はじめに ブートキャンプとは • 後半パートで困らないことを⽬的に • 概念からではなく、具体例やテクニック中⼼に • 結構駆け⾜気味なので、後で調べるとっかかりの⼀つにしても らえると幸いです #hatenaintern)*)+
3. はじめに • はてなでは主に MySQL と PostgreSQL という DBMS をよく使 っています • 後半パートの機能開発でこれらを使う際に、0 から調べること がないように基礎的な部分を説明します #hatenaintern)*)+
4. このブートキャンプのゴール MySQL や PostgreSQL をなんとなく扱えるようになる • データを格納出来るようになる • データを保存‧参照‧更新‧削除が出来る • テーブル設計の考え⽅とパフォーマンスがなんとなくわかる #hatenaintern)*)+
5. この講義で扱えないこと • データベースの内部の仕組み • 詳しいテーブル設計や SQL のテクニック • パフォーマンスの詳しい話 • MySQL と PostgreSQL 以外のこと #hatenaintern)*)+
6. ⽬次 • RDBMS について • 基本編 • 応⽤編 • 付録 #hatenaintern)*)+
7. RDBMS について #hatenaintern)*)+
8. RDBMS とは RDBMS = Relational Database Management System • Relational Database • 関係データベース • Database Management System • データベース管理システム 関係データベースのデータベース管理システム #hatenaintern)*)+
9. 関係データベース 関係モデルに基づいたデータベース 関係モデルとは？ → データを関係として表現し取り扱うデータの表現⽅法 関係データベースでは、 ⾒た⽬としては表（テーブル）の形式でデータを表現する #hatenaintern)*)+
10. 関係データベース #hatenaintern)*)+
11. 関係データベース 関係データベース以外のデータベースシステムもあるが、この講義では扱わない 例えばこういうものがあります: #hatenaintern)*)+
12. データベース管理システム データベース管理システム（DBMS）の役割 • データを抽象化し、効率よく保存 • ⽤途にあわせて最適な構造でデータを保存する • 外部からの指⽰を受け付けてデータを操作する、インターフェースの役割 • データを使う⼈はデータがどのように格納されているか意識しなくてもいい • データを堅牢に扱う仕組みを提供する • 整合性を保つ • 並列アクセスされても齟齬が起きないようにする • データの損失を防ぐ #hatenaintern)*)+
13. 基本編 #hatenaintern)*)+
14. ⽤語 ⽤語 意味 テーブル データの集合 レコード テーブルのデータの組 1 ⾏ 1 ⾏ カラム テーブルのデータの属性 スキーマ テーブルの構造 （どんなカラムがあるか、カラムにはど んな値が⼊るか、など） #hatenaintern)*)+
15. ⽤語 #hatenaintern)*)+
16. SQL RDBMS に問い合わせ（操作）を⾏うための⾔語 • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など • DCL (Data Control Language) • データへのアクセス制御に関する SQL • この講義では紹介しない SQL で書かれた、データベースへの問い合わせ⽂を、「クエリ」と呼ぶ #hatenaintern)*)+
17. SQL SQL には規格があるが、それにどこまで準拠しているかは DBMS によってまちまち DBMS はそれぞれ独⾃拡張（⽅⾔）を持っていて、互換性がない こともある このブートキャンプでも、「MySQL ではこの書き⽅ができます」 のような話が出てきたら⽅⾔のことだと思ってください #hatenaintern)*)+
18. データ定義 • DDL (Data Definition Language) ← これ • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など #hatenaintern)*)+
19. 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)*)+
20. CREATE TABLE ⽂ id BIGINT NOT NULL, • BIGINT の部分: カラムのデータ型 • NOT NULL の部分: カラムの制約 #hatenaintern)*)+
21. データ型 #hatenaintern)*)+
22. データ型 • 型によって保存に使われる容量が異なるので⼤事 • 後から変えるのも⼤変なことが多い • よく使うものについて紹介 • 数値型 • ⽂字列型 • ⽇付と時間型 #hatenaintern)*)+
23. 数値型 • 整数 • TINYINT, INTEGER, BIGINT など • 右に⾏くほど多くの桁を保存できる • MySQL では UNSIGNED 属性を付与すると符号無しで保存できるようになる（BIGINT UNSIGNED） • 属性: カラムの特性。つけられる属性は、データ型によって異なる • 浮動⼩数点 • FLOAT, DOUBLE • 固定⼩数点 • DECIMAL, NUMERIC #hatenaintern)*)+
24. 数値型 • 桁あふれに気をつける必要がある • レコードごとにユニークにつける id などは BIGINT UNSIGNED にしておくと安⼼ • 18446744073709551615（1844京）まで保存できる #hatenaintern)*)+
25. ⽂字列型 • 多くの場合で VARCHAR(n) を使う • 可変⻑⽂字列で 0 〜 65,535 バイト保存可能 • VARCHAR(32) のように、保存する最⼤⽂字数を指定する • ある程度⻑い⽂字列を保存するときにはテキスト型を利⽤ • MySQL では TEXT, MEDIUMTEXT, LONGTEXT など • PostgreSQL には TEXT だけ⽤意されている #hatenaintern)*)+
26. ⽇付と時間型 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)*)+
27. ⽇付と時間型 PostgreSQL では、タイムゾーンの有無は ↓ のように区別する • TIMESTAMP WITHOUT TIME ZONE • TIMESTAMP WITH TIME ZONE #hatenaintern)*)+
28. ⽇付と時間型 ⽇付と時間の両⽅を含んではいない型もある （登場頻度は少ない） • DATE: ⽇付のみ • TIME: 時間のみ #hatenaintern)*)+
29. 制約 #hatenaintern)*)+
30. 制約 • カラムに⼊るデータに対する制限 • 意図しないデータが⼊り込まないように • 制約に反するとエラーになる #hatenaintern)*)+
31. 制約 例: 「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)*)+
32. 制約の例 • NOT NULL制約: カラムは NULL であってはいけない • NULL は、値が⽋損していることを表す • UNIQUE 制約: カラムの値がテーブル内で⼀意でなければならない • ただし NULL はいくつあっても許されるので注意 • PRIMARY KEY 制約: カラムの値がテーブル内でレコードを⼀意に識別できなけれ ばいけない • FOREIGN KEY 制約: 紐づいているテーブル間で整合性が取れていなければいけな い #hatenaintern)*)+
33. PRIMARY KEY (主キー) • テーブル内でレコードを⼀意に識別できるカラム • 1つのテーブルで1つ指定できる • PRIMARY KEY 制約は、UNIQUE 制約と NOT NULL 制約を併せ 持っているといえる • PRIMARY KEYはインデックス（後述）としても使える #hatenaintern)*)+
34. 改めて 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)*)+
35. データ操作 • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) ← これ • データを操作する SQL • 検索、更新など #hatenaintern)*)+
36. CRUD • データ操作する際の基本機能 • それぞれの操作の頭⽂字から • Create (追加) • Read (参照) • Update (更新) • Delete (削除) #hatenaintern)*)+
37. 以降は以下のテーブルがある前提で ユーザーと、ユーザーが所有するブログを管理している状態 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)*)+
38. 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)*)+
39. 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)*)+
40. 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)*)+
41. 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)*)+
42. 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)*)+
43. データの取得⽅法 ⽬的 ⼿段 条件をつけて絞り込み WHERE 句 ソート ORDER BY 句 取得件数の制限 LIMIT 句 ページネーション LIMIT 句と OFFSET 句を組み合わせる グルーピング GROUP BY 句 テーブル同⼠の結合 JOIN 句 #hatenaintern)*)+
44. WHERE 句 条件をつけて絞り込む SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; #hatenaintern)*)+
45. ⽐較 「等しい」は =、「等しくない」は !"（!# とも書かれる） プログラミング⾔語の数値や⽂字列のように >, <, !$, !% で⽐較できる !" 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)*)+
46. ⽐較 • 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)*)+
47. 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)*)+
48. ページネーション (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)*)+
49. ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+
50. ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+
51. ページネーション (LIMIT 句 / OFFSET 句) #hatenaintern)*)+
52. ページネーション ただし、LIMIT/OFFSET を使ったページネーションには以下のようなデメリットがある • パフォーマンス • OFFSET で指定された⾏数まで辿り着くまでに全ての⾏数を数えなければいけない • データがずれる • ページネーションをしている途中に⾏が差し込まれたり削除されたりしたら、ずれてしまう • 例: 1ページ⽬(1〜10件⽬)をクエリ → 1件⽬のデータが削除される → 2ページ⽬(11〜20件 ⽬)をクエリ → もともとの11件⽬がどちらのページにも現れない これらのデメリットを避けるために、次の⽅法がよく使われる #hatenaintern)*)+
53. ページネーション 前ページの最終⾏の値を、ページの区切りのキーとして使う Keyset Pagination と呼ばれている 例: 1ページ⽬は id = - のレコードが最後だったので、2ページ⽬を取得するときに は id > - のものを取得する SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ メリット • パフォーマンス • インデックス（後述）があれば、現在のページの⾏まで素早く⾶べる • データのずれ • 起こらない デメリット • 任意のページまで⾶べない • 1ページ⽬から10ページ⽬に⾏く、というのは不可能 • 前後ページへのナビゲーションを出そうとすると⼯夫がいる • ページの前後にデータが存在するか確認するクエリも必要になる • 単調増加するカラムが必要になる • 単調増加するカラムを指定するか AND で組み合わせる必要がある #hatenaintern)*)+
54. グルーピング (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)*)+
55. グルーピング (GROUP BY 句) #hatenaintern)*)+
56. テーブル同⼠の結合 (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)*)+
57. テーブル同⼠の結合 (JOIN 句) #hatenaintern)*)+
58. テーブル同⼠の結合 (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)*)+
59. 応⽤編 #hatenaintern)*)+
60. 応⽤編 これらの話題を少しずつだけ紹介 • テーブル設計 • パフォーマンスの話 #hatenaintern)*)+
61. テーブル設計 #hatenaintern)*)+
62. テーブル設計 ブログサービスを例に考えてみる • ユーザーがいる • ユーザーは名前を持つ • ユーザーはブログを持てる • ブログにはタイトルが付く • ブログには説明⽂が書ける • ユーザーはブログを複数持てる どんなテーブル(表)で表せるか？ #hatenaintern)*)+
63. テーブル設計 - ユーザーとブログ 全部⼀つのテーブル(表)で書いてみる #hatenaintern)*)+
64. テーブル設計 - ユーザーとブログ これでも可能ではあるけど... • ユーザー名の情報が重複している • 間違えて⼀部だけ更新してしまったら整合性がなくなってしまう • シンプルなことをしたいだけなのに複雑なクエリを書く場所ができてしまう • 例: ユーザー名⼀覧を取得するためには SELECT DISTINCT user_name FROM blogs;というクエリになる • 扱う情報が増えたらどうする？ • 例: ブログに記事を複数持たせたい • 記事カラムを追加したらこのテーブルでも実現できるが、記事の数だけレコードが作られるので、その分ユー ザー‧ブログのデータの重複が増える #hatenaintern)*)+
65. テーブル設計 - ユーザーとブログ 扱う情報に着⽬してテーブルを分割 • 「ユーザー」と「ブログ」に着⽬して、それぞれをテーブルで 表現する • users テーブルと blogs テーブルに分割する • それぞれのテーブルに、⾏を⼀意に特定できるキーを追加する • そのキーを使って、テーブル同⼠が関連を持てるようにする #hatenaintern)*)+
66. テーブル設計 - ユーザーとブログ テーブルを分割する #hatenaintern)*)+
67. テーブル設計 - ユーザーとブログ テーブルに⾏を特定できるキーを追加する #hatenaintern)*)+
68. テーブル設計 - ユーザーとブログ そのキーを使ってテーブル同⼠に関連を持たせる #hatenaintern)*)+
69. テーブル設計 - ユーザーとブログ users と blogs は⼀対多の関係 #hatenaintern)*)+
70. テーブル設計 - ユーザーとブログ • 多のテーブルに、⼀のテーブルのレコードを⼀意に特定するカ ラムの値を持たせれば、⼀対多の関係を表せる • 着⽬する情報の関係を図にしてみると捉えやすい • ER図 - Google 検索 #hatenaintern)*)+
71. テーブル設計 - ユーザーとブログ 完成形 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)*)+
72. テーブル設計 - ユーザーとブログ ユーザー名とブログ名は別のテーブルに格納されるようになった ので、それらのデータを⼀気に取得するには 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)*)+
73. テーブル設計 - 購読機能 ここで購読機能を追加することを考えてみる • ユーザーはブログを購読できる • 例: ユーザー「はてな太郎」は、「⼆郎の⽇記」「⼆郎の技術 ブログ」のブログを購読できる • SNS でいう「フォロー」機能 • これをテーブル構造で表すには？ #hatenaintern)*)+
74. テーブル設計 - 購読機能 users と購読対象の blogs は多対多の関係 #hatenaintern)*)+
75. テーブル設計 - 購読機能 • blogs に subscribe_user_id（このブログを購読している ユーザー ID）のようなカラムを⾜すと？ • blogs.subscribe_user_id は1つしか値を格納できない • → 複数⼈が同じブログを購読している状態を表現できない • 多対多の関係は、どちらかのテーブルにカラムを追加するだけ では表現できない #hatenaintern)*)+
76. テーブル設計 - 購読機能 • users と blogs の購読の関係を表すテーブルを作る • subscriptions テーブル • subscriptions から users と blogs のレコードを特定できるように、user_id と blog_id カラムを持つ • この例に限らず、多対多の関係を表すには、関係を表すテーブルを作ってみるといい #hatenaintern)*)+
77. テーブル設計 - 購読機能 完成形 CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); #hatenaintern)*)+
78. テーブル設計 - 購読機能 「はてな太郎」という名前のユーザーが購読しているブログ⼀覧を取得するには、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)*)+
79. テーブル設計 まとめ • 扱う情報に着⽬してテーブルを考えてみる • テーブル同⼠の関係は図にしてみるとわかりやすい • 関係⾃体をテーブルで表すとすっきりすることもある #hatenaintern)*)+
80. パフォーマンス #hatenaintern)*)+
81. パフォーマンス DB は Web サービスを開発‧運営する上でパフォーマンスの問題に繋がりやすい • サービスが続く限りデータは増加し続ける • 最初は問題なかったクエリが、データ量が増えたことで実⾏に時間がかかるようになっ てしまったり • DB のパフォーマンスの問題はサービスの品質低下に繋がる • クエリの実⾏に時間がかかる • → ユーザに返すレスポンスが遅くなる • → ユーザが離れてしまう #hatenaintern)*)+
82. パフォーマンス パフォーマンスに関して、以下の2つのものを紹介します • インデックス • EXPLAIN #hatenaintern)*)+
83. インデックス テーブルのレコードを⾼速に検索するための仕組み • レコードを検索する際、インデックスがなければ、先頭⾏からテーブル全体を読み取り対象のレコードを⾒ 付けだす • 例: SELECT * FROM users WHERE id = 100000; なら、テーブルの先頭から1件ずつ⾒ていって id が 100000 に⼀致するものを探す • インデックスはテーブルを検索するために最適なデータ構造で保存される • よく使われるのは、B-Tree というデータ構造 • 検索対象のカラムがインデックスを持つカラムだと、全てのレコードを調べずに検索ができる • インデックスが定義されていれば、テーブルにデータを挿⼊した際に、⾃動でインデックスにもデータが追 加される #hatenaintern)*)+
84. インデックス • 明⽰的に作る⽅法 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)*)+
85. インデックス どういうときにインデックスを作るか？ • インデックス作成にはコストがかかる（後述）ため、無闇に作ればいいわけではない • アプリケーションで実⾏するクエリをイメージして、有効に使われそうなインデックス を考える • 例: 「あるユーザーのブログ⼀覧を取得する」 • クエリ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id を元に検索するので user_id にインデックスがあると⾼速に検索でき そう #hatenaintern)*)+
86. インデックス インデックスの注意点 • インデックス作成にはコストがかかる • レコードの作成‧更新‧削除時にインデックスも更新するので、オーバーヘッドがある • 多くのアプリケーションでは ࢀরॲཧ > ߋ৽ॲཧ になるのであまり問題ない • レコードとは別にインデックスを格納するためのディスク領域も別途必要になる • インデックスが使われないケースもある • 例: 全件探索の⽅が早い • 都道府県を表すテーブルだと、47レコードしかないので全件探索した⽅が早い #hatenaintern)*)+
87. EXPLAIN( • クエリの実⾏計画を出⼒する⽅法 • 実⾏計画: クエリにどのようなインデックスを使⽤し、どんな順番でテー ブルを結合するのか、など • 実⾏計画がわかれば、どの部分がパフォーマンスを悪くしているかを知るこ とができる • クエリの先頭に EXPLAIN を付けることで、そのクエリの実⾏計画を得るこ とができる ! MySQL :: MySQL ).+ リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化 PostgreSQL: Documentation: VW: EXPLAIN #hatenaintern)*)+
88. 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) #hatenaintern)*)+
89. 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) #hatenaintern)*)+
90. 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)*)+
91. パフォーマンス 「推測するな、計測せよ」という格⾔があります • パフォーマンス対策をするときは勘で対処してはいけない • きちんと計測し、ボトルネックを把握して対処していく必要が ある #hatenaintern)*)+
92. 終わりに #hatenaintern)*)+
93. 終わりに • 1時間お疲れ様でした • 概念‧SQL‧テーブル設計‧パフォーマンスに少しずつ触れまし たが、基礎的なトピックを全て扱えているわけではないです • 知識や理論については、空いた時間やインターンが終わった後に ⾒てみてください • 実サービスのデータ量を扱う経験は、後半パートで体験してもら えると思います #hatenaintern)*)+
94. 終わりに 扱えなかったトピックやキーワードについて列挙しておくので、興味があれば調べてみてください • SQL について • 複雑なクエリや集計 (サブクエリ) • 組み込み関数 • JOIN の種類とアルゴリズム • テーブル設計 • 正規化 • トランザクション • ACID 特性 • ロック • MVCC • プログラミング⾔語から SQL を扱う (付録に参考リンク記載) • N+@ 問題 • セキュリティ • SQL インジェクション #hatenaintern)*)+
95. 付録 お⼿元で⼿軽に SQL のクエリを試してもらえるよう、Docker で データベースを起動してその中に⼊る⽅法を書きました #hatenaintern)*)+
96. 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)*)+
97. Docker を使って MySQL を操作する (2/3) 注意点 • root ユーザーでは Docker コンテナ内からでないと⼊れません • → 権限管理の関係 • 公式のイメージだと mysql client から⼊ると⽇本語が⼊⼒できない‧出⼒ されない • OS に⽇本語設定が⼊っていないため • 以下の⽅法で⽇本語⼊⼒できるようになる #hatenaintern)*)+
98. 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)*)+
99. MySQL でよく使うコマンド !" ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; !" ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; !" MySQL ͔Βग़Δ exit #hatenaintern)*)+
100. 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)*)+
101. PostgreSQL でよく使うコマンド !" ςʔϒϧҰཡΛදࣔ͢Δ \dt !" ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ \d table_name !" PostgreSQL ͔Βग़Δ exit #hatenaintern)*)+
102. 参考リンク (1/2) RDBMS 全般 • データベース概論Ⅰ | 筑波⼤学オープンコースウェア｜TSUKUBA OCW • ある程度 DB を扱うことに慣れてきてから⾒てみると、理論と実践が合わさってよいと思う リファレンス • MySQL (.* リファレンスマニュアル • PostgreSQL: Documentation: EF: PostgreSQL EF.G Documentation • リファレンスなので、困ったときに辞書的に使うと良いです • 実際に使うバージョンに合ったマニュアルを参照した⽅が良いです #hatenaintern)*)+
103. 参考リンク (2/2) Go ⾔語からデータベースを扱う⽅法 • Accessing databases - The Go Programming Language • プログラミング⾔語から扱う⼀例として Go のドキュメントを紹 介します • このドキュメント⾃体は Go のドキュメントですが、プログラム から SQL を扱うことについてのチュートリアルから⼀般的な話 題、注意点などに触れられます #hatenaintern)*)+