SQL クエリからストレージ読み取りへ

SQL 実行は求める結果を計画、ページ要求、返却行へ変える。索引は訪れるページを絞り、データベースのバッファープールは、そのアクセスにストレージ読み取りが必要かを決める。本例は最初に三ページ読んで Mira を返し、二回目も Mira を返すが追加読み取りはゼロになる。

ページを要求することと、ストレージから読むことは別だ。同じページを二回要求しても一度だけ読める。バッファープールが再利用するのはメモリー上のページコピーで、各 SQL の完成済みの答えではない。

SQL がストレージへ届く仕組み

クエリは SELECT name FROM users WHERE id = 42;。解析が射影 name、関係 users、条件 id=42 を識別し、バインドがスキーマで名前を解決する。本レッスンはページ依存を見せるため Index Scan を直接選ぶ。実プランナーは推定コストを比べ、小さい表なら順次走査を選ぶこともある。PostgreSQL のクエリ経路 が段階の境界を説明している。

二次索引は id と行の位置、つまりヒープページとスロットを保存し、name は持たない。42 を見つけても次に読む場所が決まるだけで、名前はまだ得ていない。ヒープのタプルを取得して name を射影する必要がある。二次索引とヒープ保存の分離 に対応する。

根や葉を含む全ページアクセスはバッファープールを通る。ヒットなら既存コピーを使い、ミスならストレージへ要求して、返ったページを空きフレームへ置く。元ページはストレージに残り、実行器は到着後に内容を調べる。

下の箱は Storage と呼ぶ。データベースの読み取り要求が物理ディスクや SSD のアクセスを証明するわけではなく、OS キャッシュが応える場合もある。計数はバッファー層の下でのモデル化したページ取得で、実測の機器 I/O ではない。PostgreSQL の I/O 統計説明 にもこの区別がある。

SQL から読み取りまでのステップ

既定は二回実行する。四フレームが空の状態で一度計画を準備し、ページを保ったまま同じ計画を再実行する。データは変わらない。

根 R の分離キーは 40 で、<40 は L0、≥40 は L1。L1 は 40、42、63、91 とヒープ位置を持つ。ヒープの物理順は索引順とは違う。

1. SELECT name FROM users WHERE id = 42; を解析、バインドする。id 索引に name がないため、Index Scan にヒープ取得が続く。
2. 根 R を要求する。空バッファーでミスし、ストレージが全ページを返してフレームへ残す。読み取り数は 1。42 と 40 を比べ L1 を選ぶ。
3. L1 もミスし、二回目の読み取りで 42 → H3 / slot 2 を見つける。name はまだ未知。
4. H3 は三回目のミス。全ページに 35 · Dee と 42 · Mira が届き、スロット 2 から name を射影して Mira を返す。要求 3、ヒット 0、読み取り 3。
5. 結果と実行ごとの計数をリセットし、R、L1、H3 を残して再実行する。同じ三要求が全てヒットする。根の比較、葉の検索、位置の追跡、射影は再び行い、Mira を返す。要求 3、ヒット 3、読み取り 0。
6. クエリ、計画、結果は同じで、変わったのはページの常駐状態。合計六要求、三読み取りで、二回目自身の読み取りはゼロ。

温かいバッファーが読み取りを省く理由

本モデルの完了した実行では page requests = buffer hits + storage reads が成り立つ。数えるのは要求で、異なるページ ID だけではない。ミスの読み取り待ち中にはまだ等式が成立しないこともある。読み取りは転送場面で一度数え、フレームへの設置では重複計上しない。

温かいプリセットのページは初期状態であり、以前のロード費用は今回に含めない。再実行前に消せば、また三回読む。同じ SQL であること自体が常駐を保つわけではない。

索引はアクセス経路、キャッシュは各アクセスを満たすコストを変える。高い木では各層の一ページと、該当時のヒープページを訪れる。順次走査なら全ヒープページを訪れる。ストレージまで行くかは常駐次第だ。ページ数は CPU や局所性、遅延の全てを含まず、少ない読み取りだけで常に速いとは言えない。索引レッスン で比較を扱う。

境界ケース

• 43 は L1 の範囲だが存在しない。R と L1 で不在が分かり、位置が得られないのでヒープ要求はしない。空結果も成功した実行だ。
• 40 は分離キーと同じなので右へ行く。分割は <40 と ≥40。7 と 91 は最初と最後のキーを試せる。
• 索引なしでは四ページ全てを読む。7 が H0 で見つかっても H3 まで続ける。
• 温かい状態で存在しないキーを探すなら、根と候補葉だけが常駐すればよい。二ヒット、ゼロ行、ゼロ読み取り。
• 最大四ページなので本例は追い出さない。大きな作業集合では温かい再実行の利点を失いうる。ページ置換 が方針を扱う。
• UI は整数 0–99 に限る。SQL の限界ではない。行は八つで、他の有効値はゼロ行を返す。

よくある間違い

• バッファーミスを行なしと考える。ページがメモリーにないだけで、キーは到着後に調べる。
• H3 / slot 2 を射影値として返す。位置であって名前ではない。カバリング索引なら name を持てるが、本索引は持たない。
• 一行で一読み取りと数える。単位はページで、H3 の二行は一緒に届く。
• ゼロ読み取りを仕事ゼロと考える。要求、検索、タプル処理は残る。
• 計数を物理機器 I/O と呼ぶ。モデルはストレージ境界までで、機器、OS キャッシュ、実時間には追加測定が必要になる。

モデルの範囲

小さな二次 B+ 木、非クラスタ化ヒープ、固定の可視行、一つの等値条件、四フレームを使う。カタログ・計画 I/O、索引メタページ、MVCC 可視性変化、ロック、並行クエリ、追い出し、書き込み、WAL、先読み、OS・機器キャッシュ、実コスト推定は省く。容量、スロット、計数はこの例に属し、実エンジンは追加処理や別計画を選びうる。