Pandas结合SQLAlchemy的to_sql向SQL Server插入千万级大表如何提速

现有代码问题排查 #

你当前的实现存在几个直接导致超时的核心问题：

• chunksize设置过小：你配置的chunksize=20意味着每次仅插入20行，5900万行需要执行近300万次数据库请求，必然会触发超时，同时极大拉长导入耗时。
• 提速参数冲突：method='multi'和fast_executemany=True不能同时使用，fast_executemany本身已经是pyodbc层面的批量参数优化，叠加method='multi'会生成超长的INSERT语句，反而抵消优化效果。
• 对fast_executemany的内存警告误解：官方警告仅针对单次全量上传的场景，只要你设置合理的分块大小，每次仅缓存单批次的行数据，完全不会出现内存占满的问题，可以正常使用该参数。

优化后的to_sql实现（快速生效方案） #

修改后的导入代码如下，可直接替换原有逻辑：

import pandas as pd
import pyodbc
import urllib
import sqlalchemy as sa
from tqdm import tqdm # 可选，用于展示导入进度

def sql_connect(account,password):
    params = urllib.parse.quote_plus("DRIVER=SQL Server;"
                                "SERVER=servername;"
                                "DATABASE=databasename;"
                                "UID={};"
                                "PWD={}".format(account,password))
    engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params),fast_executemany=True)
    return engine

def push_new_data(self,engine,df_to_push,formatDict, chunksize=20000):
    if df_to_push.empty:
        print('No new data was added')
        return
    
    # 手动分块配合进度条，避免pandas内置to_sql的隐式缓存
    total_chunks = (len(df_to_push) // chunksize) + 1
    for i, chunk in enumerate(tqdm(pd.np.array_split(df_to_push, total_chunks), desc="导入进度")):
        chunk.to_sql(
            name=self.table_name,
            con=engine,
            if_exists = 'append',
            index=False,
            dtype=formatDict
        )

参数调整说明：chunksize可根据本机内存调整，32G内存可设为30000_{50000，16G内存可设为10000}20000

千万级数据最优导入方案（性能提升5~10倍） #

to_sql走ORM层存在固定性能损耗，5900万行级别的数据更推荐使用SQL Server原生批量导入能力：

方案1：bcp工具导入（推荐，跨服务器也可用） #

1. 先将DataFrame导出为无表头、编码为UTF-8/GBK的CSV文件，注意字段分隔符不要和数据内容冲突
2. 调用SQL Server原生bcp命令行工具执行导入，示例命令：

bcp 数据库名.模式名.表名 in "C:\你的导出文件路径.csv" -S 服务器地址 -U 登录账号 -P 登录密码 -c -t "," -r "\n" -b 20000

参数说明：-b指定每批次导入行数，-t指定字段分隔符，-r指定行分隔符

方案2：BULK INSERT语句导入（适合Python和SQL Server同服务器场景） #

先将CSV文件存到SQL Server可访问的本地路径，然后执行SQL语句：

BULK INSERT 表名 
FROM 'C:\你的导出文件路径.csv' 
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK, -- 批量表锁，大幅提升写入速度
    BATCHSIZE = 20000
)

通用提速注意事项 #

• 导入前先删除目标表的非主键索引、触发器，导入完成后再重建，可提升30%以上导入速度，避免索引维护导致的超时
• 导入前临时将SQL Server数据库的事务日志模式调整为大容量日志模式，导入完成后改回完整模式，避免事务日志暴涨导致的写入卡顿
• 如果是首次导入全量数据，优先使用if_exists='replace'建表，不要逐行append到已有大表

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