我在mysql 5.5和mariadb 10.2中运行了以下查询。在mariadb中运行查询只花了4秒钟，而在mysql中则花了大约6分钟。

SELECT
  ' ' AS max_claim_amount,
  claims_only_A.*
FROM
  (
    SELECT
      employee.emp_number AS emp_number,
      ' ' AS emp_id,
      ' ' AS emp_name,
      NULL AS estimate_id,
      NULL AS estimate_submitted_date,
      NULL AS estimate_state,
      NULL AS currency_for_reimbursement,
      NULL AS cash_in_advance,
      NULL AS estimate_purpose,
      NULL AS estimate_exp_type,
      NULL AS estimate_foreign_currency,
      NULL AS estimate_exchange_rate,
      NULL AS estimate_amount,
      NULL AS claim_id,
      NULL AS claim_currency_for_reimbursement,
      NULL AS claimed_date,
      NULL AS claim_exp_type,
      cety.id AS claim_exp_type_id,
      claim_cc.currency_id AS claim_foreign_currency,
      cex.exchange_rate AS claim_exchange_rate,
      cex.amount AS claim_amount,
      cex.remarks AS claim_remarks,
      employee.deleted_at AS emp_deleted_at,
      employee.purged_at AS emp_purged_at,
      employee.termination_id AS emp_termination_id,
      employee.emp_lastname AS emp_lastname,
      el.location_id AS emp_location_id,
      employee.job_title_code AS emp_job_title_code,
      employee.work_station AS emp_work_station,
      cr.request_id AS claim_request_id,
      employee.emp_status AS emp_status,
      NULL AS estimate_sort_id,
      cex.id AS claim_exp_id
    FROM
      `claim_request` cr
      LEFT JOIN `claim_expense` cex ON cex.request_id = cr.id
      LEFT JOIN `claim_expense_type` cety ON cex.expense_type_id = cety.id
      LEFT JOIN `_employee` AS employee ON cr.emp_number = employee.emp_number
      LEFT JOIN claim_currency claim_cc ON (claim_cc.id = cex.currency_id)
      LEFT JOIN claim_currency claim_req_cc ON (claim_req_cc.id = cr.currency_id)
      LEFT JOIN _emp_locations el ON(employee.emp_number = el.emp_number)
    WHERE
      cr.id NOT IN (
        SELECT
          claim_request_id
        FROM
          `claim_estimation_claiming`
      )
  ) AS claims_only_A
WHERE
  (claim_request_id, claim_amount) NOT IN (
    SELECT
      claim_request_id,
      MAX(claim_amount)
    FROM
      (
        SELECT
          cr.request_id AS claim_request_id,
          cex.amount AS claim_amount,
          cety.name AS claim_expense_type,
          cex.id AS claim_exp_id
        FROM
          `claim_request` cr
          LEFT JOIN `claim_expense` cex ON cex.request_id = cr.id
          LEFT JOIN `claim_expense_type` cety ON cex.expense_type_id = cety.id
        WHERE
          cr.id NOT IN (
            SELECT
              claim_request_id
            FROM
              `claim_estimation_claiming`
          )
      ) AS A
    GROUP BY
      claim_request_id,
      claim_expense_type
  )

以下是对查询运行的解释：，

