Yunus UYANIK提出的问题 -dba

Yunus UYANIK

Asked: 2020-11-24 02:14:34 +0800 CST

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

0

我对 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。

Yunus UYANIK

Asked: 2019-07-23 05:03:48 +0800 CST

双向数据传输

0

我有两个节点和一个数据库。我想在它们之间传输数据。服务器节点 1 上的 DML 操作应该转移到节点 2，节点 2 上的 DML 操作也应该转移到节点 1。

这可能吗？最好的（最快、最安全、最稳定）的方法是什么？

Yunus UYANIK

Asked: 2019-05-15 01:16:15 +0800 CST

为什么MSSQL在执行计划中选择scan？

4

这是我的查询（它是 Microsoft Axapta 查询）：

(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID 
FROM LEDGERJOURNALTRANS T1 
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))

当前执行计划：

当前计划

实际上，表上有一个适当的索引。

索引列：（PARTITION，DATAAREAID，REVRECID）

分片：

我尝试了索引力。这个执行计划（index seek+key lookup）比after plan（索引扫描）要快：

力指数计划

我试图：

更新统计
更改了它的列顺序，例如 (REVRECID,PARTITION,DATAAREAID)

MSSQL 为什么选择聚集索引？

Yunus UYANIK

Asked: 2018-12-15 00:33:15 +0800 CST

SQL 日志传送 - 恢复速度非常慢

1

我有 SQL Server 2012 SP4、十个数据库和日志传送。

一个数据库 2.7TB

十个数据文件和两个日志文件

日志传送配置：

事务日志备份作业：3 分钟
复印作业：1 分钟
恢复作业：1 分钟
待机模式，点击断开用户连接...

一个 50MB 的 trn 文件需要 2 小时才能恢复。数据库正在 100% 恢复并保持不变。我该如何解决这个问题？

Yunus UYANIK

Asked: 2018-07-10 23:20:52 +0800 CST

如何通过sys.fn_dump_dblog获取用户信息

2

我正在查看 sys.fn_dump_dblog，我可以看到记录的旧状态，但看不到哪些用户正在执行操作。SPID、事务 SID 列始终为空，如何修复它或任何选项？

我正在使用 SQL Server 2016。

我的查询：

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')

Yunus UYANIK

Asked: 2017-10-31 23:04:24 +0800 CST

MySQL 触发 IF 语句

0

我正在编写一个触发器来审计 MySQL 数据库。IF 语句不起作用。我在哪里做错了？

另外，您能否指导我了解 mysql 审计的最佳触发器。

DELIMITER $$

CREATE 
    DEFINER = 'sqlsa'@'%'
TRIGGER AuthenticationLog_U
    AFTER UPDATE
    ON AuthenticationLog
    FOR EACH ROW
