记一次 mysql left join 优化

前天客户反馈，Mysql服务器突然cpu暴增，赶紧查询查mysql执行

 
使用SHOW PROCESSLIST  查看哪些线程正在运行；

结果发现了，两个超10万条数据的查询进行了left join ，无使用索引，还使用了max()函数。整个语句根本无法执行。

EXPLAIN 查看执行计划 ，可以看到 type ALL 10万条跟1个19万条数据 left join ，一个19万数据的max()函数。其实相关的只有两个表

接下来进行优化步骤

首先，确定报表需求，查询内容，想达到的目的；

第二，了解表结构，确定相关表存放内容。

确定表A为基本信息，表B为信息流动表，与表A的关联为 A.ID = B.REF_ID  表A与表B为 1对多。一个基础信息对应2条流动记录（一进一出）并且B中AMOUNT>0的记录与A表一一对应。

修改语句为：

select A.NAME,A.ID,I.INNO,O.OUTNO
FROM A
LEFT JOIN (SELECT REF_ID,INNO FROM B WHERE AMOUNT>0) I ON A.ID=I.REF_ID
LEFT JOIN (SELECT REF_ID,OUTNO FROM B WHERE AMOUNT<0) O ON A.ID=O.REF_ID

执行计划查看，并没有明显，还是有两个19万条数据的全表扫描

修改语句为

select A.NAME,A.ID,I.INNO,O.OUTNO
FROM A
LEFT JOIN (SELECT REF_ID,INNO FROM B WHERE AMOUNT>0) I ON A.ID=I.REF_ID
LEFT JOIN (SELECT REF_ID,OUTNO FROM B WHERE AMOUNT<0) O ON I.REF_ID=O.REF_ID

执行计划查看，剩下一个ALL，扫描19万条数据。2秒多的查询时间。

语句从查询导致CPU暴增，查询速度等待时间长久，优化为查询时间 2秒。目前已是本人优化极限。

此处不明白为什么A表明明放在left 的左边，ALL表不是 A的 12万条，而是 I 中的19万条