Python Django批量更新性能远逊于Java Spring Boot的原因排查求助

将 Java Spring Boot 实现的功能迁移至 Python Django 时，遇到批量更新性能瓶颈：更新450行数据，Java 仅需约0.1秒，Python 耗时2秒。两者连接同一数据库，SELECT 查询速度一致，仅 UPDATE 操作存在显著耗时差异。

已尝试以下优化方案，均无明显效果：

• 切换数据库驱动：从 pymysql 改为 mysqlclient
• 修改 settings.py 中的数据库配置选项
• 将 executemany 替换为 execute
• 安装 mariadb 库

计时方法 #

Java 计时代码 #

long startTime = System.nanoTime();
// Execute the update
long endTime = System.nanoTime();
double duration = (endTime - startTime) / 1_000_000_000.0;;
String formattedDuration = String.format("%.2f seconds", duration);
log.info("processing_time: {}", formattedDuration);

Python 计时代码 #

import time
import logging

logger = logging.getLogger(__name__)

start_time = time.perf_counter()
# Execute the update
end_time = time.perf_counter()
processing_time = end_time - start_time
processing_time_formatted = f"{processing_time:.2f}"
logger.info(f"processing_time: {processing_time_formatted} seconds")

核心逻辑代码 #

Java 部分 #

application.yml 配置

spring:
  datasource:
    driver-class-name: org.mariadb.jdbc.Driver
    url: jdbc:mariadb:...
    username: ...
    password: ...
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        show_sql: true
        use_sql_comments: true
        format_sql: true
        dialect : org.hibernate.dialect.MariaDBDialect
    open-in-view: false
  sql:
    init:
      mode: never

Service 代码

public void updateUserIntrKwd(String indvNum) {
    // 2s
    List<IntrKwdDto> kwdList = intrKwdMapper.selectIntrKwdList(indvNum);
    // 0.001s
    DatesAndMaxFrequency datesAndMaxFrequency = getDatesAndMaxFrequency(kwdList);
    // 0.1s
    intrKwdMapper.updateUserIntrKwd(kwdList, datesAndMaxFrequency);
}

Mapper XML 代码

<update id="updateUserIntrKwd" parameterType="map">
    <foreach collection="kwdList" item="item" separator=";">
        UPDATE your_table 
        SET col1 = #{item.val1}, col2 = #{item.val2}
        WHERE id = #{item.id}
    </foreach>
</update>

Python 部分 #

settings.py 数据库配置

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "...",
        "USER": "...",
        "PASSWORD": "...",
        "HOST": "...",
        "PORT": 3306,
    },
}

Service 代码

def update_user_interest_keywords(indv_num: str) -> int:
    # 2s
    user_kwd_list = select_interest_keywords_list(indv_num)
    # 0.001s
    dates_and_max_frequency = get_dates_and_max_frequency(user_kwd_list)
    # 2s
    updated_rows = execute_user_interest_keywords_query(user_kwd_list, dates_and_max_frequency)

    return updated_rows

查询执行代码

from django.db import connections

def execute_user_interest_keywords_query(userKwdList: list[IntrKwd], datesAndMaxFrequency: DatesAndMaxFrequency):
    base_query = """
        UPDATE your_table 
        SET last_aper_yrmo = %s, ... 
        WHERE id = %s
    """

    def get_query_params(item):
        return (
            item.last_aper_yrmo,
            # 其他参数
            item.id
        )

    params = [get_query_params(item) for item in userKwdList]

    try:
        with connections['default'].cursor() as cursor:
            cursor.executemany(base_query, params)
            updated_rows = cursor.rowcount
    except Exception as e:
        # 异常处理
        raise e
    return updated_rows

性能差异原因解析 #

两者核心差异在于批量更新的执行方式：

1. MyBatis 实现：通过 <foreach> 标签将450条 UPDATE 语句拼接成一条包含多个分号分隔的SQL，通过一次网络请求发送给数据库，数据库一次性执行所有语句，减少了网络往返开销。
2. Django/MySQL 驱动实现：默认情况下，executemany 并不会将所有语句合并为一条，而是逐条发送SQL并执行（即使使用参数化查询），450条更新就会产生450次网络往返，累积的延迟导致总耗时大幅增加。

优化方案 #

方案1：拼接单条批量更新SQL（模拟MyBatis方式） #

手动将多个UPDATE语句拼接成一条SQL，通过单次execute执行，减少网络往返：

def execute_user_interest_keywords_query(userKwdList: list[IntrKwd], datesAndMaxFrequency: DatesAndMaxFrequency):
    # 拼接多个UPDATE语句，用分号分隔
    update_statements = []
    params = []
    for item in userKwdList:
        update_statements.append("UPDATE your_table SET last_aper_yrmo = %s, ... WHERE id = %s")
        params.extend([item.last_aper_yrmo, ..., item.id])
    
    # 合并为单条SQL
    batch_query = "; ".join(update_statements)
    
    try:
        with connections['default'].cursor() as cursor:
            cursor.execute(batch_query, params)
            updated_rows = cursor.rowcount
    except Exception as e:
        raise e
    return updated_rows

注意：需确保数据库开启了allowMultiQueries配置（Django可在数据库URL中添加?allowMultiQueries=true）。

方案2：使用数据库批量更新语法 #

如果表有唯一键，可使用INSERT ... ON DUPLICATE KEY UPDATE语法，将更新转为批量插入+更新，只需一次SQL请求：

def execute_user_interest_keywords_query(userKwdList: list[IntrKwd], datesAndMaxFrequency: DatesAndMaxFrequency):
    # 构造批量插入的字段和值
    fields = ["id", "last_aper_yrmo", ...]
    values = []
    for item in userKwdList:
        values.append((item.id, item.last_aper_yrmo, ...))
    
    # 构造ON DUPLICATE KEY UPDATE语句
    update_clause = ", ".join([f"{field} = VALUES({field})" for field in fields if field != "id"])
    
    batch_query = f"""
        INSERT INTO your_table ({', '.join(fields)})
        VALUES ({', '.join(['%s']*len(fields))})
        ON DUPLICATE KEY UPDATE {update_clause}
    """
    
    try:
        with connections['default'].cursor() as cursor:
            cursor.executemany(batch_query, values)
            updated_rows = cursor.rowcount
    except Exception as e:
        raise e
    return updated_rows

这种方式效率更高，且避免了多语句SQL的安全风险。

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