実験・実装 · 入門

索引の前後でEXPLAINを比べる実験

Python標準ライブラリのメモリ内SQLiteで1万行を作り、検索結果を変えずにアクセス経路が変わることを確かめます。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. 今回変えるのは、索引の有無だけ
  2. 実行して、検索結果と計画を確認する
  3. 計画の変化から、何を言えるか
  4. 次に変える条件と、実験の限界
  5. この記事をロゲイアに質問する理解を確かめる

今回変えるのは、索引の有無だけ

同じ表と同じ問い合わせに対し、索引を追加する前後の計画を比べます。対象はownerが42の行です。item_idを0から9999まで作り、ownerを100で割った余りにするため、該当する行は100件になります。条件を固定しておくことで、検索結果の違いとアクセス方法の違いを分けて考えられます。

この実験はPythonのsqlite3モジュールを使い、データをメモリ内のデータベースだけに作ります。本番の接続情報や既存ファイルは使いません。SQLiteを含まないPython配布環境では実行できない場合があります。最初に、利用する環境でsqlite3を読み込めることを確認してください。

実行して、検索結果と計画を確認する

コードは索引の作成前後でEXPLAIN QUERY PLANを実行し、同じSELECTの結果も取得します。結果の順番はSQLで指定していないため、正しさの比較では並べ替えてから照合します。索引が加わって結果の取得順が変わっても、行の集合が同じなら、この実験で確認する意味は保たれています。

EXPLAINの出力は実装やバージョンで変わるので、文字列全体が例と完全一致することを成功条件にしません。表全体を走査する説明から、ownerの索引を使った検索の説明へ変化しているかを読み取ります。実際に出力されたSQLiteのバージョンも、一緒に記録しておきましょう。

python
import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE items(item_id INTEGER PRIMARY KEY, owner INTEGER)")
    con.executemany("INSERT INTO items VALUES (?, ?)",
                    ((i, i % 100) for i in range(10000)))
    sql = "SELECT item_id FROM items WHERE owner = ?"
    before = con.execute("EXPLAIN QUERY PLAN " + sql, (42,)).fetchall()
    rows_before = con.execute(sql, (42,)).fetchall()
    con.execute("CREATE INDEX items_owner_idx ON items(owner)")
    after = con.execute("EXPLAIN QUERY PLAN " + sql, (42,)).fetchall()
    rows_after = con.execute(sql, (42,)).fetchall()
    assert sorted(rows_before) == sorted(rows_after)
    assert len(rows_after) == 100
    print("SQLite:", sqlite3.sqlite_version)
    print("Before:", before)
    print("After:", after)
    print("Matching rows:", len(rows_after))
finally:
    con.close()

計画の変化から、何を言えるか

ownerの条件で少数の行を探すため、対応する索引は候補を絞るアクセス経路になります。この例では、結果の行数が変わらないことをassertで確かめています。計画が変わることと、答えの意味が変わることは別である、という点が観察の中心です。

一方、このコードは経過時間を測っていません。索引を使う計画になったからといって、「何倍速くなった」といった数値は導けません。メモリ内の小さな表での計画を、ディスク上の大規模な本番環境にそのまま当てはめることもできません。観測できたことの範囲を守って説明します。

次に変える条件と、実験の限界

ownerの種類を減らすと、一つのownerに該当する行が増えます。そのとき同じ索引がどのように選ばれるか、条件を一つずつ変えて調べられます。また、返す列を増やすと、索引だけで処理できるかどうかも変わる場合があります。変更前後の結果が同じかを確認する手順は、そのまま残します。

索引の追加には容量と更新時の維持コストもあります。この読み取り実験だけでは、書き込み負荷や同時実行への影響は分かりません。性能測定を追加する場合は、キャッシュ、繰り返し回数、分布、実行条件を記録し、計画の観察と時間の測定を別々に解釈しましょう。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 12

学習コースから次を選ぶ