いっぽんの猟銃のむこうに (DAIZOじいさんとGun)

ヌルめの技術メモとか。フリーランスやってます (http://acez.jp/)

load dataで読み込むCSVはソートされてる方が断然速い?

なんかそうらしいとかいう資料を見つける。
http://h50146.www5.hp.com/products/software/oe/linux/summary/mwtech/files/mysql-loaddata-v02.pdf

400万件のレコードを持つデータを作ってやってみた。

使ったのはSuperSmackで生成した適当なデータ。
テーブルはこんなん。

CREATE TABLE http_auth (
username char(255) not null primary key,
pass char(25),
uid integer,
gid integer);

MySQLInnoDBにしたくらいで特に何も設定してない奴で、
マシンはNEC Express 5800とゆー安物。HDDもついてたS-ATA。適当。


# ソートしたのとしてないのを用意
$ sort -t , /tmp/words.dat > /tmp/s_words.dat
(この間20秒)

# load dataの時間を計測する
$ (time mysql -u root --password=hogehoge benchmark -e \
"load data infile '/tmp/s_words.dat' into table http_auth fields terminated by ',';") 2> time.log

で、結果。

・ソートしてないもの

real 415m23.780s → 7時間弱
user 0m0.008s
sys 0m0.000s


・最初のカラムでソートしたもの

real 6m56.782s
user 0m0.000s
sys 0m0.004s

一発流しただけなんで数字は概算ですが、にしてもソートしてない奴遅すぎだろ……
primary keyがよくないのかしら?ちょっといろいろ試してみよう。

あと今回は400万でやりましたが、どうもこの結果は非線形に実行時間が伸びていくっぽいので、とにかく大量のCSVデータを高速で叩き込みたい!!ってときは事前にデータをソートしておくのがよいようです。

追記:
というか、primary keyが効いてるとインデックス再構築の手間がかかる分だけINSERTに凄い時間かかるみたいですね。既存データが入ってる場合の追記とかで「どうしても(PKを)外したくないでござるー!!」って時には、元CSVはソートしておくとよいかも、という結論みたいです。