mysql - MYSQL 查询一直超时

Question

Mysql 查询不断超时 我是 mysql 新手，并被要求解决此问题。不知道从哪里开始解决问题。有没有办法在 MYSQL 中进行故障排除，以便我可以看到问题出在哪里？所以问题是当我运行代码时它会超时。我不知道从哪里开始，因为它是一个同事查询。任何帮助，将不胜感激。

select
    o.id as org_id,
    o.`name` as org,
    u.id as user_id,
    concat(ui.f_name, ' ', ui.l_name) as `user`,
    u.email as user_email,
    u.`status` as user_status,
    le.id as lead_id,
    le.f_name,
    le.l_name,
    le.email,
    le.employer,
    le.email_work,
    le.phone_cell,
    le.phone_office,
    le.phone_home,
    le.ok_to_mail,
    le.ok_to_call,
    le.ok_to_email,
    ulp.is_silenced,
    GROUP_CONCAT(DISTINCT(lg.name) SEPARATOR ', ') as lead_group,
    case
        when le.archived_reason_id = 6 then 'yes'
        else 'no'
    end as DNC,
    case
        when eool.unsubscribe_type = 'global' then 'yes'
        else 'no'
    end as opted_out_global,
    case
        when eool.unsubscribe_type = 'global' then eool.created_at
        else null
    end as opted_out_global_dt,
    case
        when eood.id is not null then 'yes'
        else 'no'
    end as opted_out_domain,
    case
        when eood.id is not null then eool.created_at
        else null
    end as opted_out_domain_dt,
    case
        when eool.unsubscribe_type = 'organization'
            and eool.unsubscribe_id = u.organization_id then 'yes'
        else 'no'
    end as opted_out_org,
    case
        when eool.unsubscribe_type = 'organization'
            and eool.unsubscribe_id = u.organization_id then eool.created_at
        else null
    end as opted_out_org_dt,
    case
        when eool.unsubscribe_type = 'user' and eool.unsubscribe_id = u.id then 'yes'
        else 'no'
    end as opted_out_user,
    case
        when eool.unsubscribe_type = 'user' and eool.unsubscribe_id = u.id then eool.created_at
        else null
    end as opted_out_user_dt,
    case
        when ln.id is not null and le.ok_to_email = 0 then 'yes'
        else 'no'
        end as manually_opted_out_by_user,
    case
        when ln.id is not null and le.ok_to_email = 0 then ln.created_on
        else null
    end as manually_opted_out_by_user_dt,
    case
        when el.is_bounced = 1 then 'email bounced'
        when el.is_failed = 1 then 'email failed'
        when el.is_complained = 1 then 'spam complaint'
        when el.is_unsubscribed = 1 then 'unsubscribed via email'
        else 'N/A'
    end as opted_out_via_email_reason,
    case
        when el.is_bounced = 1 then el.send_date
        when el.is_failed = 1 then el.send_date
        when el.is_complained = 1 then el.send_date
        when el.is_unsubscribed = 1 then el.send_date
        else null
    end as opted_out_via_email_send_dt,
    group_concat( distinct ln2.notes separator '; ') as email_failure_details
        
from leads le
    join `user` u on u.id = le.owner_id
    join user_info ui on ui.user_id = u.id
    join organizations o on o.id = u.organization_id
    left join email_addresses ea on ea.email = le.email
    left join email_opt_out_log eool on eool.email_address_id = ea.id
        and eool.unsubscribe_type != 'tag'
    left join email_opt_out_domains eood on eood.email_sending_domain_id = eool.unsubscribe_id
        and eool.unsubscribe_type = 'domain'
        and eood.email_address_id = eool.email_address_id
        and eood.email_sending_domain_id in
            (select id from email_sending_domains where organization_id = 141)
    left join lead_notes ln on ln.user_id = le.id and ln.type_id = 12
        and ln.notes like 'An opt-out from all%'
    left join email_log el on el.lead_id = le.id and
        (el.is_bounced = 1 or el.is_failed = 1 or el.is_complained = 1 or el.is_unsubscribed = 1)
    left join user_lead_preferences ulp on ulp.lead_id = le.id
        and ulp.user_id = le.owner_id
    left join lead_notes ln2 on ln2.user_id = le.id
        and ln2.type_id in (41,13)
    JOIN lead_group_assigned lga on lga.lead_id = le.id
    JOIN lead_groups lg on lg.id = lga.group_id 
    left join email_log el2 on el.lead_id = le.id
where
    o.id = 141
and le.deleted_at is null
and le.archived_at is null
group by le.id
having
    (le.ok_to_email = 0
    or opted_out_global = 'yes'
    or opted_out_domain = 'yes'
    or opted_out_org = 'yes'
    or opted_out_user = 'yes'
    or manually_opted_out_by_user = 'yes'
    or opted_out_via_email_reason != 'N/A'
    or ulp.is_silenced = 1
    )
order by org, `user`, le.l_name;

标签： mysql