Closed2022/06/23にクローズ5

PrismaのData Guideをやる

Database

Prisma

nissy-dev

https://www.prisma.io/dataguide/

nissy-dev

Prismaをやる前にDBに復習

SQL

Create
- 主キーと外部キーは設定しよう
- 主キーはテーブルの各行を一意に定める (主キーの重複を許可しない)
- 外部キーは他のテーブルの列との参照関係 (親子関係) を定義するもの
  - 他テーブルから参照されているデータの更新操作を禁止できたりする
Select
- DISTINCT : 重複行をまとめてくれる
- 結合系
  - INNER JOIN -> マッチしたもののみ返す
  - OUTER JOIN -> マスタのデータはそのまま残り、結合したデータも返す
    - RIGHT と LEFT があるが、マスタのデータを示している

演習の答え

以下のQiitaの記事を見た。

トランザクション

アプリケーションがDBに対する複数の読み書きを論理的な単位としてまとめる方法

SQLだと、BEGINで開始し、COMMIT or ROLLBACK で終了する。
基本的には、同時実行の制御とクラッシュのリカバリを行う

AICD特性

Atomicity：トランザクションに含まれる操作すべてが成功か中断になる
Consistency：トランザクションを実行した前後では、データの整合性が保たれている
- 口座の引き出し額と振り込み額が同じとか
- これはアプリケーション側が保証する
Isolation：同時実行している複数のトランザクションはお互いに独立する
- 完全に独立させるのは難しいので、DB側がいくつかの分離方法を提供している
Durability：コミットが完了したトランザクションによる変更は永続化される

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

ダーティリード

他のトランザクションの未コミットな書き込みが読み取れる問題

ファジーリード

同じトランザクション内で複数回読みだす値が、他のトランザクションによって更新され、
異なる値を読み取ってしまう問題

ファントムリード

他のトランザクションの書き込みによって、同じ条件で読んでいるのに処理の結果が変わる問題

MySQLのInnoDBのデフォルトの設定だと、以上の３つはどれも起きない REPEATABLE READの設定になっている。ちなみに、Postgresでは READ COMMITTED の設定になっており、ファジーリードやファントムリードの問題は起きるので注意が必要。

各レベルと解消できる問題については以下が詳しい。

データベース設計

まずはERモデルを作って、Entity (実体、DBで表現したい対象物) や関係 (関連) について整理する。ここでは、特にEntity同士の関係性 (1対1 or 1対多 or 多対1 ...)や参加制約 (最低N人のようなルール) を意識する。

ER図を書いてみた

ER図を作成したあとは、スキーマを作成する。関係性や制約を正しく定義するために、外部キーや主キーを正しく定義したり、NOT NULL や ON DELETE CASCASE を使ったりする。

正規化

ER図では、規則がないので、主観的に様々な図がかけてしまう。このため、主観性を減らし、データの冗長性を減らしていくためのプロセスとして正規化がある。

第一正規化

すべての属性を単一値にする。カンマ区切りの値を持たず、複数のカラムが概念的に同じにならないようにする。 (名前1, 名前2とかを作らない。)

方法：複数のに分割 or 別テーブルへの分解する

第二正規化

すべての非キー属性を、キーに完全関数従属させる。関数従属性は、冗長性を発見する手助けをする。

関数従属とは...?

Xの値が決まれば、Yの値も決まるような関係性のこと (XがYを関数的に決定する)

方法：キーの一部によって一意に決まる非キー属性を別テーブルへ分解する

第三正規化

すべての非キー属性を、キーに推移関数従属させない。

推移関数従属とは...?

X (キー) で Y (非キー) が決まり、Y (非キー) で Z が決まるような関係性のこと

方法：非キー属性によって決まる属性を別テーブルへ分解する

ボイスコッド正規化

非キー属性でキーの一部が決まるような関係性を排除する。

インデックス

テーブルをフルスキャンすることなく、目的のデータを取得するために必要なレコードの探索機能を提供するデータ構造。B+Treeが有名。インデックスについては、以下の資料も良さそう。

インデックスを貼るコツ

むやみにはらない (もしろパフォーマンスを落とすことも...)
- 更新時のオーバヘッドにつながる
- 性能もサイズに比例するので、少ないデータではあまり意味はない
カーディナリティ (ユニークな値の数) が高いカラムを選ぶ (絞り込みにくいデータを選ぶ)

カーディナリティについては、select index from table_name で確認できる。

実行計画

