第8章 後半: 関係データベースとパイプライン

• 表は依存に沿って分け、キーと制約で整合性を守る。NULL の比較と結合の行数は、思い込みで読まない。
• トランザクションは途中までの成功を残さない区切りで、索引は読み取りを助ける代わりに更新の仕事を増やす。
• パイプラインでは採用と隔離の件数を照合し、結果を公開する境界と再実行の意味を設計に含める。

教材の 8.3 節から 8.4 節まで。

8.3.1 表、キー、正規化

関係データベースの単位は、行と列でできた表と、表どうしのつながりである。主キーは行を見分け、外部キーは別の表のキーとの対応に制約をかける。文書ごとに著者の名前を繰り返し保存すると、著者の情報を変えたときに一部の行しか直らず、ずれが生まれる。教材はこれを更新異常と呼ぶ。文書 1 と 2 が著者 7 に属するなら、著者 7 と名前の対応は著者の表に 1 回だけ置き、文書の表には著者の番号だけを置く。これが正規化である。

細かく分けること自体が目的ではなく、キーで何が決まるのかを見極め、無駄な重複から来るずれを減らすのが狙いだ。X が決まれば Y も決まる、という関係は関数従属性と呼ばれる。表を分けても、著者の番号が重ならないことと参照の制約を守れば、結合で元の対応を組み直せる。番号を暗記して機械的に分割することはしない。

教材の例では、存在しない著者 99 の文書を登録しようとすると整合性の例外になる。SQLite は外部キーの検証を、接続のたびに有効にして使う。また、普通の表の型名は型親和性を与えるにとどまり、ほかの型の値を拒むとは限らない。CHECK の判定が NULL になるときは通常は違反扱いにならないので、場合によっては NOT NULL も付ける。表を分けると読むときに結合が要るため、整合性の利益と読み取りの費用を比べる。

8.3.2〜8.3.4 SELECT、NULL、結合、集約

SELECT でほしい列、WHERE で残す行、ORDER BY で出す順を書く。手順は書かず、ほしい結果だけを述べる形で、計画はデータベースが決める。欠損は IS NULL で探し、= NULL で比べると 1 件も見つからない。NULL は不明、または該当する値なしで、NULL を含む通常の比較は真でも偽でもない不明になり、WHERE は真だけを採る。値の受け渡しにはプレースホルダーを使うが、列名や表名には使えない。文字列をつなぎ合わせて SQL に埋めると、SQL インジェクションを招く。

内部結合は一致した組だけを返し、左外部結合は相手のいない左の行も NULL で埋めて残す。教材の例では、文書 3 件とトークン 3 件を左結合すると 4 行になる。条件を ON に書くか、結合後の WHERE に書くかで結果が変わり、後者では NULL の行が消えて、残したつもりの文書が失われる。結合の前後で件数を照合する。

GROUP BY はキーが同じ行を 1 つの組にする。COUNT(*) が数えるのは全行、COUNT(size) は値の入った行だけで、AVG も欠けた行を計算から外す。著者 7 の例では、平均は 4 割る 1 の 4 で、2 で割った 2 ではない。組を作る前に絞るのが WHERE、作った組を絞るのが HAVING である。結合してから合計すると、同じ値を何度も足すことがあり、数が合ったことだけでは粒度が正しい証拠にならない。

8.3.5〜8.3.6 トランザクションと索引

トランザクションは、いくつかの操作をまとめて成功させるか、まとめて取り消すかの区切りである。原子性は途中までの成功を残さないこと、分離性は同時に動く別の処理に何が見えるかに関わる。教材の例では、数を 3 から 2 へ更新したあと、制約に違反する行を入れて失敗し、取り消すと数は 3 に戻る。既定では失敗した文だけが取り消されるので、例外の処理で全体を戻している点が重要だ。SQLite は複数の書き込みを同時には進められず、競合すれば待機や例外が起こる。

索引は、検索のキーを整えた別の構造を持ち、そこから候補へ近づく仕組みである。SQLite が普通に使う索引は B 木系で、枝を選びながら葉へ降りる段の数が少なくて済む。探索の深さはおおむね対数に沿って増えるが、結果を読む費用が加わる。実行計画に索引の名前が現れるかは環境で変わるので、仕様とはみなさない。全体の大半を返す検索では全走査のほうが合理的なことがあり、索引は追加の領域と更新時の維持の費用を要する。

8.4.1〜8.4.3 パイプライン

接続はデータベースとの交信の状態を持ち、カーソルは文を実行して結果を受け取る窓口である。fetchone で取れるのは 1 行で、fetchall は未取得の行をすべてメモリに置く。with を使った接続は確定や取り消しを支援するが、接続を自動では閉じない。値は 1 つでも (value,) の形で渡す。

教材の例では、3 件の CSV のうち件数が空の 1 件を拒否し、採用 2 と拒否 1 を入力の 3 と照合する。件数が合っても値が正しい証明にはならない。付けた行番号は、引用の中に改行があると物理行と一致しない。バッチは有限のまとまりを一括で処理し、ストリーミングは到着順に続けて処理する。

完了した結果だけを見せるには、一時の名前に書いて成功後に置き換える。置き換えが原子的になるには、同じファイルシステムの中であるなどの前提が付く。冪等性は、何度繰り返しても最終結果が同じになる性質である。重複を無視する書き方は、内容の食い違いまで隠すことがあるので、同じ ID で内容が違えば、その回の投入を丸ごと取り消す例が挙がる。やり直しは一時的なロックの競合に絞り、文字コードの不正や制約違反を無限に再試行することはしない。

次の第 9 章は、数値の配列の配置と演算へ進む。