前天客户反馈,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万条