MySQL SQL 优化案例：JOIN派生表

ゞ浴缸里的玫瑰 2022-08-19 08:13 141阅读 0赞

优化之前：

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;

执行计划：

需要执行21分钟

优化思路：之前的SQL连接的是派生表，并不能有效使用到索引，导致这一写法的直接原因是程序员的惯性思维，总以为先把要join的内容通过过滤、分组、聚合限定在一个小的范围内之后再join效率较高且更符合惯常的思维模式。殊不知MySQL这么做会导致很差的性能。要做的优化就是先直接join这样可以利用到join字段上的索引，之后再进行过滤、分组 、聚合。

优化后：

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     | <derived2> | 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                                  |
    +----+-------------+------------+------+-----------------------------------------------------------+-----------------------------------------+---------+------------------------------+------+----------------------------------------------+

只需运行0.01秒