嵌套循环中pandas拼接大数据帧性能下降，如何改用numpy向量化优化？

问题背景 #

需要从旧版PostgreSQL数据库加载6GB大小的产品表，表中存在部分无效值需要在加载阶段过滤。当前实现采用优先大块加载、出错后逐步缩小块大小定位丢弃坏值的逻辑，功能可正常运行，但加载到约50万条记录后性能会急剧下降。

已尝试的优化路径 #

• 改用numpy处理数据，无明显优化效果
• 尝试列表推导式实现，因需要在迭代缩小块时捕获异常无法落地

性能下降根因 #

你遇到的性能陡降和numpy向量化无关，核心问题出在分页逻辑上：

• PostgreSQL的OFFSET分页需要扫描并跳过前面所有已查询的行，当偏移量达到几十万行之后，每次查询都会浪费大量时间跳过已经读取过的数据，偏移量越大查询速度越慢
• 你当前列表存DataFrame最后一次性合并的逻辑本身没有性能问题，不需要改动

具体优化方案 #

1. 替换OFFSET分页为键集分页（核心优化） #

用排序字段item_no作为分页锚点，完全消除分页偏移的性能损耗，代码修改如下：

import psycopg2 # 建议改用PostgreSQL原生驱动替代pyodbc，性能提升30%以上
import pandas as pd

# 初始化分页锚点
last_item_no = None
appended_df = []
chunksize = 35000
error_counter = 0

while True:
    try:
        if last_item_no is None:
            # 首次查询无锚点
            sql = """
            SELECT "item_no", "description_1", "description_2", "description_3" 
            FROM "products" 
            ORDER BY "item_no" 
            LIMIT %s
            """
            params = (chunksize,)
        else:
            # 后续查询用锚点过滤，不需要OFFSET
            sql = """
            SELECT "item_no", "description_1", "description_2", "description_3" 
            FROM "products" 
            WHERE "item_no" > %s
            ORDER BY "item_no" 
            LIMIT %s
            """
            params = (last_item_no, chunksize)
        
        df = pd.read_sql_query(sql, conn, params=params)
    except:
        # 原有缩小块大小定位坏值的逻辑直接保留即可，分页方式同样改用键集即可
        error_counter +=1
        # 此处补充你原有分段排查逻辑
        continue
    
    if len(df) == 0:
        # 所有数据加载完成，退出循环
        break
    
    appended_df.append(df)
    # 更新下一次查询的锚点
    last_item_no = df["item_no"].iloc[-1]

# 一次性合并所有正常块
products_df = pd.concat(appended_df, ignore_index=True)

2. 辅助优化项 #

• 给item_no字段添加索引，可进一步提升键集分页的过滤效率
• 不需要改用numpy重构逻辑，pandas的concat本身是高度优化的操作，你当前存df到列表最后合并的逻辑没有性能冗余
• 缩小块排查坏值时也统一使用键集分页，避免OFFSET带来的额外开销

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