一覧DB-07-1 · インデックスと実行計画

インデックスの効果と更新コスト

120商品の表で、商品コード検索の計画を索引の追加前後で取得します。表本体の参照が不要になる被覆索引、索引列の更新時の命令、削除後の計画まで調べます。SQLiteのEXPLAIN QUERY PLANとbytecode仮想表を使い、時間の速さではなく探索方法の変化を確認します。

説明

インデックス（索引）は、検索する値から対象の行へ到達するための追加の構造です。検索を絞れる場合には読む範囲を減らせますが、保存領域と更新処理も必要になります。索引の有無だけでなく、実行計画と返された値を合わせて確認します。

1120商品の検索表

ID 1～120に商品コードP001～P120と固定の価格を割り当てます。

CREATE TABLE items(id INTEGER PRIMARY KEY,code TEXT,price INTEGER) STRICT;

WITH RECURSIVE n(i)連番を作る補助問合せ AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<120) INSERT INTO items SELECT i,printf('P%03d',i)Pと三桁の番号,(i%10)*100+50固定の価格計算

 FROM n;
SELECT COUNT(*) AS rows,MIN(code) AS first_code,MAX(code) AS last_code FROM items;

WITH RECURSIVEは1から120までの連番を作るために使っています。priceはIDの下一桁×100+50円です。idは主キーですが、codeにはまだ索引がありません。

2索引なしの計画

P042を探すSELECTの計画を表示してから実行します。

EXPLAIN QUERY PLAN実行方法の概要 SELECT price FROM items WHERE code='P042'検索する商品コード

;
SELECT price FROM items WHERE code='P042';

SCAN itemsと表示され、価格は250円です。SCANは全体をたどる方法を示します。codeの条件で探索範囲を直接絞る索引がないため、表の行を調べて一致するものを選びます。

3商品コードの索引を作る

codeをキーとする索引を追加します。

CREATE INDEX items_code ON items(code)code順の索引を作る

;
PRAGMA

index_info(items_code)索引の列を確認;

索引の列はcodeです。通常のSQLiteの索引には行を指すrowidも保持され、同じcodeが複数あっても各行へ対応できます。ここでは一意制約を追加したわけではありません。

4同じ検索がSEARCHになる

同じ価格検索の計画と値を取得します。

EXPLAIN QUERY PLAN索引追加後の計画 SELECT price FROM items WHERE code='P042'同じ条件と同じ結果

;
SELECT price FROM items WHERE code='P042';

SEARCH items USING INDEX items_code (code=?)です。codeが一致する範囲を索引で探し、priceは表本体から取り出します。返る値は250円のままで、変わったのは取得方法です。

5コードだけなら索引で足りる

取得列をcodeだけにします。

EXPLAIN QUERY PLAN SELECT code索引に含まれる列だけを取得

 FROM items WHERE code='P042';
SELECT code FROM items WHERE code='P042';

COVERING INDEXと表示されます。この問合せで必要な条件と取得列を索引内の値だけで満たせるため、表本体からpriceなどを読む必要がありません。被覆は索引自体の固定の種類ではなく、問合せが要求する列との関係です。

6価格も含む索引へ変える

code・priceの複合索引を作り、単独のcode索引を外します。

CREATE INDEX items_code_price ON items(code,price)検索キーに価格を含める

DROP INDEX items_code不要になった単独索引を削除

;
EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='P042';

価格を返す検索もCOVERING INDEXになります。codeから一致範囲を探し、同じ索引内にあるpriceを返せます。ただし追加した列の分だけ索引が保持する情報と、更新時に扱う値も増えます。

7表と索引が別の木として保存される

sqlite_schemaで表と索引のルートページを確認します。

SELECT type,name,rootpage種類と保存構造の開始ページ FROM sqlite_schema WHERE name IN ('items','items_code_price'表本体と索引を比較) ORDER BY type DESC;

itemsとitems_code_priceには異なるルートページがあります。通常のrowid表と索引は、それぞれ別のB-treeとして保存されます。索引は表への名前の別名ではなく、維持すべき追加のデータ構造です。

8索引列を更新するときの命令

UPDATEを実行する前に、索引操作の命令だけを表示します。

SELECT opcode,p1 FROM bytecode('UPDATE items SET price=300 WHERE id=42')更新文の命令を取得 WHERE opcode IN ('IdxDelete','IdxInsert')索引の削除・追加命令に絞る ORDER BY addr;

