LASSIC Media らしくメディア

監修・編集責任者：牛尾 昭昌（株式会社LASSIC 執行役員）

複合インデックスは、テーブルの複数の列をまとめて1つのインデックスにしたもので、マルチカラムインデックスや連結インデックスとも呼ばれます。顧客IDと注文日時のように、いつも組み合わせて検索する列があるときに作ります。ところが、同じ2列でも並べる順番を間違えると、作ったのに検索で使われない、という事態が起こります。

本記事では、PostgreSQL・MySQL・SQL Server・SQLiteの公式ドキュメントと、PythonとSQLiteで出した実際の実行計画をもとに、左端一致、範囲条件の後ろの列、並べ替えと被覆インデックス、作りすぎの副作用を順に見ていきます。

複合インデックスとは

PostgreSQLのマニュアルは、major と minor の2列で頻繁に検索するテーブルを例に、CREATE INDEX test2_mm_idx ON test2 (major, minor) のように2列をまとめたインデックスを作る方法を示しています。書き方は単一列のインデックスと同じで、括弧の中に列を並べるだけです。

まとめられる列の数には上限があります。PostgreSQLは後で触れるINCLUDEの列を含めて32列まで^*1、MySQLは16列までです。^*4 もっとも、上限まで並べる場面はまずありません。

中身の並び方は、SQLiteの解説が分かりやすく説明しています。左端の列が行を並べる第一の基準になり、2番目の列は左端の列の値が同じ行どうしの順番を決めるために使われます。3番目の列があれば、最初の2列が同じ行どうしの順番を決めます。^*6 電話帳を姓で並べ、同じ姓の中を名で並べるのと同じ形です。

列の値のばらつき（カーディナリティ）がインデックスの効果にどう関わるかは「カーディナリティと索引設計」で、インデックスを支える木構造そのものは「B木の仕組み」で扱っています。本記事では列を並べる順番に絞ります。

左端一致の仕組み

先頭の列で並んでいるので、2番目の列の値は先頭の列の値ごとに散らばっています。電話帳を名だけで探すと、すべてのページをめくることになるのと同じです。

MySQLのマニュアルはこれを左端一致（leftmost prefix）として説明しています。(col1, col2, col3) のインデックスがあれば、(col1)、(col1, col2)、(col1, col2, col3) の組み合わせで検索に使えます。^*4 一方で、(col2) や (col2, col3) は左端からの並びではないため、インデックスの列を使っていても検索には使われません。

図の右のように、先頭の列に条件が無いと、該当する行がインデックスのあちこちに散らばり、読む範囲を狭められません。PostgreSQLのマニュアルも、B-treeの複合インデックスが最も効率よく働くのは先頭（左端）の列に条件があるときだとしています。

SQLiteの最適化の説明では、インデックスの先頭の列は = か IN か IS で使われている必要があり、途中の列が抜けていると、それより後ろの列の条件はインデックスで使えないとしています。^*7 言い方は違っても、先頭から順に条件がそろっている部分だけが探索に使われる点は共通しています。

具体例：SQLiteで実行計画を見る

実際の動きを、Pythonに標準で付いているsqlite3で確かめます。注文テーブルに (customer_id, ordered_at) の複合インデックスを作り、条件を変えた6つのクエリの実行計画を EXPLAIN QUERY PLAN で表示します。データは入れず、インデックスをどう使う計画になるかだけを見ます。

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INT,"
           " ordered_at TEXT, status TEXT, total INT)")
db.execute("CREATE INDEX idx_cust_date ON orders (customer_id, ordered_at)")
queries = [
    "SELECT * FROM orders WHERE customer_id = 7",
    "SELECT * FROM orders WHERE ordered_at >= '2026-09-01'",
    "SELECT * FROM orders WHERE customer_id = 7 AND ordered_at >= '2026-09-01'",
    "SELECT * FROM orders WHERE customer_id = 7 ORDER BY ordered_at",
    "SELECT * FROM orders WHERE customer_id = 7 ORDER BY total",
    "SELECT ordered_at FROM orders WHERE customer_id = 7",
]
for q in queries:
    plan = db.execute("EXPLAIN QUERY PLAN " + q).fetchall()
    print(q, "=>", " / ".join(row[3] for row in plan))

手元のSQLite 3.49.1で実行した結果を、下の表にまとめました。SEARCH はインデックスで範囲を狭めて探すこと、SCAN は全件を順に読むことを表し、括弧の中には探索に使った列が出ます。

2行目の ordered_at だけの条件は SCAN になり、2列目だけではインデックスが使われないことが分かります。3行目では範囲の条件（>=）にも ordered_at が使われ、出力では「ordered_at>?」と表示されます。