発行したクエリのスキャン範囲やスキャンする見積もり行数などがわかる。インデックスの効き方を確認する時に利用することが多い。確認の方法としては、通常のSQL文の先頭に explain をつければよい。

SQL Tips

最大値を持つ行を抜き出すときは、サブクエリを有効に使う。

あとは、With句とかもある。

nissy-dev

What are the databases

In memory databse と persist datasbe は相補関係になるので、しばしば併用される。

例１；データを新しく追加する操作を一旦 in memory databse に保持しておいて、定期的に persist datbase に同期する (persistする回数を減らし、負荷を減らす)
例２；データを persist database から in memory databse に保持しておいて、読み込み操作を高速化する

データベースの運用に必要な機能

ユーザーの権限や認証・認可の管理
バックアップの設定やメンテナンス
レプリケーションやスケーリングの設定
オフラインとオンラインでのリカバリーオプション

データベースに関わる人について

Data architects
- 一貫したデータ戦略の開発をする旗振り役
- データベースに関する技術選定
- 現在のシステムへのデータベースの統合のロードマップを引く
Database administrators
- データベースの専門家
- データベースの最適化 (モニタリングとパフォーマンス改善)
- セキュリティの監視
Application developers
- データベースを利用したユーザー操作を実装
- データ構造の管理
SRE
- データベースのバックアップなどを含めてた全般的な管理
- Database administratorsに似ているけど、SREはデータベースに特化してないところが違い
データアナリスト
- データを分析して、組織や事業の戦略に提言する
- データベースのコピーを用意して、そこにアクセスすることが多い

データベースを実際使うにあたって

アプリケーションとデータベースの間でデータを変換する
- ORMが有名だけど、完璧ではない (例：インピーダンスミスマッチ)
  - リレーショナルなデータ構造とオブジェクト指向のデータ構造は大きく違う
- アプリケーションとデータベース間の抽象化レイヤーの設計方法は考える必要がある
データ構造の変化を同期する
- データ構造もしばしば変わるので、マイグレーションの方法についても考える必要がある
データの権限やセキュリティを管理する
- データのプライバシーやアクセス権限を考える必要がある
- データの安全性を守るため、入力されるデータについても気にかける必要がある
  - 登録されるデータへのサニタイズも必要不可欠

データベースの歴史

ファイルベースのデータベース
- OSの設定ファイル (/etc/passwd) などで今でも使われている
階層型のデータベース
- ファイルシステムやDNSの管理などに今でも使われている
- ツリーを走査するコストが非常にかかる
ネットワーク型のデータベース
- データの複雑な関係性を表現できる
- 運用と構築がむずかしく、あまり実用はされなかった

現在主流のデータベース

リレーショナルデータベース
- 規則的で予測可能なデータを扱うのが得意
- 多くのアプリケーションにとって堅実な選択肢
  - アプリケーションは基本的に順序付けられた構造化されたデータを生成するため
NoSQL (not only SQL、key-value database や document databaseのことを指す)
- key-value database
  - スキーマを持たないので、値の型や形式はユーザーが適切に確認する責任がある
  - アプリケーションの設定値やアプリケーション変数、フラグを保存するのが一般的
- Document database
  - 任意の値を保存するのではなく、ドキュメントと呼ばれる構造化された形式でデータを保存する
    - これによって、key-value databaseが得意としてない検索などが可能に
  - データ構造がまだ明確ではなく、データ間に参照関係があまり存在していない場合に有効
  - 柔軟性が高い分、データの一貫性と構造を維持する責任があり、非常に困難な場合がある
- Graph database
  - ノード、エッジにプロパティをもたせてデータの関係性を表現する
  - データそのものというより、データ間の関係性を保存するのが一番の目的
- Column-family database
  - 行 (キー) ごとに、任意の属性とその値の辞書を返すデータベース
    - それぞれの行が独自のスキーマを保持することになる
  - 行方向の操作は得意
    - ある項目に関する全てのデータとメタデータに1つの行でアクセスできる
  - 列方向の操作は苦手
    - テーブルの結合や集計などの分析系
- Time series database
  - 基本的にはwriteのみで、writeされたデータは変更されることが基本的にない
  - 価値のあるデータは最新のデータの場合が多い
    - 古いデータはダウンサンプリングや集約されることもある

スキーマ

よいスキーマとは？
- リレーショナルDBの場合
  - 情報の重複がなく、一貫していて、関連データを結合しやすいこと
- 非リレーショナルDBの場合
  - アプリケーションのアクセスパターンに最適化されたデータフォーマットであること
