当调用具有以下查询的Api时，我得到'操作在160000毫秒后超时，接收0字节'。表包含大量数据，因此我设置了限制，以便每次调用只获取2000条记录。但是执行以下查询仍然需要花费大量时间。
在我的PHP端，我添加了set_time_limit(0)，增加了CURLOPT_TIMEOUT时间的curl请求，删除嵌套的for和foreach，但仍然不工作。
SELECT
SQL_CALC_FOUND_ROWS
tm.TXNID,
tm.MERCHANT,
tm.AMOUNT,
tm.MERCHANT_TXN_DATE_TIME,
tm.TXN_TYPE,
CONCAT(PG_COMPANY,'-',cm.CHANNEL) AS BANK
FROM
tbl_master AS tm JOIN tbl_pg_rates AS c
ON c.merchant_channel_pg_id=tm.merchant_channel_pg_id
INNER JOIN tbl_pg_master AS cpm
ON c.channel_pg_id=cpm.channel_pg_id
INNER JOIN tbl_channel_master AS cm
ON cpm.CHANNELID=cm.CHANNELID
INNER JOIN tbl_payment_gateway_master AS pgm
ON cpm.PGID=pgm.PGID
WHERE
MERCHANT_TXN_DATE_TIME>=UNIX_TIMESTAMP('2021-09-01 00:00:00')
AND MERCHANT_TXN_DATE_TIME<=UNIX_TIMESTAMP('2021-09-16 23:59:59')
AND txn IN('netbnk','pg','ppc','upi')
AND tm.PROFILEID=28688 AND TXN_STATUS=1
AND txnid NOT IN(
SELECT tm.txnid
FROM `tbl_master` tm JOIN `tbl_irctc_refund_settled_txns` iref
ON tm.txnid = iref.txnid
WHERE
CANCELLATION_DATE>='2021-09-01'
AND CANCELLATION_DATE<='2021-09-16'
AND txn IN('netbnk','pg','ppc','upi')
)
LIMIT 0,2000;
EXPLAIN的结果
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ------------------------------------------------------------------------------------- -------------- ------- ---------------------------------- ------ -------- -------------
1 PRIMARY tm (NULL) ref fk_TRANSACTION_MERCHANT_CHANNEL_PG_ID,PROFILEID,inx_txn_status,MERCHANT_TXN_DATE_TIME inx_txn_status 4 const 119 12.50 Using where
1 PRIMARY c (NULL) eq_ref PRIMARY,fk_RATES_CHANNELPGID PRIMARY 4 dbpayment.tm.MERCHANT_CHANNEL_PG_ID 1 100.00 (NULL)
1 PRIMARY cpm (NULL) eq_ref PRIMARY,fk_CHANNEL_ID,fk_PG_ID PRIMARY 4 dbpayment.c.CHANNEL_PG_ID 1 100.00 (NULL)
1 PRIMARY cm (NULL) eq_ref PRIMARY PRIMARY 4 dbpayment.cpm.CHANNELID 1 100.00 (NULL)
1 PRIMARY pgm (NULL) eq_ref PRIMARY PRIMARY 4 dbpayment.cpm.PGID 1 100.00 (NULL)
2 SUBQUERY iref (NULL) ALL (NULL) (NULL) (NULL) (NULL) 27 5.55 Using where
2 SUBQUERY tm (NULL) eq_ref PRIMARY,inx_txnid
我同意这个评论:NOT IN SELECT会毁了演出的。我会尝试左连接tbl_irctc_refund _settd_txns并在WHERE中添加附加条件。在类似的情况下尝试过，对我有效:
SELECT
SQL_CALC_FOUND_ROWS
tm.TXNID,
tm.MERCHANT,
tm.AMOUNT,
tm.MERCHANT_TXN_DATE_TIME,
tm.TXN_TYPE,
CONCAT(PG_COMPANY,'-',cm.CHANNEL) AS BANK
FROM
tbl_master AS tm JOIN tbl_pg_rates AS c
ON c.merchant_channel_pg_id=tm.merchant_channel_pg_id
INNER JOIN tbl_pg_master AS cpm
ON c.channel_pg_id=cpm.channel_pg_id
INNER JOIN tbl_channel_master AS cm
ON cpm.CHANNELID=cm.CHANNELID
INNER JOIN tbl_payment_gateway_master AS pgm
ON cpm.PGID=pgm.PGID
LEFT JOIN `tbl_irctc_refund_settled_txns` iref
ON tm.txnid = iref.txnid
WHERE
MERCHANT_TXN_DATE_TIME>=UNIX_TIMESTAMP('2021-09-01 00:00:00')
AND MERCHANT_TXN_DATE_TIME<=UNIX_TIMESTAMP('2021-09-16 23:59:59')
AND txn IN('netbnk','pg','ppc','upi')
AND tm.PROFILEID=28688 AND TXN_STATUS=1
AND (iref.txnid IS NULL
OR iref.CANCELLATION_DATE < '2021-09-01'
OR iref.CANCELLATION_DATE > '2021-09-16')
LIMIT 0,2000;
(假设某些列在tm中…)
iref: INDEX(txnid, txn, CANCELLATION_DATE)
tm: INDEX(PROFILEID, TXN_STATUS, txn, MERCHANT_TXN_DATE_TIME)
cpm: INDEX(channel_pg_id, PGID, CHANNELID)
cm: INDEX(CHANNELID, CHANNEL)
c: INDEX(merchant_channel_pg_id, channel_pg_id)
请提供EXPLAIN SELECT ...
我认为很多人都忽略了子选择中的一个重要项目。查询完整的Tbl_master，查找相同的20121-09-01和20121-09-16之间的cancellation_date和txn。如果你有数百万行，你就会陷入困境。它可能有助于澄清和示例数据，但数据查询可能表明Merchant_txn_date_time将始终在事务上提供，并且cancellation_date将是有条件的，只有当它表示与先前事务相对的事务时。
也就是说，您永远不会在初始事务之前取消。你的主要查询是寻找9/1和9/16之间发生的交易，我要补充的是，merchant_txn_date_time也是你的子查询的一部分，但只是为了限定FROM Date。我想要所有在9/1当天或之后被取消的交易。不是整个事务取消数据库。
有交易的样本数据来审查可能会有所帮助，下面只是对数据可能是什么样子的一个猜测，并有利于你自己在未来提供类似的澄清。
txnid Merchant_txn_date_time Cancellation_Date
100 8/30
101 8/31
102 9/1
103 9/1 9/1 Cancel of txn 100
...
109 9/7 9/7 Cancel of txn 102
110 9/7
...
128 9/13
...
132 9/15 9/15 Cancel of txn 110
...
157 9/20 9/20 Cancel of txn 128
以上只是例子。由于您的主要查询是merchant_txn_date_time，因此它将只从txnID 102开始，并在每个日期范围内以132结束。但是对于要考虑的取消，它仍然会抓取事务取消id 157，即使它超出了9/15结束日期作为内部WHERE限定符的一部分。如前所述，取消总是发生在原始事务发生之后。同样，这是部分猜测，因为如果取消日期是tbl_master或iref表的一部分，则它永远不会被限定，因此在整个查询中使用适当的alias.column引用非常重要。
另外，配对更少。配置文件ID是否与原始交易相同?就像一个人的银行账户，你只希望那个人的账户进一步限制同一个人的个人资料id = 28688和Merchant_txn_date_time>= 9/1和Cancellation_Date上下文被应用。至少，Merchant_txn_date_time>= 9/1会有很大帮助。
最后，我将添加一个string_join来告诉MySql按照这里指定的顺序运行查询
SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS
tm.TXNID,
tm.MERCHANT,
tm.AMOUNT,
tm.MERCHANT_TXN_DATE_TIME,
tm.TXN_TYPE,
CONCAT(pgm.PG_COMPANY,'-',cm.CHANNEL) AS BANK
FROM
tbl_master tm
JOIN tbl_pg_rates c
ON tm.merchant_channel_pg_id = c.merchant_channel_pg_id
JOIN tbl_pg_master cpm
ON c.channel_pg_id = cpm.channel_pg_id
JOIN tbl_channel_master cm
ON cpm.CHANNELID = cm.CHANNELID
JOIN tbl_payment_gateway_master pgm
ON cpm.PGID = pgm.PGID
WHERE
tm.PROFILEID = 28688
AND tm.MERCHANT_TXN_DATE_TIME >= UNIX_TIMESTAMP('2021-09-01 00:00:00')
AND tm.MERCHANT_TXN_DATE_TIME <= UNIX_TIMESTAMP('2021-09-16 23:59:59')
AND tm.TXN_STATUS = 1
AND tm.txn IN( 'netbnk', 'pg', 'ppc', 'upi')
AND tm.txnid NOT IN(
SELECT
tm2.txnid
FROM
tbl_master tm2
JOIN tbl_irctc_refund_settled_txns iref
ON tm2.txnid = iref.txnid
WHERE
-- not sure if profileId qualifies per my comment in answer
tm2.PROFILEID = 28688
-- but would ALWAYS include the original date/time transaction
-- should be AFTER the primary outer qualifying dates you are looking for
AND tm2.MERCHANT_TXN_DATE_TIME >= UNIX_TIMESTAMP('2021-09-01 00:00:00')
-- wont need this >= 9/1 cancellation since above would prevent any
-- cancellation prior to the date in question to begin with.
AND tm2.CANCELLATION_DATE >= '2021-09-01'
AND tm2.CANCELLATION_DATE <= '2021-09-16'
AND tm2.txn IN( 'netbnk', 'pg', 'ppc', 'upi')
)
LIMIT
0,2000;
为
提供了以下表和索引tbl_master ( ProfileId, Merchant_txn_date_time, txn_status, txn, txnid, merchant_channel_pg_id )
tbl_pg_rates ( merchant_channel_pg_id, channel_pg_id )
tbl_pg_master ( channel_pg_id, CHANNELID, PGID )
-- implied/guess on columns for channel and pg_company for descriptions in query
tbl_channel_master ( CHANNELID, CHANNEL )
tbl_payment_gateway_master ( PGID, PG_COMPANY )
tbl_irctc_refund_settled_txns ( txnid )