场景描述：

mysql 5.7版本，根据开发人员描述，执行时间为100S，

初步调整相关索引之后，执行速度达到48s，执行预期是3s内

top sql 如下：

select * FROM t_case AS ca

left join t_property tp on ca.property_ID=tp.id

left join t_property_ext pe on tp.id = pe.PROP_ID

left join t_property_no tpp on tp.property_no=tpp.property_no

left join op_user ou on ou.USER_ID=ca.assign_oper

left join t_case_repay cr on cr.CASE_ID = ca.id and cr.id =(select id from t_case_repay cr1 where

cr1.case_id=ca.id and cr1.`STATUS`!='R' order by cr1.AMOUNT desc limit 1)

left join t_case_address tca on tca.id = (select addr.id from t_case_address addr where addr.case_id = ca.id limit

left join t_case_address tca on tca.id = (select addr.id from t_case_address addr where addr.case_id = ca.id limit

1)

left JOIN t_case_assignment as case_ass ON ca.id = case_ass.CASE_ID AND case_ass.id=(SELECT id FROM

t_case_assignment ib WHERE ca.id=ib.CASE_ID ORDER BY ib.CREATE_TIME DESC LIMIT 1 )

inner JOIN t_agency as agency ON ca.agency_ID = agency.ID

inner JOIN t_org_prod as org_prod ON ca.prod_Id = org_prod.ID

left join t_case_out co on co.ASSIGN_ID=case_ass.id and co.id=(select id from t_case_out co1 where

co1.ASSIGN_ID=case_ass.id and co1.`STATUS` ='F' order by co1.id desc limit 1)

left join t_case_journal cj on cj.OUT_ID=co.id

left join t_case_eout ce on ce.assign_id = case_ass.id and ce.id=(select id from t_case_eout ce1 where

ce1.assign_id=case_ass.id order by ce1.id desc limit 1)

where ca.STATUS in ("A","W","EAA") and tp.is_enable ='1'

and agency.agency_name like'%XX%'

此sql 是一个十几张表的连接查询，包含inner join，left join ，连接条件还嵌套着相关子查询。

逐步分离 连接条件，发现执行速度都很快1-2s内执行完毕，当加上

left join t_case_eout ce on ce.assign_id = case_ass.id and ce.id=(select id from t_case_eout ce1 where

ce1.assign_id=case_ass.id order by ce1.id desc limit 1)

这个表连接条件之后，立马变慢，定位到问题点。

观察执行计划的差异：

| 1 | PRIMARY | ce | NULL | eq_ref | PRIMARY,idx_case_eout_assignid1 | PRIMARY | 8 | func | 1 | 100.00 | Using where |

| 6 | DEPENDENT SUBQUERY | ce1 | NULL | ref | idx_case_eout_assignid1 | idx_case_eout_assignid1 | 9 | collect.case_ass.ID | 1 | 100.00 | Using where; Using index; Using filesort

是一个 eq_ref 和一个子查询相关 ref 感觉是这个相关子查询拖慢了整个嵌套循环。

测试非相关子查询逻辑：

取出 t_case_eout 和 t_case_assignment 连接之后的最大id号，发现表连接很快。

select ce1.id from t_case_eout ce1 join t_case_assignment case_ass on ce1.assign_id = case_ass.id

order by ce1.id desc limit 1

--不到1秒执行跑出来

将这个连接改为如下写法：

left join t_case_eout ce on ce.assign_id = case_ass.id and ce.id=( select ce1.id from t_case_eout ce1 join t_case_assignment case_ass on ce1.assign_id = case_ass.id

order by ce1.id desc limit 1)

观察执行计划变更：

| 1 | PRIMARY | ce | NULL | const | PRIMARY,idx_case_eout_assignid1 | PRIMARY | 8 | const | 1 | 100.00 | Using where |

| 6 | SUBQUERY | ce1 | NULL | index | idx_case_eout_assignid1 | PRIMARY | 8 | NULL | 1 | 100.00 | Using where |

| 6 | SUBQUERY | case_ass | NULL | eq_ref | PRIMARY | PRIMARY | 8 | collect.ce1.assign_id | 1 | 100.00 | Using index |

可以看到变成了 基于索引的一个子查询 表连接+const 和ce 连接，大大提高了循环效率

最后执行时间优化为1.6s

--其实还有几个left join的相关子查询连接逻辑也可以 尝试使用改为非相关子查询方式优化，但是已经达到预期，不再多做调整。