BEGIN
    DECLARE Type varchar(1);
    DECLARE TableId tinyint;
    DECLARE IsActive tinyint;
    SET @Type='U';
    SET @TableId = 2;
    SET @IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=@TableId);
    IF (@IsActive=1) THEN
        INSERT INTO AuditDB.ExportLog (DBName,TableId,OperationType,HostName,UserName,PrimaryKey,OldData,NewData)
        VALUES (DATABASE(),@TableId,@Type,@@hostname,CURRENT_USER(),OLD.PKAuthenticationLogID,
        CONCAT_WS('',
          '[Key]=',IFNULL(OLD.Key,'NULL')
          ,'[Secret]=',IFNULL(OLD.Secret,'NULL')
          ,'[Token]=',IFNULL(OLD.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(OLD.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(OLD.IpAddress,'NULL')
          ,'[Message]=',IFNULL(OLD.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(OLD.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(OLD.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(OLD.LogType,'NULL')
          ),
            CONCAT_WS('',
          '[Key]=',IFNULL(NEW.Key,'NULL')
          ,'[Secret]=',IFNULL(NEW.Secret,'NULL')
          ,'[Token]=',IFNULL(NEW.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(NEW.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(NEW.IpAddress,'NULL')
          ,'[Message]=',IFNULL(NEW.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(NEW.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(NEW.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(NEW.LogType,'NULL'))
        );
      END IF;
END
$$

DELIMITER ;

没有错误信息。但我尝试查询，干得好。

SET @IsActive = 1;
    IF (@IsActive=1) THEN

Yunus UYANIK

Asked: 2017-10-11 23:22:58 +0800 CST

超出 SQL 最大内存限制

1

微软 SQL Server 2016 (SP1) (KB3182545)

服务器内存：61GB SQL

服务器最大内存：56320 MB

我的生产环境中有三个数据库（OneDB、TwoDB、ThreeDB）。

我sp_BlitzCache在 OneDB 上运行

可用内存 %7-8

我为 FirstResponderKit 创建了“Blitz”数据库，sp_WhoIsActive等等 dba 工具。

我sp_BlitzCache在 Blitz 数据库上运行

可用内存 %0

两个数据库的区别：

SQL Server 为什么超过最大内存限制？我该如何解决？

Yunus UYANIK

Asked: 2017-10-11 02:35:39 +0800 CST

使用索引的 MySQL 查询性能

0

Campaign Code 表中有 7700 万条记录。

指数声明：

索引 IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

(CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

第一次查询，执行时间：0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

第二个查询，执行时间：0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

第二个问题不需要更快吗？我应该怎么办？

编辑：

我为第二个查询创建了索引。

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

创建索引后，第二个查询更快，但第一个查询更慢

第一个查询执行计划已更改：执行时间：0:01:17.236

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;

Yunus UYANIK

Asked: 2017-09-23 03:59:51 +0800 CST

备份期间使用了太多内存

0

我在 ubuntu 服务器上有 mongodb。数据库版本：3.2.8

中央处理器：4核

内存 : 16 GB

磁盘：50GB

通常服务器内存状态：

运行备份脚本：

DIR=`date +%m%d%y`
DEST=DBBackup/MongoDB/$DIR
mongodump -d onedb -o $DEST
mongodump -d twodb -o $DEST
mongodump -d threedb -o $DEST
mongodump -d fourdb -o $DEST
mongodump -d fivedb -o $DEST

脚本完成和服务器内存状态：

缓存保持不变。如果我反复备份五天，所有的内存都会用完。内存不会自动刷新。我应该怎么办？

Yunus UYANIK

Asked: 2017-07-20 03:12:57 +0800 CST

SQL 空闲空间管理

4

我有 1.8TB 的 AX 数据库。

文件大小：1851427.75 MB

可用空间：502837.13 MB

如果以后要用到free space，我也不想缩了。因为要花很长时间。（和索引碎片）

每日观察结果：

白天可用空间 492.435,75 MB (26%)
白天可用空间 485.750,25 MB (26%)
日可用空间 502.875,75 MB (27%)

有时可用空间减少，有时数据文件大小增加。

为什么有空闲空间就有增长，SQL怎么决定的？

Yunus UYANIK

Asked: 2017-07-12 22:45:55 +0800 CST

查询计划已删除

0

我的生产服务器中有 200 GB 的数据库。

系统要求：Amazon EC2 r4.2xlarge（8 个 vCPU，61 GB 内存）

我检查，计划计数与查询：

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

计划数量随着时间的推移而增加，我看到总共有 3K 个计划。10-15 分钟后，我检查计划计数。

计划总数 200-600

结果：

为什么会这样？

我没有跑步DBCC FREEPROCCACHE。

自动关闭是错误的。我关闭了内存压力的parallisim和复制。PLE 7600，但数值也不时下降。

我没有检查计划在 3K 时使用了多少空间。但是，我现在检查看到 900-1K 计划和 250-300 MB。然后它被冲洗掉。

通常，查询等待时间小于 1 秒。平均 200 毫秒。前 3个等待类型CXPACKET，，CLR_AUTO_EVENT。SOS_SCHEDULER_YIELD

我知道缓存压力限制（计划可能因内存压力而被驱逐）但经常这样做是否正常？冲洗 2-3 分钟。

微软 SQL 服务器 2016 (SP1)。

Yunus UYANIK

Asked: 2017-03-01 00:00:54 +0800 CST

权限在自定义模式上创建表

2

我的默认架构 dbo。

我没有给我的用户创建表的权限。

用户权限；数据读取器，数据写入器

CREATE TABLE dbo.newtable ...

数据库“DB”中的 CREATE TABLE 权限被拒绝

但是，我的用户希望能够在其他模式中创建一个表。我怎样才能做到这一点？

CREATE TABLE support.newtable ...

谢谢你的帮助。

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

双向数据传输

为什么MSSQL在执行计划中选择scan？

SQL 日志传送 - 恢复速度非常慢

如何通过sys.fn_dump_dblog获取用户信息

MySQL 触发 IF 语句

超出 SQL 最大内存限制

使用索引的 MySQL 查询性能

备份期间使用了太多内存

SQL 空闲空间管理

查询计划已删除

权限在自定义模式上创建表

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

Yunus UYANIK's questions