mysql - 在下面的查询中哪一部分是昂贵的？

Question

如果有人能帮助我找出以下 SQL 查询中最昂贵的部分是什么，我将不胜感激:

SELECT a.*, c.id AS companyid, c.name AS companyname, 
CONCAT_WS(" ",fname,lname) AS name, c.alias as co_alias, 
count(DISTINCT(amprop.prop_id)) AS prop_count
FROM #__iproperty_agents as a
LEFT JOIN #__iproperty_agentmid as amprop ON amprop.agent_id = a.id
LEFT JOIN #__iproperty_companies as c on c.id = a.company
WHERE a.state = 1 AND c.state = 1 AND a.featured = 1
GROUP BY a.id
ORDER BY RAND() 

这个查询需要 20 多秒才能执行，这很荒谬。 CMS 是 joomla 3.15，这是 iproperty joomla 组件中的查询。

id select_type table  type   possible_keys key                        key_len ref                           rows   Extra
 1 SIMPLE      a      ALL    company       NO INDEX KEY COULD BE USED NULL    NULL                          8      using where; Using temporary; Using tilesort
 1 SIMPLE      c      eq_ref PRIMARY       PRIMARY                    4       daily_dailyrealsnew.a.company 1      using where
 1 SIMPLE      amprop index  agent_id      prop_id                    8       NULL                          229294 Using index

Answer 1

这是您的查询:

SELECT a.*, c.id AS companyid, c.name AS companyname, 
       CONCAT_WS(" ",fname,lname) AS name, c.alias as co_alias, 
       count(DISTINCT(amprop.prop_id)) AS prop_count
FROM #__iproperty_agents as a LEFT JOIN
     #__iproperty_agentmid as amprop
     ON amprop.agent_id = a.id LEFT JOIN
     #__iproperty_companies as c
     on c.id = a.company
WHERE a.state = 1 AND c.state = 1 AND a.featured = 1
GROUP BY a.id
ORDER BY RAND() ;

要真正理解它，你需要看解释。一些索引会有所帮助。让我假设所有 id 都是主键，因此可以有效索引。那么 #__iproperty_agents(state,featured) 和 #__iproperty_companies(state) 上的索引可能会有所帮助。

很可能导致性能下降的是group by。您可以尝试使用相关子查询。有了正确的索引，这应该表现得很好:

SELECT a.*, c.id AS companyid, c.name AS companyname, 
       CONCAT_WS(' ', fname, lname) AS name, c.alias as co_alias, 
       (select count(distinct amprop.prop_id)
        from #__iproperty_agentmid amprop
        where amprop.agent_id = a.id
       ) as prop_count
FROM #__iproperty_agents a LEFT JOIN
     #__iproperty_companies c
     on c.id = a.company
WHERE a.state = 1 AND c.state = 1 AND a.featured = 1
ORDER BY RAND() ;

对于此查询，您需要在#__iproperty_agentmid(agent_id, prop_id)上建立索引。使用相关子查询删除外部group by，这可以提高性能。