はてなサマーインターンシップ2025 RDBMSの基礎 講義資料

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

Transcript

1. RDBMSͷجૅ IBUFOBJOUFSO !
2. ΰʔϧ RDBMS(リレーショナルデータベース)の基礎が⾝につく • RDBMSの役割や必要性がわかる • データの保存‧参照‧更新‧削除ができる • テーブル設計時に気にすることが分かる • トランザクションの必要性‧気をつけることがわかる • インデックスについてなんとなくわかる IBUFOBJOUFSO !
3. ͜ͷߨٛͰѻΘͳ͍͜ͱ • データベースの内部の仕組み • 詳しいテーブル設計や SQL のテクニック • パフォーマンスの詳しい話 IBUFOBJOUFSO !
4. ࣭໰ • RDBMS 知っている⼈ﾉｼ IBUFOBJOUFSO !
5. ࣭໰ • 触り慣れてる⼈ﾉｼ IBUFOBJOUFSO !
6. ໨࣍ • RDBMS について • SQL • テーブル設計 • トランザクション • パフォーマンス • 付録 IBUFOBJOUFSO !
7. RDBMS ʹ͍ͭͯ IBUFOBJOUFSO !
8. RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational Database • 関係データベース • Database Management System • データベース管理システム 関係データベースのデータベース管理システム IBUFOBJOUFSO !
9. ؔ܎σʔλϕʔε 関係モデルに基づいたデータベース 関係モデルとは？ → データを関係として表現し取り扱うデータの表現⽅法 関係データベースでは、 ⾒た⽬としては表（テーブル）の形式でデータを表現する ! リレーション≒表 です。% 表の間の参照ではなく、表そのもの ! 漢(オトコ)のコンピュータ道: RDBMSに関する典型的な誤解が絶えないという現実 IBUFOBJOUFSO !
10. ؔ܎σʔλϕʔε IBUFOBJOUFSO !"
11. ؔ܎σʔλϕʔε 関係データベース以外のデータベースシステムもあるが、この講義では扱わない 例えばこういうものがあります: IBUFOBJOUFSO !!
12. σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • 外部からの指⽰を受け付けてデータを操作する、インターフェースの役割 • データを使う⼈はデータがどのように格納されているか意識しなくてもいい • データを抽象化し、効率よく保存、取り出せる • ⽤途にあわせて最適な構造でデータを保存する • データを堅牢に扱う仕組みを提供する • 整合性を保つ • 並列アクセスされても齟齬が起きないようにする • データの損失を防ぐ IBUFOBJOUFSO !"
13. σʔλϕʔε؅ཧγεςϜ (DBMS) ͷ໾ׂ • csv として保存するのと何が違うのかを考えると分かりやすい • SQL で扱うことは可能 • CRUD はできる • 効率良く取り出すには不⾜ • 頭から舐める以外のアクセス⽅法が無い • 堅牢性もおそらく不⾜ • 複数⼈で扱うと壊れがち IBUFOBJOUFSO !"
14. SQL ͷ͓͞Β͍ IBUFOBJOUFSO !"
15. ༻ޠ ⽤語 意味 テーブル データの集合 レコード テーブルのデータの組 1 ⾏ 1 ⾏ カラム テーブルのデータの属性 スキーマ テーブルの構造 （どんなカラムがあるか、カラムにはど んな値が⼊るか、など） IBUFOBJOUFSO !"
16. ༻ޠ IBUFOBJOUFSO !"
17. SQL RDBMS に問い合わせ（操作）を⾏うための⾔語 • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など • DCL (Data Control Language) • データへのアクセス制御に関する SQL • この講義では紹介しない SQL で書かれた、データベースへの問い合わせ⽂を、「クエリ」と呼ぶ IBUFOBJOUFSO !"
18. SQL SQL には規格があるが、それにどこまで準拠しているかは DBMS によってまちまち DBMS はそれぞれ独⾃拡張（⽅⾔）を持っていて、互換性がない こともある IBUFOBJOUFSO !"
19. σʔλఆٛ • DDL (Data Definition Language) ← これ • スキーマを定義する SQL • DML (Data Manipulation Language) • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"
20. CREATE TABLE จ • テーブルを定義する構⽂ • CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ); -- `--` ͸ίϝϯτ -- id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ -- users ͱ͍͏໊લͰఆٛ CREATE TABLE `users` ( `id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(32) NOT NULL, `created_at` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ); IBUFOBJOUFSO !"
21. CREATE TABLE จ `id` BIGINT UNSIGNED NOT NULL, • `id` の部分: カラムの名前 • BIGINT UNSIGNED の部分: カラムのデータ型 • NOT NULL の部分: カラムの制約 IBUFOBJOUFSO !"
22. ੍໿ • カラムに⼊るデータに対する制限 • 意図しないデータが⼊り込まないように • 制約に反するとエラーになる IBUFOBJOUFSO !!
23. ੍໿ͷྫ • NOT NULL制約: カラムは NULL であってはいけない • NULL は、値が⽋損していることを表す • UNIQUE 制約: カラムの値がテーブル内で⼀意でなければならない • ただし NULL はいくつあっても許されるので注意 • PRIMARY KEY 制約: カラムの値がテーブル内でレコードを⼀意に 識別できなければいけない IBUFOBJOUFSO !"
24. ੍໿͕͋Δͱ͖ 例: 「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 !"
25. PRIMARY KEY (ओΩʔ) • テーブル内でレコードを⼀意に識別できるカラム • 1つのテーブルで1つ指定できる • PRIMARY KEY 制約は、UNIQUE 制約と NOT NULL 制約を併せ持っている といえる • 基本的に作るのが無難 • 作らないとレコード数が増えたときに、特定するのに時間がかかる IBUFOBJOUFSO !"
26. վΊͯ users ςʔϒϧͷఆٛΛݟΔ CREATE TABLE `users` ( `id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(32) NOT NULL, `created_at` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ); users という名前のテーブルを定義し、以下のカ ラムを持たせる • id • "#bitの⾮負整数の値が⼊り、この値によっ てテーブル内のレコードを⼀意に識別できる • name • 最⼤32⽂字の⽂字列の値が⼊り、NULL でな いかつデータが重複することがない • created_at • ⽇時の値が⼊り、NULL ではない IBUFOBJOUFSO !"
27. σʔλૢ࡞ • DDL (Data Definition Language) • スキーマを定義する SQL • DML (Data Manipulation Language) ← これ • データを操作する SQL • 検索、更新など IBUFOBJOUFSO !"
28. CRUD • データ操作する際の基本機能 • それぞれの操作の頭⽂字から • Create (追加) • Read (参照) • Update (更新) • Delete (削除) IBUFOBJOUFSO !"
29. Ҏ߱͸ҎԼͷςʔϒϧ͕͋ΔલఏͰ ユーザーと、ユーザーが所有するブログを管理している状態 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 UNSIGNED NOT NULL, -- ϒϩάΛ͍࣋ͬͯΔϢʔβʔͷ id `user_id` BIGINT UNSIGNED NOT NULL, `title` VARCHAR(50) NOT NULL, `description` VARCHAR(512), PRIMARY KEY (`id`) ); IBUFOBJOUFSO !"
30. INSERT จ (Create) テーブルにレコードを追加する INSERT ⽂ INSERT INTO users (id, name, created_at) VALUES (1, '͸ͯͳଠ࿠', '2024-08-19 16:30:00'); Query OK, 1 row affected (0.01 sec) IBUFOBJOUFSO !"
31. SELECT จ (Read) テーブルからレコードを検索する SELECT ⽂ -- users ςʔϒϧͷϨίʔυΛશ݅ݕࡧʢऔಘʣ SELECT * FROM users; * は「全てのカラム」という意味。* の代わりに個別のカラム名を指定することもできる +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2024-08-19 16:30:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) IBUFOBJOUFSO !"
32. 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 !"
33. UPDATE จ (Update) 既に存在するレコードを更新する 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 を押しても安全 IBUFOBJOUFSO !!
34. DELETE จ (Delete) レコードを削除する DELETE FROM users WHERE id = 1; Query OK, 1 row affected (0.01 sec) -- ஫ҙ! WHERE ۟Ͱ࡟আର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅࡟আ͞ΕΔ DELETE FROM users; SELECT ⽂として書いて、対象データを確認してから SELECT -> DELETE に書き換えるのがエンジニアしぐさ IBUFOBJOUFSO !"
35. WHERE ۟ 条件をつけて絞り込む SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; IBUFOBJOUFSO !"
36. ൺֱ • 「等しい」は =、「等しくない」は !=（<> とも書かれる） • プログラミング⾔語の数値や⽂字列のように >, <, >=, <= で⽐較できる • そのほかの演算⼦はWikipedia!を参照してください -- 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 と書くこと。 ! WHERE (SQL) - Wikipedia IBUFOBJOUFSO !"
37. ORDER BY ۟ • ORDER BY [ΧϥϜ໊/ྻͷॱ ൪] [ASC/DESC]: 検索結果の ソートが出来る • ASC は昇順、DESC は降順 • WHERE 句と組み合わせるとき は、WHERE 句を先に書かない と動かない -- user ςʔϒϧͷϨίʔυΛ created_at ͷ߱ॱͰݕࡧ SELECT * FROM users ORDER BY created_at DESC; -- created_at͸3൪໨ͳͷͰ 3 Λࢦఆ͢Δͱcreated_atͰιʔτ͞ΕΔɻ -- ͨͩ͠ɺ* ͷ৔߹͸ͲͷΧϥϜ͕Կ൪໨ͳͷ͔Θ͔Γʹ͍͘ɻ -- ͦͷͨΊɺྻͷॱ൪Ͱͷࢦఆ͸ྻΛࢦఆ͢Δͱ͖ʹ࢖͏͜ͱ͕ଟ͍ɻ SELECT * FROM users ORDER BY 3 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 !"
38. άϧʔϐϯά (GROUP BY ۟) • GROUP BY 句: データをグルーピングする • SELECT で指定できるのは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 !"
39. άϧʔϐϯά (GROUP BY ۟) IBUFOBJOUFSO !"
40. ςʔϒϧಉ࢜ͷ݁߹ (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 !"
41. ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) IBUFOBJOUFSO !"
42. ςʔϒϧಉ࢜ͷ݁߹ (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 !"
43. ςʔϒϧઃܭ IBUFOBJOUFSO !"
44. ςʔϒϧઃܭ ブログサービスを例に考えてみる • ユーザーがいる • ユーザーは名前を持つ • ユーザーはブログを持てる • ブログにはタイトルが付く • ブログには説明⽂が書ける • ユーザーはブログを複数持てる どんなテーブル(表)で表せるか？ IBUFOBJOUFSO !!
45. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 全部⼀つのテーブル(表)で書いてみる IBUFOBJOUFSO !"
46. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά これでも可能ではあるけど... • ユーザー名の情報が重複している • 間違えて⼀部だけ更新してしまったら整合性がなくなってしまう • シンプルなことをしたいだけなのに複雑なクエリを書く場所ができてしまう • 例: ユーザー名⼀覧を取得するためには SELECT DISTINCT user_name FROM blogs;というクエリになる • 扱う情報が増えたらどうする？ • 例: ブログに記事を複数持たせたい • 記事カラムを追加したらこのテーブルでも実現できるが、記事の数だけレコードが作られるので、そ の分ユーザー‧ブログのデータの重複が増える IBUFOBJOUFSO !"
47. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 扱う情報に着⽬してテーブルを分割(正規化と呼ばれる) • 「ユーザー」と「ブログ」に着⽬して、それぞれをテーブルで 表現する • users テーブルと blogs テーブルに分割する • それぞれのテーブルに、⾏を⼀意に特定できるキーを追加する • そのキーを使って、テーブル同⼠が関連を持てるようにする IBUFOBJOUFSO !"
48. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルを分割する IBUFOBJOUFSO !"
49. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά テーブルに⾏を特定できるキーを追加する IBUFOBJOUFSO !"
50. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά そのキーを使ってテーブル同⼠に関連を持たせる IBUFOBJOUFSO !"
51. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά users と blogs は⼀対多の関係 IBUFOBJOUFSO !"
52. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά • 多のテーブルに、⼀のテーブルのレコードを⼀意に特定するカ ラムの値を持たせれば、⼀対多の関係を表せる • 着⽬する情報の関係を図にしてみると捉えやすい • ER図 - Google 検索 IBUFOBJOUFSO !"
53. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά 完成形 CREATE TABLE users ( `id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(32) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `blogs` ( `id` BIGINT UNSIGNED NOT NULL, `user_id` BIGINT UNSIGNED NOT NULL, `title` VARCHAR(254) NOT NULL, `description` VARCHAR(512) NOT NULL, PRIMARY KEY (`id`) ); IBUFOBJOUFSO !"
54. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ユーザー名とブログ名は別のテーブルに格納されるようになった ので、それらのデータを⼀気に取得するには 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 !"
55. ςʔϒϧઃܭ - ߪಡػೳ ここで購読機能を追加することを考えてみる • ユーザーはブログを購読できる • 例: ユーザー「はてな太郎」は、「⼆郎の⽇記」「⼆郎の技術 ブログ」のブログを購読できる • SNS でいう「フォロー」機能 • これをテーブル構造で表すには？ IBUFOBJOUFSO !!
56. ςʔϒϧઃܭ - ߪಡػೳ users と購読対象の blogs は多対多の関係 IBUFOBJOUFSO !"
57. ςʔϒϧઃܭ - ߪಡػೳ • blogs に subscribe_user_id（このブログを購読しているユーザ ー ID）のようなカラムを⾜すと？ • blogs.subscribe_user_id は1つしか値を格納できない • → 複数⼈が同じブログを購読している状態を表現できない • 多対多の関係は、どちらかのテーブルにカラムを追加するだけ では表現できない IBUFOBJOUFSO !"
58. ςʔϒϧઃܭ - ߪಡػೳ • users と blogs の購読の関係を表すテーブルを作る • subscriptions テーブル • subscriptions から users と blogs のレコードを特定できるように、user_id と blog_id カラムを持つ • この例に限らず、多対多の関係を表すには、関係を表すテーブルを作ってみるといい IBUFOBJOUFSO !"
59. ςʔϒϧઃܭ - ߪಡػೳ 完成形 CREATE TABLE `subscriptions` ( `user_id` BIGINT UNSIGNED NOT NULL, `blog_id` BIGINT UNSIGNED NOT NULL, PRIMARY KEY (`user_id`, `blog_id`) ); IBUFOBJOUFSO !"
60. ςʔϒϧઃܭ - ߪಡػೳ 「はてな太郎」という名前のユーザーが購読しているブログ⼀覧を取得するには、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 !"
61. ·ͱΊ • 扱う情報に着⽬してテーブルを考えてみる • テーブル同⼠の関係は図にしてみるとわかりやすい • 関係⾃体をテーブルで表すとすっきりすることもある IBUFOBJOUFSO !"
62. ςʔϒϧઃܭͷצॴ • ユースケースから名詞と動詞を抽出する • ユーザーはブログを複数持つ • ユーザーはブログを購読する • 名詞はマスタエンティティに • 動詞は名詞化した上でイベントエンティティ に • マスタ • モノを表す • 名前を持つ • users、blogs • イベント • 出来事を表す • 必ず時刻を持つ • 名前を持たない • subscriptions IBUFOBJOUFSO !"
63. ςʔϒϧઃܭͷצॴ • マスタとイベントという分け⽅だが、隠れたリソースを探す⽬を持つと良い • ユーザーがインターンシップに参加する • 単なる多対多 (users_internships) ではなく、例えばオンライン参加かどうか、何経由で登録した か、をカラムとして持つことができる • 「参加登録(registrations)」というリソースが隠れていた • ユーザーフォローは followership というリソース • Web API 設計にも繋がる • CRUD は POST/GET/PUT/DELETE で表現できる • POST /:user_id/follow よりも POST /followerships を好む IBUFOBJOUFSO !"
64. τϥϯβΫγϣϯ IBUFOBJOUFSO !"
65. τϥϯβΫγϣϯͱ͸ • トランザクションとは、複数の検索や更新を含む処理からなる意 味的な単位 • 「意味」とは？ • アプリケーションを提供するうえで不整合が起きてほしくない 処理のまとまり • データベース視点ではどの処理をまとめて扱えばいいのかわから ないので教えてあげる必要がある IBUFOBJOUFSO !"
66. τϥϯβΫγϣϯ͕ͳ͍ͱࠔΔέʔε 例えば、ユーザのブログの最終投稿⽇時を記録する。この処理 は、以下の2つの処理からなる 1. ブログを投稿する 2. ユーザの最終投稿⽇時を更新する ブログの投稿に成功したあとに、ユーザの最終投稿⽇時の更新に 失敗した場合、最終投稿⽇時が更新されないままになってしまう IBUFOBJOUFSO !!
67. τϥϯβΫγϣϯ͕ຬͨ͢΂͖ಛੑ • トランザクションは信頼性のためにACID特性と いう4つの特性を満たすべき • Atomicity (原⼦性): トランザクション内の処理 は全て成功するか、全て失敗する • Consistency (⼀貫性): データベースの内容に⽭ 盾がないこと • Isolation (分離性): 複数のトランザクションを 同時実⾏した場合と順番に実⾏した場合で、結 果が同じになる • Durability (永続性): トランザクションが成功し た場合、その結果は停電などの障害が起きても 永続的に保存される IBUFOBJOUFSO !"
68. τϥϯβΫγϣϯͱಉ࣮࣌ߦ • ACID特性の Isolation を実現するのは実は難しい • 複数のトランザクションが同時に同じデータを扱うと、様々な問題が起きる • 例：同時に在庫を確認して購⼊処理をすると、在庫がマイナスになることも • 最も単純な解決策：全てのトランザクションを1つずつ順番に実⾏する（直列実⾏） • しかし、処理が終わるまで待たされるため、パフォーマンスが悪くなる • 現実的な解決策：同時実⾏を許可しつつ、直列っぽく動かす • トランザクション分離レベルによって直列っぽさの度合いを調整できる IBUFOBJOUFSO !"
69. τϥϯβΫγϣϯ෼཭Ϩϕϧ IBUFOBJOUFSO !"
70. μʔςΟϦʔυ 別トランザクションの未コミットの値を読み取る現象 T2 DB T1 T2 DB T1 BEGIN 1 UPDATE x = 1 %% ະίϛοτͷॻ͖ࠐΈ 2 BEGIN 3 READ x (1) %% μʔςΟʔϦʔυൃੜ 4 ROLLBACK. x ͷߋ৽͸औΓফ͞ΕΔ 5 COMMIT. ෆਖ਼ͳ஋Λ֬ఆͤͯ͞͠·͏ 6 IBUFOBJOUFSO !"
71. ϊϯϦϐʔλϒϧϦʔυ 繰り返し読み取ると、別トランザクションの更新or削除によって値が変わる現象 T2 DB T1 T2 DB T1 BEGIN 1 READ x (1) 2 BEGIN 3 UPDATE x = 2 %% ߋ৽͕ߦΘΕΔ 4 COMMIT. x ͷ஋͸ 2 ʹߋ৽͞ΕΔ 5 READ x (2) %% ϊϯϦϐʔλϒϧϦʔυൃੜ 6 IBUFOBJOUFSO !"
72. ϑΝϯτϜϦʔυ 繰り返し読み取ると、別トランザクションの挿⼊によって値が変わる現象 T2 DB T1 T2 DB T1 BEGIN 1 READ tabel x %% 1 ͷΈ 2 BEGIN 3 INSERT INTO table (x) VALUES (2) %% ৽͍͠஋͕ૠೖ͞ΕΔ 4 COMMIT. x ͷ஋͸ 1 ͱ 2 ʹ 5 READ x (1, 2) %% ϑΝϯτϜϦʔυൃੜ 6 IBUFOBJOUFSO !"
73. ॻ͖ࠐΈεΩϡʔ 読み取り -> 判断 -> 書き込みを終えたあとに、判断の部分の条件が真ではなくなってしまう現象 例として数量限定販売を考える T2 DB T1 T2 DB T1 BEGIN 1 READ stock (1) %% ࡏݿ͸ 1 ݸ 2 IF stock > 0 THEN stock = stock - 1 %% ϥε1ʂങ͏ͧʂ 3 BEGIN 4 READ stock (1) %% ࡏݿ͸ 1 ݸ 5 IF stock > 0 THEN stock = stock - 1 %% ؒʹ߹ͬͨʔʂങ͍·͢ʂ 6 COMMIT. ࡏݿ͸ 0 ݸʹߋ৽͞ΕΔ 7 COMMIT. ࡏݿ͸ -1 ݸʹߋ৽͞ΕΔ %% ॻ͖ࠐΈεΩϡʔൃੜɻൢചτϥϒϧʹ… 8 IBUFOBJOUFSO !"
74. SERIALIZABLEΛ࢖͏͔͠ͳ͍…ʁ IBUFOBJOUFSO !"
75. Կ͕໰୊ͳͷ͔ʁ 書き込みスキューが起きるパターン 1. 同じレコードに対して複数のトランザクションが同時に値を取 得する 2. その値を元に後続の処理をするかどうかを判断する 3. 1の結果が変化しうる書き込み処理を⾏う IBUFOBJOUFSO !"
76. Ͳ͏͢Ε͹ྑ͍ʁ • トランザクション単位で直列にするSERIALIZABLEはパフォー マンスの観点で避けたい • でも問題が起きるパターンは直列にする必要がある • -> トランザクション全体ではなくレコード単位で直列にできれ ばよい IBUFOBJOUFSO !"
77. Ͳ͏͢Ε͹ྑ͍ʁ 1. 同じレコードに対して複数のトランザクションが同時に値を取 得する ← ここで直列にできれば良い 2. その値を元に後続の処理をするかどうかを判断する 3. 1の結果が変化しうる書き込み処理を⾏う IBUFOBJOUFSO !!
78. ഉଞ੍ޚ • 排他制御は同じデータを複数⼈が同時に変更できないようにする仕組み • 別のトランザクションが同じデータを変更できないようにすることをロックという T2 DB T1 T2 DB T1 BEGIN 1 LOCK stock(1) %% ϩοΫΛऔಘ͢Δ 2 READ stock (1) %% ϩοΫΛऔಘͰ͖ͨͷͰಡΈऔΓ͕Ͱ͖Δ 3 IF stock > 0 THEN stock = stock - 1 %% ϥε1ʂങ͏ͧʂ 4 BEGIN 5 LOCK x %% ϩοΫΛऔಘͰ͖ͳ͍ͷͰ଴ػ͢Δ 6 COMMIT. ߪೖ੒ޭɻϩοΫΛղ์͢Δ 7 IF stock > 0 THEN stock = stock - 1 %% ചΓ੾Εͯͨ… ఘΊΑ͏ 8 ROLLBACK. ϩοΫΛղ์͢Δ 9 IBUFOBJOUFSO !"
79. ·ͱΊ • トランザクションは複数の処理を意味のあるまとまりとして扱 う仕組み • 複数⼈が同時に同じデータを読み書きすると、不整合が起きる • 性能のために並列実⾏したいが、整合性は保ちたい。このトレ ードオフを分離レベルやロックで調整する IBUFOBJOUFSO !"
80. ύϑΥʔϚϯε IBUFOBJOUFSO !"
81. ύϑΥʔϚϯε DB は Web サービスを開発‧運営する上でパフォーマンスの問題に繋がりやすい • サービスが続く限りデータは増加し続ける • 最初は問題なかったクエリが、データ量が増えたことで実⾏に時間がかかるようにな ってしまったり • DB のパフォーマンスの問題はサービスの品質低下に繋がる • クエリの実⾏に時間がかかる • → ユーザに返すレスポンスが遅くなる • → ユーザが離れてしまう IBUFOBJOUFSO !"
82. ΠϯσοΫε IBUFOBJOUFSO !"
83. ΠϯσοΫε • レコードを検索する際、インデックスがなけれ ば、先頭⾏からテーブル全体を読み取り対象の レコードを⾒つけ出す • 例: SELECT * FROM users WHERE id = 100000; なら、テーブルの先頭から1件ずつ⾒ていって id が 100000 に⼀致するものを探す • インデックスはテーブルを検索するために最適 なデータ構造で保存される • B Tree!がよく使われる。MySQL, PostgreSQL などの多くの RDBMS で採⽤されている ! RDBMSによってはB Treeではなく、B+ Treeなどの派⽣実装が使われることもある IBUFOBJOUFSO !"
84. B Tree • 特徴 • 平衡⽊である • 検索‧挿⼊‧更新‧削除のいずれも の時間計算量で⾏える • 葉ノードだけが実データに対するポ インタを持つ • B Treeの動きを視覚的に理解できるサ イトも覗いてみると⾯⽩いかも! 出典: 第7回 性能改善の鍵、インデック スの特性を知る〜B-treeとハッシュ  （A）B-tree | gihyo.jp ! https://www.cs.usfca.edu/~galles/visualization/BTree.html IBUFOBJOUFSO !"
85. ΠϯσοΫεΛ࡞Δ • 明⽰的に作る⽅法 CREATE INDEX index_name ON table_name (column_name); • 制約により⾃動で作られる • PRIMARY KEY • UNIQUE IBUFOBJOUFSO !"
86. Ͳ͏͍͏ͱ͖ʹΠϯσοΫεΛ࡞Δ͔ʁ • インデックス作成にはコストがかかる（後述）ため、無闇に作ればいいわけで はない • アプリケーションで実⾏するクエリをイメージして、有効に使われそうなインデ ックスを考える • 例: 「あるユーザーのブログ⼀覧を取得する」 • クエリ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id を元に検索するので user_id にインデックスがあると⾼速に検索で きそう IBUFOBJOUFSO !"
87. ΠϯσοΫεͷ஫ҙ఺ • インデックス作成にはコストがかかる • レコードの作成‧更新‧削除時にインデックスも更新するので、オーバーヘッドがあ る • 多くのアプリケーションでは ࢀরॲཧ > ߋ৽ॲཧ になるのであまり問題ない • レコードとは別にインデックスを格納するためのディスク領域も別途必要になる • インデックスが使われないケースもある • 例: 全件探索の⽅が早い • 都道府県を表すテーブルだと、47レコードしかないので全件探索した⽅が早い IBUFOBJOUFSO !"
88. ෳ߹ΠϯσοΫε • 複数の列を組み合わせて作るインデックス を複合インデックスという • 右図は [c>, c@] の複合インデックスのイメ ージ • c> が同じ場合に更に c@ で並び替える • c@ 単体を⾒るとまったくソートされていな いことに注意 • c@ のみでの絞り込みや並び替えには無⼒ • 必要なら [c@] でインデックスを張る id c$ c% & % $ ' % ' ( % ) $ & $ * & & % & ) IBUFOBJOUFSO !!
89. Α͋͘Δࣦഊ • 値の種類が少なく(カーディナリティが低い)、絞り込みがまった く効かない • 複合インデックスと単発インデックスを同時に張って役割が重 複する • name と [name, େֶ໊] • 後者で前者が満たせる IBUFOBJOUFSO !"
90. ·ͱΊ • インデックスは、テーブルの検索を⾼速化するための仕組み • 闇雲にインデックスを作るのではなく、アプリケーションでの使われ⽅に応 じてインデックスを考える もっと知りたかったらこの辺を参考にしてください • MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クッ クパッド開発者ブログ • MySQLのインデックスの貼っていいとき悪いときを原理から理解したいよ IBUFOBJOUFSO !"
91. ͕࣌ؒ༨͍ͬͯͨΒഉଞ੍ޚͷσϞ IBUFOBJOUFSO !"
92. ऴΘΓʹ IBUFOBJOUFSO !"
93. ऴΘΓʹ • 1時間お疲れ様でした • 概念‧SQL‧テーブル設計‧トランザクション‧インデックスに少しずつ触れ ましたが、基礎的なトピックを全て扱えているわけではないです • 知識や理論については、空いた時間やインターンが終わった後に⾒てみてくだ さい • 実サービスのデータ量を扱う経験は、後半パートで体験してもらえると思いま す • 数千万〜億単位のレコードを扱う経験は貴重なので楽しんでいってね IBUFOBJOUFSO !"
94. ऴΘΓʹ 扱えなかったトピックやキーワードについて列挙しておくので、興味があれば調べてみてください • SQL について • 複雑なクエリや集計 (サブクエリ) • 組み込み関数 • JOIN の種類とアルゴリズム • テーブル設計 • 正規化 • トランザクション • 悲観的ロック、楽観的ロック • 2相ロック • MVCC (Multi Version Concurrency Control) • SSI (Serializable Snapshot Isolation) • インデックス • カバリングインデックス • 排他と関連して • ギャップロック • ネクストキーロック • プログラミング⾔語から SQL を扱う (付録に参考リンク記載) • N+S 問題 • セキュリティ • SQL インジェクション IBUFOBJOUFSO !"
95. ෇࿥ IBUFOBJOUFSO !"
96. Ξϯνύλʔϯ IBUFOBJOUFSO !"
97. SQL Ξϯνύλʔϯ͔Βز͔ͭ঺հ • EAV • ナイーブツリー • ジェイウォーク IBUFOBJOUFSO !"
98. 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 !"
99. SQL Ξϯνύλʔϯ: EAV • STI、CCI、CTI で解決する • 詳しくは PoEAA を参照 IBUFOBJOUFSO !!
100. SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • ナイーブツリー • 親⼦関係があるものをナイーブ (素朴) に作ってしまう comment_id parent_id 発⾔者 コメント 4 NULL Fran このバグの原因は何かな？ D 4 Ollie ヌルポインターのせいじゃないかな？ Q D Fran そうじゃないよ。それは確認済みだ。 \ 4 Kukla 無効な⼊⼒を調べてみたら？ j \ Ollie そうか、バグの原因はそれだな。 l \ Fran よし、じゃあチェック機能を追加して もらえるかな？ z l Kukla 了解。修正したよ。 IBUFOBJOUFSO !""
101. SQL Ξϯνύλʔϯ: φΠʔϒπϦʔ • 以下のいずれかを使うことで解決する • 経路列挙モデル • ⼊れ⼦集合モデル • 閉包テーブル • WITH 句が使えるのでナイーブツリーのままでも良い IBUFOBJOUFSO !"!
102. SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • カンマ区切り等でデータを ⼊れる • VARCHAR で定義しているの で ID の⽂字数によって上限 が変わる • 外部キー制約が効かない user_id blog_ids , ,,. / / IBUFOBJOUFSO !"#
103. SQL Ξϯνύλʔϯ: δΣΠ΢ΥʔΫ • 中間テーブルを作ることで解決する • 絶対に検索しないなら使っても良い • が、9割⽅は設計ミスなので避けるべき • カラムに JSON や YAML を⼊れることは割とよくある • JSON 型!が存在している ! LONGTEXT かつ JSON のパースを保存時に⾏っているので効率的に扱える IBUFOBJOUFSO !"#
104. ΠϯσοΫε͕ޮ͍͍ͯΔ͔ௐࠪ͢Δ IBUFOBJOUFSO !"#
105. EXPLAIN6 • クエリの実⾏計画を出⼒する⽅法 • 実⾏計画: クエリにどのようなインデックスを使⽤し、どんな順番でテ ーブルを結合するのか、など • 実⾏計画がわかれば、どの部分がパフォーマンスを悪くしているかを知 ることができる • クエリの先頭に EXPLAIN を付けることで、そのクエリの実⾏計画を得る ことができる ! MySQL :: MySQL (.* リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化 IBUFOBJOUFSO !"#
106. 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 !"#
107. 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 !"#
108. 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 !"#
109. ΠϯσοΫεͷ༨ஊ 暗黙の型変換に注意 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 !"#
110. ύϑΥʔϚϯε 「推測するな、計測せよ」という格⾔があります • パフォーマンス対策をするときは勘で対処してはいけない • きちんと計測し、ボトルネックを把握して対処していく必要が ある IBUFOBJOUFSO !!"
111. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ IBUFOBJOUFSO !!!
112. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ お⼿元で⼿軽に SQL のクエリを試してもらえるよう、Docker で データベースを起動してその中に⼊る⽅法を書きました IBUFOBJOUFSO !!"
113. 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 !!"
114. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) 注意点 • root ユーザーでは Docker コンテナ内からでないと⼊れません • → 権限管理の関係 • 公式のイメージだと mysql client から⼊ると⽇本語が⼊⼒できない‧出⼒ されない • OS に⽇本語設定が⼊っていないため • 以下の⽅法で⽇本語⼊⼒できるようになる IBUFOBJOUFSO !!"
115. 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 !!"
116. MySQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; -- MySQL ͔Βग़Δ exit IBUFOBJOUFSO !!"
117. ࢀߟϦϯΫ (1/2) RDBMS 全般 • データベース概論Ⅰ | 筑波⼤学オープンコースウェア｜TSUKUBA OCW • ある程度 DB を扱うことに慣れてきてから⾒てみると、理論と実践が合わさってよいと思う リファレンス • MySQL (.* リファレンスマニュアル • PostgreSQL: Documentation: EF: PostgreSQL EF.G Documentation • リファレンスなので、困ったときに辞書的に使うと良いです • 実際に使うバージョンに合ったマニュアルを参照した⽅が良いです IBUFOBJOUFSO !!"
118. ࢀߟϦϯΫ (2/2) Go ⾔語からデータベースを扱う⽅法 • Accessing databases - The Go Programming Language • プログラミング⾔語から扱う⼀例として Go のドキュメントを紹介します • このドキュメント⾃体は Go のドキュメントですが、プログラムから SQL を扱うことについてのチュートリアルから⼀般的な話題、注意点などに触れられます ࢀߟॻ੶ • データ指向アプリケーションデザイン - O'Reilly Japan • P of EAA • SQLアンチパターン 第2版 - O'Reilly Japan IBUFOBJOUFSO !!"