はじめてのRDB

2020/06 T.Takeda

データベース基礎

Database(DB)とは

  • データを蓄えて、いい感じに追加/更新/検索/削除するための仕組み
    • （データが大量にあっても）速いとうれしい！
    • いろんな条件で検索したい！
データベースイメージ

DBの活用どころ

形態適したデータ量
File
Text		数サンプルのデータ
例えば、疫学、臨床試験データなど
File
Excel/CSV		数MB級のデータ
アンケート結果、実験データ、エントリーシートなど
DatabaseMB〜GB級のデータ
社員データ、在庫管理システムなど
File Storage
Raw/AVRO/Parquet/ORC		GB〜TB級のデータ
顧客データ、Webページビュー履歴など
File Storage
+Compressed		TB〜PB級のデータ
SNS、センサーデータ、画像データなど

データの持ち方

  • テキスト、DB、ファイルなどデータ量、用途に合わせて選択する
    • 少量のデータや膨大な画像をDB管理にすると使い勝手が悪い

RDBとは

  • Relational Database
    • 関係モデルを用いて設計されたデータベース
    • テーブルとテーブルの関係を考慮して設計する
RDBイメージ

代表的なDBモデル

  • RDB
    • 関係データモデル
    • 複雑な関係性の固定構造をもつ大量データに強い
    • MySQL、PostgreSQL、AWS Auroraなど
  • NoSQL（RDB以外）
    • Key-Value形式
    • シンプルな関係性の自由構造の大量データ処理に強い
    • MongoDB、AWS DynamoDB、GCP BigTableなど
💡NoSQLがよいかRDBがよいかはケースによる

SQL（Structured Query Language）

  • リレーショナルデータベースのクエリ言語、あるいはその実装
    • 厳密にはSQL = リレーショナルデータベースではない
  • 本資料ではNoSQLの話はしない
    • SQLのベストプラクティスとかなり異なる

DB定義（DDL）

Data Definition Language

  • データオブジェクトを作成（CREATE）
  • データオブジェクトを削除（DROP）
  • データオブジェクトを変更（ALTER）
  • 全行データを削除（TRUNCATE）

データ操作（DML）

Data Manipulation Language

  • データを挿入（INSERT）
  • データを更新（UPDATE）
  • データを削除（DELETE）
  • データを検索（SELECT）

データ制御（DCL）

Data Control Language

  • 権限を付与（GRANT）
  • 権限を剥奪（REVOKE）
  • トランザクションを開始（BEGIN）
  • トランザクション処理を確定（COMMIT）
  • トランザクション処理を取り消す（ROLLBACK）
  • トランザクションを終了（END）

正規形

データベースの管理や更新を楽にするためのプラクティスの一つ

  • 第一正規形
  • 第二正規形
  • 第三正規形
💡第三正規形くらいまでは知っておき、普段から意識する

第一正規形

データがスカラ値になっていること

  • 値として、表、配列、カンマ区切りの値などが入っているのは非正規形
第一正規形の図
💡スカラ値でなければ、そもそもRDBに格納する意味がない状態
💡交差テーブルを用いないJaywalkingなどを引き起こす

解消するためには、行として分解する

グループ社員群
グループ1社員A,社員B,社員C
グループ2社員B,社員C

グループ社員群
グループ1社員A
グループ1社員B
グループ1社員C
グループ2社員B
グループ2社員C

第二正規形

第一正規形 + 非キー属性が候補キーに対して完全従属

第二正規形の図

重複行を解消し、対応を整合させる

社員番号氏名出勤時刻
社員A武田09:30
社員A武田09:15
社員B山田08:00

社員番号氏名
社員A武田
社員B山田
社員番号出勤時刻
社員A09:30
社員A09:15
社員B08:00
💡氏名が社員番号に部分従属している
💡第二正規形では、"武田"が改名しても勤務表には無関係

第三正規形

第二正規形 + 非キー属性が候補キーに対して推移的関数従属していない

第三正規形の図

テーブルを切り離し、従属関係を解消する

社員番号組織責任者
社員A組織A社員E
社員B組織B社員F
社員C組織C社員G
社員D組織D社員E

社員番号組織
社員A組織A
社員B組織B
社員C組織C
社員D組織D
組織責任者
組織A社員E
組織B社員F
組織C社員G
組織D社員E
💡社員番号→組織、組織→責任者は決まるが、
社員番号→責任者は本来決まらない関係性
💡= 責任者は社員番号に推移的に関数従属している状態

後続の正規形

  • ボイスコッド正規形
  • 第4正規形
  • 第5正規形
💡やりすぎると利便性やパフォーマンスを犠牲にすることになる

キーに関して

プライマリーキー

  • テーブルごとに一つ定義された候補キー
  • 主キー以外の候補キーは代理キーという

自然キーと人工キー

  • 格納すべき値をキーとしたものを自然キーという
    • 社員番号など値そのもの
  • 利用者が必要としないがシステム内部で生成したキーは人工キー
    • 重複を避けるためのUUIDなど

代替キー（Surrogate Key）

  • 自然キーの一意性の担保が難しい場合、更新が多い場合に代替キーを用いることがある
