【研修資料】データベース基礎

26卒エンジニア内定者向け技術研修

Transcript

1. データベース基礎 2025/09/08
2. Copyright © 2015 every, Inc. All rights reserved. 2 • 講義の目的とゴール • データとは • SQL • 正規化・インデックス • データ基盤 • おすすめ書籍 目次
3. 講義の目的とゴール
4. Copyright © 2015 every, Inc. All rights reserved. 4 • データに関する基本的な知識について学ぶ • データの基盤について学ぶ 講義の目的とゴール 前置き
5. データとは
6. Copyright © 2015 every, Inc. All rights reserved. 6 データと言っても色々存在する • 画像 • 動画 • 音声 • テキスト • 表 • 入れ子 • データベース • etc データの種類 データとは
7. Copyright © 2015 every, Inc. All rights reserved. 7 データ 構造データ 非構造データ • 画像.png • 動画.mov • 音声.wav • テキスト.text • 表.csv • 入れ子.json • データベース.db 構造データと非構造データ データとは データを分析・操作するには 扱いにくい データを分析・操作するのに 扱いやすい
8. Copyright © 2015 every, Inc. All rights reserved. 8 データ ファイルとログとデータベース データとは 構造データ 非構造データ ファイル • 画像.png • 動画.mov • 音声.wav • テキスト.text ログ • 表.csv • 入れ子.json データベース • データベース.db ---- -------- File Log DB
9. Copyright © 2015 every, Inc. All rights reserved. 9 データフロー デリッシュキッチンではどうデータが発生しているか
10. Copyright © 2015 every, Inc. All rights reserved. 10 ユーザがアクセス → アクセスログ デリッシュキッチンではどうデータが発生しているか
11. Copyright © 2015 every, Inc. All rights reserved. 11 社員がレシピを入稿 → レシピ動画 &メタデータ デリッシュキッチンではどうデータが発生しているか
12. Copyright © 2015 every, Inc. All rights reserved. 12 • リレーショナルデータベース（RDB） ◦ データベースと言えば一般的に RDBのことを指す ◦ データを人間が理解しやすい二次元表形式で管理 ◦ データ間の関連（リレーション）を管理するのが得意 ◦ 例）レシピのID、タイトル、栄養素などを持つマスターデータ • キーバリューストア（KVS） ◦ データをKey（識別キー）とValue（値）を組み合わせたデータベース ◦ シンプルなデータの問い合わせが高速 ◦ 例）ユーザIDをキー、レシピID一覧をバリューとして持つレシピ推薦 • オブジェクトストレージ ◦ データをオブジェクト（データ本体とメタデータ）として管理 ◦ スケーラビリティが高く、大量のデータを安価に保存できる ◦ 例）media-bucket：recipe/{レシピID}/video.mp4 log-bucket：access/{日付}/{月}/{日}/hogefuga.gz 発生したデータをどこに保持するか データベース ---- -------- File Log DB DB
13. SQL
14. Copyright © 2015 every, Inc. All rights reserved. 14 SQL（Structured Query Language） データベース • リレーショナルデータベースに対してデータを操作するための命令を出す言語 • クエリエンジンによって若干の方言がある • 主なデータ操作 ◦ 検索 ◦ 追加 ◦ 更新 ◦ 削除 ◦ 結合 ◦ 集約
15. Copyright © 2015 every, Inc. All rights reserved. 15 SQLの実行順序 データベース 順序 句 内容 1 FROM 参照するテーブルやビューを決める 2 JOIN 必要な他テーブルを結合する 3 WHERE 条件に合わないデータを除外する 4 GROUP BY 指定したキーでグループ化する 5 HAVING グループに対して条件で絞り込む 6 SELECT 出力する列・式などを選択する 7 ORDER BY 結果を並び替える 8 LIMIT 返す行数を制限する
16. Copyright © 2015 every, Inc. All rights reserved. 16 SQL：検索 データベース
17. Copyright © 2015 every, Inc. All rights reserved. 17 SQL：追加 データベース
18. Copyright © 2015 every, Inc. All rights reserved. 18 SQL：更新 データベース
19. Copyright © 2015 every, Inc. All rights reserved. 19 SQL：削除 データベース
20. Copyright © 2015 every, Inc. All rights reserved. 20 トランザクション：関連する一連の処理を一つのまとまりとして扱う仕組み SQL：ACID トランザクション データベース 特性 内容 Atomicity （原子性） トランザクションの処理を 分割しない（全て実行する / しない） Consistency （一貫性） トランザクションの実行前後で データベースの整合性を保つ Isolation （独立性） 同時に複数人が操作してもお互いに 干渉しない Durability （永続性） トランザクションをCOMMITした 結果が失われない
21. Copyright © 2015 every, Inc. All rights reserved. 21 SQL：トランザクションがない場合の問題点 データベース トランザクションがないと、送金が途中でとまる / 同時処理で片方の更新を上書き 【一貫性の欠如（Consistency）】 Aさんの口座からお金を引き落とした時に障害が起きると Bさんの口座には振り込まれない 【同時実行制御の欠如（Isolation）】 Bさんの口座にAさんとCさんが同時に振り込んだ時に、 トランザクションがないと一方の更新が消えてしまう
22. Copyright © 2015 every, Inc. All rights reserved. 22 SQL：結合 データベース
23. Copyright © 2015 every, Inc. All rights reserved. 23 SQL：集約 データベース
24. Copyright © 2015 every, Inc. All rights reserved. 24 SQL：WITH句 データベース CTE：Common Table Expression（共通テーブル式） - 長くて複雑なクエリをステップに分割可能 - 各ステップに名前をつけることが可能 - 再利用できるため同じサブクエリを書かなくていい
25. Copyright © 2015 every, Inc. All rights reserved. 25 データの操作と実行計画 データベース クエリの文法チェック・クエリツリーに変換 統計情報（レコード数と列数など）を元に最適な実行計画を立てる SQLクエリがDBMSに送信 オプティマイザが立てた実行計画に従ってデータの処理を実行 ディスクなどに格納されている実データにアクセス
26. 正規化・インデックス
27. 27 Copyright © 2015 every, Inc. All rights reserved. 正規化 • 目的 • 冗長性を排除し、一貫性と効率性を保持するための形式にすること • 冗長性 • 同じ情報を複数テーブルに持つ状態 • 例：ユーザー住所が注文テーブルとユーザーテーブル両方にある • 非一貫性 • データ矛盾や不整合が発生する状態 • 例：住所変更の更新漏れで異なる住所が残る
28. 28 Copyright © 2015 every, Inc. All rights reserved. 正規化の仕組みと段階 • 正規化と結合 • 正規化するとテーブルは細分化 • JOINで結合すれば元の形に戻せる（可逆） • 正規形の段階 • 第1正規形：繰り返し項目をなくす • 第2正規形：主キーの一部依存を排除 • 第3正規形：主キー以外への依存を排除 • 第4・第5正規形：より高度な整理（実務ではあまり使わない） 👉 実務では 第3正規形までがよく使われる
29. 29 Copyright © 2015 every, Inc. All rights reserved. 第1正規化 目的：キーに対して値が一意に定まる状態（関数従属性）を保証すること • ex. {顧客ID} → {顧客名}、 {顧客ID} → {商品} • RDBのテーブルは第一正規形を満たしている 顧客ID 顧客名 商品 C001 佐藤 キャベツ にんじん C002 鈴木 C003 田中 みかん ブドウ メロン 購入品 第1正規化を満たしていないテーブル❌
30. 30 Copyright © 2015 every, Inc. All rights reserved. 第1正規化 目的を達成するために • 1つのセルに単一の値になるようにする 顧客ID 顧客名 商品1 商品2 商品3 C001 佐藤 キャベツ にんじん C002 鈴木 C003 田中 ぶどう みかん メロン 顧客ID 顧客名 商品 C001 佐藤 キャベツ C001 佐藤 にんじん C002 鈴木 C003 田中 ぶどう C003 田中 みかん C003 田中 メロン OR 購入品 購入品
31. 31 Copyright © 2015 every, Inc. All rights reserved. 第1正規化 目的を達成するために • キーは一部であっても NULLを含んではならない 顧客ID 顧客名 C001 佐藤 C002 鈴木 C003 田中 顧客ID 商品 C001 キャベツ C001 にんじん C003 ぶどう C003 みかん C003 メロン 顧客 商品 テーブルの分割を行う 顧客ID 顧客名 商品 C001 佐藤 キャベツ C001 佐藤 にんじん C002 鈴木 C003 田中 ぶどう C003 田中 みかん C003 田中 メロン 購入品 主キーが定まらない！
32. 32 Copyright © 2015 every, Inc. All rights reserved. 目的：部分関数従属を解消して、完全関数従属のみの状態にすること • 完全関数従属 : キーを構成する全ての列に対して従属する状態 • ex. {任意のキー1, 任意のキー2} → {一意の値} • {顧客ID,商品コード} → {購入個数} ⭕ 第2正規化 顧客ID 商品コード 顧客名 商品名 カテゴリID カテゴリ名 単価 購入数量 CUST001 P001 佐藤 キャベツ CAT001 野菜 150 10 CUST001 P002 佐藤 にんじん CAT001 野菜 100 5 CUST003 P003 田中 りんご CAT002 果物 150 3 CUST003 P004 田中 みかん CAT002 果物 80 20 CUST003 P005 田中 ぶどう CAT002 果物 300 2 購入履歴 第2正規化を満たしていないテーブル❌
33. 33 Copyright © 2015 every, Inc. All rights reserved. 第2正規化 • 部分関数従属 • {顧客ID} → {顧客名} ❌ • {商品コード} → {商品名, カテゴリID, カテゴリ名, 単価} ❌ 第2正規化を満たしていないテーブル❌ 顧客ID 商品コード 顧客名 商品名 カテゴリID カテゴリ名 単価 購入数量 CUST001 P001 佐藤 キャベツ CAT001 野菜 150 10 CUST001 P002 佐藤 にんじん CAT001 野菜 100 5 CUST003 P003 田中 りんご CAT002 果物 150 3 CUST003 P004 田中 みかん CAT002 果物 80 20 CUST003 P005 田中 ぶどう CAT002 果物 300 2 購入履歴
34. 34 Copyright © 2015 every, Inc. All rights reserved. 第2正規化 目的を達成するために • 部分関数従属している列を、依存先のキーと共に別のテーブルに切り出す • 各テーブルの情報がキーに対して一意に定まるようになった 顧客ID 顧客名 CUST001 佐藤 CUST003 田中 商品コード 商品名 カテゴリID カテゴリ名 単価 P001 キャベツ C001 野菜 150 P002 にんじん C001 野菜 100 P003 りんご C002 果物 150 P004 みかん C002 果物 80 P005 ぶどう C002 果物 300 顧客ID 商品コード 購入数量 CAT001 P001 10 CAT001 P002 5 CAT003 P003 3 CAT003 P004 20 CAT003 P005 2 購入履歴 商品 顧客
35. 35 Copyright © 2015 every, Inc. All rights reserved. 第3正規化 目的：推移的関数従属が解消された状態にすること • 推移的関数従属 : {X} → {Y} → {Z}のときZはXに推移的関数従属している 商品コード 商品名 カテゴリID カテゴリ名 単価 P001 キャベツ CAT001 野菜 150 P002 にんじん CAT001 野菜 100 P003 りんご CAT002 果物 150 P004 みかん CAT002 果物 80 P005 ぶどう CAT002 果物 300 商品 第3正規化を満たしていないテーブル❌
36. 36 Copyright © 2015 every, Inc. All rights reserved. 第3正規化 • 推移的関数従属 • {商品コード} → {カテゴリID} ({X} → {Y}) • {カテゴリID} → {カテゴリ名} ({Y} → {Z}) • {商品コード} → {カテゴリID} → {カテゴリ名} ({X} → {Y} → {Z})❌ 商品コード 商品名 カテゴリID カテゴリ名 単価 P001 キャベツ CAT001 野菜 150 P002 にんじん CAT001 野菜 100 P003 りんご CAT002 果物 150 P004 みかん CAT002 果物 80 P005 ぶどう CAT002 果物 300 商品 第3正規化を満たしていないテーブル❌
37. 37 Copyright © 2015 every, Inc. All rights reserved. 第3正規化 目的を達成するために • 推移的関数従属の原因となっている {カテゴリID} と {カテゴリ名 } を、 新しい商品カテゴリテーブルとして切り出す 商品コード 商品名 カテゴリID 単価 P001 キャベツ CAT001 150 P002 にんじん CAT001 100 P003 りんご CAT002 150 P004 みかん CAT002 80 P005 ぶどう CAT002 300 商品 カテゴリ ID カテゴリ名 C001 野菜 C002 果物 商品カテゴリ
38. 38 Copyright © 2015 every, Inc. All rights reserved. インデックス • テーブルの索引のようなもの • 特定のデータを見つけたい時に、順に探すのではなくダイレクトに示すことでクエリのパ フォーマンスをあげるもの • テーブルのデータとは別にオブジェクトとして保持されている • SQLの評価順序が上位の WHERE句やORDER BY句に指定される列に設定すると効果 的
39. 39 Copyright © 2015 every, Inc. All rights reserved. インデックスの内部構造 B+Treeインデックス • 多くのRDBMSでデフォルト採用 • データを木構造で管理し、範囲検索や ORDER BYに強い • 例: WHERE age BETWEEN 20 AND 30 Hashインデックス • ハッシュ関数で値を直接検索 • 等価検索 (WHERE id = 123) に高速 • 範囲検索や順序付けには不向き
40. 40 Copyright © 2015 every, Inc. All rights reserved. インデックス(例) Before • recipes table(id, title, cooking_time)でcooking_time = 900の行を取得 • recipes tableのcooking_timeにindexを追加する
41. 41 Copyright © 2015 every, Inc. All rights reserved. インデックス(例) After • index追加後にcooking_time = 900の行を取得 • 探索の際に scanされたrowsが100 → 26に改善！ • scanされたrowsのうち今回の条件に一致する rowの割合(filtered)が10%→100%に改 善！
42. デリッシュキッチンのデータフロー
43. Copyright © 2015 every, Inc. All rights reserved. 43 データフロー（再掲） デリッシュキッチンではどうデータが発生しているか
44. Copyright © 2015 every, Inc. All rights reserved. 44 デリッシュキッチンにおけるデータの流れ (web) デリッシュキッチンのデータフロー
45. Copyright © 2015 every, Inc. All rights reserved. 45 データの流れ : RDB① デリッシュキッチンのデータフロー 1. 社員が入稿ツールで情報入力 2. B/EサーバーへAPIが呼ばれ、クエリ生成 3. DBでクエリが実行され、データの取得・作成・更新・削除が行われる 4. 3.の結果をサーバーで処理した結果がレスポンスになる
46. Copyright © 2015 every, Inc. All rights reserved. 46 データの流れ : RDB② デリッシュキッチンのデータフロー 1. ユーザーが操作を行う 2. B/EサーバーへAPIが呼ばれ、クエリ生成 3. DBでクエリが実行され、データの取得・作成・更新・削除が行われる 4. 3.の結果をサーバーで処理した結果がレスポンスになる 頻繁にアクセスされるデータはキャッシュから取得
47. Copyright © 2015 every, Inc. All rights reserved. 47 データの流れ : ログ デリッシュキッチンのデータフロー • アクセスログ ◦ F/E, B/Eのサーバー訪問者の情報と時刻 ◦ 例：クライアントIP, リクエストURL, ステータスコード • エラーログ ◦ アプリケーション実行中に発生したエラーの内容を含む ⇨ログはオブジェクトストレージに 格納される
48. Copyright © 2015 every, Inc. All rights reserved. 48 • リレーショナルデータベース（RDB） ◦ データベースと言えば一般的に RDBのことを指す ◦ データを人間が理解しやすい表形式で管理 ◦ データ間の関連（リレーション）を管理するのが得意 • キーバリューストア（KVS） ◦ データを識別キー（キー）と値（バリュー）を組み合わせたデータベース ◦ シンプルなデータの問い合わせが高速 • オブジェクトストレージ ◦ データをオブジェクト（データ本体とメタデータ）として管理 ◦ スケーラビリティが高く、大量のデータを安価に保存できる 発生したデータをどこに保持するか (再掲) データベース ---- -------- File Log DB DB
49. Copyright © 2015 every, Inc. All rights reserved. 49 各種AWSサービス： RDB AWSサービス • RDS ◦ 完全マネージド型のサービス ▪ リレーショナルデータベースのセットアップ、運用、スケーリングなどを簡素化 ◦ 高可用性と自動バックアップ、リードレプリカなどの機能により、データベースの信頼性とパフォー マンスを向上させる RDS
50. Copyright © 2015 every, Inc. All rights reserved. 50 各種AWSサービス： KVS AWSサービス • ElastiCache for Redis ◦ 完全マネージド型のインメモリデータストア ◦ 高速なキャッシュとデータベース機能を提供している ◦ ミリ秒単位の低レイテンシでデータにアクセスでき、アプリケーションのパフォーマンスを大幅に向 上させる ◦ レシピの材料や作成者などはRDS上で別のtableに保存されている ⇨Redisではまとめて一つのオブジェクトとして保存 ElastiCache
51. Copyright © 2015 every, Inc. All rights reserved. 51 各種AWSサービス：オブジェクトストレージ AWSサービス • S3 ◦ ログや動画などのメディアデータが保存されている ◦ 高い耐久性（99.999999999%）を誇り、どんな規模のデータでも低コストで保管できる ◦ バージョニングやライフサイクル管理など、データ管理のための豊富な機能を提供する S3
52. Copyright © 2015 every, Inc. All rights reserved. 52 デリッシュキッチンのデータ基盤 デリッシュキッチンのデータフロー BigQuery S3 RDS クライアントのログ マスタデータ ファイル・ログ 外部APIなど データレイク：データ統合と大規模分析 機械学習(ML)：AIモデル開発と活用 DevOps, CI/CDなどフローの管理 生データ ビジネスデータへ ビジネスロジック QuickSight 分析ツール
53. Copyright © 2015 every, Inc. All rights reserved. 53 Databricks デリッシュキッチンのデータフロー https://www.databricks.com/jp/product/open-source データ分析と AI ソリューションを大規模に構築、デプロイ、共有、保守するための 統合されたオープンな分析プラットフォーム
54. Copyright © 2015 every, Inc. All rights reserved. 54 Q. サーバーの基盤とデータの基盤はなぜ分離するのでしょうか？　 例えばMasterデータはサーバーにあるのに、データ基盤にもMasterデータをコピーして用 意します。それはなぜだと思いますか？ Question デリッシュキッチンのデータフロー
55. Copyright © 2015 every, Inc. All rights reserved. 55 Q. サーバーの基盤とデータの基盤はなぜ分離するのでしょうか？　 例えばMasterデータはサーバーにあるのに、データ基盤にもMasterデータをコピーして用 意します。それはなぜだと思いますか？ A. データ分析・集計をするのにサービス上のDBに負荷をかけたくないため Question デリッシュキッチンのデータフロー
56. おすすめの書籍
57. Copyright © 2015 every, Inc. All rights reserved. 57 おすすめの書籍 1. 達人に学ぶDB設計徹底指南書 第2版 2. 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ 3. ビッグデータ分析・活用のための SQLレシピ 4. Apache Spark徹底入門