mysql - 我们可以优化这个 SQL 查询吗？

Question

我有一个至少需要 1 分钟才能运行的查询。它使用了大量的左连接。有什么办法可以让它更快吗？我是 SQL 新手，因此我们将不胜感激。

   SELECT
    distinct cnt.last_name,
    cnt.bus_ph_num,
    cnt.email_addr,
    usr.login,
    cnt.fst_name,
    cnt.fk_id  
from
    contact cnt
LEFT JOIN
    transaction tran 
        ON tran.id=cnt.fk_id    
left join
    party party 
        on party.pk_id=cnt.fk_id  
left join
    user usr 
        on usr.fk_id=cnt.fk_id  
left join
    role role
on      
    usr.role_id = role.pk_row_id
where
    cnt.status_cd=1  
    and   party.fk_id= 'xyz'  
    and (
        usr.login  like '%somevalue%'  
        and  (
            tran.txn_trk_num is  null ||  tran.txn_trk_num like '%somevalue%' 
        ) 
        and  cnt.last_name like '%somevalue%'  
        and  cnt.email_addr like '%somevalue%'  
        and  cnt.busin_ph_num like '%somevalue%'    
    ) 
    and (
        tran.txn_trk_num is  null || tran.txn_typ=1 || tran.txn_typ=2 || tran.txn_typ=3
    ) 
    and (role.role_name ='ROLE_ADMIN')

Answer 1

是的，我已经尽力了。主要是一些重新排序、一些交换到 INNER JOIN 以及将 WHERE 语法移动到连接。对别名进行一些重命名，因为您使用了与表名匹配的别名，从而否定了它们的目的。

这将返回没有匹配事务的行，因此您可能也希望将该连接更改为 INNER，而不是取决于目的/预期输出，但这应该是一个很好的起点。它允许 MySQL 减少它查看的行数。

可以通过合适的索引进行进一步的改进，但在不了解数据类型/数据方差等的情况下很难就这些提出建议。

SELECT DISTINCT
    cnt.last_name,
    cnt.bus_ph_num,
    cnt.email_addr,
    u.login,
    cnt.fst_name,
    cnt.fk_id  

FROM contact cnt

-- Changed  to INNER JOIN as the WHERE indicates this is required
INNER JOIN party p 
    ON p.pk_id=cnt.fk_id  
    -- Moved this limiting statement to the join
    AND p.fk_id= 'xyz' 

-- Changed to INNER JOIN as the LIKE % indicates htis is required
INNER JOIN user u 
    ON u.fk_id=cnt.fk_id 
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND u.login LIKE '%somevalue%'  

-- Also here, INNER, as the WHERE means this is required
INNER JOIN role r
    ON r.pk_row_id = u.role_id
    -- Moved this limiting statement to the join
    AND r.role_name ='ROLE_ADMIN'

LEFT JOIN transaction tran 
    ON tran.id=cnt.fk_id
    -- Moved this limiting statement to the join
    AND tran.txn_typ IN ( 1 , 2 , 3 )
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND tran.txn_trk_num LIKE '%somevalue%' 

WHERE cnt.status_cd = 1  
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND cnt.last_name LIKE '%somevalue%'  
    AND cnt.email_addr LIKE '%somevalue%'  
    AND cnt.busin_ph_num LIKE '%somevalue%' 

如果你能去掉那些 LIKE 语句，它也会变得更好。如果有合适的索引，以下人员可以使用索引:

• ...喜欢“某个值”
• ... = '某个值'
• ... LIKE 'somevalue%'

但是... LIKE '%somevalue%' 无法使用索引