如何将MySQL查询耗时从1.3秒优化至0.1秒？

你的查询性能瓶颈完全来自WHERE子句中的两个相关子查询——主查询每返回一行数据，这两个子查询就要各执行一次，相当于做了大量重复计算，直接把耗时从0.03秒拉到1.3秒。下面是具体优化步骤：

1. 用预聚合查询替代相关子查询 #

把两个需要计算sum的逻辑改成提前聚合的临时结果集，再通过JOIN和主表关联，避免重复计算：

SELECT 
    p.po_id, 
    DATE_FORMAT(p.po_date,'%d-%m-%Y') AS po_date,
    p.branch_id,
    b.fullname as branch_send,
    p.branch_id2,
    bb.fullname as branch_recieve
FROM tbl_po_branch p
LEFT JOIN tbl_branch b ON b.branch_id = p.branch_id
LEFT JOIN tbl_branch bb ON bb.branch_id = p.branch_id2
LEFT JOIN tbl_users u ON p.user_id = u.user_id
-- 预聚合tbl_po_branch_detail的sum(amount)
LEFT JOIN (
    SELECT PO_id, branch_id, SUM(amount) AS total_amount
    FROM tbl_po_branch_detail
    GROUP BY PO_id, branch_id
) pd_detail ON pd_detail.PO_id = p.PO_id AND pd_detail.branch_id = p.branch_id
-- 预聚合tbl_po_detail和tbl_po关联后的sum(amount_take)
LEFT JOIN (
    SELECT 
        po.po_id2, 
        po.branch_id2, 
        po.branch_id AS po_branch_id,
        SUM(pd.amount_take) AS total_take
    FROM tbl_po_detail pd
    LEFT JOIN tbl_po po ON pd.po_id = po.po_id AND pd.branch_id = po.branch_id
    WHERE po.status = '2'
    GROUP BY po.po_id2, po.branch_id2, po.branch_id
) pd_take ON pd_take.po_id2 = p.po_id 
    AND pd_take.branch_id2 = p.branch_id 
    AND pd_take.po_branch_id = p.branch_id2
WHERE b.owner_id = 1
    AND p.status = 1 
    -- 用预聚合结果做比较，COALESCE处理NULL情况
    AND pd_detail.total_amount > COALESCE(pd_take.total_take, 0)
GROUP BY p.PO_id, p.branch_id, p.branch_id2
ORDER BY p.po_date DESC, b.fullname

2. 添加针对性索引 #

给涉及聚合和关联的字段添加复合索引，大幅提升聚合和JOIN的效率：

• tbl_po_branch_detail：CREATE INDEX idx_po_branch_amount ON tbl_po_branch_detail(PO_id, branch_id, amount);
• tbl_po：CREATE INDEX idx_po_status_relations ON tbl_po(status, po_id2, branch_id2, branch_id);
• tbl_po_detail：CREATE INDEX idx_pd_po_branch_take ON tbl_po_detail(po_id, branch_id, amount_take);
• tbl_po_branch：CREATE INDEX idx_pob_status_date ON tbl_po_branch(status, po_date, branch_id, branch_id2);
• tbl_branch：CREATE INDEX idx_branch_owner_fullname ON tbl_branch(branch_id, owner_id, fullname);

3. 额外优化点 #

• 如果主查询中LEFT JOIN tbl_users u没有用到u的任何字段，可以直接去掉该关联，减少不必要的开销；
• 确认pd_take.total_take的NULL场景，用COALESCE转为0避免比较逻辑出错。

内容的提问来源于stack exchange，提问作者Witchaya Eungphinij