MySQL SQL 优化案例：JOIN派生表

最新推荐文章于 2024-07-11 09:22:15 发布

zyz511919766

最新推荐文章于 2024-07-11 09:22:15 发布

阅读量4.1k

点赞数

文章标签： mysql sql optimization

本文链接：https://blog.csdn.net/zyz511919766/article/details/50469900

版权

本文通过具体的MySQL SQL查询案例，深入探讨了在遇到JOIN操作时如何利用派生表进行性能优化，揭示了派生表在复杂查询中的优化策略，并提供了解决高延迟查询的有效方法。

摘要由CSDN通过智能技术生成

优化之前：

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;

执行计划：