コンテンツにスキップ

データベースのインデックスとパーティション

概要

データベースのインデックスとパーティションは、どちらも大規模なデータセットを持つデータベースの性能、管理性、およびスケーラビリティを向上させるための重要な技術です。インデックスは、テーブル内のデータ検索を高速化するために使用される特別なデータ構造であり、書籍の索引のように、目的のデータレコードへ素早く到達するための経路を提供します。

一方、パーティションは、巨大なテーブルやインデックスを、より小さく管理しやすい単位（パーティション）に物理的に分割する技術です。これにより、個々のパーティションに対して独立した操作（検索、更新、削除、バックアップなど）が可能となり、全体の性能向上やメンテナンスの効率化が図られます。

これらの技術は、データ量の増加に伴うパフォーマンスの劣化や運用上の課題を解決するために不可欠であり、現代のRDBMS（Relational Database Management System）において広く活用されています。

注目される背景

ビッグデータ時代へと突入し、企業が扱うデータ量は指数関数的に増加しています。これに伴い、データベースはテラバイト、ペタバイト規模のデータを効率的に管理し、リアルタイムに近い速度でクエリに応答する能力が求められるようになりました。このような状況下で、データの検索性能がデータ量に比例して低下する問題、および巨大なテーブルのメンテナンス（バックアップ、データ削除、スキーマ変更など）に時間がかかりすぎるという運用上の課題が顕在化しました。

インデックスは古くから存在する技術ですが、データ量の増加に伴い、その設計と管理の重要性がさらに高まっています。また、パーティションは、特に超大規模なテーブルを扱う際の性能維持と運用効率向上のためのブレイクスルーとして、ここ十数年でRDBMSの主要な機能として広く採用されるようになりました。OLTP（Online Transaction Processing）環境での高速なトランザクション処理や、OLAP（Online Analytical Processing）環境での複雑な分析クエリの高速化において、これらの技術は不可欠な存在となっています。

核心的な考え方

インデックスの核心的な考え方: 「データの探索範囲を劇的に絞り込むことで、I/Oコスト（ディスク読み込み回数）を削減し、データ検索にかかる時間を短縮する」。これは、全件スキャン（フルテーブルスキャン）を避けて、特定条件に合致するデータに直接アクセスするための「近道」を提供することに他なりません。

パーティションの核心的な考え方: 「巨大なものを小さく分割することで、管理の粒度を高め、特定の操作の対象範囲を限定し、さらには並列処理を可能にする」。これにより、リソースの効率的な利用、特定のサブセットに対する操作の高速化、および大規模なテーブルの運用・保守の柔軟性が向上します。

仕組み・詳細

インデックスの仕組み

データベースインデックスの最も一般的な実装はB-tree (Balanced Tree) です。B-treeは、データをソートされた順序で格納し、特定のキー値に基づいてデータブロックへのポインタを保持する自己平衡型のツリー構造です。

  • B-treeの構造:

    • Root Node: ツリーの最上位ノード。
    • Branch Node (Internal Node): ルートノードとリーフノードの間に位置し、さらに下位のブランチノードやリーフノードへのポインタを持つ。
    • Leaf Node: 実際のデータ行へのポインタ（ROWIDなど）またはデータそのものを格納する最下位ノード。リーフノード間は双方向リストで接続されていることが多く、範囲検索を効率化する。

  • 検索処理:

    1. Root Nodeから検索を開始。
    2. 各ノード内でキー値を比較し、次に進むべき子ノードを決定。
    3. Leaf Nodeに到達すると、そこにあるポインタを使ってテーブルの実際のデータ行を取得する。

  • Clustered Index と Non-Clustered Index:

    • Clustered Index: テーブルの物理的なデータの並び順が、インデックスのキー順に一致するように構成されます。これにより、データの物理的な局所性が高まり、特に範囲検索で高い性能を発揮します。通常、テーブルごとに1つしか作成できません（PRIMARY KEYに自動的に作成されることが多い）。例: MySQLのInnoDB。
    • Non-Clustered Index: インデックスの物理的な並び順と、テーブルのデータの物理的な並び順が異なるインデックスです。インデックス自体がソートされたキーと、対応するデータ行へのポインタ（ROWIDなど）を持ちます。テーブルごとに複数作成可能で、様々な検索条件に対応できます。
