データベース研修【MIXI 23新卒技術研修】

23新卒技術研修で実施したデータベース研修の講義資料です。
動画も後ほど公開予定です。

資料の続き（SQL演習編）：
https://speakerdeck.com/mixi_engineers/2023-database-training-02-sql

資料の利用について
公開している資料は勉強会や企業の研修などで自由にご利用頂いて大丈夫ですが、以下の形での利用だけご遠慮ください。
・受講者から参加費や授業料などを集める形での利用（会場費や飲食費など勉強会運営に必要な実費を集めるのは問題ありません）
・出典を削除または改変しての利用

Transcript

1. 23新卒研修
   データベース研修
   2023.04.24
   

   View Slide

2. 本日のお品書き
   • 11:00〜12:00 座学-データベース基礎
   • 12:10〜13:00 SQL入門演習 前半
   • 13:00〜14:00 お昼休憩
   • 14:00〜15:00 SQL入門演習 後半
   • 15:10〜17:30 分析SQL Challenge
   2
   

   View Slide

3. 講師紹介
   • 渡辺 大貴 (Taiki Watanabe)
   • デジタルエンターテインメント事業本部 モンストゲーム運営部 解析G
   • 北島 祥伍 (Shogo Kitajima)
   • 吉村 健矢 (Takuya Yoshimura)
   3
   

   View Slide

4. 座学 - データベース基礎
   4
   

   View Slide

5. データベース基礎 目次
   I. データシステム基礎
   1. なぜデータベースを学ぶのか
   2. データシステム
   3. データモデル
   4. データベース管理システム
   5. エンコーディングと進化
   Ⅱ. 分散データの取り扱い
   6. レプリケーション
   7. パーティショニング
   8. トランザクション
   5
   

   View Slide

6. I. データシステム基礎
   6
   

   View Slide

7. 1. なぜデータベースを学ぶのか
   • 演算指向からデータ指向へシフト
   • CPU処理能力ではなく、データの量や複雑さ、変化がボトルネックになる時代
   • データベースはアプリケーションに比べて後から変更しにくい
   • 安易なデータモデル・DBMSの選定はビジネス要件とのミスマッチにより開
   発、運用段階で苦労するリスク大
   => トレードオフを理解したデータシステムの適切な技術選定の必要性
   7
   

   View Slide

8. 2. データシステム
   データシステム設計の機能要件と非機能要件
   8
   

   View Slide

9. 2. データシステム
   アプリケーションを”良く”保つためには機能要件と非機能要件どちらも欠かせない
   機能要件
   • データの保存・検索（DB)
   • よく参照されるデータの記憶（キャッシュ）
   • キーワード検索
   非機能要件
   • 障害発生時もデータが正しいことを保証したい（信頼性）
   • 負荷増大に対応したい（スケーラビリティ）
   • 安定したパフォーマンスを提供したい（メンテナンス性）
   9
   

   View Slide

10. 2.1 信頼性
    「何か問題が生じたとしても正しく動作し続けること」
    • 問題を起こしうるもの : フォールト(fault)
    • フォールトを見越してこれに対処できるシステムは
    耐障害性を持つ(fault tolerant)という
    • フォールトに耐性がないと障害につながる
    • 意図的にフォールトを発生させて耐障害性の仕組みを
    継続的にテストする => カオスエンジニアリング
    10
    

    View Slide

11. 2.2 スケーラビリティ
    「負荷の増大に対してシステムが対応できる能力」
    • スケールアップ: マシンを強力に
    • 垂直スケーリング
    • スケールアウト: 負荷分散
    • 水平スケーリング
    • one-size-fits-all なアーキテクチャは存在しない
    11
    １TB
 3TB

    スケールアップ
    １TB
 １TB x３

    スケールアウト
    

    View Slide

12. 2.3 メンテナンス性
    「劣悪なソフトウェアの制約は、しばしば優れた運用によって回避できるが、
    運用が悪ければ優れたソフトウェアも信頼性を保って動作することはできない*」
    メンテナンス性を高めるための設計原則
    1. 運用性
    • 健全性を可視して効率的な管理方法で運用しよう
    2. 単純性
    • 複雑なシステムはメンテナンスのコストを増大
    • 抽象化でクリーンなアーキテクチャを保とう
    3. 進化性
    • システムの修正容易性
    • 単純性の高さだけでなく, 開発技法も影響
    *Jay Kreps — Getting Real About Distributed System Reliability
    12
    

    View Slide

