过滤查询大约需要50秒才能加载。

Question

我有一个网页，用户可以搜索空缺与一套非常广泛的过滤器。其中两个过滤器是复选框，用户可以在其中选择兴趣和可访问性。如果被选中，它们中至少有一个必须出现在特定的空缺上，从而显示出来。

我正在使用CodeIgniter PHP。一个使我的站点崩溃的生成查询的示例如下所示：

SELECT v.*
     , o.name as orgname
     , GROUP_CONCAT(DISTINCT(vi.interest_id)) interests
     , GROUP_CONCAT(DISTINCT(i.description)) interestnames
     , GROUP_CONCAT(DISTINCT(i.name_brown)) interesticons
     , GROUP_CONCAT(DISTINCT(e.engagement_key)) engagement_key
  FROM vacancies v
  LEFT 
  JOIN organization o 
    ON v.org_id = o.org_id
  LEFT 
  JOIN vacancy_interests vi 
    ON v.vacancy_id = vi.vacancy_id
  LEFT 
  JOIN interests i 
    ON vi.interest_id = i.interest_id
  LEFT 
  JOIN engagement e 
    ON v.engagement = e.engagement_id
  LEFT 
  JOIN cities_be c 
    ON v.address_city_id = c.cities_be_id
  LEFT 
  JOIN vacancy_accessibility va  
    ON v.vacancy_id = va.vacancy_id
 WHERE v.is_deleted != 1
   AND v.status = 1
 GROUP 
    BY v.vacancy_id
HAVING MAX(vi.interest_id IN (3,4,6,7,9,10)) > 0 
   AND MAX(va.accessibility_id IN (2,1,3,4,5,6)) > 0 
 ORDER 
    BY v.modified_time DESC
 LIMIT 18

我在我的网站上使用的每一个过滤器都是平滑的，并给出了结果，但是vacancy_accessibility表使我的搜索页面在使用时完全崩溃。在我有大约100个空缺的本地主机上，它工作得很好，而且速度很快。但是我的制作服务器，我有大约11k的空缺，都下地狱，搜索需要5-6分钟才能完成。

我已经在MySQL工作台中执行了上面粘贴的查询，大约需要52秒才能计算出查询。

如果我执行相同的查询，但只对选择的兴趣(这意味着我不通过左联接加载可访问性表，它的工作速度非常快(大约0.1秒)。相反，我已经尝试了删除join的兴趣，并且查询速度更快，但是它仍然是16秒左右。请注意，我确实需要将兴趣加入到其中，所以这并不是一个真正的选项，仅用于调试。

空缺可访问性表实际上与vacancy_interests完全相同。它包含一个vacancy_id列和一个指向accessibility_id的引用列。空缺利益表包含28k行，而空缺可访问性表仅包含5.8k行.我确实注意到，在可访问性表中没有FK的集合，而在interests表中则没有设置它们。我不确定这是否导致了如此大的延迟(实际上不是那么多行)。

有人看到我的查询有什么问题使我的页面崩溃了吗？

Answer 1

您当前的选择有几个选项：

GROUP_CONCAT(DISTINCT)

• all
• 它必须先执行所有的联接，然后再进行聚合，然后才开始筛选
• ，因为这些联接增加了行数，因为其中一些是m-n而不是n-1，最终导致连接是外部连接，不会减少

的行数。

尝试尽快应用过滤器和GROUP_CONCATs，并在可能的情况下切换到内连接。将这些规则应用于查询的第一部分：

SELECT `v`.*, 
   vi.interests, 
   vi.interestnames, 
   vi.interesticons

FROM `vacancies` AS `v`
JOIN 
 ( -- Derived Table to apply filter & concat as soon as possible
   -- also results in a single row per vacancy_id (n.1-join)
   SELECT `vacancy_id`
     ,GROUP_CONCAT(i.interest_id) AS interests
     ,group_concat(i.description) AS interestnames -- DISTINCT probably not needed
     ,group_concat(i.name_brown) AS interesticons -- DISTINCT probably not needed
   FROM `vacancy_interests` AS `vi`
   JOIN 
    ( SELECT interest_id
      FROM `interests`
      GROUP BY interest_id
      HAVING Max(interest_id) IN (3, 4, 6, 7, 9, 10)  
    ) AS `i`
   ON `vi`.`interest_id` = `i`.`interest_id`
 ) AS `vi`
ON `v`.`vacancy_id` = `vi`.`vacancy_id`
...

现在，使用连接将accessibility_id添加到类似的派生表中。当应用所有筛选条件时，将整个选择放入另一个派生表中，包括限制(减少行数)，最后加入其余的表(如果可能的话，也可以使用内连接)