統計と最適化器 — ANALYZE と選択率

本記事で使うデータ — perf_sales（5 万行の売上）

本記事では、データベースがどの索引を使うかをどう決めているかを扱います。

鍵になるのが統計（statistics）です。

統計とは「テーブルに何行あるか」「ある列が何種類の値を持つか」といったデータの分布情報で、ANALYZEコマンドで収集します。

データベースはこの統計から選択率（selectivity、条件で絞ったとき何割の行が残るかの見積もり）を計算し、索引を使うべきか全件読むべきかを判断します。

売上テーブルperf_sales（5 万行）を用います。

emp_id（担当者 30 種）、region（East / West / North / South の 4 種）、product（P001〜P200 の 200 種）、status（paid が大半 / pending / refunded）など、種類の多さ（カーディナリティ）の異なる列を持ちます。

ANALYZEの前と後でプランがどう変わるかを観察します。

演習に入る前に、perf_salesテーブルの列定義とデータのサンプル、そして各列の値の散らばり方を確認しておきます。データ生成に少し時間がかかるので、最初の実行は数秒待つことがあります。

① PRAGMA table_info(perf_sales);で列名・型・主キーを確認してください。

② SELECT * FROM perf_sales LIMIT 5;で先頭 5 行をプレビューしてください。

③ regionの値ごとの件数を数えて、値の散らばり（カーディナリティ）を確認してください。

ANALYZE と sqlite_stat1 — 統計を集める

ANALYZEを実行すると、データベースはテーブルと索引を走査して統計を集め、sqlite_stat1という内部テーブルに保存します。

sqlite_stat1には「どの索引が、平均して 1 つの値あたり何行に対応するか」が記録されます。

ANALYZEを実行することで、実データに基づく統計がつくられ正確な実行計画の作成に貢献できます。

ANALYZE単体で全テーブル、ANALYZE 表名で特定テーブルだけを対象にできます。

-- 種類数の違う 2 列に索引を作る（status=3 種 / qty=20 種）
DROP INDEX IF EXISTS ix_status;
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_status ON perf_sales(status);
CREATE INDEX ix_qty    ON perf_sales(qty);

-- 統計を収集して中身を見る（1 値あたり平均行数）
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'perf_sales' ORDER BY idx;
--> ix_status: 50000 16667 のような行（1 値あたり約 16667 行）
--> ix_qty:    50000 2500 のような行（1 値あたり約 2500 行）

「どの索引がどれだけ絞り込めるか」を表す統計を、実際に収集して読み取ります。1 回の実行で索引作成・統計収集・確認まで完結させてください。（正しく実行できれば解説が表示されます）

① DROP INDEX IF EXISTSで同名索引を消してから、emp_idの索引とregionの索引をCREATE INDEXで 1 つずつ作成してください。

② ANALYZE;を実行して統計を収集してください。

③ sqlite_stat1からtblがperf_salesの行を取り出し、各索引のstat列（全行数と 1 値あたりの平均行数）を確認してください。

選択率でプランが変わる — 効く列と効かない列

選択率（selectivity）は「条件で絞ったとき、全体の何割の行が残るか」の見積もりです。

残る割合が小さいほど選択率が高く、索引が効きます。

emp_id = 7は約 1,667 行（全体の約 3%）に絞れるので選択率が高く、索引を使う価値があります。

一方status = 'paid'は約 40,000 行（全体の約 80%）が残るため選択率が低く、索引で 1 行ずつたどるより全件を順に読んだ方が速くなります。

統計を集めた後、最適化器（クエリプランナ）はこの選択率を見て、索引を使う計画と全件読む計画のどちらが安いかを比較します。

複合索引や複数候補があるときも、統計に基づいて「より少ない行に絞れる索引」を優先します。

同じテーブル・同じ索引構成でも、WHEREで指定する列の選択率と、取り出す列（索引に含まれるか）によって計画が変わるのを次の演習で確認します。

-- 選択率の高い列(amount:ほぼ一意)と低い列(status:3 種)に索引を作り
-- ANALYZE 後に同じ形のクエリで計画を比べる
DROP INDEX IF EXISTS ix_amount;
DROP INDEX IF EXISTS ix_status;
CREATE INDEX ix_amount ON perf_sales(amount);
CREATE INDEX ix_status ON perf_sales(status);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount = 500000;
--> SEARCH perf_sales USING INDEX ix_amount (amount=?)

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'paid';
--> 選択率が低いため SCAN 寄りの計画になりうる

「ほとんどの行に該当しない条件」と「ほとんどの行に該当する条件」を、索引に含まれない列を取り出す形のクエリで比べます。COUNT(*) だと SQLite は索引だけで数え終えてしまい、選択率に関係なく索引が使われがちなので、本演習では SELECT sale_id, amount のように 索引に無い `amount` を取り出す形 にして、低選択率では SCAN が選ばれる様子を見ます。

① DROP INDEX IF EXISTSで同名索引を消してから、emp_idの索引とstatusの索引を作り、ANALYZE;で統計を収集してください。

