MySQLデータベースシステムは、データを効率的に管理し、アクセスするために複数のレイヤーで構成されています。その中でも特に重要なのが「ストレージエンジン」と「インデックス」です。これらはデータの永続化、クエリ性能、トランザクションの信頼性に直接影響を与えます。

ストレージエンジン

ストレージエンジンは、MySQLサーバーの核となるコンポーネントの一つで、データの実際の格納方法、インデックスの管理、データの読み書き、そしてトランザクション処理の方法を定義します。これはデータベース全体ではなく、テーブルごとに選択できるため、「テーブルタイプ」とも呼ばれます。

ストレージエンジンの確認と指定

現在稼働中のMySQLサーバーがサポートしているストレージエンジンの一覧は、以下のSQLコマンドで確認できます。

SHOW ENGINES;

テーブルを作成する際にストレージエンジンを指定するには、CREATE TABLEステートメントの末尾にENGINE句を追加します。

CREATE TABLE product_catalog (
    item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    item_name VARCHAR(255) NOT NULL COMMENT '商品名',
    price DECIMAL(10, 2) COMMENT '価格',
    stock_count INT DEFAULT 0 COMMENT '在庫数'
) ENGINE = InnoDB COMMENT '商品カタログテーブル';

主要なストレージエンジン

InnoDB

MySQL 5.5以降のデフォルトストレージエンジンであるInnoDBは、高い信頼性とパフォーマンスを両立させた汎用エンジンです。多くのWebアプリケーションや基幹システムで採用されています。

• 特徴:
  • ACID特性に準拠したトランザクションをサポートし、データの整合性を保証します。
  • 行レベルロックを採用しており、高い並行アクセス性能を提供します。
  • 外部キー制約をサポートし、関連するテーブル間のデータ整合性を維持します。
• ファイル構造:

  通常、テーブルごとに.ibdという拡張子を持つファイルが作成されます（innodb_file_per_table設定による）。このファイルには、そのテーブルの定義（内部的には.sdi）、データ、およびインデックスが格納されます。

MyISAM

MyISAMはMySQLの初期のデフォルトエンジンで、参照処理の高速性に優れています。現在では、特定の読み込み中心のワークロードでのみ検討されることがあります。

• 特徴:
  • トランザクションや外部キーをサポートしません。
  • テーブルレベルロックを採用しており、書き込みが多い環境では並行性が低下する可能性があります。
  • データの挿入および読み込みは非常に高速です。
• ファイル構造:

  各テーブルは通常3つのファイルで構成されます。.sdiファイルにはテーブル定義、.MYDファイルにはデータ、.MYIファイルにはインデックスが格納されます。

Memory

Memoryエンジンは、その名の通り、すべてのデータをRAMに格納します。非常に高速なアクセスを提供しますが、データは永続的ではありません。

• 特徴:
  • データはメモリ上に存在するため、ディスクI/Oがほとんどなく、極めて高速なデータアクセスを実現します。
  • サーバーの再起動やクラッシュが発生すると、メモリ上のデータは失われます。
  • 主に一時テーブルやキャッシュ用途に適しています。
  • デフォルトでハッシュインデックスを使用します。
• ファイル構造:

  .sdiファイルにテーブル定義のみが格納され、データ自体はメモリ上に保持されます。

ストレージエンジンの選択基準

アプリケーションの要件に応じて、適切なストレージエンジンを選択することが重要です。

• InnoDB: トランザクション処理、データの一貫性、高並行性が求められるシステム（例：ECサイトの注文管理、金融システム）に最適です。ほとんどの場合、このエンジンが推奨されます。
• MyISAM: 読み込み中心で、データの更新・削除が少なく、トランザクションの必要がないシンプルなアプリケーション（例：Webサイトのアクセスログ、静的な情報表示）に適しています。
• Memory: 非常に高速なデータアクセスが必要な一時的なデータやキャッシュデータ（例：セッション情報、一時的な計算結果）に利用されます。データの永続性が必要な場合は使用できません。

インデックス

インデックスは、データベースのクエリパフォーマンスを大幅に向上させるための特殊なデータ構造です。特定のカラムの値に基づいて、テーブル内の行を高速に検索することを可能にします。

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

• メリット:
  • データの検索、結合（JOIN）、ソートなどの操作が高速化されます。
  • データのユニーク性を保証するために使用できます。
• デメリット:
  • インデックス自体がストレージ領域を消費します。
  • データの挿入、更新、削除の際に、インデックスも更新する必要があるため、書き込み性能がわずかに低下します。

インデックスの構造

MySQLのインデックスはストレージエンジン層で実装されており、主に以下の構造があります。

B-Tree (B木)

B-Treeは、多分岐平衡探索木の一種で、ディスクベースのデータベースシステムに適しています。ノードは複数の子ノードへのポインタとキー・データを持ち、バランスを保ちながら階層的にデータを整理します。これにより、特定のキーを探すために必要なディスクI/Oの回数を最小限に抑えます。

B+Tree (B+木)

B+Treeは、B-Treeをさらに最適化した構造で、MySQLのInnoDBエンジンのデフォルトインデックス構造です。B-Treeとの主な違いは以下の通りです。

• 全てのデータは葉ノードに存在する: 非葉ノードはインデックスキーと子ノードへのポインタのみを保持し、実際のデータ（またはデータへのポインタ）は葉ノードにのみ格納されます。これにより、非葉ノードに多くのキーを格納でき、木の高さが低く保たれます。
• 葉ノード間の連結リスト: 全ての葉ノードが、順序付けされた双方向リンクリストで連結されています。これにより、特定の範囲のデータを効率的にスキャン（範囲クエリ）できます。

この最適化された構造により、単一のキー検索だけでなく、範囲検索においても高いパフォーマンスを発揮します。

Hashインデックス

ハッシュインデックスは、ハッシュ関数を使用してキー値をハッシュ値に変換し、そのハッシュ値に基づいてデータが格納されている物理アドレスを直接特定します。これにより、極めて高速なデータアクセスが可能です。

• 特徴:
  • 等価比較（=, IN）のクエリにおいて、非常に高い検索効率（平均O(1)）を提供します。
  • キーがハッシュ化されるため、範囲検索（BETWEEN, >, <など）やデータのソート操作には利用できません。
  • ハッシュ衝突が発生すると、パフォーマンスが低下する可能性があります。
• ストレージエンジンサポート:

  Memoryエンジンがハッシュインデックスをサポートしています。InnoDBは「アダプティブハッシュインデックス」という機能を持ち、頻繁にアクセスされるB+Treeインデックスページに対して自動的にハッシュインデックスを構築して高速化を図ります。

なぜInnoDBはB+Treeインデックス構造を採用するのか？

InnoDBがB+Treeを主要なインデックス構造として採用している理由は、その特性がディスクベースのデータベースの要件に非常に適しているためです。

• B-Treeと比較して、非葉ノードにデータが存在しないため、一つのディスクブロック（ページ）により多くのインデックスキーを格納でき、木の高さが低く保たれます。これにより、データ検索に必要なディスクI/O回数が最小化されます。
• 葉ノードが連結リストで繋がれているため、単一のキー検索だけでなく、範囲内のデータを効率的に走査する範囲クエリに優れています。これは、ビジネスロジックで頻繁に発生する操作です。
• ハッシュインデックスとは異なり、B+Treeはデータのソート順序を保持しているため、ORDER BY句や範囲検索を効率的に処理できます。