最速でMySQLデータベースのデータをCSVに書き出す方法

MAGAZINE

ルーターマガジン

インフラ/運用

最速でMySQLデータベースのデータをCSVに書き出す方法

2021.07.14

Pocket

こんにちは、学生アルバイトのkyonoです。

2時に寝て5時に起き、二度寝をしたら12時半。睡眠とは斯くも摩訶不思議で絶望的なものなのです。そのとき見た夢では、鏡から出てきたゴスロリ美少女が涼しい顔で預金額を見せびらかしてきた挙句、私の口座のお金をすべてFX口座に突っ込んでくれました。何かの予知夢かと思ってロト7を一万円分買ったらすべて外れました。私は一万円と午前を失い、途方に暮れました。

さてSQLをRubyで扱うのにとても便利なActiveRecord。レコードをバッチ化してループしてくれる、メモリに優しいfind_eachのように、多くの便利な機能が備わっています。しかしレコードをCSVに書き出そうと思うと話は別。to_csvみたいな一発でCSVに変換してくれるメソッドは残念ながらありません。ならどうするのが主流かと言えば、何とCSVファイルをopenして一行ずつ書き込むというもの。「こんな事の為にわざわざ全行ループ……？」という気持ちが湧いてきます。何とかしてできるだけ速くCSVに書き出したい。そこで様々な書き出し方を比較し、最速の方法をここに決定したいと思います。

使用するデータとマシン

国税庁が公開している法人番号リストの全国データを用います。サイズは198MB、行数は5015295行です。 SQLサーバは10.1.28-MariaDBです。テーブル定義は以下の通りです。

CREATE TABLE `register_companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `corporate_number` bigint DEFAULT NULL,
  `process` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `correct` tinyint(1) DEFAULT NULL,
  `update_date` date DEFAULT NULL,
  `change_date` date DEFAULT NULL,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `name_image_id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `kind` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `prefecture_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `city_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `street_number` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `address_image_id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `prefecture_code` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `city_code` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `post_code` varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `address_outside` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `address_outside_image_id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `close_date` date DEFAULT NULL,
  `close_cause` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `successor_corporate_number` bigint DEFAULT NULL,
  `change_cause` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `assignment_date` date DEFAULT NULL,
  `latest` tinyint(1) DEFAULT NULL,
  `en_name` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `en_prefecture_name` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `en_city_name` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `en_address_outside` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `furigana` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `hihyoji` tinyint(1) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1126385 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

実行するSQL文はSELECT * FROM `register_companies` WHERE `latest`=TRUE AND `hihyoji`=FALSE;と等価になるようにします。

OSはCentOS Linux release 7.3.1611 (Core)、CPUはIntel(R) Xeon(R) CPU E5-2697A v4 @ 2.60GHzで論理コア数は2、メモリ容量は3GB、ディスクサイズは100GBです。

単純な形式変換速度の比較

検証した方法は以下の通りです。

find_eachでループ
レコードをバッチ化してpluckしてループ

こちらのブログを参照

レコードをmysql2resultsetの形で保持してループ

こちらのブログを参照

mysqlコマンドでの結果の吐き出し
mysqlコマンドでxml形式での結果の吐き出し
mysqldumpコマンドでxml形式での結果の吐き出し

計測にはtimeコマンドを使用しました。

find_eachでループ

コードは以下の通りです。 find_eachの中のnilになっている部分に、CSVに書き出す処理を記述します。

require 'active_record'

# DB接続処理
ActiveRecord::Base.establish_connection(
  :adapter   => 'mysql2',
  :database  => 'to_csv_test',
  :host      => '127.0.0.1',
  :username  => 'root',
  :password  => '',
  :charset   => 'utf8mb4',
  :encoding  => 'utf8mb4',
  :collation => 'utf8mb4_general_ci'
)

Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local

class RegisterCompany < ActiveRecord::Base; end

def select_into_csv_and_columntype
  # カラムをselectしてCSVに変換
  selected = RegisterCompany.where(latest: true, hihyoji: false)
  create_csv(selected)
end

def create_csv(selected_objects)
  selected_objects.find_each do |selected_object|
    nil
  end
end

if __FILE__ == $0
  select_into_csv_and_columntype
end

結果は以下の通りです。

real	2m44.168s
user	2m24.636s
sys	0m0.718s

レコードをバッチ化してpluckしてループ

以下のようなpluck_eachメソッドを実装しました。こちらのブログのコードを少し改造したものです。バッチ化するのにlast_pk_val..last_pk_val+1000のようなRangeで指定してしまうと、その結果がActiveRecordのオブジェクトでなくなり、pluckなどのメソッドが使えなくなってしまいます。このため、主キーがなければそれを追加して、ActiveRecordオブジェクトのまま部分レコードを切り出せるようにしています。ここではバッチサイズをfind_eachのデフォルトと同じ1000にしています。

