0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@my-agent-works(合同会社 My Agent Works)

SQLite / Cloudflare D1 で「その索引は使われているか」を確かめる ― 実行計画・統計・読み取り行数の見方

0
Posted at

はじめに

索引を作ったことと、その索引が使われていることは別です。使われるかどうかは、問い合わせのたびに SQLite の実行計画（クエリプランナ）が決めます。

この記事では、索引が使われているかを確かめる方法を、手元の SQLite での実験と、Cloudflare D1 で使える道具の両方で紹介します。

  1. EXPLAIN QUERY PLAN で実行計画を見る
  2. PRAGMA optimize / ANALYZE で統計を作り、中身を読む
  3. D1 の応答に含まれる「読んだ行数」（meta.rows_read）を見る
  4. D1 の問い合わせ単位の集計から、重い問い合わせを探す

この記事の要点

  • 索引が使われるかは実行計画が決める。作ったら EXPLAIN QUERY PLAN で使われていることを確かめる
  • 部分索引は、問い合わせの条件が索引の条件を含意するときだけ使える
  • LIMIT は該当0件の問い合わせを止めない。D1 なら meta.rows_read で1回に読んだ行数が分かる

環境

項目 内容
手元の実験 SQLite 3.50.4（Python 3.13.7 付属の sqlite3）・メモリ上のデータベース
D1 Cloudflare D1（SQLite 互換）・Workers から利用
データ 20万行の架空の表（下のコードで作成）

1. 実験用の表を作る

20万行の表を作ります。7割が active、画像の URL は6割が空です。

import random, sqlite3, time

