複合インデックスの芸術 - 列の順序が重要な理由

複合インデックスの芸術: 列の順序が重要な理由

データベースはほとんどのアプリケーションのバックボーンであり、そのパフォーマンスはユーザーエクスペリエンスに大きく影響します。クエリが遅くなると、システム全体が苦しむことになります。データベース管理者や開発者が利用できる多くの最適化手法の中で、インデックスは強力なツールとして際立っています。しかし、インデックスの真の芸術は、単にそれらを作成することにあるのではなく、特に複合インデックスに関しては、そのニュアンスを理解することにあります。適切に設計された複合インデックスは、データ取得を劇的に加速させることができますが、不適切に設計されたインデックスは、クエリオプティマイザに完全に無視されるか、パフォーマンスを低下させる可能性さえあります。この記事では、複合インデックス内の列の順序という、一見些細に見える詳細が、データベースの効率にとってなぜこれほどまでに実質的な重要性を持つのかを、その批判的な役割を探求します。

データベースインデックスの核心は、本の巻末にある索引に似ています。特定の用語を見つけるためにすべてのページを順番にスキャンする代わりに、索引に記載されている関連ページにすぐにジャンプできます。この原則はデータベースにも適用され、すべてのレコードを調べることなくデータ行を特定できます。インデックスについて話すとき、2つの重要な用語が思い浮かびます。

• 単一列インデックス (Single-Column Index): テーブルの単一の列に作成されるインデックス。特定の列の値に基づいて行をすばやく見つけるのに役立ちます。たとえば、users.email にインデックスを作成すると、メールアドレスによる高速な検索が可能になります。

• 複合インデックス (Compound Index): 2つ以上の列に作成されるインデックス。このタイプのインデックスは、WHERE、ORDER BY、または GROUP BY 句に複数の列を含むクエリに特に強力になります。複合インデックスの重要な側面は、列が定義されている順序です。

順序の重要性を説明するために、実践的なシナリオを考えてみましょう。customer_id、order_date、status などの列を含む、数百万件のレコードを持つ orders テーブルを想像してください。

次のようなクエリを頻繁に実行すると仮定します。

1. SELECT * FROM orders WHERE customer_id = 123;
2. SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
3. SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC;
4. SELECT * FROM orders WHERE order_date > '2023-01-01';
5. SELECT * FROM orders WHERE status = 'shipped';

次に、複合インデックス ((customer_id, order_date)) が ((order_date, customer_id)) と比較してどのように機能するかを見てみましょう。

左端プレフィックスルール (Leftmost Prefix Rule) の理解

複合インデックスを支配する基本的な原則は、「左端プレフィックスルール」です。インデックス ((col_a, col_b, col_c)) は、以下に対して効率的に検索するために使用できます。

• col_a
• col_a, col_b
• col_a, col_b, col_c

しかし、インデックスの左端の列から始まらないため、col_b、col_c、col_b, col_c、または col_a, col_c ( col_b なし) を直接効率的に検索することはできません。これは、(姓、名) でソートされた電話帳のようなものです。特定の姓を持つすべての人物、または特定の姓と名を持つすべての人物を簡単に見つけることができます。しかし、姓を知らずに名で特定のすべての人物を見つけるのは簡単ではありません。

シナリオ 1: インデックス ((customer_id, order_date))

このインデックスを作成しましょう。

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

• クエリ 1 (WHERE customer_id = 123): このクエリは idx_customer_date を完全に利用できます。customer_id がインデックスの左端の列であるため、データベースは customer_id の部分をたどることで、customer_id = 123 のレコードにすばやく絞り込むことができます。
• クエリ 2 (WHERE customer_id = 123 AND order_date > '2023-01-01'): このクエリも idx_customer_date を完全に利用できます。データベースはまず customer_id でフィルタリングし、次にそのサブセット内で order_date が条件を満たすレコードを効率的に検索します。これは 2 つの利点を提供します。両方の WHERE 句がインデックスによってカバーされるためです。
• クエリ 3 (WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): ここでは、インデックスはデータをフィルタリングするのに役立つだけでなく、ソートにも役立ちます。customer_id 内のデータはすでに order_date でソートされているため、データベースは追加のソート操作（MySQL 用語では「filesort」）を必要とせずに ORDER BY 句を効率的に実行できます。これは大きなパフォーマンス向上です。
• クエリ 4 (WHERE order_date > '2023-01-01'): このクエリは idx_customer_date を効果的に使用できません。order_date は左端の列ではないため、データベースはフルテーブルスキャンを実行するか、order_date に別の単一列インデックスがあればそれを使用する可能性があります。
• クエリ 5 (WHERE status = 'shipped'): このクエリは、status がインデックスの一部ではないため、idx_customer_date を使用することはできません。

シナリオ 2: インデックス ((order_date, customer_id))

次に、逆のインデックスを考えてみましょう。

CREATE INDEX idx_date_customer ON orders (order_date, customer_id);