13. 3. データモデル
    実世界を抽象化する手段
    13
    

    View Slide

14. 3. データモデル
    「実世界を抽象化するための手段」
    • データベース世界の汎用的なデータモデル
    • リレーショナルデータモデル
    • オブジェクト指向(ドキュメント)モデル
    • ネットワークデータ(グラフ)モデル
    • ソフトウェアのできること/できないことに大きな影響を及ぼす
    • アプリケーションに適したデータモデルを選択することは重要
    14
    

    View Slide

15. 3.1 リレーショナルデータモデル
    「SQL≠リレーショナルモデル」
    • 数学的で徹底的にフォーマルなデータモデル
    • 集合論に基づいてデータを表現
    • 高度に抽象的(実装に無関心) => データ独立性
    • データモデルは原理
    • 原理：根源、本質的な性質、基礎
    • 製品や技術は変化するが原理は持続力がある
    • リレーショナルモデルの３要素
    • 構造：リレーション、順序なしタプルの集合
    • 操作：集合操作と論理述語
    • 整合性：常に満たしていなければならない式
    15
    

    View Slide

16. 3.1.1 クエリ言語
    • データの問い合わせ(Query)の手段 => クエリ言語
    • 宣言的なクエリ言語はDBエンジンの実装の詳細を隠蔽
    • クエリの書き換えなしにDBシステムのパフォーマンスを改善できる
    • クエリ言語：SQL, MapReduce, Cypherなど
    SQL
    • リレーショナルモデルを設計基盤として開発された宣言型クエリ言語
    • 集合を基本のデータ構造とする
    • OOMとはデータ構造の違い(インピーダンスミスマッチ)が存在
    16
    

    View Slide

17. 3.2 ドキュメントモデル
    「データをJSONとして保存」
    • スキーマを強制しない
    • ローカリティ(局所性)に優れている
    • 関連情報が一箇所に集まっている
    • 一対多のツリー構造では結合が必要がない
    • 結合のサポートは弱い
    • OSS: MongoDB, CouchDB
    • Cloud: Google Cloud Firestore
    17
    MongoDBにおけるデータの挿入と抽出
    > db.users.find()
    {
    "_id" : ObjectId("60e..."),
    "name" : "John",
    "age" : 23
    }
    > db.users.insert(
    {"name": "John","age": 23}
    )
    

    View Slide

18. 3.2.1 Schema
    スキーマオンリード
    • データベースがスキーマを強制しない
    • データ構造は暗黙 => 読み取り時に解釈
    スキーマオンライト
    • スキーマを明示
    • 書き込み時にスキーマに従っていることを保証
    • スキーマの変更にマイグレーションが必要
    18
    

    View Slide

19. 4. データベース管理システム
    DBMS - DataBase Management System
    19
    

    View Slide

20. 4. データベース管理システム(DBMS)
    「DBMSはクライアント/サーバーモデル」
    • 右図はDBMSのアーキテクチャの例
    • 製品によってはモジュールの付け替え可
    • 特にストレージエンジンは保証するトラン
    ザクションのレベルに大きく影響(後述)
    • 大きく分けて2種類の用途がある
    1. トランザクション処理用途(OLTP)
    2. 分析処理用途(OLAP)
    20
    クライアント
    リクエスト(Query)/レスポンス
    DBMS
    クエリプロセッサ
    実行エンジン
    ストレージ
    エンジン
    - クエリパーサ
    - クエリオプティマイザ
    (Output: クエリ実行計画)
    (実行計画を処理 )
    - トランザクション
    マネージャ
    - ロックマネージャ
    - アクセスメソッド
    - エンドユーザー
    - アプリケーションプログラム
    DBMS アーキテクチャの例
    

    View Slide

21. 4.1 OLTP ( Online Transaction Processing )
    • エンドユーザーとやり取りするインタラクティブな用途で利用
    • ランダムアクセスと低レイテンシーな書き込みが求められる
    • 大量データから特定の値を効率的に見つけることが得意
    • => インデックス
    • OLTPで主流のストレージエンジンは2つ
    1. Bツリー系: update-in-place. 最も一般的.
    2. log-structured系: ファイルへの追記と削除のみ.
    21
    

    View Slide

