【読書メモ】データベースの概要まとめ

読書 DB

読んだ本の個人的メモです。

booklog.jp

リレーショナルデータベース

テーブル単位で管理するデータベース

E-R図

実体(Entity)と属性(Attribute)で表す図

データベースの正規化

第一正規形

一つの行/列に一つの値を。

一つの行に二つの値があったら、それを分離することを指す。

主キー(primary key)

テーブル内の個々の行を区別・識別するための列

外部キー(foreign key)

他のテーブルの主キーを参照する列

この列には、

  • 参照先の主キーに存在する値
  • null

のどちらかを必ず入れる必要がある。

関数従属

「ある列や列の組み合わせによって別の列の値が決まる」こと

例：

伝票番号テーブル

伝票番号 日付 販売先名
1 1/1 A
2 4/1 B
3 5/5 F

主キーは「伝票番号」で、日付と販売先名は伝票番号に関数従属

列Bが列Aに関数従属

A→Bと表記

列Xが列A,B,...に関数従属

{A,B,...}→Xと表記

完全関数従属
  • ある列が、幾つかの列の値に関数従属している
  • 幾つかの列のうちの一部だけでは従属しない

この２つを満たす状態

部分関数従属
  • ある列が、幾つかの列に完全関数従属している
  • と見せかけて、実はそのうちの一部にしか関数従属していないこと
推移的関数従属

A→B B→Cのとき、CはAに推移的関数従属

要は直接ではないが関数従属している状態

第二正規形

主キーに部分関数従属する列を別のテーブルに分けること

というか、完全従属のみ同じテーブルに残す、という表現の方が正しいかも。

なぜ第二正規形にする？
  • 部分関数従属している列があると、データをうまく追加できないため(更新時異常が起こる)

第三正規形

主キーに推移的関数従属する列を別のテーブルに移す。

例：

伝票番号（主キー） 日付 販売先名 販売先番号 販売先住所

伝票番号→日付
伝票番号→販売先番号

販売先番号→販売先名
販売先番号→販売先住所

この場合、販売先名と販売先住所を販売先だけの別のテーブルに。

伝票メイン

伝票番号（主キー） 日付 販売先番号

販売先

販売先番号 販売先名 販売先住所

SQL

文法はかったるいので省略

ユーザ

DBは誰でもアクセスできてしまうと情報漏洩につながる。

そこで、ユーザという概念を用いてアクセス権限を設定する。

権限

各テーブルへの

  • 読み出し
  • 追加
  • 変更
  • 削除

に加えて

  • テーブル作成
  • ユーザ作成
  • 権限作成

の操作を利用可能かどうかの限定ができる。

grant,revoke
  • grant
    権限を与える。
//ユーザsampleにorder_mainテーブルへのselect権限を与える
grant select on order_main to sample
  • revoke
    権限を奪う。
//ユーザsampleからorder_mainテーブルへのselect権限を奪う
revoke select on order_main from sample

ロール

権限の組み合わせのテンプレートのようなもの。

これもgrant/revokeで権限を変える

ビュー

仮想的なテーブル。select文のように実テーブルからデータを集める。

ビューはテーブル同様grant/revokeでユーザへの権限設定ができる。

なぜビューを使う？
  • テーブルを外から隠蔽するため
  • 複雑になったselect文を整理して、見やすくする

制約

テーブル/列に制約を追加

一意性制約

複数の行に同じ値が存在しない制約

uniqueというでセットできる

非null制約

値がnullを禁止する制約

not nullでセット

主キー制約

一位性制約＋not null制約

primary keyでセット

検査制約

入力値の範囲をきめる

create table game(
    point integer check(point >= 0 and point <= 100)
)

参照制約

テーブル結合時、参照先のデータ値のみ入力可能にする

order_subテーブル

order_id item_id count
1 101 20
2 102 15
3 101 10
4 103 12
5 103 10

itemテーブル

item_id name price
101 A5ノート 500
102 A4ノート 600
103 B5ノート 200

もしitemテーブルに存在しない値（id:104とか)をorder_subテーブル代入しようとすると弾く。

参照先テーブルの行を削除・変更した時の処理の自動化

  • no action 参照元テーブルに参照先テーブルの特定の行を参照している行があれば、その行はDelete/Update禁止

  • cascade 参照先テーブルで行がDelete／Updateされたら、参照元テーブル行も連動して自動Delete/Update

  • set null 参照先テーブルで特定の行が削除されたら、参照元テーブルでその値をnullに変更

インデックス

テーブルにつける「索引」

なぜインデックスを作る？

処理の高速化。ただし、あまりに巨大なテーブルに対してはインデックスを作ること自体が重かったりするので注意。

ストアドプロシージャ

RDBMS内にプログラムを仕込んでおいて、外部からそのプログラムを呼び、内部で処理させる仕組み。

