Início / dba / Perguntas / 280155
Accepted
Yunus UYANIK
Asked: 2020-11-24 02:14:34 +0800 CST

Como otimizar muitas subconsultas no MySQL

  • 772

Eu tenho uma consulta feia no MySQL. Há muitas subconsultas na consulta. Eles não podem alterar a consulta por enquanto. Às vezes, as consultas são empilhadas no statisticsestado. Algum documento diz que depende do optimizer_search_depthparâmetro de configuração do seu servidor. Eu tentei 0e 1, mas nada mudou. As consultas ainda atingem o tempo limite.

MySQL versão 8.0.20 no AWS RDS.

insira a descrição da imagem aqui

Aqui está o resultado EXPLAIN.

+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|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                       |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+

Consulta :

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

Para @Rick James

Este banco de dados não é bem projetado. Além da Latitude/Longitude, a coluna OrderId também é VARCHAR em vez de INT.

AND  '00000000' != '00000000'

Na verdade, existem dois números de telefone ou nomes de clientes diferentes aqui. Normalmente, os desenvolvedores deveriam estar fazendo no lado do aplicativo, eles não, mas eles vão mudar.

Eu crio índices que você sugeriu e descarto as chaves exclusivas e excluí alguns índices de acordo com a INDEX(a,b) - INDEX(a)regra.

Eu sei que a função using na condição está errada, nós a alteramos assim ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59'), ainda está errada ou apenas mais do que você sugere?

Essas tabelas de transações têm aproximadamente 50 milhões de linhas. O servidor tem 64 GB de memória e 44 GB innodb_buffer_pool_size.

mysql query-performance

1 respostas

  1. Best Answer

    Latitude/longitude -- VARCHAR?? Por que não FLOAT (ou outro valor numérico).

     and  td.Latitude = '40.22126'

    O uso de =funcionará se você tiver exatamente o mesmo valor e estiver usando VARCHAR. Mas isso é altamente incomum.

    O que é isso?

    AND  '00000000' != '00000000'

    INDEXsugestões:

    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)

    Ao invés de

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

    Faz

    EXISTS ( SELECT 1 FROM ... )

    ( EXISTS(..)encerra quando a primeira ocorrência é encontrada, portanto, mais eficiente. COUNT()precisa encontrar quantos. COUNT(DISTINCT ..)precisa de um passe de desduplicação.)

    (Não há atalho para COUNT(DISTINCT(ti.OrderId)) >= 5.)

    Não sargável:

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

    Altere para

    t.OrderDate >= CURDATE()

    Quanta memória RAM você tem? Por favor, forneça SHOW TABLE STATUS. Qual é o valor de innodb_buffer_pool_size? UUIDs são especialmente ineficientes para tabelas grandes; Quero ver se devemos fazer algum esforço especial para melhorar o desempenho deles.

    A PRIMARY KEYé uma UNIQUEchave. Ou seja, PRIMARY KEY(x), UNIQUE(x)é redundante; largar o único.

    Quando você tem INDEX(a,b), não tem também INDEX(a). (Esta recomendação não se aplica a b, apenas à(s) coluna(s) mais à esquerda.)

    • 1

relate perguntas