mysql - 从R到SQL插入数据帧的有效方法

我有一个要插入到现有sql表中的具有1000万行和5列的数据框。请注意，我没有创建表的权限，只能将值插入现有表中。我目前正在使用RODBCext

query_ch <- "insert into [blah].[dbo].[blahblah]
               (col1, col2, col3, col4, col5)
               values (?,?,?,?,?)"

sqlExecute(channel, query_ch, my_data)

这花费的时间太长(超过10个小时)。有没有办法更快地做到这一点？

最佳答案

TL; DR: LOAD DATA INFILE比多个INSERT语句快一个数量级，后者本身比单个INSERT语句快一个数量级。
我将以下三种从R导入数据到Mysql的主要策略进行了基准测试:

单个insert语句，如问题所示:INSERT INTO test (col1,col2,col3) VALUES (1,2,3)

多个insert语句，格式如下:INSERT INTO test (col1,col2,col3) VALUES (1,2,3),(4,5,6),(7,8,9)

load data infile语句，即在mysql中加载先前编写的CSV文件:LOAD DATA INFILE 'the_dump.csv' INTO TABLE test

我在这里使用RMySQL，但是任何其他mysql驱动程序都应该导致类似的结果。 SQL表用以下实例化:

CREATE TABLE `test` (
  `col1` double, `col2` double, `col3` double, `col4` double, `col5` double
) ENGINE=MyISAM;

连接和测试数据是使用以下格式在R中创建的:

library(RMySQL)
con = dbConnect(MySQL(),
                user = 'the_user',
                password = 'the_password',
                host = '127.0.0.1',
                dbname='test')

n_rows = 1000000 # number of tuples
n_cols = 5 # number of fields
dump = matrix(runif(n_rows*n_cols), ncol=n_cols, nrow=n_rows)
colnames(dump) = paste0('col',1:n_cols)

对单个insert语句进行基准测试:

before = Sys.time()
for (i in 1:nrow(dump)) {
  query = paste0('INSERT INTO test (',paste0(colnames(dump),collapse = ','),') VALUES (',paste0(dump[i,],collapse = ','),');')
  dbExecute(con, query)
}
time_naive = Sys.time() - before

=>在我的计算机上，这大约需要 4分钟

对多个insert语句进行基准测试:

before = Sys.time()
chunksize = 10000 # arbitrary chunk size
for (i in 1:ceiling(nrow(dump)/chunksize)) {
  query = paste0('INSERT INTO test (',paste0(colnames(dump),collapse = ','),') VALUES ')
  vals = NULL
  for (j in 1:chunksize) {
    k = (i-1)*chunksize+j
    if (k <= nrow(dump)) {
      vals[j] = paste0('(', paste0(dump[k,],collapse = ','), ')')
    }
  }
  query = paste0(query, paste0(vals,collapse=','))
  dbExecute(con, query)
}
time_chunked = Sys.time() - before

=>在我的计算机上，这大约需要 40秒

基准化load data infile语句:

before = Sys.time()
write.table(dump, 'the_dump.csv',
          row.names = F, col.names=F, sep='\t')
query = "LOAD DATA INFILE 'the_dump.csv' INTO TABLE test"
dbSendStatement(con, query)
time_infile = Sys.time() - before

=>在我的计算机上，这大约需要 4秒

精心设计SQL查询以处理许多插入值是提高性能的最简单方法。过渡到LOAD DATA INFILE将导致最佳结果。可以在this page of mysql documentation中找到良好的性能提示。

关于mysql - 从R到SQL插入数据帧的有效方法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43881694/