如何改进此查询的性能？

select m.ID,m.cell_number,cm.id 
from corporate_main_member m
   , corporate_main_corporate_membership cm 
WHERE m.status = 'active' 
   AND m.ID IN (
      select cm.FK_Member_ID 
      from corporate_main_corporate_membership cm 
      WHERE cm.status = 'active' 
        AND cm.ID IN (
           select gm.FK_Corporate_Membership_ID 
           from corporate_main_group_membership gm 
           WHERE gm.status = 'active' 
             AND gm.FK_Group_id IN (168,169,170)
             Group BY gm.FK_Corporate_Membership_ID
           )
      ) 
   AND cm.fk_corporation_id = 5 
   AND cm.FK_Member_ID = m.id

On:

1 Million records of corporate_main_member 
2 Million records of corporate_main_corporate_membership
3 Million records of corporate_main_group_membership

The above query halts mysql! It was working until day before yesterday when I added a few thousands records using a script to corporate_main_member and corporate_main_corporate_membership tables. Not sure if it is related or the db size crossed some threshold that caused drastic performance issue.

上面的查询暂停了mysql!直到前天我才使用脚本向corporate_main_member和corporate_main_corporate_membership表添加了几千条记录。不确定它是否相关或者数据库大小超过某个阈值会导致严重的性能问题。

Any help? I have no idea about the indexes or anything. The schema is obvious.

有帮助吗?我不知道索引或任何东西。架构很明显。

1 个解决方案

#1

try this one,

试试这个,

SELECT  m.ID,
        m.cell_number,
        cm.id
FROM    corporate_main_member m
        INNER JOIN corporate_main_corporate_membership cm
            ON  cm.FK_Member_ID = m.id  AND
                cm.fk_corporation_id = 5
        INNER JOIN
        (
            SELECT  ccm.FK_Member_ID
            FROM    corporate_main_corporate_membership ccm
                    INNER JOIN corporate_main_group_membership gm
                        ON ccm.ID = gm.FK_Corporate_Membership_ID
            WHERE   ccm.STATUS = 'active' AND
                    gm.STATUS = 'active' AND
                    gm.FK_Group_id IN ( 168, 169, 170 )
            GROUP   BY ccm.FK_Member_ID
        ) s ON  m.ID = s.FK_Member_ID
WHERE   m.STATUS = 'active'