如何优化MySQL上的许多子查询
如何优化MySQL上的许多子查询
提问于 2020-11-23 10:14:34
我对MySQL有一个丑陋的查询。查询中有许多子查询。他们暂时不能更改查询。有时，查询堆栈在statistics状态。一些文档说，它依赖于服务器的optimizer_search_depth配置参数。我试过0和1，但是没有什么改变。查询仍然超时。
AWS上的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 ='test@gmail.com' 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 = 'test@gmail.com'
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 ='test@gmail.com' 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 ='test@gmail.com' 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 ='test@gmail.com' 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 ='test@gmail.com' 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 = 'test@gmail.com' 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 = 'test@gmail.com' 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 = 'test@gmail.com' 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转至里克·詹姆斯
这个数据库设计得不好。除了纬度/经度之外，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')，它仍然是错误的，还是比您建议的要长？
这些事务表大约有5000万行。服务器有64 is内存，innodb_buffer_pool_size为44 is。
回答 1
Database Administration用户
回答已采纳
发布于 2020-11-27 23:43:45
纬度/经度- VARCHAR？为什么不浮动(或其他数值)。
and td.Latitude = '40.22126'如果您的值与=完全相同，并且正在使用VARCHAR，那么使用D2的情况就会发生变化。但这很不寻常。
这是什么？
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 ..)需要一个去dup通行证.)
(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尤其效率低下；我想看看我们是否应该做出一些特殊的努力来提高它们的性能。
PRIMARY KEY是UNIQUE键。也就是说，PRIMARY KEY(x), UNIQUE(x)是多余的；删除唯一的。
当您有INDEX(a,b)时，不要也有INDEX(a)。(此建议不适用于b，只适用于最左边的列(S))。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/280155
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号