Relational Database
Overview
リレーショナルデータベースに関する情報をまとめたセクション。
- データベース
- データの集積を指す論理的概念
- DBMS
- データベースを実装したソフトウェア（MySQL、Postgresなど）
データベース設計を制するものはシステム開発を指す。
それはシステムがデータのフォーマットに合わせて作られるから
※システムに合わせてデータを作るのではない。
データベースから情報を取得する場合は、膨大なデータをただ眺めていても出てこない。
これは「どのような時期によく買っているか」「どのような種類の商品をよく買っているか」といった観点（文脈）による分析が必要になる。
したがって、情報とはデータと文脈を合成して生まれてくるものと言って良い。
情報とは、データから、ある文脈なり観点なりに従って集約したり加工したりしたものを指すため。
長い歴史を持つ
誕生は1969年まで遡る。
すでに登場してからデータベースのメインストリームの座になる。
データベースに求められる基本機能
- データの検索と更新
- 同時実行制御
更新の整合性をどのように保証するか検討し実現する。 - 耐障害性
障害に強いこと。データが消えないこと。 - セキュリティ データベースに保存されているデータをいかにして隠すか
データベースの基本的な知識
テーブル
エンティティとも呼ぶことがある。
dirty write
更新処理がぶつかった時は「後出しが勝つじゃんけん」みたいなもの。
データベースにおいてはこのような制御はデータ整合性の観点から忌避される傾向にある。
DBセッションの切り方
- mysql
- DBクライアントの接続を切ってもセッションは切れない（SQLは実行されたまま）
- そのためオーバーフローしやすい
- postgres
- DBクライアントの接続を切った場合セッションは切れる（実行されいるSQLがあれば終了する）
リソースの解放
クエリが中止されると、そのクエリによって使用されていたリソース（メモリ、一時的なディスクスペースなど）も解放されることが一般的。
これにより、システムのリソースがムダに消費されることを防ぐ。
ロールバック: トランザクション内で実行されていたクエリの場合、未完了のトランザクションは自動的にロールバックされることが多いです。これにより、データベースの整合性が保たれる。
table 命名規則
一時的なレコードと永続化が必要なレコードを同じテーブルには入れない
構造が同じでも意味が違うデータは分けて管理すべきだと、私は思います。 その方がレコードの行数も少なく抑えられて、注文テーブルを引くコストが下がります。
SQL設計
DB・テーブル設計のプラクティス
SQLアンチパターンを避けるためのチェックリスト①（DB論理設計編）
パスワード保存について
データベースに生の（平文の）パスワードを保存するのは情報セキュリティの観点から望ましくない。
その代わりにパスワードのハッシュ値を(あるいはダイジェスト)と呼ばれる値をデータベースに保存するのが定石
アプリケーションからのDB基本利用法
アプリケーションからDBにアクセスするためにはデータベース固有のプロトコルを用いてアクセスする必要があり、各DB向けに実装されているドライバーを使う。 一方アプリケーション側から呼び出す接続コードは、データベースドライバーの実装に依存しない汎用的なAPI
DBバックアップについて
ダンプのファイル量が多いと時間がかかる。
そのため、その際にデータの書き換えがあるとデータの相互性が失われる。
そのためデータのバックアップを取得するときはDBを停止して行うべき
DB種類
- キーバリューストア memcachedやRedis
DB分割構成
Webアプリケーションの規模が大きくなると応答速度の低下が問題になることがあり、ボトルネックとなるのはDBアクセスに関する部分。
そういった問題を解決し、複数のDBを利用するgemパッケージとしてOctopusなどがあったがRails6.0からはRails標準機能として複数DBへ対応した。
しかし、実際に複数DBとして望まれるケースで多いのは書き込みと読み込みの負荷を軽減させるために書き込む用のデータベースと、そのデータを同期した読み取り用データベースに分割する構成。
書き込み用データベースをプライマリーDB レプリケートした読み取り専用データベースをレプリカDB
上記の構成の場合、レプリカDBへ直接マイグレーションの適用やデータの書き込みは行わない（RailsではレプリカDBであると明示するため database.yml にreplicaを指定する）
実運用ではレプリカDBへの接続は参照権限のみのユーザで接続するのが望ましい。
リードレプリカ(read replica)
リーどレプリカとは、DBの負荷分散のために作成される参照専用の複製。 データの更新＆追加は行わず検索や読みこみのみを行う。
データベースのスキーマ
- MySQLではDBとスキーマは同じと思って良い
- PostgreSQLではデータベースがあってその下にスキーマが存在する（デフォルトでpublicスキーマが作成される）
リレーションの読み込み方
Eager Loading 訳：熱望的なローディング or 積極的なリレーションシップ
Eager Loadingでは、2つのテーブルのデータを1度に取得する（関連のも）
Eager Loadingを使うと、クエリの発行回数を減らすことができるので、いわゆる「N＋1問題」の回避策として用いられる。
Lazy Loading 訳：怠惰なローディング
リレーションの際、親テーブルからデータA取得する。次に子テーブルから（便宜上、親テーブルのidを持っているもの）データAのidに関連するデータを取得する。
というように2回に分けてデータを取得すること。
N＋１問題とは？
クエリの発行回数が過剰に増えて、サーバーサイドでタイムアウトエラーなどの不具合を起こしてしまう現象のこと
データベーススキーマ設計の完全ガイド
- ブルのROW_FORMATがDynamic(Barracuda)であることを確認する
- MySQL5.6まではデフォルトのフォーマットはCompact(Antelope)でしたが、これは1レコードあたり8KBまでしかデータを入れることができません。 テキスト型を使うと8KB制限を突破してしまうこともあるため、テーブルのフォーマットがDynamic(Barracuda)であることを確認します。
- 整数値を入れる場合はint型かbigint型を使う
- float型は使わない
- 精度のトラブルに巻き込まれたくないためfloatは使いません。多くの場合、doubleかdecimalで問題ありません。 金額情報など、精度を求められる小数値にはdecimalを使う doubleも小数点以下の精度に悩まされることがあります。金額を扱う、精度が必要な計算は必ずDecimalを利用します。
- 日付を入れる場合はDATE型を使う
- 商品のお届け日など、日付を入れる場合はDatetimeやTimestamp型ではなくDATE型を使うようにします。DatetimeやTimestampはタイムゾーンの影響を受けるためです。
- JSON型を使ったら負け
郵便番号や電話番号のnumberかstringかの悩み
- 算術計算の対象ではないのでstringにする。
命名規則
-
大文字を利用しない。 テーブル名、カラム名ともに大文字を利用しない。 （DBにより大文字小文字を区別するもの、しないものなどがあるため小文字で統一を図る）
-
複数単語の連携はスネークケース テーブル名、カラム名ともにスネークケースを利用する。 キャメルケース、キャメルバックはNG。
DB・テーブル設計のベストプラクティス
テーブル 複数形 vs 単数形
実際のところWEB系のフレームワークなどは複数形を好むため、複数形のテーブル名に慣れ親しんでいる人は多い。
空文字 or NULL
空文字だと、電話番号を持っているが電話番号がない。というようなよくわからない事情がでる。
そのため持っていないのであれば NULL にした方がいい。
データが存在しないのならば、素直にNULLを入れるべきです。
エンティティ保存方法を考える
このエンティティの識別子の生成方法には様々な種類がありますが，大きく分けて永続化前に生成する早期生成と永続化後に生成する遅延生成の2種類に分けられます
外部キー制約
参照されるのが 親テーブル
参照するのが 子テーブル
リレーションシップの種類
-
依存リレーションシップ 子テーブルの存在が親に依存している場合
-
非依存リレーションシップ 子テーブルの存在が親に依存していない場合
多対多
多対多の関係の場合にどうテーブル設計をすれば良いかわからなくなってしまいがち。
そうなんです。多対多の関係の場合、どう頑張っても良い設計にならないのです。 ですのでそもそも多対多の関係にならないような設計が必要で、その解消方法は中間テーブルを用意し1対多の関係になるように設計することが必要
論理削除の可否
twadaさんに語ってもらった 論理削除はアンチパターンのひとつだが割とよくある設計。
レコードを消したい。でも消したくないみたいな時に削除フラグ項目を設け、レコードをDELETEするのではなく削除フラグをUPDATEして、SELECTの条件で削除フラグがTRUEなら取得しないようにするやつです。
頻繁に復活させたり、レコード数が少ないテーブルに設けるのであれば検討の余地はありますが、基本的には論理削除を用いないほうが良いでしょう。
内部結合と外部結合の違い
Resource
学習効率を上げるためのバックエンドのデータベース基礎知識
瞬殺でDBを使って何かしたいとき
DockerでサクッとDBからER図を作成する
データベース設計の際に気をつけていること
SQLスキーマを改善する
DB設計をするときに考えるステップ
イミュータブルデータモデル
エンティティで考える
データベースに都道府県データを持たせるべきかどうか