-- インデックスの作成例
CREATE INDEX idx_customers_name ON customers (first_name, last_name);

-- ユニークインデックスの作成例 (重複を許さない)
CREATE UNIQUE INDEX uidx_products_sku ON products (sku);

パーティションの仕組み

パーティションは、1つの論理的なテーブルを、指定された基準に基づいて複数の物理的なストレージセグメントに分割します。クエリが特定のパーティションキーを含む場合、データベースは関連するパーティションのみをスキャンし、それ以外のパーティションを無視できます（パーティションプルーニング）。

  • 主なパーティションの種類:

    • Range Partitioning (範囲パーティション): カラムの値の範囲に基づいてデータを分割します。日付、数値IDなどに適しています。 例: 2023年のデータはパーティションP1に、2024年のデータはP2に。
    • List Partitioning (リストパーティション): カラムの値のリストに基づいてデータを分割します。特定のカテゴリや地域コードなどに適しています。 例: 'Tokyo'と'Osaka'はP1に、'Fukuoka'と'Sapporo'はP2に。
    • Hash Partitioning (ハッシュパーティション): カラムの値のハッシュ関数に基づいてデータを分割します。データが均等に分散されるため、I/Oの負荷分散に役立ちます。
    • Key Partitioning (キーパーティション): PRIMARY KEYまたはUNIQUE KEYの列の値に基づいてハッシュパーティションを適用します（RDBMSによっては特定キーのハッシュ値で自動的に分散）。
    • Subpartitioning (サブパーティション): 最初にRangeで分割し、さらに各Rangeパーティション内をListやHashで分割するなど、複数のパーティション戦略を組み合わせる方法です。

  • パーティションプルーニング (Partition Pruning): クエリがWHERE句でパーティションキーを指定している場合、データベースは関連するパーティションのみをスキャンし、他のパーティションはスキップします。これにより、I/Oコストが大幅に削減され、クエリ性能が向上します。

  • パーティションワイズジョイン (Partition-wise Join): 両方のテーブルが同じパーティションキーとパーティション戦略でパーティション化されている場合、データベースは各パーティションペアに対して並列にジョインを実行できます。これにより、大規模なジョイン操作の性能が向上します。

-- パーティションテーブルの作成例 (PostgreSQLの宣言的パーティショニング)
CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- パーティションテーブルの作成例 (MySQLのRangeパーティショニング)
CREATE TABLE logs (
    log_id INT NOT NULL AUTO_INCREMENT,
    log_date DATE NOT NULL,
    message VARCHAR(255),
    PRIMARY KEY (log_id, log_date) -- パーティションキーはPRIMARY KEYの一部である必要がある
)
PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

関連手法・技術との比較

特徴 インデックス (Index) パーティション (Partition) シャーディング (Sharding)
目的 データ検索・ソートの高速化 大規模テーブルの性能・管理性向上、I/O分散 データベース全体の水平スケール、可用性向上
対象 テーブル内の特定のカラム 単一データベース内の単一テーブル データベース全体 (複数テーブル、複数DBインスタンス)
分割単位 論理的なソート済みデータ構造 物理的なデータファイル（サブテーブル） 物理的なデータベースインスタンス（ノード）
管理主体 データベースエンジン データベースエンジン データベースエンジン、ミドルウェア、アプリケーション層
透過性 アプリケーションから透過的（通常意識不要） アプリケーションから透過的（特定のクエリでパフォーマンス差） アプリケーションレベルで意識する必要がある場合が多い
用途 WHERE句の条件、ORDER BY句、JOIN条件など 超大規模テーブルの性能改善、古いデータ削除、アーカイブ、メンテナンス 非常に大量のデータ・トラフィックを処理するシステム
欠点 更新時のオーバーヘッド、ディスク容量消費 設計の複雑さ、不適切なキー選択による性能劣化、パーティション間操作の複雑さ 設計の複雑さ、データの一貫性・トランザクション管理の難しさ、運用負荷増大

メリット

インデックス

  • 検索速度の向上: 特定の条件に基づくデータ検索（WHERE句）や結合（JOIN句）の処理時間を劇的に短縮します。
  • ソート処理の高速化: ORDER BY句によるデータソート処理のコストを削減します。
  • UNIQUE制約の実現: 特定のカラムに重複する値の挿入を防ぐために使用されます（UNIQUE Index, PRIMARY KEY Index）。
  • データの整合性維持: PRIMARY KEYやUNIQUE KEYを構成するインデックスは、データの一意性を保証します。