random.seed(42)
con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE items (
  id INTEGER PRIMARY KEY, status TEXT NOT NULL, url TEXT, image_url TEXT,
  updated_at TEXT NOT NULL, processed_at TEXT)""")
rows = []
for i in range(200_000):
    status = "active" if random.random() < 0.7 else "closed"
    url = f"https://example.com/{i}" if random.random() < 0.95 else None
    image = None if random.random() < 0.6 else f"https://img.example.com/{i}.jpg"
    upd = f"2026-0{random.randint(1, 8)}-{random.randint(10, 28)} {random.randint(10, 23)}:00:00"
    processed = upd if random.random() < 0.999 else None
    rows.append((i, status, url, image, upd, processed))
con.executemany("INSERT INTO items VALUES (?,?,?,?,?,?)", rows)

調べる問い合わせは「公開中で画像がまだ無いものを、更新日の新しい順に10件」です。

SELECT id, url FROM items
WHERE status = 'active' AND url IS NOT NULL AND url != ''
  AND (image_url IS NULL OR image_url = '')
ORDER BY updated_at DESC LIMIT 10;

2. EXPLAIN QUERY PLAN で実行計画を見る

問い合わせの前に EXPLAIN QUERY PLAN を付けると、どう読むつもりかが返ります（SQLite: EXPLAIN QUERY PLAN）。

def plan(sql):
    return [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql)]

索引が無い状態の結果です。

plan: ['SCAN items', 'USE TEMP B-TREE FOR ORDER BY']
平均 18.96 ms
  • SCAN items … 表を先頭から最後まで読む
  • USE TEMP B-TREE FOR ORDER BY … 条件に合う行を全部集めてから、一時的な木で並べ替える

欲しいのは10件だけですが、並べ替えるために該当行を全部集めるので、LIMIT 10 があっても表全体を読みます。

3. 条件と並び順を持つ部分索引を作る

条件の一部（status・url）を WHERE に持ち、並び順（updated_at DESC）を列に持つ部分索引を作ります（SQLite: Partial Indexes）。

CREATE INDEX idx_items_active_updated ON items(updated_at DESC)
  WHERE status = 'active' AND url IS NOT NULL AND url != '';

作った直後の結果です。

sqlite_stat1 表: まだ無い（ANALYZE を一度も実行していない）
plan: ['SCAN items USING INDEX idx_items_active_updated']
平均 0.01 ms

計画が SCAN items USING INDEX … に変わり、USE TEMP B-TREE が消えました。索引を新しい順にたどり、条件に合う行が10件そろった時点で止まれるようになったためです。

この実験では、統計（次の章の sqlite_stat1）がまだ無い状態でも索引が使われました。ただ、どの索引を選ぶかは表の大きさや条件によって変わり、作った直後には前の計画のまま、ということもありえます。Cloudflare の D1 のドキュメントは、索引を作ったあとに PRAGMA optimize を実行するよう案内しています。どちらにしても、「作った」で終わらせず、実行計画で使われていることまで確かめるのが確実です。

4. PRAGMA optimize で統計を作り、中身を読む

con.execute("PRAGMA optimize")
print(con.execute("SELECT tbl, idx, stat FROM sqlite_stat1 ORDER BY idx").fetchall())

stat: [('items', None, '200000'), ('items', 'idx_items_active_updated', '132819 59')]

sqlite_stat1 は、プランナが索引を選ぶときに使う統計の表です（SQLite: The sqlite_stat1 table）。

  • ('items', None, '200000') … 表 items はおよそ20万行
  • ('items', 'idx_items_active_updated', '132819 59') … この索引にはおよそ 132,819 行が入っていて（部分索引なので条件に合う行だけ）、updated_at が同じ値の行は平均およそ59行

2つ目の数は見積もりです。最近の SQLite の PRAGMA optimize は、時間をかけすぎないよう読む行数を絞って統計を作ります（SQLite: PRAGMA optimize）。

Cloudflare の D1 のドキュメントは、索引を作ったあとの PRAGMA optimize について「各表に ANALYZE を実行し、表と索引の統計を集める」と説明しています（D1: Use indexes）。

5. 部分索引が候補にならない条件

部分索引は、問い合わせの WHERE が索引の WHERE を含意すると証明できる場合にだけ使えます。条件を少し変えて試しました。

-- url が NULL の行も「OR image_url があれば」対象になる。索引の条件（url IS NOT NULL）を含意しない
SELECT id, url FROM items
WHERE status = 'active' AND (url IS NOT NULL OR image_url IS NOT NULL)
ORDER BY updated_at DESC LIMIT 10;

plan: ['SCAN items', 'USE TEMP B-TREE FOR ORDER BY']

この問い合わせは「url が無いが image_url がある行」も対象にします。その行は索引に入っていないので、索引だけでは答えられません。プランナは索引を使わず、表全体を読む計画に戻りました。

部分索引は、問い合わせの条件が索引の条件と同じ形（またはそれより狭い形）で書けるときに頼るものです。問い合わせ側の条件を変えたら、実行計画を見直します。

6. LIMIT は「該当0件」の問い合わせを止めない

LIMIT で読むのを止められるのは、条件に合う行が指定の件数見つかったときです。該当が0件なら、最後まで読んでから0件を返します。次の2つを比べました。

-- 該当0件（どの行にも当てはまらない）
SELECT id FROM items WHERE status = 'archived' AND processed_at IS NULL LIMIT 10;

-- 該当が多い（先頭近くで10件そろう）
SELECT id FROM items WHERE status = 'active' LIMIT 10;

該当件数: 0
plan: ['SCAN items']
平均 8.67 ms
（比較）該当が多い問い合わせ plan: ['SCAN items'] 平均 0.004 ms

どちらも計画は同じ SCAN items ですが、該当の多い問い合わせは先頭の数十行で10件そろって止まり、該当0件の問い合わせは20万行を読み切っています。時間にして2,000倍以上の差です。

「処理待ちのものを探す」ような定期実行は、仕事が片付くほど該当が0件に近づき、毎回最後まで読む問い合わせになります。結果（0件）は正しく、エラーも出ないので、実行計画だけ見ていても気づけません。次の章の「読んだ行数」を見る理由がここにあります。

7. D1 では「読んだ行数」が応答に入っている

Cloudflare D1 は、問い合わせの結果と一緒に meta を返し、そこに読んだ行数 rows_read と書いた行数 rows_written が入っています（D1: Return objects）。

// Workers の中で
const r = await env.DB.prepare(
  "SELECT user_id, expires_at FROM sessions WHERE token_hash = ?"
).bind(hash).all();
console.log(r.meta.rows_read, r.meta.rows_written);

wrangler d1 execute --json でも同じ値が見られます。D1 のドキュメントも、meta.rows_read と meta.rows_written を見て費用の大きい問い合わせを探すよう案内しています（D1: Use indexes）。

実行計画は D1 でも同じように見られます。

CREATE INDEX idx_sessions_token_hash ON sessions(token_hash);

EXPLAIN QUERY PLAN
SELECT user_id, expires_at FROM sessions WHERE token_hash = ?;
-- detail: SEARCH sessions USING INDEX idx_sessions_token_hash (token_hash=?)

SEARCH … USING INDEX は、索引で目的の行へ直接たどる計画です。SCAN（全件）とは読む量が桁で違います。D1 の料金は、読んだ行数・書いた行数・保存量の3つで決まります。読んだ行数には、返した行数ではなく走査した行数が数えられます（D1: Pricing）。そのため、rows_read はそのまま費用の指標にもなります。

8. 問い合わせ単位で「重い常用の問い合わせ」を探す

1回ずつ見るのではなく、1日分をまとめて見るなら、Cloudflare の GraphQL Analytics API の d1QueriesAdaptiveGroups を使います（D1: Metrics and analytics）。問い合わせの文ごとに、回数と読んだ行数が集計されます。

query ($a: String!, $d: Date!) {
  viewer {
    accounts(filter: { accountTag: $a }) {
      d1QueriesAdaptiveGroups(
        limit: 25
        filter: { date: $d }
        orderBy: [sum_rowsRead_DESC]
      ) {
        count
        sum { rowsRead }
        dimensions { query databaseId }
      }
    }
  }
}

rowsRead ÷ count が1回あたりの読み取り行数です。これが大きく、しかも回数が多い問い合わせが「索引の効いていない常用の問い合わせ」です。D1 は問い合わせの文を記録しますが、バインドした値は記録しません。手元で確かめるなら wrangler d1 insights も使えます。毎日の監視なら、たとえば次のような判定で拾えます。

// 1回5,000行以上を1日200回以上、または1本で1日5,000万行以上
const PER_CALL = 5000, CALLS = 200, SINGLE = 50_000_000;
const hot = groups.filter((g) => {
  const n = g.sum?.rowsRead || 0, c = g.count || 1;
  return (n / c >= PER_CALL && c >= CALLS) || n >= SINGLE;
});

しきい値は表の大きさと利用規模で変わるので、自分の環境の平常時の値を見てから決めてください。データベース単位の合計だけでは「どの問い合わせか」まで分からないので、問い合わせ単位で見るのが近道です。

よくある質問

索引を作ったのに SCAN のままなのはなぜ？

よくある原因は3つです。問い合わせの条件が部分索引の条件を含意していない（第5章）、列を関数で包んでいて普通の索引が使えない、統計が無いか古い（第4章）。EXPLAIN QUERY PLAN の結果を見ながら、1つずつ確かめます。

SCAN と SEARCH はどう違う？

表示 意味 読む量
SCAN 表 表を先頭から読む 全件（LIMIT で早く止まることはある）
SCAN 表 USING INDEX 索引 索引の順にたどる 索引に入っている行まで
SEARCH 表 USING INDEX 索引 (列=?) 索引で目的の行へ直接たどる 該当する行の近くだけ
USE TEMP B-TREE FOR ORDER BY 並べ替えのために行を集める 条件に合う行を全部

まとめ

  • 索引が使われるかは、実行計画が決める。作ったら EXPLAIN QUERY PLAN で確かめる
  • 実行計画の読み方は3種類。SCAN 表（索引なし）は全件を読む。SCAN 表 USING INDEX 索引 は索引の順にたどる（部分索引なら条件に合う行だけで、LIMIT の件数がそろえば止まる）。SEARCH 表 USING INDEX 索引 (列=?) は索引で目的の行へ直接たどる。USE TEMP B-TREE FOR ORDER BY は並べ替えのために行を集めている印
  • 索引を作ったら PRAGMA optimize（D1 の公式の案内）。統計は sqlite_stat1 で読める
  • 部分索引は、問い合わせの条件が索引の条件を含意するときだけ使える
  • LIMIT は、該当0件の問い合わせを止めない
  • D1 なら meta.rows_read で1回の読み取り行数、d1QueriesAdaptiveGroups で1日の問い合わせ単位の集計が見られる

参考

筆者（合同会社My Agent Works）は、宅建士試験の学習アプリ「宅建BOOST」（https://takken-boost.jp/）を開発しています。開発で得た知見を今後も投稿します。
X: https://x.com/takkenboost

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?