範囲条件の後ろの列

列の順番で最も間違えやすいのが、範囲の条件と組み合わせる場面です。PostgreSQLのマニュアルは、先頭の列の等号条件と、等号条件の無い最初の列に対する不等号の条件が、インデックスの読む範囲を絞るのに使われると定めています。^*1 それより右の列の条件は、インデックスの中で確かめられてテーブル本体を読む回数は減らせますが、読むインデックスの範囲を狭めるとは限りません。

SQLiteも同じで、使われる列のうち不等号を使えるのはいちばん右の列だけであり、不等号だけで絞った列より右の列は、ふつうはインデックスの探索に使われないとしています。^*7 顧客ID・注文日時・状態の3つの条件を持つクエリで、インデックスの列の順番だけを変えて確かめます。

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INT,"
           " ordered_at TEXT, status TEXT, total INT)")
q = ("SELECT * FROM orders WHERE customer_id = 7"
     " AND ordered_at >= '2026-09-01' AND status = 'paid'")
for cols in ["ordered_at, customer_id", "customer_id, ordered_at, status",
             "customer_id, status, ordered_at"]:
    db.execute("DROP INDEX IF EXISTS idx")
    db.execute(f"CREATE INDEX idx ON orders ({cols})")
    plan = db.execute("EXPLAIN QUERY PLAN " + q).fetchall()
    print(f"({cols}) =>", plan[0][3])

(ordered_at, customer_id) => SEARCH orders USING INDEX idx (ordered_at>?)
(customer_id, ordered_at, status) => SEARCH orders USING INDEX idx (customer_id=? AND ordered_at>?)
(customer_id, status, ordered_at) => SEARCH orders USING INDEX idx (customer_id=? AND status=? AND ordered_at>?)

1つ目の (ordered_at, customer_id) では、範囲で絞る ordered_at が先頭にあるため、customer_id が探索に使われていません。2つ目は status を範囲の列の後ろに置いたため、status が探索に入りません。3つ目のように、等号で絞る customer_id と status を前に、範囲で絞る ordered_at を最後に置くと、3列すべてが探索に使われます。

SQL Serverの索引設計ガイドは、等号、不等号、BETWEENの条件や結合に使う列を前に置き、残りの列は値の種類が多い順に並べるよう勧めています。^*5 値の種類の多さは判断材料の一つですが、まずはクエリが等号で絞るのか範囲で絞るのかを見て並べるのが、間違いの少ない進め方です。

並べ替えと被覆インデックス

PostgreSQLのマニュアルによれば、ORDER BY に合うインデックスがあれば並べ替えの手順を省けます。とくに ORDER BY と LIMIT を組み合わせたクエリでは、全件を並べ替えずに最初の n 行だけを取り出せるとしています。^*3 先ほどの表で、インデックスに無い total で並べたときだけ USE TEMP B-TREE FOR ORDER BY が出たのはこのためです。先頭の列を等号で絞った範囲の中は、すぐ後ろの列の順に並んでいます。

昇順と降順を混ぜる並べ替えには注意が要ります。PostgreSQLのマニュアルは、(x, y) のインデックスは ORDER BY x, y と ORDER BY x DESC, y DESC には使えるが、ORDER BY x ASC, y DESC には使えず、(x ASC, y DESC) のように向きを指定して作る必要があると説明しています。^*3 SQL Serverでも、キー列ごとに昇順と降順を指定できます。

表の最後の行のように、クエリが使う列がすべてインデックスにそろっていれば、テーブル本体を読まずに結果を返せます。これを被覆インデックス（カバリングインデックス）と呼びます。PostgreSQLとSQL Serverでは、検索には使わず返すだけの列を INCLUDE 句で付け加えられます。^*2 CREATE INDEX tab_x_y ON tab(x) INCLUDE (y) のように書くと、y はキーではなく中身として持たれます。

INCLUDEの列は並べ替えの基準にならないため、検索や並べ替えに使う列はキーに、返すだけの列はINCLUDEに、と役割で分けて置きます。なおPostgreSQLでは、表の行が最近更新されたページは本体を確かめに行く必要があるため、更新の多いテーブルでは期待したほど読み取りが減らないことがあります。

作りすぎの副作用

クエリごとに合う複合インデックスを足していくと、検索は速くなっても別の負担が増えます。SQL Serverのガイドは、テーブルにインデックスが多いと、表のデータが変わるたびにインデックスも更新されるため、INSERT、UPDATE、DELETE、MERGEの性能に影響すると説明しています。^*5 見込みで作らず、使われていないものは取り除くよう求めています。

