mysql 批量插入数据

Posted on In

导入大数据到表

向数据库中插入3万条数据

单个插入数据

如果每个数据单独插入，效率很低，每个插入都产生一个事务，写入效率低

批量插入数据

  • 每1000条插入一次，只产生一个事件，降低了事务的生成，提高了效率

  • 具体方法

    1. 把多条插入语句拼成一条

    2. 手动开启一个事务，在插入1000条数据后，提交事务。

      这样做可以减少binlog的生成次数，但redolog不一定减少

批量 vs 单个插入

  • 单个插入效率很低，30 * 1000 条记录用时 1分钟

  • 批量插入， 每1000条插入一次，效率很高，用时7.99秒

    5.54s user 0.54s system 77% cpu 7.799 total

    5.38s user 0.48s system 78% cpu 7.452 total

总结

插入大量数据时，应减少事务的提交，让多个插入在一个事务中完成，从而提高插入数据的效率

测试代码

class ImportTradelog
  def self.import(date)
    # 导入30天的数据
    30.times do |offset|
      datas = init_data(date + (60 * 60 * 24) * offset)
      puts datas

      # 批量插入， 每1000条插入一次，效率很高，用时7.99秒
      # 5.54s user 0.54s system 77% cpu 7.799 total
      # 5.38s user 0.48s system 78% cpu 7.452 total
      Tradelog.bulk_insert set_size: 1000, values: datas
    end
  end

  # 每天导入1000条数据
  def self.init_data(day)
    datas = []
    1000.times do |index|
      # 插入效率很低，30 * 1000 条记录用时 1分钟
      # date = {tradeid: tradeid, operator: operator, t_modified: t_modified}
      # Tradelog.create(date)
      datas << {
        tradeid: "#{day.strftime('%Y%m%d')}#{format('%04d', (index + 1))}",
        operator: rand(50),
        t_modified: (day + 60 * index)
      }
    end

    datas
  end
end