static schema
- リレーショナルデータベースに関連するスキーマ
- データが従わなければいけない形式を定義する
dynamic schema
- 非リレーショナルデータベースに関連するスキーマ
- 登録されたデータに基づいて、スキーマが決まっていく (規則が出てくる)

データベース設計

一般的に必要なこと

データとドメインについて学習する
- データの属性を記録することが重要か
- データの量はどれくらいになりそうか
- データはどれくらいの速度で書き込まれるか
- データの規則性はあるか
データの利用方法について理解する
- 同時使用するユーザーなどは何人いるか？
- 一般的な操作やクエリで扱われるデータの量はどの程度か？
- リクエストの大半は読み込みクエリか、書き込みクエリか？
- 個々のレコードを対象とするのか、それとも多数のレコードを集約する操作が多いのか？
命名規則を規定する
- 大文字と小文字の使い分け。
- 複数形と単数形の使い分け
- 複数の単語からなる名前は、アンダースコア、ダッシュ、などどれで区切るか
- 常にフルネーム or 省略形も許容されるか？

リレーショナルDB

論理スキーマの設計
- ERモデルを使って、管理したいデータ項目とその関係などを整理する
- ERモデルは、以下の把握に便利
  - どのような個別の実体 (エンティティ) があるか
  - どのような属性を管理しなければならないか
  - どの実体が互いに関連しているか
物理スキーマの設計
- 各エンティティを調べて、主キーフィールドを決定する
- 外部キーを定義して、テーブルの関係を決定する
- クエリを予想し、インデックスの勘所をつける
- データの正規化を行い、エンティティも適宜変更する

非リレーショナルDB

クエリの洗い出し
- アプリケーションが特定の操作を行うためにどのようなデータが必要かを理解する
- データベースが発行しそうなクエリを予想する
- アプリケーションで最も重要なクエリを決定する
重要なクエリを中心に初期スキーマを設計する
- それぞれのクエリから返される情報を正確に判断する
- クエリに応答するためのすべての情報を単一のエンティティに格納する
可能な限りエンティティを結合し、重複を排除する
- エンティティ同士で、データの重複がないかどうかを確認する
- データの重複は、可能な限りエンティティを結合して減らすようにする
- 結合が難しい場合も多いので、そのような場合は次のアプリケーション側での結合を考える
アプリケーションで補える部分を見極める
- アプリケーション側でデータを組み立てることも検討する
- パフォーマンスとはトレードオフにはなりがちなので、テスト基盤を準備するべき
適切なパーティション・キーの決定
- パーティション・キーには、キーがかなり規則的に分布しているものを選ぶとよい
- 読み込み操作が多くなる場合には、1台のサーバーに書き込めるようなキーを選ぶ
- 書き込み操作が多くなる場合には、複数台のサーバーに書き込めるようなキーを選ぶ

nissy-dev

Data Modeling

変わらないデータ構造はないし、ユースケースを考え抜くのが大事と解いている
- 考え抜くためのリサーチとインタビューは非常に重要
SQLのView
- 定義した構文をもとに、仮想的なテーブルを定義できる機能
- https://dev.classmethod.jp/articles/mysql_practice_1/
- View はテーブルのように扱えるが、実態はただのSQL
データ型
- Prismaの対応表を見ると面白い
- https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#model-field-scalar-types
- varchar(191) になっている理由：https://www.grouparoo.com/blog/varchar-191
Primary Key となる ID の振り方
- Auto Increment
  - 問題点のまとめ：https://zenn.dev/dowanna6/articles/3c84e3818891c3
- UUID : 完全にユニークだが insert のパフォーマンスが落ちる
  - 検証している人：https://spring-mt.hatenablog.com/entry/2021/03/03/033320
- ULID : ユニークかつ生成順にソート可能
- まとめ：https://zenn.dev/j5ik2o/articles/a085ab3e3d0f197f6559

nissy-dev

英語かつモチベあんまり続かなかったので、これで終わりにする....

このスクラップは2022/06/23にクローズされました

SQL

トランザクション

AICD特性

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

データベース設計

正規化

第一正規化

第二正規化

第三正規化

ボイスコッド正規化

インデックス

実行計画

SQL Tips

What are the databases

データベースの運用に必要な機能

データベースに関わる人について

データベースを実際使うにあたって

データベースの歴史

現在主流のデータベース

最新のデータベース

スキーマ

データベース設計

一般的に必要なこと

リレーショナルDB

非リレーショナルDB

Data Modeling