Question

mysql 子查询计算太慢什么优化？

SELECT
e.*,
(SELECT NAME FROM dict WHERE id = e.areaId) as areaName,
(SELECT SUM(money) FROM engineering_project WHERE engineeringId =e.id) AS sumProjectMoney,
(IFNULL((SELECT SUM(actualMoney) FROM income WHERE engineeringId=e.id),0) +IFNULL((SELECT SUM(discount) FROM income WHERE engineeringId=e.id),0) +IFNULL((SELECT SUM(procedureMoney) FROM income WHERE engineeringId=e.id),0) ) AS accountPaid, ##已付款金额
IFNULL((SELECT SUM(procedureMoney) FROM income WHERE engineeringId=e.id),0) AS procedureMoneyS, ##手续费
IFNULL((SELECT SUM(discount) FROM income WHERE engineeringId=e.id),0) AS discountS, ##折扣
IFNULL((SELECT SUM(actualMoney) FROM income WHERE engineeringId=e.id),0) AS actualMoneyS, ##实收
(SELECT SUM(money) FROM engineering_project WHERE engineeringId =e.id)-(IFNULL((SELECT SUM(actualMoney) FROM income WHERE engineeringId=e.id),0) +IFNULL((SELECT SUM(discount) FROM income WHERE engineeringId=e.id),0) +IFNULL((SELECT SUM(procedureMoney) FROM income WHERE engineeringId=e.id),0) ) AS debtMoney, ##欠付款金额
IFNULL((SELECT SUM(actualMoney) FROM income WHERE engineeringId=e.id),0) AS paymentAll ##所有付款
FROM
engineering e

Answer 1

如果列比较多的话，建议别用*,
你这种最适合with as这种临时表 ,
使用子查询的方法表被扫描了多次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

Answer 2

建议建立临时表！

Answer 3

子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：

• semijoin

• Materialization

• exists

2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：

• Materialization

• exists

3. 对于 derived 派生表，优化器有如下策略选择：

• derived_merge，将派生表合并到外部查询中（5.7 引入 ）；

• 将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略