Mariadbでのテーブル全体の更新方法

考えられる方法

1） テーブルの全レコードをDELETE して、新しいレコードをINSERT
2） テーブルをDROP/CREATEして、新しいレコードをINSERT
3） 別テーブルをCREATEして、そこに新しいレコードをINSERTしたあと、テーブルをDROP/RENAMEで置換

この中で、テーブルが使えない時間が最短なのは、3）。
もちろん、new_item　テーブルを作るには上記1）2）のケースと同じ時間がかかりますが、
事前に作っておける点がポイントです。
ただしテーブル容量が倍になるので、その分ディスク容量が必要。

実験

AWSもt2.microにて。
事前準備として、100万件のテーブルデータを作ります。

$ mysql -u root db
CREATE TABLE item (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name1 VARCHAR(10),
  name2 VARCHAR(100),
  name3 VARCHAR(100),
  name4 VARCHAR(100),
  name5 VARCHAR(100),
  description1 VARCHAR(10),
  description2 VARCHAR(100),
  description3 VARCHAR(100),
  description4 VARCHAR(100),
  description5 VARCHAR(100),
  price1 INT UNSIGNED,
  price2 INT UNSIGNED,
  price3 INT UNSIGNED,
  price4 INT UNSIGNED,
  price5 INT UNSIGNED,
  created_at DATETIME,
  updated_at DATETIME
);

INSERT INTO item () VALUES (); --1行作成
INSERT INTO item (id) SELECT 0 FROM item; --2行になる
INSERT INTO item (id) SELECT 0 FROM item; --4行になる
INSERT INTO item (id) SELECT 0 FROM item; --8行になる
INSERT INTO item (id) SELECT 0 FROM item; --16行になる
INSERT INTO item (id) SELECT 0 FROM item; --32行になる
INSERT INTO item (id) SELECT 0 FROM item; --64行になる
INSERT INTO item (id) SELECT 0 FROM item; --128行になる
INSERT INTO item (id) SELECT 0 FROM item; --256行になる
INSERT INTO item (id) SELECT 0 FROM item; --512行になる
INSERT INTO item (id) SELECT 0 FROM item; --1024行になる
INSERT INTO item (id) SELECT 0 FROM item; --2048行になる
INSERT INTO item (id) SELECT 0 FROM item; --4096行になる
INSERT INTO item (id) SELECT 0 FROM item; --8192行になる
INSERT INTO item (id) SELECT 0 FROM item; --16384行になる
INSERT INTO item (id) SELECT 0 FROM item; --32768行になる
INSERT INTO item (id) SELECT 0 FROM item; --65536行になる
INSERT INTO item (id) SELECT 0 FROM item; --131072行になる
INSERT INTO item (id) SELECT 0 FROM item; --262144行になる
INSERT INTO item (id) SELECT 0 FROM item; --524288行になる
INSERT INTO item (id) SELECT 0 FROM item; --1048576行になる

UPDATE item SET
  name1 = CONCAT('商品', id),
  name2 = CONCAT('値段', id),
  name3 = CONCAT('説明', id),
  name4 = CONCAT('原価', id),
  name5 = CONCAT('利益', id),
  description1 = SUBSTRING(MD5(RAND()), 1, 100),
  description2 = SUBSTRING(MD5(RAND()), 1, 100),
  description3 = SUBSTRING(MD5(RAND()), 1, 100),
  description4 = SUBSTRING(MD5(RAND()), 1, 100),
  description5 = SUBSTRING(MD5(RAND()), 1, 100),
  price1 = CEIL(RAND() * 10000),
  price2 = CEIL(RAND() * 10000),
  price3 = CEIL(RAND() * 10000),
  price4 = CEIL(RAND() * 10000),
  price5 = CEIL(RAND() * 10000),
  created_at = ADDTIME(CONCAT_WS(' ','2014-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))),
  updated_at = ADDTIME(CONCAT_WS(' ','2014-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

$ mysqldump --complete-insert --skip-extended-insert -u root db item > sample_not_extended.sql

1）のケース

$ mysql -u root db
> delete from item;
Query OK, 1048576 rows affected (13.60 sec)
> source sample_not_extended.sql
Query OK, 0 rows affected (0.00 sec)
...(100万回）
およそ、713.00 sec

2)のケース

$ mysql -u root db
> drop table item;
Query OK, 0 rows affected (0.00 sec)
> source sample_not_extended.sql
Query OK, 0 rows affected (0.00 sec)
...(100万回）
およそ、713.00 sec

3)のケース

$ mysql -u root db
> drop table item;
Query OK, 0 rows affected (0.00 sec)
> rename table new_item to item
Query OK, 0 rows affected (0.00 sec)