求助：ShinyApps.io上R向MariaDB高效导入10k级数据的优化方案

针对你在shinyapps.io上使用RMariaDB或MySQL ODBC驱动导入数据速度极慢的问题，以下是几个可行的优化方向：

1. 分批次参数化插入 #

默认的dbWriteTable在禁用load_data_local_infile后可能会逐行插入数据，导致大量网络往返。改用分批次参数化插入可以大幅减少交互次数：

library(RMariaDB)
library(dplyr)

# 假设df是用户上传后读取的tibble
batch_size <- 1000
batches <- split(df, ceiling(seq(nrow(df))/batch_size))

# 建立数据库连接
conn <- dbConnect(MariaDB(), 
                  host = "你的主机地址",
                  user = "用户名",
                  password = "密码",
                  dbname = "数据库名")

# 先创建空表（保留列结构）
dbWriteTable(conn, "目标表名", df[0,], overwrite = FALSE, append = FALSE)

# 循环插入每个批次
for (batch in batches) {
  # 生成参数化INSERT语句
  col_names <- paste(colnames(batch), collapse = ", ")
  placeholders <- paste(rep("?", ncol(batch)), collapse = ", ")
  batch_placeholders <- paste(rep(paste0("(", placeholders, ")"), nrow(batch)), collapse = ", ")
  insert_query <- sprintf("INSERT INTO 目标表名 (%s) VALUES %s", col_names, batch_placeholders)
  
  # 将批次数据转为参数列表传入
  params <- unname(as.list(batch))
  dbExecute(conn, insert_query, params = params)
}

dbDisconnect(conn)

参数化查询不仅提升速度，还能避免SQL注入风险。

2. 提前统一数据类型 #

驱动在自动转换数据类型时会产生额外开销，提前将tibble的列类型对齐数据库字段类型：

df <- df %>%
  mutate(
    # 将字符日期转为Date类型
    date_column = as.Date(date_column),
    # 因子转字符避免驱动额外处理
    factor_column = as.character(factor_column),
    # 确保数值类型匹配数据库
    numeric_column = as.numeric(numeric_column)
  )

3. 配置ODBC驱动的批量参数 #

如果你使用ODBC驱动连接，添加rows_at_time和batch_rows参数优化批量传输：

library(odbc)

conn <- dbConnect(odbc(),
                  Driver = "MySQL",
                  Server = "你的主机地址",
                  UID = "用户名",
                  PWD = "密码",
                  Database = "数据库名",
                  rows_at_time = 1000,  # 一次性处理1000行
                  batch_rows = 1000)

这两个参数让驱动批量处理数据，减少网络请求次数。

4. 开启数据传输压缩 #

开启连接压缩可以减少网络传输的数据量，RMariaDB直接在连接时添加compress = TRUE：

conn <- dbConnect(MariaDB(), 
                  host = "你的主机地址",
                  user = "用户名",
                  password = "密码",
                  dbname = "数据库名",
                  compress = TRUE)

ODBC驱动可添加Compression = 1参数（需对应驱动版本支持）。

5. 服务器端文件导入（若权限允许） #

如果你的MariaDB服务器支持将文件上传至可访问目录，可先把用户上传的CSV保存到服务器临时路径，再用LOAD DATA INFILE导入（比客户端插入快得多）：

# 假设已将CSV上传到数据库服务器的/tmp目录
dbExecute(conn, "
  LOAD DATA INFILE '/tmp/user_upload.csv'
  INTO TABLE 目标表名
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '\"'
  LINES TERMINATED BY '\\n'
  IGNORE 1 ROWS;
")

注意：需要确认数据库服务器是否允许该操作，以及你是否有对应的文件权限。

内容的提问来源于stack exchange，提问作者MattB