由于某些原因，执行此查询最多需要5分钟。我已经将连接缓冲区扩展到1g，并且我对这个查询做了解释（结果在这里）似乎没有任何东西表明为什么这会花费这么多时间。在查询过程中，所有8个cpu核心的使用率都达到了接近100%。
引擎是innodb。
所有表都有一个主键索引。
SELECT Concat(Concat(cust.first_name, ' '), cust.last_name) AS customerName,
TYPE.code AS transType,
ty1.nsfamount,
np.sumrebateamount,
trans.note_id AS note_id,
trans.createdate AS createdatestr,
n.totalamount,
n.currentfloat,
( ( n.costofborrowing * 100 ) / n.amounttolent ) AS fees,
n.amounttolent,
( 0 - ( trans.cashamount + trans.chequeamount
+ trans.debitamount
+ trans.preauthorizedamount ) ) AS paidamount,
sumpenaltyamount
FROM (SELECT *
FROM loan_transaction trans1
WHERE trans1.cashamount < 0
OR trans1.chequeamount < 0
OR trans1.debitamount < 0
OR trans1.preauthorizedamount < 0) trans
inner join customer cust
ON trans.customer_id = cust.customer_id
inner join (SELECT *
FROM lookuptransactiontypes ty
WHERE ty.code <> 'REB'
AND ty.code <> 'PN') TYPE
ON trans.transactiontype = TYPE.transactiontypesid
inner join note n
ON trans.note_id = n.note_id
inner join (SELECT note_id,
SUM(rebateamount) AS sumrebateamount
FROM note_payment np1
GROUP BY np1.note_id) np
ON trans.note_id = np.note_id
left join (SELECT note_id,
transactiontype,
( SUM(chequeamount) + SUM(cashamount)
+ SUM(debitamount) + SUM(preauthorizedamount) )AS
NSFamount
FROM (SELECT *
FROM loan_transaction trans4
WHERE trans4.cashamount > 0
OR trans4.chequeamount > 0
OR trans4.debitamount > 0
OR trans4.preauthorizedamount > 0)trans5
inner join (SELECT transactiontypesid
FROM lookuptransactiontypes ty2
WHERE ty2.code = 'NSF')type2
ON
trans5.transactiontype = type2.transactiontypesid
GROUP BY trans5.note_id) ty1
ON ty1.note_id = trans.refnum
left join (SELECT note_id AS noteid,
( SUM(tp.cashamount) + SUM(tp.chequeamount)
+ SUM(tp.debitamount)
+ SUM(tp.preauthorizedamount) ) AS sumpenaltyamount
FROM loan_transaction tp
inner join (SELECT transactiontypesid
FROM lookuptransactiontypes lp
WHERE lp.code = 'PN') lp
ON lp.transactiontypesid = tp.transactiontype
GROUP BY tp.note_id) p
ON p.noteid = trans.refnum最新解释
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL 2241
1 PRIMARY <derived4> ALL 191441 Using join buffer
1 PRIMARY n eq_ref PK_NOTE PK_NOTE 8 np.note_id 1
1 PRIMARY <derived2> ALL 274992 Using where; Using join buffer
1 PRIMARY cust eq_ref PRIMARY_97 PRIMARY_97 8 trans.CUSTOMER_ID 1
1 PRIMARY <derived5> ALL 2803
1 PRIMARY <derived8> ALL 14755
8 DERIVED <derived9> ALL 2 Using temporary; Using filesort
8 DERIVED tp ref TRANSACTIONTYPE TRANSACTIONTYPE 9 lp.transactionTypesID 110 Using where
9 DERIVED lp ALL 2206 Using where
5 DERIVED <derived7> ALL 98 Using temporary; Using filesort
5 DERIVED <derived6> ALL 314705 Using where; Using join buffer
7 DERIVED ty2 ALL 2206 Using where
6 DERIVED trans4 ALL 664587 Using where
4 DERIVED np1 index note_payment_idx_id_rebateamount 16 193366 Using index
3 DERIVED ty ALL 2206 Using where
2 DERIVED trans1 ALL 664587 Using where
2条答案
按热度按时间yqlxgs2m1#
老实说，这个问题有很多错误。您可以按照以下规则轻松简化：
可以同时合并多个列（例如：concat（column1，''，column2））
不需要在同一个表上（或在第一个表的右边）使用内部联接执行子查询。只需将from直接放在子查询的主表上，并将子查询的筛选器移动到主查询的where中
不确定，但你的逻辑似乎都是基于每个音符的。如果确实是这样，请在主查询的groupby=>中移动groupby note\u id摆脱所有按note\u id处理的子查询，只需在所需的表上联接，并在主查询select中移动sum（）和其他列选择
如果希望有两个基于同一个表但使用不同筛选器的值，则不需要进行子查询，可以使用for（比如sum（））示例：
sum（如果（column1=your\u filter1或column1=your\u filter2，column1，0））作为totalwithfilter1和filter2[…]按注解分组\u id
最后但并非最不重要的一点是，您正在联接（内部）一个按不同于“reb”或“pn”的type.code筛选的表，但随后您正在联接（左）一个按type.code=“pn”筛选的结果集，这毫无意义，左联接将始终导致null
仅供参考，因为我所说的可能看起来很模糊，所以我开始简化您的查询，但停止了这种胡说八道，因为我不知道您想要实现什么（没有重构2左连接）。以下是查询（但无法测试）：
r7knjye22#
我同意@aurelian的回答，为什么您可以连接到一个派生表，而您可以连接到一个普通表并应用过滤器。为什么这么做
当你能做到的时候
除了@aurelian答案，
imho，你的查询的问题是，你需要所有客户的数据，所以不管这个查询如何优化，你仍然在做全面扫描，你无法扩展，想象一下几年后你有1亿笔交易。
可能这并不是您想要的，但是对这样的报表进行分区/分页怎么样。用户通常不需要一次向所有客户展示，也不需要浪费资源。
我们的计划是做同样的工作，但只为50个客户。
在您摆脱了不必要的子查询之后（如@aurelian answer中所示），请更改他的查询的这一部分
在这里
请注意，带偏移量的分页是不可伸缩的，因此如果您的customers表很大，您可以考虑另一种分页类型