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

はじめに

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

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

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

エクスポート

 もともとのテーブル定義には Index が追加されている場合があります。
 Index 定義部分のみ除去してからデータをインポートして、それが終われば Index を生成、という流れになりますので、データ部分とテーブル定義部分はこの段階で分けてエクスポートします。
 また、Index 定義部分はデータインポートが終わってから既存のテーブルに対して実行できるようにしたいので、エクスポートが終わった後、テーブル定義全体からカラム定義部分と Index 定義部分とに分離します。

データ部分のエクスポート

$ 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

データ

$ mysql -u${DB_USER} -p${DB_PASSWORD} -h${DB_HOST} ${DB_NAME} < table_data.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

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください