主页 / dba / 问题 / 280155
Accepted
Yunus UYANIK
Asked: 2020-11-24 02:14:34 +0800 CST

如何优化 MySQL 上的大量子查询

  • 772

我对 MySQL 有一个丑陋的查询。查询中有很多子查询。他们暂时无法更改查询。有时查询堆栈statistics状态。一些文件说，这取决于您的服务器optimizer_search_depth配置参数。我尝试了0and 1，但没有任何改变。查询仍然超时。

AWS RDS 上的 MySQL 版本 8.0.20。

在此处输入图像描述

这是解释结果。

+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|id|select_type|table|partitions|type  |key_len|rows|filtered|Extra                             |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|1 |PRIMARY    |NULL |NULL      |NULL  |NULL   |NULL|NULL    |No tables used                    |
|45|SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|45|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |100     |Using where                       |
|45|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|43|SUBQUERY   |NULL |NULL      |NULL  |NULL   |NULL|NULL    |Impossible WHERE                  |
|44|SUBQUERY   |ti   |NULL      |ref   |78     |3   |1.67    |Using where                       |
|44|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|42|SUBQUERY   |td   |NULL      |ref   |78     |2   |100     |Using index                       |
|42|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|41|SUBQUERY   |td   |NULL      |ref   |78     |10  |100     |Using index                       |
|41|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|40|SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|40|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|39|SUBQUERY   |ti   |NULL      |ref   |387    |1   |5       |Using where                       |
|38|SUBQUERY   |ti   |NULL      |ref   |111    |1   |5       |Using where                       |
|37|SUBQUERY   |ti   |NULL      |ref   |111    |1   |100     |Using where                       |
|36|SUBQUERY   |ti   |NULL      |ref   |303    |49  |100     |Using where; Using index          |
|36|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|35|SUBQUERY   |ti   |NULL      |ref   |78     |3   |100     |Using where; Using index          |
|35|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|33|SUBQUERY   |t    |NULL      |ref   |752    |2   |2.5     |Using where                       |
|32|SUBQUERY   |t    |NULL      |ref   |752    |2   |5       |Using where                       |
|31|SUBQUERY   |ti   |NULL      |ref   |753    |10  |3.77    |Using where                       |
|30|SUBQUERY   |td   |NULL      |ref   |1203   |1   |100     |NULL                              |
|30|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |100     |Using where                       |
|30|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|29|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|28|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|28|SUBQUERY   |td   |NULL      |ref   |8      |1   |100     |Using where                       |
|27|SUBQUERY   |td   |NULL      |ref   |414    |1   |100     |Using index                       |
|27|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|26|SUBQUERY   |td   |NULL      |ref   |414    |1   |100     |Using index                       |
|26|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|25|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|25|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|24|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|24|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|23|SUBQUERY   |td   |NULL      |ref   |189    |1   |100     |Using index                       |
|23|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|22|SUBQUERY   |td   |NULL      |ref   |189    |1   |100     |Using index                       |
|22|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|21|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition; Using where|
|21|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|20|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition; Using where|
|20|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|19|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|19|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|18|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|18|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|17|SUBQUERY   |ti   |NULL      |range |462    |2   |2.5     |Using index condition; Using where|
|17|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|16|SUBQUERY   |ti   |NULL      |range |84     |1   |10      |Using index condition; Using where|
|15|SUBQUERY   |ti   |NULL      |range |912    |2   |2.5     |Using index condition; Using where|
|14|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|13|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|13|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|12|SUBQUERY   |ti   |NULL      |range |462    |2   |2.5     |Using index condition; Using where|
|11|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition             |
|10|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|9 |SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|9 |SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|8 |SUBQUERY   |ti   |NULL      |ref   |753    |10  |50      |Using where                       |
|8 |SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|7 |SUBQUERY   |ti   |NULL      |ref   |111    |1   |100     |Using index                       |
|7 |SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|5 |SUBQUERY   |ti   |NULL      |ref   |387    |1   |50      |Using where                       |
|5 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|4 |SUBQUERY   |ti   |NULL      |ref   |753    |10  |1.85    |Using where                       |
|3 |SUBQUERY   |td   |NULL      |ref   |78     |10  |100     |Using index                       |
|3 |SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |65.05   |Using where                       |
|3 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|2 |SUBQUERY   |ti   |NULL      |ref   |78     |10  |100     |Using where; Using index          |
|2 |SUBQUERY   |td   |NULL      |ref   |8      |1   |100     |Using index                       |
|2 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+

查询：

