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を行える環境が必須