インデックスを使う

インデックスを使うと、よく実行されるクエリについて、対象列の走査量（行数）を減らせます。その結果、D1 のクエリ性能が上がります。

インデックスは次の場合に役立ちます。

• 述語でよく使う列の読み取り性能を上げたいとき。たとえば WHERE email_address = ? や WHERE user_id = 'a793b483-df87-43a8-a057-e5286d3537c5' です。一般的な Web アプリケーションやサービスでは、メールアドレス、ユーザー名、ユーザー ID、日付がインデックス向きの列です。
• 列（または複数列）に一意制約を付けたいとき。たとえば CREATE UNIQUE INDEX でメールアドレスやユーザー ID を一意にします。
• 複数列をまとめて照会するとき。例: (customer_id, transaction_date)
• テーブル同士を結合する列。例: JOIN の ON orders.customer_id = customers.id。適切なインデックスがあれば、繰り返しの走査や一時インデックスの作成を避けられます。クエリプランは EXPLAIN QUERY PLAN で確認します。

インデックスは、参照するテーブルと列に対する挿入・更新・削除のたびに自動で更新されます。テーブルへ書き込んだあとに、インデックスを手作業で更新する必要はありません。

D1 テーブルにインデックスを作るには、CREATE INDEX SQL コマンドでテーブルと対象列を指定します。

たとえば次の orders テーブルでは、customer_id にインデックスを付けたくなることがあります。このテーブルに対するクエリのほぼすべてが customer_id で絞り込むなら、インデックスを作ると性能が上がります。

CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    customer_id STRING NOT NULL, -- for example, a unique ID aba0e360-1e04-41b3-91a0-1f2263e1e0fb
    order_date STRING NOT NULL,
    status INTEGER NOT NULL,
    last_updated_date STRING NOT NULL
)

customer_id 列にインデックスを作るには、データベースに対して次のステートメントを実行します。

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id)

customer_id 列を参照するクエリは、このインデックスの恩恵を受けます。

-- Uses the index: the indexed column is referenced by the query.
SELECT * FROM orders WHERE customer_id = ?

-- Does not use the index: customer_id is not in the query.
SELECT * FROM orders WHERE order_date = '2023-05-01'

より複雑な場合は、クエリを直接分析 して、D1 がインデックスを使ったかを確認できます。

インデックスを作成したあとは、PRAGMA optimize を実行してデータベース性能を上げます。

PRAGMA optimize は、データベース内の各テーブルに対して ANALYZE を実行し、テーブルとインデックスの統計情報を集めます。この統計情報により、クエリプランナー はユーザークエリ実行時に、もっとも効率のよいクエリプランを生成できます。

詳細は PRAGMA optimize を参照してください。

データベース上のインデックスと、その SQL 定義は、sqlite_schema システムテーブルを照会すると確認できます。

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

次のような出力が返ります。

┌──────────────────────────────────┬───────┬────────────────────────────────────────┐
│ name                             │ type  │ sql                                    │
├──────────────────────────────────┼───────┼────────────────────────────────────────┤
│ idx_users_id                     │ index │ CREATE INDEX idx_users_id ON users(id) │
└──────────────────────────────────┴───────┴────────────────────────────────────────┘

このテーブルや既存のインデックスは変更できません。インデックスを変更するには、まず 削除 し、更新した定義で 新しいインデックスを作成 します。

クエリがインデックスを使ったかは、クエリの先頭に EXPLAIN QUERY PLAN ↗ を付けて確認します。続くステートメントのクエリプランが出力され、使われたインデックス（あれば）も分かります。

