MySQL SQL 优化--JOIN派生表

偏执的太偏执、 2024-02-18 20:21 17阅读 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;  
  
执行计划：  
![70][]  
  
需要执行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  
执行计划：  
![70 1][]  
只需运行0.01秒

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/35864222a5774002b6ca22bf6d26a672.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/eb1d9ecf86d54f428e4d655c2c821500.png