我试图在mysql数据库上运行一个查询，这似乎需要很多时间，当我在sqlite中运行相同的查询时，所需的时间非常少。
环境-〉Python36，WSL 2 Sqlite版本-〉3.36 Mysql版本-〉8.0.32-0 ubuntu0.20.04.2
SQLITE：11毫秒MYSQL：8.5秒（与11毫秒相比非常大）
我在每个表上都有以下索引：primary key约束on（id，transaction_id）for each of the table. index on operation type index on transaction id index on workspace id
注：查询由SQLALCHEMY生成
我也有在mysql中解释这个查询的结果。

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