論理削除の考察 - 奇特なブログ

えっと、以前に以下に書いたんですけど、
ちょっと今回、この点について真剣に考えてみましょうということで。

kitoku-magic.hatenablog.com

ひとまずは、考えについて整理するということで、
上記記事のリンク先の内容について突っ込んでいってから、
まとめを書きたいと思います。

ただ、「物凄く長い記事」なので、結論だけ知りたい方はこちらを。

http://mike-neck.hatenadiary.com/entry/2015/03/24/231422 から引用。

カーディナリティが云々で検索条件につけても性能上的にもよくない

0と1という二つの値しか取り得ないから、インデックス貼っても大して速くならないといった所でしょうか。
ただ、この記事の最後に書いてある

二つの値でも片方が極端に少ない、片方が極端に多い場合は意味あるらしい

というのもあるみたいですね。

論理削除というのはまだ要件的に未確定な要素が隠されていることを示すフラグ

これ、この後も沢山出てくるんですけど、結構ポイントだと思いますね。

「社員」はマスターと言っていましたが、マスター(僕はリソースと呼んでいる)はシステムの開始から未来永劫追加・変更・削除することができないデータ

ここちょっと興味あったんですけど、「マスター」ってどういうテーブルをイメージします？
個人的には、「商品・社員・アプリ上で表示するメッセージ」といった、
「管理や運営側は変更出来るけど、ユーザーが直接変更出来ないデータ」を「マスター」と呼んでいるんですけどね。
というか、上記の「システムの開始から未来永劫追加・変更・削除することができないデータ」というのがピンとこなかった。
「追加・変更・削除しないデータ」ってあるかなぁと。
まぁ、あんまり本題と関係ないので次へ。

http://blog.kazuhooku.com/2015/03/blog-post_26.html から引用。

「現在の状態を表現するビュー」を実体化したものとして表現されること

あぁ、どうやらココ、「DBA視点」と「アプリ（といってもサーバ側だが）エンジニア視点」で、
「DBに対する考え方が違う」のかなと。
僕は、アプリ（しつこいけどサーバ側）エンジニアなわけですけど、
ビューとかトリガーとかストアドとかファンクションとかって基本否定的でして。
というのも、「ロジックをDB側に書きたくない」というのがあるんですよね。
あと、トリガーに限っていえば、過去に２～３日ハマった事があるというのもあるんですけど（苦笑）
DBは、「データ格納倉庫」として使いたいんですよ。
ただ、後で散々出てくる「制約（ユニーク制約とか）」は使いたいんですけどね。
でも、「手続き型の処理（ストアドやファンクションの事）とか、AをしたらBをする（トリガーの事）」とかは、
プログラム側に一元化したいなぁ。それで「仕事が増えた」としてもね。
・・・ビューは、「ビューのビュー」とか、「複雑化しませんか？」といった懸念や、
以下の記事辺りから、「まぁあんまり」という感じですね。

ビューの功罪

で、多分、「DBの方でやらないでアプリの方でやる」の一番大きな理由としては、「仕様変更に対応しやすくしたいから」だと思いますね。
・・・といってもまぁ、アプリエンジニア間でも、意見が割れそうではありますけどね（苦笑）

なので、上記の話については、特に否定もしないけど肯定もしないといった所ですね。
そもそもの前提が違うので。

http://frsyuki.hatenablog.com/entry/2015/03/26/080627 から引用。

WebサービスとかTreasure Dataのようなサービス事業者だと、テーブルを最初に設計するときにあまり時間をかけられないので、後から見直して最適化していくことが多い

この記事も、基本さっきの話と同じ感じなんですけど、上記については
「だったら、尚更テーブル設計はシンプルにしておきたくないか？」と思うんですよね。
あと、この記事の場合、ポスグレに依存してるのでってのもあるかなと。
いや、MySQLにも対応させたいですからね。

でもPlazmaDBの実装は、ほぼ全部FUNCTIONになっている

これ、リンク先も読んでみたんですけど、「どこにFUNCTIONなんて書いてある？」のか、ちょっと見つけられなかったですね。
単純に、僕が気付かなかった可能性も高そうですけど。

http://blog.webcreativepark.net/2008/05/26-103152.html から引用。

user_mst テーブルに対して、deleted_user_mst テーブルを用意しておいて、ユーザの削除時は、user_mst テーブルから DELETE すると同時に、deleted_user_mst テーブルに INSERT するという方法です

コメントからの引用ですけど、
これ、この後も沢山出てきますね。
というか、僕の知り合いも同じ様な事言ってましたけど。

開発時はこまめにバックアップを取る

開発サーバは何とも言えないですけど、本番でバックアップ取らないってのは全然イメージ無いですね。
正直、「良い度胸してるな」って感じです（苦笑）

テストや検証の工数が取れない場合

