基于MySQL关系的查询在1秒以下运行，但当我添加更多条件/复杂性时失败(没有错误，InnoDB)

我正在运行一个laravel/lumen应用程序。SQL是使用Eloquent自动生成的，所以这可能是它不稳定的一个原因。它是基于组和字典表的存在来查询资格表的，这些表与它有多对多的关系。
下面的查询失败-它挂起运行无限...阻塞mysql。（没有给出错误-空mysql，php，apache日志）。
https://codebeautify.org/sqlformatter/y22875bc2
然而，如果i减去1-2个随机条件，因此降低了复杂性，它在1 s以下完全运行。
为了给予一些上下文，我运行了下面的api查询。

http://localhost:8000/qualification
?with=dictionaries,groups1,groups2
&limit=20
&offset=0
&metadata=false
&status=funkcjonująca,|włączona
&category=!uregulowane
&groups1>name_pl=NAUKI ŚCISŁE I PRZYRODNICZE
&groups2>name_pl=Geografia,geologia,geofizyka
&hobby=przyroda
&expectation=kultura osobista,zainteresowanie modą,dobry węch
&edulvl=Branżowe,|Średnie,|Wyższe,|Inne pozaformalne

数据库运行在InnoDB上，它有适当的键和索引。
我会说它的行为就像是我在运行一些资源，内存容量？我错了吗？
当我在这个上运行一个explain select时，我得到了同样的行为。在稍微简单一点的时候运行。在测试完整查询时失败。

如果没有更多的细节，就不可能知道哪里出了问题。有些事情是可以清理的。下面两组or exists()标准可以浓缩-

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

可减至─

select * 
from `qualifications` 
where (
    exists (
        select * 
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and (
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?)
        )
        /* or if type is same for both
        and `type` = ?
        and `content_pl` IN (?, ?)
        */
    ) 
)

同样地--

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

可减至─

select * 
from `qualifications` 
where (
    exists (
        select * 
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and (
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?)
        )
        /* or if type is same for all four
        and `type` = ?
        and `content_pl` IN (?, ?, ?, ?)
        */
    ) 
)

如果有一组and exists()条件，您可以使用类似的方法-

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    and exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    and exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

可减至─

select * 
from `qualifications` 
where (
    exists (
        select
            `dictionary_qualification`.`qualification_id`,
            count(distinct `content_pl`) num_criteria_matched
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ?
        and `content_pl` IN (?, ?, ?) # note three values and we want all three to be present
        group by `dictionary_qualification`.`qualification_id`
        having num_criteria_matched = 3 # again, this is the number of values for which we require a match
    ) 
)

基于MySQL关系的查询在1秒以下运行，但当我添加更多条件/复杂性时失败(没有错误，InnoDB)

1条答案

相关问题

热门标签

最新问答