[MySQL]大量のレコードをDELETEする

MySQLで大量のレコードをDELETEする方法をいくつか試してみました。

(2016/9/17追記)
書き漏れましたが、MySQLは5.6.30で実験しています。

mysql> select  version();
+-----------+
| version() |
+-----------+
| 5.6.30    |
+-----------+
1 row in set (0.02 sec)

(2016/9/17追記終わり)

テスト用テーブル作成

まずテスト用のテーブルを用意します。0から9の値が均等に配置された1千万レコード。ほんとは1億レコードでやりたかったんだけど、さすがに時間が掛かりすぎたのでパス。

mysql> CREATE TABLE seed (
    -> value INTEGER UNIQUE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO seed ( value ) VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 );
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS huge;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE huge (
    -> id INTEGER PRIMARY KEY AUTO_INCREMENT,
    -> number INTEGER,
    -> string VARCHAR(255),
    -> INDEX num_idx ( number ),
    -> INDEX str_idx ( string ),
    -> CONSTRAINT num_fk FOREIGN KEY ( number ) REFERENCES seed( value )
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

いろいろ試してみたいので、プライマリキーとかインデックスとか外部制約とか付けてます。以後のテストでは、毎回テーブルをDROPして作り直してます。1千万レコードの作り方はオーソドックスに結合で。

mysql> INSERT INTO huge SELECT NULL, s1.value, s1.value FROM seed AS s1, seed AS s2, seed AS s3, seed AS s4, seed AS s5, seed AS s6, seed AS s7;
Query OK, 10000000 rows affected (2 min 3.49 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM huge;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (5.64 sec)

mysql> SELECT number, COUNT(*) FROM huge GROUP BY number;
+--------+----------+
| number | COUNT(*) |
+--------+----------+
|      0 |  1000000 |
|      1 |  1000000 |
|      2 |  1000000 |
|      3 |  1000000 |
|      4 |  1000000 |
|      5 |  1000000 |
|      6 |  1000000 |
|      7 |  1000000 |
|      8 |  1000000 |
|      9 |  1000000 |
+--------+----------+
10 rows in set (6.28 sec)

2分3.49秒かかって出来上がりました。

全DELETE

まずは単純に全レコードをDELETEで消してみます。

mysql> DELETE FROM huge;
Query OK, 10000000 rows affected (28 min 57.90 sec)

28分57.90秒。当然、めっちゃ時間かかりましたね。

TRUNCATE

全レコードを消すだけならDELETEではなくてTRUNCATEの方が速いです。

mysql> TRUNCATE TABLE huge;
Query OK, 0 rows affected (0.41 sec)

0.41秒。速いんですがauto_incrementの値が初期化されるという点がDELETEと違います。

mysql> SELECT auto_increment FROM information_schema.TABLES WHERE TABLE_NAME='huge';
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set (0.13 sec)

また、MySQLではTRUNCATEはロールバック出来ません。

一部DELETE

全レコード消すってのはあまり使い道がありませんよね。やはりWHERE句で一部のレコードだけを消すのが一般的だと思います。ということで1千万レコードの内、百万レコードを消してみます。

mysql> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (3 min 15.38 sec)

3分15.38秒でした。

一部DELETE(LIMIT指定)

一度にDELETEだと、終わるまで進捗が分からないので不安になります。いつ頃完了しそうか見積もることもできません。

と、先日まで思ってたんですがステータスでチェックできることを知りました。

mysql> SHOW GLOBAL STATUS LIKE 'Handler_delete';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| Handler_delete | 48608065 |
+----------------+----------+
1 row in set (0.00 sec)

とすれば、サーバ全体でのこれまでの削除レコード数が分かります。DELETE開始前の値をチェックしておいて、その値との差をみれば進捗がわかります。あくまでもサーバ全体の値であって他のセッションでのDELETEの件数も混じってきますから参考値にはなりますけれど。

ということで進捗はみれるのですが、漢(オトコ)のコンピュータ道: たった3秒でInnoDBのデータローディングが快適になるライフハックに次のような記述があります。

InnoDBテーブルへの更新においては、適当な間隔でコミットを行わなうべきである。InnoDBは更新時にUNDOログをロールバックセグメント内に作成する。UNDOログは不要になれば削除されるのだが、一度に大量の行を更新したり挿入したりすると肥大化してしまう。UNDOログが肥大化するとバッファプールの空きページが枯渇してしまい、テーブルスペース内に書き込まれることになってしまう。そうなるとI/Oの帯域が無駄になってしまうので、UNDOログが肥大化しないよう適切なサイズでコミットしよう。

一度に大量のレコードをDELETEするとログが巨大になってパフォーマンスも落ちてしまうそうです。ということで、LIMIT句を付けて10万レコードずつDELETEしてみます。

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (11.94 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (14.28 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (18.77 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (14.46 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (22.45 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (14.99 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (22.12 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (13.88 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (20.10 sec)

mysql> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (14.28 sec)

10回の処理時間を合計すると2分47.27秒ですので、まとめて消すよりも少し速かったですね。

一部DELETE(INDEX削減)

バックアップを戻すときなど大量にINSERTする際にはインデックスを一時的に落とすのは常套手段ですよね。ということで、DELETEでもインデックスを落としたらパフォーマンスが変わるのではないかと思い試してみました。

mysql> ALTER TABLE huge DROP INDEX str_idx;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (1 min 48.33 sec)

mysql> ALTER TABLE huge ADD INDEX str_idx (string);
Query OK, 0 rows affected (44.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

2分32.75秒。インデックスの再作成の時間を含めても、かなり速くなってますね。

一部DELETE(外部キー制約解除)

インデックスと同様、外部キー制約もパフォーマンスを下げる要因になります。外部キー制約を外して試してみます。

mysql> ALTER TABLE huge DROP FOREIGN KEY num_fk;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (3 min 16.11 sec)

mysql> ALTER TABLE huge ADD CONSTRAINT num_fk FOREIGN KEY ( number ) REFERENCES seed( value );
Query OK, 9000000 rows affected (3 min 0.13 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

3分16.11秒。削除時間は全く変わらず、外部キー制約の再設定の分だけ遅くなりました。考えてみればDELETE時には外部キー制約はチェックする必要はありませんから、この結果も当然でしょうか。

別テーブルの作成

大量のレコードをDELETEした結果として残るレコードの方が少ないのなら、その残るレコードだけを新規テーブルにコピーした方が速いのではないかというアイデアがあるので試してみました。

mysql> CREATE TABLE huge_copy LIKE huge;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO huge_copy SELECT * FROM huge WHERE number<>0;
Query OK, 9000000 rows affected (2 min 12.42 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

mysql> RENAME TABLE huge TO huge_old, huge_copy TO huge;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE huge_old;
Query OK, 0 rows affected (0.07 sec)

2分12.60秒。9割をコピーするのは流石に分が悪いかと予想したのですが、意外なことに速くなってます。大抵の場合において、この方法は検討する価値があると言えそうです。

注意点としては、テンポラリテーブルの分だけディスクが一時的に必要になることと、コピー中に元テーブルに変更が入る可能性がある場合にはトリガーを仕込むとか、RENAME後に差分を反映するなどの対応が必要になることでしょうか。

(2016/9/17追記)

すいません。create table likeでは外部キー制約がコピーされないということを先ほど初めて知りました。

mysql> show create table huge_copy;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                  |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| huge_copy | CREATE TABLE `huge_copy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT NULL,
  `string` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `num_idx` (`number`),
  KEY `str_idx` (`string`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ですのでこの手順には実際には外部キー制約付与が追加で必要になり、トータルでは遅くなるという結論になります。

(2016/9/17追記終わり)

別テーブルの作成(INDEX削減)

(2016/9/17追記)

別テーブルを作成する場合にインデックスを一時的に落とすというパターンが抜けていることに気が付いたので試しました。

mysql> CREATE TABLE huge_copy LIKE huge;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE huge_copy DROP INDEX str_idx;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO huge_copy SELECT * FROM huge WHERE number<>0;
Query OK, 9000000 rows affected (1 min 50.38 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE huge_copy ADD INDEX str_idx (string);
Query OK, 0 rows affected (49.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> RENAME TABLE huge TO huge_old, huge_copy TO huge;
Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE huge_old;
Query OK, 0 rows affected (0.05 sec)

2分40秒57とここまではいい感じですが、実際にはこのあと外部キー制約の付与手順が入りますので遅くなります。

(2016/9/17追記終わり)

パーティショニング

巨大なテーブルを扱うのであればパーティショニングだ！ということで、こちらも試してみました。パーティショニングでは外部キー制約が使えませんのでテーブル定義を少し変えます。

19.6 パーティショニングの制約と制限

パーティション化された InnoDB テーブルで外部キーがサポートされない InnoDB ストレージエンジンを使用するパーティション化されたテーブルでは、外部キーはサポートされません。

mysql> DROP TABLE IF EXISTS p_huge;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE p_huge (
    -> number INTEGER,
    -> string VARCHAR(255),
    -> INDEX num_idx ( number ),
    -> INDEX str_idx ( string )
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO p_huge SELECT s1.value, s1.value FROM seed AS s1, seed AS s2, seed AS s3, seed AS s4, seed AS s5, seed AS s6, seed AS s7;
Query OK, 10000000 rows affected (1 min 49.64 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

まずはパーティショニングしていない状態での速度。

mysql> DELETE FROM p_huge WHERE number=0;
Query OK, 1000000 rows affected (3 min 14.30 sec)

3分14.30秒。次にパーティショニング化（なんか変なことば。。。）。パーティショニングに使用するカラムはプライマリキーに含まれていないといけないのですが、プライマリキー自体がテーブルに無ければ大丈夫です。

19.6.1 パーティショニングキー、主キー、および一意キー

テーブルに一意キーがない場合 (主キーがない場合を含む) はこの制約は適用されず、カラム型がパーティショニングタイプと互換性があるかぎり、パーティショニング式に任意のカラムを使用できます。

mysql> ALTER TABLE p_huge PARTITION BY LIST( number ) (
    -> PARTITION num_0 VALUES IN ( 0 ),
    -> PARTITION num_1 VALUES IN ( 1 ),
    -> PARTITION num_2 VALUES IN ( 2 ),
    -> PARTITION num_3 VALUES IN ( 3 ),
    -> PARTITION num_4 VALUES IN ( 4 ),
    -> PARTITION num_5 VALUES IN ( 5 ),
    -> PARTITION num_6 VALUES IN ( 6 ),
    -> PARTITION num_7 VALUES IN ( 7 ),
    -> PARTITION num_8 VALUES IN ( 8 ),
    -> PARTITION num_9 VALUES IN ( 9 )
    -> );
Query OK, 10000000 rows affected (2 min 20.45 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

2分20.45秒。結構時間かかりましたね。そしてパーティショニングでDELETE。

mysql> DELETE FROM p_huge WHERE number=0;
Query OK, 1000000 rows affected (2 min 38.79 sec)

2分38.79秒。ちょっとだけ速くなりました。パーティショニングについて解説している記事では、対象となるテーブルが小さくなるから速くなると書かれています。個人的にはインデックスが効いていれば誤差の範囲ではないかと思っていたのですが、それなりに差が出ました。

そしていよいよパーティショニングの醍醐味、パーティションのドロップです。はいっ！

mysql> ALTER TABLE p_huge DROP PARTITION num_0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE p_huge ADD PARTITION (
    -> PARTITION num_0 VALUES IN ( 0 )
    -> );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

0.08秒。どーん。でええーー。はええーー。さすがでやんす。

(2017/3/29追記)
はてなブックマークで以下のコメントをいただいていました。

tmatsuu DELETE LIMITはSTATEMENTなレプリケーションだと死ねるので注意

ご指摘はこちらのことを指しておられるのかと思います。

17.1.2.1 ステートメントベースおよび行ベースレプリケーションのメリットとデメリット

SBR にとって安全でないステートメント データを変更するすべてのステートメント (INSERT DELETE、UPDATE、REPLACE ステートメントなど) を、ステートメントベースレプリケーションを使用して複製できるわけではありません。ステートメントベースレプリケーションを使用する場合、非決定的動作は複製が困難です。そのような DML (データ変更言語) ステートメントの例には次が含まれます。

(中略)

ORDER BY なしで LIMIT 句を使用する DELETE および UPDATE ステートメントは非決定的です。

ORDER BY なしで LIMIT 句を使用するとどのレコードが対象になるかは不定ですので、最悪マスタとスレーブでデータの不整合が起きる可能性があります。

それはそうなのですが、この記事で ORDER BY なしで LIMIT 句を使用しているのは、あくまでも削除の速度を稼ぐためです。最終的には対象のレコードは全て削除しますので、SBR でもマスタとスレーブのデータは一致します。削除途中に一瞬でも不整合が起きてはならないというのなら、まあそうですね。どうしてもそれが問題になるのでしたら、全体をトランザクションでかこってやればいいんじゃないでしょうか。その場合に速度がどれくらいになるかはやってみないとわかりませんが。

ともあれ、「死ねる」とまでの事態が起こるとは思えないのですが、私は何か見落としてますかね。