大規模な MySQL データのエクスポートとインポート

はじめに

MySQL は検索を早めるため、行データの他に Index を管理しています。
この Index は行を追加するたびに更新されますが、大量のデータを一度に追加するインポートの場合は Index 更新にどんどん時間がかかるようになります。
結果、データが多いほどインポートにかかる時間が指数関数的に増加します。
よって、インポート時は Index を生成しないようにして、あとからまとめて Index を生成するようにすると、インポートにかかる時間を短縮できます。

以降、下記のシェル変数を使って、具体例を交えながら解説します。

項目	値	説明
DB_USER	db_user	MySQL ユーザー名
DB_PASSWORD	db_password	MySQL ユーザーのパスワード
DB_NAME	db_name	対象のデータベース名
DB_HOST	127.0.0.1	MySQLサーバーが稼動するホスト

DB_USER="db_user"
DB_PASSWORD="db_password"
DB_NAME="db_name"
DB_HOST="127.0.0.1"

エクスポート

$ mysqldump -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} --no-create-info ${DB_NAME} > table_data.sql

テーブル定義部分のエクスポート

$ mysqldump -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} --no-data ${DB_NAME} > table_definition_full.sql

テーブル定義の分離

table_definition_full.sql を、カラム定義部分 table_definition_column.sql と Index 定義部分 table_definition_index.sql に分離します。

1つのテーブルについて、編集前と編集後の具体例を挙げます。
テーブルの数だけ繰り返して実施します。

テーブル定義全体(編集前)

table_definition_full.sql から一部抜粋。

--
-- Table structure for table `sample_table`
--

DROP TABLE IF EXISTS `sample_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sample_table` (
  `column_01` int(11) NOT NULL AUTO_INCREMENT,
  `column_02` date DEFAULT NULL,
  `column_03` int(11) DEFAULT NULL,
  `column_04` int(11) DEFAULT NULL,
  `column_05` int(11) DEFAULT NULL,
  `column_06` int(11) DEFAULT NULL,
  `column_07` int(11) DEFAULT NULL,
  `column_08` int(11) DEFAULT NULL,
  `column_09` datetime NOT NULL,
  `column_10` datetime NOT NULL,
  `column_11` varchar(255) DEFAULT NULL,
  `column_12` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sample_table_on_column_02_and_column_03` (`column_02`,`column_03`),
  KEY `index_sample_table_on_column_11` (`column_11`),
  KEY `index_sample_table_on_column_12` (`column_12`)
) ENGINE=InnoDB AUTO_INCREMENT=1933824 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

カラム定義部分(編集後その1)

table_definition_column.sql から一部抜粋。カラム定義の下に続く、Index 定義部分を /* … */ でコメントアウトします。

--
-- Table structure for table `sample_table`
--

DROP TABLE IF EXISTS `sample_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sample_table` (
  `column_01` int(11) NOT NULL AUTO_INCREMENT,
  `column_02` date DEFAULT NULL,
  `column_03` int(11) DEFAULT NULL,
  `column_04` int(11) DEFAULT NULL,
  `column_05` int(11) DEFAULT NULL,
  `column_06` int(11) DEFAULT NULL,
  `column_07` int(11) DEFAULT NULL,
  `column_08` int(11) DEFAULT NULL,
  `column_09` datetime NOT NULL,
  `column_10` datetime NOT NULL,
  `column_11` varchar(255) DEFAULT NULL,
  `column_12` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)/*,
  KEY `index_sample_table_on_column_02_and_column_03` (`column_02`,`column_03`),
  KEY `index_sample_table_on_column_11` (`column_11`),
  KEY `index_sample_table_on_column_12` (`column_12`)*/
) ENGINE=InnoDB AUTO_INCREMENT=18882116 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Index 定義部分(編集後その2)

table_definition_index.sql から一部抜粋。

カラム定義でコメントアウトした Index 定義部分を抜き出して、ALTER TABLE … ADD KEY 構文または ALTER TABLE … ADD UNIQUE KEY 構文に書き換えます。

ALTER TABLE `sample_table`
  ADD KEY `index_sample_table_on_column_02_and_column_03` (`column_02`,`column_03`),
  ADD KEY `index_sample_table_on_column_11` (`column_11`),
  ADD KEY `index_sample_table_on_column_12` (`column_12`);

インポート

準備が整いましたので、計画のとおり順番にインポートしていきます。

テーブル定義(カラム部分)

$ mysql -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} ${DB_NAME} < table_definition_column.sql

テーブル定義(Index 部分)

$ mysql -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} ${DB_NAME} < table_definition_index.sql

おわりに

このように、テーブル定義から Index 部分を分離するかどうかでインポートにかかる時間が短縮できます。
分離しない手順は次のとおりです。

$ mysql -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} ${DB_NAME} < table_definition_full.sql
$ mysql -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} ${DB_NAME} < table_data.sql