MySQLで論理削除と一意性制約の両立

追記@2015-03-25

素直にMySQLのバックアップを取っていれば(さらに消すときにログを残しておけば)、特に論理削除せず普通に削除すればいい気もしてきました。いろいろと見通しがよくなりますし。とは言えTwitterみたいにアカウント削除しても30日は復活できる、みたいなことをしたい場合必要だと思います。要件次第ですね。


論理削除にはいろいろなメリットがあります。行削除のように関連する他テーブルへ削除が波及しないこと、エントリ復活ができること、障害時にデータ変更の経緯を追いやすくなることなどなど。

ところが論理削除の方針でDBを組んでいて困ったことはありませんか?
「 メールアドレスは一意性(UNIQUE)制約をかけたいのに、それだと削除済みのユーザと同じメールアドレスが使えないことになる 」

論理削除と一意性制約、両立はできないのか?
できないと思っている方、多いと思います。実はちゃんとできます。DB製品によって実現方法がちょっと違ってくるだけで。

論理削除と一意性制約を両立させる方法・DB製品別


↑を読んでなるほどと思ったのですが、MySQLでの実現方法が載っていなかったので、書いてみました。MySQLではCHECK制約が無視されますのでどうしようかなと思ったのですが、要はstatusの値も含めて一意かどうか判別すれば良さそうです。

今回行ったことは、

– 二つのカラム(usernameとemailを想定します)に対する一意性制約と論理削除の両立@MySQL

です。

ともかくやってみます。MySQLのコンソールに入って作業することを想定しています。「#」はコメントです。

