すでにあるMySQLのテーブルにパーティションを追加してみる。2019年夏。パフォーマンス確認

# 概要

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

# 環境

[この記事と同じ](https://qiita.com/abetomo/items/0f22b36f653478cdc459#%E7%92%B0%E5%A2%83)

# テーブル定義

```sql
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
```

# パーティションの追加

```sql
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` した結果

```sql
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万件程度

例）

```sql
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前確認

```sql
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

```sql
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後確認

```sql
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前確認

```sql
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

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

一瞬で消えた。

### drop後確認

```sql
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年夏。とりあえずエラーで困ったところ](https://qiita.com/abetomo/items/0f22b36f653478cdc459)