パーティション

  • 大規模テーブルの性能向上: クエリが特定のパーティションキーを含む場合、関連するパーティションのみをスキャン（パーティションプルーニング）することでI/O量を削減し、クエリを高速化します。
  • メンテナンス性の向上:
    • 古いデータや不要なデータの削除（DROP PARTITION）が高速に行え、全体的なテーブルのロック時間を短縮します。
    • 特定のパーティションのバックアップやリカバリが独立して行えるため、運用効率が向上します。
    • インデックスの再構築（REBUILD INDEX）も特定のパーティションに対してのみ行えます。
  • ディスクI/Oの分散: 異なるパーティションを異なるストレージデバイスに配置することで、I/O処理を分散させ、スループットを向上させることができます。
  • テーブルのロック競合の緩和: 異なるパーティションへのアクセスが、互いにロック競合を引き起こす可能性を低減します。

課題・注意点

インデックス

  • ディスク容量の消費: インデックス自体がデータ構造としてディスク領域を消費します。インデックスが増えれば増えるほどディスク使用量も増加します。
  • 更新系処理のオーバーヘッド: INSERT, UPDATE, DELETEといったデータ変更操作が行われるたびに、インデックスも更新される必要があります。これにより、更新系処理のパフォーマンスが低下する可能性があります。
  • インデックス選択の難しさ: 全てのカラムにインデックスを作成すれば良いというわけではありません。使用頻度、カーディナリティ（値の多様性）、クエリパターンを考慮した適切な設計が必要です。
  • 最適化の専門知識: 不適切なインデックスは、むしろクエリを遅くしたり、オプティマイザが間違った実行計画を選択する原因となったりすることがあります。

パーティション

  • 設計の複雑さ: 適切なパーティションキーとパーティション戦略の選択は難しく、将来のデータ増加やクエリパターンを予測する必要があります。不適切な設計は、かえって性能を悪化させる可能性があります。
  • パーティション間操作のオーバーヘッド: パーティションキーを使用しないクエリや、複数のパーティションにまたがる複雑なクエリは、パーティション化されていないテーブルよりもパフォーマンスが悪化する可能性があります。
  • データベースシステムによる機能差: 全てのRDBMSが高度なパーティション機能をサポートしているわけではありません。特にオープンソースRDBMSでは、機能が異なる場合があります。
  • PRIMARY KEY/UNIQUE KEYの制約: 多くのRDBMSでは、PRIMARY KEYやUNIQUE KEYにパーティションキーを含める必要があります。これにより、キーの設計に制約が生じることがあります。

代表的なツール / 実装例

ほとんどの主要なRDBMSがインデックスとパーティションの機能をサポートしています。

  • MySQL:

    • インデックス: CREATE INDEX文でB-treeインデックスを作成。InnoDBストレージエンジンではPRIMARY KEYが自動的にClustered Indexとして機能します。
    • パーティション: PARTITION BY句をCREATE TABLE文に追加して、Range, List, Hash, Keyなどのパーティションを定義できます。

  • PostgreSQL:

    • インデックス: CREATE INDEX文でB-tree, Hash, GiST (Generalized Search Tree), SP-GiST, GIN (Generalized Inverted Index), BRIN (Block Range Index) など多様なインデックスを作成可能。
    • パーティション: PostgreSQL 10以降では宣言的パーティショニング（Declarative Partitioning）が導入され、PARTITION BY RANGEまたはPARTITION BY LISTで定義し、CREATE TABLE ... PARTITION OFで子テーブル（パーティション）を作成します。

  • Oracle Database:

    • インデックス: B-tree, Bitmap, Function-Based Index, Domain Indexなど、非常に多機能なインデックスをサポートしています。
    • パーティション: Range, List, Hash, Composite (Range-Hash, Range-List) など、豊富なパーティショニングオプションを提供します。パーティション単位での管理操作も強力です。

  • SQL Server:

    • インデックス: Clustered Index, Non-Clustered Index, Columnstore Index (OLAP向け) など多様なインデックスをサポート。
    • パーティション: Range Partitioningが主に利用され、パーティション関数とパーティションスキームを組み合わせて実装します。

参考URL