MySQLのテーブルローテーションなど

ここにも書きましたが、近々社内の障害対応訓練があります。
訓練で使う（かもしれない）ネタをあげておかないとブーイングが出そうなので、非常にありがちな内容ですが、ここにあげておきます。

テーブル定義を見る

まずはDESC。使うテーブルは前にあげたネタで使ったやつです。

mysql> USE cardi_test;
Database changed

mysql> DESC test_data;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
| flag  | int(1)      | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

とりあえず構造はわかりますが、INDEXがわかりづらいので、CREATE文の内容を見てみます。

mysql> SHOW CREATE TABLE test_data\G
*************************** 1. row ***************************
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` bigint(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `flag` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_flag` (`flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

この例にはありませんが、パーティショニングの定義は、ただのDESCだけではわからないので要注意です。

ちなみに、今まで何気なく使っていたので全然気付いていませんでしたが、DESC（DESCRIBE）文はEXPLAIN文のシノニムだそうです。

mysql> EXPLAIN test_data;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
| flag  | int(1)      | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESC SELECT * FROM test_data;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

テーブルをコピーする

作業ミスに備えて、バックアップのためにテーブルをコピーすることがあると思いますが、うっかりするとINDEXやパーティショニングの定義が抜け落ちてしまいます。

mysql> CREATE TABLE test_data2 AS SELECT * FROM test_data;
Query OK, 10000 rows affected (0.43 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql> DESC test_data2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
| flag  | int(1)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE test_data2\G
*************************** 1. row ***************************
       Table: test_data2
Create Table: CREATE TABLE `test_data2` (
  `id` bigint(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `flag` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

セカンダリINDEXどころか、PRIMARY KEYすらありませんね。
というわけで、こちらを使いましょう。

mysql> CREATE TABLE test_data3 LIKE test_data;
Query OK, 0 rows affected (0.02 sec)

mysql> DESC test_data3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
| flag  | int(1)      | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE test_data3\G
*************************** 1. row ***************************
       Table: test_data3
Create Table: CREATE TABLE `test_data3` (
  `id` bigint(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `flag` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_flag` (`flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

INDEXも正しくコピーされました。

テーブルをローテーションする

先の例の「CREATE TABLE … LIKE」構文は、当然ですが単独でも使えますので、テーブル構造だけコピーして空のテーブルを作り、レコードが入ったテーブルを別名に変更することで、テーブルローテーションをすることもできます。

mysql> CREATE TABLE test_data_new LIKE test_data;
Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE test_data TO test_data_old, test_data_new TO test_data;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT COUNT(*) FROM test_data;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM test_data_old;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> DESC test_data;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
| flag  | int(1)      | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE test_data\G
*************************** 1. row ***************************
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` bigint(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `flag` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_flag` (`flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

ローテーションに合わせてテーブル構造を変えたいときなどは、「CREATE TABLE … LIKE」構文を使わずに新しいCREATE文を発行したくなりますが、INDEXやパーティション定義などをうっかり忘れてしまいがちです。
「CREATE TABLE … LIKE」構文に続けてALTER文を発行する方法でミスを防ぐこともできますので（空のTABLEに対してのALTERは瞬時に終わります）、検討してみてください。