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(１０行)
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秒程度で全行削除できました。