この辺が、削除フラグ導入の一因になってる可能性も高そうですね（苦笑）
でもコレ、その時は良くても、後からじわじわと困っていきますしねぇ。

http://qiita.com/Jxck_/items/156d0a231c6968f2a474 から引用。

「最適な設計は DELETE_FLAG ではない」という場合が有って、その場合は、その最適な設計を探すのが正しいだろ

これは、この後も散々出てくるんですけど、「とても大事なポイント」だと思いますね。
「状態」とか、他の意味が隠されているんじゃないの？って。

大抵、色々仕様を見返すと「こういう状態もありますよね?」とこちらが気づくくらいには見落としがあって、
これは放っておいたら、状態が発見されるたびに FLAG 列が追加

で、名前が「～FLAG2」とか、数字の連番になっていくわけですよね（苦笑）

しかし、これはマスタ系のテーブルだと上手く行きません。
例えば社員表から退職者を移すなどと言う場合、その退職者の ID は多くのテーブルから参照されており、
その参照をどうするかが最大の課題となります

あぁ、なるほど。
・・・ゲームの案件（だけ？）で良く見るんですけど、
「マスター系のデータは、DBではなくCSVの中に入れて、使いたい時には、CSVをインポートしてパースする」なんてのがありますね。
で、こっちだと、「社員が退職したらCSVから行を削除」でいけないですかね。
当然、外部キーは貼ってないわけですし。
といっても、結構この辺モヤってますけど。
ただ、こっちの場合、プログラム内で扱うデータの量が増えるので、「メモリに注意」とはなるんですけどね。

・・・あぁ、社員が退職に限っていえば、「退職」って多分ステータスだと思うので（在籍とか出向ってステータスもあるかと）、
単純に、社員テーブルに「在籍状態」ってカラムを用意して、それでさばくとか。
カードゲームのユーザーが所有しているカードのテーブルなら、
「売却（といっても、こっちはマスターじゃないので、削除テーブルに突っ込むで良いのでは？）」とか。

まぁ、上記の方法のいずれでもダメなら、最悪「マスター系だけは削除フラグ使う」となるかもですけど。

パーティションのキーとなる状態があればいい

それこそ、さっきの「在籍状態」とか。
といっても、これはOracleでしか使えない？ので今一つですけど。

問題は、本当にそんな場合があるのか、安易なフラグを付けることによって設計にしわ寄せがいったとしてもフラグを付けるだけの価値があるか、物理設計のツケを論理設計で払うのが本当に良いのか、つける前に検証する価値は有ると思います。推測しないで計測せよという話です

ということで、「計測」してみました。
あと、ここから「物凄く長い」ので注意です。
計測結果による結論だけ知りたいって方は、こちらを。

まず、MySQLのバージョンは「5.5.29」です。
・・・余談ですけど、はてなブログHTML微妙に書きにくい（苦笑）

１．以下のテーブルを用意



// 有効なユーザーのみ格納するテーブル

CREATE TABLE `users_test1` (

  `user_id` bigint unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(50) NOT NULL DEFAULT '',

  PRIMARY KEY (`user_id`)

) engine = InnoDB default charset = utf8;
// 無効（削除とか）なユーザーを格納するテーブル

CREATE TABLE `delete_users` (

  `delete_id` bigint unsigned NOT NULL AUTO_INCREMENT,

  `user_id` bigint unsigned NOT NULL,

  `name` varchar(50) NOT NULL DEFAULT '',

  `delete_time` datetime NOT NULL,

  PRIMARY KEY (`delete_id`),

  KEY (`user_id`)

) engine = InnoDB default charset = utf8;



// delete_flag有りのユーザーのテーブル