たとえば users テーブルに email_address TEXT 列があり、CREATE UNIQUE INDEX idx_email_address ON users(email_address) でインデックスを作ったとします。email_address を述語に含むクエリは、このインデックスを使うはずです。

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email_address = '[email protected]';
QUERY PLAN
`--SEARCH users USING INDEX idx_email_address (email_address=?)

クエリプランナーの USING INDEX <INDEX_NAME> 出力を確認し、インデックスが使われたことを確かめます。

これはインデックスのよくある使い方でもあります。メールアドレスでユーザーを探すクエリは、ログイン（認証）システムで非常に多いです。

インデックスを使うと、クエリが読む行数を減らせます。使用量の見積もりには meta オブジェクトを使います。「インデックスで、クエリが読む行数を減らせますか？」 と 「（最終的な）請求額をどう見積もればよいですか？」 を参照してください。

D1 は 読んだ行数と書いた行数 で課金します。クエリが返す行数ではありません。テーブル全体を走査して 1 行だけ返すクエリでも、走査したすべての行が課金対象です。必要な行へ直接ジャンプできるようにインデックスを付けることは、レイテンシ と コストの両方を下げる、もっとも効果的な手段のひとつです。

想定外に大きい D1 の請求の多くは、返す行数よりはるかに多くの行を読む（または書く）クエリが、少数ながら頻繁に実行されていることが原因です。次の表は、触る行数が多すぎるよくあるパターンと、その直し方です。クエリが全行を読む SCAN か、一致する行へジャンプする SEARCH ... USING INDEX かは、EXPLAIN QUERY PLAN で確認します。

複数列インデックス（複数列を指定したインデックス）は、クエリが すべての 列を指定するか、「左」側の列がすべてクエリに含まれる部分集合を指定した場合にだけ使われます。

CREATE INDEX idx_customer_id_transaction_date ON transactions(customer_id, transaction_date) というインデックスがあるとき、使われるかどうかは次の表のとおりです。

補足:

• 3 列（customer_id、transaction_date、shipping_status）にインデックスを作った場合、customer_id と transaction_date を使うクエリはインデックスを使います。「左」側の列がすべて含まれているためです。
• 同じインデックスで、transaction_date と shipping_status だけを使うクエリはインデックスを 使いません。左端列の customer_id がクエリに含まれていないためです。

部分インデックスは、テーブル内の一部の行だけを対象にするインデックスです。作成時に WHERE 句を付けて定義します。NULL の行や、特定の値がクエリ全体で出てくる行などを除外したいときに便利です。

• 具体例として、order_status INTEGER 列があり、アプリケーションコードでは 6 が "order complete"（注文完了）を表すテーブルを考えます。
• 未完了、進行中、発送済みの注文を照会するクエリに向きます。注文状況の確認はよくあるクエリです。
• 部分インデックスは、時間とともにインデックスが無制限に大きくなるのも防ぎます。完了済み注文ごとに行を保持する必要はなく、完了済み注文は進行中の注文より照会される回数が少ないことが多いです。

完了済み注文をインデックスから除く部分インデックスは、次のようになります。

CREATE INDEX idx_order_status_not_complete ON orders(order_status) WHERE order_status != 6

部分インデックスは、読み取り時（インデックス内の行が少ない）も書き込み時（インデックスへの書き込みが少ない）も、完全なインデックスより速くなることがあります。複数列インデックス と組み合わせることもできます。

インデックスの削除には DROP INDEX を使います。削除したインデックスは復元できません。

インデックスを作るときは、次の点に注意してください。

• インデックスは、常に無料の性能向上ではありません。いちばんよく照会する列にだけ作ります。インデックス自体の維持も必要です。インデックス付きの列に書き込むと、データベースはテーブルとインデックスの両方に書き込みます。ほぼすべての場合、インデックスの性能向上と読む行数の削減が、この追加の書き込みを上回ります。
• 他のテーブルを参照するインデックスや、非決定的な関数を使うインデックスは作れません。インデックスが安定しないためです。
• インデックスは更新できません。列を追加・削除するには、インデックスを削除 してから、新しい列で 新しいインデックスを作成 します。こうしたスキーマ変更は、D1 migrations のようなバージョン管理された仕組みで一度だけ適用します。
• インデックスはデータベースのストレージ使用量に加算されます。インデックスは実質的にテーブルそのものです。