Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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

テーブル定義を見る

まずは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は瞬時に終わります)、検討してみてください。

hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away