如何在SQL查询中提高多个联接表的性能

debugcn 发表于 Dev

荣达河

如何在提高查询性能的同时获得所需的所有信息。

SELECT 
    tr.id, tr.request_status, tr.note, tr.created_date, 
    c.name AS customer_name, c.mobile_phONe, 
    u.full_name AS created_by_name, tt.name AS ticket_type_name 
FROM  
    ticket_request tr 
LEFT JOIN 
    ticket_type tt ON tt.id = tr.ticket_type_id 
LEFT JOIN 
    users u ON u.id = tr.created_by 
LEFT JOIN 
    customer c ON c.id = tr.customer_id 
WHERE 
    tr.is_deleted != 1 
    AND tr.user_id IN (SELECT u.id FROM users u WHERE u.status = '1') 
GROUP BY 
    tr.id  
ORDER BY 
    tr.created_date DESC 
LIMIT 0,20

当前，此查询将在7到10秒内运行。

ticket_request 表格约有10万行
customers 表格约有30万行
users表并且ticket_type没有那么多（大约1k行）

里克·詹姆斯

下面的加速技术是省去第LIMIT 一个，然后再去做所有的JOINs。

SELECT  tr3.id, tr3.request_status, tr3.note, tr3.created_date,
        c.name AS customer_name, c.mobile_phONe,
        u2.full_name AS created_by_name,
        tt.name AS ticket_type_name
    FROM  
    (
        SELECT  tr1.id
            FROM  ticket_request tr1
            JOIN  users u1  ON u1.id = tr1.created_by
            WHERE  u1.status = '1'
              AND  tr1.is_deleted != 1
            ORDER BY  tr1.created_date DESC
            LIMIT  0,20 
    ) AS tr2
    JOIN  ticket_request AS tr3  ON tr3.id = tr2.id
    JOIN  user AS u2 ON u2.id = tr3.created_by
    LEFT JOIN  ticket_type tt  ON tt.id = tr3.ticket_type_id
    LEFT JOIN  customer c  ON c.id = tr3.customer_id
    ORDER BY  tr3.created_date

的JOINs，所述一个中的“衍生自”表TR2，都在触摸只有20行之后; 这是很大的加速。

这可能同样好：

SELECT  d.id, d.request_status, d.note, d.created_date,
        c.name AS customer_name, c.mobile_phONe, d.created_by_name,
        tt.name AS ticket_type_name
    FROM  
    (
        SELECT  tr.id AS tr_id, tr.request_status, tr.note, tr.created_date,
                tr.ticket_type_id, tr.customer_id
                u.full_name AS created_by_name
            FROM  ticket_request tr
            JOIN  users u  ON u.id = tr.created_by
            WHERE  u.status = '1'
              AND  tr.is_deleted != 1
            ORDER BY  tr.created_date DESC
            LIMIT  0,20 
    ) AS d
    LEFT JOIN  ticket_type tt  ON tt.id = d.ticket_type_id
    LEFT JOIN  customer c  ON c.id = d.customer_id
    ORDER BY  d.created_date

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。