LOAD DATA INFILEでDBに大量のテストデータを追加する(MySQL)

DBにテストデータとして10万レコードくらい追加したいと思い、GolangとGORMでテストデータを追加するコードを書いた所、レコードを追加する処理の実行に数分かかってしまうことがあった為、他の方法を検討しました。
方法はいくつかあるようですが、LOAD DATA INFILEコマンドを使用する方法が速いようなので、今回はこの方法について試した内容をまとめます。

環境

Docker環境にMySQLコンテナを立ち上げて検証しました。

• docker-compose.yaml

version: "3.8"
services:
  mysql:
    build: ./mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - mysql:/var/lib/mysql
      - ./mysql/initdb.d:/docker-entrypoint-initdb.d
      - ./mysql/users.csv:/var/lib/mysql-files/users.csv
    ports:
      - 3306:3306

volumes:
  mysql:

• mysql/initdb.d/init.sql

CREATE DATABASE develop;
USE develop;

CREATE TABLE `users` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  `name` VARCHAR(191) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• mysql/Dockerfile

FROM mysql:5.7

COPY ./my.cnf /etc/mysql/conf.d/my.cnf
RUN chmod 644 /etc/mysql/conf.d/my.cnf

• mysql/my.cnf

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
explicit_defaults_for_timestamp = 1

DB起動

以下のコマンドでDBを起動し、コンテナに入ります。
docker compose build
docker compose up -d
docker compose exec mysql bash

パスワードを入力してMySQLへ接続します
mysql -u root -D develop -p
root

読み込み用データ

LOAD DATA INFILEコマンドはCSVファイルを読み込み、レコードを作成する為、事前にデータを作成しておく必要があります。
簡単な例ですが、Golangでuserデータ用のCSVを出力する例を示します。

• main.go

package main

import (
	"encoding/csv"
	"log"
	"os"
	"strconv"
)

const UserCount = 100000

func main() {
	if err := outputCSV(); err != nil {
		log.Fatal(err)
	}
}

func outputCSV() error {
	records := createRecords()

	f, err := os.Create("./mysql/users.csv")
	if err != nil {
		return err
	}

	w := csv.NewWriter(f)
	w.WriteAll(*records)
	w.Flush()

	if err := w.Error(); err != nil {
		return err
	}
	return nil
}

func createRecords() *[][]string {
	records := [][]string{}
	for i := 1; i <= UserCount; i++ {
		record := []string{}

		record = append(record, strconv.Itoa(i))   // id
		record = append(record, GetTimeString())   // created_at
		record = append(record, GetTimeString())   // updated_at
		record = append(record, "\\N")             // deleted_at
		record = append(record, GetRandomString()) // name

		records = append(records, record)
	}
	return &records
}

• GetTimeString(), GetRandomString()等の必要な生成データは適宜定義してください
• 注意点としては、deleted_at等、NULLを追加したい場合、"NULL"としてしまうと、文字列でNULLを格納しようとしてしまい、エラーが発生しますので、"\N"とするようにしてください
  詳しくはこちら：https://dev.mysql.com/doc/refman/5.6/ja/null-values.html

生成されるCSVは以下のような形式になります。

1,2022-05-24 23:24:00,2022-05-24 23:24:00,\N,bsex
2,2022-05-24 23:24:00,2022-05-24 23:24:00,\N,tzqj
3,2022-05-24 23:24:00,2022-05-24 23:24:00,\N,twvm
...

データ追加

MySQLへ接続した状態で、以下コマンドを実行し、データを追加します。
LOAD DATA INFILE "/var/lib/mysql-files/users.csv" INTO TABLE users FIELDS TERMINATED BY ",";

• LOAD DATA INFILE: 読み込むCSVファイルのパスを指定
  MySQLの設定でsecure_file_privの値として設定されているパスのみファイルを読み込むことができます。
  今回使用したコンテナではデフォルトで/var/lib/mysql-filesが設定されている為、users.csvがこのフォルダに格納されるようにdocker-compose.yamlで設定しています。
  secure_file_privの設定値は以下のコマンドで確認することができます。
  SHOW VARIABLES LIKE "secure_file_priv";

• INTO TABLE: データを追加するテーブルを指定
  今回の例でいくと、usersテーブルは事前に作成されている必要があります。

• FIELDS TERMINATED BY: カラムの区切り文字の指定
  カンマを区切りに各データを分割します。
  また、各レコードの分割については、デフォルトで"\n"になっています。
  今回は特に指定していませんが、環境によっては変更が必要な可能性があると思われます。

まとめ

今回試した方法により、数分かかっていたレコード追加が、数秒で完了するようになりました。
機会があれば参考にしてみてください。