DBについての基礎説明（MySQL編） - コロプラ 2026年度 新卒研修

コロプラの2026年度新卒研修で、エンジニア向けに実施した「DBについての基礎説明（MySQL編）」の資料です。
MySQL を題材に、RDB の基本や SQL、インデックス、トランザクションと排他制御、実行計画、スケーリングまで、データベースの基礎を一通り紹介しています。

Transcript

1. DBについての 基礎説明 （MySQL編） 技術基盤本部 第1バックエンドエンジニア部 第2グループ 2026.05.20 CONFIDENTIAL 1
2. データベース研修の目的 CONFIDENTIAL 2
3. データベース研修の目的 正式配属後の開発タスクを担当するにあたり、データベース（DB）に関する必要最 低限の知識習得を目指す データベースの基本的な操作方法を知ること • • • リレーショナルデータベースの構造と主要概念の理解 テーブル設計 基本的なデータ操作 今日学んだことを実務の参考情報になるようにする CONFIDENTIAL 3
4. データベースの基本概念 CONFIDENTIAL 4
5. データベースの基本概念 データベースとは 構造化されたデータの集合 膨大なデータを体系的に格納、検索、更新できるよう設計されたシステム DBMSとは データベースを管理し、外部からの要求に応えて操作を行うソフトウェア APP API CLI GUI USER / CLIENT DB DBMS CONFIDENTIAL 5
6. データベースの基本概念 OLTPとOLAP OLTP（オンライントランザクション処理）👈 リアルタイムトランザクション処理に焦点。高速クエリ、挿入、更新、削除など。 小規模トランザクションを多数迅速に処理。ECサイトの注文・決済、SNSの投稿・い いね、ゲームの課金などリアルタイム性が必要な用途 OLAP（オンライン分析処理） OLTP OLAP 集計・多次元分析に特化（複雑クエリ対応） 実例: 売上分析・行動分析・KPI集計 など レポート作成・意思決定支援（DWHに典型的） CONFIDENTIAL transaction analyze ELT 6
7. リレーショナルデータベース （RDB） CONFIDENTIAL 7
8. リレーショナルデータベース（RDB） RDBとは データを表形式で表し永続化できるDBMS。 広く利用されている。 リレーショナルモデルでは、共通属性を使用して任意のテーブルを別のテーブル に関連付け可能。 論理データ構造が物理記憶域構造と別になっている。 研修ではRDBの一つであるMySQLを例に説明する CONFIDENTIAL 8
9. リレーショナルデータベース（RDB） テーブル構造 データベースという器の中にテーブルがあり、テーブルは列（カラム）と行（レ コード）で構成される。 列 行 CONFIDENTIAL 9
10. リレーショナルデータベース（RDB） データ型 CONFIDENTIAL PHP DB(MySQL) 例 数値 int TINYINT SMALLINT MEDIUMINT INT BIGINT 1, 0, -1, 2147483647(INT max), 9223372036854775807(BIGINT max) 浮動小数 float FLOAT DOUBLE 0.1, 0.0001 真偽値 boolean TINYINT true, false → 1, 0 文字列 string CHAR VARCHAR TEXT ENUM SET ‘aaa’ 日時 Datetime Carbon DATE TIME DATETIME TIMESTAMP YEAR ‘2025-05-21 01:28:17’ JSON string JSON {"key1": "value1"} 10
11. リレーショナルデータベース（RDB） 行指向と列指向 行指向 行ごとのデータをブロック（＝ディスクとの入出力の最小単位）に格納。 一意のキーで識別され、行単位データが必要なケース向き 列指向 列ごとのデータがブロックに格納。 トレンド把握、平均計算など集計処理 （分析的なケース）向き 出典: Row Store vs Column Store: Unpacking The Difference CONFIDENTIAL 11
12. リレーショナルデータベース（RDB） 正規化 RDBにおけるデータを重複・冗長性がなくなるよう構造化し、データの整合性を向上させるプロセスのこと メリット • • • 同じ情報を1箇所で管理でき、更新漏れによる不整合（更新時異常）を防げる データが共通化され利用しやすくなる データの容量が削減される デメリット • 場合によっては検索パフォーマンスが悪化する ◦ 軽量なWEBサービスでは敢えて正規化しないケースがある CONFIDENTIAL 12
13. リレーショナルデータベース（RDB） 第１正規化 行内のデータに繰り返しがなく、プリミティブな値であること PrimaryKeyが設定されている 正規化なし id name 第１正規化 office id name office address 1001 田中 ミッドタウン 東京都港区赤坂 1001 田中 ミッドタウン 東京都港区赤坂 1002 佐藤 ガーデンプレイス 東京都渋谷区恵比寿 1002 佐藤 ガーデンプレイス 東京都渋谷区恵比寿 1003 山田 ミッドタウン 東京都港区赤坂 1003 山田 ミッドタウン 東京都港区赤坂 CONFIDENTIAL 13
14. リレーショナルデータベース（RDB） 第２正規化 完全関数従属＝全ての非キー属性が「主キー全体」に従属すること employeeId name employeeId officeId 例: 社員が複数オフィスに所属できる → 主キーは employeeId + officeId（複合） 1001 田中 1001 100 name→employeeId、officeName・address→officeId にのみ従属（部分関数従属） 1002 佐藤 1001 200 1002 200 → 従属する単位ごとに表を分割する employ eeId name 1001 田中 1001 田中 1002 佐藤 CONFIDENTIAL officeId 100 200 200 office ミッドタウン ガーデンプレイス ガーデンプレイス address 第１正規化 officeId officeName zipCode address 100 ミッドタウン 107-0052 東京都港区赤坂 200 ガーデンプレイス 150-0013 東京都港区赤坂 東京都渋谷区恵比 寿 東京都渋谷区恵比 寿 第２正規化 東京都渋谷区恵比寿 14
15. リレーショナルデータベース（RDB） 第３正規化 推移的関数従属 zipCode address 107-0052 東京都港区赤坂 150-0013 東京都渋谷区恵比寿 主キー以外の属性同士の関数従属がないこと e.g.) officeId → zipCode → address 第２正規化 第３正規化 officeId officeName zipCode address 100 ミッドタウン 107-0052 東京都港区赤坂 200 ガーデンプレイス 150-0013 東京都渋谷区恵比寿 officeId officeName zipCode 100 ミッドタウン 107-0052 200 ガーデンプレイス 150-0013 ※ 現実にはzipCode(郵便番号)で住所は一意に決まらないが例として簡略化している CONFIDENTIAL 15
16. SQL CONFIDENTIAL 16
17. SQL SQLとは データベースを操作するために開発された言語。 ISOで定められた標準規格があるが、RDBMSごとに違いもある。 Structured Query Languageの略 CONFIDENTIAL 17
18. SQL SQLの種類 DDL（データ定義言語） DML（データ操作言語） DCL（データ制御言語） TCL（トランザクション制御言語） CONFIDENTIAL 18
19. SQL DDL（データ定義言語） テーブル・データベースの構造定義 CREATE テーブルやデータベース等のオブジェクトの定義を作成する CREATE TABLE … CREATE DATABASE … DROP テーブルやデータベース等のオブジェクトを削除する DROP TABLE … DROP DATABASE … ALTER テーブルやデータベース等のオブジェクトの定義を変更する ALTER TABLE ADD COLUMN … ALTER TABLE ADD INDEX … TRUNCATE テーブル内のデータを全て削除する（DROPして再生成） TRUNCATE TABLE … CONFIDENTIAL 19
20. SQL DML（データ操作言語）: データ検索、新規登録、更新、削除 SELECT テーブル内のデータを取得する SELECT * FROM User; INSERT テーブルに新しいデータ追加する INSERT INTO User (…) VALUES (…); UPDATE テーブル内の既存のデータを変更する UPDATE User SET … WHERE …; DELETE テーブル内の既存のデータを削除する DELETE FROM User WHERE …; CONFIDENTIAL 20
21. SQL TCL（トランザクション制御言語）: トランザクション処理を扱う BEGIN トランザクションを開始する BEGIN;（START TRANSACTION;） COMMIT トランザクションをコミットする。処理を確定する。 COMMIT; ROLLBACK トランザクションをロールバックする。処理を取り消す。 ROLLBACK; CONFIDENTIAL 21
22. SQL DCL（データ制御言語） データに対するアクセス制御 GRANT アクセス権限を付与する GRANT SELECT ON db.* TO user; REVOKE アクセス権限を剥奪する REVOKE SELECT ON db.* FROM user; CONFIDENTIAL 22
23. SQL id name officeId created At 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 基本的なSELECT文 SELECT * FROM User; SELECT 検索で取得する列を選択する FROM 検索対象のテーブルを選択する CONFIDENTIAL 23
24. SQL id name officeId created At 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 基本的なSELECT文 SELECT id, name FROM User; SELECT 検索で取得する列を選択する FROM 検索対象のテーブルを選択する CONFIDENTIAL 24
25. SQL id name officeId createdAt 基本的なSELECT文（フィルタリング） 1001 田中 100 2025-01-01 SELECT id, name FROM User 1002 佐藤 200 2025-04-01 WHERE createdAt >= ‘2025-04-01’; 1003 山田 100 2025-04-30 WHERE 検索条件を比較演算子を用いて指定する = 等価比較 id = 1 >, <, >=, <= 大小比較 createdAt >= ‘2025-04-01’, age < 20 IN 複数値指定 id IN (1,2,3) BETWEEN 範囲指定 createdAt BETWEEN '2025-01-01' AND '2025-04-30' LIKE 曖昧比較 name like ‘%田’ CONFIDENTIAL 25
26. SQL 基本的なSELECT文（フィルタリング） SELECT id, name FROM User id name officeId createdAt 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 WHERE createdAt >= ‘2025-04-01’ AND officeId = 200; WHERE 論理演算子で条件を複数指定できる AND A かつ B createdAt >= ‘2025-04-01’ AND officeId = 200 OR A または B createdAt >= ‘2025-04-01’ OR officeId = 200 NOT A ではない NOT (officeId = 200) XOR A と B のどちらか一方のみ真 createdAt >= ‘2025-04-01’ XOR officeId = 200 CONFIDENTIAL 26
27. SQL id name officeId createdAt 基本的なSELECT文（並べ替え） 1003 山田 100 2025-04-30 SELECT id, name FROM User 1002 佐藤 200 2025-04-01 WHERE createdAt >= ‘2025-04-01’ 1001 田中 100 2025-01-01 ORDER BY id DESC; ORDER BY データの並び替え基準を指定する。カンマ区切りで複数指定可。 ASC 昇順 ORDER BY id ASC DESC 降順 ORDER BY id DESC CONFIDENTIAL 27
28. SQL 基本的なSELECT文（集計） SELECT COUNT(*) FROM User GROUP BY officeId; GROUP BY データをグルーピングする。 COUNT() 個数カウント COUNT(*) AVG() 平均値を取得 AVG(age) MAX() 最大値を取得 MAX(id) MIN() 最小値を取得 MIN(id) CONFIDENTIAL 28
29. SQL id name officeId createdAt 基本的なSELECT文（結合） 1003 山田 100 2025-04-30 SELECT u.id, u.name, u.officeId, o.officeName 1002 佐藤 200 2025-04-01 FROM User as u 1001 田中 100 2025-01-01 INNER JOIN Office as o on u.officeId = o.officeId WHERE u.createdAt >= ‘2025-04-01’ officeId officeName address ORDER BY u.id DESC; 100 ミッドタウン 東京都港区赤坂 200 ガーデンプレイス 東京都渋谷区恵比寿 INNER JOIN テーブル同士のデータを結合する。共通の列をON句で指定 CONFIDENTIAL 29
30. SQL id name officeId officeName 基本的なSELECT文（結合） 1003 山田 100 ミッドタウン SELECT u.id, u.name, u.officeId, o.officeName 1002 佐藤 200 ガーデンプレイス FROM User as u INNER JOIN Office as o on u.officeId = o.officeId WHERE u.createdAt >= ‘2025-04-01’ ORDER BY u.id DESC; INNER JOIN テーブル同士のデータを結合する。共通の列をON句で指定 CONFIDENTIAL 30
31. SQL 基本的なINSERT文 INSERT INTO User (`id`, `name`, `officeId`, `createdAt`) id name officeId createdAt 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 1004 高橋 100 2025-05-01 VALUES (1004, “高橋”, 100, ‘2025-05-01’); CONFIDENTIAL 31
32. SQL 基本的なDELETE文 DELETE FROM User id name officeId createdAt 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 WHERE id = 1004; CONFIDENTIAL 32
33. SQL 基本的なUPDATE文 UPDATE User SET officeId = 200 id name officeId createdAt 1001 田中 100 2025-01-01 1002 佐藤 200 2025-04-01 1003 山田 100 2025-04-30 1004 高橋 200 2025-05-01 WHERE id = 1004; CONFIDENTIAL 33
34. SQL • • • • • 基本的なSELECT文（おさらい） ◦ 全データ取得 ◦ カラム指定 ◦ WHERE句によるフィルタリング ◦ ORDER BY句によるソート ▪ データ検索、新規登録、更新、削除。 ▪ SELECT, INSERT, UPDATE, DELETE。 テーブルの結合 (JOIN) データのグループ化と集計 サブクエリ、集合演算 予約語と識別子、別名 CONFIDENTIAL 34
35. リレーショナルデータベース（RDB） 制約 レコードのカラムに登録できるデータに制約をつけることで、不整合なデータ登録を防ぐ機能 CREATE TABLE users ( userId INT NOT NULL AUTO_INCREMENT, userName VARCHAR(50) NOT NULL, -- NOT NULL制約 NULLを許容しない email VARCHAR(255) UNIQUE, -- UNIQUE制約 同じ値のレコードを複数作成できなくする zipCode INT NOT NULL, createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (userId) -- PRIMARY制約 一意の値であり、NULLを許容しない ) ENGINE=InnoDB; CONFIDENTIAL 35
36. インデックス CONFIDENTIAL 36
37. インデックス インデックスとは データベースにおける「索引」 対象の属性からデータ本体の逆引きを作成することで、探索を高速にする indexなし indexあり 索引: Term → p.149 p.1 p.2 p.3 … p.149 Term p.1 … p.149 Term … p.999 先頭ページから1ページずつ順に確認する（フルスキャン） 索引で格納位置を特定し、目的の中間要素へ直接アクセス CONFIDENTIAL 37
38. インデックス 設定方法 テーブル定義に KEY index名 (カラム名)を追加する CREATE TABLE users ( userId INT NOT NULL AUTO_INCREMENT, userName VARCHAR(50) NOT NULL, email VARCHAR(100), createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (userId), INDEX idx_userName (userName) ) ENGINE=InnoDB; CONFIDENTIAL 38
39. インデックス BEFORE（インデックスなし、10万行のテーブルの例） EXPLAIN SELECT * FROM users WHERE userName = 'Alice'; → type: ALL key: NULL rows: 100000 （フルテーブルスキャン） AFTER（INDEX idx_userName (userName) を追加） EXPLAIN SELECT * FROM users WHERE userName = 'Alice'; → type: ref key: idx_userName rows: 1 （インデックスで絞り込み） 走査行数 100000 → 1 ：インデックスにより探索コストが大幅に削減される CONFIDENTIAL 39
40. インデックス プライマリーインデックス テーブルのプライマリーキーに対して作成されるインデックス ※PrimaryIndex = ISBN ISBN 書籍タイトル 著者 出版社 978-4-91551200-3 ハリー・ポッター J.K.ローリング 静山社 978-4-06273017-4 悪意 東野圭吾 講談社 978-4-06-274868-1 ノルウェイの森 村上 春樹 講談社 978-4-8387-3030-7 君たちはどう生きるか 吉野源三郎 岩波書店 ISBNコード = 978-4-06-274868-1 ISBN: 国際標準図書番号 CONFIDENTIAL 40
41. インデックス セカンダリーインデックス PRIMARY KEY以外に対して作成される、データ本体とは別の索引構造 セカンダリーインデックスからプライマリーインデックスのキーを、プライマリーイン デックスから実データを引くという２段階で取得する ※ SecondaryIndex = 出版社 ※PrimaryIndex = ISBN 出版社 ISBN ISBN 書籍タイトル 著者 出版社 静山社 978-4-91551200-3 … 978-4-91551200-3 ハリー・ポッター J.K.ローリング 静山社 講談社 978-4-06273017-4 978-4-06-274868-1 978-4-06273017-4 悪意 東野圭吾 講談社 岩波書店 978-4-8387-3030-7 978-4-06-274868-1 ノルウェイの森 村上 春樹 講談社 小学館 … 978-4-8387-3030-7 君たちはどう生きるか 吉野源三郎 岩波書店 出版社 = 講談社 CONFIDENTIAL 41
42. インデックス インデックスのデータ構造の種類 B(+)Tree 転置インデックス R-Tree Bitmap 行 1 2 3 4 5 全領域 赤坂 #1 #3 東京 #1 #2 港区 #1 #4 MBR A A1 A2 東京 1 0 1 0 0 大阪 0 1 0 0 1 福岡 0 0 0 1 0 MBR B B1 B2 出典：Data Structure Visualizations CS 1332 Data Structures & Algorithms Visualization Tool CONFIDENTIAL 42
43. インデックス BTree インデックスのデータ構造の種類 MySQL(InnoDB)で採用されている構造はB+Tree リーフノードにもデータがあり、隣のリーフノード同士も参 照を持つので範囲探索に強い B+Tree 出典：Data Structure Visualizations CS 1332 Data Structures & Algorithms Visualization Tool CONFIDENTIAL 43
44. インデックス どのようなインデックスを登録するか インデックスはデータ本体とは別に索引構造を作成 → データの追加・更新に合わせてインデックスも変更するオーバヘッドが発生 作成したインデックスの数だけ容量を消費する → 使われないインデックスはディスクスペースの無駄 カーディナリティが低い列をインデックスに指定してもデータを絞り込めない カーディナリティ = 取り得る値の種類 → DBがインデックスを使えないと判断し、フルスキャンが発生する可能性も CONFIDENTIAL 44
45. インデックス インデックスを登録するか データへのアクセスパターンを考え実際に使用されるカラムを登録する カーディナリティの高いカラムを優先的に登録する ❌ gender (Enum), isActive (bool) ◯ userId, name, email ※ 複合index：WHERE で先頭から連続して指定されるカラム順に並べる。 例: WHERE userId=? AND itemId=? が主クエリなら userId 先頭） ❌ itemId_userId ◯ userId_itemId CONFIDENTIAL 45
46. トランザクションと排他制御 CONFIDENTIAL 46
47. トランザクションと排他制御 トランザクションとは 一連のデータ処理を一つにまとめて管理する仕組み 参照・挿入・更新・削除などの複数のステップに渡るデータ操作を管理すること で、データの不整合を防ぐ CONFIDENTIAL 47
48. トランザクションと排他制御 使い方 更新を含む一連の処理をBEGIN(START TRANSACTION) ~ COMMIT(ROLLBACK)で囲う BEGIN; UPDATE `Order` SET status = ‘delivered’ WHERE id =1 AND status = ‘ordered’; COMMIT; -- 処理を確定させる場合 ROLLBACK; -- 処理を破棄する場合 CONFIDENTIAL 48
49. トランザクションと排他制御 ACID特性 トランザクションが満たすべき性質のこと 原子性 (Atomicity) 処理が全て実行されるか一切実行されないかのAll or Nothingの状態になること 一貫性 (Consistency) 実行前後でデータの矛盾が発生しないこと 独立性 (Isolation) 並行して実行している他のトランザクションから影響を受けないこと 永続性 (Durability) 一度反映したトランザクションの結果が失われないこと CONFIDENTIAL 49
50. トランザクションと排他制御 原子性 (Atomicity) 処理が全て実行されるか、あるいは一切実行されないか 中途半端に処理が実行されないようにする特性 ゲーム内通貨消費 ゲーム内アイテム付与 CONFIDENTIAL 50
51. トランザクションと排他制御 原子性 (Atomicity) 処理が全て実行されるか、あるいは一切実行されないか アイテム付与だけ失敗 → ゲーム内通貨の払い損 ゲーム内通貨消費 ゲーム内アイテム付与 CONFIDENTIAL 51
52. トランザクションと排他制御 原子性 (Atomicity) 処理が全て実行されるか、あるいは一切実行されないか 片方だけの失敗（払い損・無限増殖）を防ぐため「両方成功 or 両方失敗」を保証する 1.START TRANSACTION 2.ゲーム内通貨消費 3.アイテム付与 4.COMMIT CONFIDENTIAL トランザクション で処理を囲う 1.START TRANSACTION 2.ゲーム内通貨消費 3.アイテム付与 4.ROLLBACK 52
53. トランザクションと排他制御 ACID特性について 一貫性 (Consistency) 実行前後でデータの矛盾が発生しない データベースに与えられた制約条件に違反する更新が成功しない CONFIDENTIAL 53
54. トランザクションと排他制御 ACID特性について 永続性 (Durability) 一度反映したトランザクションの結果は、その後のデータ操作で上書きしない限り失 われない 電気的にシャットダウン 直後にディスクスペース枯渇 クラッシュして再起動...など CONFIDENTIAL 54
55. トランザクションと排他制御 WAL（Write-Ahead Logging）― 永続性(Durability)を支える仕組み データ変更の前に、変更内容をログ（WAL / redoログ）へ先に書く（ログ先行の原則） WALがディスクに永続化された時点でコミット確定 → クラッシュしてもログから復旧できる データ本体はメモリ上のバッファで更新し、後でまとめてディスクへ反映（チェックポイント） ログは順次追記でランダム書き込みより高速。復旧はチェックポイント以降をredo（再実行） ① ログ先行書き込み 更新（TX） WAL / redo ログ （ディスクに順次追記） ★ WALがディスクに書けた時点で コミット確定（＝永続性） ③ チェックポイントなどで、実データ（データファイル）へ後から反映 バッファプール（メモリ） CONFIDENTIAL データファイル（ディスク） クラッシュ後: チェックポイント以降の WAL を redo して整合状態へ復元 55
56. トランザクションと排他制御 ACID特性について 独立性 (Isolation) 並行して実行している他のトランザクションから影響を受けない 排他制御 複数のクライアント・プロセスから同じデータにアクセスした時に データの整合性を担保する仕組み CONFIDENTIAL 56
57. トランザクションと排他制御 独立性 (Isolation) トランザクション分離レベルとAnomaly トランザクションには並行トランザクションからのアク セスをどれだけ厳密に管理するかのレベルが存在する Web開発者が知っておくべき八つの分離レベルのよう に、レベルには複数の定義が存在するが、MySQLで主に 使用するのは右図の4レベル 一部の低いレベルでは、状況によって予期せぬ副作用 （Anomaly）が発生する CONFIDENTIAL 57
58. トランザクションと排他制御 排他制御 並行するトランザクションとの更新が競合しないよ うに、対象のデータの周辺に読み取り/書き込みを 防ぐロックをかける 0030 ロックの範囲はインデックスを使って決定する 0020 0010 CONFIDENTIAL 0040 0020 0030 0040 58
59. トランザクションと排他制御 排他制御 レコードロック/インデックスレコードロック 更新対象のレコードをロックする 0030 0020 0010 CONFIDENTIAL 0040 0020 0030 0040 59
60. トランザクションと排他制御 排他制御 ギャップロック レコードとレコードの間をロックする → レコードの間に新しいデータが挿入されるのを防ぐ 0030 0020 0010 0040 0020 insert CONFIDENTIAL 0025 0030 0040 blocked! 60
61. トランザクションと排他制御 排他制御 ネクストキーロック 更新対象のレコードロックとその直前のギャップのロック （範囲検索ではネクストキーロックにより後ろのギャップもロックされる） 0030 0020 0010 0040 0020 insert CONFIDENTIAL 0025 0030 0040 blocked! 61
62. トランザクションと排他制御 排他制御 ネクストキーロック 対象の行がリーフノードの端である場合は先頭行 より手前、末尾より後にもGAPロックがかかる D C infimum A SELECT A FOR UPDATE CONFIDENTIAL E B C D E supremum SELECT E FOR UPDATE 62
63. トランザクションと排他制御 独立性 (Isolation) Read Uncommitted write current current x=1 x=2 TX:bbb トランザクション内の読み取りは行データを直接参照する read SELECT FOR SHARE/UPDATE, UPDATE, DELETE TX: aaa → レコードロックを実行 0030 0020 0010 update CONFIDENTIAL 0040 0020 0020 0030 0040 blocked! 63
64. トランザクションと排他制御 独立性 (Isolation) Read Uncommitted write current current current x=1 x=2 x=2 TX:bbb トランザクション内の読み取りは行データを直接参照する read read 並行トランザクションの未コミットな値を読んでしまう Dirty Readが発生 SELECT FOR SHARE/UPDATE, UPDATE, DELETE → レコードロックを実行 0030 0020 0010 update CONFIDENTIAL 0040 0020 0020 0030 0040 blocked! 64
65. トランザクションと排他制御 独立性 (Isolation) commit write TRX_ID: bbb version: 2 current Read Committed x=1 SELECT → StatementごとにReadViewを生成して参照 x=2 x=2 TX:bbb current Undo Log TRX_ID: zzz version: 1 read x=1 SELECT FOR SHARE/UPDATE, UPDATE, DELETE read → レコードロックを実行 0030 0020 0010 update CONFIDENTIAL 0040 0020 0020 0030 0040 blocked! 65
66. トランザクションと排他制御 独立性 (Isolation) commit write current Read Committed x=1 SELECT read SELECT FOR SHARE/UPDATE, UPDATE, DELETE TX: aaa current x=2 x=2 TX:bbb → StatementごとにReadViewを生成して参照 TRX_ID: bbb version: 2 Undo Log TRX_ID: zzz version: 1 x=1 read → レコードロックを実行 0030 コミット済みの値を読んでしまうFuzzy Readが発生 0020 0010 update CONFIDENTIAL 0040 0020 0020 0030 0040 blocked! 66
67. トランザクションと排他制御 write TRX_ID: bbb version: 2 current x=1 x=2 x=2 独立性 (Isolation) Read Committed read SELECT current Undo Log TRX_ID: aaa version: 1 x=1 read → StatementごとにReadViewを生成して参照 →コミット済みの値を読んでしまうFuzzy Readが発生 0030 SELECT FOR SHARE/UPDATE, UPDATE, DELETE 0020 → レコードロックを実行 レコードの間はロックされないので、範囲選択で他のTX が挿入した行が読めてしまう = Phantom Read が発生 0010 0040 0020 0030 0040 insert 0025 CONFIDENTIAL 67
68. トランザクションと排他制御 独立性 (Isolation) write Repeatable Read current TX:bbb x=1 SELECT TRX_ID: bbb version: 2 x=2 TRX_ID: zzz version: 1 TRX_ID: zzz version: 1 TRX_ID: zzz version: 1 x=1 x=1 x=1 read SELECT FOR SHARE / FOR UPDATE、UPDATE、DELETE tx中はread viewを 読み続ける 0030 → 範囲検索などではネクストキーロックを実行し 0020 レコード間への挿入もブロックする 良さそうに見えるが... current x=2 read → 初回SELECT時のReadViewをトランザクションの中で使 い回す commit 0010 0040 0020 0030 0040 0025 insert CONFIDENTIAL blocked! 68
69. トランザクションと排他制御 独立性 (Isolation) COMMIT UPDATE x = 11 TX-B TRX_ID: B version: 2 current x = 11 x = 11 Repeatable Read 良さそうに見えるが... SELECT x = 1 current 古い値で上 書き x=1 同じデータを別々のトランザクションが更新する場合で も、更新文の形によって結果が異なる TRX_ID: A version: 3 事前にSELECTした x = 1 を使って固定値 x = 2 を書き戻す と、先行トランザクションの x = 11 を上書きする TX-A = Lost Update が発生 CONFIDENTIAL COMMIT SELECT x = 1 x=2 current x=2 UPDATE SET x = 2 69
70. トランザクションと排他制御 write x + 10 独立性 (Isolation) Repeatable Read read for update TX:bbb 良さそうに見えるが... current TRX_ID: bbb version: 2 x = 11 current x = 11 TRX_ID: aaa version: 3 x = 12 x=1 write x + 1 current read for update x = 12 Lost Update が発生 TX: aaa → 読み出し時もSELECT FOR UPDATEを使い、 commit 他TXの読み出しをcommitまで待たせる → 古い値での上書きがなくなる（Lost Update 回避） CONFIDENTIAL 70
71. トランザクションと排他制御 独立性 (Isolation) read for update （先） Serializable write TRX_ID: bbb version: 2 commit x=2 全てのトランザクションを直列実行したのと 同じになるようにする 0010 current current x=1 x=2 x=2 read for update （後） autocommitを無効にした場合、通常の SELECTもFOR SHARE相当のロック読み取り になる ネクストキーロックでレコード間の挿入もブ ロック current blocked 0030 0020 0040 0020 insert 0030 0040 blocked! 0025 CONFIDENTIAL 71
72. 最適化 CONFIDENTIAL 72
73. 最適化 実行計画の取得 Explain 実行するクエリの先頭にExplainをつけるとクエリの実行計画が見える Explain Analyze クエリが実際に実行された時の統計情報が見える CONFIDENTIAL 73
74. 最適化 実際に使用したインデックス 実行計画の取得 id select_type table 1 SIMPLE USER クエリの種類 CONFIDENTIAL partitions type possible_keys NULL ALL スキャン方法 NULL key key_len ref rows filtered extra NULL NULL NULL 4 25.00 Using Where 使用可能なインデックス 走査される推定行数 データ取得時に実行された追加処理 74
75. 最適化 実行計画の取得 id select_type table 1 SIMPLE USER partitions type possible_keys NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where select_type（クエリの種類） • • SIMPLE: 通常のクエリ SUBQUERY: サブクエリ自身のクエリ（サブクエリを含む外側のクエリはPRIMARY） CONFIDENTIAL 75
76. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where type（スキャン方法） const: PK/ユニークインデックスで完全一致のレコードを取得 range: インデックスを使用した範囲検索 CONFIDENTIAL 76
77. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where type（スキャン方法） index: フルインデックススキャン → インデックス全件を走査するので遅い ※1 all: フルテーブルスキャン → インデックスを使わず全件走査するので遅い ※1 CONFIDENTIAL ※1 ただし全体のデータ量が少ない場合はシステム(オプティマイザ)が全件走査が速いと判断し実行することもある 77
78. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where key（実際に使用されるインデックス） ref（keyと比較される列名） rows（クエリで走査されるであろう推定行数） • CONFIDENTIAL 少ないほど絞り込みが効いている 78
79. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where extra（追加処理） • Using Index ◦ CONFIDENTIAL 必要なデータの取得をインデックスだけで解決できている 79
80. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where extra（追加処理） • Using Where ◦ 必要なデータの取得をインデックスだけでは解決できず、残りのWhere条件を 追加でフィルタリングしている CONFIDENTIAL ※ createdAtがインデックスにないので、id指定で取得した行を後から絞り込んでいる 80
81. 最適化 id select_type table partitions type possible_keys 実行計画の取得 1 SIMPLE USER NULL range NULL key key_len ref rows filtered extra id NULL xxx 10 25.00 Using Where extra（追加処理） • • CONFIDENTIAL Using Filesort ◦ ストレージエンジンでインデックスでソートできず結果に対して再度ソートが 発生 Using temporary ◦ 結果の整形に一時テーブル(Temporary Table)を作成している 81
82. スケーリングと分散 CONFIDENTIAL 82
83. スケーリングと分散 リードレプリカ（Read Replica）とレプリケーション プライマリ（ソース）の複製を作り、読み取り専用クエリを処理させる仕組み 役割: 読み取り負荷の分散、集計・分析など重いクエリのオフロード、障害時の昇格先 仕組み: プライマリが更新をbinlogに記録 → レプリカが受信・適用（非同期。GTIDで位置管理） 注意: 非同期＝レプリケーションラグがあるため、書き込み直後の読み取りに考慮が必要 プライマリ レプリカ ② binlog転送（非同期） ① 記録 書き込み binlog （更新ログ） I/Oスレッド relay log ③ 保存 データ SQLスレッド APP 読み取り（分散） CONFIDENTIAL ④ 適用 83
84. スケーリングと分散 水平分割（シャーディング） データを行単位で複数のDBに分割して保持する APP シャードキー（例: user_id）で保存先を決定する ※ range / modulo など ルーティング user_id % 3 書き込みもスケールアウトできる 課題 シャードを跨ぐJOIN・集計・トランザクション リシャーディング（再分割）の運用コスト 特定シャードへのアクセス偏り ※ テーブル・機能単位で分ける垂直分割も存在 shard 0 shard 1 shard 2 user_id 3,6,9… user_id 1,4,7… user_id 2,5,8… 例: user_id = 4 → 4 % 3 = 1 → shard 1 に格納 CONFIDENTIAL 84
85. スケーリングと分散 ① アトミックコミット（2PC / XA）― 参加者間の原子性 2相コミット(2PC): Phase 1 PREPARE → 全員YESなら Phase 2 COMMIT（1つでもNOなら全ROLLBACK） コーディネータ役: ・外部TM: JTA（Atomikos, Narayana）, MSDTC ・DB内蔵: MySQL Internal XA（server=TM / storage engine=RM）, Oracle Database分散TX ・分散DB: Spanner等は「shard内Consensus + shard間2PC」を併用 XA対応: MySQL / PostgreSQL / Oracle / SQL Server / Db2 / MariaDB 2PC以外: 3PC, Saga（結果整合）, TCC, Percolator, Calvin 出典: MySQL 8.4 Restrictions on XA Transactions / Oracle Database Managing Distributed Transactions / Life of Spanner Reads & Writes コーディネータ 点線=PREPARE 実線=COMMIT shard 1 shard 2 ② コンセンサス（Paxos / Raft）― 複製レプリカ間の合意 Consensus: Quorum（過半数の合意）で複製間の値とログ順序を確定 Paxos: Phase 1 Prepare(n) → 過半数が Promise（n より古い提案は拒否すると約束） Phase 2 Accept(n, v) → 過半数が Accepted なら値 v で確定 Raft: 選挙: 候補者が投票依頼 → 過半数の票を獲得したら Leader に当選 複製: Leader がログを配布 → 過半数の ACK で commit 併用: shard内Consensus + shard間2PC（Spanner等） 出典: Lamport「Paxos Made Simple」 / Ongaro & Ousterhout「Raft」 CONFIDENTIAL Paxos Proposer proposal n=7 A1 A2 Raft Leader term=8 点線=準備 実線=採択 A3 Phase 1: quorum Promise Phase 2: quorum Accepted → chosen v R1 R2 点線=選挙 実線=複製 R3 選挙: quorum 2/3 → Leader 複製: quorum ACK → commit[42] 85
86. その他のデータベース CONFIDENTIAL 86
87. その他のデータベース Key-Valueストア 索引（Key）と値（Value）のペアを保存するDB 検索時はKeyの値で検索し、その他の属性をValueに入れる メモリ上に全てのデータが乗るインメモリーDBに多く採用されている（Redis, Memcachedなど） 基本は等価検索。Redis等追加のデータ構造の実装もある: String / List / Hash / Set / Sorted Set ... Sorted Set（Redis） String（単一値） skip list（score順の走査 O(log N)） key value K1 AAA,BBB,CCC K2 DDD,EEE head 10 25 40 NIL hash table（member→node で ZSCORE を O(1)） m10 CONFIDENTIAL m25 m40 87
88. その他のデータベース ドキュメントDB 事前のスキーマ定義なしで構造化されたデータ（JSON、XML...）を保存できる 柔軟にデータを登録できるメリットがある { id: 1001, name: テスト1, email: xxx@yyy createdAt: “xx-xx-xx” ネスト構造ごと保存し読み書きできる } シャーディングなど水平スケールに強い { id: 1002, name: テスト2, tel: xxx-xxx-xxx DynamoDB, MongoDBなど } { id: 1003, name: テスト３, zipCode: 150-xxxx address: X市Y町zzz } CONFIDENTIAL 88
89. その他のデータベース タイムシリーズ 大量の時系列データを格納し、参照するのに向いている TimescaleDB、Amazon Timestreamなど Hypertable「metrics」（アプリからは1つの表に見える） 時間で自動パーティション Chunk 05/21 のデータ Chunk 05/22 のデータ Chunk 05/23（最新=書込先） ・時間範囲でchunk（物理テーブル）に自動分割 → 範囲クエリは該当chunkのみ走査（チャンク除外） ・新しいデータは最新chunkへ追記。古いchunkは列指向で圧縮・削除が容易 ・書き込みは時刻順の追記が中心でインデックス更新の負担が小さい。集計は連続集計（事前計算）で参照を軽くできる CONFIDENTIAL 89
90. その他のデータベース グラフDB データ間の関係性を辿って検索するのに特化したDB 個別のデータをノード、データ間の関係性をエッジとして表現する ソーシャルグラフ、経路探索などのデータを扱うのに向いている Neo4jなど ソーシャルグラフ（follow関係） C follow A follow B followed CONFIDENTIAL 経路探索（重み付きエッジ） 5分 follow follow D E 駅 B 6分 駅 A 駅 D 3分 4分 駅 C 90
91. おわりに 実務ではRDB以外にも様々なデータ構造やアーキテクチャのDBを採用しています。 今日学んだ基本的な設計・計測・データ操作方法や考え方は応用が効くので是非持ち帰ってください。 ご清聴ありがとうございました。 CONFIDENTIAL 91