1. masarusan776

    Posted

    masarusan776
Changes in title
+Amazon RDS(MySQL)で1億レコードのテーブルをTRUNCATE TABLEしてみる
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,79 @@
+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)
+
+# 下準備
+```sql
+-- データベース作成
+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する
+```sql
+-- 実際に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実行
+```sql
+mysql> TRUNCATE TABLE `sample`;
+Query OK, 0 rows affected (2.06 sec)
+```
+2秒程度で全行削除できました。
+
+# 参考
+* [なんとなくやりたかった。MySQLのクエリのみで簡単に1億ものデータを作成する方法](https://liginc.co.jp/programmer/archives/2707
+)
+* [InnoDBテーブルサイズのいろいろな確認方法](http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0056)