Skip to main content

Relational Database

Overview​

リレーショナルデータベースに関する情報をまとめたセクション。
特徴としてはデータを人間が管理しやすい2次元表の形式で管理する。

長い歴史を持つ​

誕生は1969年まで遡る。
すでに登場してからデータベースのメインストリームの座になる。

DBセッションの切り方​

  • mysql
    • DBクライアントの接続を切ってもセッションは切れない（SQLは実行されたまま）
    • そのためオーバーフローしやすい
  • postgres
    • DBクライアントの接続を切った場合セッションは切れる（実行されいるSQLがあれば終了する）

リソースの解放​

クエリが中止されると、そのクエリによって使用されていたリソース（メモリ、一時的なディスクスペースなど）も解放されることが一般的。
これにより、システムのリソースがムダに消費されることを防ぐ。 ロールバック: トランザクション内で実行されていたクエリの場合、未完了のトランザクションは自動的にロールバックされることが多いです。これにより、データベースの整合性が保たれる。

長時間クエリの実行対策​

パフォーマンスチューニングは必須だが、まずはタイムアウトを設定しておく。

フルテーブルスキャンはしない​

MySQLでフルテーブルスキャンを確認する方法

SELECT文であるレコードを取得したいときに、対象のテーブルを全件検索してしまっている検索方法 LIKE * などをするとフルテーブルスキャンをしてしまうため注意が必要！

フルテーブルスキャンをしてしまう構文​

MySQLだとSELECT Queryの前に EXPLAIN をつけることで確認ができる。

typeの判別

type検索方法
ALLフルスキャン、全件検索
indexフルインデックススキャン
const主キーやユニークキーを使って検索
eq_ref↑と類似。表結合しているものに使われると表示される
refユニークじゃないインデックスを使って検索
rangeインデックスを使って検索

問題なのが前述の「ALL」と「index」

インデックスを作るときの基準​

インデックスを作るときの基準

データ量の多いテーブルにだけ作成する。 なぜなら、データ量の少ないテーブルの場合フルスキャンの方が処理が速いため つまり、インデックスを利用して取得する方が遅くなってしまう。 MySQLのドキュメントにもこのように記載がある。

インデックスが作成される条件​

  • プライマリキー

    • プライマリーキーに対して自動的にインデックスが作成される。プライマリーキーはテーブル内の各行を一意に識別するために使用され、データベースはこのキーを効率的に検索できるようにインデックスを利用します。

  • ユニークキー

    • ユニークキー（UNIQUE KEY）にも自動的にインデックスが作成される。ユニークキーはデータベース内のテーブルで、指定されたカラムの値がテーブル全体で一意であることを保証するため一意性を効率的に保証するために、データベース管理システムはユニークキーに対して自動的にインデックスを作成する。

ユニークキーとインデックス​

  1. データの一意性の保証:

    • ユニークキーはそのカラムが重複した値を持たないことを保証します。データベースは、新しいデータを挿入または既存のデータを更新する際、ユニークキーによって定義されたカラムの値が他の行と重複していないかをチェックします。

  2. 検索効率の向上:

    • ユニークキーにインデックスが作成されることによって、これらのカラムを使用して行を検索する操作が高速になります。インデックスがあることで、データベースはフルテーブルスキャンを避け、効率的にデータを見つけることができます。

実装と利用​

  • ユニークキーのインデックスは、通常、プライマリーキーのインデックスと同じように機能しますが、テーブルにプライマリーキーが存在しない場合や、複数のカラムを組み合わせて一意性を保証する場合にとくに有用です。
  • ユニークキーのインデックスは、検索クエリだけでなく、データの整合性を保持するための重要なツールとしても機能します。

SQLでのユニークキーの設定例​

CREATE TABLE Users (
    ID INT AUTO_INCREMENT,
    Username VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE KEY (Username),
    UNIQUE KEY (Email)
);

この例では、Username と Email にユニークキーが設定されており、それぞれに対して自動的にインデックスが作成されます。これにより、これらのカラムに対するクエリが高速になり、同時にこれらのカラムに重複した値が挿入されるのを防ぐことができます。

ユニークキーによるインデックスは、データの整合性を保ちながら、データベースのパフォーマンスを向上させる効果的な手段です。

インデックス メリット・デメリすべト​

テーブルのすべてのカラムにインデックスを貼ることは技術的には可能ですが、通常は推奨されない。
インデックスの設置にはメリットもありますが、それにはデメリットも伴う。
適切なカラムにインデックスを設置することが、パフォーマンスの最適化には重要。

インデックスのメリット​

  • クエリの高速化：選択的なクエリが高速に実行される。とくに検索条件によく使われるカラムにインデックスを設置することで、データベースは効率的にデータを検索できる。

インデックスのデメリット​

  1. 書き込み性能の低下 インデックスが多いと、INSERT、UPDATE、DELETE 操作の際にインデックスの再構築が必要になるため、これらの操作が遅くなる。
    データベースが書き込み操作を頻繁に行うアプリケーションでは、パフォーマンスに顕著な影響を及ぼす可能性がある。

  2. ストレージの使用量増加 各インデックスはストレージスペースを消費します。不要なインデックスはディスクの使用量をムダに増加させ、ストレージコストの増加につながる。

  3. 管理の複雑化 インデックスが多いと、データベースの管理が複雑になります。インデックスの状態を監視し、最適化する必要があるため、DBA（データベース管理者）の負担が増大する。