CREATE TABLE `users_test2` (

  `id` bigint unsigned NOT NULL AUTO_INCREMENT,

  `user_id` bigint unsigned NOT NULL,

  `name` varchar(50) NOT NULL DEFAULT '',

  `delete_flag` tinyint NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`),

  KEY (`user_id`, `delete_flag`)

) engine = InnoDB default charset = utf8;
// ユーザーIDの現在値（色々あって、こうせざるを得なかったんですよね）

CREATE TABLE `user_id_seq` (

  `current_user_id` bigint unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`current_user_id`)

) engine = InnoDB default charset = utf8;
INSERT INTO user_id_seq VALUES (0);

まぁ、テーブルをどうやって切るかも色々ありそうですけど、
ひとまず、こんな形に。

２．テーブルにデータ突っ込むPHP



// data_insert_users_test1.php

// users_test1テーブルに百万件のレコードを作成する

<?php
try

{

  $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'ユーザー名', 'パスワード', array(PDO::ATTR_EMULATE_PREPARES => false));

}

catch (PDOException $e)

{

   exit('データベース接続失敗：' . $e->getMessage());

}
$pdo->beginTransaction();
for ($i = 1; $i <= 1000000; $i++)

{

  if ($i >= 10000 && $i % 10000 === 1)

  {

    $pdo->beginTransaction();

  }

  $stmt = $pdo->prepare("INSERT INTO users_test1 (name) VALUES (:name)");

  $stmt->bindValue(':name', uniqid('', true), PDO::PARAM_STR);
  $ret = $stmt->execute();

  if ($ret)

  {

    if ($i >= 10000 && $i % 10000 === 0)

    {

      $pdo->commit();

    }

  }

  else

  {

    echo "データ追加失敗\n";

    $pdo->rollback();

    exit;

  }

}



// data_insert_delete_users.php

// delete_usersテーブルに二百万件のレコードを作成する

// 1ユーザーあたり、2件の削除レコードが存在する

<?php
try

{

  $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'ユーザー名', 'パスワード', array(PDO::ATTR_EMULATE_PREPARES => false));

}

catch (PDOException $e)

{

   exit('データベース接続失敗：' . $e->getMessage());

}
$stmt = $pdo->prepare("SELECT * FROM users_test1 ORDER BY user_id");

$ret = $stmt->execute();
if ($ret)

{

  $pdo->beginTransaction();

  while ($result = $stmt->fetch(PDO::FETCH_ASSOC))

  {

    if ($result['user_id'] >= 10000 && $result['user_id'] % 10000 === 1)

    {

      $pdo->beginTransaction();

    }

    $stmt1 = $pdo->prepare("INSERT INTO delete_users (user_id, name, delete_time) VALUES (:user_id, :name, :delete_time)");

    $stmt1->bindValue(':user_id', $result['user_id'], PDO::PARAM_INT);

    $stmt1->bindValue(':name', $result['name'], PDO::PARAM_STR);

    $stmt1->bindValue(':delete_time', date('Y/m/d H:i:s'), PDO::PARAM_STR);
    $ret = $stmt1->execute();

    if ($ret)

    {

      $stmt2 = $pdo->prepare("INSERT INTO delete_users (user_id, name, delete_time) VALUES (:user_id, :name, :delete_time)");

      $stmt2->bindValue(':user_id', $result['user_id'], PDO::PARAM_INT);

      $stmt2->bindValue(':name', $result['name'], PDO::PARAM_STR);

      $stmt2->bindValue(':delete_time', date('Y/m/d H:i:s', strtotime('+1 day')), PDO::PARAM_STR);
      $ret = $stmt2->execute();

      if ($ret)

      {

        if ($result['user_id'] >= 10000 && $result['user_id'] % 10000 === 0)

        {

          $pdo->commit();

        }

      }

      else

      {

        echo "データ追加失敗\n";

        $pdo->rollback();

        exit;

      }

    }

    else

    {

      echo "データ追加失敗\n";

      $pdo->rollback();

      exit;

    }

  }

}

else

{

  echo "SELECT失敗\n";

  exit;

}



// data_insert_users_test2.php

// users_test2テーブルに三百万件のレコードを作成する

// 百万件はdelete_flagが0で、残りはdelete_flagが1

<?php
try

{

  $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'ユーザー名', 'パスワード', array(PDO::ATTR_EMULATE_PREPARES => false));

}

catch (PDOException $e)

{

   exit('データベース接続失敗：' . $e->getMessage());

}
$pdo->beginTransaction();
for ($i = 1; $i <= 1000000; $i++)

{

  if ($i >= 10000 && $i % 10000 === 1)

  {

    $pdo->beginTransaction();

  }

  $name = uniqid('', true);

  $stmt = $pdo->prepare("INSERT INTO users_test2 (user_id, name, delete_flag) VALUES (:user_id, :name, :delete_flag)");

  $stmt->bindValue(':user_id', $i, PDO::PARAM_INT);

  $stmt->bindValue(':name', $name, PDO::PARAM_STR);

  $stmt->bindValue(':delete_flag', 0, PDO::PARAM_INT);
  $ret = $stmt->execute();

  if ($ret)

  {

    $stmt1 = $pdo->prepare("INSERT INTO users_test2 (user_id, name, delete_flag) VALUES (:user_id, :name, :delete_flag)");

    $stmt1->bindValue(':user_id', $i, PDO::PARAM_INT);

    $stmt1->bindValue(':name', $name, PDO::PARAM_STR);

    $stmt1->bindValue(':delete_flag', 1, PDO::PARAM_INT);
    $ret = $stmt1->execute();

    if ($ret)

    {

      $stmt2 = $pdo->prepare("INSERT INTO users_test2 (user_id, name, delete_flag) VALUES (:user_id, :name, :delete_flag)");

      $stmt2->bindValue(':user_id', $i, PDO::PARAM_INT);

      $stmt2->bindValue(':name', $name, PDO::PARAM_STR);

      $stmt2->bindValue(':delete_flag', 1, PDO::PARAM_INT);
      $ret = $stmt2->execute();

      if ($ret)

      {

        if ($i >= 10000 && $i % 10000 === 0)

        {

          $pdo->commit();

        }

      }

      else

      {

        echo "データ追加失敗\n";

        $pdo->rollback();

        exit;

      }

    }

    else

    {

      echo "データ追加失敗\n";

      $pdo->rollback();

      exit;

    }

  }

  else

  {

    echo "データ追加失敗\n";

    $pdo->rollback();

    exit;

  }

}
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE user_id_seq SET current_user_id = 1000000");
$ret = $stmt->execute();
if ($ret)

{

  $pdo->commit();

}

else

{

  echo "データ追加失敗\n";

  $pdo->rollback();

}

ちなみに、今更気付いたんですけど、
削除テーブルに同じユーザーが二人って変だから、
ユーザーじゃない（履歴的なテーブルであれば良いわけですし）方が良かったですね（苦笑）
まぁ、今回の本筋とは関係ないと思うので、今回はこのままいきます。

３．SELECT文を投げて速度計測

// 有効なユーザーデータのみ取得
select user_id, name from users_test1 where user_id = 492650;

select user_id, name from users_test2 where user_id = 492650 and delete_flag = 0;

// 無効なユーザーデータも含めて取得
select user_id, name from users_test1 where user_id = 492650
UNION ALL
select user_id, name from delete_users where user_id = 492650;

select user_id, name from users_test2 where user_id = 492650;

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select user_id, name from users_test1 where user_id = 492650;

user_id	name
492650	5600e36c5e6b81.87318120

1 row in set (0.00 sec)

mysql>
mysql> select user_id, name from users_test2 where user_id = 492650 and delete_flag = 0;

user_id	name
492650	5600f48c5dade8.64925888

1 row in set (0.00 sec)

mysql> select user_id, name from users_test1 where user_id = 492650
-> UNION ALL
-> select user_id, name from delete_users where user_id = 492650;

user_id	name
492650	5600e36c5e6b81.87318120
492650	5600e36c5e6b81.87318120
492650	5600e36c5e6b81.87318120

3 rows in set (0.00 sec)

mysql>
mysql> select user_id, name from users_test2 where user_id = 492650;

user_id	name
492650	5600f48c5dade8.64925888
492650	5600f48c5dade8.64925888
492650	5600f48c5dade8.64925888

3 rows in set (0.00 sec)

mysql> select user_id, name from users_test1 where user_id = 492650;

user_id	name
492650	5600e36c5e6b81.87318120

1 row in set (0.00 sec)

mysql>
mysql> select user_id, name from users_test2 where user_id = 492650 and delete_flag = 0;

user_id	name
492650	5600f48c5dade8.64925888

1 row in set (0.00 sec)

mysql> select user_id, name from users_test1 where user_id = 492650
-> UNION ALL
-> select user_id, name from delete_users where user_id = 492650;

user_id	name
492650	5600e36c5e6b81.87318120
492650	5600e36c5e6b81.87318120
492650	5600e36c5e6b81.87318120

3 rows in set (0.00 sec)

mysql>
mysql> select user_id, name from users_test2 where user_id = 492650;

user_id	name
492650	5600f48c5dade8.64925888
492650	5600f48c5dade8.64925888
492650	5600f48c5dade8.64925888

3 rows in set (0.00 sec)

mysql> show profiles;

Query_ID	Duration	Query
1	0.00042075	select user_id, name from users_test1 where user_id = 492650
2	0.00025525	select user_id, name from users_test2 where user_id = 492650 and delete_flag = 0
3	0.00049375	select user_id, name from users_test1 where user_id = 492650

UNION ALL
select user_id, name from delete_users where user_id = 492650 |

4	0.00021150	select user_id, name from users_test2 where user_id = 492650
5	0.00029775	select user_id, name from users_test1 where user_id = 492650
6	0.00022225	select user_id, name from users_test2 where user_id = 492650 and delete_flag = 0
7	0.00048450	select user_id, name from users_test1 where user_id = 492650

UNION ALL
select user_id, name from delete_users where user_id = 492650 |

0.00020925

select user_id, name from users_test2 where user_id = 492650

8 rows in set (0.00 sec)

mysql>

UNION ALLが若干遅い（といっても、ミリ秒レベルの話ですけど）けど、
そんなに変わらんって所でしょうか。
ちなみに、同じSQL二回投げたのは、キャッシュを利かせて同じ条件にする為です。
キャッシュ利いていない同じ条件の場合は・・・また今度にしましょう。

３．ユーザーの削除処理を実行し速度計測



// data_delete_users_test1.php

// users_test1テーブルのレコードを削除し、delete_usersに追加する

<?php
// コマンドライン引数からユーザーIDを渡す前提

$user_id = $argv[1];

$time_start = 0;

$time_end = 0;
try

{

  $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'ユーザー名', 'パスワード', array(PDO::ATTR_EMULATE_PREPARES => false));

}

catch (PDOException $e)

{

   exit('データベース接続失敗：' . $e->getMessage());

}
$time_start = microtime(true);
$stmt = $pdo->prepare("SELECT * FROM users_test1 where user_id = :user_id");

$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);

$ret = $stmt->execute();
if ($ret)

{

  $pdo->beginTransaction();

  while ($result = $stmt->fetch(PDO::FETCH_ASSOC))

  {

    $stmt1 = $pdo->prepare("DELETE FROM users_test1 where user_id = :user_id");

    $stmt1->bindValue(':user_id', $result['user_id'], PDO::PARAM_INT);
    $ret = $stmt1->execute();
    if ($ret)

    {

      $stmt1 = $pdo->prepare("INSERT INTO delete_users (user_id, name, delete_time) VALUES (:user_id, :name, :delete_time)");

      $stmt1->bindValue(':user_id', $result['user_id'], PDO::PARAM_INT);

      $stmt1->bindValue(':name', $result['name'], PDO::PARAM_STR);

      $stmt1->bindValue(':delete_time', date('Y/m/d H:i:s'), PDO::PARAM_STR);
      $ret = $stmt1->execute();

      if ($ret)

      {

        $pdo->commit();

      }

      else

      {

        echo "データ追加失敗\n";

        $pdo->rollback();

        exit;

      }

    }

    else

    {

      echo "データ削除失敗\n";

      $pdo->rollback();

      exit;

    }

  }

  $time_end = microtime(true);

}
echo $time_end - $time_start . "\n";



// data_delete_users_test2.php

// users_test2テーブルdelete_flagが0の行を1に更新する

<?php
// コマンドライン引数からユーザーIDを渡す前提

$user_id = $argv[1];

$time_start = 0;

$time_end = 0;
try

{

  $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'ユーザー名', 'パスワード', array(PDO::ATTR_EMULATE_PREPARES => false));

}

catch (PDOException $e)

{

   exit('データベース接続失敗：' . $e->getMessage());

}
$time_start = microtime(true);

$pdo->beginTransaction();

$stmt = $pdo->prepare("UPDATE users_test2 SET delete_flag = 1 where user_id = :user_id and delete_flag = 0");

$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$ret = $stmt->execute();
if ($ret)

{

  $pdo->commit();

}

else

{

  echo "データ更新失敗\n";

  $pdo->rollback();

  exit;

}

$time_end = microtime(true);
echo $time_end - $time_start . "\n";

で、上記PHPを、以下のコマンドで実行しました。

for i in `seq 1 1 1000`; do php data_delete_users_test1.php $i >> result_data_delete_users_test1.xls; done

for i in `seq 1 1 1000`; do php data_delete_users_test2.php $i >> result_data_delete_users_test2.xls; done

で、エクセルファイルに書き込まれた実行時間の平均を取った結果、
users_test1が「0.1151350505秒」で、
users_test2が「0.1165583317秒」となりましたって、
削除フラグ無いusers_test1の方がむしろ速いんですけど。

とりあえず、計測の結論としては、
「ほとんど変わらん」のではないかと。
勿論、前提変われば結果も変わるとは思うので、
テーブル設計やプログラムが変って所があれば、ご指摘頂きたい所ではありますけど。

では、話を戻して続きを。

http://qiita.com/Jxck_/items/156d0a231c6968f2a474 から引用。

「論理削除した行が復活する事」は、オペレーションミスの訂正時にもありますね。
オペレーションミスの訂正は画面を閉じた後でも発生するので、アンドゥ機能では対応できません。

ここからは、コメントからの引用ですけど、
オペミスがあるから、delete_flagを付けると？
「管理画面で確認ダイアログ出す」とか、
「万が一オペミスした場合のレコード復旧機能を、別途作る」とか、
色々工夫は出来ると思うんですけどね。
ちなみに、どうやってレコードを復旧させるかについては、
「削除テーブルに入れてしまったレコードを、有効テーブルに戻す」プログラムを書けば良いと思います。
まぁ、削除テーブルのdelete_idが歯抜けにはなっちゃいますけど、
このID自体、特に業務用のIDでも無い（サロゲートキー）ですし、仕方ないかと。

ちなみに、私は、作成日・更新日も何も考えずに項目に入っているのも嫌です。
JSON形式として更新前イメージをすべてログを残しておくようにしています

この作成日・更新日も多いですね。
まぁ、JSON形式かどうかはともかく、確かにログに残す方が良いと思いますね。

しかし、実際は要件定義には手をつけられないのもよくあること。私はその場合、特段問題が無い限り削除フラグやバックアップテーブルなど「意図しない不要データ」を許容してしまいます。思考停止というより（開発では）QCDに見合わないですからね。運用段階で、運用改善、パフォーマンス改善として予算取りするという政治的解決が待っていますorz

まぁ、ぶっちゃけこういった「時間が無いから」ってケースも多そうですね（苦笑）
でも、後で面倒になりますから、やっぱり時間は割くべきかと。
「自分で自分の首を絞める形になる」前に。

昔アプリ不具合でselect文のwhere句部分が無くなる

これ、SQLインジェクションじゃないですかね。
まぁ、本題と全然関係ないですけど。

http://qiita.com/yuba/items/70165875cfe02b03513d から引用。

アドレスとstatusの組をキーに一意性制約をかけます。削除済みユーザはNULLがキーに含まれるので一意性の判定対象にならないという次第。表技感あふれてますね

そうそう、さっき上記のプログラム書いてた時に、ユニークに出来ないんだがと思っていたんですけど、
で、こういう方法があるよと。
といっても、NULLは「３値論理」がねぇ。
以下の通り、結構ややこしいですし、正直覚えてられん。

codezine.jp

http://dekasasaki.tumblr.com/post/69487259373/%E8%AB%96%E7%90%86%E5%89%8A%E9%99%A4%E3%81%8C%E5%A5%AA%E3%81%86%E3%82%82%E3%81%AE から引用。

「あるエンティティ定義に、論理削除有無を設定する属性が定義されている時点で、開発者は『ああ、この表のデータって削除していいんだ』という暗黙の了解に思考を縛られる」

なるほど、こういった心理的副作用は怖いですね。

http://www.infoq.com/jp/news/2009/09/Do-Not-Delete-Data から引用。

注文は削除されません。– キャンセルされるのです。キャンセルが遅かったら、料金が発生するでしょう。
従業員は削除されません。– 解雇されるのです(あるいは退職かもしれません)。給与体系は頻繁に手を入れなければなりません。
仕事も削除されません。– 完了されるのです(または、取り消されるかもしれません)。
どんな場合でも、着目すべきはユーザが行いたい作業であり、エンティティその他に対する技術的な処理ではありません。ユーザの作業に着目すれば、ほとんどすべての場合、1つ以上のエンティティを考慮しなければならないはずです。

どう見ても削除じゃないですね、コレ。
だから、「状態があるものを、削除フラグで扱うな」となる。

http://d.hatena.ne.jp/jflute/20121116/1353001525 から引用。

ユニーク制約がないと何が困るかって、
一言でずばり「なんでも入っちゃう」ってところ。
要は「FOO=A, BAR=B, DEL_FLG=false」も、
複数入ってしまう可能性があるってこと

ですねぇ、制約で防げる所は防ぎたいです。

ふとしたときに重複データが入ってしまって、
「一件検索が二件になってエラーになっちゃった」とか

はいはい（苦笑）

削除フラグだけじゃなく、削除日時みたいなカラムを用意して、
有効なデータには固定の "9999/12/31" とか入れて、
削除されたデータには削除した日時をそのまんま入れる。
「FOOとBARと削除日時」でユニーク制約

でも、これ「同じ日（あるいは、同じ時間）」だったらアウトになりますよね。
まぁ、同じ時間（秒まで同じ）はそうそうないのは確かですけど。

削除フラグにはDBコメントが絶対に必須

もし入れるなら、確かに必須レベルかと。

そもそも有効Table作っときゃいいだけなんじゃないですかね？
こんなTableが有ったとして
create table Master
(
id serial primary key,
column1 text,
column2 text,
column3 text
);
こんな感じで生存しているRecordのIDだけを保持する有効Tableを用意します。
create table ActiveMaster
(
　 id integer references Master( id ) on delete cascade
);
Masterの検索削除を行う際は、この有効Table使って操作します。例えば削除する場合は、ActiveMasterのRecordだけをdeleteします。

ぱっと見、削除Flagと大差ありませんが、ActiveMasterに対しinsertとdeleteを行うことで追加削除ができ、やや直感的になります。また、検索時に分散Indexなんかを使わなくとも削除した項目を検索対象にしないので高速です。参照制約も活用しやすくなりますよ

これはコメントからですけど、ほほう、またさっきの僕のとは違うアプローチですね。
ただコレ、使い方がちょっと分かりにくい・・・

Masterテーブルには「削除されたレコードも含めた全件」が入っていて、
そのうち、有効なレコード「だけ」が、ActiveMasterに入ってるって事かと思うんですけど。
ちょっと、上記の僕のusersテーブルに合わせると、以下の様になりますかね。



CREATE TABLE `users_test3` (

  `id` bigint unsigned NOT NULL AUTO_INCREMENT,

  `user_id` bigint unsigned NOT NULL,

  `name` varchar(50) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY (`user_id`)

) engine = InnoDB default charset = utf8;



CREATE TABLE `active_users_test3`

(

  `id` bigint unsigned NOT NULL,

  `user_id` bigint unsigned NOT NULL,

  PRIMARY KEY (id),

  UNIQUE KEY (user_id),

  foreign key(id) references users_test3(id) on delete cascade,

  foreign key(user_id) references users_test3(user_id) on delete cascade

) engine = InnoDB default charset = utf8;



CREATE TABLE `user_id_seq` (

  `current_user_id` bigint unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`current_user_id`)

) engine = InnoDB default charset = utf8;
INSERT INTO user_id_seq VALUES (0);

う～ん、user_id_seqが余分ですねぇ。
あと、出来るだけサロゲートキーも使いたくないしねぇ。
で、SELECTは、

select * from users_test3 ut where ut.user_id = (select aut.user_id from active_users_test3 aut where aut.user_id = ?);

という感じ（あくまで一例だが）でしょうか。
あと、INSERTは、「両方のテーブルに追加」が必要で、
UPDATEは、「users_test3の方だけ」で、
DELETEは、「active_users_test3の方だけ」でという感じでしょうか。
まぁ、悪くはないですけど、やはり前述のテーブルの方が良い様に思えますね。
いや、もしかしたら、使い方が間違っているのかもしれないですけど。

話を戻して次。

http://d.hatena.ne.jp/ryousanngata/20150329/1427647377 から引用。

１．名前から目的が明確にならない
２．バグの要因の一つ (認識の祖語を生みやすいし、SQLを注意して書かないと実装バグを起こしやすい。)
３．テストするにも論理削除状態のデータを1個置いてテストするようなことをしないといけない

１番目は、だからコメントをって話だと思いますし、
３番目は、なるほどこういう手間も確かにありますね。

ただし論理削除に慣れてきてしまうと、本当に削除していいのか判断ができなくなり、とりあえず論理削除を設計に盛り込むようになる。
他のシステムもやってるしーみたいなノリで入れ始める

確かに、こういったケースで導入してる場合もありそう（苦笑）

SELECT文の条件で意識しないといけない(この操作のときはチェックすべきなのか否か)

あぁ、そうそう、これ以前の仕事中にありましたね。
で、こういう時にメンドイ（というか迷う）と。

ものぐさには2つのテーブルを行ったり来たりする気力はないかもしれない。(自分はそう思います)

う～む、そうなのか？
いや、そこまで面倒じゃないと思うんですけどね・・・

この発想に至るのは「ログは1か所で管理しろ」という点に尽きる。つまり、DBはログを管理する場所ではない。
テキストベースのログとDBの論理削除されたレコードのタイムスタンプ比較して多分このレコードはここの操作にあたるな？とアタリをつけて調べるのは辛い。
ログは大量に出るので時系列で追おうとすると、途端に辛くなりますが、ユーザの識別子(ID)とかをログに出すようにすればgrepすればある程度は追いやすくなるし、最近はJSONにしてDBにぶっこんで調べるというのも実用レベルで聞くし、ログはログでまとめたほうが良いのではと思ってる

あぁ、確かにログは１か所の方が良いと思いますね。
で、ユーザの識別子をログに出してってのは、やってる現場ありましたね。
で、確かに調べやすかった。

http://blog.crossidea.co.jp/2013/501/ から引用。

間違えて登録したデータを完全に削除できないのは困る。
あるデータに関しては通常の運用で完全に削除したい。

あぁ、でもコレは論理削除使ってても、定期的にバッチ回して消すとかで対応可能ですけどね。
ただ、特定のデータだと、絞り込めないですね（delete_timeなら絞り込めるけど）。

情報資産というのはデータの所有者が、それを保有することに価値を見出している情報という意味です。一定の役目を終えたからと言って完全に削除してしまうと、新たなデータの参考にすることすらできなくなります。その場合は論理削除が良いのではないかと思っています

いや、それこそ履歴（削除テーブルの事）に持たせれば良いじゃないですか。

http://makopi23.blog.fc2.com/blog-entry-176.html から引用。

Webアプリとかでは、パフォーマンスの問題を考慮して物理削除を行わない場合がある。
秒間１億のトランザクションを捌くのに、物理削除は難しい。

これ、よく聞く論調の話ではあるんですけど、
上記の計測結果を見るとなぁって感じでもあるんですよね。
いや、勿論条件が全然違うのは分かりますけど、これは誰が言い始めたのかが気にならないでもないですね。

「製品名」が変更になった場合、そのレコードを新規追加し、更新次数を0とする。他のレコードは更新次数をすべて +1 してズラす

う～ん、話としては分かりますけど、ちょっと面倒じゃないですかねコレ。

レコードには「開始日付」と「終了日付」を持たせる。
あるレコードの「終了日付」と、次に新しいレコードの「開始日付」は、一致させること。
日付に連続性を持たせ、Select文のWhere句で "where 開始日付 <= Target and Target < 終了日付"のように指定する。
下限を ≦ にして上限を < にする点がポイント。これによりレコードの連続性を保つ。betweenは使えない。
あるレコードの開始日付と終了日付を同じ値にすることで、そのレコードを論理削除したことと同一になる。

要するに、履歴を管理したいって事だと思いますけど、
それは、削除テーブルのdelete_timeで出来ると思うんですけどね。

http://blogs.wankuma.com/kacchan6/archive/2007/08/21/91190.aspx から引用。

現在の業務アプリはJSOXの絡みもあってDELETEは一切ご法度。
UPDATEもすべてトリガでログするかなどの消える処理はご法度です

JSOX、ほほうまた新たな観点が。
といっても、それも削除テーブルを見れば良いんじゃないかと。

もう使わないマスタデータだけれども過去のデータでマスタ参照しているものもあるので、そういったものは論理削除（削除フラグ=On）

これがちょっとな・・・まだモヤってますね。

http://blog.mogmet.com/ronsakucasual-1/ から引用。

やってみると結構難しい
1個のテーブルだけではなく、お互いのテーブルの関係などを考えると結構大変

履歴テーブルで管理するのが難しいって事ですけど、
確かに最初は、論理削除と比べて手間がかかるとは思いますね。
なので、あまりにも時間がない時（それはそれで、別の問題がある様にも思うので、そっちの改善をした方が良いですが、それでもダメなら）には、
論理削除の方が良いとは思いますね。

絶版商品に削除フラグを付けて、一覧や検索で表示されないようにしたい
ほんとうに欲しかったのもはstatus = :絶版

またしても、フラグでは無かったパターンですね。

退会した会員はログイン出来ないように論理削除したい
そのまま残してstatus = :ログイン不可にする

これも同じですね。
で、どういうマスターかにもよりますけど、マスターもこれで良いのでは。

保管期間の過ぎたデータに削除フラグをたてたい
古いやつを消したいというニーズは新しいものを表示したいというニーズではないのか

これは、以前にポイントの履歴を格納するテーブルがありまして、
「ポイントは半年間のみ有効だが、それ以前は無効になる」なんて時に、
「無効になったポイントは、別のテーブルに移す」なんてのが有効じゃないかというのがありましたね。
勿論、SQLで期間指定する方法もあるけど、
定期的にバッチを実行して、移した方が良いんじゃないかと。

http://ledsun.hatenablog.com/entry/2015/03/27/015203/ から引用。

アプリケーションの作成工数や運用工数が、データ設計工数より低い

これは、「データ設計工数がたっぷり取られていて、散々検討した結果、不要な削除フラグは排除されているので、
そういった中で導入した削除フラグについては仕方ない」のでOKって事ですかねぇ。
まぁでも確かに、要件として含まれていた場合にはとかも考えると、これは良いのではないかと。
ただ、個人的経験ですと、
「むしろ、アプリの作成工数や運用工数の方が、データ設計工数より"圧倒的に"多いよ」って印象で、
でも、そんな現場で削除フラグで導入されていて、これまでに色々書かれていた問題が発生したりしているので、
上記の意見は満更でもないですね。

結論

長々と書いてきましたが、以下の点がポイントなんじゃないかと。

１．要件を整理すること。それは本当に「削除」なのか、「状態」ではないのかとか。

これまで散々書かれていた事で、これがやっぱり一番重要だと思いますね。
で、その上で削除フラグ（フラグじゃなくて時間かもしれないが）導入はアリかと。
あと、ココは工数が足りなくて～もあるかと思うので、
エンジニア以外のメンバーの協力も必要かと。

２．パフォーマンスについては「計測」すべし。

上記の計測の通りですね。
あと、パフォーマンスって別にDB（というかSQL）だけじゃなくて、
Webサーバ・プログラム（FWやORマッパーも含む）・クライアント側のプログラム・スケールアウト・スケールアップ・DBのパラメータチューニングとか、
他にも色々あると思うので、「全体」として見てみたい所ではありますね。

３．オペミスなどからの「データ復旧」は、オペミス発生しづらい仕組み（ダイアログ出すとか）作るなり、復旧機能を作るなり、バックアップから戻せ

手作業はやっぱり大変だと思うので。
バックアップは最終手段にしたいですけどね。

「マスターの一部と大規模Webサービスは削除フラグ導入せざるを得ない？」って所が特にモヤってますけど、
ひとまず、こんな所でしょうか。
個人的には、削除テーブル案をもう少し検証してみたいですけどね。

ご意見・ご感想などありましたら、コメント等でいただければと思います。

追記

記事の途中の速度計測をしている箇所について、
users_test1テーブルを一億件、
delete_usersテーブルを二億件、
users_test2テーブルを三億件にして再計測してみたんですけど、
users_test1が「0.1128752351秒」で、
users_test2が「0.1137793614秒」ということで、
計測前と殆ど変わらなかったという（苦笑）
だから、これはもう速度が遅いという理由で、
論理削除を使う理由はちっとも無い様に思いますね。