MySQL分组取最新数据查询性能远逊于SQLite求助排查

问题概况 #

• 运行环境：Python 3.6、WSL2
• 数据库版本：SQLite 3.36；MySQL 8.0.32-0ubuntu0.20.04.2
• 耗时对比：同一分组取最新数据的查询，SQLite仅需11毫秒，MySQL耗时8.5秒，差距显著
• 现有索引配置：
  • 所有表均设(id, transaction_id)为主键
  • 单独创建operation_type索引
  • 单独创建transaction_id索引
  • 单独创建workspace_id索引
• 自动生成的查询语句（SQLAlchemy生成）：

SELECT 
  model_version_trace.workspace_id AS workspace_id, 
  CAST('2023-03-12 09:49:57.338362' AS DATETIME) AS computed_at, 
  parent_trace.name AS name, 
  feature_trace_1.alias AS alias, 
  platform_entity_trace.name AS platform_entity, 
  CASE WHEN (model_version_trace.version = max_version_query.max_version) THEN true ELSE false END AS is_latest_version, 
  model_version_trace.version AS version, 
  model_version_trace.id AS id 
FROM 
  model_version_trace 
  JOIN (
    SELECT 
      max(model_version_trace.transaction_id) AS max_transaction_id, 
      model_version_trace.id AS id 
    FROM 
      model_version_trace 
    WHERE 
      model_version_trace.transaction_id <= 500 
    GROUP BY 
      model_version_trace.id
  ) AS entity_trace_table_max_subquery ON entity_trace_table_max_subquery.id = model_version_trace.id 
      AND entity_trace_table_max_subquery.max_transaction_id = model_version_trace.transaction_id 
  LEFT OUTER JOIN (
    SELECT 
      model_version_output_feature_version_trace.feature_version_id AS feature_version_id, 
      model_version_output_feature_version_trace.model_version_id AS model_version_id, 
      model_version_output_feature_version_trace.operation_type AS operation_type, 
      model_version_output_feature_version_trace.transaction_id AS transaction_id 
    FROM 
      model_version_output_feature_version_trace 
      JOIN (
        SELECT 
          model_version_output_feature_version_trace.model_version_id AS model_version_id, 
          max(model_version_output_feature_version_trace.transaction_id) AS max_transaction_id 
        FROM 
          model_version_output_feature_version_trace 
        WHERE 
          model_version_output_feature_version_trace.transaction_id <= 500 
        GROUP BY 
          model_version_output_feature_version_trace.model_version_id
      ) AS model_output_feature_trace_max ON model_output_feature_trace_max.model_version_id = model_version_output_feature_version_trace.model_version_id 
          AND model_version_output_feature_version_trace.transaction_id = model_output_feature_trace_max.max_transaction_id 
    WHERE 
      model_version_output_feature_version_trace.operation_type IN (0, 1)
  ) AS model_output_feature_trace ON model_version_trace.id = model_output_feature_trace.model_version_id 
  LEFT OUTER JOIN (
    SELECT 
      feature_version_trace.id AS id, 
      feature_version_trace.parent_id AS parent_id, 
      feature_version_trace.operation_type AS operation_type, 
      feature_version_trace.transaction_id AS transaction_id 
    FROM 
      feature_version_trace 
      JOIN (
        SELECT 
          feature_version_trace.id AS id, 
          max(feature_version_trace.transaction_id) AS max_transaction_id 
        FROM 
          feature_version_trace 
        WHERE 
          feature_version_trace.transaction_id <= 500 
        GROUP BY 
          feature_version_trace.id
      ) AS feature_version_trace_max ON feature_version_trace_max.id = feature_version_trace.id 
          AND feature_version_trace.transaction_id = feature_version_trace_max.max_transaction_id 
    WHERE 
      feature_version_trace.operation_type IN (0, 1)
  ) AS feature_version_trace_1 ON feature_version_trace_1.id = model_output_feature_trace.feature_version_id 
  LEFT OUTER JOIN (
    SELECT 
      feature_trace.id AS id, 
      feature_trace.alias AS alias, 
      feature_trace.platform_entity_id AS platform_entity_id, 
      feature_trace.operation_type AS operation_type, 
      feature_trace.transaction_id AS transaction_id 
    FROM 
      feature_trace 
      JOIN (
        SELECT 
          feature_trace.id AS id, 
          max(feature_trace.transaction_id) AS max_transaction_id 
        FROM 
          feature_trace 
        WHERE 
          feature_trace.transaction_id <= 500 
        GROUP BY 
          feature_trace.id
      ) AS feature_trace_max ON feature_trace_max.id = feature_trace.id 
          AND feature_trace.transaction_id = feature_trace_max.max_transaction_id 
    WHERE 
      feature_trace.operation_type IN (0, 1)
  ) AS feature_trace_1 ON feature_version_trace_1.parent_id = feature_trace_1.id 
  JOIN (
    SELECT 
      platform_entity_trace.id AS id, 
      platform_entity_trace.name AS name, 
      platform_entity_trace.operation_type AS operation_type, 
      platform_entity_trace.transaction_id AS transaction_id 
    FROM 
      platform_entity_trace 
      JOIN (
        SELECT 
          platform_entity_trace.id AS id, 
          max(platform_entity_trace.transaction_id) AS max_transaction_id 
        FROM 
          platform_entity_trace 
        WHERE 
          platform_entity_trace.transaction_id <= 500 
        GROUP BY 
          platform_entity_trace.id
      ) AS platform_entity_trace_max ON platform_entity_trace_max.id = platform_entity_trace.id 
          AND platform_entity_trace.transaction_id = platform_entity_trace_max.max_transaction_id 
    WHERE 
      platform_entity_trace.operation_type IN (0, 1)
  ) AS platform_entity_trace ON platform_entity_trace.id = feature_trace_1.platform_entity_id 
  JOIN (
    SELECT 
      model_version_trace.parent_id AS parent_id, 
      max(model_version_trace.version) AS max_version 
    FROM 
      model_version_trace 
    GROUP BY 
      model_version_trace.parent_id
  ) AS max_version_query ON model_version_trace.parent_id = max_version_query.parent_id 
  JOIN (
    SELECT 
      model_trace.id AS id, 
      model_trace.algorithm_type_id AS algorithm_type_id, 
      model_trace.name AS name, 
      model_trace.model_output_type AS model_output_type, 
      model_trace.operation_type AS operation_type, 
      model_trace.transaction_id AS transaction_id 
    FROM 
      model_trace 
      JOIN (
        SELECT 
          model_trace.id AS id, 
          max(model_trace.transaction_id) AS max_transaction_id 
        FROM 
          model_trace 
        WHERE 
          model_trace.transaction_id <= 500 
        GROUP BY 
          model_trace.id
      ) AS parent_trace_max ON parent_trace_max.id = model_trace.id 
          AND model_trace.transaction_id = parent_trace_max.max_transaction_id 
    WHERE 
      model_trace.operation_type IN (0, 1)
  ) AS parent_trace ON parent_trace.id = model_version_trace.parent_id 
