Rails快速批量插入数据的几种方式:
1:Use transactions(减少 Commit 次数,从而微微的提速,但还是有 N 次数据库的调用)
2:Get down and dirty with the raw SQL
3:A single mass insert
4:Use INSERT statements with multiple VALUES lists with activerecord-import
第四种方式是AR方式的70倍左右,已经很快了,还有更快的吗?
From MySQL Doc:
When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements
SQL语句:
(15955.1ms) LOAD DATA LOCAL INFILE '/Users/hooopo/data/out/product_sales_facts.txt'
REPLACE INTO TABLE product_sale_facts FIELDS TERMINATED BY ',' (`id`,`date_id`,`order_id`,`product_id`,`address_id`,`unit_price`,`purchase_price`,`gross_profit`,`quantity`,`channel_id`,`gift`)
插入行数:
wc -l < /Users/hooopo/data/out/product_sales_facts.txt
1271413
大概130w数据用时16秒,平均每秒插入8w条记录左右。如果想用Gem,可以试试 adapter_extensions 和 load_data_infile:
https://github.com/activewarehouse/adapter_extensions
https://github.com/EmmanuelOga/load_data_infile
https://github.com/jsuchal/activerecord-fast-import
MySQL Doc里关于LOAD INFILE的详细介绍:
http://dev.mysql.com/doc/refman/5.5/en/load-data.html
http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html
转自:
http://shopperplus.github.io/blog/2014/11/08/fastest-way-to-load-data-in-mysql.html