概要

MySQLで「過去データをサクッと消したい」という要望があります。
drop partitionを利用したことがないので「drop partitionは本当に早いのか」を検証します。そのメモです。
ついでにテスト用データのインポートがてらパーティションの有無でinsertの速度が変わるのかゆるく検証してみます。

環境

この記事と同じ

テーブル定義

CREATE TABLE `example_table` (
  `user_id` varchar(36) NOT NULL,
  `item_id` int(11) NOT NULL,
  `batch_date` date NOT NULL,
  PRIMARY KEY (`user_id`,`item_id`,`batch_date`),
  KEY `item_id_batch_date_index` (`item_id`,`batch_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

パーティションの追加

alter table example_table
partition by range (to_days(batch_date))
(
  partition p201812 values less than (to_days('2019-01-01')),
  partition p201901 values less than (to_days('2019-02-01')),
  partition p201902 values less than (to_days('2019-03-01')),
  partition p201903 values less than (to_days('2019-04-01')),
  partition p201904 values less than (to_days('2019-05-01')),
  partition p201905 values less than (to_days('2019-06-01')),
  partition p201906 values less than (to_days('2019-07-01')),
  partition p201907 values less than (to_days('2019-08-01')),
  partition p201908 values less than (to_days('2019-09-01')),
  partition p201909 values less than (to_days('2019-10-01')),
  partition p201910 values less than (to_days('2019-11-01')),
  partition p201911 values less than (to_days('2019-12-01')),
  partition p201912 values less than (to_days('2020-01-01')),
  partition p202001 values less than (to_days('2020-02-01'))
);

追加後に `show create table` した結果

CREATE TABLE `example_table` (
  `user_id` varchar(36) NOT NULL,
  `item_id` int(11) NOT NULL,
  `batch_date` date NOT NULL,
  PRIMARY KEY (`user_id`,`item_id`,`batch_date`),
  KEY `item_id_batch_date_index` (`item_id`,`batch_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(batch_date))
(PARTITION p201812 VALUES LESS THAN (737425) ENGINE = InnoDB,
 PARTITION p201901 VALUES LESS THAN (737456) ENGINE = InnoDB,
 PARTITION p201902 VALUES LESS THAN (737484) ENGINE = InnoDB,
 PARTITION p201903 VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION p201904 VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION p201905 VALUES LESS THAN (737576) ENGINE = InnoDB,
 PARTITION p201906 VALUES LESS THAN (737606) ENGINE = InnoDB,
 PARTITION p201907 VALUES LESS THAN (737637) ENGINE = InnoDB,
 PARTITION p201908 VALUES LESS THAN (737668) ENGINE = InnoDB,
 PARTITION p201909 VALUES LESS THAN (737698) ENGINE = InnoDB,
 PARTITION p201910 VALUES LESS THAN (737729) ENGINE = InnoDB,
 PARTITION p201911 VALUES LESS THAN (737759) ENGINE = InnoDB,
 PARTITION p201912 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION p202001 VALUES LESS THAN (737821) ENGINE = InnoDB) */

検証で使うデータ

select count(*) で 12,550,978 件
期間: 約４ヶ月分
1日あたり9万から11万件程度

例）

mysql> select batch_date, count(*)
from example_table
group by batch_date;
+------------+----------+
| batch_date | count(*) |
+------------+----------+
| 2018-12-31 |   105525 |
| 2019-01-01 |    98958 |
| 2019-01-02 |    91431 |
| 2019-01-03 |   101750 |
| 2019-01-04 |    95111 |
| 2019-01-05 |    93807 |
...
| 2019-04-28 |   100994 |
| 2019-04-29 |    92203 |
| 2019-04-30 |   103806 |
| 2019-05-01 |    95683 |
| 2019-05-02 |   104395 |
| 2019-05-03 |    90513 |
| 2019-05-04 |    99946 |
+------------+----------+

insert

load data infile で空のテーブルにデータをinsert。
それにどのくらいかかるかゆるく計測
対象ファイルの行数は 12,550,978 行

結果
パーティションの有無で大きく時間が変わることはなかった
どちらもだいたい50分

削除の検証

delete文

delete前確認

mysql> select date_format(batch_date, '%Y-%m') d, count(*)
from  example_table
group by d;
+---------+----------+
| d       | count(*) |
+---------+----------+
| 2018-12 |   105525 |
| 2019-01 |  3061619 |
| 2019-02 |  2824514 |
| 2019-03 |  3141687 |
| 2019-04 |  3027096 |
| 2019-05 |   390537 |
+---------+----------+
6 rows in set (8.97 sec)

delete

mysql> delete from example_table
where batch_date between '2019-01-01' and '2019-01-31';
Query OK, 3061619 rows affected (55.01 sec)

今回のデータ量だと1分弱

delete後確認

mysql> select date_format(batch_date, '%Y-%m') d, count(*)
from  example_table
group by d;
+---------+----------+
| d       | count(*) |
+---------+----------+
| 2018-12 |   105525 |
| 2019-02 |  2824514 |
| 2019-03 |  3141687 |
| 2019-04 |  3027096 |
| 2019-05 |   390537 |
+---------+----------+
5 rows in set (5.74 sec)

1月だけ消えた

drop partition

drop前確認

mysql> select date_format(batch_date, '%Y-%m') d, count(*)
from  example_table
group by d;
+---------+----------+
| d       | count(*) |
+---------+----------+
| 2018-12 |   105525 |
| 2019-01 |  3061619 |
| 2019-02 |  2824514 |
| 2019-03 |  3141687 |
| 2019-04 |  3027096 |
| 2019-05 |   390537 |
+---------+----------+
6 rows in set (7.62 sec)

drop partition

mysql> alter table example_table drop partition p201901;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

一瞬で消えた。

drop後確認

mysql> select date_format(batch_date, '%Y-%m') d, count(*)
from  example_table
group by d;
+---------+----------+
| d       | count(*) |
+---------+----------+
| 2018-12 |   105525 |
| 2019-02 |  2824514 |
| 2019-03 |  3141687 |
| 2019-04 |  3027096 |
| 2019-05 |   390537 |
+---------+----------+
5 rows in set (5.92 sec)

1月だけ消えた

まとめ

delete

drop partition で一瞬で消せた
よかった

insert

真面目に検証してないけど、パーティションの有無でinsertの速度はそれほど変わらなそう
真面目に細かく確認したら差が出るのかな

関連

すでにあるMySQLのテーブルにパーティションを追加してみる。2019年夏。とりあえずエラーで困ったところ