1 年後のショップと、この計器盤

実物: 1 年後のテーブル

第一部 0 章で数十 KB だった表が、order_items だけで 2GB になっている。table_rows は推定値で、正確な行数はこのあと数える。表の名前と大きさを見たので、次は列の並びを見る。

第一部との違いが 2 つある。どちらも、行数が増えたときに現場でよく行われる判断だ。

1 つ目は、order_items に customer_id、status、ordered_at が複製されていること。第一部 4 章の正規化では「同じ事実を 2 か所に書かない」と言った。 それを破っている。理由は、このショップで最も多く実行されるクエリが「この顧客の直近の購入明細」で、正規化したままだと毎回 orders と JOIN する必要があるからだ。 複製すれば order_items だけで答えられる。代償として、注文の status を変えるときは orders と order_items の両方を更新する必要がある(第一部 7 章のトランザクションで)。 これを非正規化と呼び、「読みが多く、更新の経路を限定できる」列に対して行う。

2 つ目は、外部キー制約を付けていないこと。第一部 4 章で、存在しない顧客の注文を DB が拒否するのを見た。 1 秒に数千行を書き込むテーブルでは、書き込みごとに親の行を確認するコストが無視できなくなり、大規模なサービスでは制約を外してアプリ側で整合性を担保することがある。 この教材の第二部もそうしている。「制約を外すと何が守られなくなるか」を第一部で理解した上で外す、という順序が大事だ。

実物: 計器盤

第一部 8 章で一度見た 4 つのタブが、この第二部の主役になる。まず何も考えずに実行して、4 つの計器を見る。

数秒かかったはずだ。返した行数は 1、読んだ行数は 3,200 万、アクセス方法は Table scan。 これがマイページのアラートの正体で、第 1 章で直す。ここでは計器の意味を押さえる。

• 実行時間 — クエリ本体にかかった時間。1 秒を超えると赤、20ms 未満で青。
• 読んだ行数 — 結果を作るために InnoDB が触った行の数(Innodb_rows_read)。返した行数との差が「読んだのに捨てた量」。100 万を超えると赤。
• 返した行数 — 結果の行数。表示は先頭 500 行まで。
• アクセス方法 — 実行計画の葉ノード。Table scan なら全行を読み、Index lookup なら索引で直行した。

実物: 実行計画

「実測プラン」タブを開き、右上の「生テキスト」を押すと、DB が返した文字列そのものが出る。こういう形をしている。

-> Aggregate: count(0), avg(order_items.unit_price)  (cost=3.6e+6 rows=1) (actual time=4500..4500 rows=1 loops=1)
    -> Filter: (order_items.customer_id = 123456)  (cost=3.3e+6 rows=3.2e+6) (actual time=190..4500 rows=64 loops=1)
        -> Table scan on order_items  (cost=3.3e+6 rows=32e+6) (actual time=0.5..3900 rows=32e+6 loops=1)

これが実行計画で、「DB がこの SQL をどういう手順で処理したか」の記録だ。3 行あり、下から上へ読む。 一番下で order_items を全部読み(Table scan)、その上で customer_id が 123456 の行だけ残し(Filter)、一番上で数えて平均した(Aggregate)。 手順を決めた DB 内部の部品をオプティマイザと呼ぶ。読み方の詳細は第 2 章でやる。

4 つのタブの役割はこうなる。

• 結果 — 結果セット。
• 推定プラン — 実行する前にオプティマイザが立てた計画(EXPLAIN FORMAT=TREE)。rows= はすべて見積もり。
• 実測プラン — 実行して測った計画(EXPLAIN ANALYZE)。各行に見積もりと実測が並び、ラボはそれを 2 本のバーで表示する。ズレが 10 倍を超えると赤い注意書きが出る。行をクリックすると演算子の解説が開く。
• 統計 — 実行前後のサーバー統計の差分。ディスクから読んだページ数、一時テーブル、ソートの回数など。

4 つの計器は、このタブのどこかの値を抜き出して上段に並べたものだ。実行時間だけはタブになく、ラボが測っている。

c0-first の 4 つの計器を、4 つのタブに結ぶとこうなる。

推定と実測の 2 つがある理由は、オプティマイザが実行前の見積もりで計画を選ぶからだ。上の計画では「customer_id = 123456 は 320 万行残るだろう」と見積もっている(rows=3.2e+6)。 実際は 64 行。見積もりが外れれば、選ばれる計画も外れることがある。第 3 章の障害はそれで起きる。

いきなり本番の罠: COUNT(*)

第一部 3 章で一瞬だった COUNT(*) を、3,200 万行で叩く。かかる時間を予測してから実行する。

数秒かかったはずだ。InnoDB は行数をどこにも記録していないので、COUNT(*) は一番小さい索引(今は主キーしかないので本体)を端から端まで数える。 管理画面に「総注文数」を素朴に出すと、ページを開くたびにこの数秒が発生する。正確な数が要らないなら information_schema の推定値、要るなら集計済みの表を別に持つ。

温まったキャッシュ、冷えたキャッシュ

同じクエリを続けて 2 回実行し、実行時間と、統計タブの Innodb_buffer_pool_reads を比べる。

2 回目のほうが速く、Innodb_buffer_pool_reads が 0 に近づいたはずだ。1 回目はディスクからページを読み、2 回目はメモリ(バッファプール)にあるものを使った。 つまり実行時間には「クエリの良し悪し」と「キャッシュの温度」が混ざっている。 この教材でクエリ同士を比べるときは、必ず 2 回ずつ実行して 2 回目同士で比べること。

この画面の約束

• 1 回に実行できる文は 1 つ。
• SELECT は 60 秒でタイムアウトする。「終わらなかった」こと自体が結果になる章がある。
• 索引の作成(CREATE INDEX)にはタイムアウトがない。3,200 万行への索引作成は 1〜2 分かかる。
• 各章は前の章で作った索引を前提にすることがある。本文冒頭の「前提」に書く。

1 年後の DB は、同じ表が 3,200 万行になり、非正規化と制約の省略という現場の判断が加わっている。計器盤は「何行読んだか」と「どう探したか」を見せる。

次の章へ

マイページのアラートを止める。顧客 123456 の 64 行のために 3,200 万行を読んでいる理由と、索引の中身を開ける。