Skip to content

Instantly share code, notes, and snippets.

@bz0

bz0/DB設計.md

Created January 4, 2020 02:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bz0/5715bd6813f3bd6dd2e035b7935659c9 to your computer and use it in GitHub Desktop.
Save bz0/5715bd6813f3bd6dd2e035b7935659c9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
DB設計.md

DB設計

「気をつけよう」とか心構えの問題で片付けてしまうのか、 実際のルール作りまでするかの違いを言っているんじゃない？ https://srad.jp/story/13/03/08/0313213/

ストアドプロシージャ： DB上での一連処理に, 名前をつけて関数のように, 呼び出して使用できるもの https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46

トランザクション： 複数の並列に実行された更新を矛盾なく行うための理論 https://qiita.com/yoheiW@github/items/8a5326a516ec4452e774

ACID特性：

  • 原子性 (Atomicity)
    • トランザクションに含まれるタスクが全て実行されるか、全く実行されないことを保証する
  • 一貫性 (Consistency)
    • トランザクション開始と終了時に整合性を満たすことを保証する
  • 分離性 (Isolation)
    • トランザクション中に行われる操作の過程が他のトランザクションから隠蔽される
  • 持続性 (Durability)
    • トランザクション操作の完了通知をユーザが受けた時点で、その操作は永続的となり、結果が失われない

リレーショナルモデルとは？：

  • 述語論理のルールに支配されたデータモデル
  • 述語論理にとって厄介なことは「矛盾」

正規化

  • リレーショナルモデルを前提として構築されたDB設計理論
    • リレーション：テーブル同士の関係を設定し、関連付けるもの
  • リレーションを正規化することによるメリット
    • 矛盾を防ぐことができる
    • 矛盾：データが論理的に不整合を起こしてる状態
    • 矛盾の原因は重複
    • 重複を排除するための正規化理論

考え方

  • よく出てくるワードの意味
    • タプル：行
    • 属性：カラム
    • リレーション：データを関連付けているカラムの集合のこと
  • 候補キーとスーパーキー
    • https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11154990481
    • スーパーキー：データベースのレコードを一意（一行）に特定できる、カラムの組み合わせのこと。最小である必要はなく、冗長でも構わない。
    • 候補キー：スーパーキーのうち、必要最小限のカラムの組み合わせのこと。
  • 関数従属性
    • Aの値が分かればBの値が分かる
    • Bは重複した値でもOK
    • 第2正規化～ボイスコッド正規形までは「自明でない関数従属性」を排除する作業
    • 自明な関数従属性：Aが分かればBの値が求められる
      • {x,y}→{x}・{x,y}→{y}
    • 自明でない関数従属性：
      • {x,y}→{z}は求められない

正規形の段階

  • 第1正規形
    • 要件：
      • １．カラムや行に入れるデータの順番に依存しない
      • ２．重複する行をなくす
        • 格納されている値が重複していない
        • 主キー・ユニークキーといった一意制約をつける
      • ３．NULLが含まれてはいけない
        • NOT NULL制約
        • テーブルを分割する
      • ４．値のアトミック性
        • アトミックな値＝それ以上分離できない値
        • 分離できないといっても住所をバラバラに切り分けることが絶対必要ではない
        • アプリケーションが扱う可能性のあるデータ型がどのようなものであるかを考えること
      • ５．繰り返しグループでない
        • 繰り返しグループ：カンマ区切りの値を割り当てる等
        • 複数行にでーたを登録 / 中間テーブルの作成等
  • 第2正規形
    • 要件：
      • 無損失分解（候補キーと非キーカラムの分解）
        • 「自明でない関数従属性」は重複である
        • 重複を解消するためリレーションを分解して別テーブルにする
        • 分解したテーブルとJOIN（結合）すると元に戻ること
  • 第3正規形
    • 要件：
      • 無損失分解（非キーカラム間の分解）
        • 非キーカラム間の無損失分解を行う
  • ボイスコッド正規形
    • 要件：
      • これ以上は関数従属性による無損失分解ができない状態
      • 非キー→候補キーへの関数従属性
  • 第4正規形
  • 第5正規形

運用

外部キー制約： https://qiita.com/SLEAZOIDS/items/d6fb9c2d131c3fdd1387 https://www.atmarkit.co.jp/ait/articles/0707/17/news113_2.html http://kamipo.github.io/talks/20150213-fk_night/#/step-22

  • 他のテーブルのデータに参照（依存）するようにカラムにつける制約

  • 参照整合性を保とうとする

    • 親テーブルと子テーブルの整合性を保つ
    • 色々と設定は可能
      • ON DELETE SET NULL：参照先deleteすると参照元をnullにする
      • ON UPDATE CASCADE：参照先をupdateすると参照元も同じupdateがされる

  • デメリット

    • シャーディングされたテーブルには外部キー制約はつけれない
    • ON UPDATE CASCADEなどをきちんと認識しないと意図せぬ更新もありえる
    • ON DELETE CASCADEなどをきちんと認識しないと意図せぬ削除もありえる
    • INSERTするときに親テーブルに参照先がないとINSERTできない
    • 親テーブルを同時にロックするのでデッドロックの発生が生まれやすくなる
    • そもそもMySQLのMyIsamなど外部キー制約を張れないのものもある（張れても効かなかったはず）

