从MySQL向R查询/导入数百万行数据的性能优化问询

以下是几个能显著缩短查询耗时的实用方法：

• 只查询需要的列，避免SELECT *
  你的表有60列，但业务场景大概率不需要全部字段。把SELECT *替换成明确需要的列名，比如SELECT transaction_id, amount, customer_id FROM sales where transaction_year = 2022，这样能大幅减少需要传输的数据量，直接降低IO和网络耗时。

• 创建覆盖索引
  现有transaction_year索引只能帮MySQL快速定位行，但还需要回表读取全列数据。可以创建包含常用查询列的复合索引，让MySQL直接从索引中获取所有需要的数据，无需回表：

  CREATE INDEX idx_year_covering ON sales(transaction_year, col1, col2, col3);
  

  把col1, col2替换成你实际需要的列名，索引列不要过多，避免索引过大影响写入性能。

• 调整MySQL核心配置

  • 增大innodb_buffer_pool_size：如果是本地数据库，把这个参数设为8G（你的内存是16G，分配一半给缓冲池），让更多数据缓存到内存，减少磁盘读取次数。修改my.cnf/my.ini后重启服务生效。
  • 调整max_allowed_packet：设为64M或更大，避免大数据量传输时出现截断或性能瓶颈。

• 优化R端的数据库驱动和读取方式

  • 替换RMySQL为RMariaDB驱动：RMariaDB是RMySQL的替代项目，性能更优且维护更活跃，连接方式类似，只需替换包名即可。
  • 启用连接压缩：在建立数据库连接时添加compress=TRUE参数，压缩传输的数据，减少网络开销（即使本地连接也能降低IO负载）：

    library(RMariaDB)
    conn <- dbConnect(MariaDB(), host="localhost", user="your_user", password="your_pwd", dbname="your_db", compress=TRUE)
    

• 使用分区表
  针对transaction_year字段对表进行分区，让MySQL查询时只扫描目标年份的分区，而非全表索引：

  ALTER TABLE sales PARTITION BY RANGE (transaction_year) (
      PARTITION p2022 VALUES LESS THAN (2023),
      PARTITION p2023 VALUES LESS THAN (2024),
      -- 按需添加其他年份分区
      PARTITION p_future VALUES LESS THAN MAXVALUE
  );
  

  分区后查询2022年数据时，MySQL会直接定位到p2022分区，扫描范围大幅缩小，性能提升明显。

• 批量读取与分段处理
  在R中使用dbFetch()分批读取数据，避免一次性加载大量数据到内存导致的卡顿：

  res <- dbSendQuery(conn, "SELECT col1, col2 FROM sales WHERE transaction_year = 2022")
  chunks <- list()
  while (!dbHasCompleted(res)) {
      chunks <- c(chunks, list(dbFetch(res, n=100000)))
  }
  data <- do.call(rbind, chunks)
  dbClearResult(res)
  

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