SELECT (SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone = '000000') AS SameSenderPhoneHasChargeback,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone = '0000000') AS SameReceiverPhoneHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 5,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='[email protected]' AND ti.OrderDate >= DATE_ADD(NOW(), INTERVAL -5 DAY)) AS LastFiveDayOrders,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.HashedData='9445b81fcf6df59d129d519724a509d6' AND ti.IsSavedCreditCard=0 ) AS SameHashedHasChargeback,
(SELECT IF(80.32 <100, '1', '0') AS RES) AS FromSiteOrderTotal75CicekSepeti,
(SELECT (IF(SUM(td.Quantity) > 2, '1', '0')) as RES
FROM TransactionItem ti
JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId
WHERE ti.OrderId = '152706596'
and td.VariantId in ('1505398819','1503264559','1503264497','1505418522','1503415399','1503333610')) AS CheckProduct,
(SELECT IF(COUNT(ti.OrderId) > 1, '1', '0') as RES
FROM TransactionItem ti
JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId
WHERE ti.Email = '[email protected]'
and ti.CreatedOn >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
and td.VariantId in ('1505398819','1503264559','1503264497','1505418522','1503415399','1503333610')) AS CheckProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 4,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.OrderDate > date_add('2020-11-23 01:14', INTERVAL -60 MINUTE) AND td.Latitude = '40.22126' and td.Longitude = '28.9959') AS SameDaySameReceiverAddressXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderName ='test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.OrderDate > date_add('2020-11-23 01:14', INTERVAL -60 MINUTE) AND ti.SenderPhone ='0000000000') AS SameDaySameSenderPhoneAndMobileXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderName ='test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId  WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName ='test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameReceiverNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='[email protected]' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameEmailXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='[email protected]' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameEmailAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderPhone ='000000000' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderPhoneAndMobileAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId WHERE ti.SenderName = 'test test' AND td.ReceiverName = 'test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameAndReceiverNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.Email ='[email protected]' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameEmailDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.Email ='[email protected]' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameEmailDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone ='000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone ='0000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone ='00000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone ='00000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(td.ReceiverName)) > 6, '2', '0') AS RES FROM TransactionItem ti  INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId WHERE ti.IpAddress = '1.1.1.1' AND ti.SenderName != td.ReceiverName AND td.ReceiverName != 'test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameIPAddressDifferentReceiverName,
(SELECT IF(COUNT(DISTINCT(ti.SenderName)) > 6, '2', '0') AS RES FROM TransactionItem ti WHERE ti.IpAddress = '1.1.1.1' AND ti.SenderName != 'test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameIPAddressDifferentSenderName,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE td.ReceiverAddress = 'test') AS SameReceiverAddressHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 2 AND SUM(DISTINCT ti.OrderPrice) > 650, '1', '0') AS RES FROM TransactionItem ti WHERE ti.Email = '[email protected]' AND ti.OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -10 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY)) AS PastXDayMoreThanYOrdersTotalAmountBiggerYCicek,
(SELECT IF(COUNT(DISTINCT(t.OrderId)) > 4, '1', '0') AS RES FROM Transaction t WHERE t.Email = '[email protected]' AND t.OrderId != '152706596' AND t.IsPaid = 0 AND t.ApplicationId = '9c3d2993-8be4-4af0-9cfb-4d0dcd8db549' AND DATE_FORMAT(t.OrderDate,'%d-%m-%Y') = DATE_FORMAT(NOW(),'%d-%m-%Y')) AS HasNotBeenPaidXOldTransactionCicekSepeti,
(SELECT IF(COUNT(DISTINCT(t.OrderId)) > 0 AND t.OrderPrice > 300, '1', '0') AS RES FROM Transaction t WHERE t.Email = '[email protected]' AND t.OrderId != '152706596' AND t.IsPaid = 0 AND t.ApplicationId = '9c3d2993-8be4-4af0-9cfb-4d0dcd8db549' AND t.OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -90 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY)) AS HasNotBeenPaidXOldTransactionsTotalAmountGtYCicekS,
(SELECT IF(80.32 >= 300 AND 80.32 <= 499, '1', '0') AS RES) AS FromSiteOrderTotal150_250CicekSepeti,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.SenderPhone = '0000000' AND '00000000' != '00000000') AS SameSenderPhoneByReceiverPhoneHasChargeback,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.SenderName = 'test test' AND 'test test' != 'test test') AS SameSenderNameByReceiverNameHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.HashedData)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData not in ('9445b81fcf6df59d129d519724a509d6','9445b81fcf6df59d129d519724a509d6') AND ti.OrderId = '152706596') AS SameOrderDiffHashedDataXOrder,
(SELECT IF(COUNT(ti.HashedData) > 3,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData in ('9445b81fcf6df59d129d519724a509d6','9445b81fcf6df59d129d519724a509d6') AND ti.OrderId = '152706596') AS SameOrderSameHashedDataXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 3,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData ='9445b81fcf6df59d129d519724a509d6' AND ti.IsSavedCreditCard=0 AND ti.OrderDate > DATE_ADD(NOW(), INTERVAL -10 DAY) and ti.OrderDate < DATE_ADD(NOW(), INTERVAL 1 DAY)) AS SameHashedDataV2Last10DayXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 4,'1','0') FROM TransactionDetail td join  TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -10 MINUTE) and td.Latitude = '40.22126' and td.Longitude = '28.9959') AS CheckLatitudeAndLongitude,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 3,'1','0') FROM TransactionDetail td join  TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -60 MINUTE) and td.ReceiverPhone = '00000000') AS CheckReceiverPhone,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') FROM TransactionDetail td join  TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -60 MINUTE) and td.ReceiverPhone = '000000000') AS CheckReceiverAndSenderPhone,
(select IF(COUNT(DISTINCT(ti.OrderId)) >= 1,'1','0') from TransactionItem ti join TransactionDetail td on ti.PKTransactionItemId = td.FKTransactionItemId where  ti.CreatedOn > date_add('2020-11-23 01:14', INTERVAL -1 DAY) and ti.OrderId !='152706596' and td.ReceiverPhone = '000000000' and 1 = ( select IF(count(DISTINCT(ti.OrderId)) > 1, 1 ,0) from TransactionItem ti join TransactionDetail td on ti.PKTransactionItemId = td.FKTransactionItemId and ti.SenderPhone = td.ReceiverPhone where  ti.CreatedOn > date_add('2020-11-23 01:14', INTERVAL -1 DAY) and td.ReceiverPhone = '0000000000') ) AS CheckSenderAndReceiverPhone,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE td.Latitude = '40.22126' and td.Longitude = '28.9959') AS SameReceiverNameHasChargeback

