Python导出60GB大型SQL表数据为CSV备份的提速方案

你当前的实现慢主要来自三个可优化的点：

• 单批次拉取量仅1000条，和数据库的网络交互次数过多，60G数据按单条记录1KB计算就要产生6万次请求往返，无效开销占比极高
• 每批数据先转pandas DataFrame再序列化写CSV，pandas的结构化转换、CSV序列化逻辑比Python原生实现重很多，属于没必要的中间层开销
• 先写全量未压缩CSV再单独压缩，会多产生一次60G文件的读+写磁盘IO，额外占用大量时间

方案1：用SQL Server原生bcp工具（首选，速度比Python方案快5~10倍） #

大表导出优先用数据库自带的批量导出工具，完全绕开应用层的数据转换开销，直接在数据库侧读取数据写入目标，还支持通过管道边导出边压缩，没有中间文件落地，通常60G数据10~30分钟就能跑完。
直接在命令行执行以下命令即可：

:: 第一步：先导出CSV表头
bcp "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='mydata' ORDER BY ORDINAL_POSITION" queryout output.csv -S 你的数据库实例地址 -d 目标库名 -U 登录用户名 -P 登录密码 -c -t, -r\n

:: 第二步：全量导出数据，通过管道直接传给7z压缩，不生成中间未压缩文件
bcp "select * from mydata" queryout nul -S 你的数据库实例地址 -d 目标库名 -U 登录用户名 -P 登录密码 -c -t, -r\n | 7z a -si output.csv.7z

如果你用Windows系统身份验证登录数据库，把上面命令里的-U 登录用户名 -P 登录密码替换成-T即可。

方案2：必须用Python时的代码优化 #

如果受环境限制不能调用bcp，针对现有代码的瓶颈点改造后，速度可以提升3~4倍：

• 把单批次拉取量从1000调到20000~50000，大幅减少数据库交互次数
• 去掉pandas中间层，用Python原生csv模块做序列化，写入速度是pandas的2~3倍
• 直接写入压缩文件，跳过“写全量CSV再读CSV压缩”的重复IO
• 先查询表结构写表头，再批量写入数据
  优化后的可直接运行代码：

import pyodbc
import csv
import gzip

# 数据库连接串，建议用ODBC Driver 17/18版本，拉取批量数据性能更好
conn_str = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=你的实例地址;DATABASE=目标库名;UID=用户名;PWD=密码"
connection = pyodbc.connect(conn_str)
cursor = connection.cursor()

# 先获取表头字段
cursor.execute("select top 0 * from mydata")
headers = [col_desc[0] for col_desc in cursor.description]

BATCH_SIZE = 30000  # 可根据机器内存调整，2万~10万区间性能最优
# 直接打开gzip压缩流写入，不需要落地中间csv文件
with gzip.open("output.csv.gz", "wt", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(headers)
    # 执行全量查询
    cursor.execute("select * from mydata")
    while True:
        rows = cursor.fetchmany(BATCH_SIZE)
        if not rows:
            break
        writer.writerows(rows)

# 资源回收
cursor.close()
connection.close()

• 不要用pandas的read_sql(chunksize=xxx)接口，本质和你原来的实现逻辑一致，仍然存在DataFrame转换的额外开销，性能提升有限
• 不要把批次大小设到10万以上，单次拉取数据占用内存过高会触发系统内存交换，反而会拖慢整体速度
• 不要导出完成后再单独做压缩，边写边压是大文件处理的常规优化，能省掉一半以上的磁盘IO耗时

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