MySQLSQL优化案例：JOIN派生表

SELECT
  ap.institution_id,
  date(ra.create_time) date,
  sum(

    IF (
      ra.confirm_amount > ld.debit_amount,
      ld.debit_amount,
      ra.confirm_amount
    )
  ) return_amount,
  count(ra.confirm_amount) return_count
FROM
  t_asset_package ap
JOIN (
  SELECT
    asset_package_id,
    institution_id,
    loan_document_id,
    debit_amount
  FROM
    t_loan_details
  WHERE
    is_all_paid = 1
  OR principal_paid = 1
) ld ON ap.asset_package_id = ld.asset_package_id
JOIN (
  SELECT
    loan_document_id,
    sum(confirm_amount) confirm_amount,
    create_time
  FROM
    t_return_application
  GROUP BY
    loan_document_id
) ra ON ld.loan_document_id = ra.loan_document_id
WHERE
  ap.institution_id = 'C0000012413'
AND ap.delete_flag = 0
GROUP BY
  date(ra.create_time)
ORDER BY
  date;

执行计划：

+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+
| id | select_type | table                | type   | possible_keys                           | key                                  | key_len | ref                 | rows   | Extra                           |
+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+
|  1 | PRIMARY     |            | ALL    | NULL                                    | NULL                                 | NULL    | NULL                |  89435 | Using temporary; Using filesort |
|  1 | PRIMARY     |            | ALL    | NULL                                    | NULL                                 | NULL    | NULL                |  88516 | Using where; Using join buffer  |
|  1 | PRIMARY     | ap                   | eq_ref | PRIMARY,FK_ASSET_PACKAGE_CUSTOMER_1_idx | PRIMARY                              | 110     | ld.asset_package_id |      1 | Using where                     |
|  3 | DERIVED     | t_return_application | index  | NULL                                    | FK_RETURN_APPLICATION_DOCUMENT_1_idx | 111     | NULL                | 203837 |                                 |
|  2 | DERIVED     | t_loan_details       | ALL    | NULL                                    | NULL                                 | NULL    | NULL                |  99370 | Using where                     |
+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+

SELECT
  alr.institution_id,
  alr.date,
  SUM(

    IF (
      alr.confirm_amount > alr.debit_amount,
      alr.debit_amount,
      alr.confirm_amount
    )
  ) return_amount,
  count(alr.confirm_amount) return_count
FROM
  (
    SELECT
      ap.institution_id institution_id,
      DATE(ra.create_time) date,
      SUM(ra.confirm_amount) confirm_amount,
      ld.debit_amount debit_amount
    FROM
      t_asset_package ap
    JOIN t_loan_details ld ON ap.asset_package_id = ld.asset_package_id
    AND (
      ld.is_all_paid = 1
      OR ld.principal_paid = 1
    )
    JOIN t_return_application ra ON ld.loan_document_id = ra.loan_document_id
    WHERE
      ap.institution_id = 'C0000012413'
    AND ap.delete_flag = 0
    GROUP BY
      ra.loan_document_id
  ) alr
GROUP BY
  alr.date

执行计划：

+----+-------------+------------+------+-----------------------------------------------------------+-----------------------------------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys                                             | key                                     | key_len | ref                          | rows | Extra                                        |
+----+-------------+------------+------+-----------------------------------------------------------+-----------------------------------------+---------+------------------------------+------+----------------------------------------------+
|  1 | PRIMARY     |  | ALL  | NULL                                                      | NULL                                    | NULL    | NULL                         |    2 | Using temporary; Using filesort              |
|  2 | DERIVED     | ap         | ref  | PRIMARY,FK_ASSET_PACKAGE_CUSTOMER_1_idx                   | FK_ASSET_PACKAGE_CUSTOMER_1_idx         | 63      |                              |    3 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | ld         | ref  | IDX_LOAN_DOCUMENT,FK_LOAN_FEE_DETAILS_ASSET_PACKAGE_1_idx | FK_LOAN_FEE_DETAILS_ASSET_PACKAGE_1_idx | 111     | dcf_loan.ap.asset_package_id |    1 | Using where                                  |
|  2 | DERIVED     | ra         | ref  | FK_RETURN_APPLICATION_DOCUMENT_1_idx                      | FK_RETURN_APPLICATION_DOCUMENT_1_idx    | 111     | dcf_loan.ld.loan_document_id |    1 | Using where                                  |
+----+-------------+------------+------+-----------------------------------------------------------+-----------------------------------------+---------+------------------------------+------+----------------------------------------------+