💡例えば、ユーザー名が変更可能な場合にユーザー名を主キーとすると
ユーザーの一意性を担保できない場合
  • テーブル間の主キーによる依存を断ち切ることになる（Relational DBにも関わらず）
💡NoSQLではキーは一意にするためにUUIDを用いることは一般的

単一キーと複合キー

  • 単一キーは単体で一意になるキー
  • 複合キーは主キーがすべて揃わないと一意にならない

代替キーかつ単一キー

UUIDが代表例

  • Pros
    • 複合キーを使わず、単一のキーにできる
    • uuidでクエリーをかければSQLがシンプルになる
    • 主キーが値の更新の影響を受けなくなる
  • Cons
    • 意味のない値を管理する必要がある
    • 一意性の担保をアプリケーションが担う
    • テーブル間の依存が見えづらくなる

外部キー制約

  • DB間の関係を設定し、データの整合性の保証をDBに任せる
    • 親テーブルにない情報を子テーブルに登録しようとするとエラー
    • 子テーブルに存在するデータを親テーブルから消そうとするとエラー

親テーブル

社員番号組織
社員A組織A
社員B組織B
社員C組織C
社員D組織D

子テーブル

組織責任者
組織A社員E
組織B社員F
組織C社員G
組織D社員E
💡データ構造として保証すべきことを、アプリケーションで保証する必要がなくなる

  • 追加、更新、削除の操作順序を意識する必要がある
    • 親テーブルを作成してから子テーブルを作成する
    • 子テーブルから削除して親テーブルから削除する
社員番号組織
社員A組織A
社員B組織B
社員C組織C
社員D組織D
組織責任者
組織A社員E
組織B社員F
組織C社員G
組織D社員E
💡子テーブル操作時に親テーブルのロック状態を意識しないとデッドロックする

プログラミング言語からのSQLの利用

SQLとプログラミング言語

  • インピーダンス・ミスマッチ
    • SQLはデータ操作に特化したクエリー言語
    • オブジェクト指向言語はデータをオブジェクトに集約したい
  • ジレンマ
    • クエリーを最適化するとオブジェクト指向でなくなる
    • オブジェクトとして扱いやすくするとクエリーが複雑になる

SQLと関数型言語

  • 集合に対しての操作として思考が似ている
    • SQLにループや代入はなくてよい
    • 関数型言語にループ処理や再代入はなくてよい
  • 宣言型プログラミング言語
    • クエリーはデータの取得方法を宣言
    • 関数型言語は関数適用を宣言
  • 数理論理学に基づく言語（圏論からみれば同じもの）
    • 関数型言語は関数を扱う（トポス）
    • SQLは集合を扱う（集合論）

DAO、ORM

  • DAO
    • Data Access Object
    • オブジェクト指向言語からデータアクセスを行うためのデザインパターン
    • data.pyはどちらかというとこちらの思想
  • ORM
    • Object-Relation Mapping
    • Mapper/マッパーと呼ばれる
    • Pythonでは、sqlalchemyが代表的

行指向/列指向/ドキュメント指向

データの持ち方により、保存方法、取得方法、演算方法の効率に影響する

行指向

  • 大量の列に対する処理が効率的に行える
  • 行を絞ってデータを部分的に取得できる
  • MySQLなど
UserItem1Item2……ItemN
A101100011
B001110100
💡ユーザーごとに購買アイテムを参照したい、など

列指向

  • 大量の行に対する処理が効率的に行える
  • 列を絞ってデータを部分的に取得できる
  • SAPなど商用に多い
Class1Class2
11
01
00
10
10
01
10
💡授業の履修状況を確認したい、など

ドキュメント指向

  • 階層構造データをそのまま保持
  • XMLやJSONのようなオブジェクト構造
{
  'name': 'Takeda',
  'id': '94T0115J',
  'organization': 'cjgg'
}

💡持つ情報が個別に異なる、スパースなデータ、など

三値論理

三値論理はルール化して避けた方がよい

  • True/False/NULL
  • str/empty/NULL
  • NULLはSQLでうまく扱えないので原則入れない方がよい
    • =演算子はNULLとの演算結果は不定
    • IS NULLとする
💡JavaScriptのundefined/nullやPythonのNullも同じ
💡HaskellならMaybeモナドでうまく扱える

インデックス

  • テーブルに対して貼られる行の番号
  • インデックスでのNULL利用はパフォーマンスが低下する

NULL

  • ということで、使用を避ける

セキュリティ

DBの重要性

  • 一番重要な情報が格納されている場所
  • サービスのアカウントやパスワードが狙われるが、真の目的はその先の情報
  • 情報が漏洩、改ざん、損失してはいけない
💡そのためにセキュリティ対策、冗長化が必要
💡コストに直結するため、RTO/RTPを適切に設定する

SQLへの攻撃

  • SQLインジェクション
    • 外部から与えられた値がクエリーに含まれる場合、不正なSQLを実行できる可能性がある
    • 必ずエスケープし、ただの文字列として処理する
    • 実際にこれができる状態のサービスも世にある
  • 日本語のテーブル名、項目名は海外からの攻撃に強い
    • かもしれない・・？
    • utf8mb3 -> utf8mb4にしよう

よりよいRDBライフを！