22. 4.2 OLAP ( Online Analytic Processing )
    「結果がビジネスインテリジェンス(BI)のために利用される」
    • 分析用途のクエリーは高負荷&データセットの大部分をスキャン
    • パフォーマンスへ大きく影響
    • 分析用途に特化した独立したDB：データウェアハウス(DWH)
    • データウェアハウスはリードに最適化. データのスキャン範囲を抑える工夫.
    • => 列指向
    22
    

    View Slide

23. 4.2.1 列指向
    「列に含まれるすべての値をまとめて保存」
    • 集計処理の際、必要な列のデータのみ取
    り出せる
    • SELECT AVG(age) が強い
    • SELECT * で全データスキャン
    • 圧縮しやすい
    • 連続するデータは連長（ランレングス）圧縮が
    有効
    • カーディナリティ(濃度)が低いならビットマッ
    プエンコーディングが使える
    • 書き込みは苦手
    23
    name age
    Mike 25
    Alice 23
    John 21
    列方向にデータを保存
    イメージ↓
    name file contents: Mike,Alice,John
    age file contents: 25,23,21
    

    View Slide

24. 4.2.2 ランレングスエンコーディングとビットマップエンコーディング
    取りうる値に対応するビットマップ:
    age=25: 1,0,0,…
    age=23: 0,1,0,…
    取りゆる値の種類が多い場合:
    ビットの並びが疎になるため, 同じ値が連続する
    ランレングスエンコーディング:
    age=25: 0,2,… ( 0 zeros, 2 ones, rest zeros )
    age=23: 1,1,… (1 zeros, 1 ones, rest zeros )
    24
    name age
    Mike 25
    Alice 23
    John 21
    WHERE age IN (23, 24)
    のような検索が効率的.
    age=23, age=24に対応する2つのビット
    マップをロードしてORを取るだけ
    

    View Slide

25. 4.3 DBMS比較表
    25
    種類 データ型 表現 トランザクション 特徴
    PostgreSQL Relational 事前定義型 テーブル Yes + 有名なOSS RDBMS
    - 分散時の可用性
    HBase Colomnar 事前定義型 列 Yes(optional) + 大規模スケール
    - フレキシビリティ
    MongoDB Document 型あり JSON No + “ビックデータ”の操作
    - クエリの表現力
    CouchDB Document 型あり JSON No + 永続性と組込
    - クエリの表現力
    Neo4j Graph 型なし ハッシュ Yes +柔軟なグラフの扱い
    - BLOB,TBスケール
    DynamoDB Key-value
    (plus)
    型あり テーブル No + 高可用性
    - クエリの表現力
    Redis Key-value 準型あり 文字列 Yes + 超高速
    - 複雑なデータの扱い
    

    View Slide

26. 5. エンコーディングと進化
    26
    

    View Slide

27. 5. エンコーディングと進化
    「アプリケーションの変化に伴い、データも変化する」
    エンコーディングによって互換性を担保
    • エンコーディング
    • 「インメモリの表現からバイトの並びへの変換」
    • メモリ内のデータの持ち方とメモリ外でのデータの持ち方は異なる
    • 後方互換性
    • 古いコードによって書かれたデータを新しいコードが読める
    • 前方互換性
    • 新しいコードによって書かれたデータを古いコードが読める
    • 難しい
    27
    

    View Slide

28. 5.1 データエンコーディングフォーマットの種類
    1. プログラミング言語固有のフォーマット
    • java.io.Serializable, pythonのpickleなど
    • 他プログラミング言語との互換性が(ほぼ)ない
    2. 標準化されたフォーマット
    • 多くのプログラミング言語で読み書きできる
    • テキストフォーマット: JSON, XML, CSVなど
    • バイナリフォーマット: Thrift, Protocol Buffers, Avro
    28
    

    View Slide

29. 5.1.1 テキストエンコーディングの種類
    • JSON, XML, CSVなど
    • テキストなのでデータサイズが大きくなりがち
    • スキーマは組み込まれていない
    • 好きなスキーマでデータを格納できる
    • 合意形成がなされていれば問題は発生しない
    • 「ただし、それが何であれ、何かについて複数の組織間が合意するのは難しい」
    29
    

    View Slide

30. 5.1.2 バイナリエンコーディングの種類
    「テラバイト級のデータを扱うために開発されたデータエンコーディング」
    • Apache Thrift (Facebook), Protocol Buffers(Google), Apache Avroなど
    • スキーマを必要とするエンコーディング
    • スキーマ情報はドキュメントやコードの自動生成にも利用可能
    • JSON, XML用のバイナリエンコーディングもある
    • JSON用にはMessagePack, BSONなど
    • XML用にはWBXML, Fast Infosetなど
    30
    

    View Slide