ORDER BY 
  id 
LIMIT 
  20 OFFSET 0

核心原因分析 #

1. 查询优化器逻辑差异：SQLite和MySQL的优化器对嵌套分组+关联的查询处理逻辑不同，MySQL未能高效利用现有索引，导致大量表扫描或回表操作。
2. 索引适配性不足：现有单独索引无法覆盖分组+过滤+排序的复合场景，比如按id分组取最大transaction_id时，单独的transaction_id索引无法直接满足，而主键索引的顺序可能未被优化器充分利用。
3. 多表关联开销大：查询包含多层JOIN/LEFT JOIN，MySQL处理时生成的中间结果集过大，触发临时表或文件排序，大幅增加耗时。
4. LIMIT生效时机晚：MySQL可能在完成所有关联、排序后才应用LIMIT 20，而SQLite可提前截断计算，减少不必要的运算。

针对性优化方案 #

1. 用窗口函数重构分组取数逻辑 #

将原GROUP BY + JOIN的嵌套模式替换为ROW_NUMBER()窗口函数，简化查询结构，让优化器更好地规划执行计划。以model_version_trace的子查询为例：
原逻辑：

SELECT 
  max(model_version_trace.transaction_id) AS max_transaction_id, 
  model_version_trace.id AS id 
FROM model_version_trace 
WHERE model_version_trace.transaction_id <= 500 
GROUP BY model_version_trace.id

优化后：

SELECT id, transaction_id
FROM (
  SELECT 
    id, 
    transaction_id,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY transaction_id DESC) AS rn
  FROM model_version_trace
  WHERE transaction_id <= 500
) t
WHERE rn = 1

所有类似子查询都可按此方式重构，减少嵌套层级，提升执行效率。

2. 创建复合索引适配查询场景 #

针对每个表的分组+过滤需求，创建覆盖查询字段的复合索引：

• 对于按id分组取最大transaction_id的场景，创建(id, transaction_id DESC)联合索引（或利用主键索引，若主键已包含这两个字段，可尝试强制使用主键索引）
• 对于关联+过滤的表，比如model_version_output_feature_version_trace，创建(model_version_id, transaction_id DESC, operation_type)复合索引，覆盖WHERE过滤、分组取最大和关联字段，避免回表。

示例创建语句：

CREATE INDEX idx_mvfv_model_version_trans_op ON model_version_output_feature_version_trace(model_version_id, transaction_id DESC, operation_type);
CREATE INDEX idx_fvt_id_trans_op ON feature_version_trace(id, transaction_id DESC, operation_type);

3. 强制优化器使用指定索引 #

若优化器未自动选择最优索引，可在查询中用FORCE INDEX指定索引，例如：

SELECT 
  max(model_version_trace.transaction_id) AS max_transaction_id, 
  model_version_trace.id AS id 
FROM model_version_trace FORCE INDEX (PRIMARY)
WHERE model_version_trace.transaction_id <= 500 
GROUP BY model_version_trace.id

利用主键索引的有序性，直接按id分组获取最大transaction_id。

4. 提前过滤数据缩小结果集 #

在每个子查询中优先执行transaction_id <=500和operation_type IN (0,1)的过滤，确保中间结果集最小化。同时，可将部分LEFT JOIN改为子查询，减少多表关联的开销。

5. 分析执行计划定位瓶颈 #

执行EXPLAIN ANALYZE查看MySQL执行计划，重点关注：

• 是否存在全表扫描（type: ALL）
• 是否使用临时表（Extra: Using temporary）
• 是否触发文件排序（Extra: Using filesort）
  根据结果针对性调整索引或查询逻辑。

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