如何在RStudio中高效提取MySQL中1500万行数据且避免连接中断

断连的核心原因是单次拉取1500万行数据量过大，既超出了数据库连接的默认超时阈值、传输缓冲区上限，也会因为dbGetQuery默认一次性加载全量结果到内存，触发R或数据库侧的资源限制。可按以下优先级优化：

1. 分页批量拉取（最易实现，兼容现有逻辑） #

不用一次性加载全量数据，分批拉取每批次固定行数，既不会打爆连接也不会溢出R内存，示例代码如下：

library(RMariaDB)
library(data.table)

# 初始化连接时拉长超时阈值，避免查询过程中连接被主动掐断
con <- dbConnect(MariaDB(), 
                 dbname = "DWU",
                 host = "你的数据库主机地址",
                 user = "账号",
                 password = "密码",
                 connect_timeout = 600, # 连接超时设为10分钟
                 read_timeout = 600) # 读取超时设为10分钟

# 每批次拉取行数，可根据本机内存调整，16G内存可设为20万-50万
batch_size <- 100000
res <- dbSendQuery(con, queryStatus)
user_status <- data.table()

while(!dbHasCompleted(res)){
  batch <- data.table(fetch(res, n = batch_size))
  # 内存不足时可直接追加到本地文件，不用全量存在R内存中
  # fwrite(batch, "user_status.csv", append = TRUE, col.names = !file.exists("user_status.csv"))
  user_status <- rbind(user_status, batch)
}

# 清理结果、关闭连接
dbClearResult(res)
dbDisconnect(con)

2. 数据库侧查询优化（大幅降低查询耗时，从根源减少断连概率） #

• 给关联键、过滤键加索引：给TB_SJT_USUARIO的ID_USRO、ID_STTS_USRO加联合索引，给TB_ATR_STATUS_USUARIO的ID_STTS_USRO加索引，避免全表扫描，查询速度可提升数倍到数十倍。
• 优化IN查询逻辑：如果Id_user_aviso的ID数量超过1000个，不要直接拼接成IN语句，建议先把ID列表写入数据库临时表，用JOIN临时表的方式替代IN查询，性能更高也不会触发IN长度超限的错误，示例如下：

# 写入临时表
dbWriteTable(con, "temp_user_ids", data.frame(ID_USRO = Id_user_aviso), temporary = TRUE)
# 改写查询
queryStatus <- "
select ID_SJT_USRO,ID_USRO,DATE(DT_INI_VGNA) as data_status,SJT.ID_STTS_USRO,DS_STTS_USRO 
from DWU.TB_SJT_USUARIO AS SJT
LEFT JOIN DWU.TB_ATR_STATUS_USUARIO AS STATUS_USER
ON SJT.ID_STTS_USRO = STATUS_USER.ID_STTS_USRO
INNER JOIN temp_user_ids t ON SJT.ID_USRO = t.ID_USRO
;"

• 裁剪冗余字段：确认SELECT列表里的字段都是业务必需的，去掉不需要的字段减少数据传输量。

3. 最高效方案：数据库直接导出再读入R #

如果有数据库服务器的文件导出权限，直接在MySQL侧执行SELECT INTO OUTFILE把查询结果导出为CSV文件，再用data.table::fread读入R，传输速度比通过数据库连接拉取快3-10倍，完全不会出现断连问题。

内容的提问来源于stack exchange，提问作者Vinicius Jose Caldeira