mysql 优化慢复杂sql （多个left join 数量过大 order by 巨慢）

前沿

懒得看过程的话这里直接总结一下最后的解决方法：
如果不能直接减少主表的数据（小表驱动大表），就想办法把多个left join合成一个子查询，速度是否变快，没有的话再在子查询底下加一个having条件（having什么不重要，结果不会错就行）

项目场景：

项目场景：因为一些迫不得已的原因（产品一定要）导致一个分页查询数据的sql非常复杂，查询效率巨巨巨慢（从来没查到过结果，最长等了2分钟）

---

问题描述

涉及项目，就不贴真实代码了，大概结构是

select p.id,p.name,ps2.sort
from table1 p
left join table2 ps
on p.name = ps.name
and ps.region = 1
left join table2 ps2
on ps.name = ps2.name
and ps2.region = 1
and ...
where ...
order by ps2.sort asc,p.sale desc,p.time desc
limit 0,10

table1表有1w+的数据量，table2表有八百万数据，每个region大概有1w+数据。

---

原因分析：

left join数据量太大，笛卡尔积相当于1w✖️1w✖️1w，也就是做了1w✖️1w✖️1w的关联。最后的order by由于数据量过大，要反复回表查做排序，导致查询速度及其慢。
（不加order by时10秒能查出来，虽然也还是慢但是至少能查出来，但是order by 不能不要）

---

解决方案：

经过一系列百度，作出以下几种解决尝试。

方案一：按需给涉及的两个表加了索引，explain走了索引，但仍然因为order by 无法查出结果。（删除order by仍需要10s+）

方案二：根据对mysql join的理解，按理说减少笛卡尔积应该能大幅度提升速度，于是猜想把sql的两次left join 变成一次，是不是就能解决问题？对sql进行分析，能把后两次left join抽成一个子查询，即如下结构

select p.id,p.name,pss.sort
from table1 p
left join (
select name,sort
from table2 ps
left join table2 ps2
on ps.name = ps2.name
and ...
where ...
) pss
on p.name = pss.name
where ...
order by pss.sort asc,p.sale desc,p.time desc
limit 0,10

但是仍然查询很慢也仍然无法查出结果，explain的结果与未作修改的explain结果没有差别，也就是说笛卡尔积仍然是1w✖️1w✖️1w，而我预想的结果应当是1w✖️1w。

方案三：百思不得其解时，我随手在方案二升级的子查询里加了一个having sort = 1，速度陡然变快，与方案二的sql仅一个having之差，速度却提升了数十倍，原本2min都查不出来的sql，现在3秒内能查出，explain结果也有所不同，多了一行table为 < dervied2>的结果，看样子是实现了1w✖️1w的效果。

---

猜想：

方案二的子查询应该是被mysql自动优化成直接的left join关联，所以explain结果才会没有差别，而在子查询中加了having之后mysql无法自动优化成直接的left join，就沿用了sql的调用顺序，所以explain才会多一个子查询的行，也就实现了我想要的效果，即从1w✖️1w✖️1w ->1w✖️1w，速度也就得到了很大的提升。
没有谷歌出来实锤，但是根据explain的结果应该是这样没错。

讲道理应该从更根本的表结构的源头上解决问题，或者考虑是不是应该用es，但是有些东西身不由己，代码和人总要有一个能跑，哎。