31. Ⅱ. 分散データの取り扱い
    レプリケーション, パーティショニング, トランザクション
    31
    

    View Slide

32. 6. レプリケーション
    「複数のマシンに同じデータのコピーを保持しておくこ
    と」
    レプリケーションの目的
    1. レイテンシを下げる
    2. 障害があってもシステムを動作させる (可用性の向上)
    3. スケールアウトさせる (スループットの向上)
    レプリケーションの手法
    - 3つのアプローチ: シングルリーダー(Leader), マルチリーダー,
    リーダーレス
    - 同期的か非同期か
    - 障害を起こしたレプリカの扱いをどうするか
    32
    Amazon RDSにおけるレプリケーションのイメージ
    引用：「Amazon RDS リードレプリカ | クラウドリレーショナルデータベース
    | アマゾン ウェ
    ブ サービス」
    https://aws.amazon.com/jp/rds/features/read-replicas/
    

    View Slide

33. 7. パーティショニング (シャーディング)
    33
    「データを分割して保存」
    • スケーラビリティ向上が主な目的
    • クエリの負荷分散
    • “良い”パーティショニングはデータとク
    エリの負荷をノード間で均等に分散さ
    せる
    • 偏りがある状態：skew
    • 負荷集中しているパーティション：
    ホットスポット
    アルファベットでパーティショニングする例
    引用：「Data partitioning guidance - Best practices for cloud applications | Microsoft Docs」
    https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
    

    View Slide

34. 8. トランザクション
    34
    「複数の読み書きを一つの論理的な単位としてまとめる方法」
    • 「プログラミングモデルをシンプルにする」という目的で生まれた
    • 全体として成功 or 失敗
    • 成功 (commit:コミット) , 失敗 (abord:中断, rollback:ロールバック)
    • 一部の操作だけ成功という状態は存在しない
    • トランザクションが保証する一貫性の強さとパフォーマンスはトレードオフの関係に
    ある
    • =>トランザクション分離レベル
    

    View Slide

35. 8.1 トランザクション分離レベルとは
    35
    「あなたのトランザクション分離性は何を保証しているか」
    • 理想は並列で実行しても直列で実行した時と同じ結果になること
    • しかし、パフォーマンスの問題からある種の並行性問題のみを対処する弱い分離
    性が提供されている
    a. Read Commited
    b. Snapshot Isolation (Repeatable Read)
    c. Serializable
    

    View Slide

36. 8.2 トランザクション分離レベルの種類
    36
    Read Commited
    • ダーティリード, ダーティーライトが発生しないことを保証
    a. ダーティーリード: コミットされていないデータが見れてしまうこと
    b. ダーティーライト: コミットされていないデータを上書きすること
    Snapshot Isolation (Repeatable Read)
    • トランザクション中に他トランザクションによる変更を読み取らないことを保証.
    • nonrepeatable read (read skew) が発生しない
    Serializable
    • すべての更新不整合を回避. ただしパフォーマンスが悪い.
    

    View Slide

37. 8.3 べき等性 idempotency
    37
    「冪等な操作とは、複数回実行することができ、その影響が一度だけ実行したときと
    同じになるような操作のこと」
    • 「リトライすれば解決できる」状態を実現することが目標
    • データストアの操作やサーバーへのリクエストなどで特に重要
    • ただし、むやみリトライはサーバーへの負荷となり逆効果
    • Exponential backoffで軽減できる
    参考:
    - https://aws.amazon.com/jp/builders-flash/202104/serverless-idempotency/
    - https://aws.amazon.com/jp/blogs/architecture/exponential-backoff-and-jitter/
    

    View Slide

38. 座学END
    お疲れさまでした！
    38
    

    View Slide

39. 参考書籍
    39
    • O'Reilly Japan『データ指向アプリケーションデザイン ―信頼性、拡張性、保守性の高い分散システム設
    計の原理』
    • O'Reilly Japan『データベース実践講義 ―エンジニアのためのリレーショナル理論 』
    • O'Reilly Japan『詳説 データベース―ストレージエンジンと分散データシステムの仕組み』
    • O'Reilly Media, Inc.『Fundamentals of Data Engineering』
    • Pragmatic Bookshelf 『Seven Databases in Seven Weeks, Second Edition』
    

    View Slide