mysql 通过多个左连接、分组依据和顺序进行优化

Question

我的查询遇到了一些困难，其中包含多个左连接以及分组依据和顺序。

文本表和文本详细信息包含 +- 800k 条记录
复制表和复制详细信息包含 +- 200k 记录
其他表要小得多。

我执行左连接的每一列都有外键。我还在执行 where 语句的每一列上都有索引。下面的 MySQL 查询仍然运行大约 40 秒。省略 Group By 会有所改善。省略 Order By 可以改善很多。

我做了一些研究，但我仍然对如何改进查询或索引感到困惑。

SELECT * FROM `copy` 
LEFT JOIN `domain` ON domain.domain_id = copy.copy_domain_id 
LEFT JOIN `domaincategory` ON copy.copy_domain_id = domaincategory.domaincategory_domain_id AND domaincategory.domaincategory_account_id = copy.copy_account_id 
LEFT JOIN `text` ON text.text_id = copy.copy_text_id LEFT JOIN `textdetails` ON textdetails.textdetails_text_id = text.text_id 
LEFT JOIN `channel` ON channel.channel_domain_id = domain.domain_id AND channel.channel_account_id = copy.copy_account_id 
LEFT JOIN `feed` ON feed.feed_id = text.text_feed_id 
WHERE (feed.feed_account_id = 96) AND (feed.feed_flag_delete IS NULL) AND (text.text_flag_delete IS NULL) AND (copy.copy_flag_delete IS NULL) AND (copy.copy_tracking_date_found IS NOT NULL) AND (channel.channel_active = 1) 
GROUP BY `copy`.`copy_id`
ORDER BY `copy`.`copy_tracking_date_found` DESC LIMIT 50 

EXPLAIN选项的结果如下所示，但我不知道如何阅读并正确使用它

ID  : 1
Select_type : SIMPLE
Table : Feed
Type : Ref
Possible_Keys: PRIMARY,fk_feed_account_id,feed_flag_delete
Key: fk_feed_account_id
Key_len : 4:
Ref : const
Rows : 1
Extra: Using where; Using temporary; Using filesort


ID  : 1
Select_type : SIMPLE
Table : text
Type : Ref
Possible_Keys: PRIMARY,fk_text_feed_id,text_flag_delete
Key: text_flag_delete
Key_len : 2
Ref : const
Rows : 2628
Extra: Using where


ID  : 1
Select_type : SIMPLE
Table : textdetails
Type : Ref
Possible_Keys: fk_textdetails_text_id
Key: fk_textdetails_text_id
Key_len : 5
Ref : text.text_id
Rows : 1
Extra:


ID  : 1
Select_type : SIMPLE
Table : copy
Type : Ref
Possible_Keys: fk_copy_account_id,fk_copy_domain_id,fk_copy_text_...
Key: fk_copy_text_id
Key_len : 4
Ref : text.text_id
Rows : 1
Extra: Using where


ID  : 1
Select_type : SIMPLE
Table : domain
Type : eq_ref
Possible_Keys: PRIMARY
Key: PRIMARY
Key_len : 4
Ref : copy.copy_domain_id
Rows : 1
Extra: Using where


ID  : 1
Select_type : SIMPLE
Table : domaincategory
Type : eq_ref
Possible_Keys: fk_domaincategory_account_id,fk_domaincategory_dom
Key: fk_domaincategory_domain_id
Key_len : 4
Ref : domain.domain_id
Rows : 1
Extra:


ID  : 1
Select_type : SIMPLE
Table : channel
Type : ref
Possible_Keys: fk_channel_account_id,fk_channel_domain_id,channel...
Key: fk_channel_domain_id
Key_len : 4
Ref : copy.copy_domain_id
Rows : 2
Extra: Using where

也许我应该多解释一下其中的关系？
提要:文本 = 1:n
文本:文本详细信息 = 1:1
文本:复制 = 1:n
复制:域 = n:1
channel :域 n:1

Answer 1

我会更改很多内容并更新查询以反射(reflect)这一点。另外，对于索引。如果你在每一列上都有索引，但它们是单独的索引，那不一定对你有帮助。如果可能的话，您需要复合(多字段)索引来更好地匹配您的联接/其中条件和分组。

SELECT 
      * 
   FROM 
      feed f 
         JOIN text t
            ON feed.feed_id = t.text_feed_id
            LEFT JOIN textdetails td 
               ON t.text_id = td.textdetails_text_id 
            JOIN COPY c
               ON t.text_id = c.copy_text_id 
               LEFT JOIN domain d
                  ON c.copy_domain_id = d.domain_id
               LEFT JOIN domaincategory dc 
                  ON  c.copy_domain_id = dc.domaincategory_domain_id 
                  AND c.copy_account_id = cd.domaincategory_account_id
               JOIN channel ch 
                  ON  c.copy_account_id = ch.channel_account_id 
                  AND c.copy_domain_id = ch.channel_domain_id
                  AND ch.channel_active = 1
   WHERE 
          f.feed_account_id = 96
      AND f.feed_flag_delete IS NULL
      AND t.text_flag_delete IS NULL
      AND c.copy_flag_delete IS NULL
      AND c.copy_tracking_date_found IS NOT NULL
   GROUP BY 
      c.copy_id
   ORDER BY 
      c.copy_tracking_date_found DESC 
   LIMIT 
      50 

Transitive Property
由于copy.copy_domain_id是对domain.domain_id的联接，而channel.channel_domain_id是对domain.domain_id的联接，因此我们只需更改为copy.copy_domain_id =channel.channel_domain_id，而不需要对其他相同值的不同表进行拆分联接。

第二...您有 LEFT JOINS，但是当您添加“feed_account_id = 96”时，您会自动将其转换为 INNER JOIN，因为这是一个要求，从而使 TEXT 别名也成为一个联接。与 Channel_Active = 1 的 CHANNEL 表类似。(我有更新查询来反射(reflect)这一点)。

现在，由于根据特定帐户的 FEED 表进行资格认证，我已将其移至第一个 FROM 位置并向下连接到 Copy 表。

现在，索引可以帮助优化这一点

table        index
feed         ( feed_account_id, feed_id, feed_flag_delete )
text         ( text_feed_id, text_id, text_flag_delete )
textdetails  ( textdetails_text_id )
copy         ( copy_text_id, copy_domain_id, copy_account_id, copy_flag_delete, copy_tracking_date_found, copy_id )
domain       ( domain_id )
domaincategory ( domaincategory_domain_id, domaincategory_account_id )
channel       ( channel_domain_id, channel_account_id, channel_active )