require 'active_record'

# DB接続処理
ActiveRecord::Base.establish_connection(
  :adapter   => 'mysql2',
  :database  => 'to_csv_test',
  :host      => '127.0.0.1',
  :username  => 'root',
  :password  => '',
  :charset   => 'utf8mb4',
  :encoding  => 'utf8mb4',
  :collation => 'utf8mb4_general_ci'
)

Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local

# coding: utf-8
module ActiveRecord
  module Calculations

    def pluck_each(batch_size = 1000)
      columns = self.values[:select] ? self.values[:select] : self.column_names # selectメソッドなしでwhereだけだとnilが返ってくる
      # primary key の位置特定 (なければ columns の最後に追加)
      pk_idx = columns.map(&:to_s).index(self.primary_key) || columns.count
      pk_is_found = pk_idx < columns.count
      columns << self.primary_key unless pk_is_found

      relation = self.reorder(self.primary_key)
      part = relation.limit(batch_size)
      last_pk_val = nil
      loop do
        part = relation.where("#{self.primary_key} > ?", last_pk_val).limit(batch_size) unless last_pk_val.nil?

        # 全レコードを yield したら終了
        c = part.size
        break if c == 0

        # 部分的に pluck を実行
        part.pluck(*columns).each do |ary|
          yield ary[0..-2]
          if pk_is_found
            last_pk_val = ary[pk_idx]
          else
            last_pk_val = ary[-1]
          end
        end
      end
    end

  end
end

class RegisterCompany < ActiveRecord::Base; end

def select_into_csv_and_columntype
  # カラムをselectしてCSVに変換
  selected = RegisterCompany.where(latest: true, hihyoji: false)
  create_csv(selected)
end

def create_csv(selected_objects)
  selected_objects.pluck_each do |selected_object|
    nil
  end
end

if __FILE__ == $0
  select_into_csv_and_columntype
end

ループ部分のオブジェクトはこの通りです。 nilになっている部分に、CSVに書き出す処理を記述します。

結果は以下の通りです。

real	3m49.158s
user	3m25.421s
sys	0m0.855s

レコードをmysql2resultsetの形で保持してループ

mysql2resultsetを得る為には、普通のselectメソッドなどを使うのではなく、以下のように生SQLを直接実行する必要があります。

results = ActiveRecord::Base.connection.execute('select * from register_companies where latest = 1 and hihyoji = 0')

更にestablish_connectionでの設定で、通常の設定以外に:stream => true, :cache_rows => falseという設定を追加する必要があります。

全体のソースコードは以下の通りです。ループ部分はeachで実行します。

require 'active_record'

# DB接続処理
ActiveRecord::Base.establish_connection(
  :adapter   => 'mysql2',
  :database  => 'to_csv_test',
  :host      => '127.0.0.1',
  :username  => 'root',
  :password  => '',
  :charset   => 'utf8mb4',
  :encoding  => 'utf8mb4',
  :collation => 'utf8mb4_general_ci',
  :stream     => true,
  :cache_rows => false
)

Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local

def select_into_csv_and_columntype
  # カラムをselectしてCSVに変換
  results = ActiveRecord::Base.connection.execute("select * from register_companies where latest = 1 and hihyoji = 0")
  create_csv(results)
end

def create_csv(resultset)
  resultset.each do |fields|
    nil
  end
end

if __FILE__ == $0
  select_into_csv_and_columntype
end

結果は以下の通りです。

real	1m20.959s
user	1m19.983s
sys	0m0.964s

mysqlコマンドでの結果の吐き出し

これは直接mysqlコマンドを叩いて、全件データを吐かせます。なので書き込み部分でループはありません。

time mysql to_csv_test -uroot -e "select * from register_companies where latest = 1 and hihyoji = 0" > /dev/null

というコマンドで実行時間を計測しました。

結果は以下の通りです。途中でコネクションが失われてしまって、処理が完了しませんでした。

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

real	0m9.417s
user	0m0.842s
sys	0m1.099s

mysqlコマンドでxml形式での結果の吐き出し

mysqlコマンドで普通に結果を出力するのとほぼ一緒です。ループはありません。

time mysql -uroot -D to_csv_test --xml -e "select * from register_companies where latest=TRUE and hihyoji=FALSE" > /dev/null

結果は以下の通りです。これも途中でKillされてしまって、処理が完了しませんでした。どうやらmysqlコマンドでは500万件ものデータは処理しきれないようです。

Killed

real	0m19.664s
user	0m1.360s
sys	0m1.397s

