従属するテーブルの連動削除

昨日触れた header - detail 構造はデータベースにおいても、当然使われる構造である。 帳票の基本情報としての header と、それに対する明細情報としての detail など、事務処理においてはお約束のような構造である。

今回は、header のレコードを削除した場合に、従属する detail のレコードを連動して削除するクエリを考えてみる。 データベース上でテーブルの整合性を取るための処理である。

まず、主体となるテーブル header は主キー key を持っているものとする。 従属するテーブル detail は主キー key, no を持っているものとする。 header と detail の key は紐づいたれコードが登録されているものとする。

header レコード削除後に、detail 側で紐づいていないレコードを削除する DELETE 文の書き方はいくつか存在するため、 何パターンか書いてみることにしよう。

1. サブクエリを使って主キーが存在しない内容を削除する

DELETE FROM detail
 WHERE key NOT IN (
    SELECT key FROM header
 );

2. 外部結合で紐づかない内容を削除する

DELETE FROM detail
 USING detail d
  LEFT OUTER JOIN header h ON d.key = h.key
 WHERE detail.key = d.key AND h.key IS NULL;

3. NOT EXISTS を使う

DELETE FROM detail
 WHERE NOT EXISTS (
    SELECT 1
      FROM header h
     WHERE detail.key = h.key
 )

実際にパフォーマンスなどのテストする場合は、トランザクションをかけてロールバックしながら何度も分析する形になると思うが、 クエリ上で行うならば、以下のように EXPLAIN ANALYZE を使えば、その結果でパフォーマンスを確認できる。

BEGIN;

EXPLAIN ANALYZE
DELETE FROM detail
 WHERE key NOT IN (
    SELECT key FROM header
 );

ROLLBACK;

数千件程度では結果としては 1 の方法がシンプルで概して速かった。2 と 3 は外で結合させている手順の分だけ時間がかかっており、 メモリに展開できる程度の小さなデータであれば 1, 3, 2 の順番で速かった。

しかし、手元にあった50万件ほどのレコードで試してみると、案の定というか、3, 2 の順で速く、1 に至っては数分待ってもレスポンスが返らない。 という結果になった。サブクエリの内容を全展開した上で、否定的な判定をする必要があることから、対象となるデータ量によっては膨大なメモリが 必要となるためだろう。そのため、想定されるレコード数によっては全く使い物にならない方法と言える。

クエリの書き方で、この処理は重そう、軽そう。というのが感覚的に解る十分な経験や実績があり、技量に自信があれば、直感で組み上げても それほど問題が起こることはないだろうが、少しでも怪しいのであれば、実際の環境を想定した条件で逐一確認すべきである。 そのためにも、データベースを扱う開発者は、テストに使える様々な規模のデータを準備しておくべきでもある。