首页>Database>2020-12-02 13:28source

query performance:如何在MySQL上优化很多子查询

我在MySQL上有一个难看的查询.查询中有很多子查询.他们暂时无法更改查询.有时查询会在 statistics中堆叠州.一些文件说，这取决于您的服务器 optimizer_search_depth 配置参数.我试过 0 和 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                       |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+

Query ：

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)删除了一些索引规则。

我知道条件上的using函数是错误的，我们像这样修改了它它仍然是错误的还是比您的建议更长？

这些交易表大约有5000万行.该服务器具有64GB内存和 ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') 是44GB。

innodb_buffer_pool_size

mysql

queryperformance

最新回答

1月前
1 #
纬度/经度-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()
```
您有多少RAM？请提供 SHOW TABLE STATUS . innodb_buffer_pool_size的value是什么？对于大型表，UUID特别低效；我想看看我们是否应该采取一些特殊的措施来提高其性能。

PRIMARY KEY 是个 UNIQUE 键.也就是说， PRIMARY KEY(x), UNIQUE(x) 是多余的；删除唯一。

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

▲ index:Postgresql可以利用计划中的"偶然"集群吗？

▼ MySQL服务器可以向远程服务器发送"服务器已启动/正在运行"信号吗？

Codebug

query performance:如何在MySQL上优化很多子查询

mysql

queryperformance