IdxDeleteとIdxInsertが現れます。priceを索引へ含めたため、価格変更では古い索引エントリを除き、新しい値のエントリを入れる処理が必要です。bytecodeは生成された命令を表示し、このSELECT自体ではUPDATEを実行していません。

9更新後の値を索引経由で読む

価格を300円へ更新し、商品コードで取得します。

UPDATE items SET price=300実際の保存値を更新

 WHERE id=42;
SELECT code,price FROM items WHERE code='P042';

PRAGMA integrity_check表と索引の整合性を検査;

P042の価格は300円で、整合性検査はokです。索引から返る価格も更新後の値になります。索引を追加すると読む経路は変わりますが、更新との整合性はDBが保つ必要があります。

10索引を外して同じ検索を実行する

複合索引を削除します。

DROP INDEX items_code_price追加構造を削除

EXPLAIN QUERY PLAN探索方法が戻ったか確認

 SELECT price FROM items WHERE code='P042';
SELECT price FROM items WHERE code='P042';

計画はSCANへ戻り、値は300円のままです。索引を削除しても商品行は失われません。索引の効果は検索方法・保存量・更新負担から判断し、この小さな例の計画だけで実運用の速度向上率を決めることはできません。

まとめ

索引を使ったSEARCHでも、取得列によって表本体の参照が必要になります。

被覆索引は問合せで必要な値を索引内だけで満たします。

索引列の変更は索引の更新も伴い、削除しても元の表の値は保持されます。

操作履歴と解説を振り返れます。

SQLite 3.54.0

CREATE TABLE items(id INTEGER PRIMARY KEY,code TEXT,price INTEGER) STRICT;
WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<120) INSERT INTO items SELECT i,printf('P%03d',i),(i%10)*100+50 FROM n;
SELECT COUNT(*) AS rows,MIN(code) AS first_code,MAX(code) AS last_code FROM items;

╭──────┬────────────┬───────────╮
│ rows │ first_code │ last_code │
╞══════╪════════════╪═══════════╡
│  120 │ P001       │ P120      │
╰──────┴────────────┴───────────╯

EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='P042';
SELECT price FROM items WHERE code='P042';

QUERY PLAN
`--SCAN items
╭───────╮
│ price │
╞═══════╡
│   250 │
╰───────╯

CREATE INDEX items_code ON items(code);
PRAGMA index_info(items_code);

╭───────┬─────┬──────╮
│ seqno │ cid │ name │
╞═══════╪═════╪══════╡
│     0 │   1 │ code │
╰───────┴─────┴──────╯

EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='P042';
SELECT price FROM items WHERE code='P042';

QUERY PLAN
`--SEARCH items USING INDEX items_code (code=?)
╭───────╮
│ price │
╞═══════╡
│   250 │
╰───────╯

EXPLAIN QUERY PLAN SELECT code FROM items WHERE code='P042';
SELECT code FROM items WHERE code='P042';

QUERY PLAN
`--SEARCH items USING COVERING INDEX items_code (code=?)
╭──────╮
│ code │
╞══════╡
│ P042 │
╰──────╯

CREATE INDEX items_code_price ON items(code,price);
DROP INDEX items_code;
EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='P042';

QUERY PLAN
`--SEARCH items USING COVERING INDEX items_code_price (code=?)

SELECT type,name,rootpage FROM sqlite_schema WHERE name IN ('items','items_code_price') ORDER BY type DESC;

╭───────┬──────────────────┬──────────╮
│ type  │       name       │ rootpage │
╞═══════╪══════════════════╪══════════╡
│ table │ items            │        2 │
│ index │ items_code_price │        4 │
╰───────┴──────────────────┴──────────╯

SELECT opcode,p1 FROM bytecode('UPDATE items SET price=300 WHERE id=42') WHERE opcode IN ('IdxDelete','IdxInsert') ORDER BY addr;

╭───────────┬────╮
│  opcode   │ p1 │
╞═══════════╪════╡
│ IdxDelete │  1 │
│ IdxInsert │  1 │
╰───────────┴────╯

UPDATE items SET price=300 WHERE id=42;
SELECT code,price FROM items WHERE code='P042';
PRAGMA integrity_check;

╭──────┬───────╮
│ code │ price │
╞══════╪═══════╡
│ P042 │   300 │
╰──────┴───────╯
╭─────────────────╮
│ integrity_check │
╞═════════════════╡
│ ok              │
╰─────────────────╯

DROP INDEX items_code_price;
EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='P042';
SELECT price FROM items WHERE code='P042';

QUERY PLAN
`--SCAN items
╭───────╮
│ price │
╞═══════╡
│   300 │
╰───────╯

演習完了