MySQL
AWS
RDS

Amazon RDS(MySQL)で1億レコードのテーブルをTRUNCATE TABLEしてみる

MySQL(InnoDB)でトランザクションを張らずに大量のレコードを全行削除する場合、DELETEよりTRUNCATE TABLEの方が速いが、1億レコードの規模のテーブルでもサクッと削除できるのか検証。

環境

下記の環境で新規インスタンスを構築
DB:Amazon RDS (MySQL 5.7)
インスタンスサイズ:db.m4.large (2vCPU, mem=8GB)
ストレージサイズ:SSD 100GB (300IOPS)
リージョン:ap-northeast-1c (Single-AZ)

下準備

-- データベース作成
mysql> CREATE DATABASE `sampledb` DEFAULT CHARACTER SET utf8;
mysql> use sampledb;

-- テーブル作成
mysql> CREATE TABLE `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(255) NOT NULL,
`create_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 単純結合用のレコードをINSERT(10行)
mysql> INSERT INTO `sample` VALUES
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00'),
(NULL, 'sample', '2017-12-18 00:00:00');

-- INSERTした10行を、8回単純結合すると1億レコードになる(10^8=100000000)
mysql> SELECT count(*) FROM sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8;
+-----------+
| count(*)  |
+-----------+
| 100000000 |
+-----------+
1 row in set (11.16 sec)

実際に1億レコードをINSERTする

-- 実際に1億レコードをINSERTする
mysql> INSERT INTO `sample` (
select
sample.id = NULL,
sample.data,
sample.create_date
from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8
);
Query OK, 100000000 rows affected (10 min 17.67 sec)
Records: 100000000  Duplicates: 0  Warnings: 0

-- ファイルサイズを確認してみる
mysql> SELECT NAME,FILE_SIZE,ALLOCATED_SIZE 
FROM information_schema.innodb_sys_tablespaces WHERE NAME LIKE '%sample%';
+-----------------+------------+----------------+
| NAME            | FILE_SIZE  | ALLOCATED_SIZE |
+-----------------+------------+----------------+
| sampledb/sample | 3779067904 |     3779072000 |
+-----------------+------------+----------------+
1 row in set (0.00 sec)

TRUNCATE実行

mysql> TRUNCATE TABLE `sample`;
Query OK, 0 rows affected (2.06 sec)

2秒程度で全行削除できました。

参考