メリット

  • 違うプログラムから、ストアドプロシージャを使える
  • 処理の手順を変える時、変更が少ない

デメリット

トリガー

なんらかの発動条件を満たすと、RDBMS内でプログラムを実行する仕組み

  • before trigger
  • after trigger

と、

  • 文単位trigger
  • 行単位trigger

の組み合わせがある。

トリガーの連鎖(cascade)

例；

  • トリガー1:Aが変更されたらBを変更する
  • トリガー2:Bが変更されたらAを変更する

Aを変更→トリガ−1が発動→Bを変更→トリガー2が発動→Aを変更→トリガ−1が発動→Bを変更→...

無限ループ！

トランザクション

一連の処理の単位のこと。

一連動作が全て成功したら変更を確定(Commit)、失敗したら元に戻す(Rollback)する。

ACID特性

  • Atomicity 原子性 トランザクションは全て実行する／全て実行しないの二者択一
  • Consistency 一貫性 トランザクションの前後で整合性がある
  • Isolation 独立性 他のトランザクションから見えない/影響を受けない/実行順番による違いがない
  • Durability 持続性 障害があっても失われない

障害復旧

チェックポイント

ディスクに書き込む処理を、ある程度まとめて、一度に書き込む。
この「書き込む」瞬間のことをチェックポイントという。

ログ

どのような変更をしたかの記録

バックアップ

データベースそのもののデータの保存

復旧手順

同時アクセス

二件同時にテーブルへのアクセスがあった時のこと

ロックで対応する。

ロックの種類

ロックの粒度

ロックの細かさを示す。

  • 行単位
  • テーブル単位

など。

粒度が細かいほど
  • 競合しにくい
  • 処理が複雑で重くなる

2相ロック(2 phase lock)

  • ロックをかけるフェーズ
  • 解除するフェーズ

を分けること。

どんどんロックして行って、処理が終わったらロックを解除する。

デッドロック

ロック解除待ちのまま、その状態が解消されないこと

これがデッドロック。

防止策
  • どちらもX→Yの順にロックさせる
検出と解除

待ちグラフを使って、矢印が一周してたらデッドロックとわかる。（待ちグラフは割愛）

基本的にはどちらかのトランザクションを強制終了させて解消する。

トランザクション分離レベル

トランザクションの処理速度をどれだけ優先しているかの度合いを示す。

共有ロックを多用すると分離レベルが下がる

基本的に、処理速度を上げようとするとエラー、というかデータの不整合が起きやすくなる。

ダーティリード

またコミットされてないデータを読んでしまうこと

ノンリピータブルリード

複数の行を読み出す途中で別のトランザクションがデータを更新してしまい、途中からデータの新鮮さが変わってしまうこと

ファントムリード

複数の行を読み出す途中で別のトランザクションがデータを追加してしまい、途中からデータの量が変わってしまうこと

分離レベルの定義
Level Dirty Read non-Repeatable Phantom
serializable × × ×
repeatable read × × ○
read commited × ○ ○
read uncommited ○ ○ ○

DBの分散

サーバを複数に分けること

分散の種類

垂直分散

メインサーバの下にサブサーバがぶら下がっていること

水平分散

各サーバが対等、外部からは１つのサーバに見える

テーブルのデータの分割

一つのテーブルのデータを複数のサーバに分割して管理

水平分割

テーブルを、行でグループ化して分割

顧客テーブル

name 担当支社
A 関東支社
B 関西支社
C 関東支社
D 中部支社
E 北陸支社

↓

関東支社

name ...
A ...
C ...

のようなイメージ

垂直分割

テーブルを列で分割

商品テーブル

name price 区分 備考 ...

↓

商品メインテーブル

name price

商品詳細テーブル

区分 備考 ...

分散問い合わせ処理

別々のサーバのテーブルを結合したい時、なるべく通信量を少なくする手法

ソートマージ法

２つのテーブルをそれぞれソートさせてから、片方のサーバにデータを渡す

入れ子ループ法

１行ずつ片方に送って、結合

セミジョイン法

必要最小限のデータを送信し合う

分散トランザクション

あるアプリ(主サイト)から、複数のサーバ(従サイト)へトランザクションを実行すること

1相コミットメント

各従サイトにそれぞれ処理をさせるだけ

  • update
  • commit
  • commitへの返答

失敗すると整合性が失われる

2相コミットメント

Secure状態（Commit/Rollback可能)というものをかませてからCommit

  • update
  • secure
  • secureへの返答
  • commit
  • commitへの返答

もしどれか１つでも従サイトからSecureへの返答がなければ、すべてのトランザクションを破棄

2相コミットの問題

主サイトにエラーがあったら、従サイトがSecureへの返答のとこで止まってしまう

メモ書き終わり。

実際はSQLの文法を覚えないとどうしようもないので実践が大事っすね・・