• クエリ 1 (WHERE customer_id = 123): customer_id が左端の列ではないため、このクエリは idx_date_customer を効果的に使用できません。データベースはおそらくフルテーブルスキャンを実行するか、存在する場合は customer_id の単一列インデックスを使用するでしょう。
• クエリ 2 (WHERE customer_id = 123 AND order_date > '2023-01-01'): このクエリは idx_date_customer を部分的に使用できます。order_date > '2023-01-01' で効率的にフィルタリングできますが、その後 customer_id でフィルタリングするために選択された行をスキャンする必要があります。フルテーブルスキャンよりも優れていますが、この特定のクエリパターンでは idx_customer_date ほど効率的ではありません。
• クエリ 3 (WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): クエリ 2 と同様に、order_date フィルタと ORDER BY 句は効率的ですが、customer_id は後続のフィルタリング操作を必要とします。
• クエリ 4 (WHERE order_date > '2023-01-01'): order_date が左端の列であるため、このクエリは idx_date_customer を完全に利用できます。これは非常に高速でしょう。
• クエリ 5 (WHERE status = 'shipped'): このインデックスはまだ役立ちません。

列の順序に関する重要なポイント

1. カーディナリティ (Cardinality): 一般的に、等価条件で頻繁に使用される場合、最もカーディナリティが高い（最も多くの固有値を持つ）列を最初に配置します。これにより、インデックスは検索空間を初期段階で最も効果的に絞り込むことができます。ただし、これはガイドラインであり、厳密なルールではありません。
2. 使用パターン: 最も重要な要素は、クエリパターンです。
   • col_a のみ、または col_a と col_b で頻繁にクエリを実行する場合は、((col_a, col_b)) が適切です。
   • col_b のみで頻繁にクエリを実行する場合は、((col_b, col_a)) （または col_b の個別のインデックス）の方が優れています。
   • ORDER BY または GROUP BY 句を検討してください。col_a でフィルタリングした後、ORDER BY col_b が一般的である場合、 ((col_a, col_b)) はフィルタとソートの両方を満たし、高価な「filesort」操作を回避できます。
3. 等価条件 vs. 範囲条件: 等価条件 (=) で使用される列は、通常、範囲条件 (<、>、BETWEEN、LIKE 'prefix%') で使用される列よりも前に配置する必要があります。 WHERE col_a = 'X' AND col_b > 'Y' がある場合、 ((col_a, col_b)) は非常にうまく機能します。インデックスは col_a = 'X' にジャンプし、その後 col_b を 'Y' から効率的にスキャンできます。順序が ((col_b, col_a)) の場合、インデックスは col_a でフィルタリングする前に、はるかに広い範囲の col_b 値をスキャンします。
4. カバリングインデックス (Covering Indexes): クエリに必要なすべての列（SELECT、WHERE、ORDER BY、GROUP BY）がインデックスの一部である場合、複合インデックスは「カバリングインデックス」になります。これは、データベースが実際のテーブル行にアクセスする必要がないことを意味し、クエリをさらに高速化します。たとえば、SELECT customer_id, order_date FROM orders WHERE customer_id = 123 は ((customer_id, order_date)) によってカバーされる可能性があります。

-- ORDER BY 句における順序の重要性を示す例
-- 'orders' テーブルに 'customer_id' と 'order_date' があると仮定

-- インデックス 1: customer_id を最初に、次に order_date
CREATE INDEX idx_customer_date_order ON orders (customer_id, order_date);

-- クエリ 1: customer_id でフィルタリングし、order_date で並べ替え
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

-- これは、WHERE と ORDER BY の両方に対して idx_customer_date_order を効率的に使用する可能性が高いです。
-- インデックスは customer_id で順序付けされ、次に各 customer_id 内で order_date で順序付けされ、
-- 指定された順序での効率的なスキャンが可能になります。

-- インデックス 2: order_date を最初に、次に customer_id
CREATE INDEX idx_date_customer_order ON orders (order_date, customer_id);

-- クエリ 2: 上記と同じクエリですが、異なるインデックス構造です
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

-- idx_date_customer_order はある程度使用されるかもしれませんが、
-- customer_id が先頭の列ではないため、WHERE 句ではそれほど効率的ではありません。
-- オプティマイザが有益だと判断した場合、ORDER BY に役立つ可能性はあります。
-- ただし、customer_id に関する等価述語がなく、order_date に関する範囲のみがある場合、
-- idx_date_customer_order が威力を発揮するでしょう。

-- order_date が範囲、customer_id が等価であるクエリを検討してください
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND customer_id = 456;

-- このクエリの場合、idx_date_customer_order の方が効率的でしょう。
-- なぜなら、まず左端の列から order_date の範囲を使用できるからです。

複合インデックスの設計の芸術は、アプリケーションのクエリワークロードを深く理解することにあります。これは、書き込み操作に対するインデックスのパフォーマンスオーバーヘッドもあるため、すべての列またはすべての列の組み合わせに対して無計画にインデックスを作成することではありません。むしろ、最も重要な、または頻繁なクエリに対してインデックスの有用性を最大化し、全体的なインデックスフットプリントを最小限に抑えるように、列を戦略的に配置することです。左端プレフィックスルール、列カーディナリティ、およびクエリの性質（等価条件 vs. 範囲条件、WHERE vs. ORDER BY）を慎重に検討することにより、大幅なパフォーマンス向上を解き放ち、高負荷下でデータベースが効率的に動作することを保証できます。複合インデックスの列の順序は単なる詳細ではなく、その有効性の基盤です。