Linux环境Python3基于pymssql拉取SQL大体积数据的性能优化咨询

核心认知前置 #

你当前测试的“fetchmany和fetchall性能无差异”是完全符合预期的——两种方式最终都要把全量结果攒成统一列表存入内存，拉取总数据量、内存开销、网络交互的总字节数完全一致，自然没有性能差。真正的耗时瓶颈从来不是选哪种读取方法，而是藏在SQL执行、客户端默认配置、冗余逻辑里的可优化点，按下面的步骤调整，3万行规模的拉取耗时通常能从6秒级压到1秒级以内。

第一类优化：数据库端SQL执行提效（占总优化收益的60%以上） #

• 先排查SQL本身的执行耗时：把你代码里生成的最终SQL直接拿到数据库客户端执行，如果SQL本身在服务端的执行耗时就占了5秒以上，在Python端调整任何拉取逻辑都是无效的。
  • 针对你的查询场景，优先优化索引：为dbo.R_TAG_HIST表建立覆盖索引，字段组合为(CREATE_TIME, TAG_CODE)，把查询涉及的UI_X, UI_Y, INSERT_TIME作为索引包含列。这种配置下，数据库会直接从索引页返回所有需要的结果，不需要回表扫描数据行，同时自带按TAG_CODE的排序规则，能直接消掉查询里ORDER BY TAG_CODE的临时排序开销，通常能砍掉70%以上的服务端查询时间。
  • 如果业务逻辑不强依赖按TAG_CODE的全局排序，直接删掉SQL里的ORDER BY TAG_CODE子句，在Python端拿到数据后再按需排序，比在数据库端做无索引支撑的内存排序效率高得多。
• 废弃当前的字符串拼SQL写法：
  现在的代码用字符串拼接时间范围、用字符串replace构造IN列表的方式，不仅有SQL注入风险，还会因为每次查询传的参数值不同，导致SQL Server无法复用执行计划，每次都要硬解析生成执行计划，带来额外的耗时。pymssql本身支持参数化传值，直接用占位符传值即可，不需要手工拼接字符串。
  • 原有的问题写法：

    msg += "AND TAG_CODE IN " + str(self.tag_ids).replace("[", "(").replace("]", ")")
    

  • 优化后的参数化写法：

    placeholders = ",".join(["%s"] * len(self.tag_ids))
    msg = f"SELECT TAG_CODE, UI_X, UI_Y, INSERT_TIME FROM dbo.R_TAG_HIST WHERE CREATE_TIME > %s AND CREATE_TIME < %s AND TAG_CODE IN ({placeholders})"
    # execute的时候直接把参数列表传进去，pymssql会自动做转义处理
    cursor.execute(msg, (self.start_time, self.end_time, *self.tag_ids))
    

第二类优化：pymssql客户端传输配置提效（占总优化收益的25%） #

• 调整TCP传输的数据包大小：
  pymssql基于TDS协议和SQL Server通信，默认的TDS数据包大小为4KB，大结果集传输时会产生大量小包交互，网络开销极高。在建立数据库连接时，直接指定更大的数据包大小（最大支持32KB），同时搭配最新版本的TDS协议，能显著降低网络传输开销：

  # 连接初始化时增加的配置
  self.conn = pymssql.connect(
      server="xxx",
      user="xxx",
      password="xxx",
      database="xxx",
      tds_version="7.4",  # 用最高兼容的TDS版本
      packet_size=32768    # 32KB的数据包大小
  )
  

• 调整cursor的批量读取大小：
  很多人不知道pymssql的DB-API规范默认cursor.arraysize=1，也就是哪怕你调用fetchall，内部也是逐行从连接缓冲区读数据，单这一项就会带来数倍的读取开销。正确的做法是在拿到cursor之后、执行SQL之前，先把arraysize设到合理值（通常设为5000-10000即可），不要等execute之后再设置：

  with self.conn.cursor() as cursor:
      cursor.arraysize = 10000  # 必须在execute之前设置
      cursor.execute(msg, params)
      # 后续调用fetchmany时，不传size参数，会自动用上面设的arraysize值
  

• 删掉读查询里冗余的commit逻辑：
  你现在的select查询代码里加了self.conn.commit()，这是完全多余的——commit是针对增删改等写操作的事务提交逻辑，对SELECT查询没有任何作用，反而会触发事务边界检查、多余的锁校验，带来不必要的开销，直接删掉这行代码即可，没有任何副作用。

第三类优化：流程层面的端到端耗时压缩 #

你现在写的fetchmany迭代器之所以和fetchall没有性能差，核心原因是你在拿到每批结果后，都要把结果append到统一的tables列表里等全量拉完再返回，整体流程是完全串行的：全量拉取数据 → 再开始处理数据。
如果你的后续业务逻辑需要逐行处理数据（比如清洗、导出文件、同步到其他存储），完全不需要等全量数据拉完再返回，直接把读取逻辑改成生成器，实现“边拉取边处理”：

• 每从数据库拉到一批数据，就直接把这批数据交给业务逻辑处理，不需要攒到统一列表里
• 这样处理第一批数据的时间，会和数据库拉取第二批数据的时间重叠，整体端到端的耗时能比串行模式降低30%-50%，同时内存占用会从“存全量结果”降到“只存单批1万行的结果”。

常见踩坑提醒：不要迷信pandas的read_sql，pandas底层本质也是用pymssql的cursor做拉取，只要上面的配置不改，pandas的拉取速度和你自己写的代码没有本质差异。

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