② EXPLAIN QUERY PLANを付けて、emp_id = 7の行のsale_idとamountを取るクエリの計画を確認してください（高選択率: 約 1,667 行 / 約 3%）。

③ 続けてEXPLAIN QUERY PLANを付けて、status = 'paid'の行のsale_idとamountを取るクエリの計画も確認し、emp_id側とstatus側でプランがSEARCHとSCANに分かれることを読み取ってください（低選択率: 約 40,000 行 / 約 80%）。

本講座のコンソールでは低選択率でも INDEX SCAN が残ることがある

実は本講座のブラウザコンソール（メモリ上で動く SQLite）では、③のstatus = 'paid'の計画もSEARCH perf_sales USING INDEX ix_status (status=?)のままになる場合があります。理由は以下のとおりです。

- メモリ上で動作している: ディスク DB なら「索引経由のランダム I/O」と「順次 SCAN」のコスト差が大きく低選択率で SCAN が有利になりますが、メモリ上ではどちらも高速で差が出にくい

- 索引エントリが値の順で並んでいる: ix_statusはstatus順に並ぶため、'paid'の塊を連続範囲として読むだけで済み、ランダムアクセスにはならない

- rowid 経由の本体アクセスが速い: SQLite は内部のrowidで本体行を直接引けるため、索引経由の本体読み出しもそこそこ安い

選択率が低くなったときに`SCAN`に明確に切り替わる挙動は、PostgreSQL や Oracleのような実ディスク + 本格的なコストベース最適化器を持つ RDBMS でより観察しやすい現象です。本演習では「同じ索引構成でも、選択率と取り出す列の組合せによって最適化器の判断が変わる原理がある」ことを押さえてください。実際のSCAN/SEARCHの境界線は DB エンジン・ディスク有無・統計の精度によって動きます。

コストベース最適化とバインド変数 — 他 DB の内部のしくみ

ここまで見た「統計から選択率を見積もり、最も安い計画を選ぶ」しくみをコストベースオプティマイザ（cost-based optimizer、CBO）と呼びます。

Oracle や SQL Server などの大規模 DB では、この内部にさらに細かい段階があります。

1 つはパース（SQL 文の解析）で、初めて見る SQL を解析して計画を作るのがハードパース、過去に解析済みの計画を再利用するのがソフトパースです。

もう 1 つがバインド変数（bind variable、SQL に値を直接書かず?のような場所に後から値を渡すしくみ）と、その値の偏りを見て計画を選ぶバインド変数ピーク（bind peeking）です。

これらは大規模 DB の実行エンジン内部のしくみで、概念として知っておくとチューニングの議論が理解しやすくなります。

本講座のブラウザコンソールでは、選択率による計画の変化（前節）までを実際に観察できますが、ハードパース / ソフトパースの切り替わりやバインド変数ピークの挙動そのものは下の callout のとおり再現できないため、図と読むだけのコードで概念を押さえます。

ハードパース / ソフトパース・バインド変数ピークは Oracle 系の内部しくみ

ハードパースとソフトパースの切り替わり、およびバインド変数ピーク（渡された値の偏りで計画を選び直す挙動）は、Oracle や SQL Server など大規模 DB の実行エンジン内部のしくみです。

本講座のブラウザコンソールでは、この内部状態を観察するためのビュー（Oracle のV$SQLなど）が無いため、コンソール上で実演して見せることはできません。

ここでは概念図と、下の読むだけのコード例で「どういう考え方か」を押さえます。

一方、本記事の前半で扱ったANALYZEによる統計収集と、選択率でプランが変わる挙動は実際に観察できます。

コストベース最適化の中核である「統計→選択率→計画選択」の流れは本講座のコンソールで手を動かして確認できるので、まずそこを体得してください。

-- 以下は Oracle での書き方のイメージ（読むだけ・本講座のコンソールでは実行しない）
-- バインド変数で値を後渡しする（:s は実行時に値が入る）
-- SELECT * FROM perf_sales WHERE status = :s;
--
-- 同じ SQL 文字列なら計画を再利用 = ソフトパース
-- 文字列が少しでも違うと作り直し = ハードパース
-- Oracle なら V$SQL.SQL_TEXT で解析状況を見るが、本講座では行わない

-- 本講座のコンソールで実際に観察できるのはこちら:
-- 統計を集めると選択率の見積もりが実データに基づく
ANALYZE;
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'pending';
--> SEARCH perf_sales USING INDEX ... (status=?)

コストベース最適化のうち、本講座のコンソールで実際に観察できる「統計を集めると見積もりが変わる」部分を、product列（200 種・選択率が高い）で確認します。1 回の実行で索引作成・統計収集前後の計画確認まで完結させてください。

① DROP INDEX IF EXISTSで同名索引を消してから、product列の索引を作成してください。

② EXPLAIN QUERY PLANを付けて、productが'P050'の件数を数えるクエリの計画を確認してください。

③ ANALYZE;で統計を収集したあと、もう一度同じクエリのEXPLAIN QUERY PLANを実行してください。productは 200 種で選択率が高いため、統計収集後も索引が使われる計画になることを読み取ってください。