派生查询和子查询组合查询的 MySQL 查询优化

Question

以下查询在使用派生表和子查询的服务器上运行。约束是子查询是根据当前情况从多个模块生成的，不能真正转化为join组合。

请提出优化查询的可能解决方案

 SELECT COUNT(1) 
 AS total 
 FROM member tlb_m
 where tlb_m.active = 1 
 and tlb_m.rank > 0 
 and tlb_m.member_id not in (5735,134,241,1055,348,272,476,43,7,804,7548,90,229,346,40895) 
 and tlb_m.type = 'M' 
 and (tlb_m.hometown_list_id in 
  (SELECT l2.list_id  
    FROM ((
      SELECT t12.list_id 
      from list_tree_idx t12 
      INNER JOIN list_tree_idx t11 
      ON t12.list_parent_id=t11.list_id 
      where t11.list_parent_id='205546' 
    ) UNION ALL (
      SELECT list_id 
      from list_tree_idx 
      where list_parent_id='205546'
    ) ) as l2 
  ) or tlb_m.hometown_list_id = 205546
) 

Answer 1

我建议使用 closure table用于最佳分层查询。例如，如果有一个包含 ANCESTOR_ID、CHILD_ID 和 DEPTH 列的闭包表，您的查询将如下所示

SELECT COUNT(1) AS total 
  FROM member AS tlb_m
  LEFT JOIN hometown_closure AS c ON c.child_id = tlb_m.hometown_list_id
  where tlb_m.active = 1 
    and tlb_m.rank > 0 
    and tlb_m.member_id not in (5735,134,241,1055,348,272,476,43,7,804,7548,90,229,346,40895) 
    and tlb_m.type = 'M' 
    and c.ancestor_id = 205546