-- MYSQL 5.5
+----+--------------------+--------------------------------+----------------+------------------+------------------+---------+-----------------------------------------+------+---------------------------------+
| id | select_type        | table                          | type           | possible_keys    | key              | key_len | ref                                     | rows | Extra                           |
+----+--------------------+--------------------------------+----------------+------------------+------------------+---------+-----------------------------------------+------+---------------------------------+
|  1 | PRIMARY            | <derived2>                     | ALL            | NULL             | NULL             | NULL    | NULL                                    | 2876 | Using where                     |
|  4 | DEPENDENT SUBQUERY | <derived5>                     | ALL            | NULL             | NULL             | NULL    | NULL                                    | 2876 | Using temporary; Using filesort |
|  5 | DERIVED            | cr                             | ALL            | NULL             | NULL             | NULL    | NULL                                    | 1131 | Using where                     |
|  5 | DERIVED            | cex                            | ref            | request_id       | request_id       | 5       | dbname.cr.id               |    1 |                                 |
|  5 | DERIVED            | cety                           | eq_ref         | PRIMARY          | PRIMARY          | 4       | dbname.cex.expense_type_id |    1 |                                 |
|  6 | DEPENDENT SUBQUERY | claim_estimation_claiming | index_subquery | claim_request_id | claim_request_id | 5       | func                                    |    2 | Using index                     |
|  2 | DERIVED            | cr                             | ALL            | NULL             | NULL             | NULL    | NULL                                    | 1131 | Using where                     |
|  2 | DERIVED            | cex                            | ref            | request_id       | request_id       | 5       | dbname.cr.id               |    1 |                                 |
|  2 | DERIVED            | cety                           | eq_ref         | PRIMARY          | PRIMARY          | 4       | dbname.cex.expense_type_id |    1 | Using index                     |
|  2 | DERIVED            | employee                       | eq_ref         | PRIMARY          | PRIMARY          | 4       | dbname.cr.emp_number       |    1 |                                 |
|  2 | DERIVED            | claim_cc                       | eq_ref         | PRIMARY          | PRIMARY          | 4       | dbname.cex.currency_id     |    1 |                                 |
|  2 | DERIVED            | claim_req_cc                   | eq_ref         | PRIMARY          | PRIMARY          | 4       | dbname.cr.currency_id      |    1 | Using index                     |
|  2 | DERIVED            | el                             | ref            | PRIMARY          | PRIMARY          | 4       | dbname.employee.emp_number |    1 | Using index                     |
|  3 | DEPENDENT SUBQUERY | claim_estimation_claiming | index_subquery | claim_request_id | claim_request_id | 5       | func                                    |    2 | Using index                     |
+----+--------------------+--------------------------------+----------------+------------------+------------------+---------+-----------------------------------------+------+---------------------------------+

-- MARIADB 10.2
+------+--------------+--------------------------------+--------+------------------+------------------+---------+-----------------------------------------+------+------------------------------+
| id   | select_type  | table                          | type   | possible_keys    | key              | key_len | ref                                     | rows | Extra                        |
+------+--------------+--------------------------------+--------+------------------+------------------+---------+-----------------------------------------+------+------------------------------+
|    1 | PRIMARY      | cr                             | ALL    | NULL             | NULL             | NULL    | NULL                                    |  920 | Using where                  |
|    1 | PRIMARY      | cex                            | ref    | request_id       | request_id       | 5       | dbname.cr.id               |    1 | Using where                  |
|    1 | PRIMARY      | cety                           | eq_ref | PRIMARY          | PRIMARY          | 4       | dbname.cex.expense_type_id |    1 | Using where; Using index     |
|    1 | PRIMARY      | employee                       | eq_ref | PRIMARY          | PRIMARY          | 4       | dbname.cr.emp_number       |    1 | Using where                  |
|    1 | PRIMARY      | claim_cc                       | eq_ref | PRIMARY          | PRIMARY          | 4       | dbname.cex.currency_id     |    1 | Using where                  |
|    1 | PRIMARY      | el                             | ref    | PRIMARY          | PRIMARY          | 4       | dbname.employee.emp_number |    1 | Using where; Using index     |
|    4 | MATERIALIZED | cr                             | ALL    | NULL             | NULL             | NULL    | NULL                                    |  920 | Using where; Using temporary |
|    4 | MATERIALIZED | cex                            | ref    | request_id       | request_id       | 5       | dbname.cr.id               |    1 |                              |
|    4 | MATERIALIZED | cety                           | eq_ref | PRIMARY          | PRIMARY          | 4       | dbname.cex.expense_type_id |    1 | Using where                  |
|    6 | MATERIALIZED | claim_estimation_claiming | index  | claim_request_id | claim_request_id | 5       | NULL                                    |    1 | Using index                  |
|    3 | MATERIALIZED | claim_estimation_claiming | index  | claim_request_id | claim_request_id | 5       | NULL                                    |    1 | Using index                  |
+------+--------------+--------------------------------+--------+------------------+------------------+---------+-----------------------------------------+------+------------------------------+

我尝试分别运行子查询，子查询在mysql中没有显示任何延迟。问题似乎只有在查询作为一个整体运行时才会出现。
我觉得，根据解释，这个问题似乎是因为mysql 5.5有更多的 All 类型字段中的值（这意味着mysql必须遍历一个子集中的所有值）。
有没有人有更好的理由或其他方法来改进这个问题？