よく使う制約

  • 制約の検査における影響範囲が限定的
  • NOT NULL 制約
  • UNIQUE 制約
  • PRIMARY KEY 制約

あまり使われない制約

  • FOREIGN KEY 制約
  • CHECK 制約
    • MySQLでは使えない(☝◞‸◟)☝

MySQLのクエリの良し悪しはrows_examinedで判断する http://b.hatena.ne.jp/entry/s/blog.kamipo.net/entry/2018/03/22/084126

  • primary:
    • 主キー制約:
    • カラムの重複を許容しない + NOT NULL
    • プライマリーキーを変更する
      • ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (id);
  • unique:
    • カラムの値を重複させない
    • NULLを許容
    • ユニークインデックスの設定
      • alter table 対象テーブル add unique (対象フィールド);

キー制約とインデックス： http://d.hatena.ne.jp/bi_na/20120218/1329530104

  • 主キーやuniqueキーを表定義で指定する→内部的に重複禁止のインデクスが作成される
  • RDBMS側で重複チェックする上で、重複禁止のインデックスがないと母体の全件サーチが必要になる
  • 参照（整合性）制約の外部キーには、自動的にインデクスは作成されない
  • そのため外部キーにインデックスを定義すべき

疑問

メールアドレスの長さ

MySQLのメールアドレスのvarcharの長さはいくつが最適ですか？ https://teratail.com/questions/79080

  • メールアドレスの長さの定義としては RFC 5321（Simple Mail Transfer Protocol）
    • ローカル部（@の前）64オクテット
    • ドメイン部（@の後）255オクテット
    • 全体 256オクテット
  • 最大で256文字あればよい

主キーの文字数制限

innodb_large_prefixを使ってERROR 1071を回避する https://yakst.com/ja/posts/734

  • 文字数制限は、使っている文字コードに依存
  • utf8 の場合は varchar(255) まで
  • InnoDBの767バイトを超えるカラムにインデックスをはりたい場合
    • innodb_large_prefix

主キーを識別用以外に使わない https://qiita.com/jkr_2255/items/5a71ff5f8569c5e0f24d

  • 主キーが持つべき性質：
    • 重複しない：主キーが重複してはレコードの識別子でなくなる
    • 扱いやすい：レコードを参照するのにいちいち100文字の主キーで参照するのでは骨が折れます。
    • 永続的：レコードが一度作られれば、そこに振られた主キーは中身が変化しようとも変化せず、レコードを削除しても再利用すべきではありません。
  • 注意
    • 外部の都合で変化・重複しうるものを主キーにしない
    • 主キーが連番なことを期待しない
    • 主キーに項目識別以上の機能を持たせない
    • 複合主キー:主キーに含まれるすべての列が外的に変更されないものでない

電子メールアドレスを主キーとして使用しますか? https://code.i-harness.com/ja-jp/q/3a0bcc

  • RDBMSによっては列の値が大文字と小文字を区別する
  • メルアドを利用するならハッシュの方がいいかも（大文字と小文字を区別しない）
  • メルアドは変更の可能性がある
  • 電子メールは長さが異なる傾向があり、主キー索引には適していない

IDリクワイアド

主キー：

  • テーブルのすべての行が一意であることを保証する
  • 外部キーから参照されることでテーブルの関連付けを行う
  • どのキーを主キーに選ぶかが問題
  • どれも重複してしまう可能性があるから疑似キー（代理キー）使おう
    • 疑似キー：意味を持たない人工的な値
      • MySQLだとAutoIncrement
      • id
  • アンチパターン：全てのテーブルに「id」列を用いる
    • 冗長なキーが作成されてしまう
      • 主キーとして使えそうな列あってもid列作る
    • 重複行を許可してしまう
      • 複合キーを使うべきところでid列を主キーに
      • 一意であるべきキーが重複してしまう可能性
      • 複合キー使えるならid列は無駄
    • キーの意味が分かりづらくなる
      • idだと何を指しているのか分からない
    • USINGが使えない
  • 盲目的にid使わず、分かりやすい名前をまずつけてみよう
  • idは主キーじゃなくて疑似キー
  • 全てのテーブルで疑似キーが必要なわけではない
  • 主キーの付け方
    • １．一意であることが保証できるカラムがないか検討
      • あとから重複を許可するようになることもあるのでUNIQUE制約だけつけとくもアリか
    • ２．あればそのカラムに主キーを設定（UNIQUE+NOT NULL）
    • ３．なければ疑似キーを設定：最適な名前を考える
      • 注）ORM使っていてid使った方がよければidで
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment