Open4

DBのお勉強(RDBの違い)

イカフライイカフライ

MysqlとPostgresとBigQueryの違いは？？

Mysqlのアーキテクチャ

サーバー層

  • Connector: クライアントとの接続
  • Parser: SQLクエリを解析し、その構文が正しいかどうかをチェック
  • Optimizer:解析されたSQLクエリに対して最適な実行計画を生成
  • Executor:ストレージエンジン提供しているAPIを呼び出して、データの読み書きを行う
    • キャッシュ＆バッファ
      • クエリキャッシュ(mySQL 8.0からクエリー非推奨)
        • 理由:更新をした時にキャッシュを削除する必要があり全体パフォーマンスが下がる
      • 他のキャッシングメカニズムを利用するべき

ストレージエンジン層

  • 特徴
    • トランザクション管理(InnoDBのみ対応)
    • データフォーマットの定義とデータの永続化
    • インデックス管理

ストレージエンジンの種類

  • ストレージエンジンはいろんな種類があり、テーブルごとにストレージエンジンを選択でき、用途に応じて最適なエンジンを使用することが可能

  • InnoDB

    • デフォルトエンジン
    • トランザクション対応（ACID準拠）
    • 外部キー対応

  • MylSAM

    • 読み込みに強い
    • 非トランザクション非対応
    • 外部キー非対応

インデックスについて

トランザクションについて

  • トランザクションの管理(InnnoDB)には、多重同時実行制御（MVCC）とロック機構を中心とした設計が採用されている

ACIDについて

  • Atomicity（原子性）：すべての操作が完了するか、全く行われないか。
  • Consistency（一貫性）：トランザクションが開始前と終了後でデータの整合性が保たれる。
  • Isolation（独立性）：複数トランザクションが同時に実行されても互いに影響を与えない。
  • Durability（耐久性）：トランザクションがコミットされたら、障害が発生しても変更内容は保持される。

トランザクションの内部構造

(1) トランザクションログ
REDOログ
トランザクションの変更内容を記録し、障害復旧時にコミット済みの変更を再適用する。
書き込みはディスクI/O効率を高めるため、バッファリングされる。
UNDOログ
ロールバック用の情報を記録し、トランザクション中の変更を元に戻す。
また、読み取り一貫性（後述）を実現するためにも利用される。
(2) バッファプール
ディスクI/Oを最小化するために、頻繁にアクセスされるデータやインデックスをメモリ上にキャッシュ。
トランザクション中の変更は、最初にバッファプールに記録され、適切なタイミングでディスクにフラッシュされる。
(3) MVCC（Multi-Version Concurrency Control）
スナップショット型の読み取り一貫性を提供する仕組み。
データの変更は新しいバージョンとして記録され、古いバージョンはUNDOログに保持。
読み取り専用トランザクションは「開始時点のデータ」を参照し、他のトランザクションによる更新の影響を受けない。

Postgresの特徴は

  • マルチプロセスモデルを採用している

  • なぜマルチプロセス構成？？

    • スレッドの方が軽量に思えるが、Postgresの登場時はOSや開発環境によるスレッドが制限されることがあった
    • プロセスを分離することで安全性と安定性を向上できることを期待できる(一つのプロセスがクラッシュしても他のプロセスに影響を与えない )
    • スレッド特有の問題を解決できる。
      • スレッド間でのデータの共有はロックが必要となり、パフォーマンス低下につながる可能性がある

Postgresのアーキテクチャ


この画像がわかりやすかったので使わせていただいてます。
https://poppingcarp.com/postgresql-process/

  • マスタープロセスが全てのプロセスの親になる
  • バックグランドプロセスとバックエンドプロセスの２種類がある
  • バックグランドプロセスとは
    -postgres 起動時に生成される
  • バックエンドプロセスとは
    • 接続要求時に生成される

