Pandas read_sql内存占用过高问题及优化方案咨询

问题描述 #

使用pd.read_sql读取SQL数据期间内存大幅飙升（峰值达3733.8 MiB），但最终生成的DataFrame及其拷贝的内存占用仅约300 MiB，远低于峰值。已尝试chunksize分批读取、调用gc.collect()，但内存仍维持在3000+ MiB，未解决问题。

代码片段 #

@profile
def pd_read_mssql_data(
    sql_query: str,
    server: str,
    database: str,
    server_uid: str,
    server_pwd: str,
):
    conn = f"DRIVER={'SQL server'};server={server};database={database};UID={server_uid};PWD={server_pwd}"
    quoted = quote_plus(conn)
    new_con = f"mssql+pyodbc:///?odbc_connect={quoted}"
    engine = create_engine(new_con)
    with engine.connect() as conn:
        df = pd.read_sql(sql=sql_text(sql_query), con=conn)

    df_copy = df.copy()  # The memory spike is observed here
    return df

内存分析结果 #

Line #    Mem usage    Increment  Occurrences   Line Contents
=============================================================
    13     98.2 MiB     98.2 MiB           1   @profile
    14                                         def pd_read_mssql_data(
    15                                             sql_query: str,
    16                                             server: str,
    17                                             database: str,
    18                                             server_uid: str,
    19                                             server_pwd: str,
    20                                         ):
    21     98.2 MiB      0.0 MiB           1       conn = f"DRIVER={'SQL server'};server={server};database={database};UID={server_uid};PWD={server_pwd}"
    22     98.2 MiB      0.0 MiB           1       quoted = quote_plus(conn)
    23     98.2 MiB      0.0 MiB           1       new_con = f"mssql+pyodbc:///?odbc_connect={quoted}"
    24     98.9 MiB      0.6 MiB           1       engine = create_engine(new_con)
    25   3733.8 MiB      4.3 MiB           2       with engine.connect() as conn:
    26   3733.8 MiB   3630.6 MiB           1           df = pd.read_sql(sql=sql_text(sql_query), con=conn)
    27
    28   4043.9 MiB    310.1 MiB           1       df_copy = df.copy()
    29
    30   4043.9 MiB      0.0 MiB           1       return df

环境版本 #

• SQLAlchemy==1.4.46
• urllib3==2.0.7
• pandas==2.0.2
• Python 3.10.9

已尝试方案 #

• 使用chunksize分批读取并合并临时DataFrame
• 手动调用gc.collect()回收内存
  以上方案均未有效降低内存占用。

问题解答 #

1. 为何pd.read_sql会导致内存大幅增长？ #

• 数据转换中间态开销：ODBC驱动会先将SQL数据以原生类型加载到内存，Pandas需要将其转换为Python/Pandas兼容类型（如SQL nvarchar转str、datetime转datetime64），转换过程中会同时存在原始数据和转换后数据两份副本，直接推高内存峰值。
• SQLAlchemy中间对象残留：SQLAlchemy 1.4.x版本在处理查询时，会生成结果集缓存、元数据对象等，这些对象在DataFrame生成后不会立即被垃圾回收（GC），持续占用内存。
• Pandas内部临时对象：read_sql构建DataFrame时会创建临时数组、字典等结构存储中间数据，这些对象的内存标记为可回收后，若GC未及时触发，会维持峰值内存。
• 默认类型转换冗余：Pandas自动推断数据类型时，可能将SQL数值类型转为Python原生int/float对象（而非Pandas的int64/float64数组），或文本类型转为Python字符串列表，这类转换会产生额外内存开销。

2. 如何优化Pandas读取SQL数据时的内存消耗？ #

• 指定数据类型减少转换开销
  预先通过dtype参数指定列的目标类型，避免自动推断产生的临时对象：

  dtype_config = {
      'id': 'int32',
      'category': 'category',
      'timestamp': 'datetime64[ns]',
      'value': 'float32'
  }
  df = pd.read_sql(sql=sql_text(sql_query), con=conn, dtype=dtype_config)
  

• 简化SQLAlchemy连接方式
  直接将engine传给pd.read_sql，避免手动管理连接产生的中间对象，同时建议升级SQLAlchemy到2.x版本（内存管理更优）：

  df = pd.read_sql(sql=sql_text(sql_query), con=engine)
  

• 优化分批读取逻辑
  使用chunksize时，对每个chunk及时做类型转换并触发GC，避免临时对象堆积：

  chunk_size = 100000
  chunks = []
  for chunk in pd.read_sql(sql=sql_text(sql_query), con=conn, chunksize=chunk_size):
      chunk = chunk.astype(dtype_config)
      chunks.append(chunk)
      import gc
      gc.collect()
  df = pd.concat(chunks, ignore_index=True)
  

• 直接使用pyodbc读取
  跳过SQLAlchemy中间层，用pyodbc直接执行查询并加载数据：

  import pyodbc
  conn = pyodbc.connect(f"DRIVER={'SQL server'};server={server};database={database};UID={server_uid};PWD={server_pwd}")
  cursor = conn.cursor()
  cursor.execute(sql_text(sql_query))
  columns = [desc[0] for desc in cursor.description]
  df = pd.DataFrame.from_records(cursor.fetchall(), columns=columns, dtype=dtype_config)
  cursor.close()
  conn.close()
  

• 优化SQL查询本身

  • 仅选择需要的列，避免读取冗余数据
  • 在SQL层做过滤（WHERE）、聚合，减少返回的数据量
  • 单独处理大文本/二进制字段，避免一次性加载

• 强制内存回收
  创建DataFrame后，显式删除中间对象并触发GC：

  with engine.connect() as conn:
      df = pd.read_sql(sql=sql_text(sql_query), con=conn)
  del conn, engine
  import gc
  gc.collect()
  

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