9月から異動になって別のサービスの担当になった。先月はさらに夏季休暇もとっていて、ちょっと旅行に行ってた(日記でも書こうかな…)。なので、最近はだいぶバタバタしてた。
で、まあその異動先のサービスでDBを見てみたらデータ容量があっぷあっぷだった。どうやら不要データを削除していないらしい。んで、早速大量のデータを削除することになったのでそのTipsといか小ネタ。



実際に作業したデータは何十倍も巨大なんだけど、手元の仮想マシンに用意した適当なデータで実験結果を示してみる。

1.  削除件数が少ない時

全件件数が下記の通り。

1
2
3
4
5
6
7
select count(*) from t;
+----------+
| count(*) |
+----------+
| 10708007 |
+----------+
1 row in set (2.74 sec)

で、削除条件が下記。大体3%くらいのレコードを削除するとき。

1
2
3
4
5
6
7
select count(*) from t where c_datetime < '2013-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 348932 |
+----------+
1 row in set (0.28 sec)

1.1. delete + alter table/optimize table(削除キーにインデックスがない、また、は効かない場合)

deleteしてからalterまたはoptimizeして容量を確保する方式。言うまでもないけど、InnoDBはdeleteしてもデータファイルそれ自体の容量は減らない。なので容量を減らしたい場合はdeleteしたあとにalter table t engine=InnoDBしてやるかoptimize table tしてやる必要がある。

1
2
3
4
5
6
delete from t where c_datetime < '2013-01-01 00:00:00';
Query OK, 348932 rows affected (1 min 14.86 sec)

alter table t engine=InnoDB;
Query OK, 10359075 rows affected (2 min 59.46 sec)
Records: 10359075 Duplicates: 0 Warnings: 0

結果:約4分14秒

1.2. delete + alter table/optimize table(削除キーにインデックスがある、かつ、効く場合)

delete文をselect文で置き換えたときにインデックスが効く場合についても実験。

1
2
3
4
5
6
delete from t where c_datetime < '2013-01-01 00:00:00';
Query OK, 348932 rows affected (1 min 2.46 sec)

alter table t engine=InnoDB;
Query OK, 10359075 rows affected (3 min 31.71 sec)
Records: 10359075 Duplicates: 0 Warnings: 0

結果:約4分34秒

1.3. select insert -> リネーム -> drop

削除しないレコードで新しいテーブルを作ってリネームする方式。で、これの結果が↓

1
2
3
4
5
6
7
8
9
10
11
12
create table t_new like t;
Query OK, 0 rows affected (0.00 sec)

insert into t_new select * from t where c_datetime >= '2013-01-01 00:00:00';
Query OK, 10359075 rows affected (3 min 30.38 sec)
Records: 10359075 Duplicates: 0 Warnings: 0

rename table t to t_org, t_new to t;
Query OK, 0 rows affected (0.01 sec)

drop table t_org;
Query OK, 0 rows affected (0.10 sec)

結果:約3分30秒

deleteは遅い遅いと言われるけど実際に遅い結果になった。削除しないデータで新しいテーブルを作った方がちょい早かった。

2. 削除件数が多い時

同じテーブルを使って、今度は50%近いレコードを削除する場合について実験。おれが実際に直面したパターン。

1
2
3
4
5
6
7
select count(*) from t;
+----------+
| count(*) |
+----------+
| 10359075 |
+----------+
1 row in set (3.00 sec)

↓が削除する件数。

1
2
3
4
5
6
7
select count(*) from t where c_datetime < '2013-05-01 00:00:00';
+----------+
| count(*) |
+----------+
| 4988773 |
+----------+
1 row in set (7.21 sec)

2.1. delete + alter table/optimize table(削除キーにインデックスがない、また、は効かない場合)

1
2
3
4
5
6
delete from t where c_datetime < '2013-05-01 00:00:00';
Query OK, 4988773 rows affected (14 min 42.09 sec)

alter table t engine=InnoDB;
Query OK, 5370302 rows affected (3 min 55.81 sec)
Records: 5370302 Duplicates: 0 Warnings: 0

結果: 18分37秒

2.2. delete + alter table/optimize table(削除キーにインデックスがある、かつ、効く場合)

select で置き換えてexplainとったときにインデックスを使ってくれなかったので省略。

2.3. select insert -> リネーム -> drop

1
2
3
4
5
6
7
8
9
10
11
create table t_new like t;
Query OK, 0 rows affected (0.01 sec)

insert into t_new select * from t where c_datetime >= '2013-05-01 00:00:00';
Query OK, 5370302 rows affected (1 min 39.15 sec) Records: 5370302 Duplicates: 0 Warnings: 0

rename table t to t_org, t_new to t;
Query OK, 0 rows affected (0.01 sec)

drop table t_org;
Query OK, 0 rows affected (0.06 sec)

結果:約1分39秒

まとめ

ストレージ容量を空けるために削除処理を施す場合、削除件数が少ないときでも、実験してみると普通にdeleteするよりも、新しいテーブルに必要なデータを挿入してリネームする方が早いことがある。大量のレコードを削除するときは新しいテーブル作った方が断然早い。

以上、小ネタ。

※ 他にも削除条件でselectしたPK使ってdelete from t where PK_column = xxxxと方式も試してみてもよかったかもしれない。

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Set your Twitter account name in your settings to use the TwitterBar Section.