mysqldumpコマンドでxml形式での結果の吐き出し

こちらはmysqldumpコマンドでxmlに書き出す方法です。この方法もループはありません。

time mysqldump -uroot to_csv_test --where "latest=TRUE and hihyoji=FALSE" register_companies --xml > /dev/null

結果は以下の通りです。こちらはKillされませんでした。

real	1m8.278s
user	0m58.706s
sys	0m0.685s

因みにCSV形式に変換するには、mysqldump -uroot to_csv_test --where "latest=TRUE and hihyoji=FALSE" register_companies --xml | LC_ALL=C awk -F'>' '{print $2}' | LC_ALL=C sed -ze "s/<\/field\n/,/g" -ze "s/,\n//g" | LC_ALL=C grep -v ^$としてやれば良いです。

形式変換の最速は？

mysqldumpコマンドを用いてxml形式で吐き出す方法に軍配が上がりました。

また次点でmysql2resultsetを用いる方法が速いです。

CSV書き出し方法の速さの比較

検証した方法は以下の通りです。

CSV.openで新規ファイルを開いて書き込み
Array#to_csvで標準出力に吐き出し
File.openで新規ファイルを開いて書き込み
Arrayをjoinして形式を変え、標準出力に吐き出し

CSV.openで新規ファイルを開いて書き込み

スクリプトは以下の通りです。

require 'csv'

array = [["a", "b", '"c"', 'd"d"d', 'e"e"', 0]] * 5000000

CSV.open('./csv_open.csv', 'w') do |file|
  array.each do |line|
    file << line
  end
end

結果は以下の通りです。

real    0m57.066s
user    0m56.658s
sys     0m0.396s

Array#to_csvで標準出力に吐き出し

スクリプトは以下の通りです。ここではファイルに書き出さず、標準出力に吐き出しています。

require 'csv'

array = [["a", "b", '"c"', 'd"d"d', 'e"e"', 0]] * 5000000

array.each do |line|
  puts line.to_csv
end

結果は以下の通りです。

real    6m37.053s
user    6m36.862s
sys     0m0.177s

これだけ極端に遅いです。

File.openで新規ファイルを開いて書き込み

スクリプトは以下の通りです。 CSVの形式に合わせるため、ダブルクォーテーションを二重にしてエスケープしなければなりません。

array = [["a", "b", '"c"', 'd"d"d', 'e"e"', 0]] * 5000000

File.open('./file_open.csv', 'w') do |file|
  array.each do |line|
    joined = line.join(',')
    file.print("\"#{joined.gsub('"', '""').gsub(',','","')}\"\n")
  end
end

結果は以下の通りです。

real    0m28.539s
user    0m28.171s
sys     0m0.366s

Arrayをjoinして形式を変え、標準出力に吐き出し

スクリプトは以下の通りです。こちらもCSVの形式に合わせるため、ダブルクォーテーションを二重にしてエスケープしなければなりません。ここではファイルに書き出さず、標準出力に吐き出しています。

array = [["a", "b", '"c"', 'd"d"d', 'e"e"', 0]] * 5000000

array.each do |line| 
  joined = line.join(',')
  puts "\"#{joined.gsub('"', '""').gsub(',','","')}\""
end

結果は以下の通りです。

real    0m28.223s
user    0m28.130s
sys     0m0.081s

CSV書き出しの最速は？

以上の結果から、File.openかArrayをjoinする方法が最も速いようです。 CSVモジュールの存在価値が疑わしくなってきました。こんな適当な自前実装より遅いものを我々は今まで有り難がっていたのです。

折角なので決勝戦をやってみましょう。それぞれの方法を10回ずつ計測したところ、信頼区間95%でFile.openは28.05±0.71s、Arrayをjoinする方法は28.29±0.76sとなりました。まあ殆ど変わらないですね。

それにしてもto_csvのこの遅さは何なのでしょうか。ソースコードを見るとどうやら呼び出す度にCSVクラスのインスタンスを作っているようで、そりゃあそれを1行毎にやらせれば遅いよなあ……という感じでした。あまり奇特な事をやらないならCSVをそのままファイルに書き出すことが多いと思うので、このメソッドの出番はあまり無さそうです。

最終的に何を使うべきか

一番のネックであるActiveRecordのデータから文字列への変換部分ではmysqldumpコマンドを用いてxml形式で吐き出す方法が最速でした。これを使えばあとはxmlをcsv形式に変換するだけです。

RubyのArray形式で持っておきたいならmysql2resultsetです。その際にCSV書き出しをするなら、File.openかArrayをjoinすれば良いです。

Pocket

CONTACT

お問い合わせ・ご依頼はこちらから