致@Rick James

这个数据库设计得不好。除了纬度/经度之外，OrderId 列也是 VARCHAR 而不是 INT。

AND  '00000000' != '00000000'

实际上，这里有两个不同的电话号码或客户姓名。通常，开发人员应该在应用程序方面做，他们不会，但他们会改变。

我创建了您建议的索引并删除了唯一键，并根据INDEX(a,b) - INDEX(a)规则删除了一些索引。

我知道条件上的使用功能是错误的，我们这样更改它ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')仍然是错误的还是比您的建议长？

这些事务表大约有 50M 行。服务器有 64GB 内存，innodb_buffer_pool_size为 44GB。

mysql query-performance

1 个回答

  1. Best Answer

    纬度/经度 -- VARCHAR?? 为什么不 FLOAT（或其他数值）。

     and  td.Latitude = '40.22126'

    =如果您具有完全相同的值并且正在使用VARCHAR. 但这是非常不寻常的。

    这是什么？

    AND  '00000000' != '00000000'

    INDEX建议：

    ti:  (OrderId, PKTransactionItemId, SenderName)
ti:  (PKTransactionItemId, SenderName, CreatedOn, OrderId)
ti:  (PKTransactionItemId, Email, CreatedOn, OrderId)
ti:  (HashedData, IsSavedCreditCard, OrderId)  -- any order

td:  (Longitude, Latitude, FKTransactionItemId)

c:  (OrderId, PKChargebackId)  -- (you effectively have this)

    代替

    SELECT  IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM ... )

    做

    EXISTS ( SELECT 1 FROM ... )

    （EXISTS(..)找到第一次出现时退出，因此效率更高。COUNT()必须找到有多少。 COUNT(DISTINCT ..)需要重复数据删除。）

    （没有快捷方式COUNT(DISTINCT(ti.OrderId)) >= 5。）

    不可分割：

    DATE_FORMAT(t.OrderDate,'%d-%m-%Y') = DATE_FORMAT(NOW(),
                        '%d-%m-%Y')

    将其更改为

    t.OrderDate >= CURDATE()

    你有多少内存？请提供SHOW TABLE STATUS。的价值是innodb_buffer_pool_size多少？UUID 对于大型表尤其低​​效；我想看看我们是否应该采取一些特别的努力来提高他们的表现。

    APRIMARY KEY是一把UNIQUE钥匙。即PRIMARY KEY(x), UNIQUE(x)是多余的；放下独一无二的。

    当你有INDEX(a,b)，不要也有INDEX(a)。（此建议不适用于b，仅适用于最左侧的列。）

    • 1

相关问题