SQL 语句创建许多连接并挂起数据库

Question

SELECT Distinct org.id_name,org.id_region,org.id_inn,org.id_kpp,org.id_username, agreements.id_agr_code,
agreements.id_crat, agreements.id_project_name, agreements.comment, agreements.comment2, agreements.id_factor, agreements.id_name,
month1.id_date_money,month1.id_done,month2.id_date_money,month2.id_done,month3.id_date_money,month3.id_done,month4.id_date_money,
month4.id_done,month5.id_date_money,month5.id_done,month6.id_date_money,month6.id_done FROM agreements
inner join org on (org.id_org=agreements.id_org)
LEFT OUTER JOIN money as month1 ON (agreements.id_agr = month1.id_dogovor) and ( month1.id_date_money is NULL OR month1.id_date_money=:Month1)
LEFT OUTER JOIN money as month2 ON (agreements.id_agr = month2.id_dogovor) and ( month2.id_date_money is NULL OR month2.id_date_money=:Month2)
LEFT OUTER JOIN money as month3 ON (agreements.id_agr = month3.id_dogovor) and ( month3.id_date_money is NULL OR month3.id_date_money=:Month3)
LEFT OUTER JOIN money as month4 ON (agreements.id_agr = month4.id_dogovor) and ( month4.id_date_money is NULL OR month4.id_date_money=:Month4)
LEFT OUTER JOIN money as month5 ON (agreements.id_agr = month5.id_dogovor) and ( month5.id_date_money is NULL OR month5.id_date_money=:Month5)
LEFT OUTER JOIN money as month6 ON (agreements.id_agr = month6.id_dogovor) and ( month6.id_date_money is NULL OR month6.id_date_money=:Month6)
where agreements.id_old=:Archive
and case when :region is null then org.id_region=org.id_region else FIND_IN_SET(org.id_region, :region) end
and case when :users is null then org.id_user=org.id_user else FIND_IN_SET(org.id_user, :users) end
and case when :agrtype is null then agreements.id_type=agreements.id_type else FIND_IN_SET(agreements.id_type, :agrtype) end
and case when :agrproject is null then agreements.id_project_name=agreements.id_project_name else FIND_IN_SET(agreements.id_project_name, :agrproject) end
ORDER BY org.id_name 

MySQL中的这个SQL语句需要很多分钟才能完成，并且在某些时候会挂起BD，因为它会创建很多root的连接，并说"复制到tmp_table"。错误消息显示"与BD的连接过多"。

我认为这是因为FIND_IN_SET但我不确定。我找不到另一种方法来重写此 SQL，以便它执行得更快并且不会创建所有这些连接（它会继续创建它们，直到将其填满为止）。

这个想法是显示哪个组织的哪些协议在定义的月份内付款，哪些没有。区域过滤器为数字 01,05,09,23,26,91 和 e.t.c，用户过滤器为user_id整数。agrtypes 的过滤器在字符串中，agrprojects 也在字符串中。ID_old是一个布尔标志。ID_agr、ID_Dogovor 和 id_org 是主键整数。

Answer 1

agreements.id_old是一个 ID 还是只是某个标志，表明该 ID 是旧格式或类似格式？如果它确实是 ID，则可能不会经常出现在表中。也许甚至只有一次？然后，列上的索引应该使访问非常快。您还应该有一个关于money.id_dogovor 的索引。

我建议以下索引：

create index idx1 on agreements(id_old, id_org, id_agr);
create index idx2 on money(id_dogovor, id_date_money, id_done);

（我推测存在org(id_org)的唯一索引，因为这似乎是表的主键。