PostgreSQLのマニュアルは、複合インデックスは控えめに使うべきで、多くの場合は単一列のインデックスで足り、容量と時間も節約できるとしています。また、4列以上の複合インデックスは、テーブルの使われ方が極めて決まりきっている場合を除けば、役に立つことはあまりないとも書いています。^*1

見落としやすいのが重複です。SQLiteの解説は、一方がもう一方の先頭部分になっている2つのインデックスを持つべきではなく、列の少ないほうを削除してよいとしています。^*6 (customer_id) と (customer_id, ordered_at) が両方あれば、前者は後者で代わりが利きます。

先頭の列に条件が無くてもインデックスを飛び飛びに読むスキップスキャンという仕組みもあり、PostgreSQL 18とSQLiteの文書に説明があります。ただし、使われるのは主に先頭の列の値の種類が少ない場合で、SQLiteでは統計を集めるANALYZEを実行していないデータベースでは使われません。^*7 列の順番を間違えたままこの仕組みに頼る設計は避けます。

外部に委託するときに確認しておきたい点

データベースの設計や性能改善を外部に頼むときは、まず、どのクエリに合わせてそのインデックスを作ったのかを、条件の列と並べ替えの列まで含めて説明してもらいます。列の順番の理由が「値の種類が多い順」だけなら、範囲の条件や ORDER BY との関係を確かめ直してもらうとよいでしょう。

次に、確かめ方です。本番に近い件数とデータの偏りを持つ環境で、追加の前後に実行計画を取り、狙ったインデックスが SEARCH や Index Scan として使われているかを見せてもらいます。書き込みへの影響と容量の増え方も確認します。

最後に、運用に入った後の見直しです。使われていないインデックスや、先頭部分が重なるインデックスを定期的に洗い出す手順と、作り直すときの作業時間や負荷の見積もりを、保守の範囲に含めてもらいます。

まとめ：複合インデックスで確かめておきたい3つの点

複合インデックスを設計するうえで、確かめておきたい点は3つです。第一に、インデックスは先頭の列から順に並んでいるため、先頭の列に条件が無い検索ではふつう探索に使われないこと。第二に、等号で絞る列を前に、範囲で絞る列をその後ろに置き、並べ替えや返す列まで含めて順番とINCLUDEを決めること。第三に、列や本数を増やすほど更新の負担と容量が増えるため、実行計画で使われているかを確かめ、先頭部分が重なるものや使われないものを整理することです。

よくある質問

出典

1. *1　参考：PostgreSQL 18 Documentation「11.3. Multicolumn Indexes」（https://www.postgresql.org/docs/current/indexes-multicolumn.html）。出典：複合インデックスの作り方の例、32列までの上限、先頭の列の等号条件と最初の不等号条件が読む範囲を絞るという規則、スキップスキャン、控えめに使うべきこと・4列以上はあまり役に立たないことの記述を参照（2026年10月確認）
2. *2　参考：PostgreSQL 18 Documentation「11.9. Index-Only Scans and Covering Indexes」（https://www.postgresql.org/docs/current/indexes-index-only-scans.html）。出典：インデックスオンリースキャンの条件、可視性マップ、INCLUDE句の例（CREATE INDEX tab_x_y ON tab(x) INCLUDE (y)）を参照（2026年10月確認）
3. *3　参考：PostgreSQL 18 Documentation「11.4. Indexes and ORDER BY」（https://www.postgresql.org/docs/current/indexes-ordering.html）。出典：ORDER BYとLIMITの組み合わせ、(x, y)のインデックスで扱える並べ替えと昇順・降順を混ぜる場合の記述を参照（2026年10月確認）
4. *4　参考：MySQL 8.4 Reference Manual「Multiple-Column Indexes」（https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html）。出典：16列までの上限、leftmost prefixの説明、(last_name, first_name)のインデックスを使うクエリ・使わないクエリの例を参照（2026年10月確認）
5. *5　参考：Microsoft Learn「Index Architecture and Design Guide – SQL Server」（https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide）。出典：キー列の順序の指針、付加列（INCLUDE）、インデックスが多いときの更新への影響と使われないインデックスの削除の記述を参照（2026年10月確認）
6. *6　参考：SQLite「Query Planning」（https://www.sqlite.org/queryplanner.html）。出典：複数列のインデックスの並び方、先頭部分が重なるインデックスを持たないという目安、等号で絞った範囲での並べ替えの記述を参照（2026年10月確認）
7. *7　参考：SQLite「The SQLite Query Optimizer Overview」（https://www.sqlite.org/optoverview.html）。出典：インデックスの先頭の列の使い方と不等号を使える列、途中の列が抜けた場合の扱い、スキップスキャンとANALYZEの記述を参照（2026年10月確認）

Tweet