LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@saoyagi2

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

Posted at

[MySQL]大量のレコードをDELETEする という記事を書いたのは 2016 年のこと。使用した MySQL も 5.6.30 でした。MySQL も今では 8.0 系列が登場していますので、同じクエリを MySQL 8.0 環境で動かしてみました。

使用したのは MySQL 8.0.21 です。

root@localhost [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

開発機で試したので root ユーザになってますがご容赦を。

テスト用テーブル作成

0から9の値が均等に配置された1千万レコード。いろいろ試すためプライマリキー、インデックス、外部制約などを付けてます。以後のテストでは、毎回テーブルを DROP して作り直してます。

root@localhost [test]> CREATE TABLE seed (
    ->   value INTEGER UNIQUE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.29 sec)

root@localhost [test]> INSERT INTO seed (value) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

root@localhost [test]> DROP TABLE IF EXISTS huge;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost [test]> 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;

root@localhost [test]> 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 (6 min 38.02 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

root@localhost [test]> SELECT COUNT(*) FROM huge;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.89 sec)

root@localhost [test]> 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 (4.10 sec)

全DELETE

root@localhost [test]> DELETE FROM huge;
Query OK, 10000000 rows affected (18 min 21.52 sec)

TRUNCATE

root@localhost [test]> TRUNCATE TABLE huge;
Query OK, 0 rows affected (2.27 sec)

一部DELETE

root@localhost [test]> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (1 min 14.75 sec)

一部DELETE(LIMIT指定)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (4.19 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (4.88 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (5.27 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (8.73 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (9.62 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (10.43 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (11.02 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (12.21 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (13.78 sec)

root@localhost [test]> DELETE FROM huge WHERE number=0 LIMIT 100000;
Query OK, 100000 rows affected (13.87 sec)

一部DELETE(INDEX一時削除)

root@localhost [test]> ALTER TABLE huge DROP INDEX str_idx;
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (1 min 7.10 sec)

root@localhost [test]> ALTER TABLE huge ADD INDEX str_idx (string);
Query OK, 0 rows affected (2 min 5.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

root@localhost [test]> ALTER TABLE huge DROP FOREIGN KEY num_fk;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> DELETE FROM huge WHERE number=0;
Query OK, 1000000 rows affected (1 min 12.24 sec)

root@localhost [test]> ALTER TABLE huge ADD CONSTRAINT num_fk FOREIGN KEY (number) REFERENCES seed(value);
Query OK, 9000000 rows affected (4 min 15.43 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

別テーブルの作成

root@localhost [test]> CREATE TABLE huge_copy LIKE huge;
Query OK, 0 rows affected (0.06 sec)

root@localhost [test]> ALTER TABLE huge_copy ADD CONSTRAINT num_fk_copy FOREIGN KEY (number) REFERENCES seed(value);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> INSERT INTO huge_copy SELECT * FROM huge WHERE number<>0;
Query OK, 9000000 rows affected (5 min 55.15 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

root@localhost [test]> RENAME TABLE huge TO huge_old, huge_copy TO huge;
Query OK, 0 rows affected (0.71 sec)

root@localhost [test]> DROP TABLE huge_old;
Query OK, 0 rows affected (0.67 sec)

別テーブルの作成(INDEX一時削除)

root@localhost [test]> CREATE TABLE huge_copy LIKE huge;
Query OK, 0 rows affected (0.08 sec)

root@localhost [test]> ALTER TABLE huge_copy ADD CONSTRAINT num_fk_copy FOREIGN KEY (number) REFERENCES seed(value);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> ALTER TABLE huge_copy DROP INDEX str_idx;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> INSERT INTO huge_copy SELECT * FROM huge WHERE number<>0;
Query OK, 9000000 rows affected (4 min 47.31 sec)
Records: 9000000  Duplicates: 0  Warnings: 0

root@localhost [test]> ALTER TABLE huge_copy ADD INDEX str_idx (string);
Query OK, 0 rows affected (1 min 8.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> RENAME TABLE huge TO huge_old, huge_copy TO huge;
Query OK, 0 rows affected (0.10 sec)

root@localhost [test]> DROP TABLE huge_old;
Query OK, 0 rows affected (0.74 sec)

パーティショニングDROP

root@localhost [test]> DROP TABLE IF EXISTS p_huge;
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost [test]> 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.08 sec)

root@localhost [test]> 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 (5 min 8.32 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

root@localhost [test]> 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 (3 min 47.23 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

root@localhost [test]> ALTER TABLE p_huge DROP PARTITION num_0;
Query OK, 0 rows affected (2.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> ALTER TABLE p_huge ADD PARTITION (
    ->   PARTITION num_0 VALUES IN ( 0 )
    -> );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

総評

MySQL 5.6.30 と比較してみました。ちなみに前回と今回で使用したハードウェアは同じです。

テスト項目 MySQL 5.6.30 MySQL 8.0.21
全DELETE 28分57.90秒 18分21.52秒
TRUNCATE 0.41秒 2.27秒
一部DELETE 3分15.38秒 1分14.75秒
一部DELETE(LIMIT指定) 2分47.27秒 1分34秒
一部DELETE(INDEX一時削除) 2分32.75秒 3分13.36秒
一部DELETE(外部キー制約一時解除) 3分16.11秒 5分27.74秒
別テーブルの作成 2分12.60秒※ 5分56.87秒
別テーブルの作成(INDEX一時削除) 2分40.57秒 5分57秒
パーティショニングDROP 0.08秒 2.27秒

※ MySQL 5.6 では外部キー制約のコピーが漏れていたので直接比較はできない。

こうしてみると 8.0 系になって大量 DELETE はかなり速くなってますね。そして、下手に小細工を弄するよりも素直な手順でやった方がよさそうですね。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?