aiomysql执行SQL查询缓慢但HeidiSQL中运行快速的问题咨询

我有一个使用aiomysql与MySQL数据库交互的Python应用程序。运行某条SQL查询时，该查询在HeidiSQL中执行速度很快，但通过应用程序执行时耗时明显更长。

相关代码 #

router_mysql.py #

COMMON_QUERY_PART = """
    SELECT
        sm.*,
        lm.className
    FROM
        data_{suffix} sm
    JOIN
        names_{suffix} lm ON sm.materialType = lm.id
"""

@router.get("/all/")
async def get_all_statistics(suffix: str):
    connection = await connect_to_mysql()
    async with connection.cursor() as cursor:
        query = COMMON_QUERY_PART.format(suffix=suffix)
        try:
            await cursor.execute(query)
            statistics = await cursor.fetchall()
        except Exception as e:
            connection.close()
            raise HTTPException(status_code=400, detail=str(e))
    connection.close()
    return {"statistics": statistics}

mysql.py #

import aiomysql
import os
from dotenv import load_dotenv

load_dotenv()

async def connect_to_mysql():
    connection = await aiomysql.connect(
        host=os.getenv("DB_HOST"),
        port=int(os.getenv("DB_PORT")),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        db=os.getenv("DB_DATABASE"),
        charset="utf8mb4",
        cursorclass=aiomysql.cursors.DictCursor
    )
    return connection

疑问 #

1. 导致HeidiSQL与aiomysql之间查询执行时间差异的原因可能是什么？
2. 使用aiomysql有哪些特定配置或最佳实践可以提升性能？
3. aiomysql的异步特性是否会导致性能下降？如果是，该如何缓解？

1. HeidiSQL与aiomysql查询耗时差异的原因 #

• 连接与会话差异：HeidiSQL通常复用已有连接，会话参数（如sql_mode、autocommit）可能与aiomysql默认配置不同，比如HeidiSQL可能启用了查询缓存（若MySQL版本支持），而aiomysql连接未开启，或字符集、排序规则设置不一致导致执行计划变化。
• 结果集处理逻辑不同：HeidiSQL多是分页加载结果，而代码中fetchall()会一次性拉取所有数据，若结果集过大，网络传输和内存处理的耗时会远超过HeidiSQL的部分加载。
• 执行环境负载差异：HeidiSQL执行时数据库负载可能更低，而应用程序执行时可能遇到连接池排队、应用服务器资源紧张的情况，拉长整体耗时。
• 执行计划复用差异：动态表名的拼接方式让MySQL无法复用执行计划，每次都要重新解析优化；另外若表的统计信息过时，HeidiSQL可能强制生成新计划，而aiomysql连接复用了旧的低效计划。

2. aiomysql性能优化的配置与最佳实践 #

• 使用连接池替代单次连接：当前每次请求新建连接的开销极大，改用连接池复用连接：

  # 应用启动时初始化一次连接池
  async def init_mysql_pool():
      return await aiomysql.create_pool(
          host=os.getenv("DB_HOST"),
          port=int(os.getenv("DB_PORT")),
          user=os.getenv("DB_USER"),
          password=os.getenv("DB_PASSWORD"),
          db=os.getenv("DB_DATABASE"),
          charset="utf8mb4",
          cursorclass=aiomysql.cursors.DictCursor,
          minsize=5,
          maxsize=20
      )
  
  # 路由中使用连接池
  async def get_all_statistics(suffix: str, pool):
      async with pool.acquire() as connection:
          async with connection.cursor() as cursor:
              query = COMMON_QUERY_PART.format(suffix=suffix)
              await cursor.execute(query)
              # 可选：分页拉取结果
              statistics = await cursor.fetchmany(size=100)
  

• 避免一次性拉取超大结果集：用fetchmany(size)分页拉取，或在SQL中添加LIMIT/OFFSET做分页返回，减少单次数据传输量。
• 优化SQL与表结构：确保data_{suffix}.materialType和names_{suffix}.id字段有索引，避免全表扫描；避免SELECT sm.*，只查询需要的字段，减少数据传输量。
• 调整连接参数：无需事务时开启autocommit=True，减少事务提交开销；根据并发量合理设置连接池的minsize和maxsize，避免连接数过多或过少。
• 更新表统计信息：定期执行ANALYZE TABLE data_{suffix}, names_{suffix}，帮助MySQL生成更优的执行计划。

3. 异步特性是否会导致性能下降？如何缓解？ #

aiomysql的异步特性本身不会导致性能下降，高并发场景下反而比同步驱动更有优势，但使用不当会出现问题：

• 误用同步阻塞操作：异步函数中若调用同步阻塞代码（如同步文件IO、CPU密集型计算），会阻塞事件循环，导致所有异步任务变慢。确保所有数据库操作都是异步调用，避免在异步路由中混入同步逻辑。
• 连接池配置不合理：maxsize过小会导致高并发下连接等待；过大则会增加数据库负载。需根据应用并发量和数据库最大连接数合理设置。
• 超大结果集阻塞事件循环：fetchall()处理大量数据时会占用大量内存和事件循环时间，改用分页拉取或流式处理（迭代cursor结果）可缓解。
• CPU密集型场景瓶颈：若应用以CPU密集型任务为主，异步IO的优势无法发挥，反而会因事件循环调度开销导致性能下降。这种情况可结合多进程，或把CPU密集型任务转移到单独服务中。

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