sql查询优化sugarcrm

Question

我试图优化mysql db中的sql查询。尝试了各种索引变体，但是没有任何帮助。也许我错过了什么

查询：

SELECT  count(1) AS fAccounts
    from  sugarcrm.accounts t4, 
      ( SELECT  t3.related_id
            FROM  sugarcrm.prospect_lists_prospects t3, sugarcrm.prospect_list_campaigns t2
            where  t3.deleted=0
              and  t3.related_type='Accounts'
              and  t3.prospect_list_id=t2.prospect_list_id
              and  t2.deleted=0
              and  t2.campaign_id='10909eb7-8080-45b6-8c9f-563b42be91e5'
      ) t3
    where  t4.deleted=0
      and  t4.id=t3.related_id;

解释：

+----+-------------+------------+--------+---------------------------------------------------+----------------+---------+------------------------------+--------+-------------+
| id | select_type |   table    |  type  |                   possible_keys                   |      key       | key_len |             ref              |  rows  |    Extra    |
+----+-------------+------------+--------+---------------------------------------------------+----------------+---------+------------------------------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                              | NULL           | NULL    | NULL                         |   5000 |             |
|  1 | PRIMARY     | t4         | eq_ref | PRIMARY;idx_accnt_id_del;idx_accnt_assigned_del   | PRIMARY        | 108     | t3.related_id                |      1 | Using where |
|  2 | DERIVED     | t2         | ref    | idx_pro_id;idx_cam_id;idx_prospect_list_campaigns | idx_cam_id     | 111     |                              |      1 | Using where |
|  2 | DERIVED     | t3         | ref    | idx_plp_pro_id;idx_plp_rel_id_2                   | idx_plp_pro_id | 111     | sugarcrm.t2.prospect_list_id | 463968 | Using where |
+----+-------------+------------+--------+---------------------------------------------------+----------------+---------+------------------------------+--------+-------------+

Answer 1

内部查询是麻烦制造者。可以执行两种方法:从t2开始，然后对t3执行“嵌套循环连接”，反之亦然。优化器将查看WHERE子句以及表大小和索引，以估计哪一个是最好的开始。让我们为优化器提供每个方向的“最佳”索引：

从t2开始：

t2:  INDEX(deleted, campaign_id) -- in either order
t3:  INDEX(prospect_list_id, deleted, related_type) -- in any order

从t3开始：

t3:  INDEX(deleted, related_type) -- in either order
t2:  INDEX(prospect_list_id, deleted, campaign_id) -- in any order

与其向每个表添加2个索引，不如让我们这样做

t2:  INDEX(campaign_id, deleted, prospect_list_id)  -- in this order
t3:  INDEX(related_type, deleted, prospect_list_id)  -- in this order

类似地，t4 (将是最后一个)需要

INDEX(deleted, id)

除非它是InnoDB，并且已经有了PRIMARY KEY(id)，否则它将与数据“群集”。

有个问题..。当您执行JOIN，然后计算聚合时，JOIN首先给出行的爆炸式增长，然后COUNT()对其中的行进行过多的计数，从而得到一个膨胀的数字。所以，一定要检查结果。

由于t4的唯一需要是验证related_id是否存在，所以可以将查询重新表示为

SELECT  COUNT(*) AS fAccounts
    FROM  prospect_lists_prospects t3
    -- Note the use of `JOIN...ON...`:
    JOIN  prospect_list_campaigns t2 ON t3.prospect_list_id=t2.prospect_list_id
    where  t3.deleted=0
      and  t3.related_type='Accounts'
      and  t2.deleted=0
      and  t2.campaign_id='10909eb7-8080-45b6-8c9f-563b42be91e5'
      AND  ( EXISTS *
            FROM  accounts
            FROM  accounts t4
            WHERE  t4.id = t3.related_id 
           )

这仍然需要建议的索引(每个表一个)。

Answer 2

由于您在任何地方都不使用DISTINCT，所以我认为没有必要费心创建临时表。试试这个：

SELECT 
     count(1) AS fAccounts
  from sugarcrm.accounts t4 inner join
       sugarcrm.prospect_lists_prospects t3 on t4.id=t3.related_id inner join
       sugarcrm.prospect_list_campaigns t2 on t3.prospect_list_id=t2.prospect_list_id
  where t3.deleted=0 
    and t3.related_type='Accounts' 
    and t2.deleted=0 
    and t2.campaign_id='10909eb7-8080-45b6-8c9f-563b42be91e5'
    and t4.deleted=0