各プロセス

  • ライタ

    • ディスクI/Oが性能のボトルネックになることが多い
    • テーブルやインデックスの更新が必要な場合は共有バッファ（Shared Buffer）上で更新を行い、共有メモリがいっぱいになったタイミングで更新を行い、ディスク I/O の負荷を分散させ、クライアントの応答速度を向上させる

  • WALライタ

    • トランザクションで利用される
    • トランザクションが開始するとデータを更新する前に更新ログを先に書き込む
    • WAL はデータの変更履歴を記録する仕組みで、データベースのクラッシュ後にデータをリカバリするために使用される
      WALライタは共有メモリに蓄積された WAL のエントリを定期的にディスクに書き込む

  • チェックポインタ

    • ゲームのセーブ機能
    • チェックポイントは、共有バッファに残っている変更をすべてディスクに書き出し、その時点での WAL にもマークを付けます。
    • このマークにより、クラッシュリカバリ時にはチェックポイント以降の WAL のみを適用すればよいことがわかります。

  • 自動バキュームランチャと自動バキュームワーカ

    • PostgreSQLではテーブルのレコードが更新されると、データとしては更新前を不要領域として更新後の情報を登録する追記型のアーキテクチャを採用している

BigQueryの内部構成

よくわからないけど、激つよネットワークと激つよサーバークラスター構成で激早集計クエリを実現している

https://dev.classmethod.jp/articles/bigquery-standard-config-googlecloudnext/

それぞれの得意なことをまとめる

参考
MYSQL
https://www.wantedly.com/companies/progrit/post_articles/326087
https://qiita.com/dennis_wang/items/52d025ce176cf1a6082d

イカフライイカフライ

行指向性と列指向性の違いは？？

データの保存方法

  • 行指向性: 1行毎に保存する(全ての列が連結)
  • 列指向性:
    • 1列毎に保存する(1テーブルの全ての列を連結)
    • 列のデータは大体同じになるので圧縮率も高い

読み取り

  • 行指向性: 特定の行の全てのカラムの取得が得意
  • 列指向性: 複数行の特定のカラムの取得が得意での集計

書き込み

  • 行指向性: 得意
  • 列指向性: 列が分散されているのでやや遅い

得意

  • 行指向性: トランザクション更新
  • 列指向性: グループ化などの集合関数による分析
イカフライイカフライ

JOINする時に気をつけること

Postgres

  • PostgreSQL はネストループ結合（Nested Loop Join）、マージ結合（Merge Join）、ハッシュ結合（Hash Join）など複数の結合アルゴリズムを自動的に選択します。
  • PostgreSQL:結合で使用する列にインデックスを作成する。
  • PostgreSQL のオプティマイザは JOIN の順序を最適化しますが、MySQL は順序に依存する場合があります。

Mysql

  • MySQL は主にネストループ結合を使用し、他の結合アルゴリズムをサポートしていないため、大量データの結合時に性能が低下しやすいです。

  • MySQL:同様にインデックスを作成するが、MySQL は結合時にインデックスを使わない場合もあるため、EXPLAIN を活用して確認が必要。

  • JOIN の順序に気を付ける

    • 結合の順序を意識的に最適化する必要がある場合が多い。
      P

PostgresのJOINの種類

ネストループ結合（Nested Loop Join）

  • 基本的な方法で、外側のテーブルの各行に対して、内側のテーブル全体をループしてマッチする行を探します。
  • 簡単に実装可能で、小規模なデータセットやインデックスを使える場合に適しています
  • メリットデメリット
    • メリット: 小さなテーブルでは高速で、インデックスがある場合は効率的。
    • デメリット: データ量が多いと急激にパフォーマンスが低下。

マージ結合（Merge Join）

  • 両方のテーブルが ソート済み であることが前提。
  • 並び順を利用して効率的に結合を行います。
  • メリット・デメリット
    • メリット: データがソート済みなら非常に高速。
    • デメリット: ソートにコストがかかる場合がある。

ハッシュ結合（Hash Join）

  • 一方のテーブルをハッシュテーブルに変換し、もう一方のテーブルをスキャンしながら一致する行を探す方法。
  • データのソートは不要で、大量のデータに適しています。
  • 動作の流れ
    • 小さいほうのテーブルを読み込み、結合キーに基づいてハッシュテーブルを作成。
    • 大きいほうのテーブルをスキャンし、ハッシュテーブルを使って一致する行を探す。
  • メリット・デメリット
    • メリット: データが大規模でも効率的。ソート不要。
    • デメリット: ハッシュテーブルを作成するメモリが必要。メモリ不足時はディスク I/O が増加。