MySQL SELECT查询耗时24秒 单表记录不超1.5万如何优化

慢查询根因 #

你当前使用的是关联子查询写法，执行逻辑是：对customer表的每一行记录，都单独发起3次查询分别统计invoice、collection、cr_note三个表对应客户的金额总和。哪怕单表记录数只有1.5万条，只要customer表有数千条记录，实际扫描的数据行数就会达到千万级别，相当于反复扫了上百次三个业务表，24秒的耗时就是这么来的。

具体优化步骤 #

• 第一步：改写SQL逻辑，替换关联子查询为预聚合左连接
  先在子查询里一次性按客户编码分组算出三个表的金额总和，再和客户表做关联计算，全程每个表只需要扫描1次，从根源上减少重复扫描。改写后的SQL如下：

SELECT 
    c1.code,
    COALESCE(i.invoice_total, 0) - COALESCE(p.collection_total, 0) - COALESCE(cn.cn_total, 0) AS rem_Balance
FROM customer AS c1
LEFT JOIN (
    SELECT customer_code, SUM(total_amount) AS invoice_total
    FROM invoice
    GROUP BY customer_code
) AS i ON i.customer_code = c1.code
LEFT JOIN (
    SELECT customer_code, SUM(amount) AS collection_total
    FROM collection
    GROUP BY customer_code
) AS p ON p.customer_code = c1.code
LEFT JOIN (
    SELECT customer_code, SUM(amount) AS cn_total
    FROM cr_note
    GROUP BY customer_code
) AS cn ON cn.customer_code = c1.code;

• 第二步：创建覆盖索引，避免回表和全表扫描
  给涉及分组求和的字段建联合覆盖索引，查询时直接从索引取数据，不需要访问表数据本身，执行速度会再提升一个量级，需要执行的索引创建语句如下：

-- 发票表索引
CREATE INDEX idx_invoice_customer_amount ON invoice(customer_code, total_amount);
-- 回款表索引
CREATE INDEX idx_collection_customer_amount ON collection(customer_code, amount);
-- 贷项通知单表索引
CREATE INDEX idx_crnote_customer_amount ON cr_note(customer_code, amount);

如果customer表的code字段不是主键，也给code字段建唯一索引，进一步加快关联速度。

• 第三步：执行验证
  改写完加完索引后，用EXPLAIN命令查看执行计划，确认执行计划里没有DEPENDENT SUBQUERY标记（该标记代表仍然在执行逐行触发的关联子查询），且所有表的访问类型（type列）没有出现ALL（全表扫描），就说明优化生效。

预期效果 #

在单表记录数不超过1.5万的场景下，优化后查询耗时通常可以降到100毫秒以内。

内容的提问来源于stack exchange，提问作者Sheikh Abdul Qadeer