ベストプラクティス​

  • インデックスは選択的に

    • 頻繁にクエリの条件として使用されるカラムや、JOIN、ORDER BY、GROUP BY に使われるカラムに対してインデックスを設定する。また、テーブルのデータ量やクエリの性質を考慮して、インデックスが本当に必要かどうかを検討することが重要です。

  • 定期的な見直し：

    • 定期的にインデックスの利用状況を確認し、使用されていない、またはあまり効果のないインデックスを削除することで、パフォーマンスを向上させることができます。

  • パフォーマンステスト：

    • インデックスを追加する前後でパフォーマンステストを行い、その影響を評価します。これにより、インデックスの効果を定量的に判断できる。
    • stress testを行える環境が必須

シャーディング（Sharding）​

リレーショナルデータベースのシャーディング（Sharding）は、データを複数のサーバーやデータベースに分割する手法。
単一のデータベースに全データを保存するのではなく、データを「シャード（Shard）」と呼ばれる分割された小さな単位に分けて、各シャードを異なるデータベースインスタンスに分散配置する。
これにより、データ量が多くても効率的に処理でき、スケーラビリティとパフォーマンスが向上する。

シャーディングの目的​

  1. スケーラビリティの向上 データ量が増えたときに、1台のデータベースでは処理しきれなくなる可能性があります。シャーディングによってデータを分散させ、複数のサーバーで処理することで、パフォーマンスが向上します。
  2. 負荷分散 複数のシャードにデータを分けることで、特定のデータベースに集中する負荷を分散でき、クエリの応答速度が向上する。
  3. 高可用性の実現 シャードを複数のデータセンターに配置することで、障害発生時でも他のシャードが機能を維持し、可用性を高められる。

シャーディングの方法​

シャーディングにはいくつかの方法があります。

  1. ハッシュシャーディング 特定の列（例：ユーザーID）にハッシュ関数を適用し、その結果に基づいてシャードを決定する方法です。データが均等に分散されるため、アクセスの偏りを防ぐのに有効です。
  2. レンジシャーディング 範囲に基づいてシャードを分ける方法です。たとえば、ユーザーIDが「1〜1000」のデータはシャードAに、「1001〜2000」のデータはシャードBに、というように設定します。ただし、アクセスが特定の範囲に集中すると負荷の偏りが生じる可能性があります。
  3. 地理的シャーディング 地域ごとにシャードを分ける方法です。たとえば、アジアのデータはアジアのサーバーに、ヨーロッパのデータはヨーロッパのサーバーに保存します。これにより、ユーザーに近い場所でデータを処理できるため、レイテンシが改善されます。

シャーディングの注意点​

シャーディングは複雑な実装が必要で、次のような課題も伴う。

  • データの一貫性 分散されたシャード間でデータの一貫性を保つのが難しい場合。
  • クエリの複雑化 必要なデータが複数のシャードにまたがる場合、クエリが複雑になり、結合操作も困難。
  • 運用管理 シャードの追加や削除、データ移動などのメンテナンスが難しく、運用の負担が増える。

DBマイグレーション​

データベースを削除し手から作り直すと、DBに保存されている情報がすべて削除される。
こういった事態を回避する方法としてデータベースマイグレーションを行う方法がある。
マイグレーションとは、DBに保存されているデータを保持したままテーブルの作成やカラムの変更などを行うための機能。
運用中のデータベースにデータを入れたまま、テーブルを追加したりカラムを変更するなどして、スキーマを管理する機能 ※対象の言語ORMに対して、マイグレーションがない場合は汎用なマイグレーションツールを使うなど対策が必要。

caution

スケールされた環境で、複数のサーバーが同時にマイグレーションは行なってはいけない。
複数のサーバーインスタンスが同時にマイグレーションを走らせると、同じテーブルに対して並行して処理が走り、競合やエラーが発生する恐れ。
DBレイヤーではアドバイザリロック (Advisory Lock) という仕組みを使い、MySQLやPostgreSQLでは同時移行を防ぐ機能が提供されている。
データベースにロックをかけることで、他のプロセスが同時にマイグレーションを実行できないようにする。 ただし、このロックを完全に信用せず、同時移行を避けるために1台のインスタンスだけでマイグレーションを実行するのがベスト。

ロールバック​

DBマイグレーションツールのロールバック機能は使うな

caution

本番環境でのロールバックは推奨されない。
down メソッドにはテーブルの削除や列の削除などの破壊的なアクションが含まれているため。
代わりに新しい移行を作成して問題を修正し、本番サーバー上で実行することをお勧めする。 メンテナンスモードを使用してもデータ損失リスクは回避できないため、基本的に本番環境でのロールバックは避けるべき。 スキーマ修正が必要な場合は新しいマイグレーションを作成して適用する」という方法が安全 ロールバックするのではなく、修正マイグレーションを適切に作成し、適用して対応する。