MySQL SQL 优化案例：JOIN派生表

来源：互联网发布：mac唇膏试色最全编辑：程序博客网时间：2024/04/27 13:37

优化之前：

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_countFROM  t_asset_package apJOIN (  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_idJOIN (  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_idWHERE  ap.institution_id = 'C0000012413'AND ap.delete_flag = 0GROUP BY  date(ra.create_time)ORDER BY  date;

执行计划：

+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+| id | select_type | table                | type   | possible_keys                           | key                                  | key_len | ref                 | rows   | Extra                           |+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+|  1 | PRIMARY     | <derived3>           | ALL    | NULL                                    | NULL                                 | NULL    | NULL                |  89435 | Using temporary; Using filesort ||  1 | PRIMARY     | <derived2>           | 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                     |+----+-------------+----------------------+--------+-----------------------------------------+--------------------------------------+---------+---------------------+--------+---------------------------------+

需要执行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_countFROM  (    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  ) alrGROUP 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秒

0 0