如何加速从SQL数据库提取百万级数据并写入TSV文件的过程？

我的SQL数据库中存储有超过100万行数据，需要将全部数据写入TSV文件。当前我使用如下代码提取前100000行数据，整个提取并写入TSV文件的过程耗时近20分钟，请问是否存在其他可加速该过程的方法？

cursor.execute("select top(100000) from dbo.StoreLocations_Repo_V10")
store_details = cursor.fetchall()
store_details_list = [list(elem) for elem in store_details]
df = pd.DataFrame(store_details_list)
with open('result.tsv', 'w', encoding='UTF-8') as f:
    df.to_csv(f, header=False, sep='\t')

嘿，我来帮你解决这个大规模数据导出慢的问题！你现在的代码把10万行数据全加载到内存再写入，确实会很慢，尤其是要处理百万级数据的时候。下面几个方法亲测有效，能大幅提速：

这是提速最明显的方法——跳过Python中间层，直接用数据库自带的命令行工具导出。比如如果你用的是SQL Server，bcp工具就是专门干这个的，速度比Python代码快好几倍。示例命令：

bcp dbo.StoreLocations_Repo_V10 out result.tsv -S 你的服务器名 -d 你的数据库名 -U 用户名 -P 密码 -c -t\t -r\n

参数说明：

• -c：用字符模式导出，避免复杂的格式转换
• -t\t：指定分隔符为制表符（TSV格式）
• -r\n：指定行结束符为换行符

你的代码用fetchall()一次性把所有数据拉到内存，不仅占内存，还慢。改成分批读取，每次拉一部分数据就写入文件，内存压力小，速度也快很多。示例代码：

# 假设你用的是pyodbc连接
chunk_size = 10000  # 每次读取1万行，可根据内存调整
cursor.execute("SELECT * FROM dbo.StoreLocations_Repo_V10")

with open('result.tsv', 'w', encoding='UTF-8') as f:
    # 不需要表头的话可以删掉下面两行
    # headers = [desc[0] for desc in cursor.description]
    # f.write('\t'.join(headers) + '\n')
    
    while True:
        rows = cursor.fetchmany(chunk_size)
        if not rows:
            break
        # 把每行转成制表符分隔的字符串
        lines = ['\t'.join(map(str, row)) + '\n' for row in rows]
        f.writelines(lines)

这种方式不用转成DataFrame，减少了Pandas的额外开销，速度会快不少。

如果还是想用Pandas处理，别自己手动fetchall，用pd.read_sql的chunksize参数自动分批，Pandas底层做了优化，比你自己写循环高效：

import pandas as pd
from sqlalchemy import create_engine

# 用SQLAlchemy创建连接，比原生cursor更高效
engine = create_engine('mssql+pyodbc://用户名:密码@服务器名/数据库名?driver=ODBC+Driver+17+for+SQL+Server')

chunk_size = 100000
with open('result.tsv', 'w', encoding='UTF-8') as f:
    first_write = True
    for chunk in pd.read_sql("SELECT * FROM dbo.StoreLocations_Repo_V10", engine, chunksize=chunk_size):
        # 只有第一次写入写表头，后续追加不写
        chunk.to_csv(f, sep='\t', header=first_write, index=False)
        first_write = False

• 避免远程传输：如果数据库服务器和你写文件的机器不是同一台，尽量在数据库服务器本地导出，网络传输会大幅拖慢速度。
• 临时禁用索引：如果导出时不需要查询索引，可以临时禁用表上的非聚集索引（导出后记得恢复），减少数据库查询的负担。
• 加大文件缓冲区：写入文件时用buffering参数设置更大的缓冲区，比如open('result.tsv', 'w', encoding='UTF-8', buffering=1024*1024)，减少磁盘IO次数。

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