# 以下の様なテーブルを作成します
> CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `username` varchar(19) NOT NULL,
  `state` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_state` (`email`,`state`),
  UNIQUE KEY `username_state` (`username`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 3つほど値をいれてみます
 > insert into users (email, username) values ("foo@example.com", "foo"),("bar@example.com", "bar"),("fizz@example.com", "fizz");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 状態を確認してみます
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# emailとusernameの一意性が保たれているか確かめてみます
# emailが同じだったりusernameが同じだったりするものを入れてみます

# 同じものをいれてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailが異なり、usernameが同じものを入れてみる→一意性制約によりエラー 
> insert into users (email, username) values ("foo1@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo-0' for key 'username_state'

# emailは同じで、usernameが異なるものを入れてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo1");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailは同じで、usernameを大文字にしてみる→一意性制約によりエラー
# (これはいわゆるケースセンシティブでない例です。Collation: utf8_general_ciと
# 設定しておく必要があると思います)
> insert into users (email, username) values ("foo@example.com", "FOO");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# ↑上記のように、一意性制約がきちんと働いていることが分かりました。

# 次に、論理削除をしてみます。id:3を論理削除します。
> update users set state = -1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ↓のようにstateが-1となっています
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# さて、この状態において、emailのfizz@example.comや、usernameのfizzは、
# 論理削除されているので、同じものが追加されても受け付けるはずです。

# まったく同じものを挿入してみました。無事、挿入できました。
> insert into users (email, username) values ("fizz@example.com", "fizz");
Query OK, 1 row affected (0.00 sec)

# 状態を確認すると↓のようになっています。
# 一件、fizz@example.comとfizzが二つあっておかしな感じですが、
# あくまで一意性制約はemail+state, username+stateに掛かっているので、
# 正常です。
# 蛇足ですが、新しいレコードのidが8となっているのは、
# MySQLのトランザクションとロールバックの関係のようです。
# 参考: http://stackoverflow.com/questions/2787910/why-does-mysql-autoincrement-increase-on-failed-inserts
 > select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
4 rows in set (0.00 sec)

# 検索するときは、stateの条件も付与する必要があります

# OKな例↓
select * from users where username like "fizz" and state = 0;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
1 row in set (0.02 sec)

# だめな例↓(2つのレコードがでてくる)
> select * from users where username like "fizz";
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
2 rows in set (0.00 sec)

というわけで無事、MySQLでもできました。

mybenchでinsert

mybench: http://jeremy.zawodny.com/mysql/mybench/ というMySQLのPerl製ベンチマークスクリプトがあります。High Performance MySQLの作者によるスクリプトだそうです。Perlなので変数を使って自分で好きなクエリを作ってベンチマーク出来ていい感じです。

インストール方法や使い方は検索してみてください。

私が行ったのは、
– デフォルトの selectではなく insert で実行
– Redisを使って変数を共有
です。このスクリプトはPerlプロセスをフォークします。変数の受け渡しが困難になるので、Redisを用いました。

スクリプトは後述します。以下の様な結果になりました。Redisを立ち上げておいて下さい。

$ ./insert -n 100 -r 10000
forking: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sleeping for 11 seconds while kids get ready
waiting: ----------------------------------------------------------------------------------------------------
test: 1000000 6e-05 0.24007 0.001926992766 1926.992766 51894.330775085
  clients : 100
  queries : 1000000
  fastest : 6e-05
  slowest : 0.24007
  average : 0.001926992766
  serial  : 1926.992766
  q/sec   : 51894.330775085

100クライアントで、1クライアントにつき10000件のinsertです。つまり合計100万insertです。5.2万[q/sec]となりました。htopというコマンドで観察してますと、全コアがフルロードされていました。serialという項目が分かりにくいですが、おそらくクライアントを直列に実行した場合に掛かる時間だと思います。つまり、実際に実行して終わるまでに掛かった時間は、serial / clientsで、1926.992766 / 100 = 19.27 [sec] となります。

はまりどころとしては、
– -n 250などとすると、「failed: Too many connections」となる
– このスクリプトの実行前にtableのレコードを全削除しないとエラーになる
などです。

私のMySQLの環境については、書ききれないところが多いですが、

– Intel i7-3820
– 16GB DRAM (8GB * 4)
– Intel 520 Series 480GB
– Debian 3.2.57-3 x86_64 GNU/Linux
– mysql Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2
– テーブル定義: create table t001 (edge varchar(255) not null primary key, ts int not null) engine=innodb row_format=dynamic;

です。my.confの主要なところは、

innodb_buffer_pool_size = 24G
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_per_table = 1

です。実行する前に、tableの中身を消して下さい(delete from t001)。

insert
#!/usr/bin/perl -w

use strict;
use MyBench;
use Getopt::Std;
use Time::HiRes qw(gettimeofday tv_interval);
use DBI;
use Redis;

my %opt;
Getopt::Std::getopt('n:r:h:', \%opt);

my $num_kids  = $opt{n} || 10;
my $num_runs  = $opt{r} || 100;
my $db        = "bench01";
my $user      = "root";
my $pass      = "root";
my $port      = 3306;
my $host      = $opt{h} || "localhost";
my $dsn       = "DBI:mysql:$db:$host;port=$port";

our $min = 1;
our $max = 3 * $num_kids * $num_runs;
our $middle = ($min + $max) / 2;
our @uid = List::Util::shuffle ($min .. $middle);
our @fid = List::Util::shuffle (($middle+1) .. $max);

our $redis = Redis->new( server => 'localhost:6379');
$redis->set('pcounter', $num_kids);

my $callback = sub
{
    my $id  = shift;
    my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 });
    #my $sth = $dbh->prepare("SELECT edge FROM t001 WHERE edge = ?");
    my $sth = $dbh->prepare("insert into t001 values (?, unix_timestamp(now()))");
    my $cnt = 0;
    my @times = ();

    ## wait for the parent to HUP me
    local $SIG{HUP} = sub { };
    sleep 600;

    ## get process number
    my $pcounter = $redis->decr('pcounter');
    #print "\npcounter: " . $pcounter . "\n";
 
    while ($cnt < $num_runs)
    {
        #my $v = int(rand(100_000));
        #my $v = '6-11';
        my $edge = $uid[$pcounter] . "-" . $fid[$cnt];
        #print "edge: " . $edge . "\n";
        ## time the query
        my $t0 = [gettimeofday];
        $sth->execute($edge);
        my $t1 = tv_interval($t0, [gettimeofday]);
        push @times, $t1;
        $sth->finish();
        $cnt++;
    }

    ## cleanup
    $dbh->disconnect();
    my @r = ($id, scalar(@times), min(@times), max(@times), avg(@times), tot(@times));
    return @r;
};

my @results = MyBench::fork_and_work($num_kids, $callback);
MyBench::compute_results('test', @results);
$redis->quit;

exit;

__END__

おまけ
mysql> show table status\G
*************************** 1. row ***************************
           Name: t001
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1007866
 Avg_row_length: 66
    Data_length: 66732032
Max_data_length: 0
   Index_length: 0
      Data_free: 46137344
 Auto_increment: NULL
    Create_time: 2014-07-16 11:06:15
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)

100万レコードで、66732032[B] = 63.6[MB]ほど。ということは、
– 1000万レコードで636MB
– 1億レコードで6.36GB
– 10億レコードで63.6GB
となる。