从SQL查询加载40万条数据至Dataframe耗时过长，求优化方案

1. 修复分块读取的逻辑问题 #

当前代码默认chunk_size=10000000（1千万），远大于你的40万条数据量，导致read_sql不会分块，直接一次性读取；同时逐次concat生成新DataFrame的方式，会产生大量内存复制开销，拖慢速度。

优化后的读取逻辑：

def read_data(self, query, chunk_size=10000, auto_close_connection=False):
    """
    Retrieves the query result from the database into a dataframe. Returns an empty dataframe if no records found.

    :param query: The query string to fire to the database
    :param chunksize: Number of records to be fetched at a time from the database.
    :param auto_close_connection: Boolean value to specify whether or not to close the engine immediately after the retriving the data. Default value is False. If kept as False, make sure to call close_engine() after use.
    :return: Dataframe containing the entire resultset of the query
    """
    chunks = []
    try:
        print("Started reading data from DB...")
        # 用列表收集所有chunk，避免多次concat的内存开销
        for chunk in pan.read_sql(query, self.engine, chunksize=chunk_size):
            chunks.append(chunk)
            print(f"Read chunk: {len(chunks)} (total rows so far: {sum(len(c) for c in chunks)})")
        
        if not chunks:  # 处理空结果场景
            data_frame = pan.read_sql(query, self.engine)
        else:
            # 一次性合并所有chunk，效率远高于逐次拼接
            data_frame = pan.concat(chunks, axis=0, ignore_index=True)
        
        print("Finished reading data from DB")
        return data_frame
    finally:
        if auto_close_connection:
            self.close_engine()

• 调整chunk_size到合理值（比如1万-5万），根据本地内存情况选择，避免单块数据过大。
• 用列表批量收集chunk后一次性合并，彻底消除逐次拼接的内存复制损耗。

2. 优化数据库查询本身 #

读取慢的核心往往不在Python代码，而是数据库端的查询效率：

• 先在数据库客户端（如MySQL Workbench、pgAdmin）执行目标SQL，查看原生执行时间。如果数据库查询本身就耗时十几分钟，必须优先优化SQL：
  • 只查询需要的字段，去掉不必要的SELECT *，减少数据传输量。
  • 检查过滤、排序字段是否有索引，添加合适的索引可大幅提升数据库查询速度。
  • 若涉及多表关联，检查关联条件是否合理，避免产生笛卡尔积或全表扫描。

3. 优化数据库连接配置 #

• 使用高效驱动：PostgreSQL优先用psycopg2-binary，MySQL用mysql-connector-python或pymysql（注意与pandas版本兼容）。
• 调整SQLAlchemy引擎参数，提升连接效率：

  from sqlalchemy import create_engine
  engine = create_engine(
      "postgresql+psycopg2://user:pass@host/db",
      pool_size=10,
      max_overflow=20,
      connect_args={"options": "-c statement_timeout=300000"}  # 设置查询超时，避免无意义阻塞
  )
  

4. 其他细节优化 #

• 指定数据类型：读取时通过dtype参数明确字段类型，避免pandas自动推断的开销：

  pan.read_sql(query, self.engine, chunksize=chunk_size, dtype={
      "performance_id": int,
      "category": "category"
  })
  

• 升级pandas版本：老版本read_sql效率较低，升级到2.x以上的稳定版可获得明显性能提升。
• 简化fillna操作：如果后续逻辑不需要将NaN转为空字符串，直接去掉data_df.fillna("", inplace=True)；若必须处理，可在分块读取时逐块处理，避免一次性操作大DataFrame。

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